Excelでいろいろな計算をさせていると、中には長くて複雑な数式が出てくることがあります。
特に、先輩社員や同僚から引き継いだ Excelのシートにそのような数式が出てくると、どういう計算をさせているのかスッと入ってこないものです。
例えば、下図のような表データがあるとき、D列「総合評価」は B列「上期」の値と C列「下期」の値に基づいて「◎」「○」「(表記なし)」とするようになっています。
D3セルには次のように入力されていました：
=IF(AND(IF(B3>=1000,"A",IF(B3>=800,"B","C"))="A",IF(C3>=1000,"A",IF(C3>=800,"B","C"))="A"),"◎",IF(OR(IF(B3>=1000,"A",IF(B3>=800,"B","C"))="A",IF(C3>=1000,"A",IF(C3>=800,"B","C"))="A"),"○",""))
この数式を、先頭から読んでいってその構成や意味合いを理解していくのは、まだ Excelを扱い始めた方には難しいかもしれません。
実は、この「総合評価」列では、「上期」も「下期」も 1,000以上なら「◎」、「上期」「下期」いずれかが 1,000以上なら「○」、それ以外は「(表記なし)」とするだけのもので、
=IF(AND(B3>=1000,C3>=1000),"◎",IF(OR(B3>=1000,C3>=1000),"○",""))
とすればよいものです。
ところが、何人かの先輩を経て引き継がれていくうちにこのように冗長な数式になってしまったようです。
それでは、最初に示した数式を分解しながら解釈してみましょう。
「総合評価」列の左に 3列挿入し、D2セルに「合計」、D3セルに「=B3+C3」と入力、D3セルの数式を最下行までコピーします。（下図）
この「合計」列は今回の「総合評価」を求めるためには不要なのですが、将来的な評価ルールの変更に備えて作っておきます。
次に、数式の最初のほうに出てくる次の数式を評価してみます：
=IF(B3>=1000,"A",IF(B3>=800,"B","C"))
これは B3セル「上期」の値が 1,000以上なら「A」、800以上なら「B」、それ以外なら「C」と評価を与えるものです。
これを E列「上期評価」に入力してみます。（下図）
同様に、数式のその次に出てくる次の数式を評価してみます：
=IF(C3>=1000,"A",IF(C3>=800,"B","C"))
これは C3セル「下期」の値が 1,000以上なら「A」、800以上なら「B」、それ以外なら「C」と評価を与えるものです。
これを F列「下期評価」に入力してみます。（下図）
この「上期評価」「下期評価」の値を使って「総合評価」の数式を書き直してみます：
=IF(AND(E3="A",F3="A"),"◎",IF(OR(E3="A",F3="A"),"○",""))
これは E3セル「上期評価」と F3セル「下期評価」が両方とも「A」なら「◎」、いずれかが「A」なら「○」、それ以外なら「(表記なし)」と評価を与えるものです。
これを G列「総合評価」に入力してみます。（下図）
さらに細かく分解することもできますが、今回はここまでとします。
いかがでしょう。
数式を分解して、途中で得られた値を中間に挟むことで、長く読みづらかった数式も分かりやすくなります。
そして、数式において利用していない部分、この例では「800以上であるか」を排除できることも分かってきます。
（将来、利用するかもしれなければ、排除しなくてもよろしいですが‥‥。）
最終的に、見せておく必要がなければ「合計」「上期評価」「下期評価」の列は非表示としておけばよろしいですね。（下図）
関数の中に関数が含まれる“ネスト”または“入れ子”と呼ばれる数式が出てきて、それが難解であれば、このように個々の部分に分解してその構成を分かりやすいものにしてみるとよろしいでしょう。
換言すれば、計算過程が複雑になりそうな数式でも、途中の小さなステップを作り、それを積み上げて最終的な数式とすることで容易に振り返ることができます。
これは、1週間後の自分、そして将来の後輩のためにとても大事なことでもあります。
この小さなステップで使用した中間的なセルを「作業セル」とか「計算セル」と呼んでいます。
これは一種の「プログラム的思考」と呼べるものです。
他人が作ったプログラムは一般に読みにくいものですが、そのソースコードを 1行ずつ解釈していけば全体で何をしようとしているか分かります。