ピボットテーブルの弱点をVLOOKUP関数で補う方法

ピボットテーブルは、瞬時にデータを集計できる優れたツールです。
しかし、弱点もあります。
レイアウトやデザインの自由度がないことです。
私はその弱点をVLOOKUP関数で補っています。
スクリーンショット 2012 05 11 6 40 08

ピボットテーブルの弱点

ピボットテーブルは、こういったデータを
スクリーンショット 2012 05 11 6 30 05

瞬時に集計できます。

※ピボットテーブルを使ったことがないという方は、こちらのセミナーもご利用ください。

☆6/16 午前 経理&会計のためのピボットテーブル入門セミナー お申し込みはこちら
ただし、この表の項目は自動的に並んでしまいます。
この場合は基本的にあいうえお順に並んでいますが、漢字の読み方はExcel側が判断するため、ほぼ不規則です。
(<荷造運賃>は「か」と読んでいます)
スクリーンショット 2012 05 11 6 30 16

並べ替えるには、数字のコードをつけるか、手動でドラッグして並べ替えるしかありません。
非常にめんどくさいです。
WS000000

また、表のデザインもある程度は変えることはできますが、限界があります。

VLOOKUP関数を組み合わせる

そこで、ピボットテーブルとVLOOKUP関数を組み合わせます。
VLOOKUP関数は、特定の値を探し出し、対応するデータを表示するものです。

最終的な表のフォーマットを作成し、VLOOKUP関数を入れます。
表の場所は同じシートでも違うシートでもかまいません。
この場合、=vlookup(E4,A:B,2,false)と入れます。
WS000001

E4(科目名)をA列からB列の範囲から探してきて、もし一致すれば、2列目にあるデータ(金額)を表示

という命令です。

次のように、4,000,000が表示されます。
WS000002

<役員報酬>を探してきて、一致した場合に金額(4,000,000)を表示しているのです。
スクリーンショット 2012 05 11 7 10 30

自由なレイアウトが可能

この数式をコピーすれば、すべての数値を連動できます。
(ピボットテーブルと表の科目は完全に一致するように、かつもれなく入れなければいけません。)

スクリーンショット 2012 05 11 6 35 55

このような推移表でも
スクリーンショット 2012 05 11 6 37 12

連動させて表にすることができます。
スクリーンショット 2012 05 11 6 40 08

自分で見る、数字のチェック用ならば、ピボットテーブル本来のデザインでもまったく問題ありません。
報告会や提出用に資料を作る場合には、柔軟にデザインできるこの方法がオススメです。





【編集後記】

■スポンサードリンク

昨日、夕方にExcelマンツーマンレッスンを行いました。
テーマはピボット、ジャパンネット銀行の取込、毎月の経理の方法、Excelの勉強方法など。
ジャパンネット銀行は他の銀行のデータと比較しても、加工しやすいので使いやすいです。
さくっと会計ソフトに取り込むことができます。