私は Excel のピボット テーブルをこの強力なツールに置き換えましたが、もう元に戻ることはありません。

データの海に溺れそうになった時、ピボットテーブルはいつも私のセーフティネットになってくれましたが、いつも疲れた目で数字の列を見つめる羽目になっていました。問題は、すべてのデータをまとめることでした。従来のピボットテーブルでは、別々のデータを扱う必要があり、同じデータセットの異なる側面を個別に分析する必要がありました。そんな時、Power Pivotを発見し、すべてが変わりました。

このExcel組み込み機能は、スプレッドシートを複数のリンクされたデータソースを自動的に処理するリレーショナルデータモデルに変換します。手動でデータの準備に何時間も費やす代わりに、複雑な関係性を数分で分析できるようになりました。

Power Pivot は、ピボットテーブルが実行できるすべてのことを実行します。

などなど

一方 ピボットテーブルは単一のデータ ソースで動作します。Power Pivotはブック全体を接続されたデータベースとして扱います。 私のお気に入りのExcel関数と数式 疑似接続を作成するには、複数の関連テーブルをインポートし、Power Pivot でモデルの関係を自動的に処理することができます。

このアプローチにより、以前のワークフローで問題となっていた、数式の更新と壊れた参照の修正という終わりのないサイクルが解消されます。Power Pivot を使用すると、新しいデータの追加は、すべての分析を一度に更新するシンプルな更新プロセスになります。

Power Pivot をアクティブ化するには、 ファイル > オプション、をクリックします 余分な仕事、を選択します COMアドイン ドロップダウンメニューから、 Excel 用 Microsoft Power Pivot有効にすると、Excel リボンに新しい Power Pivot タブが表示され、データの操作方法を変革するツールにアクセスできるようになります。

リレーショナル モデリングにより、要約と分析がこれまで以上に簡単になります。

Power Pivot は、データを単なる個別のスプレッドシートではなく、真のデータベースとして扱います。各データセットをインポートし、共通フィールド間のリレーションシップを定義するだけで、Excel が自動的にテーブルを結合し、手動で検索することなく統合レポートを作成できます。Power Pivot（または Excel のほぼすべての機能）を使用する前に、信頼性の高い結果を得るために、まずブックをクリーンアップして準備することが重要です。 私は個人的に Power Query を使用しています。 従来の清掃作業の代わりに、スケーラビリティが向上し、テーブルを清掃する時間を大幅に節約できるためです。

リレーショナルモデリングの威力を説明するために、開発中にバックエンドデータベースにデータを入力するときに使用するワークブックセットを例に挙げます。これは、顧客、製品、注文、注文詳細のそれぞれに個別のデータテーブルを持つeコマースデータベースで、これらにはCustomer_ID、Order_ID、Product_IDといった共通フィールドがあります。

まず、スプレッドシートを起動して Power Pivot を開きます。 お客さま 私自身の、クリック パワーピボット リボンから選択 データ モデルに追加 セクションで テーブル類Power Pivotメニューが開きます。ここから他のスプレッドシートを追加できます。 他のソースから > Excelファイル次にファイルを参照して開き、クリックします 次へ、その後 仕上げ私はすべてのスプレッドシートでこれを行います。

すべて追加したら、 ダイアグラムビューセクション内にあります 表示 Power Pivot では、次の 4 つのブックがすべて表示されます。 お客さま و 注文詳細 و 受注 و 構成Power Pivot では、多くの場合、リレーションシップを自動的に検出して提案できますが、ダイアグラム ビューでテーブル間でフィールドをドラッグして手動で定義することもできます。

この例では、各ワークブックはテーブルをリンクするキーフィールドを共有しています。両方のワークブックには以下のフィールドが含まれています。 お客さま و 受注 分野 顧客ID二人の著者は 受注 و 注文詳細 フィールドで 注文ID2つの分類器は 注文詳細 و 構成 同じ分野 製品IDこれらの共有フィールドは、1対多の関係を形成します。1人の顧客が複数の注文を持つことができ、各注文に複数の商品が含まれる場合があり、各商品が複数の注文詳細に表示される場合もあります。PowerPivotはこれらの一意の識別子を使用して、すべてのデータを自動的に接続します。

リレーションシップの設定が完了すると、レポート作成はフィールドをドラッグ＆ドロップするだけの簡単操作になりました。VLOOKUP関数や補助列を使う必要がなくなり、4つのテーブル全体のデータを瞬時にセグメント化して分析できるようになりました。

たとえば、顧客ごとの売上合計を表示するには、 ピボットテーブル PowerPivotウィンドウで、 新しいワーキングペーパーをクリックし、フィールドリストで「Customers」テーブルを展開します。次に、 顧客名 私に クラス و 行合計 テーブルから 注文詳細 私に 価値手動でリンクしなくても、各顧客の合計売上を即座に確認できます。

これらの売上を製品カテゴリごとに分割したい場合は、 カテゴリー テーブルから 構成 私に 列Excel は、通信を注文と注文詳細別に自動的に処理し、正しい値を各カテゴリに集計します。

さまざまな配送方法のパフォーマンスを比較するには、スワイプしてください 配送方法 テーブルから 受注 私に フィルタ 選択します エクスプレス أو スタンダード軸はすぐに更新され、これらのトランザクションのみが表示されます。

Power Pivotはテーブルがどのように接続されているかを認識しているので、自由に実験することができます。 都市 の お客さま 地理的な傾向を確認したり、追加したりするには 注文日 私に フィルター 期間別。すべての変更はリアルタイムで行われるため、データモデルを再構築したり数式を書き直したりすることなく、疑問点を探求し、洞察を得ることができます。

DAX アカウントを使用すると、柔軟性が向上し、より優れた洞察が得られます。

リレーションシップを構築し、レポート作成がいかに簡単かを確認したところで、いよいよDAXの活用方法をご紹介します。DAX（Data Analysis Expressions）は、Power Pivotを支える数式言語で、データモデリングと高度な計算のために特別に設計されています。Power PivotのDAX数式は、ピボットテーブルではほぼ不可能だった分析機能を実現します。

これらの数式を使用すると、テーブル間の関係を自動的に追跡し、驚くほどシンプルな構文で複雑な分析を実行するカスタム計算を作成できます。DAXを初めて使用する場合は、 公式 Microsoft ドキュメント 始めるのに最適な場所です。

3 つのステップで、従来のピボット テーブルではほとんど不可能な計算を実行できます。

まず、顧客の生涯価値を計算してみましょう。バーには パワーピボット Excelで、 措置それから私は選択します 新しいメジャースケジュールを設定する お客さま指標に「顧客LTV」という名前を付け、次の数式を入力します。

=SUM(注文詳細[行合計])

次にクリックします OKPower Pivot は、顧客から注文、注文の詳細までのチェーンをトレースし、各顧客の購入を自動的に集計します。

次に、各顧客の平均注文サイズを調べます。ここでも、 新しいメジャー スケジュールの中で お客さまこれを「平均注文額」と呼び、次の式を使用します。

= DIVIDE([顧客LTV], DISTINCTCOUNT(注文[注文ID]))

クリック OK ヘルパー列なしで、合計支出を顧客あたりの注文数で割るメトリックが得られます。

最後に、カテゴリー別に配送の希望条件を調べてみましょう。表では以下のようになっています。 構成私は次の式を使って「Audio Express %」というメーターを作成します。

= DIVIDE( CALCULATE( SUM(order_details[Line_Total]), products[Category] ​​= "Audio", orders[Shipping_Method] = "Express"), CALCULATE( SUM(order_details[Line_Total]), products[Category] ​​= "Audio" ))

次に、各メトリックのチェックボックスを選択して、テーブルに表示します。

これらのDAX指標を使えば、各顧客のカテゴリー別の総支出額と、オーディオ製品のエクスプレス配送における正確な割合を、75つのピボットテーブルで瞬時に確認できます。スクリーンショットでは、オーディオ、ケーブル、コンピューターなどの総売上が表示されています。また、「オーディオエクスプレス%」列を見ると、例えばAlexis Parkerさんがオーディオ製品の購入のXNUMX%をエクスプレス配送で配送していることがわかります。

従来の方法を使用してこれらの洞察を収集するには、複数のヘルプ テーブルを作成し、数十の VLOOKUP や手動計算を記述する必要がありました。 ワークブックを操作するための最新の Excel これは、DAX 数式を使用してテーブル間でフィルター処理および集計を行う方法です。

ピボット テーブルに戻る理由はないと思います。

Power Pivotは、Excelでのデータ分析のアプローチを根本的に変えました。以前は手動での設定と数式の作成に何時間もかかっていた作業が、自動化されたリレーションシップ管理とDAX計算によって数分で完了します。複数のデータソースを接続し、複雑な指標を作成し、統合レポートを作成できる機能は、それと比較するとピボットテーブルが原始的に思えるほどです。

少なくとも、Power Pivot は通常のピボットテーブルと同じように使える上に、大規模なワークブックでもはるかに高速なパフォーマンスを享受できます。そのスピード、自動化、そして分析の深さの組み合わせは、Excel でデータを最大限に活用したいと真剣に考えている人にとって、Power Pivot は必須のアップグレードです。