わえなび ワード&エクセル問題集

当サイトはWordとExcelの実力を鍛える最高水準の特訓問題集です。リンクを張るのは自由です。みんなで勉強しましょう!

IF関数の入れ子でVLOOKUPを何回も繰り返す数式は、順序を逆にすることで短くすることができる【Excel】

前任者から引き継いだExcelファイルに訳の分からない数式が入力されていて、修正することもできずに困ってしまうというのはよくある話です。インターネットの掲示板やSNSでは、解読できないくらいクソ長い数式が投稿されることがあります。

先日、Twitterで残念な症例が報告されました(詳しくは後述)。IF関数で条件分岐してからVLOOKUP関数を記述するのを繰り返すことによって、だらだらと長い数式になっているという事例です。数式を作った本人は、おそらく、数式を短くする方法をネット上で必死に探しても見つからず、やむを得ず長い数式を入力したものと思われます。

そこで、IF関数とVLOOKUP関数を繰り返して、異常に長い数式を記述する前に検討することは何かということについて述べたいと思います。

目次

1.事件の概要

2020年8月5日ごろ、Twitter上に次のような画像が投稿されました(ツイートと画像の著作権は@ichi3270氏に属する)。

 

この数式はよく見ると不要なカッコが無駄にコピーされていることが分かります。

f:id:waenavi:20201010003242j:plain

 

おそらく、次のIF・AND・VLOOKUPの組み合わせを25回コピーし、さらにIF関数の入れ子にして作ったのではないかと思われます。数式を作った人の苦労がうかがえます。なぜこのような数式になってしまったのでしょうか?

  • =IF(AND(E2="金",F2=1),VLOOKUP(K2,金曜日!F:G,2,FALSE),・・・

f:id:waenavi:20201016183518j:plain

 

2.ここで問題です

上記ツイートは元データが公開されていない(身バレするので公開しないほうが良いかも・・・)ので、当サイトで改題を作成しました。当サイトで勝手に再現したものですので、実際のデータとは異なります。ご了承ください。 

問題

あるExcelファイルにはある大学における講義科目が一覧で入力されていて、シート「講義科目一覧」のほか、月曜日~金曜日のシートがある。

f:id:waenavi:20201016183843j:plain

 

月曜日~金曜日のシートには、それぞれその曜日に開講される講義の、前期後期の別、科目名称、講義担当予定者の氏名、時限、教室番号が入力されている。各講義は「前期後期+科目名称+担当予定者」の組み合わせで一意に決まるものとする。

f:id:waenavi:20201016184046j:plain

 

シート「講義科目一覧」にはすべての曜日の講義が入力されている。「前期後期+科目名称+担当予定者+曜日+時限」の組み合わせで、教室番号を求めなさい。

f:id:waenavi:20201016184227j:plain

 

3.問題の趣旨

(1)なぜシートが分かれているのか

大学や専門学校の講義は教室の数が多いだけでなく、同じ授業科目なのに複数の講師が担当したり、前期と後期で同じ授業科目を開講したりと、なにかとややこしいものです。

f:id:waenavi:20201016184227j:plain

 

大量のデータがあったとしても、本来なら曜日を分けずに1つのシートに一覧表を入力すれば済むのですが、各曜日ごとで5人で分担して入力する(または確認する)場合はシートを分けざるを得ないのです。

複数の事業所のある会社でも、それぞれの事業所で入力して、本部で一本化することがあります。このように、分かれているシートのデータを一本化する作業は、業務ではよくあることです。

(2)データの作成方法の問題か

上記のツイートでは、科目名称等を連結した日本語の文字列をキー(検索値)として、教室番号を検索しています。しかし、本来ならすべての科目に対して、半角英数字の科目コードを割り当てて、それを元に表を作成するべきであり、日本語の文字列をキーとするのはあまり良い方法とは言えません。

 

4.あえて元のツイートの方法でやってみる

ここでは、説明の便宜上、月曜日~金曜日のそれぞれのシートについて、前期後期・科目名称・担当予定者の3つを連結した文字列を、1限~5限の列に分けて出力したと仮定します(下の数式をF:O列にコピーする)。

  • セルF2:=IF($B2=F$1,$A2&$C2&$D2,"")
  • セルG2:=IF(F2="","",$E2)

f:id:waenavi:20201016191713j:plain

 

また、シート「講義科目一覧」については、K列に前期後期・科目名称・担当予定者の3つを連結した文字列を表示します。

  • セルK2:=H2&B2&I2

f:id:waenavi:20201016191848j:plain

 

これによって、検索値をセル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),~)

f:id:waenavi:20201016200145j:plain

 

曜日が月~金の5通り、時限が1限~5限の5通りで全部で5x5=25通りの場合分けとなるため、IF関数を25個入れ子にすればよいということになります。

f:id:waenavi:20201016200253j:plain

 

・・・というわけで、元のツイートにあるようなクソ長い数式になるわけです。ちなみに、平日の1限~5限でない講義(集中講義など)は空白を返していますが、授業科目が見つからなかったらエラーになるようです。

 

5.IF関数の中に他の関数を入れようとするな!

(1)VLOOKUP関数の中にIF関数を入れる

25通りのVLOOKUP関数をよく見ると、検索値K2と列数2とFALSEは同じで、検索範囲だけが異なっていることが分かります。

f:id:waenavi:20201016200634j:plain

 

このように、VLOOKUP関数の4つの引数のうち、1つだけが異なる場合は、「IF関数の中にVLOOKUP関数を入れて分岐する」のではなく、「VLOOKUP関数の中にIF関数を入れる」べきです。

f:id:waenavi:20201016201430j:plain

 

つまり、「=VLOOKUP(K2,範囲,2,FALSE)」を先に記述して、「範囲」の部分にIF関数を入れるのです。そうすれば、共通部分であるVLOOKUP関数を25回繰り返す必要が無くなります。

f:id:waenavi:20201016201654j:plain

 

このことはVLOOKUPに限ったことではありません。いっぱんにIF関数で分岐してから他の関数を入れる場合で、1つの引数以外が共通している場合は、IF関数を外ではなく中に入れると数式が短くなります。

f:id:waenavi:20201016202117j:plain

 

(2)範囲を返すにはINDIRECT関数が必要

IF関数でセル範囲を返したい場合はINDIRECT関数が必要です。INDIRECT関数の引数は文字列なのでダブルクォーテーションが必要です(参考:「INDIRECT関数」完全理解!別シートやセルを参照するメリットと使い方【Excel】)。

  • IF(AND(E2="月",F2=1),INDIRECT("月曜日!F:G"),~)

さらに、INDIRECT関数を使うと、E列の曜日を連結することができます。

  • IF(F2=1,INDIRECT(E2&"曜日!F:G"),~)

f:id:waenavi:20201016202401j:plain

 

これによって、AND関数が不要となり、25通りの場合分けが5通りに減ります。

f:id:waenavi:20201016202459j:plain

 

(3)INDIRECT関数の中にIF関数を入れる

5通りのINDIRECT関数をよく見ると、シート名は同じで、列番号だけが異なっていることが分かります。

f:id:waenavi:20201016202634j:plain

 

INDIRECT関数の一部分だけが異なる場合は、「IF関数の中にINDIRECT関数を入れる」のではなく、「INDIRECT関数の中にIF関数を入れる」べきです。

f:id:waenavi:20201016204427j:plain

 

つまり、「=INDIRECT(E2&"曜日!"&列番号)」を先に記述して、「列番号」の部分にIF関数を入れるのです。そうすれば、共通部分であるINDIRECT関数を5回繰り返す必要が無くなります。

f:id:waenavi:20201016202837j:plain

 

6.連番を等差数列に変換する

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が1~5の連番であり、これを別の文字列(列番号)に変換しています。

f:id:waenavi:20201016203001j:plain

 

この数式を簡単にする方法は複数あります。どの方法も大事なので習得しておいたほうが良いでしょう。 

(1)CHOOSE関数

1~5の連番を別の文字列に変換する最も簡単な方法は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)

 

(2)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を足せばよいことが分かります。

f:id:waenavi:20201016205222j:plain

 

したがって、CHAR関数を用いて、列番号を「CHAR(F2*2+68)&":"CHAR(F2*2+69)」と表すことができます。

f:id:waenavi:20201016204814j:plain

 

最終的な数式は次のようになります。

  • =VLOOKUP(K2,INDIRECT(E2&"曜日!"&CHAR(F2*2+68)&":"CHAR(F2*2+69)),2,FALSE)

 

(3)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)

f:id:waenavi:20201016205712j:plain

 

最終的な数式は次のようになります。

  • =VLOOKUP(K2,OFFSET(INDIRECT(E2&"曜日!D:E"),0,F2*2),2,FALSE)

ちなみに、VLOOKUP関数の第4引数は1と0で指定することも可能なので、FALSE=0とすることができます。

  • =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),"")

 

7.まとめ:この事件から学ぶこと

IF関数の入れ子を何回も使って、VLOOKUP関数を繰り返す場合、VLOOKUP関数の繰り返しの部分があるかを考えます。4つの引数のうち1つだけが変化している場合は、VLOOKUP関数の中にIF関数を入れます。IF関数と他の関数を組み合わせるときは、その入れ子の順序を変えるだけで数式がシンプルにある場合があります。

また、連番を別の文字列に変換するのにIF関数は不要です。CHOOSE関数を使えばよいです。さらに、等差数列(今回の場合は公差=2)に変換する場合は計算をすれば済みますので、CHAR関数やOFFSET関数が使えます。

 


解説は以上です。


 


Copyright(C)2018-2020 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]
Youtube | 公式サイト | twitter | 著作権 | 運営者情報 | お問い合わせ