かっこいいSUMIF・SUMIFS・COUNTIF・COUNTIFSよりも、かんたんなピボットテーブル

  • URLをコピーしました!

Excelで覚えるスキル、磨くスキルは少ないにこしたことはありません。
いろんなスキルを使えたほうが「かっこよさ」「仕事ができる感」はありますが、名より実をとりましょう。
関数よりもピボットテーブルのほうがかんたんで楽です。
EX IT
※Keynoteで作成

SUMIF・SUMIFS・COUNTIF・COUNTIFS 使えるとかっこいい?

こういったExcelの売上データがあり、
EX IT 1

店舗別に集計する場合、関数でやるならば、SUMIF関数を使います。
店舗のリストを準備して、
EX IT 3

関数を入力し、
EX IT 4

コピーすれば完成です。
ただ、リストの準備、関数の入力に手間がかかります。
入力も考えながらやるので、それほど簡単ではありません。
EX IT 5

さらに店舗別月別に集計するなら、SUMIF関数ではなく、SUMIFS関数を使う必要があります。
EX IT 6

こういった関数です。
手間がさらにかかりますし、効率よく入力するなら、複合参照($マーク)をつかいこなす必要があります。
EX IT 8

店舗別月別に集計するなら、さらに大変です。
EX IT 11

こういった日付データから月別に抽出して集計するには、一工夫しなければいけません。
1つの方法としては、たとえば、セルO2に「=MONTH(A2)」という数式を入れ、全体にコピーすれば、月を入れることができるので、
EX IT 9

それをSUMIFS関数で利用して集計します。
EX IT 10

店舗別月別種類別に集計するならば・・・・関数でサンプルを作るのも大変です。
(割愛します)

売上金額ではなく、件数でカウントしたい場合は、関数をさらに使わなければいけません。
店舗別なら、COUNTIF関数、
EX IT 12

店舗種類別なら、COUNTIFS関数です。
EX IT 13

関数をたくさん使うと、かっこいいかもしれませんが、手間がかかります。
確かに一度作ってしまえば、楽になるかもしれません。

ただ、
・店舗が増えた
・商品種別が増えた
・次の月(7月)のデータを追加した
などといった場合は、表も変えなければいけません。

せっかくの売上データなので、もっと集計したくなる場合もあります。
・年齢別に集計
・商品別に集計
・店舗の地域別に集計
・年別に集計
・曜日別に集計
・四半期別に集計

そのたびに、表を変更しなければいけません。
結局は、変更するのが大変だからさらなる集計をしないという選択になりかねないのです。

集計はピボットテーブル1本でいいのでは?

SUMIF、SUMIFS、COUNTIF、COUNTIFSなどを使いこなせるようになるよりも、ピボットテーブルを使うほうが楽です。
仮に使いこなせたとしても、関数を毎回入力するのは嫌です。

集計はピボットテーブルを使えば簡単にできます。

EX IT 14

動画を作ってみました。

サンプルはこちらです。
EX-ITサンプル SUMIFSUMIFSよりピボット.xlsx

①データを選択して、Ctrl+Tでまずテーブルにします。

EX IT 15

EX IT 16

②リボンの[挿入]→[ピボットテーブル]→Enterまたは、Alt→N→V→Enter
(私は、クリックアクセスツールバーに設定し、Alt→1→Enterで使っています)

EX IT 17

③右側から、集計したい[店舗]をクリック

EX IT 19

④[金額]をクリックすると、店舗別集計が完成です。
EX IT 20

⑤ピボットテーブルをコピーして、
EX IT 21

⑥右側のボックスから[種別]をドラッグすれば、店舗別種別集計が完成です。
(動画はクリックになっていますが、ドラッグでやったほうが確実です)
EX IT 22

⑦再度コピーして、右側のボックスで[種別]をクリックしてチェックを外し、[日付]をドラッグします。
動画で使っているExcel2016(Office365も同様)なら、月別にまとめてくれるので、これで店舗別月別集計が完成です。
EX IT 24

月ではなく、日付になってしまう方は、[月]をドラッグするか、グループ化という機能を使ってみてください。
【関連記事】Excelピボットテーブルで、日々のデータを、年月別・期(事業年度)別に集計する方法
リンク

⑧件数を集計するなら、[金額]を再度ドラッグしたあと、クリックして[値フィールドの設定]→[個数]を選べば
EX IT 26
金額も件数も集計できます。

EX IT 27

別の表にしてもかまいません。

サンプルでは、同じシートに複数の集計表を入れましたが、シートごとに分けても大丈夫です。

データを更新したら、ピボットテーブルを右クリックして[更新]またはピボットテーブルを選択してAlt+F5で更新しましょう。

関数だと、自動的に表が更新されますが、ピボットテーブルは手動で更新しなければいけません。

同じ範囲のデータを元にしたピボットテーブルなら、1つのピボットテーブルを更新すれば他のピボットテーブルも更新されます。

また、ピボットテーブルの魅力は、様々な集計をかんたんにできることです。
・年齢別に集計
・商品別に集計
・店舗の地域別に集計
・年別に集計
・曜日別に集計
・四半期別に集計
などといったものを考えながら分析できます。
表が固定されていたら柔軟な分析ができません。
これってどうだろう?と集計してみてはじめてわかることも多いです。

『インストラクターのネタ帳』管理人でMicrosoft Mvp For Excelの伊藤さんもこう書かれていました。
SUMIF関数よりピボットテーブルを使いましょう:Excel(エクセル)の使い方-ピボットテーブル・ピボットグラフ

考えていただきたいのが、7月になったらどうするのか、という点です。

このシートをコピペして7月用のシートを作成するのでしょうか?(是非やめてください)

データが溜まってきたときのこともイメージしてください。

科目ごとに集計するだけでいいのでしょうか?

科目ごとにどう変化したのかを比較したくなりませんか?(是非したくなってください)

 
私は会計データもピボットテーブルで集計しています。
会計ソフトが準備した表だと限界があるからです。

かっこいいExcel関数よりも、かんたんなピボットテーブル

Excelで効率化できているかの基準は、関数を多く知っているかではありません。

効率化できれば、その方法がすごいかどうか、かっこいいかどうかは関係ないものです。
苦労して資料を作ったほうが、仕事ができる感がありますし、関数がたくさん入っているExcelファイルのほうがすごさがあります。

でも、そんなすごさは必要ありません。
ピボットテーブルでさっさと仕事を片付けて、別のことをやることを選びます。
(ある意味ピボットテーブルもかっこよくてすごいのですが)

関数はめんどくさいですし、使わないにこしたことはありません。
今回のサンプルを作る際も泣きながら関数入れていました。。。

教える場合も、関数のほうが、すごいものを教えている感がありますが、私は「ピボットテーブル、かんたんにできるよ」と伝えることを選んでいます。
(関数のほうが伝わりやすいのは事実ですが)

ピボットテーブルを使ったことがない方は、ぜひ動画とサンプルで作ってみていただければ。





【編集後記】
2017年2月の東京マラソンはコースが変わって、東京駅ゴールになります。
(それまでは東京ビックサイト)
走ってみたいので、プレミアムメンバーで申し込み。
4,320円の会費がかかりますが、3回抽選してもらえます。

以前、プレミアムメンバーで申し込んだときは結局外れましたが・・・。

【昨日の1日1新】
※詳細は→「1日1新」

Webサービス クーピック
亀戸餃子

  • URLをコピーしました!