欠損値の検出

Excel では、リスト A の値がリスト B に含まれているかどうかを確認するために、2 つのリストを比較する必要がある場合があります。たとえば、お客様の注文リストにある製品が、仕入先が提供する製品リストに存在するかをチェックしたいといったケースです。このタスクを完了するには、以下の3 つの方法からお好みのものを選んでご利用ください。

MATCH、ISNA、および IF で欠損値を検出
VLOOKUP、ISNA、および IF で欠損値を検出
COUNTIF および IF で欠損値を検出

MATCH、ISNA、および IF で欠損値を検出

上記のスクリーンショットに示されているように、お客様のリスト内のすべての製品が仕入先のリストに存在するかどうかを確認するには、まず MATCH 関数を使って、お客様のリスト（リスト A）にある各製品が仕入先のリスト（リスト B）のどの位置にあるかを検索します。MATCH 関数は、製品が見つからない場合に#N/A エラーを返します。この結果を ISNA 関数に渡すと、#N/A エラーが TRUE に変換され（つまり、該当製品が欠品していることを示します）、その後、IF 関数が期待通りの結果を返します。

一般的な構文

=IF(ISNA(MATCH()))「lookup_value」,lookup_range,0)),「Missing」,「Found」)

※注：「Missing」や「Found」は必要に応じて任意の値に変更可能です。

上記のスクリーンショットのように、お客様のリスト内のすべての製品が仕入先のリストに存在するかどうかを確認するには、以下の数式をセル H6 にコピーまたは入力し、Enterキーを押して、結果を取得してください。

=IF(ISNA(MATCH()))30002,$B$6:$B$10,0)),「Missing」,「Found」)

または、セル参照を使用して数式を動的にします。

=IF(ISNA(MATCH()))G6,$B$6:$B$10,0)),「Missing」,「Found」)

√ 注記：上記のドル記号（$）は絶対参照を示しており、数式内のlookup_rangeは、数式を他のセルに移動またはコピーしても変更されません。一方、lookup_valueにはドル記号が付加されておらず、動的にするようになっています。数式を入力後、フィルハンドルを下にドラッグして、下のセルにも適用してください。

数式の説明

ここでは、以下の数式を例として使用します。

=IF()ISNA()MATCH(G8,$B$6:$B$10,0)),「Missing」,「Found」)

• MATCH(G8,$B$6:$B$10,0):match_type 0により、MATCH 関数は3004（セル G8 の値）が配列 $B$6:$B$10 内で最初に一致する位置を返すよう指定されます。しかし今回のケースでは、MATCH 関数が検索配列内に該当する値を見つけられなかったため、#N/Aエラーを返しています。
• ISNA()MATCH(G8,$B$6:$B$10,0))=ISNA()#N/A):ISNA 関数は、「#N/A」エラーかどうかを判定します。該当する場合は TRUE、それ以外の場合は FALSE を返します。したがって、この ISNA 数式はTRUEを返します。
• IF()ISNA()MATCH(G8,$B$6:$B$10,0)),「Missing」,「Found」) = IF(TRUE,「Missing」,「Found」):ISNA と MATCH による比較結果が TRUE の場合、IF 関数は「Missing」を返し、それ以外の場合は「Found」を返します。したがって、この数式はMissingを返します。

VLOOKUP、ISNA、IF で欠損値を検索する

ご自身のリストにあるすべての製品がサプライヤーのリストに含まれているかを確認するには、MATCH 関数の代わりに VLOOKUP 関数を使用できます。VLOOKUP 関数も MATCH 関数と同様、対象の値が別のリストに存在しない場合（つまり欠損している場合）に#N/A エラーを返すためです。

一般的な構文

=IF(ISNA(VLOOKUP()))「lookup_value」,lookup_range,1,FALSE)),「Missing」,「Found」)

※注：「Missing」や「Found」は、必要に応じて任意の値に変更可能です。

ご自身のリストにあるすべての製品がサプライヤーのリストに存在するかどうかを確認するには、以下の数式をセル H6 にコピーまたは入力し、Enterキーを押して、結果を取得してください。

=IF(ISNA(VLOOKUP()))30002,$B$6:$B$10,1,FALSE)),「Missing」,「Found」)

または、セル参照を使って数式を動的にすることもできます。

=IF(ISNA(VLOOKUP()))G6,$B$6:$B$10,1,FALSE)),「Missing」,「Found」)

√ 注記：上記のドル記号（$）は絶対参照を示しており、lookup_rangeは数式内で、他のセルに数式を移動またはコピーしても変更されません。一方、lookup_valueにはドル記号が付いておらず、動的に参照されるようになっています。数式を入力したら、フィルハンドルを下にドラッグして、下のセルにも適用してください。

数式の説明

ここでは、以下の数式を例として使用します。

=IF()ISNA()VLOOKUP(G8,$B$6:$B$10,1,FALSE)),「Missing」,「Found」)

• VLOOKUP(G8,$B$6:$B$10,1,FALSE):range_lookup にFALSEを指定することで、VLOOKUP 関数は3004（セル G8 の値）と完全に一致する値のみを検索し、返すようになります。lookup_value が3004で、その値が配列 1の1 列目に存在する場合は、VLOOKUP は該当する値を返します。一方、存在しない場合は#N/A エラーを返します。今回の例では、3004 は配列内に存在しないため、結果は#N/Aとなります。#N/A.
• ISNA()VLOOKUP(G8,$B$6:$B$10,1,FALSE))=ISNA()#N/A):ISNA 関数は、「#N/A」エラーかどうかを判定します。該当する場合は TRUE、それ以外の場合は FALSE を返します。したがって、この ISNA 数式はTRUEを返します。
• IF()ISNA()VLOOKUP(G8,$B$6:$B$10,1,FALSE)),「Missing」,「Found」) = IF(TRUE,「Missing」,「Found」):ISNA と VLOOKUP による比較結果が TRUE の場合、IF 関数は「Missing」を返し、それ以外の場合は「Found」を返します。したがって、この数式はMissingを返します。

COUNTIF と IF で欠損値を検索する

ご自身のリストにあるすべての製品がサプライヤーのリストに存在するかどうかを確認するには、COUNTIF 関数と IF 関数を使ったよりシンプルな数式を使用できます。この数式は、Excel がゼロ（0）以外の任意の数値を TRUE と評価するという性質を利用しています。したがって、ある値が別のリストに存在する場合、COUNTIF 関数はそのリスト内での出現回数を返し、IF関数はその数値を TRUE と見なします。一方、値がリストに存在しない場合は COUNTIF 関数が0 を返し、IF関数はそれを FALSE と見なします。

一般的な構文

=IF(COUNTIF())「lookup_range」,lookup_value),「Found」,「Missing」)

※注記：「Found」や「Missing」は、必要に応じて任意の値に変更可能です。

ご自身のリストにあるすべての製品がサプライヤーのリストに存在するかどうかを確認するには、以下の数式をセル H6 にコピーまたは入力し、Enterキーを押して、結果を取得してください。

=IF(COUNTIF())$B$6:$B$10,30002),「Found」,「Missing」)

または、セル参照を使って数式を動的にしましょう。

=IF(COUNTIF())$B$6:$B$10,G6),「Found」,「Missing」)

√ 注記：上記のドル記号（$）は絶対参照を示しており、lookup_rangeは数式内で、数式を他のセルに移動またはコピーしても変更されません。一方、lookup_valueにはドル記号が付加されておらず、動的にするようになっています。数式を入力後、フィルハンドルを下にドラッグして、下のセルにも適用してください。

数式の説明

ここでは、以下の数式を例として使用します。

=IF()COUNTIF($B$6:$B$10,G8),「Found」,「Missing」)

• COUNTIF($B$6:$B$10,G8):COUNTIF 関数は、3004（セル G8 の値）が範囲 $B$6:$B$10 内に何回出現するかをカウントします。明らかに、3004 はこの範囲に含まれていないため、結果は0となります。
• IF()COUNTIF($B$6:$B$10,G8),「Found」,「Missing」) = IF(0,「Found」,「Missing」):IF 関数は0 を FALSE と見なします。そのため、この数式はMissing（第1 引数が FALSE と評価された場合に返される値）を返します。

関連関数

関連数式