2014/11/16

PowerPivot で日付のグループ化

PowerPivot は強力な Excel のアドインであることは以前に紹介した。


ここで紹介したのが PowerPivot はデータモデルを採用しているため、これまでの Pivot テーブルの機能で使えていたものが使えなくなる、その代表が「グループ化」と「集計フィールド、集計アイテム」の追加だ。

コンテキストメニューのグループ化がグレイアウトされ選択できない状態

集計フィールド、集計アイテムがグレイアウトされ選択できない状態

特に日付のグループ化はデータを分析するためには必須である。この対応方法を今回は紹介する。なお、そもそも PowerPivot はマイクロソフト社の SQL Server のデータを Excel で分析するためのアドインとして開発された。そのため、Office や Excel から PowerPivot のテクニックを探すのは正直まだ情報が多いとは言えない。そのため、情報サイトなどで Excel を駆使して代替案を提示している場合が多いのだが、PowerPivot の機能として参照・検索するのであれば、SQL Server 側からの情報として探すと見つかる場合が多い。

日付のグループ化は「階層」を使う

(注) Excel 2016 の Power Query (取得と変換) は、自動グループ化機能が実装されました。

Excel はシリアル値というすばらしい仕組みを内蔵しているため、こと日付に関する処理・操作は非常に簡単に複雑なことができるようになっている。Excel の Pivot テーブルで日付をグループ化する機能などはシリアル値の恩恵を受けている。

一方、シリアル値を持たない通常のシステム、サーバーで日付を扱うには「年」や「月」といったデータを日付形式のデータから抜き出してレコードの別フィールドとして持たせる必要がある。Excel から見れば非常に面倒な処理をしているように思えるが、これは仕方ないとあきらめるしかない。

同様にデータモデルである PowerPivot では日付形式のデータから「年」や「月」を抜き出す必要が出てくる。

[PowerPivot] タブから [管理 データモデル] をクリックして、PowerPivot ウィンドウを開く。
そうするとそのブックに含まれているデータモデルを参照することができるので、該当するテーブルをデータシートビューで開く。


このデータモデルには「日付」があるので、このフィールドから「年」と「月」の列を新たに追加する。PowerPivot の場合はなるべく Excel ユーザーにも使いやすいように Excel に似た UI と関数が用意されている。(ただし、Excel の関数ではない。この PowerPivot で使う Excel のワークシート関数に似た関数を DAX 関数と呼ぶ。)

YEAR関数を使って年を抜き出す。データシートビューはワークシートではないので、関数の入力は直接セル(のようなもの)にできない。関数入力ボックスから行う必要があるので注意されたい。


MONTH関数を使って月を抜き出す。


列名は上書きで変更できるので、年、月とする。
次にこの「年」と「月」を使って、「年月」の階層を作成する。

データシートビューからダイアグラムビューに切り替える。
データモデルにある「年」で右クリックでコンテキストメニューを開き、「階層の作成」を選ぶ。


階層が作成されるので名前を「年月」にする。


「月」をドラッグして「年月」の「年(年)」の下にドロップする。


これで「年月」の階層が作られた。

これで PowerPivot ウィンドウから Pivot テープルを作ると、サンプルとして使った個人売上テーブルに「年月」フィールドが別の枠として追加されているのがわかる。


この「年月」の階層を使えば、日付のグループ化、グルーピングと同じことができるようになる。

以下がその操作だ。



まずは、PowerPivot の使い方などの情報は SQL Server の自習書としてマイクロソフトが公開しているので、本家の情報に目を通していただきたい。

SQL Server 2012 自習書シリーズ
PowerPivot for Excel によるセルフ サービス分析

MSDN PowerPivot for Excel チュートリアル
http://msdn.microsoft.com/ja-jp/library/gg413497(v=sql.110).aspx

0 コメント:

コメントを投稿

Powered by Blogger.

自己紹介

自分の写真
1989年新卒で日本IBMに入社しダウンサイジング担当としてホストコンピュータと繋げるオフコン、UNIX、PCサーバーのプロジェクトを担当。1997年 MSKK(現日本マイクロソフト)入社、NT4出荷に伴い企業向けサポート部門のビジネスマネージャーとして Excel 使いとなり、2002年 にMSMVPなどをサポートするユーザーコミュ二ティ部門を設立、部門をリード。2006年にMSKK退職後、企業向けのITトレーニング会社・団体に携わり、2014年頃よりPowerBI勉強会主催メンバーの一人として参画、そのコミュニティ活動で MSMVP for Data Platform PowerBI 2017受賞。https://mvp.microsoft.com/ja-jp/PublicProfile/5002635 同年にMVP Awardを返上し、アマゾン ウェブ サービス ジャパンに入社、コミュニティプログラム担当として現在に至る。