【スプレッドシートの便利な使い方】複雑な条件に一致するものを処理するQUERY関数
【スプレッドシートの便利な使い方】複雑な条件に一致するものを処理するQUERY関数
要約
スプレッドシートで他の列が特定の条件に一致しているか？はIF関数やSUMIF関数、COUNTIF関数など、IFがつく関数を使うことが多いと思います。
ただ、複雑な条件にしようとすると、IFがいくつも重なってしまい、見づらくなります。
そんなときに便利なのがQUERY関数です。
QUERY関数とは
Googleのヘルプページ
https://support.google.com/docs/answer/3093343?hl=ja
を読むと。。。難しいことが書いてあります。
ざっくり言うと、プログラミングで使われるSQL文と呼ばれる検索方法を使えるようになる関数です。
ただし、他のデータベースで実装されているSQL文すべてをサポートしているわけではないので、その点は注意が必要です。
非常に便利な関数ですが、スプレッドシートの他の関数とは異なり、少し癖があるため、使いこなすためには練習が必要です。
この記事では、QUERY関数の基本的な使い方を紹介します。
すべての機能を網羅することはできませんが、基本的な使い方を覚えて、色々と試してみてください。
今回の記事の例
今回使用したスプレッドシートは下記のリンクから見ることができます。合わせてご覧ください。
https://docs.google.com/spreadsheets/d/1gyRHe9obHOWCKaaWt8tk-34kMHWEpMODiEp4g2awyDc/edit#gid=1843083096
QUERY関数の引数
QUERY関数では、第1引数に対象範囲、第2引数にクエリ文字列を指定します。
以下のように書きます。
query($A
第1引数は他の関数と同じように範囲を指定します。
第2引数がクエリ文字列となっており、すべてが文字列であるため""で囲む必要があります。
select * where A like '%県'
の部分が、クエリ文字列です。
SQL文の記述方法となっています。
この例では、
select * : すべての列を抽出
where: 条件を指定
A like '%県' : A列の値が「県」で終わるもの
を指定しているため、A列の値が「県」で終わる行について、A列とB列の値を抽出します。
集計も可能
selectで指定する部分を変えることで、集計も可能です。
以下のように書くことで、A列の値が「県」で終わる行の数をカウントすることができます。
query($A
以下のように書くことで、A列の値が「県」で終わる行のC列の値の合計を求めることができます。
query($A
自動でcountやsumといったラベルをつけてくれますが、不要な場合は
query($A
と、label count(A) ''のように指定することで、ラベルを消すことができます。
''の部分が空文字を意味していますので、'独自のラベル'のように指定することもできます。
条件を複数指定
ANDやORで複数の条件を指定することも可能です。
query($A
これは、
A列の値が「県」で終わる
C列の値が50以上
という条件に合致するB列とC列の値を抽出し、
C列の値で降順に並べ替え
ということをしています。
フィルタすればいいのでは？
スプレッドシートには、フィルタの機能があるため、フィルタを使えば同じことができるのでは？と思うかもしれません。
フィルタは手動で操作する必要がありますが、QUERY関数は他のセルの値を参照して自動で更新させることができます。
例えば
query($A
は、先程と同じような抽出をしていますが、
R4に「都」「道」「府」「県」のいずれかの文字列を指定
R5に対象となる条件を指定
R6に抽出する行数を指定
という値を入力しておき、このセルの値を変更することで、抽出条件を変更した結果が表示できるようになります。
もちろん、他の計算結果が入っているセルの値を参照することも可能なので、他の計算結果をもとに、複雑な条件に合致するものだけを抽出するという使い方ができます。
まとめ
QUERY関数は、複雑な条件に一致するものを抽出するための関数です。
IF関数やSUMIF関数、COUNTIF関数などを使って複雑な条件に一致するものを抽出することもできますが、見づらくなりがちです。
QUERY関数を使うことで、見やすく、かつ、自動で更新されるようなスプレッドシートを作成することができます。
スプレッドシートの作業効率を上げるために、QUERY関数を使いこなしてみてください。
Discussion