こんにちは、プログラマーの柿原です。

日々様々な業務をこなしていますが、中でも何かとよくスプレッドシートを使います。デバッグのためのテストデータの作成準備だったり、データの整理やチェックだったり、色々な場面で役立ちます。とても便利なスプレッドシートですが、そんなスプレッドシートの「表示形式」でハマった話について書いてみます。

スプレッドシートにはデータを整理するのに便利な関数がたくさんあります。例えばQUERYという関数があります。この関数は、Google Visualization API のクエリ言語を使ってデータに対して様々な操作ができる関数です。Google Visualization API のクエリ言語はSQLにも似ていて、SQLに馴染みのある方なら扱いやすい言語です。このQUERY関数は便利なのですが、先日思わぬ落とし穴にハマってしまいました。

まずは実際に、この関数を使うとどんなことができるのか見てみましょう。例えばこんなデータがあったとします。これはジョブと4つの能力値をもつ冒険者のリストです。

QUERYを使えば様々な条件でデータを抽出したり、並び替えたりができます。試しに、「まほうつかい」を抽出して「まりょく」の降順に並べてみましょう。それは次のような関数で実行できます。

=QUERY($A$1:$G$16, "SELECT * WHERE C='まほうつかい' ORDER BY E DESC")

実行結果は以下のようになります。簡単な一例ですがQUERY関数の強力さが感じられるところだと思います。

ここで、「表示形式」にハマった話に戻ります。

今度は、「たいりょく」が50より大きいものを抽出して「たいりょく」の降順に並べます。それは次のような関数になります。

=QUERY($A$1:$G$16, "SELECT * WHERE F>50 ORDER BY F DESC")

以下が実行結果です。

なんとなく良さそうですが、よく見てみるとIDが7の「ジェリーノ」という冒険者が抽出されていないことに気が付きます。ジェリーノの「たいりょく」は99であるし、条件も満たしているはずです。QUERY関数を見直してみても間違いがあるようには見えません。それではいったいなぜ抽出されないのでしょうか。

実は、「ジェリーノのたいりょく」について表示形式が「自動」ではなく「書式なしテキスト」になっていたのです。QUERY関数についてGoogleのサポートページを見てみると以下のようにあります。

1 つの列に異なる種類のデータが含まれている場合は、その列に大多数含まれる種類のデータをクエリに使用します。小数の種類のデータは NULL 値とみなされます。

https://support.google.com/docs/answer/3093343?hl=ja

つまり、「ジェリーノ以外のたいりょくは数値」で、「ジェリーノのたいりょくは文字列」になっており結果として「ジェリーノのたいりょくはNULL値」とみなされ、ジェリーノのデータは「たいりょくが50より大きい」という条件を満足しないデータになってしまったのです。

「表示形式」が変わるということもなかなか起こることではないように思えますが、共同編集ができるスプレッドシートでコピペなどもなされれば、色々な表示形式が混在することも意外と起こります。

今回挙げた例は少ないデータのため気づくことができましたが、もっとたくさんのデータで「表示形式」が一部違っているという場合、QUERY関数の実行結果だけを見て抽出されていないものがあるということに気が付くのはほとんど不可能でしょう。気づかぬままにデータ分析やデータ作成の準備を進めていたらと思うとゾッとします。

対策としては、QUERY実行前に複数セルを選択して表示形式を揃えておく・VALUE関数やTO_TEXT関数で強制的にセルの値を数値や文字列に変換しておくなどの方法があるようでした。

便利なものはどんどん使っていきたいですが、その挙動もきちんと確認しておくことが大切だという自戒も込めたお話でした。