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

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

スプレッドシート_配列の結合

スプレッドシートでの情報操作に便利なQUERY関数。ただ、複数範囲を結合してQUERY関数で扱いたい場合に、エラーが出て思ったように処理できない方が多いと思います。QUERY関数の場合、そのときの範囲の性質によって、条件での列の指定の仕方が変わるため、少しやっかいです。

この記事では、結合した範囲をQUERY関数で処理する方法、結合したQUERY関数を範囲としてQUERY関数で処理を行う方法を解説します。画像と例を付けているので、分からなかったら手を動かしてみましょう。

単純な範囲の結合方法

単純に範囲Aの下に範囲Bを結合する場合、下記のような表現をします。

={範囲A;範囲B}

範囲の結合例

={B3:E8;G3:J8}

結合した範囲に対してQUERY関数を使う方法

結合した範囲に対してQUERY関数を使う場合には注意が必要です。

普段のQUERY関数なら列の指定を「SELECT A,B」や、条件の指定を「WHERE A=3」のように使っていますが、結合した範囲に対してはそのような表記をするとエラーが表示されます。

結合した範囲に対するQUERY関数では「Col1」「Col2」…を使う

結合した範囲の場合、参照している範囲が、A列だけ、B列だけなわけではないので、「Col1(=範囲1列目)」、「Col2(=範囲の2列目)」のような表記を使用します。

結合した範囲に対するQUERY関数例

例えばAとBの範囲を結合した範囲に対して、3列目が転職の行だけを表示するQUERY関数は次のようになります。

=QUERY({B3:E8;G3:J8},"WHERE Col3 = '転職'")

※1列目は「Col0(ゼロ)」ではなく「Col1」だということに注意して番号を振りましょう。

結合した複数のQUERY関数を範囲としたQUERY関数の使い方

言葉にするとややこしいですが、やりたいことは、下記のようなQUERY関数による処理です。

=QUERY({
QUERY(範囲A,条件A);
QUERY(範囲B,条件B)},
条件C)

※見やすさのために改行

結合した複数のQUERY関数を範囲としたQUERY関数例

Aの就活カテゴリ、Bの転職カテゴリを結合して、結合した範囲でPVが100より大きいものを表示する関数式。

=QUERY({
QUERY(B3:E8,"WHERE D ='就活'");
QUERY(G3:J8,"WHERE I='転職'")},
"WHERE Col4 > 100")

※見やすさのために改行

IMPORTRANGE関数とQUERY関数で、複数の別シートの範囲を結合する方法

複数のIMPORTRANGE関数とQUERY関数を組み合わせることで、別シートに存在する配列を結合して処理することができます。

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

※見やすさのために改行

上記のようにIMPORTRANGE関数とQUERY関数を 使用します。

複数の別シートの範囲を結合する例

=QUERY({
IMPORTRANGE("シートURL","シート115!B3:E8");
IMPORTRANGE("シートURL","シート115!G3:J8")},
"WHERE Col3='転職'")

※見やすさのために改行
※今回は「シートURL」は伏せ字として使用しています。

ここでもやはり、QUERY関数内でIMPORTRANGE関数を使用した場合、QUERY関数の条件では「Col数字」の表記で指定します。

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")