エクセルピボットテーブルとVLOOKUPを組み合わせて複数のリストを合体!

★気に入ったらシェアをお願いします!


エクセル,ピボットテーブル,複数リストを結合,

皆様こんにちは!

先日お仕事でエクセルの結合セルのコピー&ペーストに悩まされ、インターネット検索でこちらのブログの記事に行きついて救われたノグチです。

突然ですが皆様、ピボットテーブル、使っていますか?

エクセルのピボットテーブルは、知っているととても便利な機能ですよね。

今回は、エクセルのピボットテーブルに関数を組み合わせて、複数のリストを合体させた表の作り方をご案内します!

スポンサーリンク

ピボットテーブルとVLOOKUP関数の組み合わせてできること

ピボットテーブルは、データ集約の切り口を変えられるレポートツールとして、とても便利ですよね。

一つのシートにあるリストなら、ワンステップでテーブルにできてしまいます。

しかしピボットテーブルで指定できるデータ範囲は一つのシートにあるものだけですから、別々シートや別ファイルにに分かれたリストから、となるとワンステップだけでテーブル化とはいきません。

でも、もうワンステップ加えて、ピボットテーブル+関数で、ちょっとしたVBAを組むよりも簡単に表が作れるんです!

今回は、

  • 1つのリストからピボットテーブルを作ったあと、関数を使ってもう一つのリストのデータを結合する
  • 2つのリストを1つのリストにまとめてからピボットテーブル化する

の2つの方法をご紹介します!

ピボットテーブルの使い方や、機能の説明は下記の記事で詳しく紹介されていますので、併せてご覧ください。

データ分析をするならとりあえず作る!エクセルピボットテーブルの作り方
とあるラーメン屋さんのアンケートを題材にしてデータ分析を進めていきます。まずはデータ分析においてとりあえず作っておけ的な存在であるピボットテーブルについてその作り方を解説していきますね。

もちろんVLOOKUPについても紹介されていますので、こちらもご覧くださいね。

VLOOKUP関数の使い方とその威力を存分に味わうデータ準備
Excel中級への最初の難関とも言えるVLOOKUP関数。使いこなせればこれほど便利な関数もないのですが、データの準備方法に問題があると、なかなかマスターすることができません。今回の記事では、そのデータ準備で気を付けるポイントについてお伝えします。

別々のリストのデータを組み合わせて1表を作る

今回は表の作成例として、月中の顧客毎の売上リストと、売り上げた品目の仕入れ価格リストのふたつを用意しました。

まず顧客別の売上リストです。
エクセル、ピボットテーブル、売り上げリスト

顧客別売上リストには、顧客別の販売品目、販売数、売上金額が並んでいます。

そしてこちらが仕入れ価格リスト。

エクセル、ピボットテーブル、仕入れ単価表

仕入れ価格リストには、品物を仕入れ先から仕入れたときの単価と、総額が仕入先、品目別に並んでいます。

この二つのリストから、顧客別、品物別の粗利が見られる表を作ってみましょう。

方法1  ピボットテーブルを作ってからVLOOKUP関数で値を追加する

まず、1つのリストからピボットテーブルを作ってしまってから、もう一つのリストから必要な値をVLOOKUP関数で追加する方法です。

関数で加えた項目を、レポートの切り口として利用する必要がない場合にはこちらが単純で作りやすいかもしれません。

ピボットテーブルを作る

まず、顧客別売上げリストから、ピボットテーブルを作っておきます。

エクセル,ピボットテーブル,結合後,加工前

さくっと。

ここまでは通常のピボットテーブルの作り方と全く同じです。

VLOOKUP関数で必要な値を結合

では、VLOOKUP関数を使って、先ほど作ったピボットテーブルの値をIDにして必要な値をもう一つのリストから取得します。

ピボットテーブルの右端の列に「仕入れ単価」の見出しを追加しておきましょう。

販売品目をIDに、もう一つのリストから品目の仕入れ単価を取得します。

エクセル,ピボットテーブル,VLOOKUP,関数で取得

これで仕入れ単価も取得できました。

エクセル,ピボットテーブル,VLOOKUP,仕入単価結合

粗利計算の関数を加える

これで顧客別、販売品、売り上げ、仕入れ単価の表ができました。

今回は粗利を出したいので、ピボットテーブルの右端にもう一つ関数を加えます。

売り上げ-(販売数量×仕入れ単価)の関数をセルに入れて・・・

エクセル,ピボットテーブル,関数,粗利計算

あとは「粗利」の見出しを加えて数字のカンマをつければ完成!

エクセル,ピボットテーブル,関数,粗利計算

方法2 2つのリストを1つのリストにまとめてからピボットテーブル化

こちらの方法は、ピボットテーブルを作る前に2つのリストをVLOOKUP関数で結合してしまってからピボットテーブルにする方法です。

別リストやファイルから取得した値をピボットテーブルの項目に組み込んで、後でレポートの切り口を変えたい場合や、そのピボットテーブルをまた別のレポートの元データにしたい場合は、こちらの方法がよいのではないでしょうか。

リストの値をVLOOKUP関数で結合する

まず、顧客別売り上げリストに「仕入れ単価」の見出しを追加します。

後でピボットテーブル化するので、見出しを作るのは忘れないでくださいね。

追加した見出し列に、販売品目をキーにして仕入れ価格を取得するVLOOKUP関数を入力します。

エクセル,ピボットテーブル,VLOOKUP,売上表で仕入れ単価取得,

売上表の最期までVLOOUP関数をコピーすれば、ピボットテーブルの準備完了です。

ピボットテーブルに変換

では、準備したリストをピボットテーブルに変換しましょう。

エクセル,ピボットテーブル,結合後,ピボットテーブル化

先にVLOOKUP関数で取得した仕入単価も、バッチリピボットテーブルの項目になっています。

エクセル,ピボットテーブル,仕入単価取得後

粗利を計算する関数を加える

あとは方法1と同じく、ピボットテーブルの右隣に「粗利」見出しを作って、粗利を算出する関数を入れて・・・

完成!

エクセル,ピボットテーブル,VLOOKUP,仕入単価取得後,完成

ちなみに、今回の例では2つのリストから1表を作りましたが、データの結合さえできれば、3つからでも4つからでも作れますよ!

まとめ

いかがでしたでしょうか。

今回はピボットテーブルと関数を組み合わせて、複数のリストを結合させて1表を作る方法をご紹介しました。

同じ表を、例えば毎月月初など、何度も作ることがわかっている場合ならVBAを組んでしまうのがいいかと思いますが、1回限りだったり、何度も作るかわからないのであれば、今回ご紹介したピボットテーブルと関数を組み合わせる方法で対応するとよいのではないでしょうか。

それでは、最後までお読みいただきありがとうございました!


The following two tabs change content below.
ノグチ

ノグチ

元製造子会社SE、ERPパッケージシステムコンサルタント(販売管理、購買管理、生産管理、設備管理他)。 業務システム保守・導入両方やった経験をお伝えすることで、少しでもシステムや手作業に悩まされる人が減ればいいなぁ、楽にお仕事してもらえたらいいなぁと日々夢見ております。