ピボットテーブルの弱点を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の勉強方法など。
ジャパンネット銀行は他の銀行のデータと比較しても、加工しやすいので使いやすいです。
さくっと会計ソフトに取り込むことができます。

■スポンサードリンク



■スポンサードリンク


■ブログEX-ITの購読 →feedlyを使って無料で読む
→Twtterで読む
Facebookで読む

1日1新 Instagram
井ノ上陽一のVALU
■著書
ひとり税理士のIT仕事術―ITに強くなれば、ひとり税理士の真価を発揮できる!!
フリーランスとひとり社長のための 経理をエクセルでトコトン楽にする本
新版 ひとり社長の経理の基本
毎日定時で帰っても給料が上がる時間のつかい方をお金のプロに聞いてみた!
『ひとり税理士の仕事術』
『フリーランスのための一生仕事に困らない本』
『社長!「経理」がわからないと、あなたの会社潰れますよ』
『そのまま使える経理&会計のためのExcel入門』