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

0
2654

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

ピボットテーブルの弱点

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

瞬時に集計できます。


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

 

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

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

 

 

VLOOKUP関数を組み合わせる

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

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

 

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

という命令です。

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

 

<役員報酬>を探してきて、一致した場合に金額(4,000,000)を表示しているのです。

 

自由なレイアウトが可能

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

 

このような推移表でも

 

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

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

 

 





【編集後記】

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

返事を書く

Please enter your comment!
Please enter your name here

CAPTCHA