【連載】１年目から知っておきたい！　監査のExcel入門～②重要な関数はこの４つ！

村上昌志

【編集部より】
監査法人スタッフとして働き始めて直面するExcelでの膨大な作業。大手企業では会計システムが導入されているとはいえ、経理業務ではExcelを使わない日がないというほど当たり前のツールであり、監査業務においてもExcelを使いこなせないことには始まらないと聞きます。
そこで、本企画では、公認会計士試験を終えて、監査法人スタッフとして実務に就く際に、まずは知っておきたいExcelの使い方や作法を、Excelのセミナー講師なども務める村上昌志さんに伝授していただきます！（全３回予定）
第１回：監査でExcelをどのように使うのか
第２回：重要な関数はこの４つ！

監査でよく使うExcel関数

今回はExcelの関数のうち、監査でよく使う４つを紹介します。
Excelの解説が中心ですが、監査実務をイメージしやすいように工夫しました。

そして、今回紹介する４つの関数をマスターすれば、関数は入門編を卒業したといっても過言ではありません。Excelに苦手意識がある方は、まずこの４つを完璧に使えるまで練習してみてください。

関数に共通のルールとは

Excelの関数は、特定の計算や操作を簡単に行うための「魔法の呪文」のようなものです。
関数を使うことにより、指定した範囲のものを瞬時に合計したり、特定の条件に基づいてデータを抽出したり、他にも様々なことが行えます。
関数を使いこなすと、Excelがもっと楽しく、便利になります。
最初は少し難しく感じるかもしれませんが、使いこなせばきっと「武器」になるはずです。

具体的な関数の説明に入る前に、まずは「関数に共通のルール」を押さえておきましょう。関数を入力する際には、主にこの６つのルールに従って入力する必要があります。
この後、個々の関数を解説する際に改めて説明するので、ここではざっと目を通してください。

なお、引数は「関数に引き渡す数値やデータ」を意味し、関数の（ ）の中のことを指します。

それでは、関数の紹介をしていきます。

Excelの関数入門はこの４つ！！

Excelの関数は500個以上ありますが、監査ではそのうち４つを覚えると多くの作業ができます。その４つとは、次のとおりです。

では、早速４つの関数を見ていきましょう。

SUM関数

まずは最も使用頻度が高く、かつ簡単な関数であるSUM関数を紹介します。
SUM関数は、指定した範囲の合計を返してくれる関数で、次のように入力します。

=SUM(数値1,[数値2])

そして、実際に入力すると下図のようになります。

なお、下図ではC列７行目にSUM関数を入力し、選択範囲（青枠に囲まれているところ）の数値を合計して、結果300を返してくれます。

このように、SUM関数を使うことにより、１つひとつ足し算をしなくても、瞬時に合計を算出することができます。
何度も使う関数ですので、ぜひマスターしてください。

＜具体的な入力例＞

関数を直接入力するのが初めての方は、上記だけではきっと難しいはずです。
前述した６つの共通ルールも見ながら以下の手順で入力してみてください。

これで、入力完了です！

補足

SUM関数は合計金額を瞬時に計算できる便利な関数です。特に少数のデータや同じ種類の大量データを合計する場合は大活躍です。
しかし、複数の種類が含まれているデータから集計する場合は目視で対象を個々に選ぶ必要があり、大量データの集計に適さない可能性があります。

下図のような例で、仮に種類が数十種類で、行数が数万行あることを想像するとゾッとします。
そのため、状況に応じて、同種類ごとのデータに分割したり、この後紹介する自動検索関数を使ったりしたほうがよい場合が多々あります。

XLOOKUP関数

ここでレベルが一気に上がりますが、XLOOKUP関数を紹介します。
初見では難しい関数ですが、非常に便利な関数です。

XLOOKUP関数は、表の中から自動検索して、ほしいデータを抽出する関数です。
下図のように、監査調書の数値を更新する際、顧客にいただいたデータから必要なデータを抽出します。

下記の例では、試算表から長期借入金の1,000を抽出しようとしています。
そんなときに活躍するのがXLOOKUP関数です。

なお、実際のデータは膨大であり、「＝D9」などの直接入力は、対象のものを探すのに時間がかかるだけでなく、ミスをする可能性があります。
効率的かつ正確に行うために、ぜひ「XLOOKUP」関数をマスターしてください。

それでは始めていきます。
まず、XLOOKUP関数は次のように入力します。

そして、実際に入力すると次のようになります。

これだけではきっと難しいはずです。
下記に具体的な入力例をまとめたので、改めて６つのルールを参考にしながら試してみてください。

＜具体的な入力例＞

以上で完了です。

補足１

実際の監査調書では、このあとに増減分析を行います。
また、試算表は少なくても数百行、多いときは数千行もあるので、実際の監査調書では同じシートに残さず、別シートや、値貼り付けをして調書から削除しておくことが多いです（その際は試算表から抽出していることがわかるようにメモを記載します）。

補足２

XLOOUP関数はExcel2021から導入された新関数であり、それより古いバージョンのExcelでは使用できません。そのため、XLOOKUP関数を使えない場合を想定し、VLOOKUP関数も簡潔に紹介します。 VLOOKUP関数は、以下のように入力します。

※FALSEの部分は、TRUE型もありますが、多くのケースではFALSE（完全一致）を使用するため、入門者はFALSEで覚えていただいて問題ありません。

そして、実際に入力すると次のようになります。

IF関数

次に紹介するのはIF関数です。
IF関数は、ある条件が「真」か「偽」を判断して、それに応じた結果を表示する関数です。

以下のような条件分岐のイメージです。

そして、IF関数は次のように入力します。

それでは実際に入力してみましょう。
今回の例では、金額的に重要な科目を選定するために、IF関数で金額判定をしています。そして、この判定結果をもとに、重要なものは個別検証を行い、それ以外はパスすることを想定しています。

これだけではきっと難しいはずです。
以下の手順を参考にしてみてください。

＜具体的な入力例＞

補足１

偽の場合のところに 「””」を入力すれば300000未満のものは空白になりますが、
下記のように、何も入力しなければ、300000未満のものは０と表示されます。

補足２

実務では、質的重要性も加味して総合的に判断します。また、パスしたものの合計が、手続実施上の重要性を超えないように留意する必要があります。

SUMIFS関数

最後に紹介するのはSUMIFS関数です。
SUMIFS関数は、条件に合うものを合計してくれる便利な関数です。
そして、次のように入力します。

それではSUMIFS関数を使ってみようと思います。
下図では、預金明細を集計して、試算表との一致を検証しようとしています。
具体的には、E列６行目にSUMIFS関数を入力し、預金明細から「普通預金」を検索して残高を集計しています。その結果が300になれば、試算表との一致を確かめることができた、ということになります。

いかがでしょうか。
４つの関数の中では最難関の関数なので、難しく感じた方も多いのではないでしょうか。
以下の手順も参考にしてみてください。

＜具体的な入力例＞

これでSUMIFS関数の入力が完了です。

補足１

F列には以下のようにIF関数を入れています。
大量の検証を一度に行うこともあり、判定を自動で行い可視化することによって、ミスを防ぐことができます。

補足２

引数に「＄」マークがついていますが、これは「F4」キーを押して参照範囲を固定しています。

仮に参照範囲を固定しなければ、下図のように、数式をコピーした際に参照先がずれるので注意してください。

補足３

今回の集計はそもそも何のためにやっているのかを補足します。
試算表と預金明細の一致検証は、「確認」手続きを実施するための前準備として行っています。

下図のように、期末監査では確認書を発送して、その回答結果と明細の突合を行います。

しかし、明細に漏れがあれば上記の検証に支障をきたします。
そこで、残高試算表と明細の一致を検証し、明細の網羅性を確かめています。
なお、銀行確認書は多くの場合、口座があるところに全件発送かつブランク確認書（取引を全て回答）形式での発送なので、そこでも網羅性は担保されています。

以上が「監査のExcel入門」第２回でした。

次回は「監査で役立つショートカット」を紹介します！
これを使いこなせば操作速度が劇的に変わります。ぜひマスターしてください。

【執筆者紹介】

村上　昌志（むらかみ　まさし）

公認会計士試験に合格後、コンサルティング会社で原価計算や創業支援、事業計画作成等の業務を経験し、監査法人へ転職。監査法人では金融を主軸としつつ、卸売業、アミューズメント、エネルギー業等、多岐にわたって監査を経験。最近ではIPO業務に力を入れている。
また、若手向けに監査Excel研修を毎年開催しており、監査調書の作り方も一緒に学べると好評である。
・X（旧Twitter）@masashikaikei