スプレッドシートの使い方

【スプレッドシート】QUERY関数の参照にimportrangeを使う

query_importrange_example

スプレッドシートを使っていると、importrangeで他のシートを参照して、Query関数の条件で絞った出力が欲しい場合があると思います。

この記事では、Query関数内でimportrange関を使用する方法、またQuery関数内でimportrange関数を複数使う方法(複数のシートから参照する方法)を解説していきます。

Query関数内でimportrange関数を使う方法

query関数内でimportrange関数を使う場合には、下記のような表現になります。

=QUERY( IMPORTRANGE( "シートID" ,"参照範囲"), "条件")

参照としてimportrange関数を使用した場合に、条件の表現に注意

ここで注意したいのは、importrange関数で呼び出した場合、参照範囲が必ずしもA列からスタートしないため、条件の表記が普段と異なります。

普段のQuery関数なら、例えば空のセルを出力する場合に、次のような表記になりますが、

=QUERY(範囲, "where A = '' ")

importrange関数で参照した範囲の場合は、次のような表記になります。

=QUERY( IMPORTRANGE( "シートID" ,"参照範囲"), "where Col1 = '' ")

列の指定はCol1,Col2,Col3…という順番

Col0(ゼロ)から始まるのではなく、Col1から始まる点にも注意です。

query関数内でimportrange関数を使用する例

importrange関数で特定のシートから、シート名「Query」の範囲「B11~E17」を参照して、参照範囲の3列目(D列)が空の行を出力しようとすると、次のようなQuery関数の表記になります。

=QUERY(IMPORTRANGE("シートID","Query!B11:E17"),"where Col3 = '' ",true)

※シートIDはセキュリティのため伏せました
※trueは1行目を見出しにする命令です

具体的な例は下記の画像を参考にしてください。下記では、importrangeのシートIDに、このシート自体を指示しています。全く別のシートから参照する場合には、一度エラーが表示されて「アクセスを許可」を押すと表示されます。

QUERY関数内で複数のシートを参照する(複数のimportrange関数を使用する)方法

Aというシートと、Bというシートを参照してQUERY関数に渡すこともできます。その際には、次のような表現になります。

=QUERY({
IMPORTRANGE(シートURL,シート範囲);
IMPORTRANGE(シートURL,シート範囲)},
"WHERE 条件")

※わかりやすさのため改行しています

{}での範囲の結合方法など、基本的なシートの結合方法はこちらの記事が参考になります。

【スプレッドシート】QUERY関数で複数範囲・複数シートを結合する

QUERY関数の使い方」まとめ
QUERY関数の使い方使用頻度使用例
オプションOrder byで昇順・降順を指定する★★QUERY("範囲" "order by 列 asc")
オプションgroup byで同じ値をグルーピングして集計する★★★QUERY(範囲,"select 列A, 集計関数(列B) group by 列A")
オプションpivotを使って、group byをさらにグルーピングして表示するQUERY(範囲, "select 列A, 集計関数(列A) where group by 列A pivot 列B")
オプションlabelを使って、出力される列のラベルを変更するQUERY(範囲, "label 列 '名前' ")
オプションformatを使って、出力される値の表示形式を変更するQUERY(範囲, "format 列 '表示形式' ")
オプションlimitを使って、表示する行数に制限をかけるQUERY(範囲, "limit 数")
オプションoffsetを使って表示データを上からスキップする方法QUERY(範囲, "offset 数")
オプションoptionsを使う
演算子containsを使って、指定文字列を含む行を表示する★★QUERY(範囲, "where 列 contains '検索文字列' "
演算子starts withを使って、行の先頭文字列に条件を指定するQUERY(範囲, "where 列 starts with '検索文字列' "
演算子ends withを使って、行の後尾文字列に条件を指定するQUERY(範囲, "where 列 ends with '検索文字列' "
演算子likeで複雑な条件を指定するQUERY(範囲,"where 列 like '検索文字列' ")
演算子★★★QUERY(範囲,"WHERE 列 matches '正規表現' ")
テクニックwhereの条件で空白を指定・除外する★★QUERY(範囲, "where 列 is not null")
テクニックQUERY関数にSUM関数を使う★★SUM(QUERY(範囲, "where 条件")
テクニックwhereの条件に日付を指定する★★★QUERY(範囲,"where 列 = date '"& TEXT(セル,"YYYY-MM-DD") &"'")
テクニックQUERY関数内でimportrange関数を使って、別シートを参照する★★★QUERY(importrange(シートID,参照範囲), "where 条件")
テクニック複数範囲・複数シートを結合する★★QUERY({IMPORTRANGE(シートURL,シート範囲);IMPORTRANGE(シートURL,シート範囲)},"WHERE 条件")
テクニックwhereの条件を複数指定する★★★QUERY("範囲", "where 条件A or 条件B and 条件C")