Legacy Excelに埋め尽くされた世界
イントロ
自部署内の定型資料をExcelで作成する業務って多くないですか？頻度は日次、週次、月次、四半期とさまざま。データの取得元も他部署やらシステムやらいろいろ。そのデータを元にExcelで集計や計算をして、最後に表やグラフを作成するという作業を行っていませんか？
その際、毎回システムのGUIを操作してデータを出力したり、Excelファイル間で何度もデータを貼り付けたり、関数の範囲を都度修正したりしていませんか？また、上の人は軽い気持ちで注文を付けますが、ちょっと集計方法を変えるだけで面倒な修正が必要になったりしてませんか？
よくありますよね。そして、それら作業がやたらと辛い。決まりきった単調作業にやたらと時間を取られます。もっと頭を使う業務に時間を回したいのに。
上記がLegacy Excel（＝古いExcel）が生み出している悲劇です。ですが、現在のExcelにはさまざまな機能が追加されています。それらを使ったモダンなデータ処理＝Modern Excelを使いこなして効率化すれば、そのようなことから解放されます（または同じ時間でもっとたくさん働かされます）。
Legacy Excelの広がり
個人的な経験で言うと、Legacy Excelにはいくつかの段階が存在します。
レベル1 ほぼ手作業
ほぼ手作業。デジタルの恩恵が無いに等しい。あっちのファイルからこっちのファイルにデータを持ってきたり、基準日の変更に合わせてグラフ等のデータ取得元の範囲を変えたりとか。プロトタイプでありがち。中身を追って作業をしなければならない分、何をやっているかの理解は深まるが、理解した後は苦痛でしかありません。
レベル2 関数で頑張る
Excel関数を使ってある程度の自動化がされている状態。基準日を変えると、データ取得元の範囲が自動で変わったりとかです。しかしながら、データをワークシートに貼り付けたり、データ数に応じて関数の範囲を変えるなどの手作業は依然残ったまま。複雑な関数を使って何やっているのかぱっと見でわからないという症状が出始めます。
レベル3 VBA使い始め
VBAを使い始めた段階。マクロの記録レベルに毛が生えたレベル。うまくやればかなりの効率化が可能です。
ただし、VBAはプログラミングなので、わかる人とわからない人に分かれてしまいます。後者にとっては「なんか知らんけど数字が出たンゴ」となります。ぱっと見わからないがさらに進みます。
レベル4 VBAにはまる
もっとVBAを使いだす。完全自動化も可能。VBAがわからない人は置いてけぼり。データの取得や加工・計算など、何でもかんでもVBA、VBA、VBA。VBAおじさん/おばさんの完成です。
話は逸れますが、VBAおじさん/おばさんが何かいろいろ頼まれ始めます。作ったら最後、「何か動かないんだけど」とか言われてサポートやメンテもやらなければならず、部署移動しても聞かれるので、逃れるには会社を辞めるしかありません。
Modern Excelではレベル4には行きません。そこは行き止まりです。レベル3に片足を突っ込むくらい。これからExcelを学ぶ人はレベル2から先はModern Excelに方向転換してください。
Legacy Excelで生じがちなアンチパターン
Legacy Excelに限った話ではないかもしれませんが、せっかくModern Excelに進むのであればアンチパターンも心に留めておきましょう。
アンチパターン1 前回作業への継ぎ足し
修正の手間が楽なので、前回作業への継ぎ足してしまう。先代から受け継がれてきた秘伝のタレのように（現実に存在）。結果、盲腸みたいに意味のない部分が残ったりします。作り直せばすっきりするのに、面倒or大変そうなのでやりません。また、引き継がれた途端、後任者にはどうにもできないため、盲腸部分も含めて固定化されてしまいます。
アンチパターン2 タイミングの悪いRPA
元の作業内容を見直さずにRPAで自動化しちゃう。
元の作業がぐちゃぐちゃ
→RPAもぐちゃぐちゃ
→誰も中身を見たくないので一気にブラックボックス化
→計数の誤りに気づかないor上流データの変更があった場合に対応できない
と悲惨なことになります。RPAも良いですが、まずは（Excelに限らず）業務内容の見直しを行いましょう。
さようならLegacy Excel、はじめましてModern Excel
複雑な関数やVBAを使う方法は古いです。風が語り掛けます「古い、古過ぎる！埼玉銘菓(ry 」。今では便利な道具があります。
特にVBAさん、もう頑張らないでください。VBAは本格的な（？）プログラミングが可能なので、何でも自分で作ることができます。しかし、作らなくてもいいんです。既に便利なツールがいろいろあります。
料理に例えると、VBAは最低限の調理道具（包丁、まな板、鍋、コンロ）のようなもの。最低限の調理器具だけでは大変。炊飯器、圧力鍋、低温調理器、ミキサー、フード プロセッサー、たこ焼き焼き器、ワッフル メーカー、ヨーグルト メーカー、ウォーター スライダー型そうめん流し器（ちょっと違うかも）などなど、世の中にある便利な道具は使いこなしたいものです。
そのツールとは、Power Query、Power Pivot、（Excelではないが）Power BI、Pythonの各種ライブラリーです。これらの紹介は後ほど行うとして、まずはVBAで作りこむ場合との比較を行います:
| VBAで頑張る | 既存のツールを使う | |
|---|---|---|
| 作る手間 | 大変。関数で切り出すにしても、使われ方を考える必要がある。 | 使いやすいように作られている |
| 使い方 | 自分しか知らない。ドキュメント化しなければならない。 | ドキュメントを読めば使える |
| 応用 | 都度自分で拡張。 | 最初からいろいろなケースに対応済み。 |
| バグ | 自分で探してつぶさないといけない | ほとんどない |
という感じで、如何にVBAで頑張るのが大変かがわかるでしょう。これらツールを使いこなし、効率的かつメンテナンスしやすいワークシートを作成するのがModern Excelです。
はじめようModern Excel
Modern Excelで利用するツール
具体的なツールは以下です。
- テーブル
- Power Query
- Power Pivot
- Excel関数とVBA
- Pythonと各種ライブラリー
簡単に説明します。
テーブル
Excel 2007から導入された機能です。見出し+データとなっているセル範囲をテーブルとして設定すると、さまざまな機能を持たせることができます。
- 名前付け可
- 見出しにフィルターが付く
- データの増減に対応可。下や右に入力するとテーブル範囲が拡大。→ピボットテーブルなどのデータ元、関数のフィルダウン、書式・条件付き書式・入力規則も
- VBAではListObjectとして利用可
- 関数が読みやすくなる（構造化参照）
- シマシマでおしゃれになる。
- Ctrl+Down（カーソルを最下行へ）やCtrl/Shift+Space（列/行選択）がテーブルの範囲で効くので使いやすくなる。
テーブルでは、整然データとなるようにデータを持たせます（行=レコードが観測、列が変数）。
詳細は別記事で。
Power Query
Excel 2010から導入された機能（当初はアドイン、2016以降組み込み）です。高度なETL（Extract /Transform/Load）処理がGUIで行えます。
- Extract: ブック内外にあるデータの読み込み。
- Transform: 列選択、欠損値対応など、加工/修正処理。
- Load: Transform後のデータ取り込み。
取り込み先はブック内のシートまたはPower Pivot(データモデル)になります。
Power Queryを使えば、VBAでプログラミングすることなく、GUIで簡単にデータの取り込みや加工が行えます。
詳細は別記事で。
Power Pivot
Excel 2010から導入された機能（アドイン）です。以下の機能を持ったデータモデルというデータ構造を元に分析が行えるピボットテーブル/グラフ:
- 複数テーブルをデータソースとできる
- テーブル間のリレーションの設定ができる
- DAX(Data Analysis Expressions)を使った計算
Power Pivotを使えば、VLOOKUP関数等を使うことなくテーブル間を連携させ、DAXによる定量的な分析ができます。
詳細は別記事で。
Excel関数とVBA
Modern Excelとは言ってみたものの、何だかんだで便利なのでExcel関数とVBAは使います。ただし、他のツールが得意なところでは使わないなど、出しゃばり過ぎないよう注意してください。
VBA入門記事はこちら。
Pythonと各種ライブラリー
これをModern Excelに入れるかどうかは意見が分かれるかもしれませんが、複雑なロジックを伴う処理をVBAでやっている場合の置き換えとしてPythonをおすすめします。
Pythonとは、最近AI関連で人気のプログラミング言語です。AI以外にもさまざまなライブラリー（便利な機能をまとめたもの）があり、統計的なライブラリーを作ればロジックを伴う処理を手軽に作ることができます。
また、さまざまなプログラミング言語との連携が容易に行えるため、VBAやC++などの他の言語で書かれた社内に既存の資産を活用したり、速度が求められる際にC++で書いたりと言った応用も可能です。Modern ExcelでもVBAとPythonの連携を足がかりに、Pythonのエコシステムを活用します。
なお、ライブラリーを使わずに一からPythonで書いてしまうと結局VBAを使うのと違いがなくなるため注意してください。繰り返しですが、Pythonの利点はエコシステムにあります。
Python入門記事はこちら。
原則的な考え方
そもそもExcelでのデータ処理がどのようなものかを再整理して、それをModern Excelで実現する方法を考えます。
Excelでのデータ処理は、おおまかに次のように分類されます:
- データ取り込み
- データ処理
- 整形: 途中の処理、表やグラフで利用しやすいように整形
- 追加/修正
- 既存のデータに新しい数字を追加
- 新規データを数字とともに追加
- 誤ったデータの修正や補正
- 関連付け: 数字テーブルと分類テーブルを紐づける
- 集約: 報告帳票に合わせた粒度で数字を合計
- 報告帳票（図表）作成
報告帳票の表で直接修正を行うなど、混ざってしまう場合もありますが、分けてしまえば上記のとおりでしょう。
これらを先のツールに合わせて再構築すると：
- ETL ⇒Power Query
- データ取り込み
- 整形: 途中の処理や表・グラフで利用しやすいように整形
- 既存のデータに新しい数字を追加（単純なもの）
- データ追加/修正 ⇒Excel関数 or Python （追加/修正後、Power Queryで取り込み）
- 既存のデータに新しい数字を追加（複雑なもの）
- 新規データを数字とともに追加
- 誤ったデータの修正や補正
- データモデル構築 ⇒Power Pivot
- 関連付け: 数字テーブルと分類テーブルを紐づける
- 既存のデータに新しい数字を追加（やや複雑なもの）
- 集約: 報告帳票に合わせた粒度で数字を合計
- 報告帳票（図表）作成
実際には、最終的なアウトプットまでの作業を分解し、上記のいずれかに当てはめ、適切なツールを用います。
これらツールを活用する上での留意点は次のとおり:
- データの取り込みはPower Queryで
- Excel上のデータはテーブルで保持する
- 報告帳票上でデータを修正しない/修正用のレコードを作成し取り込む
- 集計時の分類用のマッピングテーブルを作成する
- データの粒度を粗くしない/集計は最後
蛇足ですが、ツールに合わせて作業方法を変えてください。なぜなら、効率的な作業を突き詰めた結果、ツールが生まれているからです。逆をたどる=ツールが活かせるような作業にすることが、作業の効率化につながります。その際、ツールがどのような背景で作られたのか、どう生かせるかを知ることが大事です。
以下、各処理を説明していきます。
ETL
簡単に説明します。詳細はPower Query入門記事を参照。
Extract - データ取得
Power Queryは非常に多くの種類のデータに対応しています。以下は一例：
- ファイル
- 形式
- Excelのテーブル、シート
- CSVファイル
- Access DBファイル
- 保存場所
- ローカルフォルダー
- 共有ドライブ
- クラウド
- 形式
- データベース
- Webサイト
ただし、神エクセルのような見た目重視の報告用フォーマット上のデータは別です。この場合は神エクセルさまが固定であることを逆手にとって、ワークシート関数などでデータを引っ張ってきて、予めテーブル化しておき、それを読み込みます。
（日本人って何でも神さまにしてしまうんだなぁ。もしかしたら神エクセルも神話の時代のこんなやりとりから生まれたのかもしれない。イザナギ「デジタルの世界にも神さまが必要だ」、イザナミ「二千年以上先になるけどね」）
Transform - 前処理
このあたりもPandasと比べて遜色が無いくらい機能が豊富です。GUIなのでみんなが使えるのが良いです。
整形: 途中の処理や表・グラフで利用しやすいように整形
いろいろな整形に対応:
- テータの持ち方: 縦持ち⇔横持ち
- 情報追加: 計算や集計で必要となる情報を追加
- 補完: 欠損値を補完
- 集約: データの粒度が細かすぎる場合に適宜集約
既存のデータに新しい数字を追加（単純なもの）
同じレコードの異なる列同士の計算のように単純な計算であればPower Query上で可能。
Load - 取り込み
Power Pivotのデータモデルへデータを取り込みます。ワークシートにテーブルとして取り込むこともできます。
データ追加/修正
Excel関数、VBA、Pythonの出番です。以下、いずれの場合も、最後は作成したデータをPower Query経由でPower Pivotのデータテーブルに取り込みます。
ExcelからPythonを利用する方法はこちらを参考にしてください。
既存のデータに新しい数字を追加（複雑なもの）
簡単な計算であればPower Queryで、やや複雑な計算でもPower PivotのDAXで対応できますが、複雑なものはExcel関数やPythonで計算するしかありません。
天気予報で例えると（やったことないですが）、過去または現時点の気象情報（降水量含まず）から、その時点の降水量をシミュレーションで計算する場合など。その時点の気象情報の数字はあるので、降水量という新しい数字を追加します。無理やりDAXを使うよりExcel関数やPythonで計算する方がメンテナンスが楽でしょう。
新規データを数字とともに追加
例えば、現時点の気象情報を元に、将来時点の気象情報を計算する場合など。将来時点の気象情報を追加します。新しい時点に対するデータはレコードの追加となることから、Power QueryやDAXでは対応できないため、Excel関数やPythonを使う以外に方法がありません。
誤ったデータの修正や補正
修正方法は二つ。一つ目は、誤った数字を含む元のレコードを削除して、正しい数字を含む修正後のレコードを追加する方法。二つ目は、誤った数字を含む元のレコードはそのままで、足すと正しい数字になるような数字を含む修正用のレコードを追加する方法。いずれの場合もデータモデル上のデータに修正をかけます。
個人的には削除する手間がない分、後者の方がやりやすいと思いますが、ケースバイケースで対応します。大事なのは報告帳票に対してではなくレコードに対して修正をかけることです。報告帳票に対して行うと、報告帳票ごとに修正が必要になるためです。
データモデル構築
データモデルは以下（再掲）のようにデータ間の構造と計算機能を併せ持ちます：
- 複数テーブルをデータソースとできる
- テーブル間のリレーションの設定ができる
- DAX(Data Analysis Expressions)を使った計算
これら機能を以下のように利用します。
関連付け: 数字テーブルと分類テーブルを紐づける
データモデルは複数のテーブルを持つことができますが、大きく2つの種類に分けることができます。数字を格納するファクト テーブルと、分類を格納するディメンション テーブルです。前者を後者の切り口で分析していくことになります。
ファクト テーブルに（複数の）ディメンション テーブルを関連付けるデータ モデリングをスター スキーマといいます。スター スキーマとすることで効率よく分析や集計をすることができるようになります。
スター スキーマについては私自身よくわからないとこも多いので、以下のリンク先を参照してください：
- https://zenn.dev/pei0804/articles/dimensional-modeling
- https://qiita.com/ns-horikoshi/items/a7a68f066f3f7b0bb0d5
- https://docs.microsoft.com/ja-jp/power-bi/guidance/star-schema
既存のデータに新しい数字を追加（やや複雑なもの）
DAXを使えば単純な集計（合計、平均、個数、etc）以外にもやや複雑な集計を行うことができます。例えば、ファクト テーブルに日別の売上が格納されている場合に、前日との差分、当月累計値、前年同月比などを計算することができます。計算用に定義されたDAXをメジャーといいます。
集約: 報告帳票に合わせた粒度で数字を合計
スター スキーマ（と必要に応じてメジャー）が適切に設定されていれば、あとはデータ モデルを元にしたピボット テーブルで自由自在に集計ができます。帳票を見直す（粒度や切り口を変える）には、再度ディメンション テーブルを作り直します。
報告帳票（図表）作成
表であれば、データ モデルから作成したピボットテーブルを参照して、見た目を整えてあげれば良いでしょう。というか、ピボット テーブルはもうちょっと見た目を綺麗にできるようにして欲しいです。
グラフであれば、データ モデルから作成したピボット グラフで大抵は事足ります。こっちは細かく見た目を調整できますので。
グラフにはスライサーやタイムラインを追加してあげると、使い勝手が良くなるし、なんか見た目がかっこ良くなってModernな感じがします。
https://atmarkit.itmedia.co.jp/ait/articles/1604/19/news017.html
ここらで（いいかげんに？）具体例を
後日、Covit-19 tokyo.xlsxを説明した記事を作成します。
そしてPower BIへ
実はPower QueryでのETLとPower Pivotでのデータモデリングは、Power BIというアプリでそのまま使えます。そして、組織で使うなら断然Power BIです。なぜなら、クラウドでレポートを共有できるからです。
ここまで頑張って作った報告帳票を共有（=報告）するには…ファイルそのものだとサイズが大きいので値だけのシートにしたり、PDFにしたりして、メールで報告とかじゃないでしょうか。だるいですね、めんどいですね。
Power BIならデータをクラウドに上げるとレポートが自動で更新されますのでそれを見てねでOKです。さらにPower BIだとよりインタラクティブなデータの分析が可能です。
おわりに
考え方を中心にModern Excelについて述べてきました。Modern Excelを使いこなすために覚えることも多く、Legacy Excelからの脱却は難しいですね。ですが、データ分析や報告を仕事とする人には、これから10年使える（or使わないと周り置いてけぼりになる）技術ですので、是非習得していただきたいです。これまで散々Legacy Excelに泣かされてきたおじさんはそう信じています。