【スプレッドシート｜QUERY関数】参照範囲の指定方法まとめ

関数教室

QUERYは、指定した参照範囲から、指定した条件のセルを抽出できる関数です。

関数の仕組みを箱で例えており、「query」の中に「範囲（どこから）」と「列（何を）」を表す「select」が存在し、「select」の中に「行の条件（どのように）」を表す「where」が入っている図

条件の指定方法を学ぶと、様々な切り方で抽出できるようになりますが、そもそも参照範囲を自由に指定できなければ宝の持ち腐れです。

本記事では、参照範囲の指定方法に絞って、パターンごとのテンプレを用意しました。

体系化すると、以下のようになります。

QUERY関数で参照範囲を指定する方法についてパターン化した図
  • まずは、一つの範囲なのか、複数範囲を結合するのか
    • 複数の場合は、列をアルファベットではなく「Col1,Col2,…」のように列番号で指定する
  • 次に、作業しているスプレッドシートなのか、別のスプレッドシートなのか
    • スプシ外の場合は、IMPORTRANGE関数を組み合わせ、列も「Col」で指定する
  • 前者であれば、作業しているシートなのか、別のシートなのか
    • こちらはそれほど気にしなくてOK

それでは、難易度の低いものから順に掲載していくので、数式をコピペしてどんどん使っていきましょう！

【補足】シートに関数をコピーする方法
  • 貼り付けたいセルにカーソルを合わせて「fx」欄に貼り付け
    • キーボード「F2」ボタン（セル編集）を押してから貼り付けでも可
スプレッドシートに関数をコピペするには、対象セルにカーソルを合わせて「fx」欄に貼り付けることを説明したキャプチャ

同じスプシ内で一つの範囲を指定

QUERY関数で参照範囲を指定する方法についてパターン化した図

例として、読売ジャイアンツの2020年データを使用します。（プロ野球データFreak2020年データより）

読売ジャイアンツの選手データ
参照先データベース

データベースから「投手」だけを抜き出し、項目を守備・選手名・年俸（推定）の順番で並べたい時は、以下のようになります。

スプレッドシートでQUERY関数を使用して「投手」だけを抜き出し、項目を守備・選手名・年俸（推定）の順番で並べたキャプチャ

以下の文字列をコピーして使用して下さい。（緑文字を置き換え）

=query('シート名'!$この列から:$この列まで,"select 列①,列②,列③ where 検索列='検索条件'",見出し行番号)

Copy

QUERY関数の基本について、詳しい解説はこちらをご覧下さい。

同じスプシ内で複数の範囲を指定

例として、セ・リーグとパ・リーグの2020年データを使用します。（プロ野球データFreak2020年データより）

プロ野球「セ・リーグ」「パ・リーグ」それぞれの順位表
参照先データベース

シート内の範囲

QUERY関数で参照範囲を指定する方法についてパターン化した図

縦に結合

両リーグの計１２球団を、１つの表にまとめたいとします。

セ・リーグの下に続けてパ・リーグの球団が並ぶように、赤枠の範囲を縦に結合すると以下のようになります。

スプレッドシートでQUERY関数を使用して、セ・リーグの下に続けてパ・リーグの球団が並ぶように、それぞれの範囲を縦に結合したキャプチャ

=query({範囲始点①:範囲終点①;範囲始点②:範囲終点②},"select*")

Copy
  • 範囲全体を { } で囲む
  • 縦結合の場合は範囲同士を ; で繋ぐ

=query({範囲始点①:範囲終点①;範囲始点②:範囲終点②},"select Col1,Col2,Col3 where Col1<=3 order by Col2 desc")

Copy
  • 列はアルファベットではなく「Col1,Col2,…」のように「列番号」で指定する
    • シートではなく指定した参照範囲内での列番号
    • それぞれの範囲が同じ列数でなければエラーになる

横に結合

セ・リーグ６球団の順位表があり、勝敗と打撃成績でデータが分かれてしまっている場合。

プロ野球「セ・リーグ」の勝敗と打撃成績のデータが、スプレッドシートで縦に並んでいるキャプチャ

勝敗と打撃成績が並ぶように、赤枠の範囲を横に結合すると以下のようになります。

スプレッドシートでQUERY関数を使用して、勝敗と打撃成績が並ぶように、それぞれの範囲を横に結合したキャプチャ

=query({範囲始点①:範囲終点①,範囲始点②:範囲終点②},"select*")

Copy
  • 横結合の場合は範囲同士を , で繋ぐ

シート外の範囲

QUERY関数で参照範囲を指定する方法についてパターン化した図

セル指定

リーグごとにシートが分かれている場合。

参照先シート①「セ・リーグ」
参照先シート①「セ・リーグ」
参照先シート②「パ・リーグ」
参照先シート②「パ・リーグ」

セ・リーグの下に続けてパ・リーグの球団が並ぶように、赤枠の範囲を縦に結合すると以下のようになります。

スプレッドシートでQUERY関数を使用して、セ・リーグの下に続けてパ・リーグの球団が並ぶように、それぞれの範囲を縦に結合したキャプチャ

=query({'シート名①'!範囲始点:範囲終点;'シート名②'!範囲始点:範囲終点},"select*")

Copy
  • １つのスプレッドシート内であれば、同じ方法でシートまたぎの複数範囲も指定可能

列指定

先程の数式をセル指定から列指定に変更すると、以下のようになります。

なお、参照先シートの下側にある空白セルまで抽出されないよう、数式で除外します。

スプレッドシートでQUERY関数を使用して、セ・リーグの下に続けてパ・リーグの球団が並ぶように、それぞれの範囲を「列指定で」縦に結合した上で、空欄を除外したキャプチャ

=query({'シート名①'!$この列から:$この列まで;'シート名②'!$この列から:$この列まで},"select* where not Col1 is null")

Copy
  • 列指定にすることで、参照先シートに新しく追加された行も反映可能
  • 参照先シートの空白セルまで抽出されないよう「is null」を活用
    • 数式は「where Col1 is not null」でも可能

複数範囲の指定方法ついて、詳しい解説はこちらをご覧下さい。

別のスプシで範囲を指定

一つのスプシを指定

QUERY関数で参照範囲を指定する方法についてパターン化した図

=QUERY(IMPORTRANGE("https://●●","シート名!$この列から:$この列まで"),"select Col1 Col2 Col3")

Copy
  • 数式前半の範囲指定部分にIMPORTRANGE関数をそのまま置き換える
  • 参照先が別のスプレッドシートである場合、複数範囲でなくても列は「Col1,Col2,…」のように番号で指定する

複数のスプシを指定

QUERY関数で参照範囲を指定する方法についてパターン化した図

=QUERY({IMPORTRANGE("https://●●","シート名!$この列から:$この列まで");IMPORTRANGE("https://▲▲","シート名!$この列から:$この列まで")},"select Col1,Col2,Col3 where not Col1 is null")

Copy
  • 範囲指定の部分をIMPORTRANGE関数にそのまま置き換える（型は変わらない）
    • 範囲全体を { } で囲む
    • 縦結合の場合は範囲同士を ; で繋ぐ

別のスプレッドシートを指定する方法について、詳しい解説はこちらをご覧下さい。

以上、QUERY関数の参照範囲指定に関するまとめでした。

条件の指定方法に関するまとめは、こちらをご覧下さい。

タイトルとURLをコピーしました