今日は、Excelのすごく役立つピボットテーブルの実用法をご紹介します。
目次を使って読みたい項目へ
ピボットテーブルでオリジナルの割合を出したい時ってあるよね。
ピボットテーブルで思うように割合が出せないんですよ。
コレじゃだめなの？
でも、この割合ってこうじゃなきゃおかしいんですよね。
コレだと何度やってみても上手くいかないね。
やり方変えるしか無いのかな。
また何かお困りごとかな？
どれだけ地獄耳で暇なんですか？
暇じゃないからね。手が空いてたって言ってくれ。
ツルトロさん。ちょっと待って
夫婦漫才は良いから。ちょっと来て下さいよ。
何よその昭和ボケしたツッコミは！！
冗談は置いといて、何をそんなに困ってたんだい？
じゃあ、まずは話をまとめてみよう。
ピボットテーブルでやりたいことをまとめてみる
今回のやりたいことをまとめてみましょう
前提として、会社の売上の管理をするために、社員1人1人の業績をチェックする必要があるんです。
そのためには、進捗状況をチェックするために、会議のたびに進捗率を管理する必要があります。
それを簡単に実化するには、ピボットテーブルを使った方がいいだろうと思った長谷川さんでしたが、どうやら上手く行かないようですね。
そこで、どうしたいのか改めて目的を整理してみましょう。
- 売上の目標額を1人ずつ決める
- 1ヶ月を1サイクルとして、中間と結果の進捗率を自動的に計算する
- 1年間以上のデータを簡単に貯めておけるようにする
まず、どうして長谷川さんは、「売上金額の進捗管理」をピボットテーブルで管理しようと考えたと思いますか？
答えは、その方が《進捗率の管理が簡単》だからです。
ピボットテーブルで管理するときのポイントは、基の表を「テーブルとして書式設定」機能を使って作ることです。
テーブルとして書式設定をする方法はコレを読んでみよう
↓ ↓ ↓ ↓ ↓
まずベースになる表を用意しました。
今回は、今後も使える売上管理用のダミーデータで作っています。
ランダム関数を使って売上の金額を自動的に設定してくれるように作りました。
まずは、《売上進捗分析のファイル》をダウンロードしてください。
ブックの作りを整理します。
《テストデータの生成》シートは、大元になるデータです。
100人分のデータを12ヶ月分(1,200行分)のデータを用意しました。
ランダム関数で金額が変化するように作ってあります。
《売上管理データ》シートは、ピボットテーブルに利用する大元のデータです。
《テストデータの生成》シートの内容をコピペして、値のみ貼り付け
「テーブルとして書式設定」機能で表にしています。
《売上ピボット分析》シートは、《売上管理データ》シートのテーブルを基にピボットテーブルを作りました。
そして、今回の目的は、「目標金額」に対して「中間金額」と「達成金額」の割合を自動で計算することです。
これは、例えば月に2回ほど会議があったとしましょう。
今月の売上目標ですから、今月の○○日でどれくらい達成出来ているのかチェックして、今後の対策を考える為の資料として使います。
テーブルやシートで割合を出さずにピボットテーブルで割合を出す理由
ここで1つ疑問が浮かびませんか？
それは、どうしてテーブルやシートでも割合は出せるのにそうしないのか？ってことなんです。
答えは、データを増やしたときに面倒なんです。
例えば、1ヶ月分増やしたとします。つまり1月分が終わって次の2月分を作る時です。
人数は、100人なので100行増やします。
基本的なデータ(日付や氏名)は、オートフィルやコピペを使えば簡単です。
もちろん。前もって関数を設定しておけば、コピペで済むので同じように思えますよね。
ところがです。現実的に考えてみて下さい。
社員やアルバイト・パートの人って退社したり入社したりしますよね。
つまりデータの増減があるということです。
その上、入力をサポートするために「シートの保護」を掛けることが作り方として望ましいんです。
そうすることで、誰かが間違って「計算式」を消してしまうことを防ぐことが出来ます。
ところが、またここで問題が発生します。
シートの保護。いちいち解除しないと数式のコピーが出来ないんです。
どこまで数式が設定されているか見つけるのも意外と手間です。
あ～。面倒くせ。 結構、大変ですよね。
私は、この手間は掛けられません。もう人に押し付けちゃいます。
そこで登場するのがピボットテーブルです。
では早速、ピボットテーブルを使った「簡単割合計算の設定方法」を教えちゃうぞ。
ピボットテーブルで思い通りの割合を自動計算してもう方法
気を取り直して、ピボットテーブルで割合を自動計算してもらう設定方法を説明しよう。
Step1 集計フィールドを追加する
①「ピボットテーブルツール」→「分析タブ」→「計算方法グループのフィールド／アイテム／セット」をクリック
②集計フィールドの挿入ダイアログ画面で、名前のテキストボックスに「中間割合」と入力
数式のテキストボックスに【=中間金額／目標金額】と設定する。
※数式のテキストボックスには、「／記号を「半角」で入力して下さい」
③②と同じ手順で、集計フィールドの挿入ダイアログ画面で、名前のテキストボックスに「最終割合」と入力
数式のテキストボックスに【=達成金額／目標金額】と設定する。
Step2 追加された集計フィールドを整理する
ここまでくれば、もう出来たも同然です。
①「中間割合」と「最終割合」の項目が追加されたのが分かりますね。
フィールドリストの値の最終行に追加されているので、「中間割合」を「中間金額」の下に移動しましょう。
②移動すると割合の表示が、それぞれ通貨表示になっているので、「値のフィールド設定」の「表示形式」を「パーセンテージ」にそれぞれ変更しましょう
③お疲れ様でした。これで完成です。
今回は、ランダムな金額で設定しているので、割合のバランスが悪いですが実際に使ってみるとしっかりと計算してくれますよ。
これなら基のデータに追加や変更があっても更新をかければ、自動的に計算してくれるようになったよ。
本当に「たまには」役に立ちますね。
たまに。はないだろ。「そこそこ」ためになる話をしてもらってるじゃないか。
おら、お国に帰るだ。
今日はありがとうございました。
とっても助かりました。
でも、ピボットってやっぱりすごいですね。
これで面倒な関数の設定をしなくても数字を入れるだけで自動的に整理してくれるわけですよね。
このデータベース＋ピボットテーブルは、実は結構万能なんだよ。
ただ、今回みたいな比較的マニアックな内容は、なかなか教えてくれる人も少ないから、今回は、俺も勉強になったよ。
それじゃあまた、困ったときに来てくださいね。
じゃあ、用が済んだらササッと退散しようかな。
何かあったら、連絡してね。それでは、さらば！！
本人達は、自分たちの関係をどう思ってるんだろ？
う～ん。コッチはコッチで気になる。
まとめ
いかがだったでしょうか？
ピボットテーブル奥が深いですね。
変な名前なのに結構役に立つんです。にくいねコノコノ。
そして、もう一つ抑えておきましょう。
それは、『フィールド名と計算式の変更方法』です。
コレは至って簡単。集計フィールドを追加した手順を辿って「名前」と「計算式」を変更して、更新してあげるだけです。
以上で実は奥深い。ピボットテーブルを活かした管理術の紹介でした。
最後までお読みいただきありがとうございました。
この記事が、少しでもお役に立てたら幸いです。
分かりにくい点があれば、気軽にコメントして下さい。改良頑張ります。
ご意見もいただけると励みになります。 それではまた、お会いしましょう。 じゃあね。
コメントを残す