PowerPivot や Excel 2013 以降の新機能である「リレーションシップ」を使いテーブルをデータ モデルに追加するとピボットテーブルの「グループ化」や「集計フィールド」、「集計アイテム」が使えなくなることはすでに紹介した。
http://road2cloudoffice.blogspot.jp/2014/11/powerpivot.html
結論から言えば「データ モデル」を使った場合は PowerPivot を併用しないと、これまでピボットテーブルで行っていたことができないと思ってもいいだろう。前回は「階層」を使ったグループ化を紹介したが、今回は集計フィールドや集計アイテムに相当する「計算列」と「計算フィールド」について紹介する。
なお、繰り返しになるが、データ モデルを使うことで「グループ化」や「集計フィールド」や「集計アイテム」が使えなくなり、「新しい Excel 使えない」と早合点しないでほしい。そもそも PowerPivot は SQL Server や SQL Server Analysis Services と Excel を使った「データ分析」のために作られた Excel のアドイン機能であり、そのような分析のための機能は当然持っている。
そして、わざわざデータ モデルを使う理由はある。一つの例として巨大なデータを扱えることだ。データ モデルにすることでワークシートの制限がはずれ、100万行をゆうに超えるデータを扱えるようになるからだ。PowerPivot で扱う元データが数千万件であっても問題ない。そして、何件まで扱えるかは PC に搭載されているメモリーに依存する。そのため、そのような巨大なデータを扱う場合は Windows は 64bit を使い、Excel も 64 bit 版のものを使うことが推奨される。
計算列はすべてのレコードを対象に計算する
計算列や計算フィールドは PowerPivot ウィンドウで設定する。ここで設定をすることで、Excel のピボットテーブルのフィールドリストに表示され利用可能になる。そこからはこれまでのピボットテーブルと同様の操作性を提供することになる。
以下のようなリレーションのテーブルを例にして説明する。
個人売上テーブルのデータは「日付」、「名前」、「商品」、「個数」しかない。ここに売上金額の計算結果を追加したい、というケースだ。
個人売上テーブルの PowerPivot ウィンドウのデータビューが以下になる。
欲しいのは商品の単価と、個数と単価を掛け合わした金額である。
すでに個人売上テーブルと商品テーブルは「商品コード」(A,B,C,,,)でリレーションを張っているので、商品テーブルから「単価」の列をこの個人売上テーブルに追加する。
そのときに使うのが DAX 関数の RELATED 関数である。RELATED 関数を使うことで、関連付けられているテーブルから該当する「単価」が参照できる。Excel の VLOOKUP 関数のような働きをすると考えれば理解しやすいであろう。
以下がその操作である。Excel 同様に数式オートコンプリートも使える。非常に簡単なのがわかると思う。
次に個数と単価を掛け合わせた金額の計算列を追加する。これは Excel の数式とまったく同じ操作性だ。しかし、1点注意しなければならないのは Excel のテーブル同様、構造化参照の数式のように、最初の行に数式を入力することですべての行に対して同じ数式が入力されることだ。
これで金額の計算列が追加された。ここからピボットテーブルを作ってみよう。
そうすると、先ほど追加した計算列の「単価」と「金額」がフィールドリストにも表示されていることが確認できる。
個人売上テーブルには「商品名」はなかったが、商品テーブルとリレーションを張っているのでピボットテーブルでは参照可能だ。このためわざわざ商品名を RELATED 関数で参照する必要はない。
計算列ではほぼワークシート関数と同じものを使用することができる。
IF なども利用可能なので、単純な数値演算だけでなく、論理演算も可能だ。
Excel をすでに使いこなしているユーザーであれば、PowerPivot の計算列をすぐに活用できるだろう。
長くなったので、次回で「計算フィールド」について紹介したい。計算列がすべてのレコードを対象にして計算するものであり、計算フィールドは「ある条件で絞られたレコードを対象に計算する」と考えれば理解しやすいだろう。
マイクロソフトの記事
PowerPivotの計算列
https://support.office.com/ja-jp/article/PowerPivot-%E3%81%AE%E8%A8%88%E7%AE%97%E5%88%97-a0eb7167-33fc-4ade-a23f-fb9217c193af?ui=ja-JP&rs=ja-JP&ad=JP
PowerPivotの自習書
http://road2cloudoffice.blogspot.jp/2014/11/powerpivot.html
結論から言えば「データ モデル」を使った場合は PowerPivot を併用しないと、これまでピボットテーブルで行っていたことができないと思ってもいいだろう。前回は「階層」を使ったグループ化を紹介したが、今回は集計フィールドや集計アイテムに相当する「計算列」と「計算フィールド」について紹介する。
なお、繰り返しになるが、データ モデルを使うことで「グループ化」や「集計フィールド」や「集計アイテム」が使えなくなり、「新しい Excel 使えない」と早合点しないでほしい。そもそも PowerPivot は SQL Server や SQL Server Analysis Services と Excel を使った「データ分析」のために作られた Excel のアドイン機能であり、そのような分析のための機能は当然持っている。
そして、わざわざデータ モデルを使う理由はある。一つの例として巨大なデータを扱えることだ。データ モデルにすることでワークシートの制限がはずれ、100万行をゆうに超えるデータを扱えるようになるからだ。PowerPivot で扱う元データが数千万件であっても問題ない。そして、何件まで扱えるかは PC に搭載されているメモリーに依存する。そのため、そのような巨大なデータを扱う場合は Windows は 64bit を使い、Excel も 64 bit 版のものを使うことが推奨される。
計算列はすべてのレコードを対象に計算する
計算列や計算フィールドは PowerPivot ウィンドウで設定する。ここで設定をすることで、Excel のピボットテーブルのフィールドリストに表示され利用可能になる。そこからはこれまでのピボットテーブルと同様の操作性を提供することになる。
以下のようなリレーションのテーブルを例にして説明する。
個人売上テーブルのデータは「日付」、「名前」、「商品」、「個数」しかない。ここに売上金額の計算結果を追加したい、というケースだ。
個人売上テーブルの PowerPivot ウィンドウのデータビューが以下になる。
欲しいのは商品の単価と、個数と単価を掛け合わした金額である。
すでに個人売上テーブルと商品テーブルは「商品コード」(A,B,C,,,)でリレーションを張っているので、商品テーブルから「単価」の列をこの個人売上テーブルに追加する。
そのときに使うのが DAX 関数の RELATED 関数である。RELATED 関数を使うことで、関連付けられているテーブルから該当する「単価」が参照できる。Excel の VLOOKUP 関数のような働きをすると考えれば理解しやすいであろう。
以下がその操作である。Excel 同様に数式オートコンプリートも使える。非常に簡単なのがわかると思う。
次に個数と単価を掛け合わせた金額の計算列を追加する。これは Excel の数式とまったく同じ操作性だ。しかし、1点注意しなければならないのは Excel のテーブル同様、構造化参照の数式のように、最初の行に数式を入力することですべての行に対して同じ数式が入力されることだ。
これで金額の計算列が追加された。ここからピボットテーブルを作ってみよう。
そうすると、先ほど追加した計算列の「単価」と「金額」がフィールドリストにも表示されていることが確認できる。
個人売上テーブルには「商品名」はなかったが、商品テーブルとリレーションを張っているのでピボットテーブルでは参照可能だ。このためわざわざ商品名を RELATED 関数で参照する必要はない。
計算列ではほぼワークシート関数と同じものを使用することができる。
IF なども利用可能なので、単純な数値演算だけでなく、論理演算も可能だ。
Excel をすでに使いこなしているユーザーであれば、PowerPivot の計算列をすぐに活用できるだろう。
長くなったので、次回で「計算フィールド」について紹介したい。計算列がすべてのレコードを対象にして計算するものであり、計算フィールドは「ある条件で絞られたレコードを対象に計算する」と考えれば理解しやすいだろう。
マイクロソフトの記事
PowerPivotの計算列
https://support.office.com/ja-jp/article/PowerPivot-%E3%81%AE%E8%A8%88%E7%AE%97%E5%88%97-a0eb7167-33fc-4ade-a23f-fb9217c193af?ui=ja-JP&rs=ja-JP&ad=JP
PowerPivotの自習書
SQL Server 2012 自習書シリーズ
PowerPivot for Excel によるセルフ サービス分析
