今回は、Excelを使った集計方法についてのお話です。
膨大なデータを正確に素早く集計することはまさにExcelの醍醐味と言えますが、データが散らばっていたり形式が統一されていなかったりすると、集計方法にちょっとした工夫が必要になります。
では、その大変な集計作業をExcelを使ってサクッと仕上げてしまいましょう。
羅列した膨大なデータからの集計
一例として、50品番ある商品と、その売上個数が以下のように羅列されているとします。
B列：品番1001～1050
C列：売上個数
データ量：1000件
このデータを使って品番ごとに何個売れたかを集計し、売上個数の多い品番順に並べ替え、「売れ筋トップ10」を調べたいと思います。
さて、ここで問題です。
どういう方法で集計すれば「効率的に集計できる」と思いますか？
そうですね。
品番ごとに集計をしたいので、品番をキーに並べ替えると集計しやすくなりますね。
でも、その解答ではまだ40点ぐらいです。
いいえ、100点満点中です。
では解説していきましょう。
品番ごとオートSUMで集計はできるけれど…
悩さんが回答されたように品番をキーに並べ替えると以下のようになります。
品番は1001から1050番までの50個あり、「品番」をキーにして並べ替えてみると品番によって行数が異なることがわかりますね。
上図では、1001という品番が11個（行）あるのに対し、1002という品番は2個（行）しかなく、品番によってデータ数が違いますね。
では、更に質問です。
品番ごとにデータ数が異なる状態で「品番ごとに集計」する場合はどのような方法で行えばよいでしょうか？
えーと、品番ごとにオートSUMを使って足し算するのはどうでしょう！
なるほど。
例えば品番1001の場合、1001の最終行は12行目だから、セルD12にオートSUMを選択し、売上個数の範囲を選択する方法ですね？
計算機で計算しなくてもExcelが足し算してくれるから、正確で素早いです！
確かにこの方法であれば品番ごとに集計できますが、品番によってデータ数が異なるので数式をコピペすることができません。
そのため、品番ごとに毎回オートSUMをクリックして、売上個数を範囲選択する手間が発生します。
品番の数が1000個なら、1000回オートSUMを設定しなければなりませんので、データ量が多ければ多いほど大変な作業となってしまいます。
何か良い方法はないですか？
Excelに不可能はござらぬ！！
品番ごとに最終セルを取得する
手作業でオートSUMを設定しようとしたことを、Excelで簡単にやる方法をお伝えしますね。
まず、同一品番の最終セルを求めるためにIF関数を使います。
IF関数については過去記事『文系でもExcelが得意になれる | IF関数を国語的解釈してみたらとってもわかりやすい件』を参考にしてくださいね。
IF関数をどのように使うかというと、
A列に、B列の品番がひとつ下のセルの品番と同じなら「スペース」、そうでなければ「最終」と表示させます。
つまり、ひとつ下のセルと品番が違うということは、そのセルがその品番の最終セルになるということなので、そこで「最終」と表示させるわけです。
ものは試しで、セルA2に、「=IF(B2=B3,””,”最終”)」と入力してみましょう。
セルA3以降にもこのIF関数をコピペすると、
各品番の最終行に「最終」と表示されました。
でも、最終行が分かったところで、オートSUMを設定するのは、やっぱり手作業になるのではないでしょうか？？
いいところに気づきましたね。
ここでもまたIF関数の出番です。
品番ごとに売上個数の累計を取得する
Ｄ列に各品番の売上個数の累計を求めていきます。
IF関数を使い、1行ごとに、品番が変わるまで売上個数を加算させ、値を積み上げていくのです。
まず先頭のセルD2は、ここから累計をスタートさせるので、C2の値をそのまま取得させますよ。
D3以降のセルには、B列の品番がひとつ上のセルと同じなら「D列の累計の値に加算」し、品番が変わったなら「その行の売上個数の値を取得」します。
実際にIF関数を入力するとわかり易いですよ。
2行目のデータ、セルD3に「=IF(B3=B2,C3+D2,C3)」と入力してみましょう。
「品番が変わるまでひとつずつ足し算し、品番が変わったところで、また計算を新たに始める」っていうことですか。
そういうことです。
このIF関数をD列の各セルにコピペすることで、A列の「最終」と書かれた行に各品番の「累計」が表示されることになります。
必要なデータだけを抽出する
さあ、ここからは抽出作業に入りますよ。
抽出に必要なのは「最終」と表示された行だけです。
どうやって抽出したらよいでしょうか？
大正解です！！
A列にある「最終」をオートフィルターで抽出すればいいですね。
これで品番1001～1050まで集計できました。
売上個数を降順で並べ替える
トップ10を調べるために、品番と集計結果だけを別ファイルに置き換えましょう。
まず、C列を非表示にします。
D列には関数が入力されているので、この状態では並べ替えはしないでくださいね。
B列とD列をコピーし新規ファイルに貼り付けます。
新規ファイルに貼り付けることで、集計の値のみが貼り付けられます。
さあ、悩さん。
あとは分かりますね？
その通りです！
よくできました。
関数とExcel機能の組み合わせで、効率的に集計をしましょう
数字を扱うExcelでは、多くのシーンで集計作業を必要とします。
必要となる集計データをどうやって取得するか、関数とExcelの機能を上手に組み合わせて、効率の良い方法を導き出しましょう。
特に、今回のようなIF関数の使い方は覚えておくと便利ですよ。
で、時間を掛けずに急所を減らすことができます。
【厳選】お勧めのエクセル関連書籍のご紹介
私が今まで読んできた中で、とても役に立ったエクセル書籍をご紹介します。
エクセルは業務効率のために開発されたソフトであるため、使いこなせないとその恩恵を十分に享受することができません。
少しでも業務効率を上げたい方は、是非ご覧になってみてください。
ビジネスエリートの「これはすごい！」を集めた 外資系投資銀行のエクセル仕事術－－－数字力が一気に高まる基本スキル
私の職場ではコンサル出身の上司がいたこともあり、この本は非常に役立ちました。
「仕事は早く、完璧に」という永遠の課題に取り組む上で、読んでおいて損はないと思います。
中級者以上の方にお勧めですね。
こちらはエクセルのコツを学びながら、数字力を磨くことができる良書。
エクセルの基本操作がカバーされているので、初心者でも飽きずに読破することができると思います。
ちょっと他の本と趣向が異なるので、お勧めです。
関数が苦手、という方にうってつけの書籍です。
関数は食わず嫌いの人が多いですが、すぐに使いこなせなくても、「どういうことができるのか」ということを知っておくだけでも大きな価値があります。
エクセルの場合、その方法を知っているかどうかで業務時間に雲泥の差が生まれることがよくあるので、是非読んでいただきたいですね。
どんなデータでもExcelが計算してくれるならありがたいです。