Power Pivot for Excelチュートリアル：主な使用例と例

エグゼクティブサマリー

ここからデータセットをダウンロードして、チュートリアルに従ってください。

皇帝の新しい服：パワーピボットチュートリアル

金融、財務分析、金融市場、および金融投資にまたがるさまざまなフィールドおよびサブサブフィールド全体で、MicrosoftExcelが最も重要です。 しかし、ビッグデータの到来と指数関数的成長に伴い、何十年にもわたるデータの集約と蓄積、安価なクラウドストレージの出現、モノのインターネット（eコマース、ソーシャルメディア、デバイスの相互接続性）の台頭により、Excelの従来の機能と機能は限界に達しています。

具体的には、旧世代のExcelのインフラストラクチャと、1,048,576行の行制限などの処理の制限、または大規模なデータセット、データテーブル、相互接続されたスプレッドシートが関係する場合の必然的な処理速度の低下により、効果的なビッグデータツールとしての使いやすさが低下しました。 ただし、2010年に、MicrosoftはPowerPivotと呼ばれる新しいディメンションをExcelに追加しました。 Power Pivotは、処理速度を低下させることなく、ほぼ無制限のデータセットを抽出、結合、分析する機能において、Excelに次世代のビジネスインテリジェンスおよびビジネス分析機能を提供しました。 しかし、8年前にリリースされたにもかかわらず、ほとんどの金融アナリストはまだPower Pivotの使用方法を知らず、多くの人はPowerPivotが存在することすら知りません。

この記事では、Power Pivotを使用して一般的なExcelの問題を克服する方法を示し、いくつかの例を使用してソフトウェアのその他の主要な利点を確認します。 このPowerPivotチュートリアルは、このツールで達成できることのガイドとして機能することを目的としており、最後に、PowerPivotが非常に貴重であることが多いいくつかのサンプルユースケースを探ります。

パワーピボットとは何ですか？なぜそれが役立つのですか？

Power Pivotは、Excel2010および2013のアドインとして導入されたMicrosoftExcelの機能であり、現在はExcel 2016および365のネイティブ機能です。Microsoftが説明するように、Power Pivot for Excelを使用すると、「数百万行をインポートできます。複数のデータソースからのデータを単一のExcelワークブックにまとめ、異種データ間の関係を作成し、数式を使用して計算列とメジャーを作成し、Pivo​​tTablesとPivotChartsを作成してから、データをさらに分析することで、ITの支援を必要とせずにタイムリーなビジネス上の意思決定を行うことができます。 」

MicrosoftがPowerPivotで使用する主要な表現言語はDAX（Data Analysis Expressions）ですが、特定の状況で使用できる言語もあります。 繰り返しになりますが、Microsoftが説明しているように、「DAXは、数式または式で使用して1つ以上の値を計算して返すことができる関数、演算子、および定数のコレクションです。 もっと簡単に言えば、DAXは、モデルにすでに存在するデータから新しい情報を作成するのに役立ちます。」 幸いなことに、Excelに既に精通している人にとっては、DAX数式はよく知られているように見えます。これは、数式の多くが同様の構文（ SUM 、 AVERAGE 、 TRUNCなど）を持っているためです。

明確にするために、Power Pivotと基本的なExcelを使用する主な利点は、次のように要約できます。

• これにより、Excelに100万行をわずかに超える厳しい制約がある場合に、数億行のデータをインポートして操作できます。
• バージョン管理や転送可能性の問題に悩まされている複数のソースシートを作成しなくても、複数のソースから1つのソースワークブックにデータをインポートできます。
• インポートしたデータを操作して分析し、コンピューターの速度を低下させることなく結論を導き出すことができます。
• PivotChartsとPowerBIを使用してデータを視覚化できます。

次のセクションでは、上記のそれぞれについて説明し、Power PivotforExcelがどのように役立つかを示します。

パワーピボットの使い方

1）大規模なデータセットのインポート

以前に示唆したように、Excelの主な制限の1つは、非常に大きなデータセットの操作に関係しています。 幸いなことに、Excelは100万行の制限をはるかに超えてPowerPivotに直接ロードできるようになりました。

これを実証するために、9つの異なる製品カテゴリと4つの地域を持つスポーツ用品小売業者の2年分の売上のサンプルデータセットを生成しました。 結果のデータセットは200万行です。

リボンの[データ]タブを使用して、CSVファイルから新しいクエリを作成しました（以下の新しいクエリの作成を参照）。 この機能は以前はPowerQueryと呼ばれていましたが、Excel 2016および365では、Excelの[データ]タブにより緊密に統合されました。

新しいクエリの作成

Excelの空白のブックから、200万行すべてをPower Pivotにロードするまで、約1分かかりました。 最初の行を列名に昇格させることで、簡単なデータ書式設定を実行できたことに注意してください。 過去数年間で、Power Query機能は、Excelアドインから、ツールバーの[データ]タブの緊密に統合された部分に大幅に改善されました。 Power Queryは、一連のオプションと独自の言語Mを使用して、データをピボット、フラット化、クレンジング、および整形できます。

2）複数のソースからのデータのインポート

Power Pivot for Excelのその他の重要な利点の1つは、複数のソースからデータを簡単にインポートできることです。 以前は、私たちの多くがさまざまなデータソース用に複数のワークシートを作成していました。 多くの場合、このプロセスには、VBAコードの記述と、これらの異なるソースからのコピー/貼り付けが含まれていました。 ただし、幸いなことに、Power Pivotを使用すると、上記の問題に遭遇することなく、さまざまなデータソースからExcelに直接データをインポートできます。

図表1のクエリ機能を使用すると、次のソースのいずれかから取得できます。

• Microsoft Azure
• SQLサーバー
• Teradata
• フェイスブック
• Salesforce
• JSONファイル
• Excelワークブック
• …などなど

さらに、複数のデータソースをクエリ機能またはパワーピボットウィンドウのいずれかで組み合わせて、データを統合することができます。 たとえば、Excelワークブックから生産コストデータを取得し、SQLサーバーからPowerPivotへのクエリを介して実際の販売結果を取得できます。 そこから、生産バッチ番号を照合して2つのデータセットを組み合わせ、単位あたりの粗利益を生成できます。

3）大規模なデータセットの操作

Power Pivot for Excelのもう1つの重要な利点は、大規模なデータセットを操作および操作して、関連する結論と分析を引き出すことができることです。 以下のいくつかの一般的な例を実行して、ツールの能力を理解してください。

対策

Excel中毒者は、ピボットテーブルが最も便利なものの1つであると同時に、私たちが実行する最も苛立たしいタスクの1つであることに間違いなく同意します。 特に大きなデータセットでの作業に関してはイライラします。 幸い、Power Pivot for Excelを使用すると、より大きなデータセットを操作するときに、ピボットテーブルを簡単かつ迅速に作成できます。

下の「メジャーの作成」というタイトルの画像で、PowerPivotウィンドウが2つのペインに分割されていることに注目してください。 上のペインにはデータがあり、下のペインにはメジャーが格納されています。 メジャーは、データセット全体で実行される計算です。 強調表示されたセルに入力してメジャーを入力しました。

 Total Sales:=SUM('Accounting Data'[Amount])

これにより、[金額]列全体で合計される新しいメジャーが作成されます。 同様に、下のセルに別のメジャーを入力できます

Average Sales:=AVERAGE('Accounting Data'[Amount])

メジャーの作成

そこから、大規模なデータセットで使い慣れたピボットテーブルを作成するのがどれほど速いかを見てください。

ピボットテーブルの作成

ディメンションテーブル

Excelを使用する金融アナリストとして、私たちは複雑な数式を使用してテクノロジーを思い通りに曲げることに長けています。 VLOOKUP 、 SUMIF 、さらには恐ろしいINDEX(MATCH())をマスターします。 ただし、Power Pivotを使用することで、その多くをウィンドウの外に投げ出すことができます。

ユーザーが作成したテーブルをPowerPivotモデルに追加する

この機能を実証するために、各カテゴリをタイプに割り当てた小さな参照テーブルを作成しました。 「データモデルに追加」を選択すると、このテーブルがPower Pivotにロードされます（上記のPower Pivotモデルへのユーザー作成テーブルの追加を参照）。

また、データセットで使用する日付テーブルを作成しました（以下の日付テーブルの作成を参照）。 Power Pivot for Excelを使用すると、月、四半期、および曜日ごとに統合するために、日付テーブルをすばやく簡単に作成できます。 ユーザーは、よりカスタムな日付テーブルを作成して、週、会計年度、または組織固有のグループ別に分析することもできます。

日付テーブルの作成

計算列

メジャーの他に、計算列という別のタイプの計算があります。 Excelユーザーは、データテーブルで数式を作成するのと非常によく似ているため、これらの数式を快適に作成できます。 以下に新しい計算列を作成しました（以下の計算列の作成を参照）。これは、会計データテーブルを金額で並べ替えます。 50ドル未満の売上には「小」のラベルが付けられ、その他はすべて「大」のラベルが付けられます。 数式は直感的ではありませんか？

計算列の作成

関係

次に、ダイアグラムビューを使用して、AccountingDataテーブルのCategoryフィールドとCategoryテーブルのCategoryフィールドの間に関係を作成できます。 さらに、AccountingDataテーブルのSalesDateフィールドとCalendarテーブルのDateフィールドの間の関係を定義できます。

関係の定義

これで、 SUMIFまたはVLOOKUP関数を必要とせずに、トランザクションサイズのスライサーを使用して、年ごとの総売上高とタイプを計算するピボットテーブルを作成できます。

リレーションシップを使用したピボットテーブル

または、新しいカレンダーテーブルを使用して、曜日ごとの平均売上のグラフを作成できます。

リレーションシップを使用したピボットチャート

このグラフは単純に見えますが、売上データに新しい列を追加せずに、200万行を超えるデータの統合を作成するのに10秒もかからなかったことは印象的です。

これらの統合されたレポートシナリオをすべて実行できますが、個々の広告申込情報にいつでもドリルダウンできます。 非常に詳細なデータを保持します。

高度な機能

これまでのところ、私が示した分析のほとんどは比較的簡単な計算です。 ここで、このプラットフォームのより高度な機能のいくつかを示したいと思います。

タイムインテリジェンス

多くの場合、財務結果を調べるときは、前年と比較できる時間枠と比較したいと思います。 Power Pivotには、いくつかの組み込みのタイムインテリジェンス機能があります。

 Same Period Last Year Sales:=CALCULATE([Total Sales],SAMEPERIODLASTYEAR('Calendar'[Date])) YOY Sales Growth:=if(not(ISBLANK([Same Period Last Year Sales])),([Total Sales]/[Same Period Last Year Sales])-1,BLANK())

たとえば、上記の2つのメジャーをPower Pivotのアカウンティングデータテーブルに追加すると、数回クリックするだけで次のピボットテーブルを作成できます。

タイムインテリジェンスピボットテーブル

不一致の粒状性

金融アナリストとして、私がしばしば解決しなければならない問題の1つは、不一致の粒度の問題です。 この例では、実際の売上データがカテゴリレベルで表示されていますが、季節レベルのみの予算を準備しましょう。 このミスマッチをさらに助長するために、売上データが毎日であっても、四半期予算を作成します。

不一致の粒度–予算表

Power Pivot for Excelを使用すると、この不整合を簡単に解決できます。 2つの追加の参照テーブル、またはデータベースの命名法でディメンションテーブルを作成することにより、予算額に対して実際の売上を分析するための適切な関係を作成できるようになりました。

不一致の粒状性–関係

Excelでは、次のピボットテーブルがすぐに統合されます。

不一致の粒度–予算と実際の結果

さらに、実際の売上と予算売上の差異を計算する新しいメジャーを次のように定義できます。

 Actual-to-Budget Variance:=DIVIDE([Total Sales],[Total Budgeted Sales])-1

このメジャーを使用して、ピボットテーブルに分散を表示できます。

不一致の粒度–分散の結果

合計のパーセント

最後に、特定のカテゴリの売上をすべての売上のパーセントとして調べ（たとえば、全体の売上に対するカテゴリの寄与）、特定のカテゴリの売上を同じタイプのすべての売上のパーセントとして調べます（たとえば、季節タイプへのカテゴリの寄与）。販売）。 以下の2つのメジャーを作成しました。

 Total Sales as Percent of All Sales:=[Total Sales]/CALCULATE([Total Sales],ALL('Accounting Data')) Total Sales as Percent of Type:=[Total Sales]/CALCULATE([Total Sales],ALL('Accounting Data'[Category]))

これらのメジャーは、新しいピボットテーブルに展開できるようになりました。

合計のパーセント

カテゴリレベルと季節タイプレベルの両方で計算がどのように実行されるかに注意してください。 このような大規模なデータセットでこれらの計算がどれほど迅速かつ簡単に実行されるかが気に入っています。 これらは、PowerPivotの優雅さと純粋な計算能力のほんの一例です。

圧縮

もう1つの利点は、ファイルサイズが縮小することです。 元のファイルサイズは91MBでしたが、現在は4MB未満です。 これは、元のファイルの96％の圧縮です。

ファイルサイズ

これはどのように起こりますか？ Power Pivotは、xVelocityエンジンを使用してデータを圧縮します。 簡単に言うと、データは行ではなく列に格納されます。 この保存方法により、コンピューターは重複する値を圧縮できます。 この例のデータセットでは、200万行すべてにわたって繰り返される領域は4つだけです。 Power Pivot for Excelは、このデータをより効率的に保存できます。 その結果、繰り返し値が多いデータの場合、このデータを保存するのにかかるコストははるかに少なくなります。

注意すべき点の1つは、このサンプルデータセットで1ドルの金額を使用したことです。 セントを反映するために小数点以下2桁を含めると、圧縮効果は元のファイルサイズの80％にまで減少します。

SSAS表形式

Power Pivotモデルは、企業全体に拡張することもできます。 組織内で多くのユーザーを獲得し始めるPowerPivotモデルを構築した場合、またはデータが1,000万行に増加した場合、あるいはその両方であるとします。 この時点で、30人の異なるユーザーがモデルを更新したり変更したりすることは望ましくない場合があります。 モデルはシームレスにSSAS表形式に変換できます。 すべてのテーブルと関係は保持されますが、更新頻度を制御し、さまざまなユーザーに役割（読み取り専用、読み取り、処理など）を割り当て、表形式モデルにリンクする小さなExcelフロントエンドのみを展開できるようになりました。 その結果、ユーザーは小さなワークブックを使用してデプロイされた表形式モデルにアクセスできますが、数式やメジャーにはアクセスできません。

4）データの視覚化と分析

CUBEフォーミュラ

クライアントからの絶え間ない要求の1つは、厳密に定義されたレイアウトに準拠したレポートを作成することです。 特定の列幅、RGBカラーコード、および事前定義されたフォント名とサイズを要求するクライアントがあります。 次のダッシュボードについて考えてみます。

埋め込まれたCUBE式

すべての売上がPowerPivotfor Excelに格納されている場合、ピボットテーブルを生成せずに売上数を入力するにはどうすればよいですか？ CUBE式を使用してください！ 任意のExcelセル内にCUBE数式を記述でき、既に作成したPowerPivotモデルを使用して計算を実行します。

たとえば、「2016TotalSales」の下のセルに次の数式を入力します。

= CUBEVALUE（ " ThisWorkbookDataModel "、" 【対策】【総売上高】 "、" [カレンダー]。[年]。[2016] "）

黄色で強調表示されている式の最初の部分は、PowerPivotモデルの名前を示しています。 一般に、Power PivotforExcelの新しいバージョンの場合は通常ThisWorkbookDataModelです。 緑の部分は、メジャーTotalSalesを使用することを定義します。 青い部分は、2016年に等しい年の販売日を持つ行のみをフィルタリングするようにExcelに指示します。

舞台裏では、Power Pivotは、データ、計算された列、およびメジャーを使用してオンライン分析処理（OLAP）キューブを構築しました。 この設計により、ExcelユーザーはCUBE関数を使用して直接フェッチすることでデータにアクセスできます。 CUBE式を使用して、事前定義されたレイアウトに準拠する完全な財務諸表を作成することができました。 この機能は、財務分析にPower PivotforExcelを使用する際のハイライトの1つです。

Power BI

Power Pivot for Excelのもう1つの利点は、作成したPower Pivotブックをすばやく取得して、PowerBIモデルにすばやく変換できることです。 ExcelブックをPowerBIDesktopアプリまたはPowerBIOnlineに直接インポートすることで、データを分析、視覚化、および組織内のすべてのユーザーと共有できます。 基本的に、Power BIは、Power Pivot、PowerQuery、およびSharePointをすべて1つにまとめたものです。 以下では、以前のPower PivotforExcelブックをPowerBIデスクトップアプリケーションにインポートしてダッシュボードを作成しました。 インターフェイスがいかにインタラクティブであるかに注目してください。

Power BI

Power BIの優れた点の1つは、自然言語のQ＆Aです。 実例を示すために、PowerBIモデルをオンラインのPowerBIアカウントにアップロードしました。 Webサイトから質問をすることができ、Power BIは、次のように入力すると適切な分析を構築します。

自然言語に関するQ＆A

このタイプのクエリ機能を使用すると、ユーザーはデータモデルについて質問したり、Excelよりも簡単な方法でデータを操作したりできます。

Power BIのもう1つの利点は、Microsoftの開発者がPowerBIの更新を絶えずリリースしていることです。 多くのユーザーが要求する新機能は、毎月プッシュされます。 何よりも、これはPower PivotforExcelからのシームレスな移行です。 したがって、DAX式の学習に投資した時間は、PowerBIに展開できます。 さまざまなデバイスの多くのユーザーと分析を共有する必要があるアナリストにとって、PowerBIは調査する価値があるかもしれません。

ベストプラクティス

開始したら、従う必要のあるいくつかのベストプラクティスがあります。

1つ目は、最初に何をインポートするかを慎重に決定することです。 営業担当者の自宅の住所を使用することはありますか？ このワークブックのコンテキストで顧客の電子メールアドレスを知る必要がありますか？ 目標がデータをダッシュ​​ボードに集約することである場合、利用可能なデータの一部はそれらの計算に必要ありません。 入ってくるデータのキュレーションに時間を費やすと、後でデータセットが拡張されたときに問題とメモリ使用量が大幅に軽減されます。

もう1つのベストプラクティスは、PowerPivotはExcelではないことを覚えておくことです。 Excelでは、ワークシートを常に右に拡張して計算を作成することに慣れています。 Power Pivot for Excelは、マニフェストデスティネーションに対するこの欲求を制限する場合、データを最も効率的に処理します。 データの右側に計算列を継続的に作成する代わりに、下部ペインにメジャーを記述する方法を学びます。 この習慣により、ファイルサイズが小さくなり、計算が速くなります。

最後に、対策には平易な英語の名前を使用することをお勧めします。 これは採用するのに長い時間がかかりました。 最初の数年間はSumExpPctTotalのような名前を作成していましたが、他の人が同じワークブックを使い始めると、多くの説明が必要になりました。 これで、新しいワークブックを開始するときに、 Expense Line Item as Percent of Total Expenses使用します。 名前は長くなりますが、他の人が使用する方がはるかに簡単です。

実際のユースケース

この記事では、Power PivotforExcelを使用して単純なExcelを超えた重要な一歩を踏み出すことができる方法のほんの一握りを紹介しました。 Power PivotforExcelが非常に役立つことがわかった実際のユースケースを強調することは有用だと思いました。

ここにあるいくつかの：

• さまざまな時間範囲にわたる資産の大規模なポートフォリオのパフォーマンスを分析する： Power Pivot for Excelを使用すると、期間を前の期間と比較するメジャーを定義できるため、四半期ごと、年ごと、いくつかのメジャーを記述するだけで、月ごとのパフォーマンスはすべてローリングベースになります。
• カスタマイズされた集計レベルを使用して会計データを要約します。名前、カテゴリ、および財務諸表で各総勘定元帳の明細を識別することにより、適切な明細を含むレポートをすばやく作成できます。
• チェーンは既存店売上高を識別できます。店舗がオンラインになったときにマップするテーブルを使用して、財務結果を既存店ベースで比較できます。
• 売上のパフォーマンスの高低を特定する：売上、粗利益、生産期間などで上位5つのSKUと下位5つのSKUを強調表示するピボットテーブルを作成できます。
• 小売業者は、4-4-5構成を使用するカレンダーテーブルを定義できます。カスタム日付テーブルを使用すると、小売業者は特定の4-4-5月に毎日を簡単に割り当てることができ、その後、毎日の売上結果を対応する月にロールバックできます。

不格好なスプレッドシートから最新のワークブックまで

金融アナリストとして、私たちは拡大し続けるデータセットに対して複雑な計算を実行する必要があります。 Excelはすでにデフォルトの分析ツールであるため、Power Pivotの学習曲線は簡単であり、多くの関数はExcelのネイティブ関数を反映しています。

Power Pivot for Excelは、CUBE関数を使用して、既存のExcelワークブックにシームレスに溶け込みます。 計算効率の向上は見逃せません。 保守的な処理速度が20％速いと仮定すると、Excel内で1日6時間費やす金融アナリストは、年間300時間を節約できます。

さらに、従来のExcelで以前よりもはるかに大きいデータセットを分析できるようになりました。 効率的に設計されたモデルを使用すると、迅速な分析の俊敏性を維持しながら、従来のExcelで許可されていたデータ量の10倍のデータを簡単に取得できます。 モデルをPowerPivotからSSASTabularに変換する機能により、処理できるデータの量は、Excelで達成できる量の100〜1,000倍になります。

Power Pivot for Excelは、大量のデータに対して超高速の計算を実行し、詳細を掘り下げる機能を保持しているため、財務分析を不格好なスプレッドシートから最新のワークブックに変換できます。

Power Pivot for Excelを試してみたい場合は、以下の資料を参考にしてください。

便利なリファレンスとガイド

Collie、R.、およびSingh、A.（2016）。 PowerPivotおよびPowerBI：Excel 2010-2016でのDAX、Power Query、Power BI、およびPowerPivotに関するExcelユーザーガイド。 アメリカ合衆国：ホーリーマクロ！ 本。

フェラーリ、A。、およびルッソ、M。（2015）。 DAXの最も信頼のおけるガイド：Microsoft Excel、SQL Server Analysis Services、およびPowerBIを使用したビジネスインテリジェンス。 米国：Microsoft Press、米国。