IFERROR関数の使い方: #N/Aや#DIV/0!を消す(IFNAも)

=IFERROR(B2/C2,0)はB2/C2の結果を返し、その結果がエラーなら0を返します。1つ目の引数が求めたい数式で、2つ目が、その数式がエラーになったときに代わりに表示するものです。

数式リセット

E2fx

=IFERROR(B2/C2,0)

	A	B	C	D	E
1	Product	Revenue	Units	Plain	With IFERROR
2	Pens	$120	80	$1.50	$1.50
3	Paper	$300	50	$6.00	$6.00
4	Ink	$90	0	#DIV/0!	$0.00
5	Tape	$45	30	$1.50	$1.50
6	Clips	$0	0	#DIV/0!	$0.00

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

InkとClipsは個数が0なので、D列の普通の割り算は#DIV/0!と表示されます。E列ではそれらに$0.00が表示され、ほかの行には普通の価格が表示されます。C4に15と入力すると、両方の列にInkの価格が表示されます。

IFERROR関数の構文

=IFERROR(value, value_if_error)

• value(値)は計算する数式です。
• value_if_error(エラーの場合の値)は、valueがエラーのときに返されます。#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?、#NULL!、それに#CALC!のような新しいエラーも対象です。
• valueがエラーでなければ、IFERRORはそれをそのまま返します。

置き換える値には、数値(0)、文字列("Not found")、空の文字列("")、別の数式を指定できます。たとえば別の表でもう一度検索することもできます：=IFERROR(VLOOKUP(E2,A2:C6,3,FALSE),VLOOKUP(E2,G2:I6,3,FALSE))。

VLOOKUPとIFERROR

検索値が表にないと、検索は#N/Aを返します。IFERRORで囲むと、代わりにメッセージを表示できます：

数式リセット

F2fx

=IFERROR(VLOOKUP(E2,$A$2:$C$6,3,FALSE),"Not found")

	A	B	C	D	E	F
1	Product	Category	Price		Look for	Price
2	Apple	Fruit	$1.20		Pear	$1.50
3	Pear	Fruit	$1.50		Kiwi	Not found
4	Carrot	Vegetable	$0.80		Milk	$1.10
5	Bread	Bakery	$2.40
6	Milk	Dairy	$1.10

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

Kiwiはリストにないので、F3はNot foundと表示します。A4のCarrotをKiwiに書き換えると、F3で見つかります。XLOOKUPなら、4つ目の引数が「見つからない場合」の値なので、このためのIFERRORは要りません：=XLOOKUP(E2,A2:A6,C2:C6,"Not found")。

IFNA: #N/Aだけを受け止める

IFNAはIFERRORと同じように動きますが、置き換えるのは#N/Aだけです。検索ではたいていこちらが望ましい動きです。#N/Aは「見つからない」という普通の答えですが、それ以外のエラーは数式そのものが間違っていることを意味するからです。このシートの数式は、3列の表の4列目を求めるという打ち間違いをしています：

数式リセット

F2fx

=IFERROR(VLOOKUP(E2,$A$2:$C$6,4,FALSE),"Not found")

	A	B	C	D	E	F	G
1	Product	Category	Price		Look for	IFERROR	IFNA
2	Apple	Fruit	1.2		Pear	Not found	#REF!
3	Pear	Fruit	1.5
4	Carrot	Vegetable	0.8
5	Bread	Bakery	2.4
6	Milk	Dairy	1.1

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

Pearは表にあるのに、F2はNot foundと表示します。IFERRORが、間違った列番号による#REF!を、商品がないときと同じメッセージに変えてしまったからです。G2は#REF!をそのまま通すので、数式が壊れていることがわかります。G2の4を3に変えると、1.5が返ります。IFNAにはExcel 2013以降が必要です。

エラーの代わりに空白を返す

何も表示しないなら、置き換える値に空の文字列(ダブルクォーテーション2つ)を使います：

数式リセット

D2fx

=IFERROR((C2-B2)/B2,"")

	A	B	C	D
1	Month	Last year	This year	Growth
2	Jan	200	240	20%
3	Feb	0	150
4	Mar	180	171	-5%
5	Apr		90
6	May	250	300	20%

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

2月と4月は昨年の売上がないので伸び率を計算できず、セルは空のままです。ほかの月は20%、マイナス5%、20%と表示されます。""のセルには文字列が入っています。SUMとAVERAGEはそれを飛ばしますが、=D3*2は#VALUE!になります。後の数式でその列を計算に使うなら、代わりに0を返してください。

練習: 見つからないときの表示付きで検索する

数式リセット

F2fx

	A	B	C	D	E	F
1	Product	Stock			Look for	Stock
2	Apple	40			Kiwi
3	Pear	25
4	Carrot	60
5	Bread	12
6	Milk	30

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

やってみよう: F2で、E2の商品の在庫をA2:B6から検索し、リストにないときは「Not found」と表示してください。

答え合わせ答えを見る

すべてのエラーを隠すと間違いまで隠れる理由

IFERRORは何も直しません。セルに何を表示するかを決めるだけです。数式をIFERRORで囲む前に：

1. エラーが起きる理由を確かめる。 Unitsの空白セルが#DIV/0!の原因なら、本当に必要なのは価格を0にすることではなく、誰かがデータを入力することかもしれません。
2. 検索にはIFNAを使う。 そうすれば、間違った列番号(#REF!)、つづりの間違った名前(#NAME?)、数値の列に入った文字列(#VALUE!)はそのまま表示されます。
3. 割り算では特定のケースを判定する。 =IF(C2=0,0,B2/C2)は割る数が0の場合だけを処理し、B2の参照が間違っていればそのエラーは表示されます。2つの方法は#DIV/0!のページで比べています。
4. データと見間違えない置き換えの値を選ぶ。 価格の列の0は本物の価格に見え、平均を下げてしまいます。""や「Not found」ならその心配はありません。

数式を囲むのは最後にします。まず、正しく動くはずの行で正しい結果が出ることを確かめてください。