PartT  IF関数 AND・OR・IS関数との組み合わせ!

基本あらかると

 匠への技で毎回登場するIF関数。
条件判断を多重化(ネストIF)することができますが、乱発は計算速度を低下させますので注意が必要です。
また、式が複雑になりデバック(虫取り=誤り探し)に苦労することがあります。そんなときにはワークセル(別に作業をするセル)を用意して式を分割させる方法をとるとわかりやすい計算式が完成します。複雑になりそうな場合は、フローチャートを作成してワークセルを使うかどうか判断しましょう。 
関数式  =IF(論理式,真の場合,偽の場合)

 ネストIFとは真の場合または偽の場合にIFを重ねて定義することです。
こんな時に使います。 セルA1が1の時 "当座性貯金" 、2の時は "定期貯金"、3の時は "定期積金" をセルA2に代入するといった場合です。

  セルA2の式  =IF(A1=1,"当座性貯金",IF(A1=2,"定期貯金","定期積金"))

最初の論理式 A1=1 はセルA1が1かどうかを判断しています。真の時は "当座性貯金" を代入します。
偽の時は判断2と3が残っていますのでIF文を重ねます。 IF(A1=2, 1でない時2かどうかを判断しています。真の時は "定期貯金" を代入します。偽の時は、さらにIFを重ねる?
ではなく残りの条件は、3のみですので 偽の式に "定期積金" を記述して終了です。この条件式の多重化を「ネストする」などと呼んでいます。

次の同様の式を見てください。論理式を否定から開始した場合です。
=IF(A1<>1,IF(A1<>2,"定期積金","定期貯金"),"当座性貯金")  これは最初の定義とまったく同じ条件式です。しかし、見た目も悪いし理解しがたいイメージを感じます。分かりづらい文法はあまり表現しないことですね。
さて、前置きが長かったですが、関数あらかるとPartTではIF関数を取り上げます。AND、OR、IS関数との組み合わせにもご注目ください。
AND、OR、IS関数を単独で使った場合、対象となる値がTRUE(真)かFALSE(偽)かを返します。
これらをIF関数と組み合わせることにより論理式が複数に重なる時などネストIFを使うことなく定義できたり、セル値のデータタイプにより分岐式を組み立てられたり以外と役に立つ機能です。
AND関数、OR関数、IS関数、IF関数の順に記述します。

 AND関数
関数式 =AND(論理式1, 論理式2, ...)
  • すべての引数が TRUE のとき、TRUE を返します。引数が 1 つでも FALSE である場合、戻り値は FALSE になります。
  ● 使い方例−T  セルA2、A3の値が2002/10/1から2002/10/31の期間内か否かの判断
 
預入日
2002/10/05
2002/09/30
関数式
=AND(A2>=DATEVALUE("2002/10/01"),A2<=DATEVALUE("2002/10/31"))
=AND(A3>=DATEVALUE("2002/10/01"),A3<=DATEVALUE("2002/10/31"))
 
 
 
 
結果 
返値は TRUE です。
返値は FALSE です。
※Excelへコピーペして確認することができます。 上図ではA1の預入日から縦にセルA6のAND関数式までをコピー、Excelのワークシート上のセルA1をアクティブにしてペーストします。以下全てに適用します。

  ● 使い方例−U  セルA1、セルA2、セルA3の値を判断
 
1
2
4
関数式
=AND(A1=1,A2=2,A3=4)
=AND(A1=3,A2=5,A3=1)
 
 
 
 
結果 
セルA1が1、A2が2、A3が4か否かの判断  返値は TRUE です。
セルA1が3、A2が5、A3が1か否かの判断  返値は FALSE です。


 OR関数
関数式 =OR(論理式1, 論理式2, ...)
  • いずれかの引数が TRUE のとき、TRUE を返します。
    引数がすべて FALSE である場合は、FALSE を返します。

  ● 使い方例−T  セルA2、A3の値が2002/10/1以前もしくは(または)2002/10/31以降か否かの判断
 
預入日
2002/10/05
2002/09/30
関数式
=OR(A2<DATEVALUE("2002/10/01"),A2>DATEVALUE("2002/10/31"))
=OR(A3<DATEVALUE("2002/10/01"),A3>DATEVALUE("2002/10/31"))
 
 
 
 
結果 
返値は FALSE です。
返値は TRUE です。


  ● 使い方例−U  セルA1、セルA2、セルA3の値を判断
 
1
2
4
関数式
=OR(A1=9,A2=2,A3=7)
=OR(A1=3,A2=5,A3=8)
 
 
 
 
結果 
セルA1が1、A2が2、A3が4か否かの判断  返値は TRUE です。
セルA1が3、A2が5、A3が1か否かの判断  返値は FALSE です。


 IS関数
値や参照のタイプを調べるためのワークシート関数が、全部で 9 種類用意されています。これらを総称してIS関数と呼んでいます。
関数式  
ISBLANK(テストの対象) テストの対象が空白セルを参照するとき TRUE を返します。
ISERR(テストの対象) テストの対象が #N/A を除くエラー値を参照するとき TRUE を返します。
ISERROR(テストの対象) テストの対象が任意のエラー値 (#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? または #NULL! のいずれか) を参照するとき TRUE を返します。
ISLOGICAL(テストの対象) テストの対象が論理値を参照するとき TRUE を返します。
ISNA(テストの対象) テストの対象がエラー値 #N/A (使用する値がない) を参照するとき
TRUE を返します。
ISNONTEXT(テストの対象) テストの対象が文字列でない項目を参照するとき TRUE を返します (テストの対象が空白セルを参照するときも TRUE になりますので注意してください)。
ISNUMBER(テストの対象) テストの対象が数値を参照するとき TRUE を返します。
ISREF(テストの対象) テストの対象がセル範囲を参照するとき TRUE を返します。
ISTEXT(テストの対象) テストの対象が文字列を参照するとき TRUE を返します。

○ 数式エラーの種類
###### セルの幅よりも長い数値が入力されている場合、または結果が負の値になる日付または時刻が入力されている場合に表示されます。
※数式のエラーではないが桁溢れを起こすと表示されます。
#N/A 関数や数式に使用できる値がない場合に表示されます。
#VALUE ! 引数やオペランドの種類が正しくない場合に表示されます。
#DIV/0 数式でゼロ (0) による除算が行われた場合に表示されます。
#NAME ? Excel で認識できない文字列が使われた場合に表示されます。
#NULL ! 指定した 2 つのセル範囲に共通部分がない場合に表示されます。
#REF! 数式中のセル参照が無効なときに表示されます。
#NUM ! 数式または関数の数値に問題がある場合に表示されます。

○ エラーが生じる関数定義例
 
A B
100 =VLOOKUP(A1,J23:J27,2)
A =CEILING(A2,3)
100 =A3/0
  =A77777
=(A5 A1)
=OFFSET(A1:A3,0,-1)
-10 =CEILING(A7,10)
エラー原因
返値 #N/A 範囲に該当するデータがない。
返値 #VALUE ! 引数が正しくない。
返値 #DIV/0 除数0の演算
返値 #NAME ? セルが存在しない。
返値 #NULL ! 何の指定かわからない。
返値 #REF! ワークシート範囲外を指定。
返値 #NUM ! 符号が異なっている。


  ● 使い方例−T
ISBLANK関数  (テストの対象が空白セルを参照するとき TRUE を返します。)
 
A B
123456 =ISBLANK(A1)
ABCDEF =ISBLANK(A2)
あいうえおかき =ISBLANK(A3)
  =ISBLANK(A4)
 結果
FALSE
FALSE
FALSE
TRUE


  ● 使い方例−U
ISERR関数 (テストの対象が #N/A を除くエラー値を参照するとき TRUE を返します。)
 
B
#N/A
#VALUE !
#DIV/0
#NAME ? 
#NULL !
#REF!
#NUM !
C
=ISERR(B1)
=ISERR(B2)
=ISERR(B3)
=ISERR(B4)
=ISERR(B5)
=ISERR(B6)
=ISERR(B7)
 結果
FALSE
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
  ※ エラーが生じる関数定義例 で試行した内容にC列のみ複写し確認ください。


  ● 使い方例−V
ISERROR関数  (テストの対象が任意のエラー値 (#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? または #NULL! のいずれか) を参照するとき TRUE を返します。)
 
B
#N/A
#VALUE !
#DIV/0
#NAME ? 
#NULL !
#REF!
#NUM !
C
=ISERROR(B1)
=ISERROR(B2)
=ISERROR(B3)
=ISERROR(B4)
=ISERROR(B5)
=ISERROR(B6)
=ISERROR(B7)
 結果
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
  ※ エラーが生じる関数定義例 で試行した内容にC列のみ複写し確認ください。


  ● 使い方例−W
ISLOGICAL関数  (テストの対象が論理値を参照するとき TRUE を返します。)論理値=TRUEまたはFALSE の値か否かの判断
 
C
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
D
=ISLOGICAL(C1)
=ISLOGICAL(C2)
=ISLOGICAL(C3)
=ISLOGICAL(C4)
=ISLOGICAL(C5)
=ISLOGICAL(C6)
=ISLOGICAL(C7)
=ISLOGICAL(C8)
 結果
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
FALSE
  ※ 使い方例−Vに継続しD列にのみ複写し確認ください。


  ● 使い方例−X  
ISNA関数 (テストの対象がエラー値 #N/A (使用する値がない) を参照するときTRUE を返します。)
 
B
#N/A
#VALUE !
#DIV/0
#NAME ? 
#NULL !
#REF!
#NUM !
C
=ISNA(B1)
=ISNA(B2)
=ISNA(B3)
=ISNA(B4)
=ISNA(B5)
=ISNA(B6)
=ISNA(B7)
 結果
TRUE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
  ※ エラーが生じる関数定義例 で試行した内容にC列のみ複写し確認ください。


  ● 使い方例−Y
ISNONTEXT関数  (テストの対象が文字列でない項目を参照するとき TRUE を返します 。(テストの対象が空白セルを参照するときも TRUE になりますので注意してください)。)
 
A B
123456 =ISNONTEXT(A1)
ABCDEF =ISNONTEXT(A2)
あいうえおかき =ISNONTEXT(A3)
  =ISNONTEXT(A4)
 結果
TRUE
FALSE
FALSE
TRUE  (※空白も認識)


  ● 使い方例−Z   
ISNUMBER関数 (テストの対象が数値を参照するとき TRUE を返します。)
 
A B
123456 =ISNUMBER(A1)
ABCDEF =ISNUMBER(A2)
あいうえおかき =ISNUMBER(A3)
  =ISNUMBER(A4)
 結果
TRUE
FALSE
FALSE
FALSE


  ● 使い方例−[
ISREF関数  (テストの対象がセル範囲を参照するとき TRUE を返します。)
 
A
=ISREF(OFFSET(A1:A3,0,1))
=ISREF(OFFSET(A1:A3,0,-1))
 結果
ワークシート範囲内の指定なので返値  TRUE となります。 
ワークシート範囲外の指定なので返値  FALSE となります。


  ● 使い方例−\
ISTEXT関数  (テストの対象が文字列を参照するとき TRUE を返します。)
 
A B
123456 =ISTEXT(A1)
ABCDEF =ISTEXT(A2)
あいうえおかき =ISTEXT(A3)
  =ISTEXT(A4)
 結果
FALSE
TRUE
TRUE
FALSE



 IF関数
関数式 =IF(論理式,真の場合,偽の場合)
  • 指定された論理式(条件)が TRUE のとき「真の場合」を返し、FALSE のとき「偽の場合」を返します。
    IF 関数を使用して値または数式が処理に値する条件を満たしているかどうかをテストすることができます。


  ● 使い方例−T
セルB1へセルA1の値が1の時に「正取引」を代入し、以外「取消」を代入する。
 
A B
1 =IF(A1=1,"正取引","取消")
 結果
セルA1の値が1なので論理式 A1=1 はTRUEとなり真の場合の "正取引" が代入されます。 


  ● 使い方例−U
セルC1へセルA1が数値の場合にA1*B1の計算値を代入し、以外 "" を代入する。
IF関数とISNUMBER関数の組み合わせです。
 
A B C
10 100 =IF(ISNUMBER(A1),A1*B1,"")
 結果
セルA1は数値なので真の場合となり結果 1000 が代入されます。

 
A B C
AA 100 =IF(ISNUMBER(A1),A1*B1,"")
 結果
セルA1は文字列なので偽の場合となり結果 "" が代入されます。
 
次にセルA1とB1が数値の条件の場合です。処理は同様とします。
IF関数、AND関数、ISNUMBER関数の組み合わせです。
 
A B C
10 100 =IF(AND(ISNUMBER(A1),ISNUMBER(B1)),A1*B1,"")
 結果
セルA1とB1が数値であることをISNUMBER関数でテストし結果両方とも数値である場合の条件判断がAND関数の判定となり、真の場合となるので結果 1000 が代入されます。

 
A B C
AA 100 =IF(AND(ISNUMBER(A1),ISNUMBER(B1)),A1*B1,"")
 結果
同様に、偽の場合となるので結果 "" が代入されます。
※ ちなみにAND関数を使わなかった場合の関数式です。
=IF(ISNUMBER(A1),IF(ISNUMBER(B1),A1*B1,""),"")    IFを2回重ねる式となります。


  ● 使い方例−V
セルB1へセルA1>=10かつ<=100の時に「許容範囲」を代入し、以外「許容範囲外」を代入する。
 
A B
99 =IF(AND(A1>=10,A1<=100),"許容範囲","許容範囲外")
 結果
許容範囲  が代入されます。
※ 論理式 AND(A1>9,A1<101) の定義も同様の値が得られます。


  ● 使い方例−W
セルB1へセルA1<=10または>=100の時に「対象」を代入し、以外「非対象」を代入する。
 
A B
99 =IF(OR(A1<=10,A1>=100),"対象","非対象")
 結果
対象  が代入されます。
※ 論理式 OR(A1<11,A1>99) の定義も同様の値が得られます。


  ● 使い方例−X
#DIV/0 の抑制です。 (除数0を実行した時のエラーで ディバイド バイ ゼロ と呼んでいます。)
セルC1へセルA1/B1の計算値を代入し代入する。
 
A B C
1000 0 =A1/B1
 結果
#DIV/0 エラー値が代入されます。
A1とB1が数値で0でないことを判断しての関数式です。
 
A B C
1000 0 =IF(AND(ISNUMBER(A1),ISNUMBER(B1)),IF(A1>0,IF(B1>0,A1/B1,""),""),"")
 結果
空白です。
同様にもっと簡単にする場合、ISERROR関数でエラー判断します。
 
A B C
1000 0 =IF(ISERROR(A1/B1),"",A1/B1)
 結果
空白です。


  ● 使い方例−Y
セルC1の値を検索値をして参照範囲セルA1:B5から該当するデータをセルC2へ代入する。検索は完全一致とする。
 
A B C
CODE NAME 8
1 AAA =VLOOKUP(C1,A1:B5,2,FALSE)
2 BBB
3 CCC
4 DDD
 結果
該当する検索値がないので 返値 #N/A となります。
検索値が存在しない場合は「データなし」を代入する。
 
A B C
CODE NAME 8
1 AAA =IF(ISNA(VLOOKUP(C1,A1:B2,2,FALSE)),"データなし",VLOOKUP(C1,A1:B2,2,FALSE))
2 BBB
3 CCC
4 DDD



以上、各関数毎に何種類かの例を取り上げて見ました。