DIME MAGAZINE
エクセルでは、パワークエリとパワーピボットを連携して高度なデータ分析を実現できる。DAXはパワーピボット内で使用する数式であり、列単位で膨大なデータを効率的に処理できる。
目次
エクセルで複数の表を扱う業務では、「部門ごとの売上をまとめたい」「年度をまたいで実績を比較したい」といったニーズも多いだろう。こうした複雑な集計をスムーズに行うために役立つのが パワーピボットとDAX式である。
この記事では、DAXの基本概念から実際の集計例までをわかりやすく解説する。
パワークエリとパワーピボットの関係
DAXを理解するには、まずモダンエクセルの仕組みを知る必要がある。エクセルにはパワークエリとパワーピボットという機能があり、両者が連携して高度な分析を実現する。
パワークエリは外部や他シートのデータを取り込み、整形・加工する役割を担う。一方、パワーピボットはデータを「データモデル」として管理し、複数表の集計や関連付けを可能にする。
そして、このデータモデル内で用いる数式がDAXであり、列単位で膨大なデータを効率的に処理できるのが特徴である。
今回は、東京・神奈川・千葉の3支店の売上予算と実績のデータを使って、それぞれの役割を見ていこう。
パワークエリでデータを取り込む手順
まずは3支店のデータをパワークエリで取り込み、統合の準備を整える。
■支店別の売上データを集める
「データ」タブを開き、「データの取得と変換」→「テーブルまたは範囲から」を選択する。
全支店データをまとめてデータモデルへ追加
すべての支店データを1つの表にまとめ、パワーピボットで扱えるようにデータモデルへ読み込む。この工程が、DAX式を活用するための前提条件となる。
■複数のクエリを統合する
全支店のクエリが準備できたら、最後に1つの表に統合する。パワークエリの「ホーム」タブ→「クエリの追加」を使い、各支店データを選択して「追加」をクリックする。
■データモデルに読み込む
統合後、パワークエリエディターの「閉じて次に読み込む」を選択する。ここで「接続の作成のみ」にチェックを入れ、「データモデルに追加」を選択することで、表がエクセルの裏側にあるデータモデルに保存される。このデータモデルこそが、DAX式が動作する基盤となる。
このデータを基にピボットテーブルを作成すると、フィールドリストに「まとめデータ」が表示されるので、「支店名」を行、「月」を列、「予算」と「実績」を値に追加する。これで支店別・月別の予算と実績が並んだピボットテーブルが完成する。さらに「スライサー」を追加すれば、特定の月だけを選んで分析することも可能だ。
DAX式で差額を算出する
ピボットテーブルの基本集計だけでは得られない、「差額」や「達成率」などの分析を行うのがDAXの強みだ。ここでは、代表的な例として「実績−予算」の計算式を作成する。
■メジャー（計算フィールド）を作成する
目的は「実績 − 予算」という差額を自動計算することだ。パワーピボットのフィールド名を右クリックし、「メジャーの追加」を選択する。メジャーとは、DAX式で定義したカスタム計算式のことを指す。
そして、次のように設定する。
予実差し引き = SUM(’支店まとめデータ’[実績]) – SUM(’支店まとめデータ’[予算])
■DAX式のSUM関数
SUM()は通常のExcel関数と同じ合計関数だが、DAXでは「列全体」を対象にしている点が異なる。この式では、実績列の合計から予算列の合計を差し引き、その結果を「予実差し引き」というメジャーとして登録している。
■メジャーをピボットに追加して確認
作成したメジャーをピボットテーブルの「値」エリアにドラッグすると、「予実差し引き」列が表示される。
予算500・実績500なら差し引き0、予算800・実績700なら差し引き100といった具合に自動計算される。
このように、DAXを使えばセルごとに式をコピーせずとも、テーブル全体をまとめて処理できるのが大きな利点である。
最新号
