Excel(エクセル)基本講座：IF関数＆IS関数

• 条件判定（IF関数)および複数条件を設定する時のAND関数、OR関数の使い方、セルや数式の状態を調べるIS関数の説明をしています。
• CELL関数は情報関数：CELL関数へ移動しました。
• IF関数のネスト(入れ子)に関してはExcel(エクセル)関数の技：IF関数のネスト(入れ子)の方法をご覧ください。

IF関数	AND関数、OR関数	IS関数
	N関数	NA関数

条件判定の関数　　　topへ
　イフ
=IF(条件式,値１,値2）
　　条件式が満たされている場合(真の場合)は値１を表示し、満たされていない場合(偽の場合)は値2を表示する。
　　(補足)　IF 関数は最大 7 つまで組み合わせることができます。それを超える場合には他の関数を使って処理します。

• 【問題】国語の得点が60以上であれば[合格]、そうでなければ[不合格]と判定の列Dに表示しなさい。　

  	B	C	D
  2		国語	判定
  3	相沢恭子	80	合格
  4	今田浩次	50	不合格
  5	上田美香	30	不合格
  6	江口恭介	70	合格

• 【解答例】D3セル：=IF(C3>=60,"合格","不合格")
  「もしC3セルが60以上であったら『合格』、そうでなかったら『不合格』としなさい」となります。
  

  	B	C	D
  2		国語	判定
  3	相沢恭子	80	=IF(C3>=60,"合格","不合格")
  4	今田浩次	50	=IF(C4>=60,"合格","不合格")
  5	上田美香	30	=IF(C5>=60,"合格","不合格")
  6	江口恭介	70	=IF(C6>=60,"合格","不合格")

条件式に使う記号(比較演算子)　　　　topへ

A=B	AとBが等しい
A>B	AがBよりも大きい
A<B	AがBよりも小さい
A>=B	AがB以上
A<=B	AがB以下
A<>B	AとBが等しくない

未入力セルの空白処理　　　　　topへ

• 得点欄が空白の(入力されていない）場合にはD3セルに[不合格]と表示されてしまいます。
  それを防ぐのにIF関数を使います。
  • =IF(C4="","",IF(C4>=60,"合格","不合格"))
    「もし、C4セルが空白なら、空白にして、そうでなかったら、60以上で合格、それ以外は不合格とする」
    との意味合いで、IF関数を2重で使用しています。
• ISBLANK関数を使う方法もあります。
  • =IF(ISBLANK(C4),"",IF(C4>=60,"合格","不合格"))
    「C4セルがブランクであったら""(長さ0の文字列)と表示しなさい、もしC4セルが60以上なら合格とし、それ以外(60未満)は不合格と表示しなさい。」との意味合いで、IF関数を2重で使用しています。
• COUNTA関数を使うこともできます。
  =IF(COUNTA(C4)=0,"",IF(C4>=60,"合格","不合格"))
  • =IF(COUNT(C4),IF(C4>=60,"合格","不合格"),"") とすることもできます。
    この場合、COUNT(C4)=0 の時「FALSE(偽)」、COUNT(C4)>=1の時「TRUE(真)」と扱われます。
  

【文字列の比較】　　　　　topへ

• ここでは　C2="OK"　で条件判定を行うと　大文字/小文字の区別がされませんという例を示します。
  • 【問題2】
    • C列に正解であったら「OK」と入力して、得点「25」を与えたいと思います。
    • ところが入力ミスで「Ok」、「oK」とも入力されています。D列の得点を表示する式を入れなさい。

    • 	B	C	D
      2		国語	得点
      3	問題1-1	OK	25
      4	問題1-2	×	0
      5	問題2-1	oK	25
      6	問題2-2	Ok	25
      7	合計得点		75

  • 【解答例】
    • C2="OK"　で条件判定を行うと　大文字/小文字の区別がされませんので下記の数式で計算できます。
    • 大文字/小文字の区別する場合は下のEXACT関数を使います。

    • 	B	C	D	E
      2		国語	判定	答え
      3	問題1-1	OK	=IF(C3="OK",25,0)	25
      4	問題1-2	×	=IF(C4="OK",25,0)	0
      5	問題2-1	oK	=IF(C5="OK",25,0)	25
      6	問題2-2	Ok	=IF(C6="OK",25,0)	25
      7	合計得点		=SUM(D3:D6)	75

• 大文字と小文字の区別をしたい時はどうすればよいでしょうか?　(上の表で合計得点を25とするには？)
  • EXACT関数を使います。

  • 文字列の比較　　　　topへ
    　イグザクト
    =EXACT(文字列1，文字列2）
    　　　2つの文字列を比較して、まったく同じである場合は TRUE を、そうでない場合は FALSE を返します。
    　　　英字の大文字と小文字は区別されます。

  • 	B	C	D	E
    2		国語	判定	答え
    3	問題1-1	OK	=IF(EXACT(C3,"OK"),25,0)	25
    4	問題1-2	×	=IF(EXACT(C4,"OK"),25,0)	0
    5	問題2-1	oK	=IF(EXACT(C5,"OK"),25,0)	0
    6	問題2-2	Ok	=IF(EXACT(C6,"OK"),25,0)	0
    7	合計得点		=SUM(D3:D6)	25

【時刻の比較】　　　topへ

• 時刻の比較は小数誤差によってうまく処理されないことがあります。
  • TIMEVALUE関数でシリアル値に変換する
  • 時刻のシリアル値(小数値)を整数に直して比較する
  など、小数誤差の影響を受けないようにする必要があります。
• 例
  

  	B	C	D
  2	9：00	=IF(B2="9:00","OK","NG")	NG
  3		=IF(B2=TIMEVALUE("9:00"),"OK","NG")	OK
  4		=IF(B2=TIME(9,0,0),"OK","NG")	OK
  5		=IF(ROUND(B2*24*60,0)=ROUND("9:00"*24*60,0),"OK","NG")	OK

  • C2セルの条件式　B2="9:00"　は左辺B2の値はシリアル値で、右辺の"9:00"は文字列なので、同じ値とはなりません。
  • C3セルは、右辺をTIMEVALUE("9:00")でシリアル値にしていますので、同じ値と判定されます。
  • C4セルも同様にTIME(9,0,0)としてシリアル値での比較にしていますので、同じ値と判定されます。
  • C5セルの例は、小数誤差を含んでいる可能性があるとき、左辺のROUND(B2*24*60,0)でB2セルの値を分単位の整数値に丸めています。
    右辺も同様に、ROUND("9:00"*24*60,0)として9：00を分単位の整数値に丸めています。
    こうすることで小数誤差の影響を排除することができます。

複数条件の判定　　　　topへ
=IF(AND(条件1,条件2,・・・),値1,値2）
　　　　　条件1　かつ　条件2　かつ・・・・の場合値1を返し、そうでない場合は値2を返す。
=IF(OR(条件1,条件2,・・・),値1,値2）
　　　　　条件1　または　条件2　または・・・・の場合値1を返し、そうでない場合は値2を返す。

• 【問題1】
  • 国語と算数の両方が60以上の場合に[合格]それ以外は[不合格]としなさい。

    	B	C	D	E
    2		国語	算数	判定
    3	相沢恭子	80	85	合格
    4	今田浩次	60	90	合格
    5	上田美香	30	40	不合格
    6	江口恭介	70	50	不合格

• 【解答例1】
  • E3セル：=IF(AND(C3>=60,D3>=60),"合格","不合格")
  • ”もしC3セルが60以上かつD3セルも60以上なら『合格』、そうでなければ『不合格』としなさい”となります。

  • 	B	C	D	E
    2		国語	算数	判定
    3	相沢恭子	80	85	=IF(AND(C3>=60,D3>=60),"合格","不合格")
    4	今田浩次	60	90	=IF(AND(C4>=60,D4>=60),"合格","不合格")
    5	上田美香	30	40	=IF(AND(C5>=60,D5>=60),"合格","不合格")
    6	江口恭介	70	50	=IF(AND(C6>=60,D6>=60),"合格","不合格")

• 【余談】AND関数を使用しない方法：論理演算子（論理積「*」）を使用する方法
  • セルE3に=IF((C3>=60)*(D3>=60),"合格","不合格")　とします。
    • 
    • TRUE=1　FALSE=0　に置き換えて計算すると、TRUE*TRUE=1*1=1　以外の組み合わせは0(FALSE)になります。

• 【問題2】
  • 国語か算数のどちらかが60以下であれば[不合格]そうでなければ[合格]と表示しなさい。

    	B	C	D	E
    2		国語	算数	判定
    3	相沢恭子	80	85	合格
    4	今田浩次	60	90	不合格
    5	上田美香	30	40	不合格
    6	江口恭介	70	50	不合格

• 【解答例2】
  • E3セル：=IF(OR(C3<=60,D3<=60),"不合格","合格")
  • 「もしC3セルが60以下あるいはD3セルが60以下ならば『不合格』、そうでなければ『合格』としなさい」となります。

  • 	B	C	D	E
    2		国語	算数	判定
    3	相沢恭子	80	85	=IF(OR(C3<=60,D3<=60),"不合格","合格")
    4	今田浩次	60	90	=IF(OR(C4<=60,D4<=60),"不合格","合格")
    5	上田美香	30	40	=IF(OR(C5<=60,D5<=60),"不合格","合格")
    6	江口恭介	70	50	=IF(OR(C6<=60,D6<=60),"不合格","合格")

• 【余談】OR関数を使用しない方法：論理演算子（論理和「+」）を使用する方法
  • セルE3に=IF((C3<=60)+(D3<=60),"不合格","合格")　とします。
    • 
    • TRUE=1　FALSE=0　に置き換えて計算すると、FALSE+FALSE=0+0=0　以外の組み合わせは1以上になります。
      Excelでは1以上はTRUEと見なすので、FALSE+FALSE以外の条件はTRUEになります。

ANDとORを組み合わせて使う　　　　topへ

• 【問題】
  • A1セルに入力された年がうるう年であるか否かを判定するには?
  • うるう年の判定は↓で行います。
  1. 西暦年数が4で割り切れる年は原則として「うるう年」にする。
  2. 上記の例外として、西暦年数が100で割り切れる年は「平年」とする。
  3. さらに例外として、西暦年数が400で割り切れる年は「うるう年」にする。
• 【解答例】
  • =IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100)<>0)),"うるう年","平年")
  • 上の数式を解釈すると、『400で割り切れるか、あるいは、4で割り切れて100で割り切れない場合には「うるう年」そうでない場合は「平年」としなさい』となります。
  • 数式は　Microsoft　サポート技術情報　[XL] うるう年を判定する方法　を利用しています。
• 【参考】
  • =DATE(A1,3,1)-1=DATE(A5,3,0)　を使って2/29　になれば「うるう年」　そうでなければ「平年」となりそうです。
  • =IF(DAY(DATE(A5,3,0))=29,"うるう年","平年")　としたら、1900年が「うるう年」と判定されてしまいます。
  • 原因は、Microsoft　サポート技術情報を参照願います。
    • [XL] 1900 年から計算する日付システムと 1904 年から計算する日付システム
      http://support.microsoft.com/kb/180162/jp/

IS関数　　　　topへ
　イズエラー
=ISERROR(テストの対象)
　　　テストの対象が任意のエラー値 を参照するとき TRUE を返します。
　　　エラー値：#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? または #NULL! のいずれか

• E5の式は=D5/C5*100ですが、C5が空白であるためにエラー(#DIV/0!)となります。
• エラー表示をなくすためにIF関数とISERROR関数を組み合わせて使います。
  E8セルの例：=IF(ISERROR(D8/C8),"",D8/C8*100)
• 式の意味は「もしD8/C8がエラー値であったら、「""」(空白)としなさい、そうでなかったらD8/C8*100の結果を表示しなさい。」となります。
• 

　イズブランク　　　　　topへ
=ISBLANK(テストの対象)
　　　テストの対象が空白セルを参照するとき TRUE を返します。

• ISBLANK関数を使って空白の判定をすると、下記のようにOR関数と組み合わせて使います。
  E8セルの例：=IF(OR(ISBLANK(C8),ISBLANK(D8)),"",D8/C8*100)
• 式の意味は「もし、C8が空白であるか または D8が空白であったら、「""」(空白)を表示しなさい、そうでなかったらD8/C8*100の結果を表示しなさい」となります。
• 

• 【注意】ISBLANK関数で空白の判定ができない例
  • ISBLANK関数はIF関数などで空白("")を返しているセルに対しては「FALSE」となります。
  • C列の価格欄にはIF関数で『=IF(A5="","",VLOOKUP($A5,$F$3:$H$4,3,0))』と、A列が空白なら空白を返すようにしています。
  • E列で金額の計算時にISBLANK関数を使って『=IF(ISBLANK(C5),"",C5*D5)』とすると、空白と判定されません。
    この様な場合は「=IF(C6="","",C6*D6)」とします。
    

その他のIS関数　　　　　topへ

• ISERROR	対象が(#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? または #NULL! のいずれか) を参照するとき TRUE を返します。
  ISBLANK	対象が空白セルを参照するとき TRUE を返します。
  ISERR	対象が #N/A を除くエラー値を参照するとき TRUE を返します。
  ISLOGICAL	対象が論理値を参照するとき TRUE を返します。
  ISNA	対象がエラー値 #N/A (使用する値がない) を参照するとき TRUE を返します。
  ISNONTEXT	対象が文字列でない項目を参照するとき TRUE を返します (対象が空白セルを参照するときも TRUE になりますので注意してください)。
  ISNUMBER	対象が数値を参照するとき TRUE を返します。
  ISREF	対象がセル範囲を参照するとき TRUE を返します。
  ISTEXT	対象が文字列を参照するとき TRUE を返します。

N関数　　　　topへ
　ナンバー
=N(値)
　　値を数値に変換します。

• 次の戻り値になります。
  

  値	戻り値
  数値	そのままの数値
  Excelの組み込み書式で表示された日付	その日付のシリアル値
  TRUE	1
  FALSE	0
  #DIV/0!などのエラー値	エラー値
  その他	0

• (例)計算式の中で文字列になる可能性があるセルを使用する場合。
  • 四則演算で文字列が有るとエラーになるのを、文字列は「0」とみなして計算します。
    

NA関数　　　　topへ
　エヌエー
=NA()
　　常にエラー値 #N/A を返します

• 折れ線グラフで値が「0」を無視したい時に使います。
• 【例】品目3の売上金額(B4)が「0」になっています。
  
  1. データの方を変更します。
     B4に『=NA()』と入力して「#N/A」を返すようにします。
  2. #N/A　は無視して、折れ線が作成されます。
     

PageViewCounter

Since2006/2/27