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

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

Excelで論理積ANDと論理和ORの違いを考える、比較演算の掛け算と足し算


ExcelのAND関数は条件をすべて満たすときに使い、OR関数は条件を1つ以上満たす場合に使い、いずれも論理値を返します(参考:【Excel】AND関数やOR関数を使うなら、まず「論理値を返す関数」であることを理解せよ)。

ところで、AND関数のANDとは論理演算の論理積のことであり、ORは論理和のことです。「論理積」「論理和」ということは、論理値(TRUE・FALSE)の掛け算や足し算が関係しているのは間違いないのですが、Excelを使って、論理値の掛け算や足し算をしたことがない人は多いと思います。

そこで、今回は、AND関数やOR関数の理解を深めるために、これらの関数が掛け算や足し算とどのように関係しているのかExcelを用いて解説します。

目次

1.論理積 AND

問題

セルA1に入力した値が、C列以上D列未満であるかをそれぞれ判定し、該当すれば「*」を表示しなさい。

f:id:waenavi:20201221120038j:plain

 

解説

(1)比較演算の掛け算

「C列以上」「D列未満」をそれぞれ判定することは比較演算を使えば簡単にできます(参考:【Excel】真と偽の意味と比較演算のメリットを知らずしてIF関数を語ってはいけない)。A1は絶対参照です。TRUEであれば該当していることを表し、FALSEであれば該当しないことを表します。

  • =$A$1>=C2
  • =$A$1<D2

f:id:waenavi:20201221120743j:plain

 

「C列以上D列未満」のように2つの値の間に入っているためには、「C列以上」「D列未満」の両方を満たす必要があります。つまり、両方TRUEであることが必要です。

f:id:waenavi:20201221120822j:plain

 

ここで、2つの論理値(TRUE・FALSE)を掛け算します。論理値を掛け算するとTRUE=1、FALSE=0とみなされるため、どちらか一方がFALSEであれば0となり、両方がTRUEの場合だけ1となります

  • =E2*F2

f:id:waenavi:20201221120940j:plain

 

逆に、IF関数の論理式では、1=TRUE、0=FALSEとみなされるため、この掛け算をそのまま論理式として利用することができます(参考:IF関数の条件に「等号や不等号が必要」と思っているなら、IF関数を勉強し直してほしい)。これで完成です。

  • =IF(E2*F2,"*","")

f:id:waenavi:20201221121023j:plain

 

比較演算を用いて記述すると次のようになります。

  • =IF(($A$1>=C2)*($A$1<D2),"*","")

このように、論理値同士を掛け算することを「論理積」と言います。論理値の積だから論理積です。ここで、TRUEまたは1の個数を考えます。C列以上またはD列未満を判定した時よりも、論理積にしたほうが少なくなります。

f:id:waenavi:20201221121206j:plain

 

論理積のほうが2つの条件をクリアしないといけないので、該当するものが少なくなるのは当たり前のことです。

f:id:waenavi:20201221134301j:plain

 

(2)AND関数を用いる方法

2つの論理値を掛け算をする代わりに、AND関数の引数にしてみましょう。1と0ではなく、TRUEとFALSEになりますが掛け算をした場合と同じ結果になります。

  • =AND(E2,F2)

f:id:waenavi:20201221122002j:plain

 

AND関数は、論理積をTRUEとFALSEで返す関数です。条件をすべて満たす場合はTRUE、それ以外はFALSEとなります(参考:【Excel】AND関数やOR関数を使うなら、まず「論理値を返す関数」であることを理解せよ)。TRUEとFALSEで返す関数はそのままIF関数の論理式として利用することができます。

  • =IF(AND(E2,F2),"*","")

f:id:waenavi:20201221125051j:plain

 

比較演算を用いて記述すると次のようになります。

  • =IF(AND($A$1>=C2,$A$1<D2),"*","")

論理値を掛け算して判定するのと、AND関数を用いて判定するのは全く同じです。このため、論理積のことを「AND条件」と言うこともあります。

f:id:waenavi:20201221125619j:plain

 

2.論理和 OR

問題

英語と数学の得点のどちらかがセル範囲A3:B3の点数に達していなければ「不合格」と表示しなさい。

f:id:waenavi:20201221125833j:plain

 

解説

(1)TRUEの個数を求める

「基準点未満」をそれぞれ判定します。A1は複合参照です(参考:【Excel複合参照】行固定や列固定のドルマークの意味と練習問題)。TRUEであれば基準点未満であることを表します。

  • =D2<A$3

f:id:waenavi:20201221130117j:plain

 

どちらか一方でも該当する、つまり1つ以上TRUEであることを判定します。2つともTRUEでも構いません。ここで、2つの論理値(TRUE・FALSE)を足し算します。論理値を足し算するとTRUE=1、FALSE=0とみなされるため、TRUEの個数を求めることができます。

  • =F2+G2

f:id:waenavi:20201221131452j:plain

 

逆に、IF関数の論理式では、1以上がTRUE、0だけがFALSEとみなされるため、この足し算をそのまま論理式として利用することができます。これで完成です。

  • =IF(F2+G2,"不合格","")

f:id:waenavi:20201221131700j:plain

 

比較演算を用いて記述すると次のようになります。A3、B3は絶対参照です。

  • =IF((D2<$A$3)+(E2<$B$3),"不合格","")

f:id:waenavi:20201221131829j:plain

 

このように、論理値同士を足し算することを「論理和」と言います。論理値の和だから論理和です。ここで、TRUEまたは1の個数を考えます。英語と数学をそれぞれ判定した時よりも、論理和にしたほうがTRUEの個数が多くなります。

f:id:waenavi:20201221132422j:plain

 

論理和のほうが2つの条件のどちらか一方をクリアすればいいので、該当するものが多くなるのは当たり前のことです。

f:id:waenavi:20201221134019j:plain

 

(2)OR関数を用いる方法

2つの論理値を足し算をする代わりに、OR関数の引数にしてみましょう。1と0ではなく、TRUEとFALSEになりますが足し算をした場合と同じ結果になります。OR関数は、論理和をTRUEとFALSEで返す関数です。条件を1つ以上満たす場合はTRUE、それ以外はFALSEとなります。

  • =OR(F2,G2)

f:id:waenavi:20201221135810j:plain

 

TRUEとFALSEで返す関数はそのままIF関数の論理式として利用することができます。

  • =IF(OR(F2,G2),"不合格","")

f:id:waenavi:20201224170804j:plain

 

比較演算を用いて記述すると次のようになります。

  • =IF(OR(D2<$A$3,E2<$B$3),"不合格","")

論理値を足し算して判定するのと、OR関数を用いて判定するのは全く同じです。このため、論理和のことを「OR条件」と言うこともあります。

f:id:waenavi:20201221180621j:plain

 

3.障害物で考えた場合のイメージ

AND条件(論理積)はすべての条件をクリアしなければTRUEになりません。条件が2つある場合は2つともクリア、3つある場合は3つともクリアしなければなりません。

障害物が2つある場合は2つとも超える必要があり、3つある場合は3つとも超えなければゴールできません。障害物が多いほうがクリアしにくくなります。したがって、条件を多くするとTRUEは少なくなります。

f:id:waenavi:20201221185454j:plain

 

例えば、0.5 * 0.4 * 0.3 = 0.06です。これは、50%と40%と30%の確率の事柄が同時に発生する確率が6%しかないことを表しています。1未満の小数を掛け算するとどんどん小さくなります。

f:id:waenavi:20201221185913j:plain

 

OR条件(論理和)はいずれかの条件をクリアすればTRUEになりません。条件が何個あっても1つクリアすればよいので、クリアしやすい条件を選ぶことができます。複数の障害物が設置されていても、1つ以上を選んでゴールすればよいので、障害物が多いほうがゴールできる確率が上がります。

したがって、条件=選択肢を多くしたほうがTRUEは多くなります。

f:id:waenavi:20201221190618j:plain

 

4.否定同士の論理和・論理積

例えば、「化学と物理と世界史と日本史を全部受験しなければならない入試」と、「化学と物理と世界史と日本史の4科目のうち1つ選択すれば良い入試」とでは難易度が異なるのは明らかです。前者がANDで、後者がORです。ANDとORを間違えたら判定結果が異なります。

(1)否定の論理和

問題

セルA1に入力した値が、C列以上D列未満であるかを判定するため数式を入力したところ、すべて該当する結果になってしまった。その原因を述べたうえで、数式を修正しなさい。

  • =IF(OR($A$1>=C2,$A$1<D2),"*","")

f:id:waenavi:20201224190523j:plain

 

解説

AND関数で判定するべきところ、誤ってOR関数で判定するとすべて該当することになってしまいます。ORをANDに修正すれば正しく判定できます。

  • =IF(AND($A$1>=C2,$A$1<D2),"*","")

f:id:waenavi:20201224190856j:plain

 

0以上と500未満の両方に該当しない数は存在しません。必ず0以上か500未満のどちらかに該当します。

f:id:waenavi:20201224191939j:plain

 

ここで、それぞれの範囲を逆にすると0以上500未満を除いた範囲になります。

f:id:waenavi:20201224193518j:plain

 

そこで、OR関数を用いて、0未満または500以上の判定をしたとします。イコールの有無も変わることに注意します。偽の場合を「*」とすると、正しい判定となります。

  • =IF(OR($A$1<C2,$A$1>=D2),"","*")

f:id:waenavi:20201224193646j:plain

 

このように、AND条件と、それぞれの条件を逆(否定)にしたOR条件は、真逆の関係になります。真の場合と偽の場合を逆にすれば同じ判定結果になります。

f:id:waenavi:20201224194534j:plain

 

(2)否定の論理積

問題

英語と数学の得点のどちらかがセル範囲A3:B3の点数に達していなければ「不合格」と表示しようとして数式を入力したところ、1人だけが不合格となった。その原因を述べたうえで、数式を修正しなさい。

  • =IF(AND(D2<$A$3,E2<$B$3),"不合格","")

f:id:waenavi:20201224195957j:plain

 

解説

OR関数で判定するべきところ、誤ってAND関数で判定すると該当者が減ってしまいます。

f:id:waenavi:20201224195734j:plain

 

ここで、英語数学が両方とも基準点をクリアした者を合格とした場合、それ以外を不合格とすれば正しい判定となることが分かります。

f:id:waenavi:20201224195707j:plain

 

そこで、AND関数を用いて、英語55以上、数学65以上の判定をしたとします。イコールの有無も変わることに注意します。偽の場合を「不合格」とすると、正しい判定となります。

  • =IF(AND(D2>=$A$3,E2>=$B$3),"","不合格")

f:id:waenavi:20201224195857j:plain

 

このように、OR条件と、それぞれの条件を逆(否定)にしたAND条件は、真逆の関係になります。真の場合と偽の場合を逆にすれば同じ判定結果になります。

f:id:waenavi:20201224200249j:plain

 

5.論理値の和で判定していることを理解しているか?

問題

物理と化学のどちらか1つでも60点以上であれば合格とする数式を入力しようとしている。

f:id:waenavi:20201224200434j:plain

 

次の式が誤っている理由を述べなさい。

  • =IF(OR(A2:B2)>=60,"合格","")
  • =IF(OR(A2,B2)>=60,"合格","")

解説

物理60点以上、化学60点以上をそれぞれ判定して、どちらか一方がTRUEになる論理和(OR条件)とする数式を入力すれば正しく判定できるはずです。

  • =IF(OR(A2>=60,B2>=60),"合格","")

f:id:waenavi:20201224200523j:plain

 

このOR関数は論理和なので、論理値(TRUEまたはFALSE)の足し算で判定することができます。

  • =IF((A2>=60)+(B2>=60),"合格","")

f:id:waenavi:20201224200549j:plain

 

このことが理解できていれば、AND関数やOR関数の引数として記述するのは、比較演算などの論理値を求める式であることは分かるはずです。

f:id:waenavi:20201224200902j:plain

 

OR関数の中に、セル範囲またはセル参照を入力していいのは、そのセルに論理値(TRUEまたはFALSE)や判定結果が表示されている場合に限られます。

f:id:waenavi:20201224201419j:plain

 

また、OR関数は論理値を返すので、OR関数と60を比較するのもナンセンスです。

f:id:waenavi:20201224201834j:plain

 

6.ANDとORが混ざったパターン

最後に、ANDとORが混ざった複雑な判定について考えてみましょう。

問題

物理と化学のうち1つでも60点以上で、数学も60点以上の場合に「合格」と表示しなさい。

f:id:waenavi:20201224202244j:plain

 

解説

物理と化学はどちらか1つでも60点以上あればクリアなのでOR条件です。しかし、理科のほかに数学も60点以上でなければ合格になりません。これはAND条件です。AND条件とOR条件が混ざる場合は図をかくと分かりやすくなります。

f:id:waenavi:20201224202649j:plain

 

これを式にすると次のようになります。

  • =AND(OR(物理>=60,化学>=60),数学>=60)

f:id:waenavi:20201224202825j:plain

 

TRUEとFALSEが正しく表示されたことを確認してから、IF関数で囲みます。

  • =IF(AND(OR(物理>=60,化学>=60),数学>=60),"合格","")

f:id:waenavi:20201224202911j:plain

 

別解

理科は物理と化学の点数の良いほうだけを採用して60点以上であればよいと考えるとMAX関数が使えます。

  • =AND(MAX(物理:化学)>=60,数学>=60)

f:id:waenavi:20201224202954j:plain

 

TRUEとFALSEが正しく表示されたことを確認してから、IF関数で囲みます。

  • =IF(AND(MAX(物理:化学)>=60,数学>=60),"合格","")

f:id:waenavi:20201224203032j:plain

 


解説は以上です。


 


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