新規登録 求人検索
新規登録

監査や経理担当者が知っておくべき!『モダンExcel』データ分析超入門(全10回)

写真1

【第1回】実務で覚えておきたい『モダンExcel』の機能・関数

本稿(全10回)で、CAAT(Computer Assisted Audit Techniques、コンピュータ利用監査技法)に通じるExcelを使ったデータ分析の基礎や、これまでの関数やVBAによるマクロ主体の『レガシーExcel』よりも簡単にデータの収集や分析が行える『モダンExcel』そのものに対する基本的な理解も深めていただこうと思います。

公認会計士・内部監査人・監査担当者など監査業務に携わられている方を中心に、税理士・職員・経理担当者など経理・財務・管理等の業務に従事される方などを対象に、実務で知っておくと便利に使える、Excelを使ったデータ分析のスキル・ノウハウの基本をご紹介します。

第1回は、実務で覚えておきたい『モダンExcel』の機能・関数についてです。

POINT

・データ収集・分析は、『モダンExcel』が主流になる。
・モダンExcelは「Power Query(現行Excelでは「データの取得と変換」と呼ぶ)と「Power Pivot for Excel」の2大ツールで構成される。
・『モダンExcel』を利活用するには「一体理解」が大切。具体的には、『レガシーExcel』のデータ可視化ツールであるピボットテーブルやピボットグラフなどと、『モダンExcel』の2大ツールそれぞれの基本、これらを「三位一体」で理解する必要がある。
・転職の場面で、既に『モダンExcel』が求められている。

■これだけは知っておきたい!7つのExcel関数

■『モダンエクセル』『レガシーExcel』を一体理解することが大切

■『ノーコード』『ローコード』で自動化できる『モダンExcel』

■『レガシーExcel』と『モダン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

図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

図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)も参考にしてみてください。

写真2

『モダンExcel研究所』https://excel.value.or.jp/

執筆者プロフィール

村井 直志(むらい ただし)
公認会計士 / データアナリスト

日本公認会計士協会東京会コンピュータ委員会委員長、経営・税務・業務各委員会委員、第三者委員会委員などを歴任。研究テーマ『CAAT(コンピュータ利用監査技法)で不正会計に対処する、エクセルを用いた異常点監査技法』で日本公認会計士協会研究大会に選抜。

著書

『3000社の決算を分析してきた会計士が教える 経営を強くする 会計7つのルール』(ダイヤモンド社)
『Excelによる不正発見法 CAATで粉飾・横領はこう見抜く』(中央経済社)
『会計ドレッシング10episodes』(東洋経済新報社)
『モダンExcel入門 データ分析&可視化の新しい教科書』(日経BP)など

現場で働く会計士の声

現場で働く会計士の声 一覧

10問の質問に回答するだけで、
性格タイプやキャリア志向が明らかに!

診断をはじめる(無料)

新着記事一覧

執筆者一覧を見る

特集記事

公認会計士の資格に関するカテゴリーごとにまとめたページです。

会計人の人生観・仕事観を紹介「Accountant's Magazine」最新号

「Accountant's Magazine」は、著名な会計プロフェッションにスポットをあて、その人生観・仕事観を紹介。会計・経理分野に従事する人と仕事の将来像を提示する、読者と共に考えるヒューマンドキュメント誌です。今なら新規登録していただくと、「Accountant's Magazine」(WEB版)の全記事を無料で閲覧することができます。

新規登録をしてWEBマガジンを読む(無料)

公認会計士に会える

PARTNERS
学生インターン募集

10問の質問に回答するだけで、
性格タイプやキャリア志向が明らかに!

診断をはじめる(無料)

公認会計士、会計士補・試験合格者向け新着求人

公認会計士の求人一覧

公認会計士の転職成功事例

公認会計士の転職成功事例一覧

公認会計士 転職Q&A

公認会計士の転職Q&A

10問の質問に回答するだけで、
性格タイプやキャリア志向が明らかに!

診断をはじめる(無料)

企業特集

マンガでわかる 会計士の転職

関連コンテンツ