[ピボットテーブル事例]ピボットテーブルとExcelの機能を組み合わせる

ピボットテーブルで作成した表に、データや数式・関数を追加することで、さらに詳細な分析ができます。
今回はセミナーごとの利益を計算する事例を取り上げます。
スクリーンショット 2012 02 01 8 36 18

データを準備

まずデータを準備します。
セミナーの申込フォームとしてシステムを使っていれば、そのシステムからこのようなデータを取り出すことができます。
集計したい項目を入れておくのがポイントです。
今回は、[日付]、[タイトル](セミナータイトル)、[氏名](参加者名)、金額というデータを準備しています。

※データは、サンプルとして作ったものです。金額はランダムに計算させています。
スクリーンショット 2012 02 01 7 57 02

ピボットテーブルで集計

データをピボットテーブルで集計します。

集計したのは[日付]、[タイトル]ごとの[金額]です。
各セミナーの売上高が分かります。
スクリーンショット 2012 02 01 8 30 21

ピボットテーブル外にデータを追加

このピボットテーブルにデータを追加していきます。
セミナーごとの利益を知りたいので、売上に対する原価を追加していきます。
今回の場合、[会場費]を追加します。
必要があれば、[人件費]や[広告費]を追加してもいいでしょう。
スクリーンショット 2012 02 01 8 30 47

データを入力するとこのようになります。

※会場費のデータもランダムに計算させています。
スクリーンショット 2012 02 01 8 34 17
このデータはVlookp関数で連動させてもいいでしょうね。

さらに[利益]の欄を作成し、数式を入力します。
ピボットテーブルの特性上、ピボットテーブルの数値をマウスで選択して数式を入力しようとするとうまくいかないことがあります。
直接、「=c5-d5」と入力した方が無難です。
スクリーンショット 2012 02 01 8 39 23

※ピボットテーブルをクリックして、[ピボットテーブルツール](Excel上部のリボンに表示されます)→[オプション]→[Getpivotdataの生成]のチェックを外すと、数式内の数値をピボットテーブルからマウスで選択できるようになります。(2010の場合)
スクリーンショット 2012 02 01 8 46 59

数式を全体にコピーすれば、完成です。

スクリーンショット 2012 02 01 8 34 59

この表をピボットテーブルだけで作成することもできますが、かえって手間がかかることが多いです。
特に[利益]の計算は、ピボットテーブル外でやった方が楽だと思います。
ピボットテーブルと一般的なExcel機能を臨機応変に組み合わせてみましょう。

さらなるバリエーションとして、利益率を追加したり、
スクリーンショット 2012 02 01 8 56 41

利益を表すグラフを追加したりすることもできます。
スクリーンショット 2012 02 01 8 57 49

【セミナー情報】
2/19 ピボットテーブル入門セミナー

2/19(日)経理&会計のためのExcelマクロセミナー 入門コース

★経営者向けメルマガ、先行登録受付中です→詳しくはこちらの記事で





【編集後記】
明日、人生初の人間ドックです。
今日の21時から明日ドック終了時まで飲食禁止なのがつらいですね。
今年からまじめにドック行こうと思っています(^_^;)

ブログ村税理士ブログランキングに参加しています。

↓記事がお役に立てたときは、1クリックしていただけるとうれしいです(^_^)

にほんブログ村 士業ブログ 税理士へ
にほんブログ村

【税理士井ノ上陽一Official Website】

自分が退職したときに退職金を出せば、会社の節税にもなり、個人の節税にもなります。退職金が税金上優遇されているからです。 退職金を出せるかどうか 退職金を会社から出せるかどうか? 現実的にはかなり難しいでしょう。 1

○税務会計相談、セカンドオピニオンについて→こちら

○セミナー・研修のご依頼→こちら

○執筆のご依頼→こちら

【Twitter】

井ノ上陽一 (@yoichiinoue)さんの最新ツイート 税理士。MicrosoftMVP【Excel】。3200日以上毎日更新ブログ「EX-IT」管理人。「税理士進化論」発行人。最新刊『毎日定時で帰っても給料が上がる「時間のつかい方」をお金のプロに聞いてみた!』。著書に『ひとり社長の経理の基本』『フリーランスのため...

【メルマガ】

平日に毎日配信! 『税理士進化論』

【著書】

『使える経理帳票―これ一冊でOK! Excel2010/2007/2003』

『そのまま使える経理&会計のためのExcel入門』


■スポンサードリンク

■著書
新版 ひとり社長の経理の基本
毎日定時で帰っても給料が上がる時間のつかい方をお金のプロに聞いてみた!
『ひとり税理士の仕事術』
『フリーランスのための一生仕事に困らない本』
『社長!「経理」がわからないと、あなたの会社潰れますよ』
『そのまま使える経理&会計のためのExcel入門』