Excelの押えておきたい関数 Excelのピボットテーブル

Vlookup関数を使ってデータを追加してピボットテーブルをカスタマイズする

更新日:

Excelピボットテーブルとショートカットキーを効率的に学ぶ講座⑥

これまでの内容
講座①
・ピボットテーブルの作成方法
・ピボットテーブルのデータのルール
講座②
・千円単位の表示の設定
・ピボットテーブルのグループ化
・ピボットテーブルのフィールドの展開/折りたたみ
・ピボットテーブルの比率の計算
・ピボットテーブルの条件付き書式
・ウィンドウ枠の固定
・ショートカットキーでの操作
講座③
・全てを1ページに収めて印刷
・全ページにタイトルを表示して印刷
講座④
・フィルター
・F2で編集
・フィル機能
・一括置換
・オートフィル機能
・列の挿入
・ピボットテーブルのデータソースの変更
・ピボットテーブルの更新
講座⑤
・グループ化を利用して、売上総利益を求める
・小計の表示場所を変更する
・フィルター機能を使って営業利益()を求める算出する

スポンサーリンク


前の記事
→ピボットテーブルの集計や表示を変更して損益計算書をカスタマイズする

※この記事からスタートの場合、Pivotサンプルデータ06をダウンロ-ドして、シート「Sheet1」から初めてください。

ここでやる内容
・Vlookup関数を使って人件費区分を追加する

5.VLOOKUP関数を使って、人件費区分を追加する

シート「伝票データ」のO列に人件費区分を追加しましょう。

①セルO1に「人件費区分」と記入してください。

②セルO2に[=VLOOKUP(C2,]と入力します。

③「左シートへ移動(Ctrl + Pagedown)」し、セルA2を選択し「Ctrl + Shift + →」で「F2」までを一括選択し、「Ctrl + Shift + ↓キー」でセルD33まで一括で選択します。


④関数式は「=VLOOKUP(C2,勘定科目マスタ!A2:F33」と相対参照となっていますので、「F4キー」を押して絶対参照にします。

⑤人件費区分の値を返すための指定した範囲のA列目から数えて4列目を指定します。
「=VLOOKUP(C2,勘定科目マスタ!$A$2:$F$33,4」

⑥完全に一致する値だけ検索するため「FALSE」を選択してEnterで式を閉じます。

セルO2の式は[=VLOOKUP(C2,勘定科目マスタ!$A$2:$F$33,4,FALSE)]となります。

⑦式は間違っていないのに、セルO2に「#N/A」と表示されています。

これは検索値にしたセルC2の「1001」に対して、シート「勘定科目マスタ」の範囲指定した1列目である「勘定科目コード」の表示形式の分類が一致していないために生じるエラーです。

●シート「伝票データ」のセルC2「1001」の表示形式の分類は「標準」
●シート「勘定科目マスタ」のセルA2「1001」の表示形式の分類は「文字列」

⑧分類を統一させるためにシート「勘定科目マスタ」のセルA2「1001」の分類は変更する。

セルA2「1001」を選択すると左横に「!マーク」が表示されていますので、クリックします。

メニューの1行目に「数値が文字列として保存されています」と表示されています。

2行目の「数値に変換する(C)」を実行します。

文字列が数値に変換されました。
シート「伝票データ」に戻ってセルO2が「01売上」と表示されているか確認してください。

シート「勘定科目マスタ」のセルA33を選択し、「Ctrl + Shift + ↑」でセルA3まで一括で選択し、左横の「!マーク」をクリックして、「数値に変換する(C)」を実行します。

数値に変換されました。

⑨セルO2の関数を他のO列に使う。
シート「伝票データ」のセルO2の右下にマウスポインタを合わせ、ポインタが「+」の状態でダブルクリックします。

列Oに一括で関数式が入りました。

⑨人件費区分を表示する
さきほどVLOOKUP関数で追加した人件費区分をピボットテーブルで利用してみましょう。

ピボットテーブルのフィールドに人件費区分が表示されていません。

⑩データソースを変更します。
「分析(Alt + JT)」→「データソースの変更(I)」→「データソースの変更(D)」を実行します。

シート「伝票データ」に切り替わります。

データ/範囲で選択されているデータ範囲は[伝票データ!$A$1:$N$1033]となっており、O列は範囲指定されていません。

データ範囲にO列を加えるためにNをOに変更します。

変更後範囲[伝票データ!$A$1:$O$1033]
ピボットテーブルのフィールドに人件費区分が追加されました。

行ラベルボックスから「小計区分」を削除して、「人件費区分」を「小計区分2」と「勘定科目」の間に追加してやります。

「02労務費」がなくなり「02人件費」が追加されました。

「2-02労務費(原)」と「04経費」に分類されていた「3-01給与(原)」~「3-05福利厚生費(原)」が内訳となります。

Pivotサンプルデータ06完成

 

 

フォローしていただけると嬉しいです

follow us in feedly Twitterボタン

-Excelの押えておきたい関数, Excelのピボットテーブル
-, , , ,

Copyright© 経理と事務の効率化 , 2017 All Rights Reserved Powered by AFFINGER4.