はじめに
クエリ関数をもっと便利に使う！
クエリ関数とは、Googleスプレッドシート特有の関数で、指定したデータ範囲内から、条件を指定してデータ抽出などを行える関数です。
このように、指定範囲から条件を指定してデータを取り出す関数は、EXCELでもお馴染みのデータベース関数各種がありますが、このクエリ関数を使うことによって、より柔軟に複雑な条件を簡単に指定することが出来るようになります。
EXCELにはない関数なので、せっかくスプレッドシートを使うなら覚えておきたい関数ですね(^^)
今回は、クエリ関数をもっと便利に使う方法を紹介させていただきます！
これまでのシリーズはこちら
指定する条件を「セル参照」で指定する
これまでは、条件をクエリ関数に直接入れ込む方法で紹介してきましたが、実際に実務で使う場合は、条件をちょこちょこと変えたりしたい場面なども多いのではないかと思います。
ここでは、クエリ関数の条件部分を「セル参照」にし、セルの値を変更することで抽出するデータを変更する方法をご紹介します。
構文：セルの値が「文字列」の場合(条件は仮に「＝」とする)
=QUERY(範囲,”select 列 where 列='”&セル&”‘”)
「”」ダブルクォーテーションと「’」シングルクォーテーションが入り乱れてちょっと分かりにくいですね。。。
離して書きます。
=QUERY(範囲,”select 列 where 列=’ ” &セル& ” ‘ “)
「select～」全体を「”」ダブルクォ―テーションで囲っています。「＆セル＆」を「”」ダブルクォーテーションで囲って、更に「’」シングルクォーテーションで囲っています。
構文：セルの値が「数値」の場合(条件は仮に「＝」とする)
=QUERY(範囲,”select 列 where 列=”&セル&””)
「select～」全体を「”」ダブルクォ―テーションで囲っています。「＆セル＆」を「”」ダブルクォーテーションで囲っています。文字列であった最後の「’」シングルクォーテーションの囲いがない状態です。
基本
【サンプル】
※なんちゃって個人情報さんから頂いたサンプルですm(_ _)m
この元データを「範囲」に指定して、次の「表示用」シートのA1セルを参照セルとして、条件を入力、A3セルにQuery関数をいれていきます。
それでは早速いきます。
「D列：年齢」の条件をセル参照で指定して、抽出しましょう。(数値の場合なので2つ目の構文の方です)
「年齢が◯◯歳以上のデータを抽出」、◯◯をセル参照にします。
=QUERY(‘クエリ関数Data’!A1:H, “select * where D>=”&A1&””)
こんな感じになります。（条件の設定方法が分からない方は、シリーズの前の記事を参考にして下さい^_^）
「表示用」シートのA3セルに上の数式を入力すると。。。
エラー。
大丈夫です、まだ条件が入力されていないのでエラーになります。
それではA1セルに、「30」と数値で入力してみます。
はい。30歳以上のデータが抽出されましたね。
A1セルの数値を変更するだけで、抽出データを変更することが出来ます。
実行動画
このように条件をセル参照にすることで、データの検索機能が実装出来ます！
order by 抽出したデータを自動で並べ替える
ここまでは、抽出したデータは元の順番で該当するものだけが表示されていました。実務で使うときは、大きいもの順や小さいもの順など、抽出したデータが並べ替えられていて欲しいことが多いですよね。
クエリ関数でも、抽出したデータを並べ替えて表示させる機能がありますので紹介させていただきます！
構文：小さいもの順(昇順)
=QUERY(範囲,”select 列 order by 列 asc”)
構文：大きいもの順(降順)
=QUERY(範囲,”select 列 order by 列 desc”)
※昇順の場合の「asc」は省略できます。
一緒に抽出条件をつけることも可能です。
構文：条件付きで大きいもの順(降順) ※条件は仮に「>」
=QUERY(範囲,”select 列 where 列>条件 order by 列 desc”)
こんな感じですね。
サンプルで試してみましょう。
先程のサンプルの、「表示用」シートのA1セルに次の数式を入力します。
=QUERY(‘クエリ関数Data’!A1:H, “select A,C,D where C=’女’ order by D desc”)
「C列：性別」が「女」のA列、C列、D列のデータを抽出して、「D列：年齢」を「降順」で並べて表示する。ということです。
図のように、条件通り抽出されていることが分かります。
limit 抽出するデータの件数を指定する
「limit」を指定することで、抽出データの件数を指定することも可能です。
構文
=QUERY(範囲,”select 列 where 列>条件 order by 列 desc limit 件数”)
先程のサンプルで、抽出件数も指定してみましょう。
=QUERY(‘クエリ関数Data’!A1:H, “select A,C,D where C=’女’ order by D desc limit 10”)
「C列：性別」が「女」のA列、C列、D列のデータを抽出して、「D列：年齢」を「降順」で10件並べて表示する。ということです。
図のように、先程と条件は同じですが、10件だけ表示されていることが分かります。
offset 最初のn件は飛ばして指定の件数を抽出する
さらに、ややこしいですが「offset」を指定することで、最初のn件分のデータを飛ばして抽出することも可能です。
構文
=QUERY(範囲,”select 列 where 列>条件 order by 列 desc limit 件数 offset 飛ばす件数”)
いつ使うのか今の所想像が出来てませんが(笑)とにかくやってみましょう。
今回も先程のサンプルに「offset」を追加します。
先程のサンプルで、抽出件数も指定してみましょう。次の数式を入力します。
=QUERY(‘クエリ関数Data’!A1:H, “select A,C,D where C=’女’ order by D desc limit 10 offset 2”)
「C列：性別」が「女」のA列、C列、D列のデータを抽出して、「D列：年齢」を「降順」で最初の2件だけ飛ばして後の10件並べて表示する。ということです。
図のように、先程の上位の2つのデータを飛ばして抽出されました。
skipping 何件かおきに抽出する
「skipping」を指定することで、1つおきや2つおきなど、一定の間隔でデータを飛ばしながら抽出することが可能です。
構文
=QUERY(範囲,”select 列 where 列>条件 order by 列 desc skipping 抽出する間隔の件数 limit 件数”)
今回は「offset」のサンプルを「skipping」に置き換えてみましょう。
※リミットの位置に注意して下さい。
次の数式に変更します。
=QUERY(‘クエリ関数Data’!A1:H, “select A,C,D where C=’女’ order by D desc skipping 2 limit 10”)
「C列：性別」が「女」のA列、C列、D列のデータを抽出して、「D列：年齢」を「降順」で1件置きに後の10件並べて表示する。ということです。
図のように、元のデータと比べると、２件ごとのデータが抽出されているのが分かります。
※「skipping 1」がそのまま、「skipping 2」が1件空き、「skipping 3」が2件空きです。
まとめ
このように、クエリ式にはいろいろな条件を指定できます。組み合わせを使うことで様々な形のデータ抽出が可能になりますね^_^
次回は更にレベルアップして、クエリ関数を使ったデータ集計を紹介したいと思います！
コメント
ありがとうございます！
そう言っていただけると嬉しいです
これからもコツコツ頑張ります！！
Google検索から飛んできました！
offset関数について、
＞いつ使うのか今の所想像が出来てませんが
と言っておられるますがすごく助かりました！ありがとうございます！！
使用したのは社内で印刷してから回覧する「いただいたお中元（お歳暮）リスト」的なやつです。
①トランザクションデータ（過去うん年分のお中元データ）のシート
↓query関数
②今年のお中元データ
↓query関数
③今年のお中元データを印刷用に加工
という流れでquery関数で抽出していたのですが③印刷の際に30の倍数毎に印刷用紙を分けていたので
『=query(‘集計’!A3:J300,”select A,B,C… limit 30 offset “&(R2-1)*30,1)』
のような指定をすると用紙何枚目を抽出といったことができました。
まだまだペーパーで動いているうちみたいなとこだと貴重な情報でした♪
skippingの実用例説明が「「C列：性別」が「女」のA列、C列、D列のデータを抽出して、「D列：年齢」を「降順」で最初の2件だけ飛ばして後の10件並べて表示する。ということです。」となっていますが、「最初の2件だけ飛ばして」ではなく「１件ずつ飛ばして」等の間違いかと思われます。
コメントありがとうございます！
確かに間違ってました！修正させて頂きましたm(_ _)m
助かります！！