奮闘記(9)マルチピボット

実務の分析を向上させる「マルチピボット」を紹介します!

今回は、ピボットテーブルの分析向上の手法の紹介です。
その中でも、ピボットテーブルを2回使う「マルチピボット」をご紹介します。
『ピボットテーブルからピボットテーブルへ』データを転用します。
乾電池に例えると、
ピボットテーブルのレポート数を多くする=並列 、電圧は同じです、
ピボットテーブルをデータソースに用いる=直列 、電圧が高くなる、
直列的にデーター分析を強化するのが「マルチピボット」です。

皆さんは、ピボットテーブルが出来上がると「完成」ですね。
それで「完了」ですか?完了は、自分の実務が完了したときですね。
「表」を作っても、レポートの内容を吟味したりします。

ピボットテーブルで、データーを深く分析する

①表のレイアウトを変更する・・クロス集計表、推移表、ダイス分析
②デ-ターの絞り込み[フィルター]、明細の展開
③グルーピング

グルーピング
グルーピング

④値フィールド
    [集計方法:合計、個数、平均、最大、最小・・]
    [計算の種類:総計に対する比率、基準値との差分、累計・・]
⑤集計フィールド
    [加重平均など、自分で数式を設定]

ピボットテーブルでデーターを強化する

⑥管理用の項目を追加する方法

③グルーピングと似ていますが、グルーピングを追加すると、自動的に他のピボットテーブルにも追加されます。これで困ることもあります。また、グルーピングの追加や削除も煩わしいです。

項目の追加で、管理を充実させる
項目を追加するときに、新しいテーブルを用意します。

テーブル
テーブルの用意

ピボットテーブルを、VLOOKUP関数で検索し、
項目を追加するようにします。

VLOOKUP関数
VLOOKUP関数で検索

元のテーブルの右側にピボットテーブルで、表示して、
追加や見直しをしてください。

マルチピボット『ピボットテーブルからピボットテーブルへ』
= 新データソース

⑦予算と実績の管理分析方法

皆さんは、予算と実績があるデータソースをクロス集計します。
それで集計できますが、予算と実績の比較するには、どうしますか。
先ず、右側の列に、比較用の計算ができるようにして作表しますね。

数式で手計算
数式で手計算


問題は、担当者や年月などいろいろな選択条件で作表しようとすると、その都度表を直さなければなりません。
私の以前いた会社では、予算と実績の計算が完了した専用のデータベースを用意し、ピボットテーブルで作表をやらせていました。
でも、このやり方では、元のデータがあって分析できますが、必ずしも使い勝手は良くないです。

そこで、分析用のデーターソースを、ピボットテーブルのレポートを
加工して用意して、分析が自由にできるようにします。

マルチピボット=予実管理
『ピボットテーブルからピボットテーブルへ』データ転用
マルチピボット・予実管理

そのままでは、ピボットテーブルのデーターソースにはなりません。
当然、「データテーブル」も使えません。
範囲名を使って、空白などの不要なデーターを除外して分析しています。

次の手順で作業します
①行フィールドに、項目(アイテム)を配置する
②予算と実績を列フィールドに配置する
③行の空白を埋める・・ピボットテーブル・デザイン/
      レイアウトで、アイテムのラベルをすべて繰り返す。
④分析をする項目を数式を追加する
  ・実績ー予算 などの差異計算
  ・単価差、数量差 などの要因分析・・IF関数を使う必要が出てきます。
⑤データソースの範囲を確定・・名前で管理をお薦めします
⑥ピボットテーブルで作表
⑦ラベルフィルターで、空白行などの不要データーを除外します。

不要データーの除外
不要データーの除外

このように、マルチピボットで新たなデーターソースが使えるようにすると、分析のレベルが向上します。
専用の予算実績解析が可能になりました。
分析手法の詳細は、別の機会にご紹介します。「管理会計」などで考え方は、紹介されていますが、Excelを用いて実践できますので、是非、機会があればこちらの勉強もお薦めします。

最後に、データソースの件数が多くなると、どうしても対策が必要になります。
VLOOKUP関数や数式をデータテーブルの構造化参照で使えれば、マクロは使用しなくて済みます。
しかし、データテーブルが使えない場合には、数式のコピー、値複写を行います。
毎回手作業をするのは大変なので、マクロを使った方が楽になります。