Excel 2016 であれば Power Pivot for Excel がなくてもデータモデルにメジャーの追加や編集ができる。ピボット テーブルや ピボット グラフでより高度な集計分析作業が Excel 2016 だけでできるようになるわけです。Power BI Desktop や SSDTでメジャーを勉強したり試してみるのもよいのだけど、Excel + ピボット テーブルで確かめながらもいいのかなと思うんです。ワークシート で検算的なこともできますし。ただ、Excel データモデルの 管理 ができないだけ。
目的
- Power BI や SSAS(表形式モード)の メジャー(measure)の勉強を手軽に
- ピボットテーブルでもっと高度な集計を
※ Power BI / SSAS / Excel の 分析エンジンは基本的に同じですが、バージョンが異なることで使用できるDAX 関数やリレーション機能が異なる部分が存在する。ただ、基本的なことには差異はない。
動作に必要な環境
Excel 2016
※ エンタープライズ向けSKUにない Excel 2016 でも動作するようです。サポートが受けられるかどうかは不明、というか調査に至らず。
できないことがあるけれど、、
Power Pivot for Excel がない時、たとえば、
- Excel データモデルの編集
- 列のデータ型の設定
- 列の非表示
- 計算列の追加
- 階層の追加
- 日付テーブルの追加
- 列による並び替え
ができない。ただ、[データの取得と変換] 機能 (Power Query)で充分に代用可能かと。フィールドの階層を設定するのは無理っぽい。フィールドの"セット"で似たようなことはできるが、スライサーなどの設定に制限が出てしまう。
試してみる
サンプルデータ を使わせてもらって。
取得したデータは何にしても"データモデル"に追加しないといけない。
データを読み込む
Excel の テーブルとしてクエリエディターで読み込む。
[データ] タブ → [取得と変換] グループ → [テーブルまたは範囲から]
リボン上の配置は環境により異なる。
データを加工する
クエリ エディターで取り込んだデータを加工し、さらにデータモデルに [日付テーブル] を追加する準備をする。
クエリ Sales の 日付に関する列 "Date" から クエリ Calendar を作成。
クエリ Sales
データの型を変換しているだけ。時刻は不要なので日付(date)だけに、金額に関するものは通貨型(currency)に
let
// テーブル名で指定しているので異なる場合は修正が必要
Source = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
ChangedType = Table.TransformColumnTypes(
Source,
{
{"Date", type date}
, {"Year", Int64.Type}
, {"Customer ID", Int64.Type}
, {"Customer Age", Int64.Type}
, {"Customer Gender", type text}
, {"Country", type text}
, {"State", type text}
, {"Product Category", type text}
, {"Sub Category", type text}
, {"Product", type text}
, {"Order Quantity", Int64.Type}
, {"Unit Cost", Currency.Type}
, {"Unit Price", Currency.Type}
, {"Cost", Currency.Type}
, {"Revenue", Currency.Type}
}
)
in
ChangedType
クエリ Calendar
クエリ Sales の Date 列に含まれる日付から範囲を抽出し [日付カレンダー]を構成。Power Pivot for Excel がないので クエリ エディター(Power Query) で。
let
Source = Sales[[Date]],
Dates = Table.Profile(Source)[[Min], [Max]]{0},
ListDates = List.Dates(
Dates[Min]
, Duration.Days(Dates[Max] - Dates[Min]) + 1
, #duration(1, 0, 0, 0)
),
ConvertedToTable = Table.FromList(
ListDates
, Splitter.SplitByNothing()
, type table [Date = date]
)
in
ConvertedToTable
Table.Profile 関数に不具合があったようです。
/// Table.Profile に不具合出たとき用
let
Source = Sales[Date],
Dates = {List.Min(Source), List.Max(Source)},
ListDates = List.Dates(
Dates{0}
, Duration.Days(Dates{1} - Dates{0}) + 1
, #duration(1, 0, 0, 0)
),
ConvertedToTable = Table.FromList(
ListDates
, Splitter.SplitByNothing()
, type table [Date = date]
)
in
ConvertedToTable
データモデルに追加
2つのクエリができたらデータモデルに読み込む。環境の設定によるが、[閉じて次に読み込む] を実行すると間違いがない。
テーブルは不要なので [接続の作成のみ] を選択
オプション：[このデータをデータ モデルに追加する]を有効
ピボットテーブルを作成
読み込まれたデータモデルからピボットテーブルを作成
[このブックのデータ モデルを使用する] を選択
※ Office 365 ProPlus など エンタープライズ向け製品であれば クエリ エディターから読み込むと同時にピボットテーブル作成に遷移。
リレーションを構成
テーブル Sales と テーブル Calendar で時系列に関する集計を試すのでリレーションを構成
メジャーを追加する
[ピボットテーブルのフィールド] ウィンドウの テーブルを選択しコンテキストメニューから [メジャーの追加]
※ Calendar テーブルの 列 [Date] を 行 もしくは 列 に追加すると、年 / 四半期 / 月 の列が データモデルに追加 される。これはデータモデル上テーブルに計算列(Calculated Field)状態なのだけどデータモデルの管理ができない。元に戻したい場合は、ピボットテーブルの列を選択し、[グループの解除]を実行すればよいし、再度追加したいときは[グループ化]すればよい。
メジャーダイアログ
関数や列名、テーブル名など Intellisense が有効
メジャーは直ちに計算されるものではないので[DAX 式を確認]で問題がないか確認できる。
Ctrl キー + スクロール でフォントサイズの変更が可能。
メジャーをピボットテーブルに配置
作成したメジャーを値エリアに配置することでメジャーがどのような結果を返しているのか確かめることができる。
