Google Spreadsheetは、Excelにはない便利な関数があるという記事やエントリーがネットを探せばたくさん出てきます。確かに便利と言えば便利なのですが、使うに当ってやっかいなことも非常に多いですし、結局はGA上でしか使えないので、どうなんだろうという点もあるのは事実です。そんなものの中で、自分が使わざるを得なくなった為に使ったものについてのメモをここに残そうと思っています。使用例のスプレッドシートはこちらです。SUMIFS等の関数も新しいスプレッドシートでサポートされたようですが、今回紹介する関数があれば、今更って感じです。
この関数、とても便利なようで、めちゃくちゃ扱いにくい関数です。しかも、相変わらずリファレンスは不親切で充実などしておらず、かといって、Google SpreadsheetにはVBAでいうところのDlookupなどの関数が充実していないために、これを使わざるを得なくなりました。いろいろ検討した結果なのですが、確かにこの関数自体の存在はありがたいと言えます。特徴をまとめます。
といったところです。最新のGoogle SpreadsheetではSUMIFSなどの関数も使えるようにはなっているものの、条件式の指定などは柔軟に動的に出来るわけじゃないので、この関数を扱えるようになると、非常にありがたいと言えます。が、しかし、この関数を扱うにあたっては、この関数の極めて扱いにくいクセを克服しなければいけません。それらを下記にまとめます。
今回のサンプルケースの場合、条件にもあるように「指定された日付以降の日付できのこの売上をさらに合計して返せという計算式になっています。
=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関数などは、条件式の部分も範囲指定してどうこうやっているわけですが、慣れるとあのやり方は制限に感じますし、実際制限でもあります。今回のこれは柔軟に抽出条件を指定できる上に、値さえ取れれば形式は問わないので、使いやすいです。
この関数はQUERY関数よりも遥か使い易くて、そして非常に高速にデータの抽出が出来ます。この関数に他の関数を組み合わせれば、正直、DSUMやSUMIFSは必要ないんじゃないか？と思うほどです。5000レコード/27カラムのデータでも、なんとか許容できるスピードで表示してくれます。もちろん、単発で抽出ならば数秒も掛からず抽出が可能なので、この関数はGoogle Spreadsheetを使う上では絶対の覚えておきたい関数と言えます（他の機能があまりにも貧弱なので、余計そのように感じると思います）。特徴としては・・・
といったところです。とは言え、若干クセがある関数でもあります。
=FILTER(A:A;(A:A="キーワード１")+(A:A="キーワード２"))今回のサンプルケースの場合、条件にもあるように「指定された日付以降の日付で植物の売上をさらに合計して返せという計算式になっています。
=sum(filter(E2:E20,$B$2:$B20>=datevalue(B29),$C$2:C20=$D29))FILTER関数も通常は配列を返すのですが、フィルタしたデータレコードを更にSUM関数に投げているので、帰ってきている値は合計値となっています。
QUERY関数よりも計算結果が早く、おまけに大きなデータテーブルでも計算して返してくれるので、こちらを専ら中心に使っています。
この関数は、他のスプレッドシートファイルに入っているデータの塊を取ってくる関数です。Excelのシートのリンクの機能に似ているようでちょっと違うもので、使用する為には関数に対してシートへのリンクの許可を与える必要性があります。非常に便利で、これまでの関数同様、配列で値を返してくれるので、これをSum関数に食わせて合計値を出すといったようなこともできるので、組み合わせればDSUM的な事をやらすこともできる便利な関数です。特徴としては・・・
といったところです。クセといっても、取得したいデータが入っているスプレッドシートのIDが必要ということだけで、ほかはそれほど特殊ではありません。
今回のケースでは、データベースという名のファイルの中databaseという名前のシート、その中でもA2:Cで範囲を指定しています。
※この範囲の指定方法だとC列の終わりを指定していないので、データが入っている最後まで自動的に調べて返してくれます。データが順次追記されるようなファイルの場合この指定方法が効果的です。
=importrange("10boX_4D0li-7iaMQWbTZP0GR3EOE6K_0ZLtRfLCfQZM","database!A1:C")今回の関数はただ単純に値を引っ張ってるだけではなく、リンクさせているので、大元のファイルを変更した場合、この関数を使用しているスプレッドシートの側も値が変わります。
今回の取りたいデータの入っているファイルはこれです。関数で取ってきている側のファイルはこちらです。