皆様こんにちは！
先日お仕事でエクセルの結合セルのコピー&ペーストに悩まされ、インターネット検索でこちらのブログの記事に行きついて救われたノグチです。
突然ですが皆様、ピボットテーブル、使っていますか？
エクセルのピボットテーブルは、知っているととても便利な機能ですよね。
今回は、エクセルのピボットテーブルに関数を組み合わせて、複数のリストを合体させた表の作り方をご案内します！
ピボットテーブルとVLOOKUP関数の組み合わせてできること
ピボットテーブルは、データ集約の切り口を変えられるレポートツールとして、とても便利ですよね。
一つのシートにあるリストなら、ワンステップでテーブルにできてしまいます。
しかしピボットテーブルで指定できるデータ範囲は一つのシートにあるものだけですから、別々シートや別ファイルにに分かれたリストから、となるとワンステップだけでテーブル化とはいきません。
でも、もうワンステップ加えて、ピボットテーブル+関数で、ちょっとしたVBAを組むよりも簡単に表が作れるんです！
今回は、
- 1つのリストからピボットテーブルを作ったあと、関数を使ってもう一つのリストのデータを結合する
- 2つのリストを1つのリストにまとめてからピボットテーブル化する
の2つの方法をご紹介します！
ピボットテーブルの使い方や、機能の説明は下記の記事で詳しく紹介されていますので、併せてご覧ください。
もちろんVLOOKUPについても紹介されていますので、こちらもご覧くださいね。
別々のリストのデータを組み合わせて1表を作る
今回は表の作成例として、月中の顧客毎の売上リストと、売り上げた品目の仕入れ価格リストのふたつを用意しました。
まず顧客別の売上リストです。
顧客別売上リストには、顧客別の販売品目、販売数、売上金額が並んでいます。
そしてこちらが仕入れ価格リスト。
仕入れ価格リストには、品物を仕入れ先から仕入れたときの単価と、総額が仕入先、品目別に並んでいます。
この二つのリストから、顧客別、品物別の粗利が見られる表を作ってみましょう。
方法1 ピボットテーブルを作ってからVLOOKUP関数で値を追加する
まず、1つのリストからピボットテーブルを作ってしまってから、もう一つのリストから必要な値をVLOOKUP関数で追加する方法です。
関数で加えた項目を、レポートの切り口として利用する必要がない場合にはこちらが単純で作りやすいかもしれません。
ピボットテーブルを作る
まず、顧客別売上げリストから、ピボットテーブルを作っておきます。
さくっと。
ここまでは通常のピボットテーブルの作り方と全く同じです。
VLOOKUP関数で必要な値を結合
では、VLOOKUP関数を使って、先ほど作ったピボットテーブルの値をIDにして必要な値をもう一つのリストから取得します。
ピボットテーブルの右端の列に「仕入れ単価」の見出しを追加しておきましょう。
販売品目をIDに、もう一つのリストから品目の仕入れ単価を取得します。
これで仕入れ単価も取得できました。
粗利計算の関数を加える
これで顧客別、販売品、売り上げ、仕入れ単価の表ができました。
今回は粗利を出したいので、ピボットテーブルの右端にもう一つ関数を加えます。
売り上げ-(販売数量×仕入れ単価)の関数をセルに入れて・・・
あとは「粗利」の見出しを加えて数字のカンマをつければ完成！
方法2 2つのリストを1つのリストにまとめてからピボットテーブル化
こちらの方法は、ピボットテーブルを作る前に2つのリストをVLOOKUP関数で結合してしまってからピボットテーブルにする方法です。
別リストやファイルから取得した値をピボットテーブルの項目に組み込んで、後でレポートの切り口を変えたい場合や、そのピボットテーブルをまた別のレポートの元データにしたい場合は、こちらの方法がよいのではないでしょうか。
リストの値をVLOOKUP関数で結合する
まず、顧客別売り上げリストに「仕入れ単価」の見出しを追加します。
後でピボットテーブル化するので、見出しを作るのは忘れないでくださいね。
追加した見出し列に、販売品目をキーにして仕入れ価格を取得するVLOOKUP関数を入力します。
売上表の最期までVLOOUP関数をコピーすれば、ピボットテーブルの準備完了です。
ピボットテーブルに変換
では、準備したリストをピボットテーブルに変換しましょう。
先にVLOOKUP関数で取得した仕入単価も、バッチリピボットテーブルの項目になっています。
粗利を計算する関数を加える
あとは方法1と同じく、ピボットテーブルの右隣に「粗利」見出しを作って、粗利を算出する関数を入れて・・・
完成！
ちなみに、今回の例では2つのリストから1表を作りましたが、データの結合さえできれば、3つからでも4つからでも作れますよ！
まとめ
いかがでしたでしょうか。
今回はピボットテーブルと関数を組み合わせて、複数のリストを結合させて1表を作る方法をご紹介しました。
同じ表を、例えば毎月月初など、何度も作ることがわかっている場合ならVBAを組んでしまうのがいいかと思いますが、1回限りだったり、何度も作るかわからないのであれば、今回ご紹介したピボットテーブルと関数を組み合わせる方法で対応するとよいのではないでしょうか。
それでは、最後までお読みいただきありがとうございました！
