今回は、ExcelやスプレッドシートでIF関数が複数ある計算式を作りたい時のテクニックをお教えします。計算式を作る時は出来るだけシンプルにすることが理想ですが、やむを得ない事情で複雑な計算式を作らざるを得ないことがあります。しかし、Excelやスプレッドシートでは、IFの数が増えるとカンマや括弧の数が増えて訳が分からなくなることがあります。その限界は人によって違うと思いますが、おそらくIFが３つを超えると多くの人は思考がついていかなくなるのではないでしょうか。そんなときにこの方法をお試しください。
この方法では難しい関数は必要ありません。使う関数は次の３つです。
◆TRIM関数：余分な空白を削除する
◆SUBSTITUTE関数：特定の文字列を異なる文字列に置き換える
◆CONCAT関数：複数の文字列をつなげる
ただし、この方法は「構造化プログラミング」を用いることが前提です。今回お伝えする方法は、構造化プログラミングで作成した文字列の [IF] [ELSE] [ENDIF] をカンマや括弧に置き換えて、Excelやスプレッドシートの計算式の書式にするという方法です。
※ 構造化プログラミングについては、記事【複雑なことを整理する｜構造化プログラミング】をご覧ください。
以下、具体例を示します。顧客リストの情報を元に、おすすめ商品を決める式です。この時、おすすめ商品を決めるルールはNSチャートで示しています。
以下、顧客リストのセルG3に入力する式を作る手順を説明します。
以降①〜⑥は、顧客リストとは別のシートで行います。
①NSチャートの構造を、構造化プログラミングで記述します。
・構造を示す文字列は、[IF] [ELSE] [ENDIF] の３種です。
・[IF] の入れ子状態が深くなる場合は、階層に応じて字ずらしすると分かりやすくなります。
・[IF] に続く条件は、”(条件)” と記述します。
②余分な空白文字を削除します。これにより、字ずらしのために入力したスペースを消します。
セルC3の記述 ……『 =TRIM(B3) 』
③ [IF]に続く条件の、右括弧をカンマに変えます。
セルD3の記述 …… 『 =SUBSTITUTE(C3,”)”,”,”) 』
④ [ELSE] をカンマに変えます。
セルE4の記述 …… 『 =SUBSTITUTE(D3,”ELSE”,”,”) 』
⑤ [ENDIF] を右括弧に変えます。
セルF4の記述 …… 『 =SUBSTITUTE(E3,”ENDIF”,”)”) 』
⑥すべての行を結合して１つの文字列にします。
・セルB36の記述 …… 『 =CONCAT(F3:F31) 』
これで出来たものが求める計算式です。
これをクリップボードにコピーしてから値の貼り付けで文字列にします。
ここからは、顧客リストでの作業です。
⑦先ほど⑥で作成した文字列をセルG3に張り付け、先頭に “=” を加えます。
これで、顧客リストの最初の計算式が完成しました。
例の場合、計算式の結果として、おすすめ商品に “E” と表示されるはずです。
⑧完成した計算式を、下方へオートフィルしてリストのすべての行にコピーします。
これで完了です。
※ ②～⑤は一つの計算式にすることが可能です。計算式に慣れた方はチャレンジしてみてください。
また、計算式を使わずに「置換」機能で直接変えることもできます。やりやすい方法で行ってください。
※ ③の条件内で関数を使っていてIF以外の括弧がある場合は工夫が必要です。ご自分で考えてみてください。（例えば、構造化プログラミングの条件を丸括弧以外の文字にしておき、後で一括置換するなど）
以上、構造化プログラミングを用いて、複雑なIF構造を持った計算式を作成する方法でした。
お気づきの方もおられると思いますが、今回の構造化プログラミングは、Excelやスプレッドシートのマクロ（VBAやGAS）を作成する際のベースになります。今回はセルG3の値を求める計算式でしたが、マクロを使えば他のセルも自由に設定することができます。その際に、マクロの基本構造を考えるのが構造化プログラミングです。興味のある方はVBAやGASを勉強してみてください。
以前の記事【複雑なことを整理する｜構造化プログラミング】において、「構造化プログラミングは、多くのプログラミング言語の共通記法」と述べましたが、実は、Excelやスプレッドシートの計算式を考えることもプログラミングなのです。
コメント