スプレッドシートQuery関数を使ってみよう ⑨データに改行が入ってるぞ etc クエリ関数エラー対策

クエリイメージ スプレッドシート
この記事は約6分で読めます。
Advertisements

読んで欲しい人

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

Query関数(クエリ関数)シリーズ予定 (あくまで予定)

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

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

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

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

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

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

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

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

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

Query関数が扱いにくいデータ

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

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

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

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

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

select *で検索

ちょっと待ってください、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関数には敵です。

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

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

今度は年齢で検索しようとしてみます。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関数の方が単一関数処理なので、肌感的には重くならないと実感しています(と言っても100万行ぐらいまでのデータでしか試していませんが)

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

連載目次: spreadsheet_query関数を使いこなそう

SQL勉強もかねて、spreadsheetのquery関数の使い方に慣れていきます。簡単なselect文の使い方から、SQLとの違いも含めて勉強していきます。

  1. スプレッドシートQuery関数を使ってみよう ①概要と目的
  2. スプレッドシートQuery関数を使ってみよう ②クエリ関数の構成
  3. スプレッドシートQuery関数を使ってみよう ③SELECT句
  4. スプレッドシートQuery関数を使ってみよう ④WHERET句
  5. スプレッドシートQuery関数を使ってみよう ⑤GROUP BYを使ってみる
  6. スプレッドシートQuery関数を使ってみよう ⑥他のシートからデータを持ってきてみよう〜 ImportRange関数との組み合わせ〜
  7. スプレッドシートQuery関数を使ってみよう ⑦並び替えの基本 ORDER BY句
  8. スプレッドシートQuery関数を使ってみよう ⑧SQLやるなら必須 Query関数でもサブクエリをやってみる
  9. スプレッドシートQuery関数を使ってみよう ⑨データに改行が入ってるぞ etc クエリ関数エラー対策

コメント