前任者から引き継いだExcelファイルに訳の分からない数式が入力されていて、修正することもできずに困ってしまうというのはよくある話です。インターネットの掲示板やSNSでは、解読できないくらいクソ長い数式が投稿されることがあります。
先日、Twitterで残念な症例が報告されました（詳しくは後述）。IF関数で条件分岐してからVLOOKUP関数を記述するのを繰り返すことによって、だらだらと長い数式になっているという事例です。数式を作った本人は、おそらく、数式を短くする方法をネット上で必死に探しても見つからず、やむを得ず長い数式を入力したものと思われます。
そこで、IF関数とVLOOKUP関数を繰り返して、異常に長い数式を記述する前に検討することは何かということについて述べたいと思います。
目次
- １．事件の概要
- ２．ここで問題です
- ３．問題の趣旨
- ４．あえて元のツイートの方法でやってみる
- ５．IF関数の中に他の関数を入れようとするな！
- ６．連番を等差数列に変換する
- ７．まとめ：この事件から学ぶこと
１．事件の概要
2020年8月5日ごろ、Twitter上に次のような画像が投稿されました（ツイートと画像の著作権は@ichi3270氏に属する）。
職場でやばめな数式見つけた pic.twitter.com/mnFNm0a1KZ
— ichi3270 (@ichi3270) 2020年8月5日
この数式はよく見ると不要なカッコが無駄にコピーされていることが分かります。
おそらく、次のIF・AND・VLOOKUPの組み合わせを25回コピーし、さらにIF関数の入れ子にして作ったのではないかと思われます。数式を作った人の苦労がうかがえます。なぜこのような数式になってしまったのでしょうか？
- =IF(AND(E2="金",F2=1),VLOOKUP(K2,金曜日!F:G,2,FALSE),・・・
２．ここで問題です
上記ツイートは元データが公開されていない（身バレするので公開しないほうが良いかも・・・）ので、当サイトで改題を作成しました。当サイトで勝手に再現したものですので、実際のデータとは異なります。ご了承ください。
問題
あるExcelファイルにはある大学における講義科目が一覧で入力されていて、シート「講義科目一覧」のほか、月曜日～金曜日のシートがある。
月曜日～金曜日のシートには、それぞれその曜日に開講される講義の、前期後期の別、科目名称、講義担当予定者の氏名、時限、教室番号が入力されている。各講義は「前期後期＋科目名称＋担当予定者」の組み合わせで一意に決まるものとする。
シート「講義科目一覧」にはすべての曜日の講義が入力されている。「前期後期＋科目名称＋担当予定者＋曜日＋時限」の組み合わせで、教室番号を求めなさい。
３．問題の趣旨
（１）なぜシートが分かれているのか
大学や専門学校の講義は教室の数が多いだけでなく、同じ授業科目なのに複数の講師が担当したり、前期と後期で同じ授業科目を開講したりと、なにかとややこしいものです。
大量のデータがあったとしても、本来なら曜日を分けずに1つのシートに一覧表を入力すれば済むのですが、各曜日ごとで5人で分担して入力する（または確認する）場合はシートを分けざるを得ないのです。
複数の事業所のある会社でも、それぞれの事業所で入力して、本部で一本化することがあります。このように、分かれているシートのデータを一本化する作業は、業務ではよくあることです。
（２）データの作成方法の問題か
上記のツイートでは、科目名称等を連結した日本語の文字列をキー（検索値）として、教室番号を検索しています。しかし、本来ならすべての科目に対して、半角英数字の科目コードを割り当てて、それを元に表を作成するべきであり、日本語の文字列をキーとするのはあまり良い方法とは言えません。
４．あえて元のツイートの方法でやってみる
ここでは、説明の便宜上、月曜日～金曜日のそれぞれのシートについて、前期後期・科目名称・担当予定者の３つを連結した文字列を、1限～5限の列に分けて出力したと仮定します（下の数式をF:O列にコピーする）。
- セルF2：=IF($B2=F$1,$A2&$C2&$D2,"")
- セルG2：=IF(F2="","",$E2)
また、シート「講義科目一覧」については、K列に前期後期・科目名称・担当予定者の３つを連結した文字列を表示します。
- セルK2：=H2&B2&I2
これによって、検索値をセルK2として検索すれば良いということになります。
- =VLOOKUP(K2,各曜日の一覧,教室番号の列,0)
曜日と時限も条件としなければなりません。例えば、木曜の1限であれば、IF関数の論理式は「AND(E2="木",F2=1)」となります。木曜1限のデータは、シート「木曜日」のF:G列にあり、その2列目が教室番号です。したがって、VLOOKUP関数を用いて次のような式となります。
- IF(AND(E2="木",F2=1),VLOOKUP(K2,木曜日!F:G,2,FALSE),～)
曜日が月～金の5通り、時限が１限～５限の5通りで全部で5x5＝25通りの場合分けとなるため、IF関数を25個入れ子にすればよいということになります。
・・・というわけで、元のツイートにあるようなクソ長い数式になるわけです。ちなみに、平日の１限～５限でない講義（集中講義など）は空白を返していますが、授業科目が見つからなかったらエラーになるようです。
５．IF関数の中に他の関数を入れようとするな！
（１）VLOOKUP関数の中にIF関数を入れる
25通りのVLOOKUP関数をよく見ると、検索値K2と列数2とFALSEは同じで、検索範囲だけが異なっていることが分かります。
このように、VLOOKUP関数の4つの引数のうち、１つだけが異なる場合は、「IF関数の中にVLOOKUP関数を入れて分岐する」のではなく、「VLOOKUP関数の中にIF関数を入れる」べきです。
つまり、「=VLOOKUP(K2,範囲,2,FALSE)」を先に記述して、「範囲」の部分にIF関数を入れるのです。そうすれば、共通部分であるVLOOKUP関数を25回繰り返す必要が無くなります。
このことはVLOOKUPに限ったことではありません。いっぱんにIF関数で分岐してから他の関数を入れる場合で、1つの引数以外が共通している場合は、IF関数を外ではなく中に入れると数式が短くなります。
（２）範囲を返すにはINDIRECT関数が必要
IF関数でセル範囲を返したい場合はINDIRECT関数が必要です。INDIRECT関数の引数は文字列なのでダブルクォーテーションが必要です（参考：「INDIRECT関数」完全理解！別シートやセルを参照するメリットと使い方【Excel】）。
- IF(AND(E2="月",F2=1),INDIRECT("月曜日!F:G"),～)
さらに、INDIRECT関数を使うと、E列の曜日を連結することができます。
- IF(F2=1,INDIRECT(E2&"曜日!F:G"),～)
これによって、AND関数が不要となり、25通りの場合分けが5通りに減ります。
（３）INDIRECT関数の中にIF関数を入れる
5通りのINDIRECT関数をよく見ると、シート名は同じで、列番号だけが異なっていることが分かります。
INDIRECT関数の一部分だけが異なる場合は、「IF関数の中にINDIRECT関数を入れる」のではなく、「INDIRECT関数の中にIF関数を入れる」べきです。
つまり、「=INDIRECT(E2&"曜日!"&列番号)」を先に記述して、「列番号」の部分にIF関数を入れるのです。そうすれば、共通部分であるINDIRECT関数を5回繰り返す必要が無くなります。
６．連番を等差数列に変換する
IF関数の部分だけを取り出すと次のような式になります。
- IF(F2=1,"F:G",IF(F2=2,"H:I",IF(F2=3,"J:K",IF(F2=4,"L:M",IF(F2=5,"N:O","")))))
セルF2が１～５の連番であり、これを別の文字列（列番号）に変換しています。
この数式を簡単にする方法は複数あります。どの方法も大事なので習得しておいたほうが良いでしょう。
（１）CHOOSE関数
１～５の連番を別の文字列に変換する最も簡単な方法はCHOOSE関数です。
- CHOOSE(F2,"F:G","H:I","J:K","L:M","N:O")
最終的な数式は次のようになります。
- =VLOOKUP(K2,INDIRECT(E2&"曜日!"&CHOOSE(F2,"F:G","H:I","J:K","L:M","N:O")),2,FALSE)
（２）CHAR関数
アルファベットのF、H、J、L、Nは1つおきになっています。Shift_JISのコード番号（10進）はそれぞれ70、72、74、76、78です（参考：Excelで連続するアルファベットやひらがな50音を入力する方法【オートフィル、CHAR関数、CODE関数】）。連番であるF2を2倍すると2、4、6、8、10なので、68を足せばよいことが分かります。
したがって、CHAR関数を用いて、列番号を「CHAR(F2*2+68)&":"CHAR(F2*2+69)」と表すことができます。
最終的な数式は次のようになります。
- =VLOOKUP(K2,INDIRECT(E2&"曜日!"&CHAR(F2*2+68)&":"CHAR(F2*2+69)),2,FALSE)
（３）OFFSET関数
"F:G","H:I","J:K","L:M","N:O"は、"D:E"列をそれぞれ2、4、6、8、10列だけ右にシフトした列番号です。2、4、6、8、10は、連番であるF2を2倍した値です。
- OFFSET(INDIRECT(E2&"曜日!D:E"),0,F2*2)
最終的な数式は次のようになります。
- =VLOOKUP(K2,OFFSET(INDIRECT(E2&"曜日!D:E"),0,F2*2),2,FALSE)
ちなみに、VLOOKUP関数の第4引数は１と０で指定することも可能なので、FALSE＝０とすることができます。
- =VLOOKUP(K2,OFFSET(INDIRECT(E2&"曜日!D:E"),0,F2*2),2,0)
VLOOKUP関数の検索でエラーになったら空白を返します。これが最初の問題の答えとなります。
- =IFERROR(VLOOKUP(K2,OFFSET(INDIRECT(E2&"曜日!D:E"),0,F2*2),2,0),"")
７．まとめ：この事件から学ぶこと
IF関数の入れ子を何回も使って、VLOOKUP関数を繰り返す場合、VLOOKUP関数の繰り返しの部分があるかを考えます。４つの引数のうち1つだけが変化している場合は、VLOOKUP関数の中にIF関数を入れます。IF関数と他の関数を組み合わせるときは、その入れ子の順序を変えるだけで数式がシンプルにある場合があります。
また、連番を別の文字列に変換するのにIF関数は不要です。CHOOSE関数を使えばよいです。さらに、等差数列（今回の場合は公差＝２）に変換する場合は計算をすれば済みますので、CHAR関数やOFFSET関数が使えます。
解説は以上です。
