本稿(全10回)で、CAAT(Computer Assisted Audit Techniques、コンピュータ利用監査技法)に通じるExcelを使ったデータ分析の基礎や、これまでの関数やVBAによるマクロ主体の『レガシーExcel』よりも簡単にデータの収集や分析が行える『モダンExcel』そのものに対する基本的な理解も深めていただこうと思います。
公認会計士・内部監査人・監査担当者など監査業務に携わられている方を中心に、税理士・職員・経理担当者など経理・財務・管理等の業務に従事される方などを対象に、実務で知っておくと便利に使える、Excelを使ったデータ分析のスキル・ノウハウの基本をご紹介します。
■これだけは知っておきたい!7つのExcel関数
実務で必須となる、最低限覚えておきたいExcel関数は、次の7つです。
①合計する➡SUM関数
②平均を求める➡AVERAGE関数
③テキスト(文字列)を結合する➡CONCATENATE関数(& [アンパサンド] 演算子でも代用可能)
④条件にあったものを合計する➡SUMIF関数(別途、SUMIFS関数)
⑤条件にあったものを数える➡COUNTIF関数(別途、COUNTIFS関数)
⑥条件分岐に必須となる➡IF関数(別途、IFS関数)
⑦値を検索する➡VLOOKUP関数
上記7つの関数に対する理解と、Excelで必須の機能「ピボットテーブル」の活用は、Excelの登竜門と言っても良いでしょう。
・「エクセルで最も使う!関数ベスト7(Microsoft調べ)」
https://excel.value.or.jp/?p=1256
・「ピボットテーブルの基本は、これだ!」
https://excel.value.or.jp/?p=1505
いずれにも、マイクロソフト公式「一次情報」のリンクを張っておきました(※1)。
(※1)
Excelに関する市販本やブログなどのコンテンツの中には、「一次情報」にない奇想天外な記述や、明らかに間違った内容も散見されます。
初学者にとって大切なことは、正しい基本の「型」を学習すべきということです。これはExcelでも同じです。
マイクロソフト公式「一次情報」をベースに、そのうえで市販本やブログなどで学習すると、Excelに対する正しい理解を深められると思います。
なお、サンプルデータを無料配布するサイトも散見されますが、注意してください。『モダンExcel』『Power BI』ともに強力なツールなため、サンプルデータを通じ「情報漏洩」する事例も公表されています(こうした指摘は、VBAによるマクロでも同様)。安易にサンプルデータをダウンロードすることは控えるべきです。信頼できるサイト以外からのダウンロードはお勧めしません。
■『モダンエクセル』『レガシーExcel』を一体理解することが大切
上記7つの関数とピボットテーブルは『レガシーExcel』の代表的な関数・機能 です。まずは、これらをしっかり理解してください。そのうえで、『モダンExcel』を理解して欲しいと思います。
そもそも『モダンExcel』は、従来のExcelと別の何かではありません。
マイクロソフト関係者によれば、Excel 2010 に「Power Query」と「Power Pivot for Excel」というアドインが追加されたとき、それ以前のExcel と区別する意味で『モダンExcel』という愛称が使われるようになった そうです。
こうした拡張機能とも言える『モダンExcel』と、以前からあるピボットテーブルやピボットグラフなど、言うなれば『レガシーExcel』の機能もフル活用することで、これまで不可能と思われていたことがごく簡単にできたり、思いも付かない用途に使えるようになったりもします。
Excelの新しい活用法そのものを『モダンExcel』と呼んでもいいでしょう。
図1 『レガシーExcel』(図1上のExcelとある部分)と『モダンExcel』(図1下がモダンExcelの2大ツール)を
一体理解することが大切(拙著「モダンExcel入門」(日経BP)より転載)
『モダンExcel』の活用は、図1 で示す2大ツールの「Power Query(現行Excelでは「データの取得と変換 」と呼ぶ)と「Power Pivot for Excel 」、さらに『レガシーExcel』とも言える従来からある機能のうち、ピボットテーブルやピボットグラフ、場合によってはテーブル も含め、データを分析・可視化する機能を「一体理解」 することがポイントです(※2)。
(※2)
残念ながら『モダンExcel』やその先にあるビジネスインテリジェンスツール『Power BI』も含め、間違った情報が氾濫しています。動作が不安定になる、計算を誤る、最悪「情報漏洩」に繋がる記述が散見されます。こうした事態に、マイクロソフト関係者も頭を抱えています。
正しい『Power BI』に関しては、下記リンクも含め、マイクロソフトの一次情報である「MS Learn」「Docs」などで理解を深めることを強くお勧めします。
https://docs.microsoft.com/ja-jp/power-bi/?WT.mc_id=DP-MVP-5002429
■『ノーコード』『ローコード』で自動化できる『モダンExcel』
『モダンExcel』を活用する大きなメリットは、その手軽さ にあります。
これまでExcelにおける「自動化」と言えば、VBA(Visual Basic for Applications)によるマクロが一般的でした。ただ、このVBAによるマクロは、プログラミングのスキルが必要なため、その攻略のハードルが高く、挫折された読者も結構いると思います。
しかし『モダンExcel』であれば、データ収集・分析の「自動化」をプログラミング不要(ノーコード)で実行でき、データ更新もワンクリックで可能となるため、VBAよりも簡単に使いこなせるようになると思います。
とはいえ、『モダンExcel』でまったくプログラミングの知識が不要ということではなく、最低限のデータベースなどに関するノウハウは要求されます。ただ、そのハードルはVBAに比べてはるかに低いものです。
したがって、これからExcelによるデータ収集・分析の「自動化」をしたいというのであれば、断然『モダンExcel』をお勧めします。
また、少しだけプログラムを書く「ローコード」により、実に多彩な「自動化」を『モダンExcel』を通じて可能となります。VBAなどでプログラミングに慣れた方であれば、『モダンExcel』で本格的なプログラムを記述することも可能です。『モダンExcel』による記述方法などがVBAによるマクロよりも簡単なことに驚かれることでしょう。
このように、プログラミングのスキルを全面的に必要とするVBAと違い、「ノーコード」「ローコード」でデータ収集・分析の「自動化」を実現できてしまうというのが『モダンExcel』の真骨頂 です。
■『レガシーExcel』と『モダンExcel』の新旧比較
『レガシーExcel』でよく使われる22の関数と機能(図2、左列)について、『モダンExcel』(同、右列)と比較してみます。
図2 『レガシーExcel』(左列)と『モダンExcel』(右列)の関数・機能の比較 (拙著「モダンExcel入門」(日経BP)より転載)
図2 の比較は一例です。このほかにも様々な方法が『モダンExcel』にあることを申し添えます。
『モダンExcel』を扱う上で最も重要な概念が、データベースの基本となる「VLOOKUP関数」の思考回路とデータの可視化に必須の「ピボットテーブル」のフル活用 です。
これら二つ以外の関数や機能は、すべて『モダンExcel』でより簡単に実行可能です。そのため、ワークシートで操作を要する『レガシーExcel』の関数や、VBAによるマクロなどの機能は、必要に応じて学べばよいと思います。
中でも財務会計や監査の実務に携わられている方であれば『モダンExcel』を利活用することで、VBAによるマクロよりも簡単にデータ収集・分析の「自動化」ができるようになります。ぜひ、『モダンExcel」を試してみてください。
■『モダンExcel』が、これからの主流
従来のExcel=『レガシーExcel』には、先述した7つの関数やピボットテーブル以外にもさまざまな関数や機能があり、それらを駆使すれば多くの処理ができます。
しかし、そもそも『レガシーExcel』には、大きな「欠点」があります。
データ量が多くなると動作が遅くなる など、Excelに由来する致命的な「欠点」があるわけです。たかだか10万行程度の「小さなデータ」ですら、読み込みや処理に時間がかかることもザラです。例えば、COUNTIF関数で10万行程度のデータを対象にデータ集計して見れば分かるでしょう。
こうした『レガシーExcel』の「欠点」は、『モダンExcel』を「一体理解」することで大きく改善できます。Excelの最大行104万8576行を超えるデータを『モダンExcel』に容易に取り込むことができるからです。
筆者は10億行を超えるデータを『モダンExcel』に取り込み(データベースソフトAccessではなく!)、CAAT(コンピュータ利用監査技法)を通じ、不正会計の兆候を発見した ことが何度もあります。その中には、発見が困難とされる「循環取引」のデータもあり、最も発見が困難とされる「経営者が、外部と共謀する、複雑な循環取引」の事案も含まれます。
『モダンExcel』と『異常点監査技法』を駆使すれば、容易とは言わないまでも、不正発見の可能性が高まることは、経験上から断言できます。
こうした実績から考えても、『モダンExcel』はCAATを含め、データ収集・分析の有効なツール だと言えます。
そもそも『モダンExcel』は、『レガシーExcel』(従来のExcel)を入り口として、強力な圧縮技術を搭載する「メモリー内分析エンジン」という『モダンExcel』特有のアーキテクチャ(技術)を用い、データを効率よくメモリーに格納してくれます。その効果は驚くべきもので、マイクロソフトの説明によれば、元の状態に比べて7分の1~10分の1にもデータを圧縮できるというのです。
そのため、『レガシーExcel』よりも『モダンExcel』のほうが、圧倒的に使い勝手が良く、データ収集・分析の局面で『レガシーExcel』よりも簡単に利用できることが多くなるわけです。
■転職の場面でも『モダンExcel』をできる人が求められる
もちろん、『レガシーExcel』による関数や機能も、便利な局面は多々ありますし、これらを全否定するものでもありません。
ただし、上述を踏まえれば、今後『モダンExcel』による処理が増えることは確実ですし、有能な人材を求める企業・組織においても『モダンExcel』に対応できる人材を欲することが増える ことは確定的でしょう。
したがって、これまでの「Excelできますか?」というのは、転職時の「当然」の前提条件となり、これからは「モダンExcelできますか?」というように、Excelスキルにも「付加価値」「バージョンアップ」が問われるシフトチェンジが起こります。
実際にそうした人材募集の事例も少しずつ出始めました。任天堂では「Excelのパワークエリ・パワーピボットなどの新たな機能を覚えるのが好きな方」という人材募集をしています。
【任天堂のモダンExcel人材の募集事例】
https://www.nintendo.co.jp/jobs/career/kyoto_sec5.html#stp
こうした現象はDX(デジタル・トランスフォーメーション)やRPA(ロボティック・プロセス・オートメーション)という「自動化」の新潮流からも当然のことと言えるでしょう。
次回以降も本稿(※3)では、実にさまざまなことが行える『モダンExcel』などの基本を、経理や監査の業務、経営管理の側面から「Microsoft365」で解説します。
(※3)
本稿に関連するブログを『モダンExcel研究所』で解説しています。一部、YouTube動画も用意しています。当該ブログ等も参考に『モダンExcel』に関する知見を得て欲しいと思います。拙著『モダンExcel入門』(日経BP)も参考にしてみてください。