Googleスプレッドシートには、Excelにはない特別な関数がいくつかあります。それらはスクリプト無しでまた、特別な機能を使わずにデータの塊の中から、色々な条件でデータを塊で取り出すことが出来ます(Excelは基本的に関数は塊ではなく、単一の値でしか答えを返せません)。この特別な関数を使えば、スクリプトを組む時に余計なコードを記述しなくても済むので、是非使えるようになっておくとお得です。

今回はそんな特別な関数の中でも、難易度は高いものの、非常に柔軟なデータの抽出だけでなく、クロス集計まで可能な「Query関数」を使ってみたいと思います。この関数で使われるQuery文は、Google Visualization APIで使われるものとほぼ同じ仕様になっています。

※今回の関数は非常に多機能なので2回に分けて紹介しています。

今回使用するサンプルスプレッドシート

Query関数の使い方

概要

この関数は他の2つ(Filter関数、ImportRange関数)と比較すると、非常に使いにくい関数です。また、関数の組み方スタイルが独特なのでとっつき難い人が多いでしょう。しかし、SQLの使えないスプレッドシートに於いて、この関数は非常にありがたい関数です。数式が非常にSQLライクな感じなので、VBAなどでSQL文を書いてる人は割りと習得しやすいのではないでしょうか?特徴として、

  1. 馬鹿でかいデータの塊に対して実行しても非常に高速に値を返してくれる。
  2. 通常の関数とは異なり、かなり関数内でのパラメータの指定方法が独特である。
  3. SQLチックなパラメータ指定であるため、非常にとっつきにくい。
  4. キーとなるパラメータの値を特定のセルから取りたい場合、日付・数値・テキストで指定方法に違いがある。
  5. SQLチックな文章の中で集計やカウントなどの指定も出来ますが、妙な文字列が1行目に出るのが非常に気になる(9.を参照)
  6. Query関数で帰ってきた値を通常のSUM関数などに食わせて、集計などが出来る。SUMIFやDSUMなどは不要になる。
  7. パラメータの指定をレンジではなく、セル単位で指定が可能なので、DSUM関数のようなパラメータが特定レンジで指定しないといけないといったことがないため、非常に融通がきく。
  8. ピボットタイプ(クロス集計)の集計が可能である。
  9. データのタイトル部分を関数内で構築が可能である。

注意点

この関数を使う上での注意点が結構あります。以下にそれをまとめてみました。

  1. Query関数内ではカラム行は使用しないので、範囲指定でもそれらの行は含めない(そのため、A2から指定している)。
  2. A列、B列などで指定するが、select文の中でsum(C)といったような書き方も可能ではある。しかし、1行目にsumという文字列が出力されたりするので、通常は1.のようにsum関数に食わせるのが通常。
  3. Where以外にもGroup Byなどが使用できる
  4. 条件式の指定に特定のセルの値を参照させたい場合には、””で括って、中に&&で括ったセル番地を指定する。
  5. 但し、数値ではない場合(文字列)には、”で括った中に4.の書き方をしなければならない(ここ重要)。日付の場合も同じ。
  6. 更に日付を指定する場合には、5.の前にdateを指定するのが決まりになっている。
  7. selectで始まる条件式は””で括って置かなければならない。
  8. andやorが使用できるが、できればそれぞれの条件単位ごとに()で括っておくと、わかりやすくなる。
  9. 範囲指定した中の日付を元に何かを抽出したい場合は要注意。Queryの条件式側は「2014-10-06」といったパターンで指定されていないと受け付けてくれない。もちろん、セルの中の値に表示としてこのような形にしてもダメ。書式なしテキストにして格納しておくべし。「2014/10/06」といった指定もダメ。
  10. さらに、抽出される側の日付データにも注意が必要である。こちらは「2014/10/06」といったデータであっても何ら問題がないが、その代わり、表示がそうでも、時刻データが入っているとダメ。
  11. 故にどこぞからコピーしてきてsetValuesなんかで貼り付けた後のデータを見ると、表示形式で2014/10/06となっていても、セルの中のデータは「2014/10/06 7:00:00」なんて形で、日付データが勝手に混じっていることがある。setValuesでセットした時にこれが起きる。ここで自分は大嵌まりしてました。データから時刻部分を削って戻してやらないといけない。
  12. 正直言って、何個もこの関数を使うと、数百レコードで既に重たい。ましてや、多数のセルに同様の計算式を入れると更に重い。sum関数に食わせてセルに反映を多様するには実用的ではない。

最もシンプルな使い方

簡単な使い方からまず。*(ワイルドカード)も使用可能なので、指定範囲の列を全て出す場合には、活用しましょう。条件式としてF列の数値が100より上のレコードを抽出するといった事例の場合には、以下のような数式となります。

複数条件で抽出

また、特定のセルの値を抽出条件として取り、さらに日付を抽出条件として取るようなケースでは、

こんな書き方になります。D24の値が文字列の抽出で、ポイントの4,5がコレに該当します。また、日付の場合dateを頭に付いてるが、これがポイントの6に該当しています。この計算結果を更にSUM関数に食わせて合計を出すといった事も可能です。VBA内で変数を抽出条件として使う感覚なので、プログラミング的な数式の作り方だと言えます。

もともとQueryなので、ANDやORで簡単に条件式を構築出来るので、複数条件での抽出はFilter関数よりもお手の物です。

指定範囲内で抽出

日付などに於いて、ここからここまでの期間で抽出といった場合には、dateを頭につけてANDでそれぞれ以上・以下で指定を作って上げると抽出が可能です。もちろん特定のセルを参照させると利便性が向上するでしょう。Aが日付のデータの入ってる列です。

比較演算子の使い方や、また、9.の注意点にあるように、指定する条件側の日付形式はyyyy/mm/ddではエラーになるので、yyyy-mm-dd形式にしてあげる必要があります。普通に日付を入力するとスラッシュになってしまうので、セルの表示形式に於いて、「書式なしテキスト」にしてあげるとスムーズです。

図:日付の指定方法だけ注意が必要

集計やカウント

SQL文と同じく、Query関数内でも集計やカウントをする事が可能です。使用できる集計関数は、avg(), sum(), count(), max(), min()の5つとなっています。sumとcountが利用頻度が最も高いでしょうね。それぞれの列の合計を取って集計する例を作ってみました。

それぞれの売上金額のある列の全合計(ただし売上合計が100より上のもの)を集計しています。集計等を行う場合、ポイントの5.にあるようにタイトル行にsumと出てしまうので、LABELもつけて上げています。countであれば件数が出てくるわけです。集計結果を別途Sum関数に食わせたりする必要がありません。

Query関数の結果を別の関数に食わせる

Query関数の場合、関数単体で殆どの事が実現可能なので、使うシーンは少ないですが、他の関数同様返ってきた配列データを更に別の関数に食わせて処理を行わせる事が可能です。Sumなどの場合はQuery関数自体に集計機能が備わってるので、別途食わせるといったことをしませんが、その他の関数を使う場合には有効です。

また、配列で結果が返ってきたものを別の関数に食わせている点もExcelにはない感覚ですね。今回は平均値ではなく統計などで利用される中央値(メジアン)の関数で組み立ててみました。

E列(D商品売上高)の数値の配列に対して、Median関数で中央値を取っています。統計ではよく利用される関数ですが、極端に高い数値があると平均値が上がってしまい、現実的ではない数字になります。そこでより集まってる塊に対してだけ平均を取るような関数で、平均値よりも現実的な数値が求められます。

図:平均値と比べてみた。

クロス集計を行う

Googleスプレッドシートには、Excel同様にピボットテーブル機能が用意されているので、問題なくクロス集計が可能になっています。しかし、データの二次利用となると、あの形式では正直使いにくい。Access等では、クロス集計クエリをまたテーブルとして扱えるので、更なる計算などが可能になっているけれど、これをGoogle Spreadsheet上でやるためには、ピボットテーブル機能を使っての作成ではちょっと困難です。

ということで、Query関数を旨いこと活用することで、Accessのように二次利用に最適な形で、日々の入力データをクロス集計化し、シートに展開する事が出来ます。複雑ではあるので万人にオススメという訳ではありませんが、標準の関数のみで行えるので、スピードが早い点、そして何よりもスクリプトを組まずに展開が出来、スクリプトから二次利用が出来るというのは非常に都合の良いものです。グラフ作成でも、柔軟に自動的にグラフ展開が期待できるので、オススメですよ。

事前準備

今回想定しているのは、以下のようなデータカラムの日々の入力データから、クロス集計化して別のシートに展開するケースです。今回使用するスプレッドシートも全く同じデータ構造なので、そちらを見てもらったほうが、理解は早いかもしれませんね。下記のデータテーブルより、【商品名】を基準にクロス集計を行って行きます。

販売先コード支店名日付ID商品名売上個数
101渋谷支店2015/5/11ハエトリソウ300
102梶が谷支店2015/6/22サラセニア150

これをクロス集計して展開する時には以下のような構造で行列を設定し、展開を行います。商品名毎に売上個数を集計する訳です。

販売先コード支店名ハエトリソウサラセニア
101渋谷支店3040
101渋谷支店2512

さて、これでクロス集計化した時のイメージは出来たので、実際に実データを基に、Query関数を使用してピボット化を行います。普通はココで、ピボットテーブルを使う所ですが、今回は使用しません。あくまでもQuery関数で二次利用の為にデータテーブルを作るのが目的ですから。

クロス集計させてみる

さて実際に使用します。Query関数自体の使用方法についてはいずれ他の独自の関数とまとめて紹介しようと思います。

今回の数式は以下のようになります。SQLチックですが、独自のPivotという項目が今回の肝になります。Group Byでどこまでグルーピングするかは、ユーザがどこまで列を残すかによります。今回は、日付の部分を外し、グループ化の対象にはしませんでした。

図:スクリプトからの二次利用がとてもしやすい

カラムのソートはできないので・・・

Query関数は、構文の中でPivotを使った場合、ORDER BYが利用できません。その為、Pivotを使ったクロス集計を使った時に、例えば「日付」をカラムに取った場合、順番がオカシナ事になります(1日の次が10日、2日の次が20日といったような感じで列が並んでしまう)。

出勤簿やら日付を横にとって見ていく時にこれでは具合が悪いです。但し、数字のみであるならば、きちんと並んだりするので、「日」といった文字が入ると、順番がおかしくなるようです。この場合、数字の部分に於いて1桁の数字には0を頭に加えて「01日」としてあげて、2桁の数字は普通に「10日」としてあげることで、きちんと並びます。

下記の図はそれぞれ同じ構文ですが、並びが違うのがわかると思います。Query関数できちんとPivotで並べたい時は、関数ではなくデータ側に細工が必要なので注意が必要です。

図:良い事例

図:悪い事例

ポイント

  1. 使用する列は、D列以外の全てですが、表示する行は、A~Cそして、集計としてF列の値を指定しています。
  2. Group Byにてグループ化を指定します。A~Cまでをグループ化の対象として指定しています。この辺りは、Accessではお馴染みですね。
  3. Pivotにて列指定としてE列を指定しています。1.で集計対象にした内容が、これで商品名毎に集計表示されるわけです。
  4. LABEL以下は要らないと言えば要らないのですが、任意の列ラベルを付ける事ができます。標準だとラベルがない為、A~Cまでラベル指定してます。
  5. 但し、E列のPivotで指定した箇所は、自動的に列名として商品名が付与されるようになっています。
  6. 値は配列で返されるので、Filter関数同様自動的にスプレッドシートが拡張され、A1に式を入れた場合、そこを基準に値が自動的に展開されますので、誤って消しても復元されます
  7. あとは作成されたデータテーブルの値を他のレポート用シートからvlookupで取ったり、カウントしたり、グラフを作ればOKです。

関連リンク

共有してみる: