見て分かる、Excel「パワーピボット」「DAX関数」初級講座 分析をラクにする方法
ExcelにはPower Queryのほか、データの集計や分析を自動化する「Power Pivot」や「DAX関数」といった業務効率化に役立つ機能がある。今回の記事では、クリーク・アンド・リバー社主催のウェビナーを基に、時系列分析の方法や年度データを簡単にExcelで算出する方法を解説する。
前回の記事では、「Microsoft Excel」（Excel）の「Power Pivot」を使った業務効率化の一例を紹介した。前回に続く本稿では、その応用として、時系列分析に焦点を当てる。
今回の記事では、売上データの集計をサンプル例とし、年度ごとの売上推移や前年との比較、累計などをExcelの機能を使って効率的に集計する方法を解説する。架空の販売データを使って、各月・各年度の販売額を整理し、前年度比や年度累計を自動計算できるピボットテーブルの作成手順を、Power PivotとDAX関数を使って解説する。
本記事はクリーク・アンド・リバー社主催のウェビナー「3つに分けて体系を理解するExcelシリーズ データ集計・分析編 Vol.7」の内容をベースに、編集部で再構成して内容を追加した。
数字の推移を把握したいなら「時系列分析」機能を使おう
Excelで年度区分や期間比較、期間別の累計といった時系列の集計作業に、意外な程の手間がかかっていた。
しかしパワーピボットの「時系列分析」（Time Intelligence）を活用して一度仕組みを作れば、データ更新と同時に年度を基準とした期間比較や期間累計を自動計算できる。
今回は、こうした時系列分析を行うための基本手順として、次の4ステップで操作方法を解説する。
1．日付テーブルの作成
2．計算列を使った年度の設定
3．テーブル間のリレーションシップ設定
4．DAX関数を使ったメジャーの作成
時系列分析の最初の一歩、「日付テーブル」を自動作成する
前回の記事で、Excelの複数テーブルに複数のテーブルを関連づける（リレーションシップを設定する）ことにより、ソースデータを1つのテーブルにまとめることなく、集計できることを説明した。
リレーションシップによって結び付けられたテーブル群と関係性のことを「データモデル」と呼ぶ。データモデルの中に独立したカレンダーとしての「日付テーブル」を追加しておくと、時系列を分析しやすくなる。
一例を示すと、前回のサンプルのようにソースデータに「販売日（年）」と「販売日（月）」が入っている場合に3年分の販売額の集計表（ピボットテーブル）を作ると、各年の1月を先頭に、12月までの販売額を表示する表ができる。
しかし「年度」を基準に分析したいときは、「4月」から翌年「3月」までの一期を対象にしなければならない。図のような表をピボットテーブル機能だけで作り直すには、過去のファイルを掘り起こしてVLOOKUP関数で横に並べたり、4月始まりの年度合計を算出するために、行を並べ替えたり、SUM関数の範囲を手動で修正したりといった、面倒な操作が必要になる。
一方、パワーピボットで「日付テーブル」をデータモデルに追加すると、DAX関数式を使って、とてもシンプルに年度ごとの各種の表を作成できる。
例えばある年度の各月販売額の横に前年度の販売額を配置し、その横に対前年比（％）、さらにその横に販売額累計を配置して表にできる（手順は後述）。一度作成してしまえば、ソースデータが更新されてもピボットテーブルを変更することなく、即座にその変更が反映される。これがパワーピボットを使う大きなメリットだ。
では、「日付テーブル」を作成してみよう。日付テーブルとは、集計対象となる期間の全ての日付を一覧にしたテーブルだ。これはパワーピボットのカレンダー自動生成機能で簡単に作成できる。
以下、操作手順は、前回の記事でサンプルとしたExcelファイルを前提にしている。すでにピボットテーブルが、「T_販売データ」「M_販売先マスタ」「M_担当マスタ」「M_商品マスタ」の各パワーピボットテーブルのリレーション設定を経て作成されているものとする。ただし、詳しいデータ内容が分からなくても手順を読めば使い方のイメージはつかめるはずだ。
手順
1．Excel画面の［Power Pivot］タブの［データモデル］グループから［管理］をクリック。
2．パワーピボットの画面が開き、データビューが表示される。表示ビューは［表示］グループ内の［データビュー］ボタンや［ダイアグラムビュー］ボタンで切り替えられる。
3．データビューの［デザイン］タブから、［予定表］グループ内［日付テーブル］ボタンの［新規作成］をクリック。
4．新しく「予定表」（または「Calender」）テーブルが追加される。Date列には、データモデル内の日付範囲をカバーする連続した日付データが表示される。同時に「年」「月」「曜日」などの属性を示す列も自動作成されている。この日付範囲を、仕上がりのレポートなどが対象とする日付範囲に変更したい。
5．［日付テーブル］ボタンから［範囲の更新］をクリックする。
6．表示される「日付テーブルの範囲」ダイアログで、集計対象にしたい日付範囲を設定し、［OK］ボタンをクリックする。
多くの企業会計では「4月1日〜翌年3月31日」を1つの年度として扱うため、元データが存在しない未来の日付（年度末まで）も含めておく必要がある。図の例のように、データが2023年1月から始まっているが、2022年度（2022年4月1日開始）として扱いたいという場合や、2025年12月までのデータしかないが、2025年度末（2026年3月31日）までの欄（空欄）は用意しておきたいという場合には、日付テーブルの開始日を「2022/04/01」、終了日を「2026/03/31」に設定する。分析に必要な年度の全期間が含まれるようにするのがポイントだ。
自動生成されたテーブルには曜日や英語表記の月名などが含まれるが、実務では使いにくいものも多いため、英語の月名や曜日などの不要な列は削除してよい。列を選んで右クリックし、［列の削除］を実行しよう。
また自動追加された「月の番号」列はただの数字になっているので、「4月」や「5月」といった月表記に書き換えた方がよい。いちいちセルを書き換える必要はない。ここはDAX関数式を使おう。
計算列を使った「年度」対応
「1月」「2月」…と表示させる手順
まず対象の列を選択する。数式バーには「=MONTH([Date])」と表示されている。これは「Date」列から月の数字を取り出すDAX関数式で、自動で設定されたものだ。このあとに「&”月”」を追加入力する。
=MONTH([Date])&"月"
Excel関数の書式とほぼ同様なので簡単だ。セル範囲を列名で指定できるのがDAX関数式の特長で、Excel関数より手間いらずだ。このようにDAX関数式の入っている列を「計算列」という。
年度を表示させる手順
この表を4月始まりの「年度」で並べ替える操作をしてみよう。そのためには、計算列を使ってロジックを実装する必要がある。ここではIF関数を使い、並べ替えの基準となる数値列（インデックス）を作成する。
ロジックは、次のようになる。
- 月が4以上の場合：月-3（例：4月→1 、12月→9）
- 月が3以下の場合：月+9（例：1月→10、3月→12）
この計算列の見出しを「月の順序」とし、1から12までの連番を振って、年度始まりの順序を定義する。パワーピボット画面に自動で表示されている「列の追加」列をダブルクリックし、「月の順序」と入力してEnter、数式バーに次の式を入力する。
=IF(MONTH([Date])>=4,MONTH([Date])-3,MONTH([Date])+9
「4月」が「1」、「5月」が「2」、…、「12月」が「9」、翌年「1月」が「10」、…、「3月」が「12」となるインデックスが出来上がる。
次にこのインデックスを使って[年度]列を作る。［列の追加］をダブルクリックし、［年度］に見出しを変更してEnterを押し、画面の数式バーに次のように式を入力する。
=if([月の順序]>=10,[年]-1,[年])&"年度"
「月の順序」が「10」以上であれば、［年］列（例えば2024）から1を引き、「年度」という文字を後に加えるという式だ。すると2024年1月〜3月の行の［年度］列に「2023年度」と表示できるようになる。以下のパワーピボット画面ではその全貌が見えないが、バックグラウンドでそのように処理されている。
「月の順序」と「年度」が正しく表示できたが、さらに、もともとの［月の番号］列と［月の順序］列を結び付ける必要がある。ピボットテーブル上で［月］列を使用した場合に、自動的に4月を先頭にした並び順を適用するためだ。
そのために「列の並べ替え」機能を利用する。［月の番号］列を選択した状態で、ホームタブの［並べ替えとフィルター処理］グループにある［列で並べ替え］をクリックする。設定ダイアログが表示されるので、「並べ替えの基準」列として先ほど作成した「月の順序」を指定して［OK］をクリックすればよい。
これで「月の順序」に従って「月の番号」が並べ替えられた。
日付テーブルのリレーションシップ設定
データモデルには、重要な手順として日付テーブルを追加する必要がある。
［ホーム］タブの［表示］グループにある［ダイアグラムビュー］をクリックすると、各テーブルのリレーションシップ設定状態が表示されるので、新しく表示された「予定表」テーブルの［Date］列を「T_販売データ」テーブルの［販売日］にドラッグ＆ドロップする。
すると「予定表」と「T_販売データ」のリレーションシップが、［Date］と［販売日］のつながりを介して設定される。ダイアグラムビューでは、両テーブルの間に関係線が表示される。関係線上をポイントすることでどのフィールドが関連づけられているのかが囲み線で分かるようになっている。
これで「予定表」テーブルの「Date」フィールドと「T_販売データ」テーブルが「販売日」フィールドを介して関連づけられ、リレーションシップの設定が完了し、データモデルが更新された。
パワーピボット画面を閉じてExcel画面のピボットテーブル（冒頭の図）に戻ると、画面右の「ピボットテーブルのフィールド」パネルに今作った「予定表」のフィールドが表示されている。表示が隠れているときは、パネルの［すべて］から［予定表］の［その他のフィールド］のクリックで当該フィールドが表示される。
このパネル下部の［列］ボックスに「予定表」テーブルの［年度］フィールドをドラッグ＆ドロップし、［行］ボックスには「予定表」テーブルの［月の番号］フィールドをドラッグ＆ドロップする。すでに他のテーブルのフィールド名が入っている場合は、フィールド名をドラッグして、パネル外（シート上）にドロップすると削除される。
するとピボットテーブルの形が切り替わる。それまでは販売データの中の日付を使っていたのだが、この操作で「予定表」テーブルの中の「年度」や「月の番号」を使うように変化した。
以降はピボットテーブルで「いつ」という切り口で集計するとき、4月始まりの「年度」や「月」を使うことができるようになる。
ここまでで、データの時系列分析の土台ができた。
前年度の各月販売額の追加、前年対比、販売額年度累計の計算
日付テーブルを利用した「年度」別集計表への改訂例の図のように、ピボットテーブルを改訂していこう。
年度ごとに分析するため、年度基準で表示できるようにスライサーを設定する。
スライサーは、［ピボットテーブル分析］タブの［フィルター］グループにある［スライサーの挿入］をクリックし、スライサーの挿入ダイアログを選択し、「予定表」テーブルの「年度」列を選択して［OK］をクリックする。
すると次の図のように、「年度」で表示を切り替えられるスライサーが表示される。
スライサーの年度をクリックすると、ピボットテーブルはその年度のデータだけを表示できるようになる。
このピボットテーブルの「販売額合計」列の横に前年の数字を表示して対比しやすくし、さらに「前年対比」「累計」「販売額年度累計」の列を並べて集計表の形にしたい。計算式はDAX関数式で作り、それを「メジャー」としてピボットテーブルに追加していく。
メジャーの作成 「販売額合計（前年）」
メジャーの作成は、ピボットテーブル内のどこかを選択した状態で、かつ「ピボットテーブルのフィールド」パネルで［T_販売データ］テーブルを選んだ状態で、以下の手順で作成する。
1. ［Power Pivot］タブの［計算］グループ内の［メジャー］をクリックし、［新しいメジャー］を選択
2.メジャー設定のダイアログが表示されるので、次の項目を設定する。
テーブル名は自動入力されている（T_販売データ）。メジャー名は「販売額合計（前年）」、下部の書式オプションのカテゴリーは「数値」、書式は「整数」、位どり区切り記号（桁区切りのコンマ）を使用する」にチェックする。
中央の「式」ボックスに、次のDAX関数式を入力する。
=CALCULATE('T_販売データ'[販売額合計],SAMEPERIODLASTYEAR('予定表'[Date]))
入力に不安があれば、「数式の確認」ボタンでエラーチェックできる。間違いなければ［OK］をクリックする。
ピボットテーブルに「販売額合計（前年）」列が追加された。（もし追加されていなければ、「ピボットテーブルのフィールド」パネルで「T_販売データ」テーブルのフィールドリスト中の［販売額合計（前年）］を、下部の「値」ボックスにドラッグ＆ドロップする。）
DAX関数の意味
- CALCULATE関数：任意のフィルター条件で式を計算する。
- SAMEPERIODLASTYEAR関数：現在ピボットテーブルで集計している期間の前年同期を取得する。
例えば2023年度の場合、それに対応する「前年の同じ期間」（2022年度）の日付セットを、日付テーブルから自動的に取得する。
これで、どの年度でスライスしても、必ず「販売額合計（前年）」が表示できるようになった。
メジャーの追加（前年対比、販売額年度累計）
続いて、「前年対比」と「販売額年度累計」をメジャータイトルとする2つのメジャーを作成する。手順は上と同様で、「式」ボックスへの入力内容がそれぞれ異なる。
前年対比
=DIVIDE('T_販売データ'[販売額合計],'T_販売データ'[販売額合計（前年）])
販売額年度累計
=TOTALYTD('T_販売データ'[販売額合計],'予定表'[Date],"3/31")
ピボットテーブルが完成すると、スライサーで各年度を切り替えながら、前年との差や成長率の推移を分析できるようになる。
DAX関数の意味
- DIVIDE関数：エラー（ゼロ除算）を回避して安全に割り算を行う関数
- TOTALYTD関数："Total Year-To-Date"の略で、年初から現在までの累計を計算する関数。
通常、YTDは1月1日から12月31日までを計算するが、第3引数に年度末の日付（"3/31"）を文字列で指定することで、パワーピボットは「4月1日スタート」の年度累計として計算を実行する。
スライサーで年度をクリックすれば、瞬時にその年度の実績、前年実績、累計推移が表示されるピボットテーブルができた。これをグラフ化すれば、簡単に期間の比較分析が可能なダッシュボードになる。
全2回の記事ではパワーピボットの概要を部分的に紹介したが、モダンExcelの画期的な利便性を感じていただけたかと思う。ここまでの手順にパワークエリによるデータ収集や整形の自動化を加えると、さらに大きな業務改革が可能になるだろう。
Copyright © ITmedia, Inc. All Rights Reserved.