使えると便利なピボットテーブルとＶＬＯＯＫＵＰ関数

ちょっとややこしいけれども、
使えると便利な操作を２つ説明します。

■請求書の名寄せに使える「ピボットテーブル」
■転記する手間がはぶける「ＶＬＯＯＫＵＰ（ブイルックアップ）関数」

それでは、順番に説明していきます。

請求書の名寄せに使える「ピボットテーブル」

「名寄せ」とは、取引先ごとに取引金額を集計することです。
例えば、商品の売上出荷記録を以下のように管理しているとします。

４月分の請求書を作成して、取引先に渡すには、
取引先ごとに売上金額を集計する＝名寄せする必要があります。

エクセルには「ピボットテーブル」という
情報をまとめてくれる機能があります。
上の右側の表がピボットテーブルです。

この機能を使えば一瞬で名寄せできます。
取引先や取扱商品が多い場合は、とても便利です。

上の「商品出荷記録」を例に手順を説明していきます。
手順は全部で３つです。

手順１

情報をまとめたい範囲を選択します。

一番上の「日付」が入力されているセルから
一番下の４月３０日の売上金額「１００」が入力されているセルまで選びます。

手順２

画面上の方にあるツールバー「挿入」を選ぶと、
一番左の方に「ピボットテーブル」のボタンが出てきます。
エクセル２０１０でも、画面上の方にツールバー「挿入」があります。

これを押すと次のような画面が出てきます。

この画面では「ＯＫ」を押すだけです。

手順３

新しいシートが自動作成され、
そこに「ピボットテーブルのフィールドリスト」という画面が表示されます。

今は「取引先」ごとに「売上金額」を集計したいので、
この２つの項目にチェックマークを入れます。

そして以下のように
「行ラベル」に「取引先」、「Σ値」に「合計/売上金額」
と表示されていれば完了です。

シート上に名寄せされたピボットテーブルが表示されています。
元々の表の合計＝ピボットテーブルの「総計」となっています。
ピボットテーブルの操作は以上になります。

→商品ごとの売上も知りたい場合

ピボットテーブルが上手く作成されない場合があります。
以下、それについて説明しておきます。

ピボットテーブルに合計額が表示されない場合

「取引先」と「売上金額」の２つの項目にチェックを入れても
合計額が表示されない場合があります。
以下のように「行ラベル」に項目が２つ入っているのが原因です。

この場合、
「売上金額」を隣りの「Σ値」へマウスを使って移動させ、クリックします。
クリックすると「値フィールドの設定」という項目が現れます。
その中の「集計の方法」→「合計」を選びます。

このようになれば、
「取引先」ごとに「売上金額」が集計されたピボットテーブルが作成されます。

「ピボットテーブルのフィールドリスト」が消えた場合

ピボットテーブルをクリックすると表示されます。
それでも表示されない場合はピボットテーブルを右クリックして
下の方にある「フィールドリストを表示する」を選べば現れます。

ピボットテーブルに元データの変更が反映されない

元データ（商品出荷記録）からピボットテーブルを作成した後、
元データの誤りに気づくことがあります。
元データを修正しても、そのまま自動的には、ピボットテーブルに反映されません。
ピボットテーブルを右クリック→「更新」を選ぶと反映されます。

転記する手間がはぶける「ＶＬＯＯＫＵＰ関数」

上の「名寄せ」の続きの流れです。
取引先に売り上げた金額が分かりました。

請求書を送ると共に、売上金がきちんと入金されたかどうか管理するため、
売掛金台帳（売掛帳）にもこれらの金額を記録する必要があります。

名寄せの結果を売掛帳の「売上高（売掛金の発生額）」に転記します。
下の黒枠の部分です。

取引先が多くない場合は、該当の取引先に「コピー＋貼り付け」で十分です。
しかし、取引先が多い場合は、「コピー＋貼り付け」は大変です。
貼り付け先を目視でひとつひとつ探し出すことになるからです。

ＶＬＯＯＫＵＰ関数を使えば、いちいち探す必要はなくなります。
エクセルが代わりに探し出してきてくれます。
売掛帳の「いいい社」を例に手順を説明していきます。
手順は全部で４つです。

手順１

売掛帳の１番上、「いいい社」の「売上高」のセルを選択します。

セルを選択したうえで、
画面の上の方にある小さな「ｆｘ」の文字（下図丸印参照）をクリックすると、
「関数の挿入」画面が出てきます。

「関数の分類」→「検索/行列」を選びます。
「関数名」の中の一番下に「ＶＬＯＯＫＵＰ」関数があります。
それを選んで「ＯＫ」を押します。

手順２

ＶＬＯＯＫＵＰ関数を選ぶと、下のような画面が出てきます。

これを日本語文章に直すと、次のとおりです。

■「検索値」＝この文字を探してきなさい
■「範囲」＝探す範囲はここからここまでです
■「列番号」＝もし文字が見つかったら、「範囲」の中の左から何番目の内容を回答しなさい
■「検索方法」＝探し方はこのようにしなさい

↓

売掛帳の例にあてはめます。

▼「検索値」＝「いいい社」を探してきなさい

丸で囲んだボタンを選択すると、セルを選択できるようになります。
探してきてほしい文字＝「いいい社」の文字が入ったセルを選択（下図参照）。
「検索値」で選択するセルは１つだけです。
そして再び丸で囲んだボタンを押します。

▼「範囲」＝ピボットテーブルで名寄せした表の中を探してきなさい
「検索値」と同様にボタンを押して、セルを選択します。
名寄せ表の全体を選択します。表が別のシートにある場合でも選択できます。

「範囲」のところに、アルファベットと数字が出てきます。
アルファベットと数字の前にドルマーク「＄」を付けます。

全部で４つドルマークを付けます。
「＄Ａ＄１：＄Ｂ＄５」となります。
この例では「Ａ１：Ｂ５」となっておりますけれども、
表示されるアルファベットと数字は選択する場所によって様々です。
表示されたアルファベットと数字の前にドルマークを付けてください。

名寄せ表が別のシートにある場合、
「範囲」には例えば「Ｓｈｅｅｔ２！Ａ１：Ｂ５」と表示されます。
これは「Ｓｈｅｅｔ２の中のＡ１からＢ５の範囲」という意味です。
この場合もドルマークはアルファベットと数字の前に合計４つ付けます。
「Ｓｈｅｅｔ２！＄Ａ＄１：＄Ｂ＄５」となります。

▼列番号＝名寄せ表の中で「いいい社」が見つかった場合、
名寄せ表の左から２番目にある「合計/売上金額」を回答しなさい

以下のように、数字の「２」を入力して終わりです。

セルは選択しません。

▼検索方法＝ｆａｌｓｅ
必ず「false（フォールス）」と手入力します。
未入力または「true（トゥルー）」だと結果が正しく表示されません。

「ＯＫ」を押すと関数の挿入は完了となります。
手順１で選択したセルに
「いいい社」の売上金額２，６００が表示されているはずです。

以上の通り、
関数の挿入画面で日本語の文字を入力する必要はありません。

→ピボットテーブルの結果を組み込めない

手順３

売掛帳のいいい社の「売上高」セルに、ＶＬＯＯＫＵＰ関数を挿入できました。
あとはこれを下にコピーするだけです。
一番下の「合計」は除いてコピーしてください。
以下のようになります。

「あああ社」と「ううう社」の金額もきちんと自動表示されています。
他の取引先の売上高には「#N/A」と表示されています。

「#N/A」はどういう意味かというと、例えば、「ろろろ社」の場合、
「ろろろ社という文字を名寄せ表の中で探してみましたけれども、見つかりませんでした」
という意味です。

名寄せ表には「あああ社」、「いいい社」、「ううう社」しかありませんので、
他の取引先はすべて「#N/A」となります。