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

2012年5月11日

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

ピボットテーブルの弱点

ピボットテーブルは、こういったデータを

瞬時に集計できます。

ただし、この表の項目は自動的に並んでしまいます。
この場合は基本的にあいうえお順に並んでいますが、漢字の読み方はExcel側が判断するため、ほぼ不規則です。
（＜荷造運賃＞は「か」と読んでいます）

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

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

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

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

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

という命令です。

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

＜役員報酬＞を探してきて、一致した場合に金額（4,000,000）を表示しているのです。

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

このような推移表でも

連動させて表にすることができます。

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

【編集後記】

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