ExcelだけでSQLのGROUP BY相当の集計結果を出すベストプラクティス
仕事柄、ユーザから台帳ライクなExcelファイルを突然見せられて「このExcelデータで挙がっている項目の件数TOP10を出したいんだけど...」的なことを相談されることが時々あります。そんな時にとり得る選択肢はいくつかあると思いますが、ベストプラクティス…というか、相手のことも考えた"妥協点"は一体どこなのか。一度は考えておく必要があると思ったので今更ながらまとめておきます。
- １．Excelの標準機能を使う
- ２．自分のやりやすい方法としてSQLのGROUP BYを使う
- ３．じゃあ間をとってコレなんかどうだろう？
- ４．まとめ
- ５．【追記あり】PythonのPandasが便利
- あわせて読みたい
１．Excelの標準機能を使う
まず、前提として今回の話はシンプルに「項目の件数（個数）」を求めたい場合です。「項目ごとの金額の加算結果」などではありません。例えば下記のような話です。
本当はExcelの標準機能を使って、目の前で解決しするのがスジというものでしょう。当然依頼者もそれを期待していると思います。
ですが、単純にExcelの機能を使うと、意外かもしれませんが思い通りの形で結果を得るのはなかなか困難な印象があります。
例えば「小計」機能の場合。
（メニューの「データ」→「小計」を選択）
一応個数を出すことが可能ですが、途中に行が挿入される、アウトラインができる、書式も勝手に作られる、などと余計なモノが入ってきてしまいます。もう元のデータとはかけ離れた姿になってしまいますね。（そして、正確にいうと事前のソートも必要です。）
百歩譲って合計行だけをコピペして加工したら活かせそうかな、と思いきや...
別シートに貼り付けると...
こんな感じで、「小計」は結構早い段階で"距離を置きたい存在"として脳にインプットされてしまいます。
また、例えば「countif関数」の場合。
これは指定した文字列の存在件数をカウントしてくれるという便利なものではありますが、「現在リスト中に存在する品目全てを自動で抽出してグループ化してくれる」という機能はありません。あくまで検索文字列は自分で指定する必要があります。なので、「一発で簡単に」を求める依頼者にこの方法を薦めるとあまりいい顔をされないでしょう（私の経験上はそうでした）。
※Excelの「入力規則」なんかで項目名をマスタから選択するようにしている場合などは別です。このように「後で統計を取ることを意識したデータ構造」だったらcount系関数は多大な効果を発揮すると思います。ですが往々にしてこのような依頼がくるときは、あまり「よいデータ」は提示されないものなのです。
２．自分のやりやすい方法としてSQLのGROUP BYを使う
私の場合は、一旦Access等のDBにインポート→SQLをたたいて返った結果をExcelに貼りつけて渡す（または目の前で実践して見せる）、という手法がベストだと思っています。
これが個人的にはラクでもあり、大量データにも耐えられ、欲しい結果の形を自在に変えられる、そして自分的にも多少の満足感が得られる、といったメリットがあるからです。
SQLは下記のような簡単なもので事足りる場合がほとんとです。また、仮に複雑なものを求められた場合はそればそれで達成感があるというものです。
select max(名称) as item, count(*) as cnt from t_temp group by 名称 order by count(*) desc;
人によっては、「DBにインポートするなんて余計な手間」という人もいるかもしれませんが、慣れている人にとってはこの方法がラクで早いんです。何より「得たい結果の形をいくらでも変えられる」という、かゆいところに手の届く安心感が一番大きな魅力です。
ただ、このような方法をとると依頼者は「あーExcelとかで簡単にやりたいのにな...」という顔をすることがほとんどです。このような依頼者のほとんどは「一発で簡単にできる方法」を求めているため、SQLなんか薦めると引かれてしまいます。
また、必ず依頼者のPCに必ずAccess等のDBソフトが入っているとも限りません。
３．じゃあ間をとってコレなんかどうだろう？
そんなこんなで、依頼者が望むような「Excelで一発でできる方法」ってないのかなと考えるようになりました。
デファクトスタンダードであるMicrosoftOfficeほどのソフトが、手軽にきれいな集計結果を出せるような機能を用意していないものでしょうか。
Officeにも一応そんな機能あります。
先に答えをいうと定番のピボットテーブルを使うことになるのですが、ちょっと本来の使い方とは言えないかもしれません。ですがこれが今最も求めるものに近いのではないでしょうか？以下、手順を書いておきます。
（１）「挿入」⇒「ピボットテーブル」⇒範囲：件数をほしい項目列（ここでは"名称"の列）を選択
（２）"名称"を行ボックスと列ボックスへドラッグ＆ドロップ
（３）一旦生成された結果をコピーし、別シートに貼りつけて加工（ソートや装飾）。完了。
ピボットテーブルなので当然生成される結果はこちらが避けたがっている「独特の書式」ではあるのですが、「小計」とは違って別シートに貼り付けて活用できます。
４．まとめ
エンジニア視点でみると、Excelの標準機能を使った場合、「小計」にしろ「ピボット」にしろ生成される結果が"独特の書式"をもってしまうため、「そのソフトに依存する」という部分で敬遠してしまいがちです。しかし、ユーザのことも考えた場合、ある程度寄り添うことも求められます。
ピボットテーブルを少し邪道的に使ったやり方だと、得たい形に近い結果を得ることができ、それを別シートにコピーすることで"独特の書式"を捨てることも可能なようです。
ということで、ExcelだけでSQLのGROUP BY相当の集計結果を出すベストプラクティスは「ピボットテーブルを邪道的に使う」という結論に個人的には落ち着いておりますが如何でしょうか？
５．【追記あり】PythonのPandasが便利
最近であればPythonの定番ライブラリ「Pandas」を使うという選択肢もありかと思いますので追記しておきます。
kojikoji75.hatenablog.com
kojikoji75.hatenablog.com
