モダンExcel活用術 パワーピボットやDAX関数を使った業務自動化の最短ルート

「Microsoft Excel」にモダンExcel機能が搭載されたことで、従来のVBA習得の手間をかけずに、業務自動化を構築するためのツールとしての活用が可能となった。今回はクリーク・アンド・リバー社主催のウェビナーを基に、パワーピボットとDAX関数の基礎を学ぶ。

[キーマンズネット]

　「Microsoft Excel」マクロの使い手が悔しがるのが「モダンExcel」の新機能だ。彼らの腕の振るいどころをなくしているのが「Power Query」（パワークエリ）、「Power Pivot」（パワーピボット）、200種類以上ある「DAX関数」だ。

　Excelの拡張で生まれたこれら機能や関数は、従来のVBA習得の手間をなくし、データハンドリングを極めて容易にする。単なる表計算ツールにとどまらない、大量の企業情報資産を活用した業務自動化を構築するためのツールとして新たな脚光を浴びるモダンExcelの使い方のうち、今回は特にパワーピボットとDAX関数の基礎を学ぶ。

本記事はクリーク・アンド・リバー社主催のウェビナー「3つに分けて体系を理解するExcelシリーズ　データ集計・分析編 Vol.6」の内容をベースに、編集部で再構成して内容を追加した。

モダンExcelは分析業務自動化の最短ルート

　表計算とデータ視覚化中心のExcelを、社内にバラバラに存在する大量のデータの「集計・分析」ツールとして再認識させるのがモダンExcelの魅力だ。

　従来のデータ集計機能は、VLOOKUP関数やピボットテーブル機能によりExcelファイル内の複数の表から必要な項目を選び出し、一度シート上に配置して集計するのが一般的だった。

　パワークエリとパワーピボットは主にExcel 2016以降で使用可能になった機能だ。パワークエリはExcelファイル外のさまざまなデータソースからデータを抽出して整形や加工（前処理・分析準備）でき、パワーピボットは複数のテーブルのデータを対象に簡単にレポートを作成できる。

パワークエリ、パワーピボット、ピボットテーブル、テーブルの位置付け

　これらの機能により、組織内の各種システムに分散しているデータをシートに書き込まなくても、必要に応じてExcelに集約して処理できるようになった。Excelワークシートの容量上限は1048576行×16384列だが、それを超える大量データも対象にできる。従来は、容量上限に至らずともデータや関数が多くなるとExcelの動きが遅くなるのが常だったが、モダンExcelでは大量のデータでも軽やかに処理可能だ。パワークエリで各種データソースから必要データをスムーズに抽出して、パワーピボットで高速に分析可能な点がモダンExcelの利点で、分析業務をストレスなく自動化できる。

　マクロだらけのExcelシートでは処理の仕方がブラックボックス化しがちだが、パワークエリとパワーピボットの組み合わせなら、処理の仕組みも視覚化できるので、分析担当者が変わっても容易に理解可能だ。

　今回説明するのはパワーピボットの使い方の基礎だ。Excel利用法に詳しい古澤登志美氏（ワンズ・ワン代表取締役）は「モダンExcelによる集計の本丸はパワーピボット」だと断言した。同氏が、サンプルデータを利用したオンラインセミナーの講義内容から主なポイントを紹介する。

「データモデル」を理解してモダンExcelを使いこなす

　モダンExcelを使いこなすポイントの一つとして古澤氏が指摘したのが「データモデル」を意識した活用だ。この場合のデータモデルとは、Excelの複数の「テーブル」を互いに関連付けて使いやすくする構造だ。

　従来、Excelではテーブルがよく使われてきた。少し説明すると、最上行に列見出し（フィールド名）があり、その下にデータが縦に並ぶいわゆる「縦持ち」の表は、簡単な操作（［挿入］タブの［テーブル］をクリック）で「テーブル化」できる。

　テーブル化するとそれだけで自動フィルターが設定され、「スライサー」機能によりさまざまな条件でフィルタリングできる。また書式を一括で設定できるのもメリットだ。

　それだけでなく、「ピボットテーブル」機能を使って複雑な計算式を使ったクロス集計表を作成したり、元データの更新をすぐに反映できるようにもなる（［挿入］タブの［ピボットテーブル］クリックから操作）。

　特にピボットテーブルは売り上げ集計や達成率の算出などに簡単に使えて便利だが、大量のデータを集計するとなると、例えば「商品マスタ」「担当マスタ」「販売先マスタ」「販売データ」などの表をVLOOKUP関数やXLOOKUP関数で1つの大きなテーブルにまとめる必要があり、ファイルが肥大化してPC動作が遅くなる弱点もある。

　これを解消するのがパワーピボットで、リレーションシップを設定し複数のテーブルを関連づけて管理し、1つのテーブルにソースデータをまとめることなく集計表などを作成できる機能を持っている。リレーションシップによって結び付けられたテーブル群と関係性のことを「データモデル」と呼び、その構造を理解することが、分析業務やレポート作成業務の効率化に役立つ。

　パワーピボットを利用するときは、各テーブル間の共通のフィールド（商品IDなど）を介して関係をつなぐ操作が必要になる。これが「テーブル間のリレーションシップの設定」だ。その操作例は次のようになる。

必要なテーブルを「データモデル」に追加

　1.各テーブルを「データモデル」に追加する。各テーブル内のどこかのセルを選択して［Power Pivot］をクリック。

　2.画面上部の［データモデルに追加］をクリック。

　これを繰り返して全ての必要テーブルをデータモデルに追加する。

リレーションシップ設定の操作

　1.［Power Pivot］タブの［管理］をクリックするとパワーピボット画面が表示される。これがパワーピボットの「データビュー」だ。

パワーピボットの画面例

　2.ウィンドウ右上の［ダイアグラムビュー］をクリック。

　3.各テーブルのフィールド名が表示されるので、例えば「販売データ」テーブルの「商品ID」フィールドに「商品マスタ」テーブルの「商品ID」フィールドをドラッグ＆ドロップする。

　これで「販売データ」テーブルと「商品マスタ」テーブルに「商品ID」を介したリレーションシップが設定される。画面上ではテーブル間に「関係線＝リレーション」が表示される。関係線をクリックすると、どのフィールドにリレーションが設定されているかが表示できる。線上の矢印は参照元と参照先を示している。このような操作を繰り返して各テーブル間のリレーションシップを全て設定していく。

各テーブルのリレーションシップを設定

注：パワーピボットが表示されないときは

　Excelのバージョンによってはパワーピボットをアドインとして有効にする必要がある。

　1.［データ］タブ＞［データツール］グループ＞［データモデル］ボタンをクリック。

　2.「アドインを有効にしますか？」と表示されたら許可してインストールする。

アドインが有効でなかった場合の操作

ピボットテーブルの作成

　リレーションシップを設定したらExcelに戻り、画面上の［挿入］タブから［ピボットテーブル］をクリックし、［データモデルから］を選択する。するとピボットテーブルが作成されるので、画面右側のピボットテーブルのフィールドパネルで、どのテーブルのどのフィールドを列にしたいか、行にしたいか、「値」にしたいかを、左側のフィールド表示から右側の「列」「行」「値」のボックスにドラッグ＆ドロップして設定できる。

ピボットテーブルの作成

パワーピボットが用意する「暗黙のメジャー」とDAX関数

　パワーピボット画面上部の表示グループ内にある［データビュー］をクリックすると、データ画面に切り替わる。そこで2つのポイントに注目しよう。

　1つは画面下部の「計算領域」に表示される計算結果の表示だ。これは「暗黙のメジャー」といって、フィールドリストから数値項目を「値」ボックスにドラッグするだけで自動作成される（表示されていない場合は［暗黙のメジャーの表示］をクリック）。集計が必要な項目を勝手に集計してくれるので、一時的に参照するのに便利だ。

暗黙のメジャー

　この暗黙のメジャーの表示セルの中身は、図の場合は「合計／数量;=SUM(’T_販売データ’[数量])」となっている。これがDAX関数を使った計算式である。セル範囲を計算対象にするのではなく、テーブルや列、行を対象にして式を作れるのがポイントだ。

　つまり、従来のExcelでは「=SUM(A1:A10)」のようにデータのある場所を指定して計算するのが普通で、行が増えると範囲修正が必要な場合があったのに対し、DAX式では、上に示した書式で書けば「数量」という列全体を計算できる。つまりデータが増減しても修正がいらなくなる。これが大きなメリットの一つだ。

　なお、何もしなくても「暗黙のメジャー」が現れるのは、裏側で自動的にDAX関数を使って計算しているからだ。このような自動処理（暗黙的）ができることは覚えておきたい。ユーザーが自分でDAX関数の式を書く（明示的）ことももちろんできて、これによって目的通りの集計ができるのがパワーピボットの醍醐味だ。

DAX関数の書式（例）　

　=SUMX(’テーブル名’,[数量]*RELATED('別のテーブル名'[単価]))

• 関数名でどんな計算をするかを表す
• かっこの中は引数
• 引数が複数ある場合は「,」で区切る
• 引数には「列」「テーブル」「DAX式」「値」などを指定できる

　SUM(’テーブル名’[列名])

• 引数で指定した列の値の合計を求める

　RELATED(’テーブル名’[列名])

• 別のテーブルにある列の値を、リレーションシップに従って呼び出す

明示的な「メジャー」はこう使え

　「暗黙」ではなく明示的な「メジャー」も使える。「メジャー」はピボットテーブルの「値」フィールドに配置して集計結果を出すための数式だ。ピボットテーブル内で集計する式の入れ物といえ、フィールドリストに表示され、いつでも集計できる。計算対象は列、テーブル、メジャーなどだ。

　明示的なメジャーは、パワーピボット画面の「計算領域」で次のように作成できる。

手動でメジャーを作成する（SUM関数）

　例えば「数量合計」というメジャーを作ってみる。

　1.パワーピボット画面下部の「計算領域」の空きセルを選択。

　2.「数量合計:=SUM(’T_販売データ'[数量])」といった式を入力すると、計算結果が表示される（「T_販売データ」は仮のテーブル名）。

　この自作メジャーは、Excel側のピボットテーブルのフィールドリストにも「fx」アイコン付きで表示され、通常の項目と同じように集計に使える。

リレーションを活用した計算（RELATED関数とSUMX関数）

　パワーピボットのデータモデルに新しい「計算列」を追加すると、行ごとの値の計算が効率的になる。例えば単価×数量などの計算に便利だ。これを求めるときに、「T_販売データ」には「数量」しかなく、「単価」は別の「M_商品マスタ」テーブルにしかない場合を考える。

方法1：計算列にRELATED関数を使って単価データをもってくる

　Excel関数のVLOOKUPに似た機能のDAX関数の「RELATED関数」を使うのが一つの方法だ。

　1.パワーピボットのテーブル右端「列の追加」をダブルクリックし、「販売単価」と名前を付ける。

　2.「=RELATED(‘M_商品マスタ’[単価])」と入力する。

　これで、リレーションシップが設定されている「M_商品マスタ」テーブルから、関連する値を自動的に探して列に貼り込まれる。さらに隣の列に「販売額」を作成し、「=[数量]*[販売単価]」とすれば、行ごとの売り上げが算出される。

　しかし次のようにもっとスマートな方法がある。

方法2：SUMX関数を使ったメジャーを作成する

　古澤氏が「DAX関数を使う最初の関門で、これを理解すると後の習得がスムーズになる」と強調したのが、もう一つの方法だ。ここでは「SUMX関数」（DAX関数。書式：SUMX(テーブル,数式)）を使う。

作成手順

　Excel画面から操作する例を示す。図のようなピボットテーブルがExcelにあるとする。

計算列を追加したいピボットテーブルの例

　1.［Power Pivot］タブの［計算］グループにある［メジャー］をクリック、［新しいメジャー］を選択する。

　2.メジャー作成のダイアログが表示されるので、「メジャー名」を「販売額合計」と入力。

　3.式の入力ボックスに「=SUMX(’T_販売データ',[数量]*RELATED(‘M_商品マスタ’[単価]))」と入力する。

　4.ダイアログ下部の「カテゴリ」で書式が選べるので、「数値」とし、桁区切りも設定する。

　5.［OK］をクリックすると、Excel上のピボットテーブルに、新しい計算列が一度にでき上がる。

SUMX関数を使ったメジャーを作成

SUMX関数を使ったメジャーによる列の追加例

　少し解説をすると、ここではSUMX関数は、「T_販売データ」の1行目の［数量］と、リレーションをたどった先の「M_商品マスタ」の［単価］を掛け算するという計算を、データの上行から下行まで1行1行実行して、その合計を出力する。

　これをメモリで実行するので、高速に結果が出力できる。作業用の列を作ることもなく、正しい計算結果を得ることができるのがこの種のイテレータ（反復子関数）の長所で、分析業務には重要だ。

　なお、作成したメジャーは、別の数式の中で再利用できる。例えば［販売額合計］メジャーを使ってその10％アップを目標値とするメジャーを作るといったことが簡単だ。

（例）

　メジャー名　次期目標値

　数式　　　　=[販売額合計]*1.1

　このように記述するだけで、［販売額合計］が変われば目標値も自動連動する。また、スライサー機能で「2024年」を選べば2024年の実績に対する目標が、「2025年」を選べば2025年の目標が、動的に計算可能になる。「去年と比較して」「累積で」といった計算も、このメジャーの組み合わせで実現していくことができる。

　以上、パワーピボットの使い方の一例を鍵にモダンExcelのメリットも紹介した。パワーピボットがいかに業務効率を上げるか、またメンテナンス性に優れているかの一端を理解いただけただろうか。次回はさらにモダンExcelの上手な使い方を解説していく。