ピボットテーブルで損益比較表を作成したい

質問

Aシートに予算、4月～3月の単月データ（行：科目、列：月）
Bシートに前回予想、4月～3月の単月データ（行：科目、列：月）
Cシートに今回予想、4月～3月の単月データ（行：科目、列：月）

ピボットテーブルでこの３つのシートを参照し、複数のシートを月ごとに比較し、比率と差額をピボットテーブル上でつける。

このようなことはピボットテーブルの機能でできますでしょうか。

なお、複数のシートを統合する方法は、下記の方法でわかっているのですが、
比較する（並べる）方法がわかりません。
http://www11.plala.or.jp/koma_Excel/contents6/mame6040/mame604001.html

　　　　　　　　4月　
予算　　前回予想　今回予想　差額　比率

よろしくお願いいたします。

回答No.1

keithin · ベストアンサー

今のデータからでは，「複数シートを統合する方法」では並べることはできません。

方法１：
添付図（作成後のレポート）のように元データを直してしまいます。

＞Aシートに予算、4月～3月の単月データ（行：科目、列：月）

この１行目を「予算４月」「予算５月」…と
同様に前回予測の４月以降も「前回４月」「前回５月」…
今回予測の４月以降も「今回４月」「今回５月」…
のように。

その上で「複数シートのワークシート範囲」からピボットテーブルレポートを作成し，さらにピボットテーブルレポートの数式から「集計アイテム」を列に追加して
名称　比較４月
数式　=今回４月-前回４月
名称　比較５月
数式　=今回５月-前回４月
　：
などのように追加していくことで，一応ご希望の通りの数字を並べることができます。

＃ただし「実際に」手を動かして複数シートからのピボットテーブルレポートを作成したことがあるなら，

＞　　　　　　　　4月　
＞予算　　前回予想　今回予想　差額　比率

といった具合には作成できないことも判りましたね？

方法２：
ピボットテーブルレポートでも，また統合などの機能を使ってでも「科目の一覧」をまず作成し，各シートからふつーに関数で値を参照して集計表をとりまとめてしまうのでも，そこそこに結果は得られます。

方法３：
全体のデータを(適宜数式等を利用して)再配置し
Ａ列　科目
Ｂ列　月
Ｃ列　区分　（予算/前回予測/今回予測)
Ｄ列　数字
といった具合の４列データに切り直してしまえば，完全にご希望通りの配置でピボットテーブルレポートで集計させることもできます。

この場合も「差分」は「区分」フィールドに対して集計アイテムを追加して表示させますが，この方法では前述の方法１と違って
名称　差分
数式　=今回-前回
のように一個作成するだけで，一発で毎月の比較が現れます。

回答No.2

imogasi

こういう課題は、エクセルの「表の組み換え」です。関数では複雑になりますが、VBA向きの課題です。
ビジネスや事務ではこういうニーズはよくあり、エクセルをビジネスで使うには、わたしはVBAが出来ることが必須という持論をよく書いています。
A.表の様式の組み換え
B.多数の表を対象にする
の場合が有用です
コードは、変換の規則性があるので、そんなに複雑になりません。参考までに挙げておきます。
Sub test01()
Dim sh1, sh2, sh3, sh4 As Worksheet
Set sh1 = Worksheets("予算")
Set sh2 = Worksheets("前回予想")
Set sh3 = Worksheets("今回予想")
Set sh4 = Worksheets("予算比較")
x = 2 '４月予算は第２列から始まる
d = 6 '勘定科目の最終行を（この際は仮定）
For j = 2 To 2 + 12 '12月分列の繰り返し、J=1は見出し列
For i = 3 To d '勘定データ行だけ繰り返し
sh4.Cells(i + 1, x) = sh1.Cells(i, j)
sh4.Cells(i + 1, x + 1) = sh2.Cells(i, j)
sh4.Cells(i + 1, x + 2) = sh3.Cells(i, j)
Next i
x = x + 5 '予算比較シートでは、１か月分は5列ー＞月ごとに、5 列右へずらしてポイント
Next j
End Sub
ーーー
例データ　
予算シート
a、ｃ、ｃ・・は勘定科目
A2:E5　右部分は省略
ー 4 5 6 7　＜－月数字
a 1 2 3 4
b 1 2 3 4
c 1 2 3 4
・・・
前回予想シート
A2:E5　右部分は省略
ー 4 5 6 7　＜－－月数字
a 11 12 13 14
b 11 12 13 14
c 11 12 13 14
今回予想シート
A2:E5　右部分は省略
ー 4 5 6 7　＜－－月数字
a 21 22 23 24
b 21 22 23 24
c 21 22 23 24
＝＝＝＝
結果
予算比較シート　A2:K6　予算 前回予想などの項目のため１行増えている
4 5
ー 予算 前回予想 今回予想 差額 比率 予算 前回予想 今回予想 差額 比率
a 1 11 21 2 12 22
b 1 11 21 2 12 22
c 1 11 21 2 12 22
差額、比率は関数ででも計算できるので略しています。
VBAでやっても、引き算や割り算をコード化するだけで、数行のコード増加です。
ーー
表の組み換えにピボットが使えれば良いが、もうひとつ組み替えに徹した目的でないので歯がゆいです。

お礼

ご回答ありがとうございました。
「VBAは必須」確かにそうかもしれませんね。
まだ私は「For ～　Next」などはわかりますが、Setなどが理解できません。
書いていただいたコードもわからない部分があるので、今後勉強したいと思います。