Googleスプレッドシート QUERY関数 超応用例 2（第1引数 データの挙動を理解する）

Googleスプレッドシートの最強関数 QUERY関数について書いたnoteの第2回です。

前回は使う上での注意点と、とりあえず使いたい人向けの基本となる使い方について書きました。

また、QUERY関数は長くなりそうなんで、単独で「【基本から裏技、超応用】どこよりも詳しい 超 QUERY関数」としてマガジンとしてまとめまています。

きっと、どこよりも詳しいはず！（役に立つとは言ってない）

今回からディープな考察に入っていきます。（超応用例は今回はありません）

QUERY関数の第1引数を完全理解する

なんと今回は

QUERY関数の 3つの引数の1番目、第1引数のデータについてのみ掘り下げます。

普通は第2引数のクエリがメインですが、第1引数も奥の深い独特の挙動があるんで、これだけでも書くことが結構あるんです。（役に立つかは別としてｗ）

QUERY関数はリスト表（データベース形式）のデータに対して使う関数

QUERY関数の第1引数のデータは、基本的に 👇 のような リスト型のデータを対象とします。

• 1行に1データ

• 1つのセルには1つの値が入っている

• 列毎のデータの型が統一されている

QUERY関数で扱うデータ（第1引数）は、このようになっていることが基本です。

ですから、QUERY関数は Googleスプレッドシートの「テーブル機能」と非常に相性が良いと言えます。

これが同じリスト表形式であっても

このような1つのセルに複数の情報が入っていたり、列内で型が揃っていないような表だったり

既に集計されたクロス表（縦横のマトリックスになっている表）の場合は

QUERY関数で扱うのは難しい（QUERY関数を使うケースではない）と言ってよいでしょう。

QUERY関数は第1引数のみでも使える スピルする関数

Googleスプレッドシートはスピル対応のExcelと違って、単純にセル範囲（複数セル）をイコールで参照しても 結果が展開されません。（スピルしません）

=A2:C10　とセルに入れた場合、 #VALUE!エラーが返ります。

これを回避する為には、スピル効果を付与する関数を組み合わせる必要があります。

一般的にはARRAYFORMULA関数を使うことが多いですが

同様の効果を持つ INDEX関数を使ったり

中カッコ { } で括ることで配列化させて展開する方法もあります。

QUERY関数もスピルする関数（結果が配列として展開される関数）の一つです。

このようにQUERY関数は第1引数のみで利用することが出来ます。

一見すると ARRAYFORMULAやINDEXと同じように使えると勘違いしてしまうかもしれませんが、QUERYはスピルする関数ではありますが、出力される結果は参照しているデータそのままではありません。

前回紹介した 列ごとのデータ型に沿わない異物を排除（空白化）する以外にも、非常に挙動にクセがあるので注意が必要です。

QUERY関数の第1引数 代表的パターン

まず第1引数としてとれるデータはどんなものがあるか？を見ていきましょう。

公式には「クエリを実行するセル範囲です」とありますが、もちろん「配列」を指定することも可能です。

代表的なケースを挙げていきます。

※わかりやすいように 第1引数のみでQUERY関数を使っていますが、本来は第2引数のクエリと組み合わせて利用するものです。このように参照するだけの場合、QUERY関数を使う必要はありません。

同一シート内のセル範囲【範囲】

まずは基本ですが、同一シート内のセル範囲を指定できます。

Googleスプレッドシートの場合はお尻を指定した A1:C10のような開始行と最終行を指定した範囲だけではなく、A:Cといった指定や Excelには出来ない A2:C（開始の行は指定しつつ最終行は指定しない）が出来るのが便利です。

同一スプレッドシート内の別シートのセル範囲【範囲】

同じスプレッドシート内（ブック内）の別シートを指定するケースもあるあるですね。

テーブル（同一スプレッドシート内）【範囲】

前述しましたが、QUERY関数は データベース形式となっているテーブルとの相性が良いです。

もちろんQUERY関数でも第1引数は構造化参照による指定が可能で、ヘッダ行含めて取得する場合は

このように指定できます。

ヘッダ行を除いたデータ範囲であれば

さらにシンプルな、このような指定となります。

IMPORTRANGEで取得した 別スプレッドシートのセル範囲【配列】

Googleスプレッドシートで別のスプレッドシートを参照する際に使うIMPORTRANGE関数。

このIMPORTRANGE関数もQUERY関数と非常に相性のいい関数です。

さらに IMPORTRANGE関数は、Googleスプレッドシートの名前付き範囲やテーブルと組み合わせると便利です。

たとえば

SSIDという名前を付けたセルに 参照するスプレッドシートのIDを入れ、通常はIMPORTRANGE関数の第2引数でシート名!セル範囲を 文字列で指定するところを テーブル名で指定すると、このような記述ができます。

もちろん普通にシートIDとシート名、セル範囲を指定する

このような書き方でもOKです。

複数のテーブル（範囲）を結合したデータ【配列】

ピボットテーブルでは対応できない複数テーブルを統合した集計が出来るのがQUERY関数のメリットです。

例えば複数のテーブルを {} 中カッコと ;（セミコロン）で縦に連結して

このような複数テーブルを統合したデータに対して、QUERY関数による集計が可能です。

もちろん普通に複数シートのセル範囲を対象とした👇このような式や

中カッコによる配列連結ではなく、VSTACK関数を使った結合👇でもOK

また、バーチャルな列を横に連結させるケースを対象データとしても問題ありません。

いずれのケースも結合面（縦連結の場合は列数、横連結の場合は行数）のサイズを揃えることを意識しましょう。

セル範囲を数式で加工したデータ【配列】

第1引数にセル範囲を加工したデータを渡すケースもあります。

上の式は空白を0で埋めるという式。

ここでの注意点は前回も触れましたが、QUERY関数はスピルする関数だけど第1引数内に配列処理効果が付与される関数では無いという点。

その為、第1引数に配列処理をする数式を指定した場合は ARRAYFORMULAで括る必要があります。

ARRAYFORMULAは一番外側につけておきましょう。

QUERY関数は第1引数も省略（空白指定）できる【例外】

余談となりますが、QUERY関数の第1引数は単体の文字列や空文字の指定も可能です。

また=QUERY()　はエラーとなりますが、　=QUERY(,) とした場合はエラーにならず空白を返します。

つまりQUERY関数は第1引数無し（空白）として、第2引数だけで使うことも出来るってことです。

ここで紹介した第1引数に指定する代表的なケースで、【範囲】としているものはセル範囲を参照したデータで、【配列】としたものは 配列データです。

今回は触れませんが、第2引数の select 句でこの2つの違いを意識する必要があります。（説明は select句の時に書きます）

QUERY関数の第1引数 型を理解する

上でも書きましたが、QUERY関数は他のシート関数と比べデータの型という点で非常にクセのある関数です。

第1引数で扱えるデータもデータベース的思想の制限があり、公式にも

と記載があります。

この制限について詳しく見ていきましょう。

QUERY関数の第1引数（データ）で扱える型

QUERY関数で扱えるデータ型は

これらであると記載があります。

ただ実はGoogleスプレッドシートのQUERY関数ではもう一つ例外的に扱えるものがあります！

QUERY関数は スマートチップを返すことができる

QUERY関数が扱える特殊なデータ型（型といっていいのか微妙ですが）、それがスマートチップです。

👆の画像を見てください。上のデータをQUERY関数で出力したものが下のデータなんですが、C列のチェックボックスは形式が失われて ブール値（Boolean型）に変換されています。

また、一番右のハイパーリンクテキストも QUERY関数を通すと失われ、普通の文字列となっているのがわかります。

しかし、B列のスマートチップはそのままチップとして、チップの機能を保持してQUERY関数が結果を返せているのがわかります。

残念ながら評価チップとプルダウンチップは 機能が失われましたが、それ以外のチップは QUERY関数で扱うことが出来るのは便利ですね！

つまり スマートチップもQUERY関数と相性が良いってことです！

ちなみにIMPORTRANGEで他のスプレッドシートからデータを取得した場合は、

チップは扱えずず、ハイパーリンクは扱えるという仕様になっています。

QUERY関数の第1引数（データ）で扱えない型

では逆に、QUERY関数で扱えない型はどんなものがあるか？

型と言ってよいかわかりませんが、他の多くのシート関数では扱えるけどQUERY関数で扱えない代表的な例は

これらです。

まずセル内画像に関しては QUERY関数で扱うことは出来ません。セル内画像は全て空白が返ります。

これは直接挿入したセル内画像、IMAGE関数、IMAGE関数値コピー画像、いずれも同じ扱いです。

エラー値に関しては そのまま返している（扱えている）ように見えますが、これはエラー値が文字列化したものです。

ISERROR関数で判定するとわかりますが

元のデータは TRUE（エラーである）と返っているのに対して、QUERY関数出力後は FALSE（エラーではない）となっているのがわかりますね。

ハイパーリンクに関してはスマートチップの時にも触れましたが

このようにQUERY関数を通すとリンク機能が失われてしまいます。

ただし、URL文字列の場合はセルに書き出された時に自動でハイパーリンクとなります。

QUERY関数でリンクを扱いたい場合は、ハイパーリンクではなくGoogleドライブ内のファイルやYoutubeならチップとする、それ以外はタイトルとURLを列を分けて記述するとしましょう。

そして最後、「空文字」もQUERY関数では扱えません。

ExcelやGoogleスプレッドシートは空文字と空白は別モノなんですが、QUERY関数を通すと空文字は空白となります。

ISBLANK関数で空白判定した場合、下のQUERY関数の結果の方は 文字が入っている行以外は全て TRUE（空白である）となっているのが分かりますね。

またTOCOL関数の第2引数を1指定で空白を無視とした際の挙動も、このようにQUERY関数の出力結果は全て空白扱いなので元データの時と違う結果が返ります。

実用機会は無いかもしれませんが、空文字と空白が混在したデータに対して空文字を空白に揃えたい！なんて時のデータ精査にQUERY関数が使えるかもしれません。

セル内画像などQUERYで扱えないデータを処理する場合はFILTER関数を使おう

セル内画像やハイパーリンクなど、QUERY関数で扱えないデータの場合は FILTER関数やSORT関数を使いましょう。

これらの関数であれば、セル内画像やハイパーリンク、さらにエラーや空文字をそのまま返すことができます。

ほんとQUERY関数は一般のシート関数とは別モノなんですね。

QUERY関数の第1引数 型の優先度を理解する

QUERY関数で扱えるデータの型、扱えないデータの型は理解出来ました。

それでは

前回も注意点として紹介した QUERY関数が、各列において多数派のデータを元にその列の型を自動で定義し、その列に含まれる型の合わない小数派データを無視する仕様について、もう少し掘り下げていきましょう。

QUERY関数の列の型は一つでも多い要素に切り替わる

QUERY関数はどのように列の型を自動判別して定義しているか？

基本的にはデータの順番は関係なく、型の個数が多い方が勝つ仕様となっています。

たとえば上の動画の例だと

文字列10、数値9　の場合は 文字列型
　→ 小数派の数値が 文字列の数字になる

文字列9、数値10　の場合は 数値型
　→ 小数派の文字列が Null（空白）となる

となっているのが、わかります。

QUERY関数の型には優先度（強さ）がある

では列内のデータの型が同じ数だったらどうなるか？

このように並び順（どちらが上にあるか）は関係なく、文字列と数値の型が同数の場合、数値が優先されました。

つまり、型が同数の時にどちらが勝つか？は、型の優位性（序列）で決まるということです。

念のため、もう少し母数が多いケースも確認しておきましょう。

このように 

データの数が多くても、数の多さで列の型は決まる、同数の場合は型の優位性（序列）で決まるというルールになっています。

QUERY関数の型で最強はブール値（ランキング発表）

では列の中で同数だった場合、最も強い型はなんなのか？

「俺より強い型に会いにいく！」

ってことで、総当たり比較してみたところ・・・

最強は ブール値（TRUE,FALSE)となりました！

以降の順位は

このようになっています。文字列より時刻が下（最弱）だったのは意外です。

QUERY関数で日付と日時は連合を組む

型の優先度は、1対1での型の戦いでは ブール値が最強という検証結果が得られました。

しかし、実は 日付と日時は 近い型ということもあって、列に両方が混在した場合に連合を組むという特殊な挙動をします。

👆こちらを見てください。 最初 A列は 日付、日時、文字列が、それぞれ3つずつと同数なので、優先度 序列2位の 日付が 優先され、QUERY関数の結果は日付型となっています。

しかし、文字列型のデータを1つ増やして 日付3、日時3、文字列4 となった途端、QUERYは文字列型になるかと思いきや、なんと 日付型が 日時に寝返り、日付と日時が 日時型連合を形成し 文字列を排除しています。

日付と日時を合わせたものと同数の 6まで文字列データが増えると、ようやく文字列が型を奪うことが出来ます。

ちなみに 文字列でなく ブール値が 同数の場合は、

序列が日付より上のブール値の方が勝つので、この時点で日付＆日時連合が形成されます。

連合を組めるのは 日付型と日時型だけで、日時と時刻 や 日付と時刻 では連合になりません。

ちょっと不思議なQUERY関数の列のデータ型における、日付と日時がタッグを組むという挙動を紹介しました。

QUERY関数で型と合わない小数派データの末路

型の考察の最後は、列の型と適合しないデータがどうなるか？を見ていきましょう。

公式には NULL値（スプレッドシートの場合は空白と同義）と見なされるとありますが、実は型によってこの挙動も違いがあります。

もっとも恐ろしいのは 序列1位、最強の型であるブール値が支配する世界。

他の全ての型は存在を許されず 全てNull（空白）となります。

逆にもっとも 緩い平和な世界は、列が文字列型と判定された世界です。

数値やブール値、日付、日時、時刻は、全て文字列として生き残ることができます。

TO_TEXT関数を使った時と同じイメージですね。

数値、日付、日時、時刻 の型と判断された場合は、 お互いに変換できるのもあり、この4つはその型に形を変えて残り、ブール値と文字列は空白となります。

ちなみに 時刻の型となった場合は、中身のデータは 日時のまま残りますが

日付の型と判定された場合の日時や時刻は、QUERY関数の出力で時間の情報を失います。

とりあえず 日付、日時、時刻が 一つの列に混在したデータもQUERY関数使う際は注意ってことですね。

ぶっちゃけあまり役に立たない情報だと思いますが、他のサイトであまり見かけないQUERY関数のデータ型判定についてのディープな検証でした。

QUERY関数の第1引数 表示形式の挙動を理解する

えっ、今回は本当にQUERY関数の第1引数だけで終わるの？？

本当です。今週は第1引数だけですｗ

型だけでなくQUERY関数は、表示形式に関しても独特な挙動があるので、そちらを紹介して終わりとしましょう。

ちなみにExcelと違ってGoogleスプレッドシートは 表示形式込みで（表示形式を適用した結果に対して）数式や関数で処理ができたりします。

その辺りの話は 👇のnoteで書いています。

QUERY関数は 文字列の表示形式は無視される

たとえば、標準の数値形式や文字列に対するカスタム数値形式を適用させたセル範囲に対してQUERY関数を使うとどうなるか？

👆このようになります。

数値に対する表示形式はそのままQUERY関数で取得できますが、文字列に対する表示形式 （A列の @様 ） は解除されます。

つまり、QUERY関数は

• 数値の表示形式（日付、日時含む）は考慮される

• 文字列の表示形式 は無視される

ということです。

QUERY関数は 表示形式が混在する場合は 先頭のデータが優先

では、1つの列に数値の表示形式が混在する場合はどうなるか？

その場合、このように表示形式が列単位で統一されます。

この列毎の表示形式を決めるルールですが、データの型と違って多数派で決まるわけではありません。

データの先頭行の表示形式に全て揃えられる、という挙動になっています。

というわけで元データの先頭行の表示形式を変えると

このようにQUERY関数の出力結果は、列単位でまるっと表示形式が変わります。

ちなみにこれ、出力されるセルに表示形式を設定した場合は、そちらが優先されます。

QUERY関数を使って列毎の元データの最初の行の表示形式が影響するのは、出力先のセルの表示形式が「自動」になっている時だけです。

ま、通常は 列内で表示形式が混在することはあまり無いですし、今後登場するQUERY関数のlabel句で表示形式は制御できるんで、ぶっちゃけこの情報もたいして重要ではありません。

ただ、データの型だけでなく表示形式に関してもQUERY関数は独特の挙動があることは知っておいて損はないです。

表示形式の混在データをそのまま抽出したい場合はFILTER関数

このような 文字列の表示形式を適用したデータや 列単位で 表示形式が混在しているデータを、そのまま扱いたい、条件で抽出した結果が元の表示形式そのまま出力したい場合は、やはり QUERY関数ではなく FILTER関数やSORT関数を使うと良いです。

色々書きましたが、とにかくQUERY関数の第1引数のデータは 型や表示形式が列単位で揃っている綺麗なデータであることが大事ってことです。

QUERY関数、次回はいよいよクエリへ

今回はQUERY関数の第1引数を徹底的に掘り下げてみました。

QUERY関数を普通に使う分には、ここまで知らなくてもいいネタばかりですが、型を意識することはデータベース的な考え方の第一歩なんで無駄にはなりません。

QUERY関数のクエリ句を使いこなす上でも、型や表示形式を意識する機会は多々あるので、是非頭の片隅にでも入れておいてください。

次回、いよいよ 第2引数 クエリへ。まずは select 句の基本、そして超応用例もいよいよ登場！

とりあえずはクエリの概要から。公式サイトに非掲載の情報も！！