お役立ちコラム
東京パソコンアカデミーのお役立ちコラムです。
東京パソコンアカデミーのお役立ちコラムです。
目次
Excelを使っていると、複数のテーブルに分かれたデータをまとめて集計したい場面はよくあります。
売上、商品、顧客、店舗と…… それぞれ別々の表だけど、ひとつのピボットテーブルで分析したい。
でも、関数を複雑に組んだり、コピー貼り付けしたりして結合しようとすると、手間がかかるうえに、データ量が増えるほど、管理が大変になります。
少し前のコラムでは、Excel標準の「リレーションシップ」機能を使って複数テーブルからピボットテーブルを作成する方法をご紹介しました。
この方法でも複数テーブルを扱うことはできますが、データ量が増えたり、より複雑な集計・分析を行いたい場合には、少し物足りない場面も出てきます。
そこで今回は、Power Pivot（パワーピボット）を使った方法をご紹介します。
Power Pivotは、Excelのデータ分析機能を強化するための機能です。複数の表を関連付けて扱え、数万～数十万件のデータでも快適に集計・分析できます。複数テーブルをより安定的に管理できるだけでなく、集計や分析も、より柔軟に行えるようになります。
Power PivotはExcelに標準で搭載されている機能ですが、環境によってはリボンに表示されていないことがあります。
その場合は、アドインを有効化する設定を行うことで利用できるようになります。
①［ファイル］タブ → ［オプション］をクリック。
②Excelのオプションダイアログボックスが表示されたら、左側のメニューから ［アドイン］を選択。
③画面下部の「管理」欄で ［COM アドイン］を選択し、「設定」をクリック。
④表示された一覧の中から「Microsoft Power Pivot for Excel」にチェックを入れ、［OK］をクリック。
これでExcelのリボンに［Power Pivot］タブが表示され、機能を利用できるようになります。
＜表をテーブルにする＞
Power Pivotを使う場合は、元の表をテーブルにしておくことが出発点になります。
今回は「売上」「商品」「店舗」の3つのテーブルを例に説明します。
①テーブルにしたい表内を選択
［挿入］タブをクリックし、［テーブル］グループにある［テーブル］をクリック。
②[テーブルの作成]ダイアログボックスが表示されたら、範囲が正しいことを確認し、
[先頭行をテーブルの見出しとして使用する]にチェックを付け、[OK］をクリック
これで、表が「テーブル」になります。
＜テーブルに名前を付ける＞
Power Pivotで扱いやすくするために、わかりやすいテーブル名を付けます。
①テーブル内の任意のセルを選択。
②［テーブルデザイン］タブ→[プロパティ]グループにある[テーブル名]のボックスにわかりやすい名前を入力してEnterで確定。
同様に、ほかの表もテーブルにし、テーブル名をつけておきます。
＜データモデルに追加する＞
つづいて、このテーブルをデータモデルに追加する必要があります。
データモデルに追加することで、Excelに「この表同士は関係していますよ」と教えることができます。
①テーブル内の任意のセルを選択。
②［Power Pivot］タブ → [テーブル]グループにある[データモデルに追加]をクリック。
③Power Pivotウィンドウが起動し、データモデルにテーブル「売上」が追加される。
同様の操作を行い、すべてのテーブルをデータモデルに追加します。
これで、Power Pivot側で複数テーブルを扱う準備が整います。
＜パワーピボットでリレーションシップを設定する＞
データモデルに追加したら、次はリレーションシップ（関連付け）を設定します。設定方法はいくつかありますが、今回はPower Pivotの「ダイアグラムビュー」で設定していきます。
①Power Pivot for Excelの画面の、[ホーム]タブ → [表示]グループにある[ダイアグラムビュー]をクリック。
②ダイアグラムビューが表示される。
一番上にテーブル名が表示され、その下に列が一覧表示されます。主にリレーションシップの設定に使用する画面です。
表示を元の状態に戻すには[データビュー]にします。テーブルが表の形式で表示される画面です。
※Excel標準のリレーションシップよりも視覚的でわかりやすく、複雑な構造でも管理しやすいのがPower Pivotの強みです。
ダイアグラムビューでは、テーブル同士のつながりを視覚的に設定します。テーブル「売上」には[商品ID]と[店舗ID]があり、テーブル「商品」にも[商品ID]、テーブル「店舗」にも[店舗ID]があります。このように両方のテーブルに共通して存在する列をドラッグして線で結ぶと、Power Pivotが「この列を使ってテーブル同士をつなげている」と理解し、複数テーブルをまたいだ集計などができるようになります。
では、実際に共通の列を使ってリレーションを作成してみましょう。
①テーブル[売上]の列[商品ID]をテーブル[商品]の列[商品ID]までドラッグ。
②[商品ID]が結合線で結ばれ、リレーションシップが設定される。
③同様に、[店舗ID]もリレーションシップを設定する。
これで、3つのテーブルが共通の列によって、連携された状態となりました。
＜ピボットテーブルを作成＞
テーブル同士を商品IDや店舗IDで結びつけたあとは、そのつながりを使ってピボットテーブルで集計していきます。
①Power Pivotウィンドウの[ホーム]タブ → [外部データの取り込み]グループにある[ピボットテーブル]をクリック。
②[ピボットテーブルの作成]ダイアログボックスが表示されたら、[新しいワークシート]を選択し、[OK]をクリック。
③Excelの新規シートにピボットテーブルが作成される。
ピボットテーブルのフィールド一覧に、データモデルに追加したすべてのテーブルが表示されます。
④行、列、値にそれぞれフィールドを追加すると、集計結果が表示される。
ここでは、列→店舗名、行→カテゴリ、商品名、値→数量を設定し、商品ごとの数量の合計を集計した結果が表示されます。
テーブルを連携させることで、商品名や単価などの情報を売上データと組み合わせて利用できるようになりました。これにより、「数量 × 単価」のような計算も行えるようになります。ピボットテーブルでは、数量の合計などを簡単に集計できます。しかし、「数量 × 単価」の計算結果を集計したい場合は、どのようにすればよいのでしょうか。そこで活躍するのが「メジャー」です。メジャーを使うと、ピボットテーブル内で自由に計算式を作成し、集計結果を表示できるようになります。標準のピボットテーブルにも「計算フィールド（集計フィールド）」という機能があり、「数量 × 単価」のような簡単な計算は可能です。ただし、複数のテーブルをまたいだ計算や、ピボットテーブルの行や列の配置に応じて計算結果が変化するような柔軟な集計には対応していません。このような高度で柔軟な集計を実現するために使用するのが、Power Pivotの「メジャー」です。
＜メジャーを使って単価×数量を計算する＞
①フィールド一覧にある[売上]テーブルの上で右クリックし、[メジャーの追加]をクリック。
②[メジャー]ダイアログボックスが表示されるので、式を入力し、カテゴリ、桁区切り等の設定を行い[OK]をクリック。
式は「=SUMX('売上',RELATED('商品'[単価])*'売上'[数量])」
「SUMX」や「RELATED」などの関数はPower Pivot（データモデル）で使うことができる関数「DAX関数」です。
SUMX・・・テーブルを1行ずつ処理し、計算結果を合計するDAX関数。
RELATED・・・リレーションシップを利用して、関連するテーブルの値を取得するDAX関数。
③これで、新しく「売上合計」というフィールドが出来上がりました。
あとは出来上がったフィールドを値に設定すると、ピボットテーブルに反映されます。
このメジャーは、テーブル「売上」を1行ずつ読み込みながら、テーブル「商品」の単価を参照して計算しています。SUMXが「行ごとの計算」を担当し、RELATEDが「別テーブルの値を取得」する役割を持つため、標準のピボットテーブルではできない「複数テーブルをまたいだ動的な計算」が可能になっています。
さらに、Power Pivotのメジャーは、ほかのメジャーを材料として再利用することができます。たとえばSUMXとRELATEDで作成した「売上合計」メジャーを使って「売上合計が3,000円以上なら○」という条件判定のメジャーを作成することもできます。
式は次のようになります。
=if([売上合計]>=3000,"○","")
そして、ピボットテーブルの行に商品名やカテゴリを置くだけで、それぞれの分類ごとに「売上が3,000円を超えているかどうか」を自動で判定してくれます。
普通のピボットテーブルでは実現できない、Power Pivotならではの柔軟な分析方法です。
今回はDAX関数の基本的な使い方をご紹介しました。DAXにはこのほかにも多くの関数があり、組み合わせることで、より複雑な集計や分析も行えるようになります。
いかがでしたでしょうか。Power Pivotを使うと、複数のテーブルをつなげながら、ピボットテーブルの中で柔軟な計算ができるようになります。SUMXで売上合計を求めたり、IFで条件判定の式を作成したり、標準のピボットテーブルではできない分析が手軽に実現できます。
Excelの関数だけでは難しい集計も、Power Pivotならシンプルに実現できます。複数のテーブルを扱う機会がある方は、ぜひPower Pivotを活用してみてください。
石田
関連するコラムはこちら
東京パソコンアカデミーの「Excel講座」はこちら
お近くの教室で無料体験レッスンを受け付けております。
ぜひお気軽にお申し込みください。
