仕事柄、ユーザから管理台帳ライクなExcelファイルを突然見せられて「このExcelデータで挙がっている項目の件数TOP10を出したいんだけど…」的なことを相談されることがときどきあります。
そんなときにとり得る選択肢はいくつかあると思いますが、ベストプラクティス……というか、相手のことも考えた”妥協点”は一体どこなのでしょう。一度は考えておく必要があると思ったので今さらながらまとめておきます。
Excelをもっと使いたおすために
この記事ではExcelのかいつまんだ機能について紹介していますが、Excelをもっと使いたおすための効果的な学習方法については以下にまとめています。ご参照ください。
この記事の前提
まず、前提として今回の話はよくある話として「項目ごとの合計」と「項目の件数（個数）」を求めたい場合です。
渡されたExcelファイルは下記のようなものとします。
また、得たい結果は下記のようなものとします。
①数量の合計
②出現回数（項目の件数）
これらの結果を得るためにどういった方法がよいか検討していきましょう。
方法１：Excelの標準機能を使う
Excelで渡されたものなので、Excelの標準機能を使い、依頼者の目の前でパパパッと解決したいところです。依頼者もおそらくそれを期待していると思います。
ですが、単純にExcelの機能を使うと、意外かもしれませんが思い通りの形で結果を得るのはなかなか困難な印象があります。
小計機能（合計・個数） → NG
例えば「小計」機能の場合を考えます。
これは「項目ごとの合計」を求める場合も、「項目の件数（個数）」を求める場合も使えます。
手順としては、上部メニューの「データ」タブ→「小計」を選択するという手順となります。
ただし、小計を使うと「途中に行が挿入される」「アウトラインができる」などと余計なモノが入ってきてしまい、元のデータとはかけ離れた姿になってしまいますね。（また、正確にいうと事前のソートも必要です。）
ここでは合計値を算出していますが個数も算出できます
百歩譲って集計行だけをコピペして加工したら活かせそうかな、と思ったら……
別シートに貼り付けると……
こんな感じで、「小計」は結構早い段階で「避けたい方法」と判断されます。
sumif関数（合計） → NG
次に、例えば「sumif関数」の場合を考えてみます。
これは指定した文字列ごとにもつ値を合計してくれるという便利なものではありますが、「現在リスト中に存在する品目全てを自動で抽出してグループ化してくれる」という機能はありません。
あくまで検索文字列は自分で指定する必要があります。なので、「一発で簡単に」を求める依頼者にこの方法を薦めるとあまりいい顔をされないでしょう（私の経験上はそうでした）。
countif関数（個数） → NG
また、例えば「countif関数」の場合を考えてみます。
これは指定した文字列の存在件数をカウントしてくれるという便利なものではありますが、sumif関数と同様に「現在リスト中に存在する品目全てを自動で抽出してグループ化してくれる」という機能はありません。
sumif関数と同様にあくまで検索文字列は自分で指定する必要があります。
方法２：AccessにインポートしてSQLのGROUP BYを使う
お題に沿っていない反則技ですが、Excelが使えるということはAccessを使える環境である可能性も高いはず……ということで一応候補として挙げておきます。
個人的には一旦Access等のDBにインポート→SQLをたたいて返った結果をExcelに貼りつけて渡す（または目の前で実践して見せる）、という手法がベストだと思っています。
これが個人的にはラクでもあり、大量データにも耐えられ、欲しい結果の形を自在に変えられる、そして自分的にも多少の満足感が得られる、といったメリットがあるからです。
経験上、業務で求められるSQLは下記のような簡単なもので事足りることがほとんどです。また、仮に複雑なものを求められた場合はそればそれで達成感があるというものです。
項目ごとの合計値を出すSQL
select
max(名称) as item, sum(数量) as quantity
from
t_sample
group by
名称;項目ごとの個数を出すSQL
select
max(名称) as item, count(*) as cnt
from
t_sample
group by
名称
order by
count(*) desc;人によっては、「DBにインポートするなんて余計な手間」という人もいるかもしれませんが、慣れている人にとってはこの方法がラクで早いんです。何より「得たい結果の形をいくらでも変えられる」という、かゆいところに手の届く安心感が一番大きな魅力です。
ただ、このような方法をとると依頼者から「あーExcelとかで簡単にやりたいのにな…」という顔をされることがほとんどです。
このような依頼者のほとんどは「一発で簡単にできる方法」を求めているため、SQLを薦めると引かれてしまいます。
また、依頼者のPCに必ずAccess等のDBソフトが入っているとも限りません。
ポチップ
方法３：間をとってピボットテーブルを使う
そんなこんなで、依頼者が望むような「Excelで一発でできる方法」ってないのかなと考えるようになりました。
デファクトスタンダードであるMicrosoft Officeほどのソフトが、手軽にきれいな集計結果を出せるような機能を用意していないものでしょうか。
そんなことはありません。Officeにも一応そんな機能あります。
先に答えをいうと定番の「ピボットテーブル」を使うことになるのですが、ちょっと本来の使い方とは言えないかもしれません。ですがこれが今最も求めるものに近いのではないでしょうか？
以下、手順を書いておきます。
ピボットテーブルで項目ごとの合計を求める
- 「挿入」⇒「ピボットテーブル」⇒データの範囲指定
- “名称”を行ボックスへ、”数量”を値ボックスへドラッグ＆ドロップ
- 結果を別シートにコピペしてから好きな感じに整える
ピボットテーブルで項目の個数を求める
- 「挿入」⇒「ピボットテーブル」⇒データの範囲指定
- “名称”を行ボックスと値ボックスへドラッグ＆ドロップ
- 結果を別シートにコピペしてから好きな感じに整える
ピボットテーブルなので当然生成される結果はこれまでできるだけ避けてきた「独特の書式」ではあるのですが、「小計」機能とは違って別シートに貼り付けるたあとは加工がしやすいです。
ポチップ
まとめ
エンジニア視点でみると、Excelの標準機能を使った場合、「小計」にしろ「ピボットテーブル」にしろ、生成される結果が”独特の書式”をもってしまうため、「そのソフトに依存する」という部分で敬遠してしまいがちです。ですが、ユーザのことも考えた場合はある程度寄り添うことも求められます。
ピボットテーブルを少し邪道的に使った上記のやり方であれば、得たい形に近い結果を得ることができ、それを別シートにコピーすることで”独特の書式”を捨てることも可能なようです。
ということで、ExcelだけでSQLのGROUP BY相当の集計結果を出すベストプラクティスは「ピボットテーブルを邪道的に使う」という結論に個人的には落ち着いておりますが如何でしょうか？
【追記】PythonのPandasが超絶便利
最近であればPythonの定番ライブラリ「Pandas」を使うという選択肢もありかと思います。
例えば以下のようなコードで簡単にSQLのGROUP BY相当のことができます。
mport pandas as pd
import openpyxl
#csvファイル読み込み
df = pd.read_csv('d:\sample.csv', encoding = 'utf-8')
# DataFrame側で抽出操作
df2 = df.query('subject=="Science" and sex=="male"')
# df_db2.sum(axis=0) で全カラムも可
sum_score = df2['score'].sum(axis=0)
# 既存のExcelへ書き出し
wb = openpyxl.load_workbook('d:\output.xlsx')
sheet = wb['Sheet1']
sheet['A1'] = sum_score
wb.save('d:\output.xlsx')
この辺の記事で詳しく解説していますよ。
ポチップ
本ブログでは業務に役立つ技術情報をこれからも発信していきますので、困った時にはぜひ参考にしてみて下さいね。
ポチップ
ポチップ
ポチップ
