経理が知っているべきExcel関数

今回はExcelの関数について書いてみました。
関数は本やWebを読んで勉強しても、いざ使おうとしたときスペルや引数の内容を覚えていないことが多く、意味のない学習になることがよくあります。
私は実務をこなす中で関数を頻繁に使用して、初めて使いこなせるものだと感じてます。つまり、頭ではなく体で覚えるものだと思います。

関数を使う際に注意してほしいことがあります。
それは、誰も使い方を知らないような関数を使って複雑なExcelを作る人がいますが、その人が休んだり、いなくなったりすると誰もExcelをメンテすることができなってしますことです。
結局、同じ機能のExcelをもう一度作り直す羽目になったりします。
基本は、内容がブラックボックスにならないよう誰もが知っている関数のみを使って作り上げのがベストです。
それが不可能なら、Excel内に詳細にメモを残す必要があります。
ほんのわずかなメモがあるだけでも、内容の把握の難度が全く違ってきますので複雑なExcelには必ずメモを付けましょう。

以下、私の考える「必須関数」と経理では頻繁に使用することになる「VLOOKUP関数」、「SUMIF関数」を見ていきます。

必須関数

以下の関数はどれも普段から頻繁に使うものです。
本屋やWebで調べなくてもその場ですぐに入力できるようになる必要があります。

関数名 機能
SUM データの個数を求め
COUNT 切り取り
ROUND/ ROUNDUP/ ROUNDDOWN 小数点以下の数を四捨五入/切上げ/切下げ
AVERAGE 平均値を求める
IF 条件を満たせばTRUEを満たさなければFALSEを返す
RIGHT/ LEFT/ MID 文字列を指定文字数だけ取り出す

これらはどのExcelでも使われるため特に使用例などは記載しません。

VLOOKUP関数

この関数は、経理の仕事で使われるものの中で最も重要なものだと考えています。
使い方は次の通り、4つの引数を指定して使います。

VLOOKUP(検索値, 範囲, 列番号, 検索の型)

細かい引数の説明をするよりも使用例をいくつか見てもらった方が早いので、以下に2つのよく使用される例を示します。

1.マスタ照合

経理システムなどから帳票を出力すると勘定科目名が付与されておらず、勘定科目コードのみのデータしか得られない場合があります。
そのような時に、VLOOKUP関数で勘定科目マスタから出力データに科目名を付与することができます。

この図では出力データに科目コードと残高しかないため、資料を作成したり分析したりする場合に不便です。
そこで、勘定科目マスタから科目名をVLOOKUP関数で取得して残高の隣に科目名を付与しています。
勘定科目以外にも製品コードや部門コード、取引先コードなど様々な場面でこの方法を使い名称を付与することができます。

今回の場合は勘定科目マスタと出力されたデータ内の科目コードが同じ形式だったためすぐにVLOOKUP関数を使うことができましたが、例えば、勘定科目マスタは6桁の整数で科目コードが登録される一方、経費明細の科目は4桁で設計されている場合も想定できます(普通はあり得ないことなのかもしれませんが、私自身の経験として、このような桁数の違いや科目コード体系が異なるデータには頻繁に遭遇し、その都度変換テーブルを使ってコードの変換を行い名称変更や別システムへの登録などを行ってきました。
自社システムと外部のパッケージソフトでコードの桁数が違っており、すでに稼働している自社システムには手を加えられないため仕方なく手作業で変換するのが日常でした)。この場合、6桁→4桁への変換作業が必要となります。

B列の科目コードは6桁ですが下2桁は常に00となるように運用されているとします。
この時、4桁の科目コードを作るにはLEFT関数を使えばよいのですが、LEFT関数を使用すると結果が「文字列」となって返ってきます。
経費明細データのコード(G列)は「数値」であるため、このままでは単純にVLOOKUP関数を使うことができません。
そこで、VALUE関数を使って「文字列」から「数値」への変換を行っています。これにより、経費明細データのコードを検索キーにしてVLOOKUP関数を使うことができます。

2.表間のデータ照合

表Aにあって表Bにはないもの、あるいはその逆を知りたい場合にもVLOOKUP関数を使うことができます。
この場合、検索キーにヒットするかしないかだけが問題となるため第3引数の列番号はエラーにならなければ何でもよいのですが、下図では1を使用しています。

図の通り、自分の表にはあって相手の表にはない果物名はエラー(#N/A)が返されています。
両方の表にある果物名は「みかん」と「ブドウ」だけであり、残りはどちらかの表にしか載っていないことが分かります。
表のように果物の種類が少なければ目視でも確認できますが、数百、数千点ある製品リストの照合であれば関数を使うしか手はありません。

本来の使い方ではないと思われますが、私の周りでもこのようにVLOOKUP関数を使って表の照合を行う人が結構います。
使う機会は意外と多いので、このような方法もあると覚えておくとよいでしょう。

SUMIF関数

SUMIF関数は条件に合致したものの合計値を求める関数です。
3つの引数を使って次のように書きます。

SUMIF(範囲, 検索条件, 合計範囲)

私の経験上、使う機会はVLOOKUP関数に比べると少ないのですが、うまく使えばすごく便利な関数という印象です。
例えば下図のようにExcelで仕訳を作っていき最後にSUMIF関数で借方・貸方を科目別に集計すれば精算表が一気に作れてしまいます。
第1引数に仕訳帳の科目名(F列)を指定し、第2引数は精算表の科目名(A列)を指定(これが集計のキーになります)、第3引数は精算表の借方を指定すれば、A列で指定した科目について精算表から借方合計を計算してくれます。

このような仕組みを整えれば会計システムを導入せず、Excelだけで経理ができるかもしれません。
最近はフリーソフトや安価なクラウド型のシステムがあるので敢えてExcelでチャレンジする人はいないと思いますが・・・。

しかしながら、連結決算となると話は別で、このようにSUMIF関数を駆使して連結精算表を作っている会社がよくあります。
連結仕訳が多くなかったりシステムを導入するほど大きな規模ではなかったりするなど事情は様々ですが、Excelで連結決算を行うというのは比較的多いようです。

まとめ

Excelが標準で用意している関数はかなりの数がありますが、日常的に使うものとなるとこのページで取り上げたような10前後しかありません。
最初にも書いた通り、あまり知られていない関数を得意げに使ったとしても後で引継ぎを行う際に苦労するのは自分自身です。
基本的な関数を確実に使いこなせるようになれば十分なのですが、使いこなすまでにはそれなりの訓練が必要です。新入社員にはいろいろな資料を作成する機会を与え、関数を使っていかに効率的に目的のものを作れるか考えてもらうことが重要であると思います。