TechNote

とあるエンジニアのただのメモ

ExcelだけでSQLのGROUP BY相当の集計結果を出すベストプラクティス

仕事柄、台帳風のExcelファイルを突然見せられて「このExcelデータで挙がっている項目の件数TOP10を出したいんだけど...」的なことを相談されることがときどきあります。
そんなとき、とり得る選択肢はいくつかあると思いますが、ベストプラクティス…というか、相手のことも考えた"妥協点"はどれでしょうか。
この件については自分/依頼者間の妥協点を一度は考えておく必要があると思ったので、今更ながらまとめておきます。

f:id:kojikoji75:20150804225130j:plain

1.Excelの標準機能を使う
2.自分のやりやすい方法としてSQLのGROUP BYを使う
3.じゃあ間をとってコレなんかどうだろう?
4.まとめ



1.Excelの標準機能を使う

まず、前提として今回の話はシンプルに「項目の件数(個数)」を求めたい場合です。「項目ごとの金額の加算結果」などではありません。例えば下記のような話です。

f:id:kojikoji75:20150812221738p:plain
f:id:kojikoji75:20150812221749p:plain

本当はExcelの標準機能を使って、目の前で解決しするのがスジというものでしょう。当然依頼者もそれを期待していると思います。
ですが、単純にExcelの機能を使うと、意外かもしれませんが思い通りの形で結果を得るのはなかなか困難な印象があります。

例えば「小計」。

(メニューの「データ」→「小計」を選択)

一応個数を出すことが可能ですが、途中に行が挿入される、アウトラインができる、書式も勝手に作られる、などと余計なモノが入ってきてしまいます。もう元のデータとはかけ離れた姿になってしまいますね。(そして、正確にいうと事前のソートも必要です。)

f:id:kojikoji75:20150815153332p:plain

百歩譲って合計行だけをコピペして加工したら活かせそうかな、と思いきや...
f:id:kojikoji75:20150815153951p:plain

別シートに貼り付けると...
f:id:kojikoji75:20150815154007p:plain

こんな感じで、「小計」は結構早い段階で"距離を置きたい存在"として脳にインプットされてしまいます。


また、例えば「countif関数」があります。

これは指定した文字列の存在件数をカウントしてくれるという便利なものではありますが、「現在リスト中に存在する品目全てを自動で抽出してグループ化してくれる」という機能はありません。あくまで検索文字列は自分で指定する必要があります。なので、「一発で簡単に」を求める依頼者にこの方法を薦めるとあまりいい顔をされないでしょう(経験上そうでした)。

Excelの「入力規則」なんかで項目名をマスタから選択するようにしている場合などは別です。このように「後で統計を取ることを意識したデータ構造」だったらcount系関数は多大な効果を発揮すると思います。ですが往々にしてこのような依頼がくるときは、あまり「よいデータ」は提示されないものなのです。



2.自分のやりやすい方法として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なんか薦めると引かれてしまいます。


3.じゃあ間をとってコレなんかどうだろう?

そんなこんなで、依頼者が望むような「Excelで一発でできる方法」ってないのかなと考えるようになりました。
デファクトスタンダードであるMicrosoftOfficeほどのソフトが、手軽にきれいな集計結果を出せるような機能を用意していないものでしょうか。


一応あります。

 



先に答えをいうと定番のピボットテーブルを使うことになるのですが、ちょっと本来の使い方とは言えないかもしれません。ですがこれが今最も求めるものに近いのではないでしょうか?以下、手順を書きいておきます。

(1)「挿入」⇒「ピボットテーブル」⇒範囲:件数をほしい項目列(ここでは"名称"の列)を選択
(2)"名称"を行ボックスと列ボックスへドラッグ&ドロップ

f:id:kojikoji75:20150815164727p:plain

(3)一旦生成された結果をコピーし、別シートに貼りつけて加工(ソートや装飾)。完了。


ピボットテーブルなので当然生成される結果はこちらが避けたがっている「独特の書式」ではあるのですが、「小計」とは違って別シートに貼り付けて活用できます。



4.まとめ

エンジニア視点でみると、Excelの標準機能を使った場合、「小計」にしろ「ピボット」にしろ生成される結果が"独特の書式"をもってしまうため、「そのソフトに依存する」という部分で敬遠してしまいがちです。しかし、ユーザのことも考えた場合、ある程度寄り添うことも求められます。

ピボットテーブルを少し邪道的に使ったやり方だと、得たい形に近い結果を得ることができ、それを別シートにコピーすることで"独特の書式"を捨てることも可能なようです。

ということで、ExcelだけでSQLのGROUP BY相当の集計結果を出すベストプラクティスは「ピボットテーブルを邪道的に使う」という結論に個人的には落ち着いておりますが如何でしょうか?


あわせて読みたい

kojikoji75.hatenablog.com
kojikoji75.hatenablog.com
kojikoji75.hatenablog.com



SQL実践入門──高速でわかりやすいクエリの書き方 (WEB+DB PRESS plus)

SQL実践入門──高速でわかりやすいクエリの書き方 (WEB+DB PRESS plus)

たった1日で即戦力になるExcelの教科書

たった1日で即戦力になるExcelの教科書