ExcelでA列にあってB列にない数字を抽出する方法｜COUNTIF関数やXLOOKUPで入力ミスを確認する

Excelで大量の数値を入力していると、計算結果や入力した数字に間違いがないか確認したくなることがあります。特に、正解データと自分で入力したデータを比較して、不足している数字だけを見つけたい場合は関数を使うと効率的です。

この記事では、A列を正しい数字の一覧、B列を入力した数字の一覧として、A列には存在するもののB列にはない数字を抽出する方法をわかりやすく解説します。

Excelで正解データと入力データを比較する基本的な考え方

Excelで2つの列を比較する場合は、「片方の値がもう片方の範囲内に存在するか」を確認する方法が一般的です。

例えば、A列に正解の数字が100個あり、B列に自分が計算した数字を入力している場合、A列の数字がB列に含まれているかを順番に確認すると、入力漏れや計算ミスを発見できます。

このようなチェックには、COUNTIF関数やMATCH関数、XLOOKUP関数などが利用できます。特にCOUNTIF関数はシンプルで初心者でも使いやすい方法です。

COUNTIF関数を使うと、指定した範囲の中に特定の数字が何個存在するかを調べることができます。

例えば、A列の数字を確認して、B列に同じ数字があるか調べる場合、C列に以下の数式を入力します。

=IF(COUNTIF($B:$B,A1)=0,”なし”,”あり”)

この数式では、A1の数字がB列全体の中に存在するかを確認しています。存在しない場合は「なし」、存在する場合は「あり」と表示されます。

例えばA列に「100」「200」「300」、B列に「100」「300」がある場合、C列では「200」だけが「なし」と表示されるため、入力漏れを簡単に確認できます。

確認結果から不足している数字だけを一覧表示したい場合は、FILTER関数を使う方法があります。

Excel 365やExcel 2021以降では、以下の数式で抽出できます。

=FILTER(A:A,COUNTIF(B:B,A:A)=0)

この数式を入力すると、A列の中からB列に存在しない数字だけが自動的に一覧表示されます。

例えば、A列に「10、20、30、40」、B列に「10、30、40」が入力されている場合、結果として「20」だけが表示されます。

Excelでは検索関数を使って、別の列に同じ値があるか確認することもできます。

新しいExcelであれば、XLOOKUP関数を利用すると次のように書けます。

=IF(ISERROR(XLOOKUP(A1,B:B,B:B)),”なし”,”あり”)

XLOOKUPは検索対象が見つからない場合の処理を設定しやすいため、データチェック作業に向いています。

古いExcelを利用している場合は、VLOOKUP関数やMATCH関数でも同じような確認が可能です。

不足している数字を抽出するだけでなく、間違いを視覚的に確認したい場合は条件付き書式が便利です。

例えばA列を選択して、「ホーム」タブの「条件付き書式」から数式を設定すると、B列に存在しない数字だけ色を付けることができます。

条件付き書式には以下の数式を利用できます。

=COUNTIF($B:$B,A1)=0

この設定をすると、A列の中でB列にない数字が自動的に強調表示されるため、大量のデータ確認でもミスを見つけやすくなります。

入力ミス確認用のExcelシートを作成する場合は、元データを直接変更しないように、確認用の列を追加する形がおすすめです。

例えば、A列を「正解データ」、B列を「入力データ」、C列を「確認結果」として分けると、後から見ても何を確認しているか分かりやすくなります。

また、データ数が増える場合はテーブル機能を利用すると、行が追加されても数式を自動的に反映できるため便利です。

ExcelでA列にあってB列にない数字を見つけるには、COUNTIF関数を使った確認方法が最も簡単です。

少ないデータなら「=IF(COUNTIF($B:$B,A1)=0,”なし”,”あり”)」で確認でき、Excel 365などを利用している場合はFILTER関数で不足している数字だけを抽出できます。

入力ミスを防ぐためのチェックシートを作成しておけば、計算結果や大量の数字入力でも効率的に間違いを発見できるようになります。