スプレッドシートで特定の条件に合致するデータを抽出する際に便利な関数は主に以下のようなものがあります。
FILTER関数
条件に合致する行を抽出できます。複数の条件を組み合わせることも可能です。
- 構文:
=FILTER(範囲, 条件1, [条件2, ...]) - 範囲から指定した条件に合致する行を抽出して配列として返します。 例: 売上データから東京の売上のみを抽出する
- 例:
=FILTER(A2:B10, A2:A10>20, B2:B10<>"東京")
=FILTER(A2:D10, C2:C10="東京")QUERY関数
より高度な条件抽出が可能で、SQLに準じた構文を使用します。
- 構文:
=QUERY(データ, "クエリ") - SQLクエリに準じた構文でデータを抽出・加工します。
- 例: 部門ごとに売上の合計を算出し、売上が高い順に並べ替える
=QUERY(A1:C10, "SELECT A, B WHERE C > 30 ORDER BY B DESC")VLOOKUP、HLOOKUP関数
別の列やシートを検索して、条件に合致する値を返します。
- 構文:
=VLOOKUP(検索キー, 範囲, 列番号, [範囲外部分]) - 最初の列を検索キーとして、範囲内で一致する行の指定列の値を返します。
- 例: 従業員IDから従業員名を引っ張る
従業員マスター(A2:B10)が | 101 | 鈴木 のとき、=VLOOKUP(D2, A2:B10, 2, FALSE) でD2セルの値が101なら”鈴木”を返す。
=VLOOKUP(D2, A2:B10, 2, FALSE)SUMIFS、COUNTIFS、AVERAGEIFS関数
複数の条件に合致するセルの値を集計(合計、カウント、平均など)できます。
- 構文:
=SUMIFS(合計範囲, 条件範囲1, 条件1, [条件範囲2, 条件2, ...]) - 指定した条件に合致するセルの値の合計を返します。
- 例: 各支店の売上合計を算出する
=SUMIFS(F2:F20, B2:B20, B2, C2:C20, "東京支店")INDEX、MATCH関数
行番号や列番号で条件に合致するセルの値を取得できます。MATCHで条件を指定し、INDEXで値を取得します。
- INDEX構文:
=INDEX(範囲, 行番号, [列番号]) - MATCH構文:
=MATCH(検索値, 範囲, [検索方式])MATCHで条件に合致する位置を取得し、INDEXでその位置の値を返します。 - 例: Aカラムのデータから最大・最小値を探す
=INDEX(A2:A10, MATCH(MAX(A2:A10), A2:A10, 0))まとめ
これらの関数を上手く組み合わせることで、複雑な条件に合致するデータの抽出が可能になります。状況に応じて適切な関数を選択するとよいでしょう。条件が複雑になるほど、QUERY関数が便利になる傾向にあります。
