Google独自のSpreadsheet関数について

Google Spreadsheetは、Excelにはない便利な関数があるという記事やエントリーがネットを探せばたくさん出てきます。確かに便利と言えば便利なのですが、使うに当ってやっかいなことも非常に多いですし、結局はGA上でしか使えないので、どうなんだろうという点もあるのは事実です。そんなものの中で、自分が使わざるを得なくなった為に使ったものについてのメモをここに残そうと思っています。使用例のスプレッドシートはこちらです。SUMIFS等の関数も新しいスプレッドシートでサポートされたようですが、今回紹介する関数があれば、今更って感じです。

Query関数

概要

この関数、とても便利なようで、めちゃくちゃ扱いにくい関数です。しかも、相変わらずリファレンスは不親切で充実などしておらず、かといって、Google SpreadsheetにはVBAでいうところのDlookupなどの関数が充実していないために、これを使わざるを得なくなりました。いろいろ検討した結果なのですが、確かにこの関数自体の存在はありがたいと言えます。特徴をまとめます。

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

といったところです。最新のGoogle SpreadsheetではSUMIFSなどの関数も使えるようにはなっているものの、条件式の指定などは柔軟に動的に出来るわけじゃないので、この関数を扱えるようになると、非常にありがたいと言えます。が、しかし、この関数を扱うにあたっては、この関数の極めて扱いにくいクセを克服しなければいけません。それらを下記にまとめます。

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

使用例

今回のサンプルケースの場合、条件にもあるように「指定された日付以降の日付できのこの売上をさらに合計して返せという計算式になっています。

=sum(query(A2:H20,"select E where (C = '"&$D24&"') and (date '"&$B24&"'<=B)"))

通常、QUERY関数は配列で返すのですが、それを更にSUMで受け取って返してるので、今回は通常の関数のように、計算結果が帰って来ています。E列をselectしており、C列がD24の条件式を読み込み、B列がB24の条件式を読み込んで評価しています。B列は日付であるため、date指定をしています。

DSum関数やSumif関数などは、条件式の部分も範囲指定してどうこうやっているわけですが、慣れるとあのやり方は制限に感じますし、実際制限でもあります。今回のこれは柔軟に抽出条件を指定できる上に、値さえ取れれば形式は問わないので、使いやすいです。

FILTER関数

概要

この関数はQUERY関数よりも遥か使い易くて、そして非常に高速にデータの抽出が出来ます。この関数に他の関数を組み合わせれば、正直、DSUMやSUMIFSは必要ないんじゃないか？と思うほどです。5000レコード/27カラムのデータでも、なんとか許容できるスピードで表示してくれます。もちろん、単発で抽出ならば数秒も掛からず抽出が可能なので、この関数はGoogle Spreadsheetを使う上では絶対の覚えておきたい関数と言えます（他の機能があまりにも貧弱なので、余計そのように感じると思います）。特徴としては・・・

  1. 馬鹿でかいデータの塊に対して実行しても非常に高速に値を返してくれる。
  2. 通常の関数同様の指定方法に若干拡張された感じなので、QUERY関数よりかは理解しやすい。
  3. 条件指定が複数可能であり、また計算式での条件指定も可能。
  4. QUERY関数同様に、結果をSUM関数等に食わせることが可能である。
  5. QUERY関数よりも高速であり、複数のセルにSUMで食わせるような数式を書いても、断然早い。

といったところです。とは言え、若干クセがある関数でもあります。

  1. 基本的な関数の使用例は、=sum(filter('102'!C$2:C,'102'!$B$2:$B>=datevalue($J$2),'102'!$B$2:$B<=datevalue($L$2),'102'!$A$2:$A=$B19))といったような感じです。通常の関数の流儀には近いものの、複数の条件式をカンマで区切ってつなげる点が若干違う。今回は３つの条件式を加えている。
  2. 日付の条件式は普通にDATE関数などが使えるが、今回はセル上の日付ボックスの値を使いたかったのでDATEVALUE関数を使用した。であるため、特に日付の形式は限定されない。
  3. OR条件を書きたい場合には、非常にクセがある。同一のカラム内で、２つ以上のワードを抽出対象とするような場合、ORを使用するが、
    1. =FILTER(A:A;(A:A="キーワード１")+(A:A="キーワード２"))
  1. といったような書き方をする。ヒントは、それぞれの条件式をカンマで区切らず、括弧で括って + 演算子でつなぐ。これで、OR条件が作成できる。
  2. 普通に全範囲指定して、抽出条件を端的に書くと、配列で帰ってくるので、CONTINUE関数を含めた形で演算結果が表示される。ここが独特。AccessのDLookupで帰ってきた塊がそのままシート上に展開されるようなイメージである（単一の値ではなく、複数行複数列の答え）。
  3. 今回テストした限りでは、5000レコード/27カラムのデータから単純な抽出ならば直ぐに演算結果が出る。しかし、この中から１つの集計結果を複数手に入れたい時には、セルに1.のような数式を入れて、且つ10以上のセルに渡って、記述した場合、かなり遅くなる（それでもQUERY関数より断然早い）。

使用例

今回のサンプルケースの場合、条件にもあるように「指定された日付以降の日付で植物の売上をさらに合計して返せという計算式になっています。

=sum(filter(E2:E20,$B$2:$B20>=datevalue(B29),$C$2:C20=$D29))

FILTER関数も通常は配列を返すのですが、フィルタしたデータレコードを更にSUM関数に投げているので、帰ってきている値は合計値となっています。

QUERY関数よりも計算結果が早く、おまけに大きなデータテーブルでも計算して返してくれるので、こちらを専ら中心に使っています。

IMPORTRANGE関数

概要

この関数は、他のスプレッドシートファイルに入っているデータの塊を取ってくる関数です。Excelのシートのリンクの機能に似ているようでちょっと違うもので、使用する為には関数に対してシートへのリンクの許可を与える必要性があります。非常に便利で、これまでの関数同様、配列で値を返してくれるので、これをSum関数に食わせて合計値を出すといったようなこともできるので、組み合わせればDSUM的な事をやらすこともできる便利な関数です。特徴としては・・・

  1. 馬鹿でかいデータの塊に対して実行しても非常に高速に値を返してくれる。
  2. ちょっと関数の引数の指定方法にクセがある。
  3. QUERY関数同様に、結果をSUM関数等に食わせることが可能である。
  4. 取りたいスプレッドシートのIDとシート名、範囲の指定が必要。

といったところです。クセといっても、取得したいデータが入っているスプレッドシートのIDが必要ということだけで、ほかはそれほど特殊ではありません。

使用例

今回のケースでは、データベースという名のファイルの中databaseという名前のシート、その中でもA2:Cで範囲を指定しています。

※この範囲の指定方法だとC列の終わりを指定していないので、データが入っている最後まで自動的に調べて返してくれます。データが順次追記されるようなファイルの場合この指定方法が効果的です。

=importrange("10boX_4D0li-7iaMQWbTZP0GR3EOE6K_0ZLtRfLCfQZM","database!A1:C")
  • 青字の部分にはデータの入っているスプレッドシートのIDを入れます。
  • 赤字はそのシート名!を入れます。
  • 紫字はそのシート内の範囲を指定します。

今回の関数はただ単純に値を引っ張ってるだけではなく、リンクさせているので、大元のファイルを変更した場合、この関数を使用しているスプレッドシートの側も値が変わります。

今回の取りたいデータの入っているファイルはこれです。関数で取ってきている側のファイルはこちらです。

参考リンク