「仕組み」で作るモダンExcel～集計分析を自動化し、毎月の業務時間を劇的に減らす

日々の業務で膨大な売上データを扱いながら、Excelでの集計や分析に時間がかかっていませんか？「毎月、複数のファイルを開いてデータをコピペでまとめる」「VLOOKUP関数でマスタを参照したらファイルが重くて動かない」。これらの悩みは、従来の「セル単位の操作」に固執していることが原因です。

「モダンExcel」とは、従来の表計算操作とは異なり、「データの取り込み・加工（Power Query）」と「集計・分析（Power Pivot）」という機能を活用し、業務を「仕組み化」する新しい手法です。

本記事では、実際の研修で使われているノウハウを元に、具体的な手順を交えてその活用法を解説します。

Power Queryで「データ整形」を自動化する

多くの人が時間を費やしているのが、分析の前段階である「データの準備」です。Power Queryを使えば、このプロセスを一度の設定で自動化できます。

具体例：バラバラの売上データとマスタを統合する

例えば、売上データから不要な行を消したり、データ型を直したりする作業。毎回手作業で行っていませんか？ Power Queryエディターを使えば、以下のような処理を「手順」として記録・自動化できます。

１行目をヘッダーにする: 取り込んだデータの列名が正しくない場合、「ホーム」タブの「1行目をヘッダーとして使用」をクリックするだけで修正できます。
不要なデータの除外（フィルタリング）: 顧客IDが空欄のデータなど、不要な行は列メニューから「（null）」のチェックを外すだけで除外設定が完了します。次回以降、データが増えても自動的に除外されます。
計算列の追加: 例えば「消費税率」を一律で追加したい場合、「列の追加」タブから「カスタム列」を選び、数式を入れるだけで全行に自動適用されます。

一度この「クエリ（処理手順）」を作ってしまえば、翌月は「更新」ボタンを押すだけで完了です。

Power Pivotで「分析」を高速化・高度化する

「データはまとまったが、集計しようとするとExcelが固まる」「前年比を出すのが面倒」。これを解決するのがPower Pivotです。

秘訣は「スタースキーマ」でのデータ管理

大量データを扱う際、すべてを1枚の巨大な表にするのではなく、役割ごとにテーブルを分ける「スタースキーマ」という構成が推奨されます。

ファクトテーブル（事実）: 売上データなど、件数が多く日々増え続けるデータ。
ディメンションテーブル（属性）: 顧客マスタや商品マスタなど、分析の切り口となるデータ。

これらを「リレーションシップ」（テーブル間の関連付け）で結ぶことで、VLOOKUP関数を使わずにデータを紐づけることができ、ファイルサイズも軽量化されます。

実務で使える「ＤＡＸ関数」の威力

Power Pivotでは、ＤＡＸ（Data Analysis Expressions）という関数を使って、ピボットテーブルだけでは出せない高度な指標を作成できます。以下は実務でそのまま使える計算式の例です。

別のテーブルの値を参照して計算する（VLOOKUPの代わり）売上テーブルにいながら、商品マスタの単価を持ってきて計算する場合： = '売上ファクトテーブル'[数量] * RELATED('商品ディメンションテーブル'[商品単価])
配送にかかった日数を計算する注文から到着までのリードタイム分析などに有効です： = DATEDIFF('売上ファクトテーブル'[成約日], '売上ファクトテーブル'[商品到着日], DAY)

これらの計算式（メジャー）を一度定義しておけば、商品別でも、月別でも、ドラッグ＆ドロップひとつで瞬時に数値を呼び出せます。

ダッシュボードで「意思決定」を直感的に

最後に、整えたデータを可視化します。モダンExcelにおけるダッシュボード作成のポイントは、「スライサー」との連携です。

ピボットグラフを作成し、「年」や「商品カテゴリ」のスライサー（ボタン型のフィルター）を設置します。これにより、例えば「2025年」のボタンを押すだけで、売上推移グラフも、商品別円グラフも、すべて連動して「2025年」のデータに切り替わります。静的なレポートではなく、会議の場で「じゃあ、この部門はどう？」と聞かれても、その場でクリックして回答できる「動的なレポート」が完成します。