ExcelのAND関数は条件をすべて満たすときに使い、OR関数は条件を1つ以上満たす場合に使い、いずれも論理値を返します(参考:【Excel】AND関数やOR関数を使うなら、まず「論理値を返す関数」であることを理解せよ)。
ところで、AND関数のANDとは論理演算の論理積のことであり、ORは論理和のことです。「論理積」「論理和」ということは、論理値(TRUE・FALSE)の掛け算や足し算が関係しているのは間違いないのですが、Excelを使って、論理値の掛け算や足し算をしたことがない人は多いと思います。
そこで、今回は、AND関数やOR関数の理解を深めるために、これらの関数が掛け算や足し算とどのように関係しているのかExcelを用いて解説します。
目次
1.論理積 AND
問題
セルA1に入力した値が、C列以上D列未満であるかをそれぞれ判定し、該当すれば「*」を表示しなさい。
解説
(1)比較演算の掛け算
「C列以上」「D列未満」をそれぞれ判定することは比較演算を使えば簡単にできます(参考:【Excel】真と偽の意味と比較演算のメリットを知らずしてIF関数を語ってはいけない)。A1は絶対参照です。TRUEであれば該当していることを表し、FALSEであれば該当しないことを表します。
- =$A$1>=C2
- =$A$1<D2
「C列以上D列未満」のように2つの値の間に入っているためには、「C列以上」「D列未満」の両方を満たす必要があります。つまり、両方TRUEであることが必要です。
ここで、2つの論理値(TRUE・FALSE)を掛け算します。論理値を掛け算するとTRUE=1、FALSE=0とみなされるため、どちらか一方がFALSEであれば0となり、両方がTRUEの場合だけ1となります。
- =E2*F2
逆に、IF関数の論理式では、1=TRUE、0=FALSEとみなされるため、この掛け算をそのまま論理式として利用することができます(参考:IF関数の条件に「等号や不等号が必要」と思っているなら、IF関数を勉強し直してほしい)。これで完成です。
- =IF(E2*F2,"*","")
比較演算を用いて記述すると次のようになります。
- =IF(($A$1>=C2)*($A$1<D2),"*","")
このように、論理値同士を掛け算することを「論理積」と言います。論理値の積だから論理積です。ここで、TRUEまたは1の個数を考えます。C列以上またはD列未満を判定した時よりも、論理積にしたほうが少なくなります。
論理積のほうが2つの条件をクリアしないといけないので、該当するものが少なくなるのは当たり前のことです。
(2)AND関数を用いる方法
2つの論理値を掛け算をする代わりに、AND関数の引数にしてみましょう。1と0ではなく、TRUEとFALSEになりますが掛け算をした場合と同じ結果になります。
- =AND(E2,F2)
AND関数は、論理積をTRUEとFALSEで返す関数です。条件をすべて満たす場合はTRUE、それ以外はFALSEとなります(参考:【Excel】AND関数やOR関数を使うなら、まず「論理値を返す関数」であることを理解せよ)。TRUEとFALSEで返す関数はそのままIF関数の論理式として利用することができます。
- =IF(AND(E2,F2),"*","")
比較演算を用いて記述すると次のようになります。
- =IF(AND($A$1>=C2,$A$1<D2),"*","")
論理値を掛け算して判定するのと、AND関数を用いて判定するのは全く同じです。このため、論理積のことを「AND条件」と言うこともあります。
2.論理和 OR
問題
英語と数学の得点のどちらかがセル範囲A3:B3の点数に達していなければ「不合格」と表示しなさい。
解説
(1)TRUEの個数を求める
「基準点未満」をそれぞれ判定します。A1は複合参照です(参考:【Excel複合参照】行固定や列固定のドルマークの意味と練習問題)。TRUEであれば基準点未満であることを表します。
- =D2<A$3
どちらか一方でも該当する、つまり1つ以上TRUEであることを判定します。2つともTRUEでも構いません。ここで、2つの論理値(TRUE・FALSE)を足し算します。論理値を足し算するとTRUE=1、FALSE=0とみなされるため、TRUEの個数を求めることができます。
- =F2+G2
逆に、IF関数の論理式では、1以上がTRUE、0だけがFALSEとみなされるため、この足し算をそのまま論理式として利用することができます。これで完成です。
- =IF(F2+G2,"不合格","")
比較演算を用いて記述すると次のようになります。A3、B3は絶対参照です。
- =IF((D2<$A$3)+(E2<$B$3),"不合格","")
このように、論理値同士を足し算することを「論理和」と言います。論理値の和だから論理和です。ここで、TRUEまたは1の個数を考えます。英語と数学をそれぞれ判定した時よりも、論理和にしたほうがTRUEの個数が多くなります。
論理和のほうが2つの条件のどちらか一方をクリアすればいいので、該当するものが多くなるのは当たり前のことです。
(2)OR関数を用いる方法
2つの論理値を足し算をする代わりに、OR関数の引数にしてみましょう。1と0ではなく、TRUEとFALSEになりますが足し算をした場合と同じ結果になります。OR関数は、論理和をTRUEとFALSEで返す関数です。条件を1つ以上満たす場合はTRUE、それ以外はFALSEとなります。
- =OR(F2,G2)
TRUEとFALSEで返す関数はそのままIF関数の論理式として利用することができます。
- =IF(OR(F2,G2),"不合格","")
比較演算を用いて記述すると次のようになります。
- =IF(OR(D2<$A$3,E2<$B$3),"不合格","")
論理値を足し算して判定するのと、OR関数を用いて判定するのは全く同じです。このため、論理和のことを「OR条件」と言うこともあります。
3.障害物で考えた場合のイメージ
AND条件(論理積)はすべての条件をクリアしなければTRUEになりません。条件が2つある場合は2つともクリア、3つある場合は3つともクリアしなければなりません。
障害物が2つある場合は2つとも超える必要があり、3つある場合は3つとも超えなければゴールできません。障害物が多いほうがクリアしにくくなります。したがって、条件を多くするとTRUEは少なくなります。
例えば、0.5 * 0.4 * 0.3 = 0.06です。これは、50%と40%と30%の確率の事柄が同時に発生する確率が6%しかないことを表しています。1未満の小数を掛け算するとどんどん小さくなります。
OR条件(論理和)はいずれかの条件をクリアすればTRUEになりません。条件が何個あっても1つクリアすればよいので、クリアしやすい条件を選ぶことができます。複数の障害物が設置されていても、1つ以上を選んでゴールすればよいので、障害物が多いほうがゴールできる確率が上がります。
したがって、条件=選択肢を多くしたほうがTRUEは多くなります。
4.否定同士の論理和・論理積
例えば、「化学と物理と世界史と日本史を全部受験しなければならない入試」と、「化学と物理と世界史と日本史の4科目のうち1つ選択すれば良い入試」とでは難易度が異なるのは明らかです。前者がANDで、後者がORです。ANDとORを間違えたら判定結果が異なります。
(1)否定の論理和
問題
セルA1に入力した値が、C列以上D列未満であるかを判定するため数式を入力したところ、すべて該当する結果になってしまった。その原因を述べたうえで、数式を修正しなさい。
- =IF(OR($A$1>=C2,$A$1<D2),"*","")
解説
AND関数で判定するべきところ、誤ってOR関数で判定するとすべて該当することになってしまいます。ORをANDに修正すれば正しく判定できます。
- =IF(AND($A$1>=C2,$A$1<D2),"*","")
0以上と500未満の両方に該当しない数は存在しません。必ず0以上か500未満のどちらかに該当します。
ここで、それぞれの範囲を逆にすると0以上500未満を除いた範囲になります。
そこで、OR関数を用いて、0未満または500以上の判定をしたとします。イコールの有無も変わることに注意します。偽の場合を「*」とすると、正しい判定となります。
- =IF(OR($A$1<C2,$A$1>=D2),"","*")
このように、AND条件と、それぞれの条件を逆(否定)にしたOR条件は、真逆の関係になります。真の場合と偽の場合を逆にすれば同じ判定結果になります。
(2)否定の論理積
問題
英語と数学の得点のどちらかがセル範囲A3:B3の点数に達していなければ「不合格」と表示しようとして数式を入力したところ、1人だけが不合格となった。その原因を述べたうえで、数式を修正しなさい。
- =IF(AND(D2<$A$3,E2<$B$3),"不合格","")
解説
OR関数で判定するべきところ、誤ってAND関数で判定すると該当者が減ってしまいます。
ここで、英語数学が両方とも基準点をクリアした者を合格とした場合、それ以外を不合格とすれば正しい判定となることが分かります。
そこで、AND関数を用いて、英語55以上、数学65以上の判定をしたとします。イコールの有無も変わることに注意します。偽の場合を「不合格」とすると、正しい判定となります。
- =IF(AND(D2>=$A$3,E2>=$B$3),"","不合格")
このように、OR条件と、それぞれの条件を逆(否定)にしたAND条件は、真逆の関係になります。真の場合と偽の場合を逆にすれば同じ判定結果になります。
5.論理値の和で判定していることを理解しているか?
問題
物理と化学のどちらか1つでも60点以上であれば合格とする数式を入力しようとしている。
次の式が誤っている理由を述べなさい。
- =IF(OR(A2:B2)>=60,"合格","")
- =IF(OR(A2,B2)>=60,"合格","")
解説
物理60点以上、化学60点以上をそれぞれ判定して、どちらか一方がTRUEになる論理和(OR条件)とする数式を入力すれば正しく判定できるはずです。
- =IF(OR(A2>=60,B2>=60),"合格","")
このOR関数は論理和なので、論理値(TRUEまたはFALSE)の足し算で判定することができます。
- =IF((A2>=60)+(B2>=60),"合格","")
このことが理解できていれば、AND関数やOR関数の引数として記述するのは、比較演算などの論理値を求める式であることは分かるはずです。
OR関数の中に、セル範囲またはセル参照を入力していいのは、そのセルに論理値(TRUEまたはFALSE)や判定結果が表示されている場合に限られます。
また、OR関数は論理値を返すので、OR関数と60を比較するのもナンセンスです。
6.ANDとORが混ざったパターン
最後に、ANDとORが混ざった複雑な判定について考えてみましょう。
問題
物理と化学のうち1つでも60点以上で、数学も60点以上の場合に「合格」と表示しなさい。
解説
物理と化学はどちらか1つでも60点以上あればクリアなのでOR条件です。しかし、理科のほかに数学も60点以上でなければ合格になりません。これはAND条件です。AND条件とOR条件が混ざる場合は図をかくと分かりやすくなります。
これを式にすると次のようになります。
- =AND(OR(物理>=60,化学>=60),数学>=60)
TRUEとFALSEが正しく表示されたことを確認してから、IF関数で囲みます。
- =IF(AND(OR(物理>=60,化学>=60),数学>=60),"合格","")
別解
理科は物理と化学の点数の良いほうだけを採用して60点以上であればよいと考えるとMAX関数が使えます。
- =AND(MAX(物理:化学)>=60,数学>=60)
TRUEとFALSEが正しく表示されたことを確認してから、IF関数で囲みます。
- =IF(AND(MAX(物理:化学)>=60,数学>=60),"合格","")
解説は以上です。