Excelピボットテーブルとショートカットキーを効率的に学ぶ講座の⑥です。
ピボットテーブルで損益計算書を作成してみようという内容を紹介していきましょう。
よかったらフォローしてください。→ハヤトのTwitter
1.ピボットテーブルとショートカットキーを効率的に学ぶ講座
2.ピボットテーブルで活用したい５つの機能。グループ化、フィルター他
3.Excelの印刷設定。１ページに収める・タイトルを全ページに表示
4.フィルターや置換機能でピボットテーブルのデータを修正
5.ピボットテーブルの集計や表示を変更して損益計算書をカスタマイズする
6.Vlookup関数を使ってデータを追加してピボットテーブルをカスタマイズする
これまでの内容
講座①
・ピボットテーブルの作成方法
・ピボットテーブルのデータのルール
講座②
・千円単位の表示の設定
・ピボットテーブルのグループ化
・ピボットテーブルのフィールドの展開/折りたたみ
・ピボットテーブルの比率の計算
・ピボットテーブルの条件付き書式
・ウィンドウ枠の固定
・ショートカットキーでの操作
講座③
・全てを１ページに収めて印刷
・全ページにタイトルを表示して印刷
講座④
・フィルター
・Ｆ２で編集
・フィル機能
・一括置換
・オートフィル機能
・列の挿入
・ピボットテーブルのデータソースの変更
・ピボットテーブルの更新
講座⑤
・グループ化を利用して、売上総利益を求める
・小計の表示場所を変更する
・フィルター機能を使って営業利益（）を求める算出する
前の記事
→ピボットテーブルの集計や表示を変更して損益計算書をカスタマイズする
※この記事からスタートの場合、Pivotサンプルデータ06をダウンロ－ドして、シート「Sheet1」から初めてください。
目次
Vlookup関数を使ってデータを追加してピボットテーブルをカスタマイズする
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」に対して、シート「勘定科目マスタ」の範囲指定した１列目である「勘定科目コード」の表示形式の分類が一致していないために生じるエラーです。
●シート「伝票データ」のセルC2「1001」の表示形式の分類は「標準」
●シート「勘定科目マスタ」のセルA2「1001」の表示形式の分類は「文字列」
分類を変更する
⑧分類を統一させるためにシート「勘定科目マスタ」のセルA2「1001」の分類は変更する。
セルA2「1001」を選択すると左横に「！マーク」が表示されていますので、クリックします。
メニューの１行目に「数値が文字列として保存されています」と表示されています。
２行目の「数値に変換する(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福利厚生費(原)」が内訳となります。