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値)