はじめに
Excelの複数のテーブルでデータが絡み合う状況、おなじみですよね。そんな時「VLOOKUP関数」を使って結合していませんか?私もWebサイト内の情報を整理する時に利用していました。でも、これってデータが増えるとちょっと重くなり、最悪の場合はExcelがエラーを起こして、データが一瞬で消えちゃうこともあります。。。でも大丈夫!今回はその手間いらずで、Excelの「Power Pivot」を使ってテーブルをスムーズに結合し、さらに結合したテーブルの中身もカンタンに集計する方法をお伝えします!これで作業がぐっとラクになりますよ!
Power Pivotとは
Power Pivotとは、Excel 2010から導入されたアドイン(機能)です。 Power Pivotを活用すると、複数のテーブルをデータソースにして、テーブル間の関係性(リレーションシップ)を設定できるので、「VLOOKUP関数」を利用せずに、簡単に結合させたテーブルを作成できます。 では次の図(Webサイト内の情報を整理した2つの表があった場合を想定)のように、表1と表2のテーブルに関係性を設定して結合し、表3の状態でテーブルを作る方法を説明します。
設定方法
Power Pivot は、アプリケーションで強力なデータ分析を実行するために使用できるアドインExcelなので、最初から有効になっていません。 有効にする方法を以下に示します。
- [ファイル]、[オプション]、[アドイン] の順にクリックします。
- [管理] ボックスで、[COM アドイン]、[設定] の順にクリックします。
- [Microsoft Office Power Pivot] チェック ボックスをオンにして、[OK] をクリックします。 他のバージョンの Power Pivot アドインをインストールした場合は、それらのバージョンも COM アドイン リストに表示されます。 ここでは、Excel 用の Power Pivot アドインを選びます。
- これにて、リボンに Power Pivot タブが追加されました。
データの取り込み
表1のテーブルデータと表2のテーブルデータをPower Pivotに取り込みます。
Excelのデータタブから、Power Pivotウィンドウを開きます。
「データモデルに追加」を選択し、ディレクトリマップのデータを含むExcelファイルを選択します。
今回取り込む2つのテーブルデータを選択し、「OK」をクリックします。 ※先頭行が見出しの場合はチェックしてください
Power Pivotが起動して、2つのテーブルが表示されれば取り込み完了です。
テーブルの関連付け
Power Pivotを使用してこれらのテーブルを関連付けます。 まず、右上のボタンダイアグラム ビューを押します。 すると、先ほど取り込んだテーブルが2つ表示されます。 表1の「url」と表2の「url」の値が対応しているので、要素をドラックしてつなぎ合わせます。 これでテーブル間の関係性が設定されたので、画面上部のピボットテーブルを押します。
「新しいワークシート」を選択してOKを押すと、Excelの新しいテーブルが作成され、表1と表2のテーブルデータが関係性を持ったまま、ピボットを作成することができるようになります。
一覧の作成
では目標であった表1と表2のテーブルを結合していきます。
ピボットで一覧形式で表示するための設定として、Excel画面上部のタブ「デザイン」から「レポートのレイアウト」をクリックし、「表形式で表示」を選択します。
そしてピボットの四角い枠をクリックすると、画面右にピボットテーブルのフィールドが表示されます。
表1と表2のテーブルの要素を下記画像のように選択してもらうと、一覧が表示できます。
※行には片方の「url」ラベルを選択します。値には「url」のカウントを設定してください。
あとは、B列からG列の必要なテーブルをコピーして別テーブルに張り付ければ完成です。
結合した一覧の集計
Power Pivotは結合した一覧に対して、要素ごとに集計する事も可能です。 例えばテンプレートパターン別で集計したい場合、下記のように表示することができます。 このようにピボット機能を活用し、結合させた一覧を整理できるので便利です。
おわりに
このブログでは、Excelアドイン「Power Pivot」を利用して、2つのテーブルを結合する方法を紹介しました!
是非「VLOOKUP関数」を脱して、Webサイト内のページ情報を”効率的”に一覧化していただければと思います!
ありがとうございました!