トップページ > エクセル道具集 > ピボットVLOOK

使えると便利なピボットテーブルとVLOOKUP関数

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

■請求書の名寄せに使える「ピボットテーブル」
■転記する手間がはぶける「VLOOKUP(ブイルックアップ)関数」

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

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

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

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

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

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

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

手順1

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

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

手順2

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

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

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

手順3

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

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

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

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

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



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

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

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

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

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

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

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

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

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

転記する手間がはぶける「VLOOKUP関数」

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

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

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

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

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

手順1

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

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

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

手順2

VLOOKUP関数を選ぶと、下のような画面が出てきます。

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

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

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

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

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

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

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

全部で4つドルマークを付けます。
「$A$1:$B$5」となります。
この例では「A1:B5」となっておりますけれども、
表示されるアルファベットと数字は選択する場所によって様々です。
表示されたアルファベットと数字の前にドルマークを付けてください。

名寄せ表が別のシートにある場合、
「範囲」には例えば「Sheet2!A1:B5」と表示されます。
これは「Sheet2の中のA1からB5の範囲」という意味です。
この場合もドルマークはアルファベットと数字の前に合計4つ付けます。
「Sheet2!$A$1:$B$5」となります。

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

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

セルは選択しません。

▼検索方法=false
必ず「false(フォールス)」と手入力します。
未入力または「true(トゥルー)」だと結果が正しく表示されません。

「OK」を押すと関数の挿入は完了となります。
手順1で選択したセルに
「いいい社」の売上金額2,600が表示されているはずです。

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

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

手順3

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

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

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

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

手順4

「#N/A」のままだと、見苦しいですし、
一番下の「合計」も「#N/A」と表示され、正しく集計されていません。
正しく集計されるように「#N/A」をゼロに置き換えます。

「売上高」のうち、一番下の「合計」以外を範囲選択します。

この範囲を「コピー」し、そのまま範囲を変えずに「値貼り付け」します。
「値貼り付け」の方法はエクセル操作一覧に載っています。

→「値貼り付け」方法

さらにこの範囲を選んだままの状態で「置換」機能を使います。
「置換」の方法もエクセル操作一覧に載っています。
以下のとおり、「#N/A」を数字の「0(ゼロ)」に置き換え、
一番左の「すべて置換」ボタンを押します。

→「置換」方法

置き換えたら、最終的に次のようになります。

「合計」もきれいに表示されています。

以上、
売上、売掛帳を例として説明しました。
もちろん、仕入、買掛帳でもピボットテーブルとVLOOKUP関数は使えます。
経理、会計以外の経営情報の集計、分析にも使えます。

使いこなすことができれば、幅広く応用できます。

画面トップへ

    会社設立から創業融資まで
      全部まとめて定額支援!
        →詳細はこちらへ

ご連絡先はこちら

迅速・丁寧な対応を
心がけております。
お気軽に
お問合せください。

03-5372-4677
融資相談所

西田恭隆(nishida  yasutaka)
     ◆公認会計士
         ◆中小企業診断士
             ◆税理士事務所

所長の著書

  「税理士ができる
   『中小企業の資金調達』
          支援実務」

    税理士などのプロ向けに
   資金調達支援の実務を解説
         →詳しくはこちらへ

独立開業【問答集】

独立志望の会計士税理士向けに
よくある質問をまとめました。
・会計事務所の運営方法
・営業方法あれこれ

・ホームページ活用方法
・クラウド会計についてなど
         →詳しくはこちらへ