かずきのBlog@hatena

すきな言語は C# + XAML の組み合わせ。Azure Functions も好き。最近は Go 言語勉強中。日本マイクロソフトで働いていますが、ここに書いていることは個人的なメモなので会社の公式見解ではありません。

PowerPivot for Excelで列の中である値がしめる割合出したい

腹痛の頭痛に襲われる今日この頃。

表題の通りですが、PowerPivot for ExcelってPowerXXXX(QueryやらMapやら)シリーズの1つですが、なかなか使い勝手のいいやつだったりします。DBから値取り込んでいい感じに見せるというだけなら、こいつでいけることもあったりするんじゃないんだろうかと思うくらい。Office 2013からは、見た目に特化したようなPowerViewもあるし、気になるところです。

さて、PowerPivotで以下のようなデータをもったテーブルを取り込んだとします。(ユーザー列にはuser1とuser2しかない)

f:id:okazuki:20131016225212p:plain

この中でuser1とuser2が、それぞれ全体の何割を占めるのかというのを出したい!というのをやってみます。まず、普通にユーザーを行ラベルに置きます。そのあと、PowerPivotのリボンのところにある計算フィールドから計算フィールドの管理を選択します。

f:id:okazuki:20131017155356p:plain

ここに計算式を書いて全体に対するユーザーの割合を出していきます。PowerPivotの計算式は、Excelの関数チックなのでとっつきやういですが、数式が評価される箇所によってフィルタリングが自動でかかってたりといった癖があるので一度マニュアルには目を通しておいたほうがよさそうです。

新規作成ボタンを押して以下のような計算式を割合という名前で作ってみました。ALLを使ってテーブル全体の行数を数えた後、現在のコンテキストでのユーザー列の数を数えて割ることで割合を出しています。

=COUNTA([ユーザー])/COUNTROWS(ALL('テーブル1'))

書式を数字のパーセンテージにして完了です。

f:id:okazuki:20131017160255p:plain

この割合の列をピボットテーブルに追加すると、それぞれの割合が表示されます。

f:id:okazuki:20131017160710p:plain

このほかに、PowerPivotの管理画面でテーブルに以下のような秒単位の日時を時間単位にまるめるための列を追加します。

=DATE(YEAR([日時]), MONTH([日時]), DAY([日時])) + TIME(HOUR([日時]),0,0)

これを列をピボットテーブルの行に追加して、ユーザーを列に持っていきます。

f:id:okazuki:20131017162335p:plain

そうすると、時間単位のユーザーの割合がわかります。

f:id:okazuki:20131017162521p:plain

なかなか、いい感じにレポート作ったりできそうですね。