スプレッドシートには、SQL のようにデータを抽出するQUERY関数が用意されています。
QUERY関数を使うと条件を満たすデータを簡単に抽出することができますが、条件が多くなったりすると数式を書くのが大変です。
さらに、条件を数式に直接書いていると、条件を変えたいときに毎回書き換える必要があります。
この記事では、QUERY関数の条件を動的に変える方法について解説します。
スプレッドシートの準備
サンプルデータとして、次のようなデータを使います。
| ID | 名前 | 部署 |
| 1 | 佐藤 | 営業 |
| 2 | 鈴木 | 総務 |
| 3 | 高橋 | 人事 |
| 4 | 田中 | 営業 |
| 5 | 伊藤 | 総務 |
| 6 | 渡辺 | 人事 |
| 7 | 山本 | 営業 |
QUERYを使って抽出する
QUERY関数については、次の記事で解説しています。
QUERY関数を使ってデータを抽出するには、次のような数式を使います。
OR を使って複数の条件を指定することで、抽出することができます。
=QUERY(A:C, "WHERE C = '営業' OR C = '人事'")
これで、条件を指定して抽出するとができましたが、条件が多くなったときに１つ１つ書くのは大変です。
もう少し、簡単に書ける方法について考えてみましょう。
複数の条件を動的に指定する
条件を指定するデータを準備します。
| 条件部署 |
| 人事 |
| 営業 |
| 開発 |
| 総務 |
この条件によってデータを抽出してみます。
FILTERで条件を抽出する
FILTER関数は、指定した条件に一致するデータを抽出するのに使えます。
=FILTER(範囲, 条件１, [条件2, ...])- 範囲：フィルタ処理をするデータを指定します。
- 条件1：範囲の1列目に対応する TRUE または FALSE を含む列、あるいは TRUE または FLASE と評価される配列数式を指します。
- 条件2：省略可能
たとえば、A列からC列までのデータで、部署が「営業」のデータを抽出するには、次のように使います。
=FILTER(A:C, C:C="営業")
今回は、条件のデータを取得するのに使います。
次のような数式を使えば、E列の2行目以下の値を取得することができます。
=FILTER(E2:E, E2:E <> "")
E列に値を追加すれば、自動でFILTER関数で表示している値も追加されます。
ARRAYFORMULAで条件を作る
スプレッドシートには、ARRAYFORMULA関数というとても便利な関数が用意されています。
ARRAYFORMULA関数は、配列数式から返された値を複数行に表示することができます。
=ARRAYFORMULA(配列数式)- 配列数式：１つの範囲か１つのセル範囲または同じサイズの複数範囲を使用する数式か、１つのセルより大きい結果を返す関数を指定します。
ARRAYFORMULA関数を使って、先ほどのFILTER関数で表示している値を変更します。
次の数式で「C = “人事”」のようにします。
=ARRAYFORMULA("C = '" & FILTER(E2:E, E2:E <> "") & "'")
JOINで条件をつなぎ合わせる
JOIN関数は、指定した区切り文字を使って値を結合することができます。
=JOIN(区切り文字, 値または配列1, [値または配列2, ...])- 区切り文字：結合する各値の間に配置する文字列
- 値または配列１：区切り文字を使用して結合する値または配列
- 値または配列２：省略可能
JOIN関数を使って、先ほど作った条件を OR でつなぎあわせます。
=JOIN(" OR ", ARRAYFORMULA("C = '" & FILTER(E2:E, E2:E <> "") & "'"))
QUERY
最後に、この条件を使ってデータを抽出します。
QUERY関数をシンプルにするために、先ほどの数式を次のように変更します。
これでQUERY関数のクエリができました。
="WHERE " & JOIN(" OR ", ARRAYFORMULA("C = '" & FILTER(E2:E, E2:E <> "") & "'"))
QUERY関数のクエリに先ほどのセルを参照することで、データを抽出することができます。
=QUERY(A:C, F2)
E列の条件部署を変えるだけで、QUERY関数はそのままで抽出するデータが自動で変わります。
まとめ
QUERY関数のクエリ部分をセル参照にすることで、柔軟にデータを抽出することができます。
FILTER関数、ARRAYFORMULA関数、JOIN関数などを利用すればクエリの条件を作ることができます。
参考
QUERY – Google ドキュメント エディタ ヘルプ
FILTER – Google ドキュメント エディタ ヘルプ
