データセットをダウンロードする ここに チュートリアルに従ってください。
金融、財務分析、金融市場、金融投資にまたがるさまざまなフィールドとサブサブフィールドにわたって、マイクロソフト Excel 王様です。の到着と指数関数的成長とともに ビッグデータ ただし、数十年にわたるデータの集約と蓄積、安価なクラウドストレージの出現、モノのインターネットの台頭によって推進されています。 eコマース、ソーシャルメディア、およびデバイスの相互接続性 —Excelのレガシー機能と機能は限界に達しています。
具体的には、旧世代のExcelのインフラストラクチャと、1,048,576行の行制限や、大規模なデータセット、データテーブル、相互接続されたスプレッドシートが関係する場合の必然的な処理速度の低下などの処理制限により、効果的なビッグデータツールとしての使いやすさが低下しました。ただし、2010年に、MicrosoftはExcelに新しいディメンションを追加しました。 パワーピボット 。 Power Pivotは、処理速度を損なうことなく、ほぼ無制限のデータセットを抽出、結合、分析する機能において、Excelに次世代のビジネスインテリジェンスおよびビジネス分析機能を提供しました。しかし、8年前にリリースされたにもかかわらず、ほとんどの金融アナリストはPower Pivotの使用方法をまだ知らず、多くの人はPowerPivotが存在することすら知りません。
この記事では、Power Pivotを使用して一般的なExcelの問題を克服する方法を示し、いくつかの例を使用してソフトウェアのその他の主要な利点を確認します。このPowerPivotチュートリアルは、このツールで達成できることのガイドとして機能することを目的としており、最後に、PowerPivotが非常に貴重であることが多いいくつかのサンプルユースケースを探ります。
Power Pivotは、Excel2010および2013のアドインとして導入されたMicrosoftExcelの機能であり、現在はExcel2016および365のネイティブ機能です。Microsoftとして 説明します 、Power Pivot for Excelを使用すると、複数のデータソースから数百万行のデータを単一のExcelワークブックにインポートし、異種データ間の関係を作成し、数式を使用して計算列とメジャーを作成し、ピボットテーブルとピボットグラフを作成して、データをさらに分析できます。 ITの支援を必要とせずに、タイムリーなビジネス上の意思決定を行えるようにします。」
MicrosoftがPowerPivotで使用する主要な表現言語はDAX(Data Analysis Expressions)ですが、特定の状況で使用できる言語もあります。繰り返しになりますが、Microsoftが説明しているように、「DAXは、数式または式で使用して1つ以上の値を計算して返すことができる関数、演算子、および定数のコレクションです。もっと簡単に言えば、DAXは、モデルにすでに存在するデータから新しい情報を作成するのに役立ちます。」幸いなことに、Excelに既に精通している人にとっては、DAX数式はよく知られているように見えます。これは、数式の多くが同様の構文を持っているためです(たとえば、SUM
、AVERAGE
、TRUNC
)。
明確にするために、Power Pivotと基本的なExcelを使用する主な利点は、次のように要約できます。
グラフィックデザインの要素と原則
次のセクションでは、上記のそれぞれについて説明し、Power Pivot forExcelがどのように役立つかを示します。
以前に示唆したように、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サーバーから実際の販売結果をQuery into 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%の圧縮です。
これはどのように起こりますか? PowerPivotは xVelocity データを圧縮するエンジン。簡単に言うと、データは行ではなく列に格納されます。この保存方法により、コンピューターは重複する値を圧縮できます。この例のデータセットでは、200万行すべてにわたって繰り返される領域は4つだけです。 Power Pivot for Excelは、このデータをより効率的に保存できます。その結果、繰り返し値が多いデータの場合、このデータを保存するのにかかるコストははるかに少なくなります。
注意すべき点の1つは、このサンプルデータセットで1ドルの金額を使用したことです。セントを反映するために小数点を2つ含めた場合、圧縮効果は元のファイルサイズの80%にまで減少します。
Power Pivotモデルは、企業全体に拡張することもできます。組織内で多くのユーザーを獲得し始めるPowerPivotモデルを構築した場合、またはデータが1,000万行に増加した場合、あるいはその両方であるとします。この時点で、30人の異なるユーザーがモデルを更新したり変更したりすることは望ましくない場合があります。モデルはシームレスにSSAS表形式に変換できます。すべてのテーブルと関係が保持されますが、更新頻度を制御し、さまざまなユーザーに役割(読み取り専用、読み取り、処理など)を割り当て、表形式モデルにリンクする小さなExcelフロントエンドのみを展開できるようになりました。その結果、ユーザーは小さなワークブックを使用してデプロイされた表形式モデルにアクセスできますが、数式やメジャーにはアクセスできません。
クライアントからの絶え間ない要求の1つは、厳密に定義されたレイアウトに準拠するレポートを作成することです。特定の列幅、RGBカラーコード、および事前定義されたフォント名とサイズを要求するクライアントがあります。次のダッシュボードについて考えてみます。
すべての売上がPowerPivot for Excelで処理されている場合、ピボットテーブルを生成せずに売上数を入力するにはどうすればよいですか? CUBE式を使用!任意のExcelセル内にCUBE数式を記述でき、既に作成したPowerPivotモデルを使用して計算を実行します。
たとえば、「2016 TotalSales」の下のセルに次の数式を入力します。
SumExpPctTotal
黄色で強調表示されている式の最初の部分は、PowerPivotモデルの名前を示しています。一般に、Power Pivot forExcelの新しいバージョンの場合は通常ThisWorkbookDataModelです。緑の部分は、メジャーTotalSalesを使用することを定義します。青い部分は、2016年に等しい年の販売日を持つ行のみをフィルタリングするようにExcelに指示します。
舞台裏では、Power Pivotは、データ、計算された列、およびメジャーを使用してオンライン分析処理(OLAP)キューブを構築しました。この設計により、ExcelユーザーはCUBE関数を使用して直接フェッチすることでデータにアクセスできます。 CUBE式を使用して、事前定義されたレイアウトに準拠する完全な財務諸表を作成することができました。この機能は、財務分析にPower Pivot forExcelを使用する際のハイライトの1つです。
Power Pivot for Excelのもう1つの利点は、作成したPower Pivotブックをすばやく取得して、PowerBIモデルにすばやく変換できることです。 ExcelブックをPowerBIDesktopアプリまたはPowerBI Onlineに直接インポートすることで、データを分析、視覚化、および組織内の誰とでも共有できます。基本的に、PowerBIはPowerPivot、PowerQuery、およびSharePointがすべて1つにまとめられています。以下では、以前のPower Pivot forExcelブックをPowerBIデスクトップアプリケーションにインポートしてダッシュボードを作成しました。インターフェイスがいかにインタラクティブであるかに注目してください。
Power BIの優れた点の1つは、自然言語のQ&Aです。実例を示すために、PowerBIモデルをオンラインのPowerBIアカウントにアップロードしました。 Webサイトから質問をすることができ、PowerBIは入力時に適切な分析を構築します。
このタイプのクエリ機能により、ユーザーはデータモデルに質問したり、Excelよりも簡単な方法でデータを操作したりできます。
Power BIのもう1つの利点は、Microsoftの開発者が常に更新をリリースしていることです。多くのユーザーが要求する新機能は、毎月プッシュされます。何よりも、これはPower Pivot forExcelからのシームレスな移行です。したがって、DAX式の学習に費やした時間は、PowerBIに展開できます。さまざまなデバイス上の多くのユーザーと分析を共有する必要があるアナリストにとって、PowerBIは調査する価値があるかもしれません。
開始したら、従う必要のあるいくつかのベストプラクティスがあります。
1つ目は、最初に何をインポートするかを慎重に決定することです。営業担当者の自宅の住所を使用することはありますか?このワークブックのコンテキストで顧客の電子メールアドレスを知る必要がありますか?目標がデータをダッシュボードに集約することである場合、利用可能なデータの一部はそれらの計算に必要ありません。入ってくるデータのキュレーションに時間を費やすと、後でデータセットが拡張されたときに問題とメモリ使用量が大幅に軽減されます。
もう1つのベストプラクティスは、PowerPivotはExcelではないことを覚えておくことです。 Excelでは、ワークシートを常に右に拡張して計算を作成することに慣れています。 Power Pivot for Excelは、マニフェストデスティネーションに対するこの欲求を制限すると、データを最も効率的に処理します。データの右側に計算列を継続的に作成する代わりに、下部ペインにメジャーを書き込む方法を学びます。この習慣により、ファイルサイズが小さくなり、計算が速くなります。
最後に、対策には平易な英語の名前を使用することをお勧めします。これは採用するのに長い時間がかかりました。最初の数年間はExpense Line Item as Percent of Total Expenses
のような名前を作成していましたが、他の人が同じワークブックを使い始めると、説明することがたくさんありました。これで、新しいブックを開始するときに、
|_+_|のようなメジャー名を使用します。名前は長くなりますが、他の人が使用する方がはるかに簡単です。
この記事では、Power Pivot forExcelを使用して単純なExcelを超えた重要な一歩を踏み出すことができる方法のほんの一握りを紹介しました。 Power Pivot forExcelが非常に役立つことがわかった実際のユースケースをいくつか取り上げると便利だと思いました。
ここに幾つかあります:
金融アナリストとして、私たちは拡大し続けるデータセットに対して複雑な計算を実行する必要があります。 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、米国。
Power Pivotは、速度や機能を損なうことなくビッグデータのインポート、操作、分析を可能にするExcelの機能です。 Power Pivotテーブルは、ユーザーが異なるテーブルのデータを混合できるようにするピボットテーブルであり、複数のテーブルで作業するときに強力なフィルターチェーンを提供します。
まず、データセットをPowerPivotワークブックにインポートします。次に、[Power PivotHome]タブの[PowerPivot]ウィンドウで、[PivotTable]をクリックします。次に、「新しいワークシート」を選択します(Excelは空のピボットテーブルを追加します)。次に、空のピボットテーブルを選択し、そこに記載されている手順に従います。
まず、データセットをPowerPivotワークブックにインポートします。次に、PowerPivotテーブルを作成します。次に、[Power Pivot]タブで、[PivotTable]の下の矢印をクリックし、[PivotChart]を選択します。 「既存のワークシート」を選択し、「OK」をクリックします。 Excelは、同じワークシートに空のPivotChartを追加します。そこにある指示に従ってください。