Excelパワーピボット 7つのステップでデータ集計・分析を「自動化」する本

マクロ、VBAいらず?

Excel VBAがある程度できても、PowerQueryやPowerPivotには手付かずという方は少なくありません。ですが本書は、実際のサンプルデータを用いて解説されており、かなり分かりやすいのでスラスラ読み進めることができます。あっという間にスキルアップできるような1冊です。

はじめに

多くの皆様に愛され、使われ続けているExcelは、いま大きな変革を迎えています。この変化はExcel誕生以来の革新と呼ばれており、Excelの使用方法を抜 本から変える可能性をはらんでいます。特に本書のテーマである「データ集計・分 析」といったレポートの作成、そしてレポートの使用方法に関して、絶大な威力を 発揮する変化です。
しかし、残念なことにこれらの機能は日本ではほとんど知られていません。本 書を手にされた皆様は「パワービボット」という言葉を聞いたことがあるでしょう か?おそらく「ピボットテーブル」について知っている方はいても、それに「パ ワー」が付いた「パワービボット」についてはほとんどの方がご存じないことと思 います。「パワービボット」はその変化の中で生まれた強力な機能の1つで、本書 のメインテーマとなる機能です。

私はIT部門で10年以上のキャリアを積んだのち、経理部門へ異動した人間で すが、経理部門へ来て約4年が経った頃、ひょんなことからこのパワーピボットを 知りました。正直なところ初めてこのパワーピボットの機能を知ったとき、私は背 筋が寒くなりました。それまでIT部門の人間として多大な労力を払って作らなければいけなかったレポートが、プログラミングなしで簡単に実現できてしまうからで す。さらに調査を進めてゆき、「パワークエリ」の存在を知った時には恐怖すら感 じました。このパワークエリとパワービボットを組み合わせれば、従来ソフトウェ アとハードウェアの両面からIT部門の支援を得なければ成し遂げられなかったレ ボートが、もはやExcelファイル1つで誰でも作ることができるからです。IT部門 出身の人間として自分の誇ってきたレーゾンデートル=存在意義が揺らぐのを感 じました。当初、私は最初はこの事実を直視できませんでしたが、恐る恐る目を向けてみると、今度はその新技術、アプローチ、そして完成するものの美しさに魅了 され、すっかりExcelの虜となってしまいました。

しかし、さっそく知った新技術を使ってみようと手を動かし始めたところ、すぐに壁にぶつかってしまいました。それらの新技術は大変すばらしいものですが、ある特有のアプローチで使用しなければ、技術の群れの袋小路にはまりこみ、満足 なレポートすら作ることができないのです。それはITの世界では「ビジネス・イン テリジェンス」と呼ばれる分野でのアプローチです。
どんな優れた道具があったとしても、それを使いこなせなければ意味がありま せん。そこで私は自分自身の学習と体験をもとに、「パワーピボット」を中心とし て「パワークエリ」「DAX」「ピボットグラフ」「条件付き書式」といった種々の機 能を、一貫した7つのステップにまとめました。本書を手にされた皆様は、それに よって従来では到底考えられなかった高度なレポーティングおよび自動化を実現 できることでしょう。

本書は、これらのモダンExcelとも呼ばれる新技術群を日本に根付かせるべく、私が強い使命感を持って企画した内容が、翔泳社様のご協力によって実現された本です。本書をきっかけに1人でも多くの皆様が、従来の時間と精神力とお 金を削るアプローチから脱却し、新しいアプローチで次元の異なるレポーティン グを実現できるようになることが私の切なる願いです。
購尾祥

目次

はじめに
本書の使い方
第1部 理論編
第1章 Excelの常識を変えるモダンExcelの登場
1 Excel誕生以来の革新_004
社内に散らばるデータをまとめて取り込むPower Query_004
データの骨格を作るパワービボット_007
横無尽の集計を可能にするDAX_008
KPIを一覧できるダッシュボード_009
2 マクロ・VBAによる自動化の限界_010
習得に時間がかかるプログラミング言語_010
プログラムのブラックボックス化_010
遅い実行速度_011
3 モダンExcelのテクノロジー_011
プログラム不要で誰でも作れる_011
見える化されたプロセス_012
ハイ・パフォーマンス_012
4 テクノロジーの進化が「発想の制約」をなくす_013
インフラ環境の制約がなくなる_013
アプリケーション開発の制約がなくなる_014
発想の制約がなくなる_014

第2章 モダンExcelによる全自動レポートの仕組み
1「集計」と「分析」の違い_016
2 データとロジックの徹底分離で「定点観測」レポートを実現する_017
「定点観測」アプローチ_017
一回きりのレポート=One-Timeレポートの表面_019
「定点観測」のメリット_020
3「骨格」を作ってデータを集める_021
定型レポート-One-Patternレポートの悲劇_021
データの骨格=「データモデル」 _022
骨格を支える骨=「テーブル」 _024
骨と骨をつなぐ関節「リレーションシップ」_028
「データモデル」で何ができるか?_029
4 インタラクティブ・レポートでデータを表現_033
「文脈」に沿って「集計」をするピボットテーブル_033
データモデルで文脈を「 選択」_034
変化する文脈に対応した計算式「メジャー」_036
データを厚く「窓」=インタラクティブ・レポートによる「表現」_038
5 インタラクティブ・レポートがおこす変化_038

第2部 実践編
第1章 実践にあたって
1 動作環境について_044
2「パワーピボット」について_044
3 本書で掲載している画面イメージについて_045
4 Excelのバージョン確認の方法_046
5 レポートを作る際の心構え_048
とりあえず「試作品」を作る_048
どこで何を使うのかを明確に_048
作るときに考えて、緑り返すときは考えない_049
後で忘れても困らないように_049
意識しなくても要点が目に飛び込んでくるレポートを_049

第2章 まずは基本の星型モデルで7つのステップをマスター
1 生きたレポートを作るための7つのステップ_052
1みたてる_053
インプット情報の棚卸し_054
データモデルの下書き_055
アウトプットのラフスケッチ_057
2 とりこむ_058
サンプルファイルの準備_059
「売上明細」をとりこむ_061
「客」をとりこむ_082
「商品」をとりこむ_090
「支店」をとりこむ_092
「カレンダー」を作る_095
作ったクエリはグループ化_104
3つなげる_107)
「F_売上明細」と「顧客」をつなぐ_107
「F_売上明細」と「商品」をつなぐ_111
「F_売上明細」と「支店」をつなぐ_112
「F_売上明細」と「カレンダー」をつなぐ_112
ダイアグラムビューについて_114
4 ならべる_115
ピボットテーブルを呼び出す_116
確認用項目を「値」に_122
「商品」テーブルの項目を「行」に_123
「カレンダー」テーブルの項目を「列」に_126
スライサーを追加_128
仕上げのレイアウト調整_132
5かぞえる_138
「売上合計」メジャーの追加_139
「メジャー」と「値フィールドの設定」の違い_144
「販売数量合計」メジャーの追加_147
「平均単価」メジャーの追加_149
6えがく_152
ピボットテーブルからピボットグラフを作らない_153
ビボットグラフはゼロから作る_157
ピボットグラフとテーブルはスライサーでつなぐ_161
グラフを含めたレイアウト調整_162
7 くりかえす_167
データソースの更新_169
レポートを更新して「定点観測」 169
「数字テーブル」のコンパクト化_172

第3章 商品別収益性分析 1「クエリのマージ」で2つのテーブルを結合_178
接続用「F商品」クエリの用意_178
「F_商品」を「F_売上明細」にマージ_180
「商品」まとめテーブルの簡素化_189
追加項目の確認_190
2 利益率のドリルアップ・ダウン_191
「商品別収益性分析」ビボットテーブルの用意_191
「割増引合計」「利益合計」「利益率」メジャーの追加_193
「利益率」メジャーをドリルアップ・ダウンに対応_196
3 条件付き書式はメジャーに設定_200
4 割合の比較にはレーダーチャート_204

第4章 商品カテゴリー・商品別の売上割合
1 総計に対する割合_210
「商品カテゴリー別売上割合」ピボットテーブルの用意_211
「顔選択」の流れ_212
「商品」テーブルの選択条件を解除して「総計」を出す_216
「総計」に対する「商品カテゴリー」の割合を出す_220
スライサーと選択条件の解除_221
2 小計に対する割合_235
ピボットテーブルに「商品名」を追加_235
「商品名」項目の選択条件を解除して小計を出す_227
「小計」に対する「商品」の割合_230
3 階層ごとの条件判断_232
有効なフィルターを知る_232
条件に応じて処理を分ける_235
データバーには最大値と最小値をセット_237

第5章 当期累計売上
1 当期累計売上その1詳細パターン_242
「当期累計売上」ピボットテーブルの用意_242
「当期累計」フィルターの作り方_243
2 当期累計売上その2タイムインテリジェンス関数_255
タイムインテリジェンス関数で当期累計_256
当四半期累計と当月累計_258

第6章 売上前年比較
1 一年前の数字を持ってくる_262
「前年同期比」ピボットテーブルの用意_262
前年度の半期売上を取得する_263
2 前の年と比較する_2654
売上の前年同期比(単期)_265
売上の前年同期比(累計)_268
3 条件付き書式で前年比較_272

第7章 予算vs実績比較
1 予算vs実績比較の見立て_280
予算ファイルの構造_280
「ダイヤ型データモデル」について_281
2 複数のExcelファイルを一括取り込み_283
「フォルダーから」予算ファイル一覧を取得する_283
「カスタム列」でデータを一括取得_286
ピボット解除で横に並んだデータを縦に_290
テキストを日付データに変換_296
仕上げと予算データのとりこみ_301
3「商品カテゴリー」まとめテーブルの作成_307
4 ダイヤ型データモデルを作る_309
「F_予算」と「支店」をつなぐ_309
「F_予算」と「カレンダー」をつなぐ_310
「F_予算」と「商品カテゴリー」をつなぐ_310
「商品」と「商品カテゴリー」をつなぐ_311
5 予算vs実績比較_312
「予算実績対比」ビポットテーブルの用意_312
予算の合計と累計のメジャーを作る_314
予算vs実績比較のメジャーを作る_317
6 一人当たりの生産性分析_321

第8章 ダッシュボード
1 ピボットテーブル・グラフの用意_326
2 ダッシュボード向けの共通設定_331
目盛線の非表示_331
スライサーの設定_332
ピボットテーブルの設定_335
ピボットグラフの設定_337

本書の使い方

1.本書の構成
本書は、第1部:理箱編と、第2部:実践編で構成しています。 第1部:理論編では、パワーピボットをはじめとしたモダンExcelの新機能に ついて紹介します。また、それらモダンExcelの新機能を応用してレポート作成 を自動化するための仕組みについて説明しています。
第2部:実践編では、実際に手を動かしてExcelのレポートを作ります。この 中で、基本の7つのステップをマスターし、レポート自動化の基本を身に付けま す。様々なシナリオを通じて、新技術の応用方法を学んでいきます。
第2部:実践編から先に読み始めても構いませんが、初めて聞く言葉や考え方 が見つかった場合は、第1部:理論編に立ち戻って確認することをお勧めします。

2.動作環境および画面イメージについて
Microsoft Excelは、バージョンによって画面のデザインや機能面に違いがあります。本書は、その中でもExcel 2016以降のバージョンを主な対象としています。
Excel 2013やExcel 2010をご利用の場合は、アドインを追加することで、本 書で紹介している内容の大部分を試すことは可能ですが、画面やメニューが大 きく異なるため、動作の保証はできません。
また、本書に掲載している画面イメージは、筆者が本書執筆時に利用していたOffice365環境のExcel(2019年4月頃)によるものです。そのため、プレイン ストール版およびパッケージ版のExcel 2016の画面とは、イメージが一部異なる場合があります。いくつかの大きく異なる画面についてはExcel 2016の画面イ メージ(図番号の後に[2016]と記しています)を併記する形で対応しています。 また、わずかな違いについては、文章で説明しています。
動作環境については、第2部:実践編 第1章で詳しく説明していますので、ご 利用前に必ずご確認ください。

3・読者特典について
本書では、読者特典として翔泳社のWebサイトから、第2部:実践編で使用 する練習用のサンプルファイルと、特別付録のPDFファイルをダウンロードすることができます。
サンプルファイルには、デモで使用する「データソース」ファイルのほか、 各章の開始・終了時点のExcelファイルを用意していますので、自分が興味を 持っている章からデモを開始することもできます。ただし、PowerQueryのデー タソースを指定したフォルダーの場所(C:¥データソース)は変更できないので ご注意ください。また、Officeの更新プログラムが適用されていない環境の場合、 Power Queryの関数に互換性が無く、サンプルファイルが使用できない場合が ありますのでご了承ください。
特別付録のPDFをファイルには、ページ数の都合で本書に掲載しきれなかったシナリオ「顧客別売上分析」や、ビジネスインテリジェンス(BI) 用語についての解説、およびPower Pivotアドインを有効にする手順についての解説を掲載しています。

4・読者特典のダウンロード
本書の読者特典として、以下のサイトからサンプルファイルおよびPDFファ イルをダウンロードできます。
http://www.shoeisha.co.jp/book/present/9784798161181/
※会員特典データのダウンロードには、SHOEISHA iD(翔泳社が運営する無料の会員制度)への会員登録が必要です。詳しくは、Webサイトをご覧ください。
※ファイルをダウンロードするには、本書に掲載されているアクセスキーが必要になります。 該当するアクセスキーが掲載されているページ番号はWebサイトに表示されますので、そちらを参照してください。
※会員特典データに関する権利は著者および株式会社翔泳社が所有しています。許可なく配布したり、Webサイトに転載することはできません。
※会員特典データの提供は予告なく終了することがあります。あらかじめご了承ください。

[第1部] 理論編
第1部では、近年のExcelにおける「表計算システム型アプローチか らデータベース型アプローチへの変化」とその仕組み、および、それが業務ユーザーにもたらすメリットについて解説します。

[第1章] Excelの常識を変える モダンExcelの登場
ここでは、近年のExcelの進化の概要を、 旧来型のアプローチと比較しながら説明していきます。