Googleスプレッドシートで条件に一致するセルを抽出・合計する方法

Googleスプレッドシートで条件と一致するセルを抽出する、XLOOKUP関数の使い方をご紹介します。条件と合う行をすべて抽出・合計する方法や、色付けする方法についても解説しているので、ぜひ参考にしてみてください。

条件に一致するセルを抽出する方法

XLOOKUP関数は検索範囲から検索キーを検索し、結果の範囲から行または列を返します。

例えば XLOOKUP(E2,A2:A6,B2:C6) なら、Noの列から 2 を検索します。一致した行を結果の範囲から返すので、抽出されるのは果物とりんごの 1 行です。

検索キーに文字列を直接指定する場合は、二重引用符(“検索キー”)を使用します。

XLOOKUP関数の必須要素：(検索キー, 検索範囲, 結果の範囲)

FILTER関数は指定した範囲を、条件を満たす行または列にフィルタ処理して返します。

例えば FILTER(A2:C6,B2:B6=E2) なら、まず分類を果物に絞込みます。次に処理結果を指定した範囲から返すので、分類が果物のすべてのデータを抽出可能です。

条件に文字列を直接指定する場合は、二重引用符(B2:B6=”果物”)を使用します。

FILTER関数の構成要素：(範囲, 条件1, [条件2, …])

SUM関数を組み合わせると、条件と合うデータをそのまま合計することも可能です。

例えば SUM(FILTER(D2:D6,B2:B6=F2)) なら、まずFILTER関数が価格を果物のみにフィルタ処理します。次にSUM関数はフィルタ結果を受け取り、合計値 600 を返します。

SUM関数の構成要素：(数値 1, [数値 2], …)

SUMIF関数は検索範囲から、検索条件満たすセルを合計値を返します。

例えば SUMIF(D2:D6,”>=200″) なら、返り値は 200 と300 の合計値 500 です。条件に記号や文字列を使用する場合、例のように二重引用符で囲ってください。

SUMIF関数の構成要素：(検索範囲, 条件, [合計範囲])

検索範囲と合計範囲が異なる場合は、最後に合計範囲を追加してください。

例えば SUMIF(B2:B6,”果物”,D2:D6) なら分類が果物のセルを検索し、合計を価格の範囲から返します。なお SUMIFS関数を使うと、条件を複数指定することが可能です

SUMIFS関数の構成要素：(合計範囲, 条件範囲 1, 条件 1, [条件範囲 2, 条件 2], …)

まず色付けする範囲全体を選択し、表示形式タブから条件付き書式を開きます。

書式設定の条件を 次と等しい に設定し、色付けするセルの条件を入力してください。完了ボタンをクリックすると、条件と一致するセルに色がつきました。

行全体に色付けする場合は、書式設定の条件を カスタム数式 に設定します。

数式欄に入力するのは =$B2=”果物” です。B2 の部分にはトリガーとなる列の最初のセルを指定、列記号の前には固定記号($)を付けてください。

指定した列のセルが条件と一致する場合に、範囲内の同じ行のセルにも色が付きます。

条件に指定する文字列をセル参照にする場合は、$B2=$E$1 のようになります。

IF関数は論理式が成立する場合は TRUE値を、不成立の場合は FALSE値を返します。

例えば IF(D2>=200,”〇”,””) は価格が 200 以上なら〇を、それ以外なら空白(“”)を返します。

論理式に重複を指定する場合は IF(COUNTIF($C$2:$C$6,C2)>1,”〇”,””) です。COUNTIF関数で範囲内の重複を数え、重複が 1 より大きければ〇を返します。

IF関数の構成要素：(論理式, TRUE値, FALSE値)