PowerQuery Duration関数（期間型）

期間型データのDuration関数のサンプル式と結果の一覧を用意しました。適宜ご利用ください。

基本形

働き	サンプル式	結果
期間型	#duration(1,2,3,4)	1.02:03:04

抽出形

働き	サンプル式	結果
日数をとりだす	Duration.Days(#duration(1, 2, 3, 4))	1
時間を取り出す	Duration.Hours(#duration(1, 2, 3, 4))	2
分を取り出す	Duration.Minutes(#duration(1, 2, 3, 4))	3
秒を取り出す	Duration.Seconds(#duration(1, 2, 3, 4))	4

数字で計算系

働き	サンプル式	結果
合計日数に変換	Duration.TotalDays(#duration(1, 2, 3, 4))	1.0854629629629629
合計時間に変換	Duration.TotalHours(#duration(1, 2, 3, 4))	26.051111111111108
合計分に変換	Duration.TotalMinutes(#duration(1, 2, 3, 4))	1563.0666666666666
合計秒に変換	Duration.TotalSeconds(#duration(1, 2, 3, 4))	93784

型変換系

働き	サンプル式	結果
期間型⇒テキスト型	Duration.ToText(#duration(1, 2, 3, 4))	1.02:03:04
数字（日数） ⇒ 期間型	Duration.From(1.085462963)	1.02:03:04.0000032
テキスト型 ⇒ 期間型	Duration.From("1.02:03:04")	1.02:03:04
テキスト型 ⇒ 期間型	Duration.FromText("1.02:03:04")	1.02:03:04
期間型 ⇒ レコード型	Duration.ToRecord(#duration(1, 2, 3, 4))	[Days] 1, [Hours] 2, [Minutes] 3, [Seconds] 4

関連記事

MS社のリファレンスです。

docs.microsoft.com

PowerQuery テキスト型データ （Text関数）

テキスト型データのText関数のサンプル式と結果の一覧を用意しました。適宜ご利用ください。

型変換

働き	サンプル式	結果
文字コード⇒文字	Character.FromNumber(65)	A
文字⇒文字コード	Character.ToNumber("A")	65
日付型⇒テキスト型	Text.From(#date(2019,11,24))	2019/11/24 0:00:00
テキスト型⇒リスト型	Text.ToList("あいうえお")	{"あ", "い", "う", "え", "お"}

部分抽出

※ PowerQueryでは座標は0開始です。つまり、0が１文字目、1が2文字目になります。

働き	サンプル式	結果
X文字目から 1文字	Text.At ("あいうえお", 1)	い
X文字目から Y文字	Text.Middle ("あいうえお", 1, 3)	いうえ
X文字目から Y文字	Text.Range ("あいうえお", 1, 3)	いうえ
先頭（左）から X文字	Text.Start ("あいうえお", 3)	あいう
末尾（右）から X文字	Text.End ("あいうえお", 3)	うえお
最初の区切り 記号のあと	Text.AfterDelimiter ("あ-いう-えお", "-")	いう-えお
2番目の区切り 記号のあと	Text.AfterDelimiter ("あ-いう-えお", "-", 1)	えお
最初の区切り 記号のまえ	Text.BeforeDelimiter ("あ-いう-えお", "-")	あ
2種の区切り 記号の間	Text.BetweenDelimiters ("あ【いう】え", "【", "】")	いう
2種の区切り 記号の間（2番目）	Text.BetweenDelimiters ("あ【いう】え【お】", "【", "】", 1, 0)	お

置き換え

働き	サンプル式	結果
X文字目にY文字列を挿入	Text.Insert ("あいお", 2, "うえ")	あいうえお
リストで指定した1文字を削除。 パラメータのリストは1文字のみ。 （複数文字はエラー）	Text.Remove ("あ い　う、え。お", {" ", "　", "、", "。"})	あいうえお
X文字目からY文字を削除	Text.RemoveRange ("あいアイウエオうえお", 2, 5)	あいうえお
Xの文字列をY文字列に置き換え	Text.Replace ("あ123い123う123え123お", "123", "_")	あ_い_う_え_お
X文字目からY文字を置き換え	Text.ReplaceRange ("あ１２お", 1, 2, "いうえ")	あいうえお
リストで指定した文字 のみを拾い出す	Text.Select ("あ い　う、え。お", {"あ".."お"})	あいうえお

情報・検索・判定

働き	サンプル式	結果
文字数	Text.Length ("あいうえお")	5
指定した文字列で始まる？	Text.StartsWith ("あいうえお", "あい")	true
指定した文字列で終わる？	Text.EndsWith ("あいうえお", "えお")	true
指定した文字列がある？	Text.Contains ("あいうえお", "いう")	true
指定した文字の位置	Text.PositionOf ("あいうえお", "いう")	1
指定した文字の位置 （無い場合）	Text.PositionOf ("あいうえお", "かき")	-1
最初の指定の文字の場所	Text.PositionOf ("あいうえおあいうえお", "いう", Occurrence.First)	1
最後の指定の文字の場所	Text.PositionOf ("あいうえおあいうえお", "いう", Occurrence.Last)	6
複数ある場合は リストで出てくる	Text.PositionOf ("あいうえおあいうえお", "いう", Occurrence.All)	List {1, 6}
リストの文字列の 最初の位置	Text.PositionOfAny ("あいうえおかきくけこさしすせそ", {"い","き","し"})	1

加工

働き	サンプル式	結果
テキストを結合	Text.Combine ({"あいう", "えお"})	あいうえお
テキストを結合（2つ以上）	Text.Combine ({"あ","い","う","え","お"})	あいうえお
区切り記号を挟んでテキストを結合	Text.Combine ({"あいう", "えお"}, ",")	あいう,えお
アルファベットを小文字	Text.Lower ("Excel")	excel
アルファベットを大文字	Text.Upper ("Excel")	EXCEL
英単語の頭を大文字、ほかは小文字	Text.Proper ("i LOVE eXcel")	I Love Excel
テキストが10文字になるように 先頭に半角スペース	Text.PadStart ("あいう", 10)	あいう
テキストが10文字になるように 先頭に記号	Text.PadStart ("あいう", 10, "^")	^^^^^^^あいう
テキストが10文字になるように 後ろに半角スペース	Text.PadEnd ("あいう", 10)	あいう
テキストが10文字になるように 後ろに記号	Text.PadEnd ("あいう", 10, "^")	あいう^^^^^^^
区切り記号でリストに分割	Text.Split ("あ-いう-えお", "-")	{"あ","いう","えお"}
区切り記号でリストに分割	Text.SplitAny ("あ-いう-え-お", "-")	{"あ","いう","えお"}
印刷できない文字を削除	Text.Clean ("あいう#(lf)えお")	あいうえお
先頭と最後のスペースを削除	Text.Trim (" あ い う え お　")	あ い う え お
先頭のスペースを削除	Text.TrimStart (" あ い う え お　")	あ い う え お
最後のスペースを削除	Text.TrimEnd (" あ い う え お　")	あ い う え お
指定の数だけ繰り返す	Text.Repeat ("あ", 5)	あああああ
逆にならべる	Text.Reverse ("あいうえお")	おえういあ

定数

働き	サンプル式	結果
最初	Occurrence.First	0
最後	Occurrence.Last	1
すべて	Occurrence.All	2
最初	RelativePosition.FromStart	0
最後	RelativePosition.FromEnd	1
Shift-JIS	932 （定数なし）	932
Unicode	TextEncoding.Unicode	1200
Utf16	TextEncoding.Utf16	1200
BigEndianUnicode	TextEncoding.BigEndianUnicode	1201
Windows	TextEncoding.Windows	1252
Ascii	TextEncoding.Ascii	20127
Utf8	TextEncoding.Utf8	65001

関連記事

MS社のリファレンスです。
docs.microsoft.com

参考図書① ビジネス極意シリーズ エクセルでできる！ ビッグデータの活用事例 「Power BI」で売上倍増！

ビジネス極意シリーズ　エクセルでできる！ ビッグデータの活用事例　「Power BI」で売上倍増！

https://www.amazon.co.jp/dp/B00NBPBLPY/ref=cm_sw_em_r_mt_dp_U_4IIHDbTCBM4Z1

ビジネス極意シリーズ　エクセルでできる！ ビッグデータの活用事例　「Power BI」で売上倍増！ (アスキー書籍)

私が初めてPower Pivotの存在を知って衝撃を受けた本です。

内容としてPower PivotのみならずPower Query、Power View、Power Mapについても紹介されており、それぞれの特徴を生かしたシナリオが揃っています。

発売は2014年です。当時としては極めて先進的な内容の本で、これらの技術を初めて日本に紹介した本だったのではないでしょうか。また、日本で発売されている本でPower Viewについて紹介しているのは今でもこの本だけだと思います。この本との出会いが無ければ私も執筆することはありませんでしたので、拙著もこの本の延長線上にあると言えます。

なお、Power BIと言うと最近はもっぱらクラウド版を指していますが、元々はExcelのアドオンを総称したものであり当時はPower BIと言えばExcelのことでした。

対応製品はExcel2013でもProfessional Plusや単製品などハイスペックなもので、企業向けの製品です。詳細は以下マイクロソフトのサイトをご確認ください。
support.office.com

ちなみに、Power Viewは2016でも一部のハイスペックな製品では使用可能ですが、レジストリを変更する必要があったりと使用までのハードルが上がっています。私もPower Viewは結構好きだったのですが、マイクロソフト社の意向としてグラフィカルなものはPower BIに集約させていくという動きなのでしょうか。

以下、hamachanさんのサイトで2016での詳細な設定方法が紹介されていますので、興味のある方はご覧ください。
hamachan.info

PowerQuery 日付型データ③ （Date関数）

今回は日付型データの関数のサンプル式と結果の一覧を用意しました。
また、それぞれの関数の特徴を元にカテゴライズしています。
適宜ご利用ください。

情報系

日付データから一部の情報を取り出します。

単位	基準日	サンプル式	結果
日	2019/1/10	Date.Day(#date(2019, 1, 10))	10
曜日（数字）	2019/1/10	Date.DayOfWeek(#date(2019, 1, 10))	4
曜日（文字）	2019/1/10	Date.DayOfWeekName(#date(2019, 1, 10))	木曜日
月を数字で	2019/1/10	Date.Month(#date(2019, 1, 10))	1
月を文字で	2019/1/10	Date.MonthName(#date(2019, 1, 10))	1月
年を数字で	2019/1/10	Date.Year(#date(2019, 1, 10))	2019

変換系

その他のデータ型からDate型に変換します。

変換元	サンプル式	結果
数字から	Date.From(43780)	2019/11/11
DateTime型から	Date.From(#datetime(2019, 11, 10, 06, 45, 12))	2019/11/11
Text型から	Date.FromText("2019-11-10")	2019/11/10
Text型から	Date.FromText("2019, 11, 10")	2019/11/10
Text型から（日省略）	Date.FromText("2019, 11")	2019/11/1
Text型から（月日省略）	Date.FromText("2019")	2019/1/1
Text型へ（指定なし）	Date.ToText(#date(2019, 9, 7))	2019/09/07
Text型へ（指定あり）	Date.ToText(#date(2019, 9, 7), "yyyy-M-d")	2019-9-7
Record型へ	Date.ToRecord(#date(2019, 11, 10))	Record

Text型への変換のフォーマット指定はISO8601規格
ISO 8601 - Wikipedia

計算系

基準日からXX日後、XX月後などに移動した日付を出力します。前はマイナス値をパラメーターに入れます。

単位	基準日	増分	サンプル式	結果
X日後	2019/1/10	1	Date.AddDays(#date(2019, 1, 10), 1)	2019/1/11
X週後	2019/1/10	1	Date.AddWeeks(#date(2019, 1, 10), 1)	2019/1/17
X月後	2019/1/10	1	Date.AddMonths(#date(2019, 1, 10), 1)	2019/2/10
X四半期後	2019/1/10	1	Date.AddQuarters(#date(2019, 1, 10), 1)	2019/4/10
X年後	2019/1/10	1	Date.AddYears(#date(2019, 1, 10), 1)	2020/1/10

数える系

基準日が何日目、何週目など。

単位	基準日	サンプル式	結果
年の何日目？	2019/2/10	Date.DayOfYear(#date(2019, 2, 10))	41
年で何週目？	2019/9/10	Date.WeekOfYear(#date(2019, 9, 10))	37
年の何四半期目？	2019/5/10	Date.QuarterOfYear(#date(2019, 5, 10))	2
月で何週目？	2019/1/30	Date.WeekOfMonth(#date(2019, 1, 30))	5
月は何日ある？	2019/1/10	Date.DaysInMonth(#date(2019, 1, 10))	31

最初の日

単位	基準日	サンプル式	結果
日の始まりの 日付時刻	2019/1/10 10:30:45	Date.StartOfDay (#datetime(2019, 1, 10, 10, 30, 45))	2019/1/10 0:00
週の最初の日	2019/8/21	Date.StartOfWeek(#date(2019, 8, 21))	2019/8/18
月の最初の日	2019/8/21	Date.StartOfMonth(#date(2019, 8, 21))	2019/8/1
四半期の最初の日	2019/8/21	Date.StartOfQuarter(#date(2019, 8, 21))	2019/7/1
年の最初の日	2019/8/21	Date.StartOfYear(#date(2019, 8, 21))	2019/1/1

最後の日

単位	基準日	サンプル式	結果
日の終わりの 日付時刻 （のはずが丸めて翌日に？）	2019/1/10 10:30:45	Date.EndOfDay (#datetime(2019, 1, 10, 10, 30, 45))	2019/1/11 0:00
週の最後の日	2019/8/21	Date.EndOfWeek(#date(2019, 8, 21))	2019/8/24
月の最後の日	2019/8/21	Date.EndOfMonth(#date(2019, 8, 21))	2019/8/31
四半期の最後の日	2019/8/21	Date.EndOfQuarter(#date(2019, 8, 21))	2019/9/30
年の最後の日	2019/8/21	Date.EndOfYear(#date(2019, 8, 21))	2019/12/31

判定系

基準日に対して、true、falseを返します。以下、本日の日付を2019/11/17とした場合の動作です。

今日かどうかの判定。

単位	サンプル式	結果
今日？	Date.IsInCurrentDay(#date(2019, 11, 17))	true
今週？	Date.IsInCurrentWeek(#date(2019, 11, 17))	true
今月？	Date.IsInCurrentMonth(#date(2019, 11, 17))	true
今四半期？	Date.IsInCurrentQuarter(#date(2019, 11, 17))	true
今年？	Date.IsInCurrentYear(#date(2019, 11, 17))	true

翌判定

単位	サンプル式	結果
翌日？	Date.IsInNextDay(#date(2019, 11, 18))	true
翌週？	Date.IsInNextWeek(#date(2019, 11, 24))	true
翌月？	Date.IsInNextMonth(#date(2019, 12, 17))	true
翌四半期？	Date.IsInNextQuarter(#date(2020, 1, 17))	true
翌年？	Date.IsInNextYear(#date(2020, 11, 17))	true

翌X内判定

当日、当週、当月、当四半期、当年はfalseになり、それ以降X内がtrueになります。

単位	サンプル式	結果
翌X日内？	Date.IsInNextNDays(#date(2019, 11, 18), 5)	true
翌X週内？	IsInNextNWeeks(#date(2019, 12, 1), 2))	true
翌X月内？	Date.IsInNextNMonths(#date(2020, 1, 17), 2)	true
翌X四半期内？	Date.IsInNextNQuarters(#date(2020, 4, 17), 2)	true
翌X年内？	Date.IsInNextNYears(#date(2021, 12, 31), 2)	true

前判定

単位	サンプル式	結果
前日？	Date.IsInPreviousDay(#date(2019, 11, 16))	true
前週？	Date.IsInPreviousWeek(#date(2019, 11, 10))	true
前月？	Date.IsInPreviousMonth(#date(2019, 10, 17))	true
前四半期？	Date.IsInPreviousQuarter(#date(2019, 9, 17))	true
前年？	Date.IsInPreviousYear(#date(2018, 11, 17))	true

前X内判定

当日、当週、当月、当四半期、当年はfalseになり、それ以前X内がtrueになります。

単位	サンプル式	結果
前X日内？	Date.IsInPreviousNDays(#date(2019, 11, 15), 5)	true
前X週内？	Date.IsInPreviousNWeeks(#date(2019, 11, 3), 2)	true
前X月内？	Date.IsInPreviousNMonths(#date(2019, 9, 17), 5)	true
前X四半期内？	Date.IsInPreviousNQuarters(#date(2019, 4, 17), 2)	true
前X年内？	Date.IsInPreviousNYears(#date(2017, 1, 1), 3)	true

そのほか

単位	サンプル式	結果
うるう年？	Date.IsLeapYear(#date(2020, 11, 17))	true

定数

単位	サンプル式	結果
日曜日	Day.Sunday	0
月曜日	Day.Monday	1
火曜日	Day.Tuesday	2
水曜日	Day.Wednesday	3
木曜日	Day.Thursday	4
金曜日	Day.Friday	5
土曜日	Day.Saturday	6

関連記事

Microsoft社のリファレンスです。
docs.microsoft.com

以下、ブログの関連記事です。

modernexcel7.hatenablog.com

modernexcel7.hatenablog.com

PowerQuery 日付型データ② （計算）

今回はパワークエリのデータ型のうち、日付型データの計算を扱います。
以下、前回の記事のサンプルデータを使用します。

modernexcel7.hatenablog.com

ポイントとしては、日付・時刻の加減算には期間(Duration)型を使用する点です。
また、2点の日付・時間と差も同様に期間(Duration)型として計算されます。

日付の加算

X日後の日付（データ型のエラー）

まずはカスタム列で以下の式を追加します。

サンプル式：　[日付] + 1

すると結果はErrorとなります。

Errorをクリックしてエラーの中身を確認します。

メッセージを見ると、どうやらDate型とNumber型では　+（足し算）を行えないようです。

X日後の日付（#durationの使用）

今度はPowerQuery 日付型データ① （基本） - Akira Takao’s blogに登場した期間型のデータを使用します。

期間型の書式：　#duration(日数, 時間, 分, 秒)

サンプル式：　　[日付] + #duration(1, 0, 0, 0)

すると無事、１日後の日付となりました。

ちなみに負の数：ｰ1を入力すると、1日前の日付となります。

サンプル式：　　[日付] + #duration(-1, 0, 0, 0)

X日後の日付（他列との連携）

今度は他列の値を日付に反映させます。まずMS社のガイドで#durationを検索して仕様を確認します。

#duration - PowerQuery M | Microsoft Docs

#duration(days as number, hours as number, minutes as number, seconds as number) as duration

ここを見ると、それぞれのパラメータは数字型が使えますので、Number列を当てはめてみます。

サンプル式：　　[日付] + #duration([Number], 0, 0, 0)

それぞれNumberの値の分だけ加算された日付が表示されました。

さらに次は日付/時刻型と期間型の列を加算します。

サンプル式：　　[#"日付/時刻"] + [期間]

※ 今回は期間の時刻部分の加算も確認するため、日付列ではなく日付/時刻列を使用しています。

日、時、分、秒が加算されました。

X日後の日付（関数を使用した例）

最後は関数を使用したパターンです。今回もまずMS社のガイドでDateを検索すると、Date.AddDaysという関数が見つかります。

#duration - PowerQuery M | Microsoft Docs

Date.AddDays(dateTime as any, numberOfDays as number) as any

この使用を参考にカスタム列の数式を以下のように変更します。

サンプル式：　Date.AddDays([日付],1)

すると無事１日あとの日付にすることができました。

NumberOfDaysの値をNumber列に差し替えても同様に上手くいきます。

サンプル式：　Date.AddDays([日付],[Number])

ただし、関数の仕様を確認すると、戻り値の方がAny（すべて）型になっているので、このまま閉じて読み込むを実行すると日付の書式になりません。

Date.AddDays(dateTime as any, numberOfDays as number) as any

データ型を日付型に変換して、再度閉じて読み込むを実行してください。

日付の差分

最後に日付の差分を求めます。新しいカスタム列を作成して、以下の数式を入力してください。

サンプル式：　[日付の加算] - [日付]

すると、結果が期間型の形式で表示されます。

期間型のデータでは扱いにくいので、カスタム列の式を以下のように変えて日数に変換します。

サンプル式：　Duration.Days([日付の加算] - [日付])

これで日数のデータになりました。

PowerQuery 日付型データ① （基本）

今回はパワークエリの日付型データがテーマです。
それぞれ計算式のサンプルを載せますので実際に手を動かして試してみてください。

デモ用テーブルの用意

まず、デモ用のテーブルを用意します。今回はデータソースは使用せず、以下の手順で空のクエリから作成します。

データメニュー　→　データの取得　→　その他のデータソースから　→　空のクエリ

PowerQueryエディターが開きます。

数式バーが表示されていない場合は以下の手順で表示させます。

表示メニュー　→　数式バーにチェックを入れる

数式バーに以下の式を入力して、デモ用テーブルを作成します。

= #table({"Number"}, {{1},{2},{3}})

これでテーブルが作成されました。

日付・時刻型

ここから先はカスタム列で計算式を追加し、確認していきます。

列の追加 → カスタム列 を選択します。

以下、下図のようにカスタム列にサンプル式を入力し、それぞれ確認してください。

日付（date）

書式：　　　　#date(年, 月, 日)

サンプル式：　#date(2019,7,12)

時刻（time）

書式：　　　　#time(時,分, 秒)

サンプル式：　#time(18,30, 45)

日付/時刻（datetime）

書式：　　　　#datetime(年,月,日,時,分, 秒)

サンプル式：　#datetime(2019,7,12, 18,30, 45)

日付/時刻/タイムゾーン（datetimezone）

書式：　　　　#datetimezone(年, 月, 日, 時, 分, 秒, +時間, +分)

サンプル式：　#datetimezone(2019,7,12, 18,30, 45, 3, 30)

期間（duration）

書式：　　　　#duration(日数, 時間, 分, 秒)

サンプル式：　#duration(10, 1, 2, 3)

※ 期間型のデータは上記の日付や時刻に比べると性格が異なります。日付や時間型データに加減算をするときに使用します。

Excelワークブックへの取り込み

ここまで来たら閉じて読み込むを実行して、Excelワークシートテーブルに読み込みます。

そうすると、Power Query Editorでは日付として表示されていたデータが全て数字になってしまいます。

これは、Power Queryのデータ型が「すべて（Any)」型であったことが原因ですので、もう一度クエリを開いてそれぞれの列の型変換を行います。

型の設定が済んだら、もう一度閉じて読み込むを実行すると、日付の表示に変わります。

参考

以下、MS社の公式リファレンスです。画面左上のFilter by Textに#dateと入力し、検索してみてください。
Power Query M function reference - PowerQuery M | Microsoft Docs

今回は、日付・時刻型のデータ作成がテーマでしたが、次回は日付・時刻型データの計算（XX日後、月・四半期のの最初の日付の取得など）がテーマです。

バンプチャート（順位推移グラフ）ー RANKX

バンプチャート（順位推移グラフ）ー RANKX

目標

今回のテーマはバンプチャート（順位推移グラフ）です。バンプチャート（順位推移グラフ）は、時系列を横軸においた、順位（ランキング）の変遷を表現した折れ線グラフです。

技術的なポイント

DAXで順位を求めるにはRANKX関数を使用します。

RANKX function (DAX) - DAX | Microsoft Docs

RANKX – DAX Guide

第一引数：テーブル　　ここに順位を求める対象のテーブルを指定します。（今回は商品テーブル）
第二引数：計算式　　　ここに順位を比較するための計算式（今回は「売上合計」）を指定します。

第一引数のテーブル指定時にALLまたはALLSELECTEDを付けるのがポイントです。

実践

ならべるステップ

まず、挿入→ピボットテーブルから、このブックのデータ モデルを使用するを選んでピボットテーブルを作成します。

ピボットテーブルのフィールドは以下のように、行に商品名、値に売上合計を配置します。

この段階では商品名と売上合計しか並んでいません。

かぞえるステップ

これからRANKX関数でメジャーを作成しますが、敢えて誤った例を紹介しながらポイントを押さえていきます。

商品売上順位メジャーの作成

さっそくメジャーを作成します。

商品テーブル→右クリック→メジャーの追加を選びます。

商品売上順位メジャーを以下のように作成します。

= RANKX('商品', [売上合計])

ところが、作成したメジャーをピボットテーブルに追加して順位を確認すると、すべて１です。

これはRANKXの第一引数、'商品' がピボットテーブルのセルのフィルターコンテキストの影響を受けているため、順位を比べる対象が１対１になっているからです。

したがって、ALL関数を使って第一引数 '商品'テーブルのフィルターを解除します。以下のように商品売上順位メジャーを修正してください。

= RANKX(ALL('商品'), [売上合計])

これで商品名全体の中での順位が出ました。（青枠は比較対象の全商品名、赤枠は順位を求める商品です。）

次にスライサーを追加します。

ピボットテーブルのフィールドから商品→商品カテゴリー→右クリック→スライサーとして追加を実行します。

さっそくスライサーを選択してみると、商品名は絞られたものの、順位が商品名全体での順位でスライサーを受けた順位になっていません。

スライサーの選択を反映するには、ALLSELECTED関数を使います。以下のように商品売上順位メジャーを修正してください。

= RANKX(ALLSELECTED('商品'), [売上合計])

これでスライサーの内容を反映した順位を表示することができました。

次に列に時間軸としてカレンダーテーブルの会計年度を追加します。

これで会計年度ごとの順位の推移が出ました。ここまで来たら売上合計メジャーはピボットテーブルから外します。なお、2019年度には売上実績データが無いためすべて１になっています。

えがくステップ

バンプチャート（順位推移グラフ）の作成

まずは空のピボットグラフを作ります。

何もないセルにカーソルを移動し、挿入→ピボットグラフ→ピボットグラフを選んでください。例によって、このブックのデータ モデルを使用するが選択されていることに注意してください。

ピボットグラフには以下のように設定します。
　軸（分類項目）：　会計年度
　凡例（系列）：　　商品名
　値：　　　　　　　商品売上順位

次にピボットグラフをすでに作成した商品カテゴリースライサーに結び付けます。
商品カテゴリースライサー→右クリック→レポートの接続を選択します。

先ほど作成したピボットグラフにチェックを入れ、OKを押します。

これでピボットグラフがスライサーで絞り込まれました。

次に、ピボットグラフをクリックし、デザイン→グラフの種類の変更→折れ線→折れ線を選択します。

これで折れ線グラフになりました。

次に、グラフの順位を反転させます。
折れ線グラフの縦軸の順位をダブルクリックし、表示された軸の書式設定で軸を反転するにチェックを入れます。

グラフを選択したまま、デザインメニューで以下のデザインを選択します。

これでだいぶ形になってきました。

しかし、2019年には売上実績が無いのにもかかわらずグラフが表示されているので、これを消します。以下のように商品売上順位メジャーを修正してください。
売上合計に値が入っている時のみ順位を表示し、値が入っていない場合はブランクにする条件を追加しました。

=
IF(
[売上合計],
RANKX(ALLSELECTED('商品'), [売上合計])
)

これで完成しました。

同様に支店名など、他のスライサーも追加すると便利でしょう。

以下は支店名で順位を作成した時のバンプチャートとメジャーの例です。

=
IF(
[売上合計],
RANKX(ALLSELECTED('支店'), [売上合計])
)

ピボット解除応用：多階層クロス集計表のデータ化

ピボット解除応用：多階層クロス集計表のデータ化

パワークエリのテクニックで人気の高いピボット解除ですが、今回は上に多階層乗ったクロス集計表の解体＝データ化がテーマです。

ヘッダー部分が１階層（一行）であった場合はすんなりとピボット解除できるのですが、複数階層あった場合はひと工夫が必要です。

Before

※　上から年月、予実区分、PL科目分類が３階層ならんでいます。

After

いつものようにテーブルの形に変換します。

技術的なポイント

端的に言うと、上部の３階層をいったん１階層にしてからピボット解除します。

列のピボット解除（複数階層）

参考資料

こちらのテクニックは『M Is for Data Monkey: The Excel Pro's Definitive Guide to Power Query』で紹介されていたものです。
この本はパワークエリの入門書として最適な本で、私がPower Queryの素晴らしさを知った本です。Power Queryの基本を学びたい方はぜひご購入ください。

M Is for Data Monkey: The Excel Pro's Definitive Guide to Power Query

実践

多階層クロス集計表をPower Query Editor に読み込む

まず、多階層クロス集計表の上にカーソルを置きます。

そのままデータ→データの取得と変換→テーブルまたは範囲からをクリックします。　

テーブルの作成ダイアログボックスでは、先頭行をテーブルの見出しとして使用するにチェックを入れないでOKを押します。

Power Query Editor が開きます。
先頭行をテーブルの見出しとして使用するにチェックを入れなかったので列の名前はそれぞれ列１、２…です。

一時的に縦と横を入れ替える（転置）

次に変換メニューの入れ替えをクリックします。

そうすると、テーブルの中身が転置され、縦と横が入れ替わります。

列のマージで３階層を１列に結合する

まずは空白データ（null）を埋めるために、Ctlrキーを押しながら、列１、列２をクリックします。

次に変換メニューのフィルをクリックし、下を選択します。

これで、空白データ（null）が埋まりました。

今度は列をマージするために、Ctlrキーを押しながら、Column1、Column2、Column3 をクリックします。

次に変換メニューの中の列のマージをクリックします。

区切り記号の選択には、これら3つのデータに絶対に存在しない値を選んでください。
今回はカスタム→　_　（アンダースコア）を選びましたが、データに存在しない値なら何でも構いません。

これで３つの列が１つの列にマージされました。だいぶ近づいてきた感じがします。

ふたたび縦と横を入れ替えて元に戻す

次に縦横を元に戻すため、変換メニューの入れ替えをクリックします。

これで縦横が元に戻りましたが、今回は上にあった三階層が結合されて一階層になっていることに注意してください。
そして、その一階層の中身は「_（アンダースコア）」でマージされています。

ピボット解除

ピボット解除をするためには、縦に属性として並べるための列名＝ヘッダー行を用意する必要があります。
変換メニューの一行目をヘッダーとして使用するをクリックします。

これで先ほど１階層に結合した情報が列名になり、ピボット解除をする準備が整いました。

Ctrlキーを押しながら、会社名__、氏名__をクリックして選択します。

変換メニューの列のピボット解除→その他の列のピボット解除を選んでください。
※　毎回データを読み込む時に横に並ぶ列の件数に増減がある場合は、必ず残る列（今回は、会社名__、氏名__）を選択して、その他の列のピボット解除をすると増減があっても対応可能です。

これで先ほど選択されなかった列名が属性として縦に並びました。
（私はいつもそれまでの長い準備段階を経てピボット解除を行うとき、ヒーローが必殺技でトドメを刺すような気持になります。）

１つに結合した３階層を分割して元に戻す

これで最後の仕上げです。属性列を選択します。

変換メニューで列の分割→区切り記号による分割を選択します。

区切り記号には列のマージで入力した「_（アンダースコア）」を入力します。
また、分割が区切り記号の出現ごとになっていることに注意して、OKを押します。

これで３階層が分割され、データ化されました！

最後に列名を整えて完成です。

閉じて読み込むを実行し、テーブルに読み込んで出来上がりです。

関連記事

modernexcel7.hatenablog.com

modernexcel7.hatenablog.com

選択肢テーブルによる集計の切り替え

選択肢テーブルによる集計の切り替え

目標

今回のテーマは 選択肢テーブルによる集計の切り替えです。

今回はリレーションシップを持たない選択肢テーブルからスライサーを作り、その選択肢をメジャーに組み込んでピボットテーブルをより便利なものにします。

以下の図でいうと、オレンジのスライサーの集計タイプの選択肢で、ピボットテーブルの集計を売上・原価・利益のいずれかに切り替えます。

技術的なポイント

　●　リレーションシップを持たない独立した選択肢テーブルを作る
　●　選択肢テーブルの値を取ってくる（VALUES)
　●　１つだけの値が選ばれているかチェックする（COUNTROWS、HASONEVALUE）
　●　条件に応じて表示を切り替える（SWITCH)

実践

とりこむステップ

選択肢テーブルの作成

集計タイプという項目を１つだけ用意し、売上、原価、利益の３行をテキストで並べ、テーブルとして書式設定でテーブルを作ります。

テーブル名は先頭にパラメータ（Parameter）のPを付けて、P_集計タイプにします。

データモデルへの読み込み

ワークシートテーブルはできましたが、このままではDAXで参照できないのでデータモデルに読み込みます。

P_集計タイプテーブルにカーソルを置いてデータ → テーブルまたは範囲からをクリックし、Power Query エディターを開きます。

Power Query エディターが開いたら、何もせずにそのまま閉じて読み込む→閉じて次に読み込むを選びます。

データのインポート画面が現れますので、接続の作成のみを選び、このデータをデータモデルに追加するにチェックを入れてOKを押します。

これで、P_集計タイプがデータモデルに追加されました。

Power Pivotアドインがある環境だとダイアグラムビューで確認できますが、以下のようにリレーションシップを持たない孤立したテーブルになっています。

スライサーとしてDAXのフィルターコンテキストに値を渡すためだけのテーブルなので、これで大丈夫です。

【別解】選択肢テーブルの作成：空のクエリで作成

なお、P_集計タイプテーブル作成の別解として空のクエリから直接作成することもできます。

まず、データ→データの取得→その他のデータソースから→空のクエリを選びます。

Power Query エディターが開いたら、数式バーに直接以下の式を記入し、Enterを押します。

= #table({"集計タイプ"}, {{"売上"}, {"原価"}, {"利益"}})

すると、以下のようなテーブルが作成されます。

これはテーブルを直接作成する文で、#table内の最初のパラメータ、{"集計タイプ"}は項目名（列名）、次のパラメータの{{"売上"}, {"原価"}, {"利益"}}はその項目内の各レコードとなっています。

あとは、クエリ名を変更して前述の手順と同じ形でデータモデルに読み込みます。

閉じて読み込む→閉じて次に読み込むを選択します。

データのインポート画面が現れますので、接続の作成のみを選び、データモデルに追加するにチェックを入れてOKを押します。

なお、空のクエリで直接作成した場合、ピボットテーブルのフィールドリストを見たときに重複表示されないというメリットがあります。

以下のように、ワークシートテーブルから作成したものはワークシートテーブルとデータモデルの二つが、空のクエリで作成したものはデータモデルのもののみが表示されています。

これでテーブルの作成は完了しました。次にスライサーを追加します。

ならべるステップ

次に作成したP_集計タイプテーブルからスライサーを作ります。拙著『Excelパワーピボット7つのステップでデータ集計・分析を「自動化」する本』の商品別売上推移シートを開きます。

ピボットテーブルにカーソルを移動し、ピボットテーブル分析→スライサーの挿入→すべて→P_集計タイプ→集計タイプの手順で、スライサーに追加します。
この時、データモデルのアイコンのついているテーブルを選ぶことに注意してください。

これでスライサーが追加されました。

ただし、このテーブルはその他のテーブルとはリレーションシップを一切持っていないので、ボタンを選んでもピボットテーブルは一切変化しません。この段階では、ピボットテーブルに対してフィルターコンテキストを変更する術を持っていないからです。

かぞえるステップ

ここからはメジャーを作ってスライサーの値をピボットテーブルに反映させていきます。

スライサーの値を取得する（VALUES)

まず先ほど作成したスライサーで一つの値だけを選択してください。今回は売上を選択します。

次に以下の手順でスライサーで選択された値を表示するためのメジャーを作成します。

フィールドリスト→F_売上明細→右クリック→メジャーの追加

最初に「’」をクリックしてP集計タイプテーブルの集計タイプ列を探しますが、列は選択肢に現れません。

そのままでは列は表示されないので、VALUESという関数を使って表示します。

集計:
= VALUES('P_集計タイプ'[集計タイプ])

メジャーを作成したら、ピボットテーブルの値フィールドに追加します。
すると、スライサーで選択した売上が文字として表示されます。

さらに、原価、利益をスライサーで選択しても、ピボットテーブルが変化することを確認してください。

一つだけの値が選択されているかチェック（COUNTOROWS）

では、スライサーで複数の値を選ぶとどうなるでしょうか？
Ctrlキーを押しながら売上と原価の二つを選んでみてください。次のエラーが表示されます。

要するに、ピボットテーブルのセルには一つの値しか表示できないところに、無理やり二つ以上のデータを置こうとしたことによるエラーです。

このVALUESという関数は少し特殊な関数で、主な機能は指定したテーブルまたは列を、重複を排除したユニークなテーブルに変換するですが、もう一つ、結果が１行・１列だった場合は、テーブルではなく一つの値に変換するという機能を持っています。

つまり先ほど上手くいっていたのは、たまたま結果が一行・一列だったため、その値をピボットテーブルが受け取って表示していたにすぎないのです。

なので、値が１行・１列であるときと、そうでないときとのエラー処理を追加する必要があります。'P_集計タイプ'[集計タイプ]で列を指定しているので、１行か１行でないかを判断すればよいです。
先ほど作成したメジャーを以下のように変更します。

まず、 メジャーを書きかえて、VALUES ( 'P_集計タイプ'[集計タイプ] )のテーブルの行数を出します。

集計:
= COUNTROWS ( VALUES ( 'P_集計タイプ'[集計タイプ] ) )

COUNTROWSは、フィルターコンテキストの中でテーブルの行数をかぞえる関数です。
これで、行数を数えることができました。スライサーで売上、原価、利益を選択して複数行のカウントができることを確認してください。

次にテーブルの行数が1の時は、選択された値を、それ以外の場合はブランクを表示するように変更します。

集計:
=IF (
COUNTROWS ( VALUES ( 'P_集計タイプ'[集計タイプ] ) ) = 1,
VALUES ( 'P_集計タイプ'[集計タイプ] )
)

こうすると、一つだけ項目が選択されている時はその項目を表示し、複数選んだ場合はブランクになるようになりました。
ちなみにDAXのIF文でFALSE（偽）のケースを省略すると、結果はブランクになります。

選択に応じて集計を切り替える(SWITCH)

最後の仕上げに取り掛かります。集計タイプの値に応じて表示する集計を変えます。

集計:
=
IF (
COUNTROWS ( VALUES ( 'P_集計タイプ'[集計タイプ] ) ) = 1,
SWITCH (
VALUES ( 'P_集計タイプ'[集計タイプ] ),
"売上", [売上合計],
"原価", [売上合計] - [利益合計],
"利益", [利益合計]
)
)

あわせて書式に桁区切りも追加します。

これで、スライサーのボタンで集計タイプを切り替えることができるようになりました。

別解：1つだけ選択の判定（HASONEVALUE）

ちなみにHASONEVALUE関数を使って以下の数式にすることも可能です。読んで字のごとく値が一つだけの時にTRUEとなる関数です。
こちらの関数は、COUNTROWSと異なり、テーブルではなく、列（ 'P_集計タイプ'[集計タイプ] ）が直接インプットとなります。

=
IF (
HASONEVALUE ( 'P_集計タイプ'[集計タイプ] ),
SWITCH (
VALUES ( 'P_集計タイプ'[集計タイプ] ),
"売上", [売上合計],
"原価", [売上合計] - [利益合計],
"利益", [利益合計]
)
)

なお、Powre BIではSELECTEDVALUEという関数がダイレクトで使えるそうですが、EXCELではまだ使えないようですね。

そのほかの応用例

今回は、売上、原価、利益という費目でパターン分けしましたが、例えば、単期／累計というスイッチにすることも可能です。

また、この選択肢テーブルは数値も値にセットできますので、1,　1000,　1000000という数字を使って数字の桁調整を行ったり、移動平均のレンジを変更したり、度数分布表の階級幅を変更したりと色々と応用ができます。

参考図書

こちらのテクニックは、以下『DAX Patterns』という本で紹介されている「Parameter Table」を応用したものです。こちらの本はDAXを使った典型例を多く紹介しておりますので、興味のある方は是非ご覧ください。

DAX Patterns 2015 by Marco Russo Alberto Ferrari(2014-12-19)

こちらのWebサイトもご覧ください。
www.daxpatterns.com

絵をいただきました！

松田軽太さんにパワーピボットのイメージ画像を頂きました！

ブログ・ツイッターで交流させていただいている松田軽太さんからパワーピボットのイメージ画像をいただきました！

いつも無機質な私のサイトですが、急に華が出てきました（私）。今はまだ認知度の低いパワーピボットですが、こんな素敵な女性に声を掛けられた日には使わざるを得ません！急速に認知度が広がってゆくことでしょう！

※中の人（＝私）がこんなきれいな女性だと誤解せぬようご注意ください。

情報システム部出身の人間として松田さんのブログは共感するところが多く、いつも楽しみにしています。会社レベルはもとより個人の働き方まで日々の業務へのヒントが得られますので、是非ご覧ください。

www.matudakta.com

スライサーが使えるウォーターフォール・チャートを作る

目標

ピボットテーブル、ピボットグラフはユーザーの操作で自由にレイアウト、集計・分析の組み合わせを変えられる極めて便利なレポートですが、欠点もあります。

　●　ユーザーの操作で変化してしまうので、レポートを固定できない
　●　ピボットグラフはすべてのレポートは使えない

一つ目の点は、社内の多くの人にレポートを送る場合などに、レイアウトが変更されて誤った数字が独り歩きする恐れがあります。

二つの目の点については、残念ながらピボットグラフではウォーターフォールチャート、ヒストグラム、散布図、サンバーストなどのレポートは使用できません。

今回はいったん作成したピボットテーブルをCUBE関数に変換して通常の集計表にすることでこれらの問題を解決します。ただし、ただ変換するのではなく、ピボットテーブルの最大の良さであるインタラクティブ性を残したまま変換します。

技術的なポイント

　●　データモデルからピボットテーブルを作る
　●　スライサーを追加する
　●　CUBE関数に変換する
　●　関数化された表からグラフを作る

実践

サンプルデータとして拙著Excelパワーピボット 7つのステップでデータ集計・分析を「自動化」する本のデータモデルを使用します。

まずは挿入→ピボットテーブルからピボットテーブルを作ります。この時、このブックのデータモデルを使用するを選びます。

ピボットテーブルのレイアウトは以下のように設定します。今回はウォーターフォールチャートを作るので、列に会計年度を、値に売上前年差異を置きます。

合わせてスライサーも追加しておきます。今回は支店名を追加しました。

これでベースとなるピボットテーブルができました。ここまではいつも通りの手順です。

CUBE関数に変換する

次にこのピボットテーブルを通常のExcel集計表に変換します。ピボットテーブルにカーソルを置いたまま以下の手順を実行します。

ピボットテーブル分析（分析）　→　OLAPツール　→　数式に変換

なお、ピボットテーブルを作るときににデータモデルを選択しなかった場合、グレーアウトされてクリックできません。

これで通常のExcel集計表ができました。

ここでスライサーのボタンをどれか選んでみて下さい。なんと集計表にスライサーのフィルターがかかります！

これでピボットテーブルの良さであるインタラクティブ性を残したまま、Excel集計表ができました。

値の入っているセルをクリックすると、CUBEVALUE関数がスライサーを参照していることが分かります。

なお、CUBEVALUEの引数は以下4つを参照しています。
　①　このブックのデータモデル　"ThisWorkbookDataModel"
　②　メジャー　　　　　　　　　$B4
　③　フィルター　　　　　　　　C$3
　④　スライサー　　　　　　　　スライサー_支店名51

さらに、$B4、C$3はそれぞれ、以下のCUBEMEMBER関数が設定されています。
　$B4　=CUBEMEMBER("ThisWorkbookDataModel","[Measures].[売上前年差異]")
　C$3　=CUBEMEMBER("ThisWorkbookDataModel","[カレンダー].[会計年度].&[2016]")

なお、C$3に直接｢2016｣と指定しても計算結果はエラーになります。CUBEMEMBERでフィルターを指定する必要があります。

ウォーターフォールチャートを作る

ここまで来たので、最後に仕上げを行います。
通常のExcel集計表になっているので、総計を削除し、2019年の列名をSUMに、値を以下の数式にします。
=SUM(C4:E4)

次に数字をカンマ区切りにし、以下のように範囲選択します。

挿入→グラフ→ウォーターフォールを選択します。

ウォーターフォールチャートが作られました。

最後の列であるSUMの棒グラフをダブルクリックします。

合計として設定にチェックを付けて出来上がりです。

これでスライサーと連動したウォーターフォールチャートができました。

通常のExcel集計表とピボットテーブル・ピボットグラフの両方の良いところ取りをするテクニックです。

応用

ピボットグラフでは使えないグラフも自由に使えるので、色々と応用できそうです。Excelとしては画期的なことではないでしょうか？
特に統計分析では一回作って終わりのワンパターン・レポートが多いと思いますので、インタラクティブにすることで応用の幅が広そうです。

インタラクティブ散布図

インタラクティブ・ツリーマップ

参考文献

なお、今回紹介しましたピボットテーブルををCUBE関数に変換するテクニックは以下の本で知りました。
それにグラフを追加して面白いことができないかというのが今回の記事です。

Matt Allington さんの本には最初にPower Pivotの勉強を始めたときに大変お世話になりました。

Supercharge Excel: When You Learn to Write DAX for Power Pivot

移動平均（３か月間） - DATESINPERIOD

移動平均（３か月間） - DATESINPERIOD

目標

今回のテーマは移動平均（単純移動平均）の計算です。

移動平均とは「今ここ」の時点を基準とした一定期間の数値の平均のことです。平均をとる開始日が基準日の移動に伴って変化するのが特徴です。

移動平均を使うことで、短い期間の細かなバラツキを丸めて、大局的な数字の傾向を把握することができます。

移動平均には、将来の時間を含めた平均もありますが、今回は過去３か月間の売上移動平均を計算します。（この場合、基準日＝最終日となります。）

技術的なポイント

• 「今ここ」から一定時間さかのぼった日付リストを手にいれる
• そのためにタイムインテリジェンス関数 DATESINPERIODを使う

なお、「分子を計算する」に当たっては、拙著『Excelパワーピボット 7つのステップでデータ集計・分析を「自動化」する本』の「第５章 当期売上累計」の当期売上累計メジャーとよく似た計算をします。

ただし、当期売上累計では開始日は常に固定の期初（4月1日）であるのに対し、移動平均では、テーブル上の基準点から常に３か月間さかのぼった日付が開始日となります。つまり、開始日が「今ここ」を起点として相対的に決まるという点が異なります。

実践

ならべるステップ

まず、『Excelパワーピボット 7つのステップでデータ集計・分析を「自動化」する本』の「第５章 当期売上累計」のピボットテーブルを以下のようにレイアウト変更してください。

かぞえるステップ

「今ここ」から3カ月間さかのぼった日付リストを手にいれる

一定期間の日付リストを用意するには、DATESINPERIODという関数を使います。

書式
　DATESINPERIOD ( <カレンダーテーブルの日付項目>, <基準日>, <間隔数>, <間隔タイプ> )

アウトプット
　上の指定を受けた日付リスト

この中で注意しなくてはならないのは、<基準日>です。これは基準日なのでデータが複数行あってはいけません。

例えば、ピボットテーブルが８月という月レベルまでドリルアップされ、日にちが非表示である場合、その文脈における「今ここ」の日にちは「8月1日から8月31日」までの日付群となってしまいます。

日付が複数あっては基準点となりえないので、日付を何らかの基準で一つの値＝代表値にする必要があります。複数のデータを受け取って一つの代表値にするにはMIN、MAXといった集計関数を使います。今回は、MAXを使ってその文脈における月末日にします。

それでは、まず以下のメジャーを作って日付リストの中身を確認してみましょう。

なお、移動平均開始日と移動平均終了日については、カテゴリを日付にしてください。

移動平均日数:
=CALCULATE (
COUNTROWS('カレンダー'),
DATESINPERIOD ( 'カレンダー'[日付], MAX( 'カレンダー'[日付] ), -3, MONTH )
)

※　COUNTROWSはその文脈におけるデータ件数を取得する関数です。今回は「今ここ」を起点としてさかのぼった3か月間の日数となります。

移動平均開始日:
=CALCULATE (
MIN('カレンダー'[日付]),
DATESINPERIOD ( 'カレンダー'[日付], MAX ( 'カレンダー'[日付] ), -3, MONTH )
)

※　「今ここ」を起点としてさかのぼった3か月前の日です。

移動平均終了日（基準点）:
=CALCULATE (
MAX('カレンダー'[日付]),
DATESINPERIOD ( 'カレンダー'[日付], MAX ( 'カレンダー'[日付] ), -3, MONTH )
)

※　基準点＝最終日となります。

会計年度、四半期、月、日のそれぞれのレベルで、移動平均日数、移動平均開始日、移動平均終了日（基準点）が正しく表示されていることを確認してください。

なお、ここまで「日付リスト」という言葉を使ってきましたが、ピボットテーブル上ではこのフィルターコンテキスト内の日付を直接見ることはできません。フィルターコンテキストは想像しなければいけない部分があり、それがDAXの理解のハードルを上げています。

したがって、上のように中間メジャーを使ってフィルターコンテキストの中身を可視化しながら実感をもって理解していただくように勧めています。

３か月間の売上を合計する

DATESINPERIODの動作は確認できたので、メジャーでこの期間の売上合計を出します。

移動平均売上合計:
=CALCULATE (
[売上合計],
DATESINPERIOD ( 'カレンダー'[日付], MAX ( 'カレンダー'[日付] ), -3, MONTH )
)

ピボットテーブルで直近３か月の売上合計と移動平均売上合計の値が一致していることを確認してください。

移動平均を求める

最後に、上で作成した移動平均売上合計を３で割って売上移動平均を算出します。

売上移動平均:
=DIVIDE( [移動平均売上合計], 3)

合わせて分母を移動平均日数にした売上移動平均（日）も作ります。

売上移動平均（日）:
=DIVIDE([移動平均売上合計], [移動平均日数])

えがくステップ

最後に、グラフにプロットして売上と売上移動平均の違いを見てみます。

例によって新規にピボットグラフを作成し、以下の設定にしてください。

またグラフの種類は「折れ線グラフ」にします。

スライサーで「商品カテゴリー」を追加して出来上がりです。

上下する売上合計と緩やかな売上移動平均を比較してください。

最終月補正の追加（2020年4月25日追記）

上記までのステップで移動平均が算出されましたが、一点、問題があります。

移動平均の計算が過去3か月であるため、以下の図のように実績が2018年度で終了しているにもかかわらず、移動平均が2019年第一四半期まで伸びている点です。

今回はこの延長を補正するために、実績の最終日までしか移動平均を表示しないように修正します。

まずは実績の最終日を取得するメジャーを作ります。

F_売上明細最終日:
=CALCULATE(MAX('F_売上明細'[日付]), ALL('カレンダー'))

これで売上の入力された最終日を取得しました。ピボットテーブルで確認してみると3月29日で一致していることが分かります。

次にフィルターコンテキスト上のカレンダーの日付とF_売上明細最終日を比較して、F_売上明細最終日より以前である場合のみ移動平均を表示するように売上移動平均のメジャーを変更します。

売上移動平均:
=IF(
MIN( 'カレンダー'[日付] ) <= [F_売上明細最終日],
DIVIDE( [移動平均売上合計], 3 )
)

ちなみにDAXではIF文の条件に一致しなかった場合の数式を省略するとブランクとなり、結果としてピボットテーブル・グラフではブランクは非表示になるので、これをうまく利用します。

これで結果をピボットテーブル・グラフで確認してみます。

ピボットテーブルでは3月29日で移動平均がストップしているのが分かります。

また、ピボットグラフでもきれいに2018年度で折れ線が止まるようになりました。

リンク集

よくお世話になるリンクを集めました。

DAX関連

DAX関数ガイド（英語）

マイクロソフト社のものよりずっと読みやすいです。

DAX Guide

DAX Formatter

DAX式を自動でフォーマットしてくれます。一度、式が動くようになったら、ここに入れて成型するとよいでしょう。

DAX Formatter by SQLBI

Power Query関連

その他

mockaroo

フリーのWEBテストデータ作成サイト。本を書くときにお世話になりました。
https://mockaroo.com/

スライサーを使った予算２点バージョン比較

[DAX] 第７章 予算vs実績比較 - 予算バージョン比較

目標

今回は、拙著『Excelパワーピボット 7つのステップでデータ集計・分析を「自動化」する本』の「第７章 予算vs実績比較」のデータを使って、予算の２点バージョン比較を行います。

例えば、年間の予算計画は、会計期間中、四半期ごと月ごとに見直される運用があり、それぞれのバージョン毎にどこが変化したのかを知る必要があります。今回は、目標としてスライサーで任意の２つのバージョンを選ぶと、それぞれの売上予算と差異を表示できるようにします。（テストデータの関係上、会計年度を使って比較します。）

なお、バージョンとなる項目は数値項目であることに注意してください。

とりこむステップ

とりこむステップでは、予算Excelファイルのファイル名である会計年度をバージョンという新項目として追加します。

拙著『Excelパワーピボット 7つのステップでデータ集計・分析を「自動化」する本』のP301、仕上げと予算データのとりこみで、会計年度を削除せずに残します。

削除された列１ステップを選択し、数式バーで「"会計年度",」を削除して以下のように変更してください。

変更前

変更後

次に、会計年度をバージョンにリネームしてください。

閉じて読み込む実行後、予算テーブルにバージョンが追加されるのを確認してください。

これでとりこむステップは完了です。

ならべるステップ

ならべるステップでは、予算のバージョン比較をするためのピボットテーブルを作ります。そのとき、バージョンはピボットテーブルの中に置くのではなく、スライサーにする点に注意してください。敢えてピボットテーブルの絞り込みは緩めにしておくのがポイントです。

最初に予算のバージョン比較シートを追加します。

次に、B3セルに移動して挿入メニューからピボットテーブルを追加します。ピボットテーブルを追加するときは、このブックのデータ モデルを使用するが選択されていることに注意してください。

次に、分析（ピボットテーブル分析）メニューで更新→すべて更新を実行し、ピボットテーブルのフィールド→すべて→F_予算を確認して、項目にバージョンが追加されたのを確認します。

次に、ピボットテーブルに項目を以下のように並べます。
　列：　　　　　カレンダー [会計四半期]、カレンダー [月]　
　　　　　　　　（「会計年度」は追加しません）
　行：　　　　　商品カテゴリー [商品カテゴリー]
　値：　　　　　F_予算 [売上予算]
　スライサー：　F_予算 [バージョン]

これでならべるステップは完了です。

ただし、この段階ではピボットテーブルの各セルの値はすべての予算バージョンの合計を表示しています。なぜなら、各セルの値にはバージョンに関する絞り込み（フィルター）が効いていないため、すべてのバージョンの売上予算合計となっているためです。

バージョンの絞り込み条件（フィルター・コンテキスト）

バージョン
2016
2017
2018

試しにスライサーで2017を選択して、売上予算を確認してください。

これでバージョン2017の売上予算が表示されました。

バージョンの絞り込み条件（フィルター・コンテキスト）

バージョン
2017

次に、Ctrlキーを押しながら、2016と2018を選択してください。

これでバージョン2016と2018の合計を表示することができました。

バージョンの絞り込み条件（フィルター・コンテキスト）

バージョン
2016
2018

次のかぞえるステップでは、このようにして合計された2つのバージョンをそれぞれ切り出し、別のメジャーで表示します。

かぞえるステップ

今度はメジャーを使って、スライサーで選択された２つの予算のバージョンを比較します。

選択されたスライサーから最大値・最小値を持ってくる

まずは、スライサーで複数選択されたバージョンの最大値と最小値を持ってきます。バージョンは数値項目なのでそれぞれ以下のように定義します。

• 最大値　⇒　最新バージョン
• 最小値　⇒　前回バージョン

選択された二つのバージョンは数字なのでそれぞれMAX関数、MIN関数で特定することができます。さっそくF_予算テーブルに以下2つのメジャーを作ってピボットテーブルに追加してください。

最新バージョン :
= MAX('F_予算'[バージョン])

前回バージョン :
= MIN('F_予算'[バージョン])

スライサーの選択を解除して数値の中身を確認すると、以下のようになっています。

• 前回バージョン：2016
• 最新バージョン：2018

バージョン	メジャー	数式
2016	前回バージョン	MIN('F_予算'[バージョン])
2017
2018	最新バージョン	MAX('F_予算'[バージョン])

ここで試しに、スライサーの選択を2017と2018にしてください。今度は、最新バージョンと前回バージョンが以下のように変化します。

• 前回バージョン：2017
• 最新バージョン：2018

バージョン	メジャー	数式
2017	前回バージョン	MIN('F_予算'[バージョン])
2018	最新バージョン	MAX('F_予算'[バージョン])

これで選択された複数のスライサーから最新バージョンと前回バージョンの値を持ってくることができました。

選択されたスライサーの最大値・最小値で売上予算を絞り込む

複数選択したバージョンから最大値、最小値を持ってくることができたので、各値セルの中のフィルター・コンテキストを書き換えて、それぞれのバージョンの売上予算を出すメジャーを作ります。

拙著『Excelパワーピボット 7つのステップでデータ集計・分析を「自動化」する本』のP251の当期売上累計メジャーの式を参考にして以下2つのメジャーを作り、ピボットテーブルに追加します。

売上予算（前回）:
=CALCULATE (
[売上予算],
FILTER( 'F_予算', 'F_予算'[バージョン] = MIN('F_予算'[バージョン]))
)

売上予算（最新）:
=CALCULATE (
[売上予算],
FILTER( 'F_予算', 'F_予算'[バージョン] = MAX('F_予算'[バージョン]))
)

先ほど作成した前回バージョン、最新バージョンのメジャーは不要なので外しました。適当にスライサーを選択して、前回と最新の値を正しく取得していることを確認してください。なお、3つ以上スライサーが選ばれている場合、その中の最小値と最大値を持ってきます。

本文P251の式との違いは、'F_予算'にALL関数が使われていない点です。

既に、この値セルには四半期、月、商品カテゴリーの絞り込みを受けた売上予算の合計値が入っており、その絞り込み条件（フィルター・コンテキスト）を尊重しているためです。

その合計の中からスライサーの値を使った更なる絞り込みで前回、最新の売上予算を取得しています。

ここまでできたので、仕上げに2つのバージョンの違いである売上予算差異メジャーを追加します。

売上予算差異:
=[売上予算（最新）]-[売上予算（前回）]

最後に、以下4点の変更を加えてかぞえるステップの仕上げです。

• 売上予算メジャーを外す
• Σ 値を「行」に移動
• 支店名のスライサーを追加
• P273の条件付き書式のテクニックを使って、書式を変える

バージョンだけでなく、支店名も組み合わせてインタラクティブに数値を比較することができます。

これでかぞえるステップは完了です。

えがくステップ

最後にグラフを追加して完成です。

ピボットグラフを作るときは本文（P.157）にあるように、ピボットテーブルからではなく、ゼロから作るのがポイントです。

フィールドリストは以下のように選択してください。

ピボットグラフができたら、デザインメニューのグラフの種類の変更で、組み合わせを選んでください。データ系列に使用するグラフの種類と軸を選択してください：は以下の設定にしてください。

次に本文（P161）を参考にして、ピボットグラフをスライサーに結び付けます。（事前に、ピボットテーブル、ピボットグラフには適切な名前を付けておいてください。）

最後にレイアウトを整えてえがくステップは完了です。

これで、スライサーを選択することで好きな2点間の予算バージョン比較ができるようになりました。

当期売上累計-VARを使った別解

[DAX] 第５章 当期売上累計 - VARを使った別解

今回は、拙著『Excelパワーピボット 7つのステップでデータ集計・分析を「自動化」する本』の「第５章 当期売上累計」についてVAR（変数）を使った別解を紹介します。

元の数式

第5章 のP251では、売上の累計を出すために以下の式を使っています。

=
CALCULATE (
[売上合計],
FILTER ( ALL ( 'カレンダー' ), 'カレンダー'[日付] <= MAX ( 'カレンダー'[日付] ) )
)

※ 数式を見やすくするため、改行を入れています。

ここで少し補足すると、FILTER関数とは、テーブルをふるいに掛ける関数です。

つまり、

• 一つ目の引数で受け取ったテーブル（ALL'カレンダー'）に対して、

• 第二引数のふるい（'カレンダー'[日付] <= MAX ( 'カレンダー'[日付] )）に掛けて、

• 残った行をアウトプットのテーブルとして返す

働きをします。

本文でも述べていますが、この数式を難しくしているのは右側のMAX関数がFILTER関数の中を飛び越えてオリジナル・フィルター・コンテキストの値を参照しているためです。

つまり、

1. FILTER関数の中では、カレンダーの行ループの中で「 'カレンダー'[日付] <= MAX ( 'カレンダー'[日付] )」の条件を一行一行チェックしていますが、
2. 「MAX ( 'カレンダー'[日付] )」は、FILTER関数を飛び越えて、ピボットテーブルの絞り込み条件＝フィルター・コンテキストを参照しているためです。

ちなみに①の一行一行のことを行コンテキスト (Row Context )と言い、FITLER関数のように一行一行のループ処理を作り出す関数をイタレイター (Iterator)と言います。

とっつきにくい言葉ですが、行ループ処理だと思っていただければ大丈夫かと思います。

別解

上記の②のMAXの部分を最初からFILTERの外に出してしまえば、式が分かりやすくなります。そのためにはVAR（変数）を使います。

書式　
　VAR <変数> = <式>
　[VAR <変数2> = <式2> [...]]
　RETURN <結果の式>

P251の式をこれに当てはめると以下の式になります。

=
VAR MaxDate = MAX ( 'カレンダー'[日付] )
RETURN
CALCULATE (
[売上合計],
FILTER ( ALL ( 'カレンダー' ), 'カレンダー'[日付] <= MaxDate )
)

こんどは、MaxDateを変数として外に出したので分かりやすくなりました。RETURN以下の数式は、MaxDateの部分を除いて全く同じです。

つまり、MaxDateはFILTER, CALCULATEの外で最初にフィルターコンテキストの値を拾ってきて、それをFILTERの中の行ループのふるいに掛けるイメージがしやすくなったと思います。

まずは、ピボットテーブルの個々のセルのフィルターコンテキストを受けたMAX日付を拾ってきて・・・
MaxDate（フィルターコンテキスト）のイメージ

その値を、FILTER関数の行コンテキストの１行１行で比較しています。
FILTER関数の中（行コンテキスト）のイメージ

二つの式の集計結果も同じになっています
集計結果

参考までに「当期」の条件も含めた2変数の式も以下に記します。

=
VAR MaxDate = MAX ( 'カレンダー'[日付] )
VAR MaxFinYear = MAX ( 'カレンダー'[会計年度] )
RETURN
CALCULATE (
[売上合計],
FILTER (
ALL ( 'カレンダー' ),
'カレンダー'[日付] <= MaxDate
&& 'カレンダー'[会計年度] = MaxFinYear
)
)

第５章では最終的にタイムインテリジェンス関数を使用するので、こちらの数式は使用しなくても良いですが、この書き方を覚えておくと別なケースで応用できます。