2019/12/18

IF文を重ねて書く




0.初めに

ワークシート関数のIFを使って分類を分かり易く表示するような場面は、Excelでは多くあります。
ただしIF文は条件式に合っている(True)か、違っている(False)かの2通りしかなく、3通り以上の表示をしたい場合は、IF文を重ねて(ネストと言います)書かなければなりません。
しかし、IF文の重ね書きは非常に解読しにくく、多くの人がExcelを苦手になるきっかけになっているようです。

今回、その苦手を少しでも解消できるように、IF文の重ね書きについて説明します。

1.例:成績の評価表

点数によって成績をつける際は、図1のような表で整理する事が良くあります。この図でいえばD列にIF文を使った数式を入れて◎〇×をつけたりします。また、この数式で表示させた◎〇×の個数を数えたりすることもあります。

図1

このD列(D5~D12セル)の数式にはIF文が2つ重なっていて、苦手な人には頭が混乱することでしょう。ここでは数式を見ずに、上(D2セル)に書いてある凡例に注目してみます。

・80点以上      = ◎
・80点未満~60点以上 = 〇
・60点未満      = ×

となっています。この中で、◎〇×の印を区分けている数字(閾値(しきいち))は「80点」と「60点」です。

図2

この2つの閾値をつかってIF文を2つ考えてみましょう。1つ目は「80点以上か」のIF文。もう1つは「60点未満か」のIF文です。
IF文には、まず「条件式」があり、その条件に「当てはまる(Yes)」か、「当てはまらない(No)」かを数式として表します。

IF(条件式, 当てはまる場合, 当てはまらない場合)

図で表現すると図3の様な感じです。

図3

図3もなじみが無い方には、条件によって掬い取ったり流したりするフルイ(篩)に例えると分かり易いかもしれません。
図4

これを数式にしてセルに埋め込んだのが図5になります。2つIF文がありますから、列も分けて記入します。
D5,E5に書かれている数式は赤い吹き出しの通りで、「80点以上か」「60点未満か」で記号を分けて表示させています。

図5

D列・E列の「◎〇×の結果」は、完全に合ってはいないけど間違ってはいませんよね。1つのIF文では2通りの答え(Yes か No)しか出せませんので、3通り(◎〇× )を表すには、IFを重ねていくしかありません。
なお、IF文を2つ重ねると3種類、3つ重ねると4種類、n個重ねるとn+1種類の分類をすることができます。

IF文は「フルイ(篩)で条件に合うものを拾ってYesの行動を、条件に合わないものはフルイから抜けてNoの行動を取る」という仕事をします。そしてIF文を重ねている時は、左の方から(=に近い方から)流れていきます

まず「どこに重ねるか」ですが、 ですので、80点側のIF文を先にもって来たとすると、Yesで掬えたデータは80点以上の◎で決定済みのため、まだ決定しきれていないNo側に60点側のIF文をくっつければ良さそうです。フルイ図で書くと図6の通りです。

図6

これを数式としてくっつけると、図7になります。

図7

そして、図6で分かる通り、「80点以上」「60点未満」という二つのフルイをすり抜けたのは「80点未満~60点以上」という〇印のデータですので、図6の「?」の部分には「"〇"」を割り当ててあげます。
尚、くっつける時は、数式の先頭の「このセルは数式ですよ記号(=)」は消します。

図8

以上で、3通りの印をつけるIF文の数式が完成しました。

図1

このようにIFの重なった数式は、左から読んでいって「IFが出てくるたびにフルイでデータを掬っている」と考えればイメージが沸くのではないでしょうか。

なお、今回は「◎ → × → 〇」の順にフルイですくっていったのですが、色々な順番が考えられます。数値により3種に分類するのですから、どの式でも同じ結果は得られますが、「端から決定したものを除いていく」のが内部の数式が難しくならずミスも少ないと思います

今回 「◎ → × → 〇」 ⇒ = IF ( C5 >= 80 , "◎" , IF ( C5 < 60 ,"×" , "〇") )
2. 「◎ → 〇 → ×」 ⇒ = IF ( C5 >= 80 , "◎" , IF ( C5 >= 60 ,"〇" , "×") )          ・・・・おすすめ
3. 「〇 → ◎ → ×」 ⇒ = IF ( AND(C5 < 80 , C5 >= 60 ) , "〇" , IF ( C5 >= 80 ,"◎" , "×") )
4. 「〇 → × → ◎」 ⇒ = IF ( AND(C5 < 80 , C5 >= 60 ) , "〇" , IF ( C5 < 60 ,"×" , "◎") )
5. 「× → 〇 → ◎」 ⇒ = IF ( C5 < 60 , "×" , IF ( C5 < 80 ,"〇" , "◎") )          ・・・・おすすめ
5. 「× → ◎ → 〇」 ⇒ = IF ( C5 < 60 , "×" , IF ( C5 >= 80 ,"◎" , "〇") )


2.例:カレンダーの1行目

Excelのワークシートで作るカレンダーがあります。B2セルとC2セル(青いセル)に年月を入力すると、カレンダーが自動的に変わるものです。(図9)
自動的に変化するのは、入力した年月を元に「その月の初日」「初日の曜日」「その月の最終日」をワークシート関数で計算し、その結果を使ってカレンダーのマス目に数字を埋めているからです。
カレンダー(it-012.xlsx)
図9

このカレンダーの1週目(Excelでは8行目)の数字は、どうやって埋めているのでしょうか。実はこの部分も、今回のテーマ「IF文の重ね書き」で作っているのです。

2-1.初日を埋める

まず、曜日の上側(6行目)に1~7の数字が記入してありますが、これは初日の曜日計算(F3セル)で使用した「WEEKDAY関数」から出てくる曜日の代替の数値と合わせています。
また初日が配置されるのは、第1週目のみです。2週目に「1日」が来ることは決してありません。ですから初日の曜日を探して配置するのは1週目の8行目のみで良いことになります。

手始めに、初日の「1」をカレンダーに書いてみましょう。「$F$3」が初日の曜日、6行目が曜日代替数値ですので。曜日の数値と合った列のセルに「1」を配置すれば良い事になります。
計算式の中で「B$6」と行固定してありますが、複数行へのコピーではないので「B6」でも大丈夫です。但し行方向・列方向に式をコピーする際は、行固定・列固定を意識する方がエラーが出にくいと思います。

これを数式「 =IF($F$3=B$6,1,"") 」に表し8行目のセルに記入すると図10の様になります。

図10

初日である「1」が希望の曜日に入りました。その他の曜日はカラです。計算式は「曜日が合ったところに1を入れる。合わなかったらカラにする」となっているので、その通りの結果となりました。

2-2.初日以外を埋める

次は初日(1日)曜日以外の曜日を考えます。曜日との関連をもう一度見てみましょう

図11

1日より前は「初日の曜日より小さい」。 1日より後は「初日の曜日より大きい」ことが分かります。これは「1.成績の評価表」でやったフルイと同じですね。

図12

ですので、1週目に入れる計算式は、以下の式になります(B8セルの計算式です。隣接セルは相対的に式が変わります)。

   = IF ( $F$3 > B$6 , "" , IF ( $F$3 = B$6 , 1 , A8 + 1 ) )

3.例:カレンダーの最終行目

カレンダーの1週目は上記の通りですし、また1か月は最少でも28日ありますので、4週目まで(7日/週 x 4週 = 28日)は必ず数字が入っています。 ですので、月の最終日は「5週目または6週目」ということになります。
では、5週目6週目に入る計算式は、どう考えれば良いのでしょう。

いままでであれば「ある条件に合ったら、そのセルに1をつける」等というフルイを使うものでした。例えば、月の初日の曜日と日数はわかっているのですから、「何週目の何曜日に最終日が来る」というのは計算すれば出るはずです。
しかし方法はそれ以外にもあるのです。「前日が最終日でなければ、+1日する。前日が最終日だったら空欄にする」というような、少しアナログ的な方法です。つまり今いるセルの計算結果が、次のセルの判断に使われるのです。今回はこの方法にトライしましょう。

「アナログ的」といってもコンピュータですからデジタルなので、どういう時に最終日になるか空欄になるか、などを考えます。

・前日が、最終日より小さい → 前日に+1日する
・    最終日と同じ   → 空欄にする
・    空欄       → 空欄にする

カレンダーの流れを見ながら考えると、こんな感じです。これを例のフルイで考えましょう。


図13

前日の値を条件のフルイにかけ、その値が次セルの条件フルイにかけられる、というところは合っています。しかしこの図13は間違っているのです。
最初のフルイの分で言えば、条件式は「IF(前日の値 < 最終日)」となりそうです。前日値が数字であれば問題ないのですが、「空欄」が来てしまうとどうなるでしょうか。「空欄は文字列」なので「文字列と数字の大きさを比較」する事となってしまい、「本来比べられないものを比べている」ミスです。

実をいうと、この図13の式でもエラーにはならず「正しそうに見えるカレンダー」にはなります。それどころか、1つ目のフルイだけの式でも「正しそうに見える・・」になります(2つ目のフルイは何が来ても空欄なのですから、不要ですよね)。

しかし、図13の計算式が間違っている証拠として、式の中の「空欄("")」を「0(ゼロ)」に変えてみたのが図14です。

図14

どうでしょう、本来ゼロ(=空欄)が並んでいるはずの部分が、1・2・3・・と入ってしまいます。比較の式が間違っている証拠で、ぞっとします。

では、どうすれば良いかというと、一番目のフルイで空欄を取り除いてしまうのです。フルイから外れたものは文字列ではなく数字のみですので、その数字を最終日と比較すれば問題ないのです。フルイ図で書けば図15です。


図15

これをB12セルの数式で表すと「 =IF( H11 = "" , "" , IF( H11 = $F$4 , "" , H11 + 1 ) ) 」となり、最終日周辺が決定します。

図16

もちろん、空欄ではなく数値のゼロを入れるようにして、シート全体で「ゼロを表示しない」に設定すれば、もっとすっきりとするかもしれません。
カレンダー(it-012.xlsx)