会計士が実践する、Excelの数式を複雑にしない3つの下ごしらえ

気付くとExcelの数式が複雑になってしまっている。
かっこいい関数を使っているはずなのに、あとから見返すと自分でも読みにくい。

そんなことはないでしょうか？

今回は、Excelの数式をシンプルに保つための3つの考え方について書いてみます。

なぜシンプルにしたいのか？

Excelの数式が複雑だと、以下のようなデメリットが大きくなります。

• ブラックボックス化してしまう
• 難しい数式を多用するハメになる
• 他の人が使う時に理解できない

ひとことで言えば、
「美しくない」ということです。

仕事において「美しいかどうか」は大切だと思います。
もちろんその背後には泥臭さなどがあってもいいし、あるべきだとは思いますが、最終的には美しく整えたいもの。

「シンプルさ＝美しさ」と考えてみてもいいでしょう。

• 美しいとは、シンプルであること
• シンプルであることは、分かりやすく使い勝手がいいということ。
• 分かりやすく使い勝手がいいということは、お客様にとってメリットがあるということ。

こんな感じで考えてみるのも、面白いかもしれません。

コツ3選

さて、本題に進みたいと思います。

実践としては、次の3つを押さえておくと、数式はかなり読みやすくなります。

1. 1つのセルに詰め込まない
2. 条件は数式の外に出す
3. 複雑な関数で一発解決しようとしない

①1つのセルに詰め込まない（列を分ける）

例えば、請求書の一覧があり、
請求日と入金日をもとに、入金状況を判定したいケースを考えてみます。

前提状況としては以下を想定します。

• 入金期限は、請求日の翌月末
• 入金日が空欄なら「未入金」
• 入金日が期限内なら「入金済」
• 入金日が期限後なら「期限後入金」

上記の表をもとに「判定」列を考えるのであれば、こんな数式になるかなと。

=IF(D2="","未入金",IF(D2<=EOMONTH(A2,1),"入金済","期限後入金"))

このくらいであれば、まだ読めます。
ただ、この1つのセルの中に、

・入金日が空欄かどうか
・入金期限の計算
・期限内か期限後かの判定

がまとめて入っています。

ここに条件が1つ増えるだけで、すぐに読みづらくなります。

そこで考えたいのは「列を追加する」といったこと。

たとえば、上記の表に「入金期限」「遅延日数」といった列情報を足すだけで、グッとシンプルになります。

E列：入金期限

=EOMONTH(A2,1)

F列：遅延日数

=IF(D2="","",MAX(0,D2-E2))

このような情報を持たせておくと、G列：判定では以下の数式で表現できるようになります。

=IF(D2="","未入金",IF(F2=0,"入金済","期限後入金"))

ポイントは、

「判定結果」を出すセルに、日付計算まで全部入れない

ということです。

• 入金期限は入金期限の列で出す
• 遅延日数は遅延日数の列で出す
• 最後に判定だけする

こう分けると、数式が一気に読みやすくなります。

特に日付関係は、

• 月末
• 翌月末
• 年度
• 経過日数
• 期限超過
• 空欄判定

が絡むとすぐ複雑になります。

なので、日付こそ列を分けたほうがいいと思うのです。

② 条件は数式の外に出す（マスタを作る）

例えば、銀行明細のデータがあり、摘要を見て勘定科目を自動判定したいケースを考えてみます。

摘要（B列）をもとに勘定科目（D列）を入れたいとします。

もし1つの数式で表現しようとすると、こんな感じになります。

=IFERROR(IF(ISNUMBER(SEARCH("Amazon",B2)),"消耗品費",IF(ISNUMBER(SEARCH("Google",B2)),"通信費",IF(ISNUMBER(SEARCH("JR",B2)),"旅費交通費",IF(ISNUMBER(SEARCH("freee",B2)),"支払手数料",IF(C2<0,"売上返金","要確認"))))),"要確認")

これ、動くには動きます。
が、かなり読みにくいです。

やっていることは、

• 摘要に「Amazon」が含まれていたら「消耗品費」
• 「Google」が含まれていたら「通信費」
• 「JR」が含まれていたら「旅費交通費」
• 「freee」が含まれていたら「支払手数料」
• どれにも該当せず、金額がマイナスなら「売上返金」
• それ以外は「要確認」
• エラーが出ても「要確認」

という判定です。

ただ、条件が増えるたびに数式のメンテナンスが必要になりますし、それは現実的ではないでしょう。

やや極端な例ではありますが、1つの数式で判断しようとすると、こんな形になってしまいます。

そこで考えたいのは、別シートに「マスタ」を作ることです。

こんなイメージでマスタを別シートに持たせておきます。

このキーワードをキーにXLOOKUP関数で勘定科目を引っ張るような形式にすることで、数式がすっきりします。

=XLOOKUP(B2,マスタ!A:A,マスタ!B:B)

イメージはこんな感じです。

仮に1つのシートで表現すると、このような関係性になっています。

摘要と一致するキーワードを探し出し、その勘定科目を持ってくる。
そんなイメージです。

この数式で表現するいいところは、数式がシンプルになることに加えて、
「メンテナンスが楽になる」という点があります。

摘要に新しい項目が登場したとしても、
数式を変更する必要はなく「マスタ」シートを更新すれば済むようになるからです。

かなり単純化したケースではありますが、
根本の考え方が伝われば幸いです。

③複雑な関数で一発解決しようとしない

Excelには数多くの関数が用意されていますが、
細かいところまで把握できている人もいれば、そうでない人もいます。

基本的な関数だけ押さえている方が大多数かもしれません。

そんな中で、

• FILTER
• SORT
• UNIQUE
• LET
• LAMBDA
• MAP
• BYROW
• REDUCE

のように、かなり高度な処理ができる関数もあります。

これらを使えば、1つのセルで複雑な処理をまとめることもできます。

ただ、使っていない人も多い領域です。

FILTER、SORT、UNIQUEあたりは、処理内容も比較的イメージしやすい関数です。
一方で、LET、LAMBDA、MAP、BYROW、REDUCEあたりになると、かなり分かる人を選びます。

自分だけが使うExcelなら問題ありません。
ただ、お客様や未来の自分が見る資料であれば、少し慎重に使いたいところです。

• 四則演算
• IF
• SUM
• XLOOKUP

くらいでも工夫すれば十分なケースも多いです。

そして、こうした基本的な関数のほうが、高度な関数よりも分かりやすく、使い勝手も良いです。

「基礎的な関数で、いかに分かりやすく表現するか」
をぜひ考えていきましょう。

使う人のことを考える

「お客様」
「未来の自分」

が使うことを想定してExcelは整理していきたいもの。

「いまの自分」が使うだけならOKだけれども、
他の人が使おうとすると訳が分からないといった状況は最も避けたいところです。

誰が使うのか？

を常に頭において、資料は作っていきましょう。

まとめ

Excelの数式をシンプルにするには、「分けること」が最重要です。

「分ける」から「分かる」ようになる。

これはExcelでも同じ本質です。

「数式が複雑になり過ぎているかも…」と感じた方は、
まずは列を分けるところから始めてみてはいかがでしょうか。

では、また次回。

編集後記

◇日記
　昨日は初回面談と打ち合わせの2件。
　今週、検証を続けていた案件にいったん決着。大分前進しました。

◇ブログネタ経緯
　シンプルさとは、質の高さというネタ帳メモから浮かんだものを採用。

◇1日1新
　ナビゲーション