SpreadsheetsもしくはExcelでもいいんですが、条件付き集計何使ってますか？
いや、条件付き集計というのはつまりSUMIFS的なもののことを言いたいのですが、特定の関数をデファクトのように言わずに進めていきたいわけです。
前提
- GoogleSpreadsheetsを前提として進めるのでExcelでは使えない方法も含める（が、Excelでも使えるかは考慮する）
- 集計方法全体での汎用性は検討するが、XXXIFS()とか書くのはダルいので、SUMを例にする
- いわゆる"Census Income Data Set (Adult)"データセットで実際に集計しながらやっていく
というわけでやっていきます。
実装方法洗い出し
実際のところ、上記の前提を実現できるのが半分しかないけど、どこができないかを含めて書きたいのでいれました。
- SUMIF()
- SUMIFS()
- DSUM()
- SUM(FILTER())
- QUERY()
- PivotTable
SUMIF()
SUMIF(範囲, 条件, [合計範囲])
すいません。いきなりで悪いのですがSUMIFは単一条件の指定しかできないので、今回の要件を実現できません。
僕はこの関数が嫌いです。Excelの教科書を見ていると、まずSUMIFが解説されて、そのあとSUMIFSに進む、というようなものになっているイメージがあるのですが、単一条件しか指定できないこの関数が存在する必要ありますかね？集計対象を後ろに書くというSUMIFSとの不整合も許せない。
なんなんですかあなたは。
SUMIFS()
ノンエンジニア、Excel畑の人はたいていこれなんじゃないかなとおもっています。僕は基本はSUMIFS派です。
=SUMIFS(合計範囲, 条件範囲1, 条件1, [条件範囲2, 条件2, ...]) =SUMIFS(hours_per_week,race,B$1,workclass,"<>Never-worked",age,">="&$A2,age,"<"&($A2+10))
いいところ
- セルの値を条件として使用する標準的な発想をしているので今回のような行列の先頭にするのは楽
- 後ろに書き足せばいいので条件の付け足しが楽
- 名前付きの範囲設定とは相性が良くて、設定しておくと格段に作業効率がいい
イマイチな所
- "<>"&とか"<="&とか書くのは正直あんまり直感的ではないし、わりと書き損じやすい
- 文字列の正規表現使う場合は"*"&G$1&"*"と書くけど、簡易的なこの表現しかできない。ちなみにExcelだとこの表現、うまくいかなくて一度どこかのセルで*を含んだ文字列を作成しないといけなかった気がする
- OR条件の指定、SUMIFS()+SUMIFS()+...と書かないといけなくてどうかんがえてもめんどくさい。
SUMIFS function - Docs Editors Help
DSUM()
社内でエンジニア作のSpreadsheetsみると、SUMIFSよりもこちらがメジャーな印象。 前提でいうけど、僕も不慣れ。
=DSUM(database, field, criteria)
=DSUM(データ全体, 集計対象のカラム名, {条件カラム1,条件カラム2,...;条件1,条件2,...})
=DSUM(dataset,"hours_per_week",{"race","workclass","age","age";B$1,"<>Never-worked",">="&$A2,"<"&($A2+10)})
- とにかくドキュメントがわかりづらいのだが、そもそもこうやって行列頭のセルの値を条件にテーブルを作るのはむいていなくて、条件表に基づいて1セルに値をいれる用途{条件カラム1,条件カラム2,...;条件1,条件2,...}と書くといい
- ドキュメントがむずい、むしろ複数条件の指定どこに書いてんだ？（社内の事例参照した）
- 名前付き範囲に依存しなくてカラム名を引けるのは利点ではある
- OR条件書くときにH2からA8:D10参照するみたいなことはできて、行ごとに条件のORを重ねられる
- とりあえずこの用途でSUMIFSと比較すると、名前付き範囲に依存せずにカラム名を引けるというのが利点
SUM(FILTER())
GoogleSpreadsheets的には極めて素朴な書き方。最近、MEDIANIFSっぽいものの実装を考えてた時に使って以来イチオシ。いやほんとこれいいですよ。
=sum(filter(集計列,条件1,条件2,...) =sum(filter(hours_per_week,race=B$1,workclass<>"Never-worked",age>=$A2,age<$A2+10))
いいところ
- 条件をSUMIFSと違って、素朴にA=BみたいにTRUE/FALSEを返す式で書く、むちゃくちゃ直感的
- 名前付き範囲も使えるし、条件式でセルを参照するのも容易
- TRUE/FALSEさえ返せば、等号不等号でも関数でもなんでもいい、つまりAND/OR関数も使えて、OR条件指定もしやすい
- 正規表現もExcel的な簡易版じゃなくてREGEXMATCHでガッツリ書ける、この例だと.*t.*e.*（WhiteとOtherが対象）で集計してみた
- 素朴なだけに、SUMを置き換えれば関数があるもの何でも集計できる
- XXXIFSは、COUNT,SUM,AVERAGEしかない
- DXXXは、COUNT,SUM,AVERAGEに加えて、GET,MAX,MIN,VAR,STDEV,STDEVP,VARP,COUNTA,PRODUCTがある
いまいちなところ
- Excelで使えないので、教科書に乗ってない、使ってる人が少ないで学習しにくい
- 当然Excelに移植できない
- 素朴な分、バリデーションは自分で気を使わないといけなくて、ISNUMBER()とかで欠損値除かないといけない、カラムが多いとこれは結構つらい
- 上記の事情で書いてる途中にエラーが出がちで少々難易度が高い
- Google特有のFILTER関数使うので、FILTER関数への理解がないとなんでこうなっているのかさっぱりわからない
FILTER function - Docs Editors Help
QUERY()
なんでリストアップしたんだっけ。行列集計みたいなアウトプットの発想ないんだけどな・・・。
=query(dataset,"SELECT A,I,SUM(M) WHERE B <> 'Never-worked' GROUP BY A,I ORDER BY A,I")
- roundとかで10台ごといけるかとおもったけど、QUERY関数そんなに使い勝手はよくなかった
- シュッと分析するにはいい（が、どう考えてもピボットテーブル使ったほうがいい）
- 列をカラムで指定しないといけないのはダルい
- DBに詳しい人が使う分には楽だけど、列指定とかめんどくさいのが学習コストの低さと釣り合わないのでは
- Excelでは使えない
QUERY function - Docs Editors Help
PivotTable
実際惜しい。GoogleSpreadsheetsの場合は行および列に集計フィールド使えないので、データ用のシートに関数列を付け足さないという前提を守ると、10歳単位の集計だけできない
- おそらく下準備とか含めて最速
- 集計関数の種類も多くて、SUM,COUNT,COUNTA,COUNTUNIQUE,AVERAGE,MAX,MIN,MEDIAN,PRODUCT,STDEV,STDEVP,VAR,VARP
- ちょくちょく体裁に制約はでる、集計行を上部に持ってこれないとか
- 列要素とかが増えたときの体裁変更がめんどくさい印象
検証用のSpreadsheets
まとめ
- SUMIF()衰退してほしい
- このエントリのような例でDSUM()使ってる人、分散とか日常的に出してない限りSUMIFS()使ったほうがいいと思う
- バリデーションだけ気をつかえば、書くときの直観性としてもSUM(FILTER())が最高
- SpeedでいえばPivotTableが最速
- 集計の汎用性は、XXXIFS << DXXX < PivotTable < XXX(FILTER)
- DXXXとPivotTableの差はMEDIAN
- PivotTableとXXX(FILTER)の差は探すとたくさんありそうだけど、まともに使う案がありうるのは四分位点ぐらいだろうか？
- とはいえ個人的には、全方法メンテできるように押さえておきたい
余談
- 労働時間をなぜ合計したのか意味が分からない
- 間違ってる記述あったら教えてください
書き終わってからもうDataStudioでやったらよいのではみたいな徒労感に包まれてきたDataStudioはQUERYと同じ形しか表作れないのでそんなことなかった
- 作者:きたみ あきこ
- 発売日: 2016/07/20
- メディア: 単行本（ソフトカバー）
PR
