Help us understand the problem. What is going on with this article?

Excel のピボット テーブル で メジャー(DAX) を使用するには

More than 1 year has passed since last update.

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)に

Sales
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) で。

Calendar
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 関数に不具合があったようです。

Calendar
/// 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 キー + スクロール でフォントサイズの変更が可能。
メジャーダイアログ

メジャーダイアログ

メジャーをピボットテーブルに配置

作成したメジャーを値エリアに配置することでメジャーがどのような結果を返しているのか確かめることができる。
ピボットテーブル

情報

その他

PowerBIxyz
#PowerBI とか #PowerQuery とか Microsoft MVP for Data Platform (2016,2017-2018,2018-2019,2019-2020,2020-2021), Access (2011-2015)
https://powerbi.connpass.com/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした