QUERYは、指定した参照範囲から、指定した条件のセルを抽出できる関数です。
![関数の仕組みを箱で例えており、「query」の中に「範囲(どこから)」と「列(何を)」を表す「select」が存在し、「select」の中に「行の条件(どのように)」を表す「where」が入っている図](https://pumpuppineapple.com/wp-content/uploads/2021/05/Screenshot-2021-05-01-15.55.15-1024x328.png)
条件の指定方法を学ぶと、様々な切り方で抽出できるようになりますが、そもそも参照範囲を自由に指定できなければ宝の持ち腐れです。
本記事では、参照範囲の指定方法に絞って、パターンごとのテンプレを用意しました。
体系化すると、以下のようになります。
![QUERY関数で参照範囲を指定する方法についてパターン化した図](https://pumpuppineapple.com/wp-content/uploads/2021/09/image.png)
- まずは、一つの範囲なのか、複数範囲を結合するのか
- 複数の場合は、列をアルファベットではなく「Col1,Col2,…」のように列番号で指定する
- 次に、作業しているスプレッドシートなのか、別のスプレッドシートなのか
- スプシ外の場合は、IMPORTRANGE関数を組み合わせ、列も「Col」で指定する
- 前者であれば、作業しているシートなのか、別のシートなのか
- こちらはそれほど気にしなくてOK
それでは、難易度の低いものから順に掲載していくので、数式をコピペしてどんどん使っていきましょう!
- 貼り付けたいセルにカーソルを合わせて「fx」欄に貼り付け
- キーボード「F2」ボタン(セル編集)を押してから貼り付けでも可
![スプレッドシートに関数をコピペするには、対象セルにカーソルを合わせて「fx」欄に貼り付けることを説明したキャプチャ](https://pumpuppineapple.com/wp-content/uploads/2021/05/Screenshot-2021-05-08-15.20.25.png)
同じスプシ内で一つの範囲を指定
![QUERY関数で参照範囲を指定する方法についてパターン化した図](https://pumpuppineapple.com/wp-content/uploads/2021/09/image-1.png)
例として、読売ジャイアンツの2020年データを使用します。(プロ野球データFreak2020年データより)
![読売ジャイアンツの選手データ](https://pumpuppineapple.com/wp-content/uploads/2021/04/Screenshot-2021-04-29-16.21.12-1024x518.png)
データベースから「投手」だけを抜き出し、項目を守備・選手名・年俸(推定)の順番で並べたい時は、以下のようになります。
![スプレッドシートでQUERY関数を使用して「投手」だけを抜き出し、項目を守備・選手名・年俸(推定)の順番で並べたキャプチャ](https://pumpuppineapple.com/wp-content/uploads/2021/05/Screenshot-2021-05-03-18.59.36.png)
以下の文字列をコピーして使用して下さい。(緑文字を置き換え)
=query('シート名'!$この列から:$この列まで,"select 列①,列②,列③ where 検索列='検索条件'",見出し行番号)
QUERY関数の基本について、詳しい解説はこちらをご覧下さい。
同じスプシ内で複数の範囲を指定
例として、セ・リーグとパ・リーグの2020年データを使用します。(プロ野球データFreak2020年データより)
![プロ野球「セ・リーグ」「パ・リーグ」それぞれの順位表](https://pumpuppineapple.com/wp-content/uploads/2021/07/image-11.png)
シート内の範囲
![QUERY関数で参照範囲を指定する方法についてパターン化した図](https://pumpuppineapple.com/wp-content/uploads/2021/09/image-3.png)
縦に結合
両リーグの計12球団を、1つの表にまとめたいとします。
セ・リーグの下に続けてパ・リーグの球団が並ぶように、赤枠の範囲を縦に結合すると以下のようになります。
![スプレッドシートでQUERY関数を使用して、セ・リーグの下に続けてパ・リーグの球団が並ぶように、それぞれの範囲を縦に結合したキャプチャ](https://pumpuppineapple.com/wp-content/uploads/2021/07/image-10.png)
=query({範囲始点①:範囲終点①;範囲始点②:範囲終点②},"select*")
- 範囲全体を { } で囲む
- 縦結合の場合は範囲同士を ; で繋ぐ
=query({範囲始点①:範囲終点①;範囲始点②:範囲終点②},"select Col1,Col2,Col3 where Col1<=3 order by Col2 desc")
- 列はアルファベットではなく「Col1,Col2,…」のように「列番号」で指定する
- シートではなく指定した参照範囲内での列番号
- それぞれの範囲が同じ列数でなければエラーになる
横に結合
セ・リーグ6球団の順位表があり、勝敗と打撃成績でデータが分かれてしまっている場合。
![プロ野球「セ・リーグ」の勝敗と打撃成績のデータが、スプレッドシートで縦に並んでいるキャプチャ](https://pumpuppineapple.com/wp-content/uploads/2021/07/image-6.png)
勝敗と打撃成績が並ぶように、赤枠の範囲を横に結合すると以下のようになります。
![スプレッドシートでQUERY関数を使用して、勝敗と打撃成績が並ぶように、それぞれの範囲を横に結合したキャプチャ](https://pumpuppineapple.com/wp-content/uploads/2021/07/image-7-1024x499.png)
=query({範囲始点①:範囲終点①,範囲始点②:範囲終点②},"select*")
- 横結合の場合は範囲同士を , で繋ぐ
シート外の範囲
![QUERY関数で参照範囲を指定する方法についてパターン化した図](https://pumpuppineapple.com/wp-content/uploads/2021/09/image-4.png)
セル指定
リーグごとにシートが分かれている場合。
![参照先シート①「セ・リーグ」](https://pumpuppineapple.com/wp-content/uploads/2021/07/image-15-1024x280.png)
![参照先シート②「パ・リーグ」](https://pumpuppineapple.com/wp-content/uploads/2021/07/image-16-1024x280.png)
セ・リーグの下に続けてパ・リーグの球団が並ぶように、赤枠の範囲を縦に結合すると以下のようになります。
![スプレッドシートでQUERY関数を使用して、セ・リーグの下に続けてパ・リーグの球団が並ぶように、それぞれの範囲を縦に結合したキャプチャ](https://pumpuppineapple.com/wp-content/uploads/2021/07/image-17-1024x412.png)
=query({'シート名①'!範囲始点:範囲終点;'シート名②'!範囲始点:範囲終点},"select*")
- 1つのスプレッドシート内であれば、同じ方法でシートまたぎの複数範囲も指定可能
列指定
先程の数式をセル指定から列指定に変更すると、以下のようになります。
なお、参照先シートの下側にある空白セルまで抽出されないよう、数式で除外します。
![スプレッドシートでQUERY関数を使用して、セ・リーグの下に続けてパ・リーグの球団が並ぶように、それぞれの範囲を「列指定で」縦に結合した上で、空欄を除外したキャプチャ](https://pumpuppineapple.com/wp-content/uploads/2021/07/image-20.png)
=query({'シート名①'!$この列から:$この列まで;'シート名②'!$この列から:$この列まで},"select* where not Col1 is null")
- 列指定にすることで、参照先シートに新しく追加された行も反映可能
- 参照先シートの空白セルまで抽出されないよう「is null」を活用
- 数式は「where Col1 is not null」でも可能
複数範囲の指定方法ついて、詳しい解説はこちらをご覧下さい。
別のスプシで範囲を指定
一つのスプシを指定
![QUERY関数で参照範囲を指定する方法についてパターン化した図](https://pumpuppineapple.com/wp-content/uploads/2021/09/image-5.png)
=QUERY(IMPORTRANGE("https://●●","シート名!$この列から:$この列まで"),"select Col1 Col2 Col3")
- 数式前半の範囲指定部分にIMPORTRANGE関数をそのまま置き換える
- 参照先が別のスプレッドシートである場合、複数範囲でなくても列は「Col1,Col2,…」のように番号で指定する
複数のスプシを指定
![QUERY関数で参照範囲を指定する方法についてパターン化した図](https://pumpuppineapple.com/wp-content/uploads/2021/09/image-6.png)
=QUERY({IMPORTRANGE("https://●●","シート名!$この列から:$この列まで");IMPORTRANGE("https://▲▲","シート名!$この列から:$この列まで")},"select Col1,Col2,Col3 where not Col1 is null")
- 範囲指定の部分をIMPORTRANGE関数にそのまま置き換える(型は変わらない)
- 範囲全体を { } で囲む
- 縦結合の場合は範囲同士を ; で繋ぐ
別のスプレッドシートを指定する方法について、詳しい解説はこちらをご覧下さい。
以上、QUERY関数の参照範囲指定に関するまとめでした。
条件の指定方法に関するまとめは、こちらをご覧下さい。