スプレッドシートQuery関数学習シリーズ⑨エラー対策

読んで欲しい人
Query関数（クエリ関数）シリーズ予定　（あくまで予定）
Query関数（クエリ関数）でよく起きる想定外動作の例
1. Query関数：よくあるエラーリスト
まとめ
1. 【レクチャー関係紹介】

読んで欲しい人

SQLに興味があるが、一から環境やデータ整備するのがめんどくさい人
vlookupとかでデータ抽出しているが、限界を感じている人
データ分析したい人
ノンプログラマーでもクエリぐらいは使いたいって人
クエリって美味しいの？って人（クエリは美味しいですよって意味も込めて）

Query関数（クエリ関数）シリーズ予定　（あくまで予定）

概要と目的
クエリ関数の構成
基本の基本　SELECT句（全部取得、特定カラムだけ取得）
抽出の基本　WHERE句を使ってみる(数値一致、大小、日付一致、大小etc）
GROUP　BY句を使ってみる
他のシートからデータを持ってきてみよう〜 ImportRange関数との組み合わせ〜
並び替えの基本　ORDER BY句
SQLやるなら必須　サブクエリをやってみる
データに改行が入ってるぞ　likeで対応できない時はcontains/starts withが助けてくれる←今回

Query関数（クエリ関数）でよく起きる想定外動作の例

さてこれまでクエリ関数の使い方をみてきました。WHEREだとかGroup BYとか色々勉強しました。サブクエリやデータ結合系の処理もありました。忘れてる機能がある方は是非過去回を見直してみてください。

今回は少し毛色が異なるのですが、実際実務で当たっていると起きるクエリ関数がイメージした挙動をしない場合とその対応策です。

結論ほとんどのイメージ外の挙動はデータ構成が悪影響して、データがブランクになるという物です。よく起きる例を３つほどみていきましょう。

Query関数：よくあるエラーリスト

データに改行が入っていてうまく検索されない
カラムデータが１つだけ別物になっていてうまく検索されない
セルが結合されている

データに改行が入っている場合とその対応策

さて、こんなデータがあったとします。さて、TOMのデータが探したいなとなったとします。

さて、TOMのデータが探したいなとなったとします。名前でTOMを検索すれば出てくるはずですよね。

=query(データ範囲, "select * where A = 'TOM'")

あれ、何も返ってこないぞ？ってなりませんか？

=query(データ範囲, “select * where A = ‘TOM'”)の結果

なぜでしょう？一旦原因を確認するには、全て表示してみるのが早いです。そうすると、おかしいなぁやっぱりTOMはいますね？

ちょっと待ってください、TOMのデータみてみると、名前のカラム改行されてaaaが入っていますね。あぁそうかTOMだけじゃないからダメだったのかLike使って前方一致で検索すればいいのかってなりませんか？

=query(データ範囲,"select * where A like 'TOM%'")

実はこれをやっても何も返ってきません。

問題は一致かLikeかというところではなく、改行が含まれているからなのです。

改行が含まれてしまっているデータはcontainsかstarts with/ends with

改行が含まれている場合、改行をCHARで記号してって方法もあるのですが、もっと簡単にできるのが、containsです。TOMが含まれていれば検索されます。

containsで検索した場合は、改行が含まれていても、containsで指定したデータが含まれていれば対応可能です。また、同様のことはstarts with、名前がTOMで始まっているということが確定している場合、でも対応可能です。

contains/starts withなどを使う場合の留意点

containsを使う場合の留意点としては、名前がA”TOM”だとか、”TOM”THONみたいな人間もデータに含まれている場合、抽出してきてしまいます。contains(含む）は便利ですが、当然キレイに一致しないものも入ってくることにご留意ください。

じゃぁstarts withならいいのかというと、A”TOM”はstarts withでは含まれてきませんが、”TOM”THONは含まれてきてしまいます。

containsやstarts withを使う場合は、データにスペシャルマーク（★とか！みたいな記号）が入ってデータを識別できる場合がいいですね。

ただし、そもそもそんなデータを作らずに、ステータスごとにカラムを分けて特別なフラグを立てることをお勧めします。汚いデータはquery関数には敵です。

カラムデータが１つだけ別物になっていてうまく検索されない

さて先ほどと同じデータでまたみていきます。

今度は年齢で検索しようとしてみます。Benの年齢はいくつだったかなぁ。

=query(データ範囲,"select * where A = 'Ben'")

あれ、Benの年齢がブランクだ。。

オリジナルをみてみると、実はBenのデータってaaaとなっていて、ageカラムは他は全て数値なのに、Benだけ文字列が入ってきてしまっていますね。

だったら、aaaって表示されるんじゃないかと思われた方もいるかと思います。ただし、これがquery関数の仕様で以下のようなものがあります。

QUERY will convert columns with mixed data types into one data type. If the data is mostly numerical values, then text strings (eg 1-2 in your data) will be converted to blank cells.

複数のデータタイプが混ざっている場合、少数のデータタイプがブランクセルになるというものです。バグかな？って思ってしまうような仕様ですが、現状の仕様です。

今回のケースもBenの年齢だけが文字で、他は数値だったので、Benの年齢がBlankになってしまっていました。

対応策

複数データタイプが含まれている場合の対応策としては、苦肉の策なのですが全て文字列かすると。。

関連articleはこのstackoverflowの記事になります。かなりめんどくさそうですね。

Google sheet Query Function Returns blank result from cell with data

I am creating a spreadsheet for my work place that will find drivers for the LED's we use but I've run into a slight problem. i pull in the data from another sh...

なので、実際はそんなことをするより、データに入力規則を先に入れておいて、数値以外のデータを入力できないようにするのがベターでしょう。数値もテキストも日付も選べますので、query関数で対応したいものと基本的に連動します。

セルが結合されている

厄介ですが、よくやられますよね。。セルを結合するとどうなるかというと、先頭セル以外のデータはブランクとなってきます。これはGASでも同じ仕様ですよね。

実はこれに対応する方法は、パッといいものがありません。

運用でカバーです。セルは結合しない。というか、データの正規化を保っておくっいうのが、一番の解決策です。

まとめ

さて、9回にわたってQuery関数についてみてきました。長かったですね。

少ないデータの場合、sumifsやvlookupなどでも事足りますが、データが大きくなってくるとよく複数関数で処理していると重くなりますよね？query関数の方が単一関数処理なので、肌感的には重くならないと実感しています（と言っても１００万行ぐらいまでのデータでしか試していませんが）

SQLの勉強の一環としてもQuery関数は手を出しやすいものですし、是非慣れていってもらえればと思います。そして、実際のSQLへ扉を開けてみましょう。似たようなものもあるなぁって気づくはずです。

【レクチャー関係紹介】

ストアカ

ストアカにおいて、私も1.5H程度で簡単にGASやQuery関数のとっかかりを解説する講座を実施しています。1Hくらいの講座と、30分くらいの相談時間（自由時間）を設けて、これからGASやQueryを始める人向けに実際のドキュメントや簡単なスプレッドシートでのGAS・Queryの使い方などを解説させていただきます。

個人的には普及活動の一環だと思って、他の業務のお客さんには申し訳ないものの、それなりにお求めやすく設定しています。必要に応じて別途継続サポートさせていただくことも可能です。

ゼロから始めるGAS講座

私の講座以外にもストアカには同じような金額帯でQuery講座が複数準備されていました。受講したことがないので、評価は出来ない立場にありますが、相性の良さそうな先生を探すのも手かもしれません。

MENTA

メンターを探すサイトも存在します。あまりGASやQuery関数を教えますってメンターはそこまで多くはないのが実情です。ノンプロエンジニア向けではなく、エンジニア向けor エンジニア志望者向けなので、Query関数はあまり対象にならないかもしれません。他方で純粋SQLを教えてほしいというリクエストであれば、多くのメンターが集まると思います。

ちなみに、私のレクチャーページも紹介しておきます。

【Query関数】　相談PLAN