この記事のバージョンはMariselaOrdazによって翻訳されました ここでデータセットをダウンロードして、チュートリアルに従ってください。
金融、財務分析、金融市場、および金融投資にまたがるさまざまなフィールドおよびサブサブフィールド全体で、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ワークブックにまとめ、異種データ間の関係を作成し、数式を使用して計算列とメジャーを作成し、PivotTablesとPivotChartsを作成してから、データをさらに分析することで、ITの支援を必要とせずにタイムリーなビジネス上の意思決定を行うことができます。 」
MicrosoftがPowerPivotで使用する主要な表現言語はDAX（データ分析式）ですが、特定の状況で使用できる言語もあります。繰り返しになりますが、Microsoftが説明しているように、「DAXは、数式または式で使用して1つ以上の値を計算して返すことができる関数、演算子、および定数のコレクションです。もっと簡単に言えば、DAXは、モデルにすでに存在するデータから新しい情報を作成するのに役立ちます。」幸いなことに、Excelに既に精通している人にとっては、DAX数式はよく知られているように見えます。これは、数式の多くが同様の構文（SUMなど）を持っているためです。 、AVERAGE 、TRUNC 。
わかりやすくするために、Power Pivotと基本的なExcelを使用する主な利点は、次のように要約できます。
次のセクションでは、上記のそれぞれについて説明し、Power PivotforExcelがどのように役立つかを示します。
以前に示唆したように、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を使用して、データをピボット、フラット化、クレンジング、および整形できます。
Power Pivot for Excelのその他の重要な利点の1つは、複数のソースからデータを簡単にインポートできることです。以前は、私たちの多くがさまざまなデータソース用に複数のワークシートを作成していました。多くの場合、このプロセスには、VBAコードの記述と、これらの異なるソースからのコピー/貼り付けが含まれていました。ただし、幸いなことに、Power Pivotを使用すると、上記の問題に遭遇することなく、さまざまなデータソースからExcelに直接データをインポートできます。
図表1のクエリ機能を使用すると、次のソースのいずれかから取得できます。
さらに、複数のデータソースをクエリ機能またはパワーピボットウィンドウのいずれかで組み合わせて、データを統合することができます。たとえば、Excelワークブックから生産コストデータを取得し、SQLサーバーからPowerPivotへのクエリを介して実際の販売結果を取得できます。そこから、生産バッチ番号を照合して2つのデータセットを組み合わせ、単位あたりの粗利益を生成できます。
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を使用することで、その多くをウィンドウの外に投げ出すことができます。
この機能を実証するために、各カテゴリをタイプに割り当てた小さな参照テーブルを作成しました。 [データモデルに追加]を選択すると、このテーブルがPower Pivotに読み込まれます（ユーザーが作成したテーブルをPowerPivotモデルに追加するを参照）。 上記）。
データセットで使用する日付テーブルも作成しました（日付テーブルの作成を参照）。 下）。 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％にまで減少します。
Power Pivotモデルは、企業全体に拡張することもできます。組織内で多くのユーザーを獲得し始めるPowerPivotモデルを構築した場合、またはデータが1,000万行に増加した場合、あるいはその両方であるとします。この時点で、30人の異なるユーザーがモデルを更新したり変更したりすることは望ましくない場合があります。モデルはシームレスにSSAS表形式に変換できます。すべてのテーブルと関係は保持されますが、更新頻度を制御し、さまざまなユーザーに役割（読み取り専用、読み取り、処理など）を割り当て、表形式モデルにリンクする小さなExcelフロントエンドのみを展開できるようになりました。その結果、ユーザーは小さなワークブックを使用してデプロイされた表形式モデルにアクセスできますが、数式やメジャーにはアクセスできません。
クライアントからの絶え間ない要求の1つは、厳密に定義されたレイアウトに準拠したレポートを作成することです。特定の列幅、RGBカラーコード、および事前定義されたフォント名とサイズを要求するクライアントがあります。次のダッシュボードについて考えてみます。
すべての売上がPowerPivotfor Excelに格納されている場合、ピボットテーブルを生成せずに売上数を入力するにはどうすればよいですか？ CUBE式を使用してください！任意のExcelセル内にCUBE数式を記述でき、既に作成したPowerPivotモデルを使用して計算を実行します。
たとえば、「2016 TotalSales」の下のセルに次の数式を入力します：
=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Total Sales]","[Calendar].[Year].[2016]")
黄色で強調表示されている式の最初の部分は、PowerPivotモデルの名前を示しています。一般に、Power PivotforExcelの新しいバージョンの場合は通常ThisWorkbookDataModelです。緑の部分は、メジャーTotalSalesを使用することを定義します。青い部分は、2016年に等しい年の販売日を持つ行のみをフィルタリングするようにExcelに指示します。
舞台裏では、Power Pivotは、データ、計算された列、およびメジャーを使用してオンライン分析処理（OLAP）キューブを構築しました。この設計により、ExcelユーザーはCUBE関数を使用して直接フェッチすることでデータにアクセスできます。 CUBE式を使用して、事前定義されたレイアウトに準拠する完全な財務諸表を作成することができました。この機能は、財務分析にPower PivotforExcelを使用する際のハイライトの1つです。
Power Pivot for Excelのもう1つの利点は、作成したPower Pivotブックをすばやく取得して、PowerBIモデルにすばやく変換できることです。 ExcelブックをPowerBIDesktopアプリまたはPowerBIOnlineに直接インポートすることで、データを分析、視覚化、および組織内のすべてのユーザーと共有できます。基本的に、Power BIは、Power Pivot、PowerQuery、およびSharePointをすべて1つにまとめたものです。以下では、以前のPower PivotforExcelブックをPowerBIデスクトップアプリケーションにインポートしてダッシュボードを作成しました。インターフェースがいかにインタラクティブであるかに注目してください：
Power BIの優れた点の1つは、自然言語に関するQ＆Aです。実例を示すために、PowerBIモデルをオンラインのPowerBIアカウントにアップロードしました。 Webサイトから質問をすることができ、PowerBIは入力時に適切な分析を作成します。
このタイプのクエリ機能により、ユーザーはデータモデルについて質問し、Excelよりも簡単な方法でデータを操作できます。
Power BIのもう1つの利点は、Microsoftの開発者が常に更新をリリースしていることです。多くのユーザーが要求する新機能は、毎月プッシュされます。何よりも、これは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が非常に役立つことがわかった実際のユースケースをいくつか取り上げると便利だと思いました。
ここにいくつかあります：
金融アナリストとして、私たちは拡大し続けるデータセットに対して複雑な計算を実行する必要があります。 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、米国。