Excelで複数シートのデータを比較し、「Yes」や「No」で判定したいと思ったことはありませんか？ 業務や集計作業で役立つテクニックですが、実は簡単な関数を使うだけで効率よく実現できます。本記事では、その具体的な方法をステップバイステップで解説します。どうぞ最後までご覧ください。
2つのシートを比較してYes/Noを返す基本的な手法
Excelで異なるシートに入力されたデータをつき合わせて、「対象の値が存在するかどうか」を判定し、結果をYes/Noで返す手法はとても便利です。特に、複数の名簿やリストの重複確認・名寄せなどのシーンでよく使われます。ここでは、最もシンプルな方法として、COUNTIF関数とIF関数を組み合わせる方法をご紹介します。
COUNTIF関数とIF関数を組み合わせるメリット
COUNTIF関数は、指定した範囲の中で特定の条件を満たすセルの個数を数える関数です。ある値が範囲内に「1つ以上」存在するかどうかを調べたいときに非常に重宝します。
そしてIF関数は、「もし○○ならば××を返す、そうでなければ△△を返す」という分岐処理ができる関数です。
これらを組み合わせることで、目的とする「対象の値がシート内に存在すればYes、存在しなければNoを返す」というロジックが簡単に実現できるのです。
基本的な数式例
最も基本的な数式例は下記のようになります。ここでは、比較対象を「Worksheet A」と「Worksheet B」の列Aに分け、Worksheet Bの列Aの各セルに対して、「Worksheet Aに同じ値があるか」を調べるケースを想定しています。
=IF(COUNTIF('Worksheet A'!A:A, 'Worksheet B'!A1) > 0, "Yes", "No")COUNTIF('Worksheet A'!A:A, 'Worksheet B'!A1)- 「Worksheet A」の列A全体（A:A）を探索範囲とし、「Worksheet B」列Aの1行目（A1）にある値が何個含まれているかをカウントします。
IF(条件式, "Yes", "No")- COUNTIFの結果が「0より大きい（=1以上）」場合はYesを返し、そうでない場合はNoを返します。
この数式を「Worksheet B」の任意の列（例：B列）に入力し、下方向にオートフィル（コピー）すれば、行数分の判定結果を一括で確認できます。
シート名にスペースや記号がある場合
シート名にスペースやハイフン、その他の特殊文字が含まれている場合は、式内でシート名をシングルクォートで囲む必要があります。たとえば「Data Sheet」という名前なら、以下のように記述します。
=IF(COUNTIF('Data Sheet'!A:A, A1) > 0, "Yes", "No")これを忘れると「#NAME?」エラーが出てしまい、正常に動作しなくなるので注意が必要です。
行数が異なる場合や参照範囲の注意点
Excelの実務では、片方のシートが何百行もあり、もう片方のシートが数十行しかない、またはその逆というケースも多々あります。そんなときでも、COUNTIFを使った手法ならほぼ同じ手順で実現できます。ただし、いくつかの注意点があります。
行数が異なってもOKな理由
COUNTIF関数で指定する範囲はA列全体（A:A）などのように列単位で丸ごと指定することができます。列単位で指定していれば、たとえWorksheet Aが10行、Worksheet Bが1,000行あっても、「Worksheet AのA列内にWorksheet Bの値が含まれているか」を正しく判定できます。
一方で、任意の範囲（例：A1:A100）を指定している場合は、実際のデータが101行目や200行目にまで及んでいると、そこが検索対象から漏れてしまいます。このため、列全体を指定しておくと安全です。
参照セルの相対・絶対指定
下方向にコピーする場合、通常は「Worksheet B」側のA1がA2、A3…と自動でずれていけば良いので、相対参照のままで問題ありません。
一方、検索対象の範囲「’Worksheet A’!A:A」は固定したい場合がありますが、列全体を指定している場合はもともと変動しないため、特に$を付ける必要はありません。ただし、特定の範囲だけに絞りたいときは、$を付けて絶対参照にすることを検討しましょう。
誤って範囲参照がずれてしまうケース
- 例：式を引っ張ってコピーするときに、COUNTIFの検索範囲が「A2:A101」「A3:A102」といった形でずれてしまう
- こうなると、最初の行の比較では正しく動作しても、後続の行は思わぬ値しか検索しないことになる
列全体を指定（A:Aなど）しているときはこの問題は発生しませんが、部分範囲指定をしているときには注意しましょう。
よくあるトラブルシューティング
ここでは、よくあるトラブルとその対策をまとめます。
トラブル1：結果がすべてNoになる
- シート名のスペルミスやスペース漏れ
- 例えば「WorksheetA!A:A」なのに実際のシート名は「Worksheet A（スペースあり）」だった
- 必要に応じてシングルクォートを付ける
- 検索範囲と検索値のデータ型が不一致
- 片方の値が数値、片方の値が文字列の場合、見た目が同じでも一致判定されない
- 数値を文字列化していないか、セルの表示形式を確認する
- COUNTIFが意図せず0を返している
- 実際にはシート上に値が存在しないか、条件式が間違っていることも考えられる
トラブル2：数式をコピーしても下の行が更新されない
- 計算モードが手動になっている
- [数式]タブ → [計算方法の設定] → [自動]を選択
- 手動モードだとセル内容を変更しても再計算されない
- 数式の参照先が固定されている
- $A$1のように絶対参照になっていると、コピー先でも常にA1だけを参照してしまう
- 下方向にコピーするときは、検索値部分を相対参照にする
トラブル3：重複データが存在するときの取り扱い
COUNTIF関数は重複しているデータも含めてカウントします。つまり、Worksheet Aに同じ値が3件あれば、COUNTIFの結果は「3」になります。IF関数では、0より大きいかどうかを判断しているので、1件であろうと100件であろうとYesになってしまいます。
もし「重複の数まで知りたい」のであれば、IF関数でなくCOUNTIFの結果をそのまま表示し、「0なら存在しない、1以上なら存在」という判断をする方法もあります。
=COUNTIF('Worksheet A'!A:A, 'Worksheet B'!A1)- 0ならNo相当、1以上ならYes相当
重複の管理方法
- Worksheet A側で重複を排除しておく（データ→重複の削除）
- ピボットテーブルや条件付き書式を使って重複箇所を可視化する
IFとCOUNTIF以外のアプローチ
同様の判定を行う方法はIF + COUNTIFだけではありません。実務では、VLOOKUP関数やXLOOKUP関数、またはMATCH関数などを利用することも多いです。ここでは簡単に各手法の特徴を見てみましょう。
VLOOKUP関数を使う場合
VLOOKUP関数は、左端の列からデータを検索して、同じ行の別列にある値を返す関数です。
「対象が存在すれば対応する値、存在しなければ#N/Aエラー」となるのが特徴です。
エラーを回避するにはIFERROR関数を組み合わせることが多いでしょう。
=IFERROR(
VLOOKUP(A1, 'Worksheet A'!A:B, 2, FALSE),
"No"
)- 上式は、Worksheet BのA1の値をWorksheet AのA列で探し、見つかったらB列の値を返す。見つからなければ「No」という文字を返す、という動きになります。
- 「Yes/No」だけ返したい場合は、対応する値の代わりに任意の文字列を返すように式を組み替えればOKです。
XLOOKUP関数を使う場合
Microsoft 365以降で利用できるXLOOKUP関数は、VLOOKUPよりも柔軟性が高く、指定した範囲内を上から下まで検索し、該当がなければエラー時の戻り値を指定できます。
VLOOKUPのように列番号を意識する必要がなく、水平・垂直どちらも扱えるため、最新のExcel環境ではこちらを活用するほうが便利です。
=XLOOKUP(A1, 'Worksheet A'!A:A, 'Worksheet A'!B:B, "No", 0)- 第4引数に「No」を指定すると、見つからない場合にNoを返すように設定できます。
- COUNTIFの代わりにXLOOKUPを使ってYes/No判定したい場合は、IFERRORやIFNAと組み合わせたり、第4引数に指定する形で可能です。
MATCH関数を使う場合
MATCH関数は、指定範囲内で指定値が何番目にあるかを返す関数です。値が存在しないと#N/Aエラーが返ります。
このエラーを利用してYes/No判定することもできます。IF関数やIFERROR関数と組み合わせると、COUNTIFを使わなくても同様の判定が実現できるわけです。
=IF(ISNA(MATCH(A1, 'Worksheet A'!A:A, 0)), "No", "Yes")部分一致や曖昧検索にしたい場合
標準のCOUNTIFは完全一致での検索を行います。もし部分一致（ワイルドカード）を活用したいときは、式内で「*」などを活用できます。例えば、「前後に何か文字がついていても一致するかを調べたい」というケースです。
=IF(COUNTIF('Worksheet A'!A:A, "*"&A1&"*")>0, "Yes", "No")- A1の値を含む文字列がWorksheet AのA列に存在すればYes、それ以外はNoを返す
- たとえば、A1が「ABC」なら「ABC123」や「xyzABC」のようなセルもヒット対象になります
ただし、意図しないデータまでマッチしてしまう可能性が高まるので、部分一致検索は慎重に使いましょう。
複数条件で判定するには？
COUNTIF関数は基本的に単一条件でのカウントしかできません。複数条件を同時に判定したい場合はCOUNTIFS関数（末尾にSが付く）が役立ちます。たとえば「列AがWorksheet BのA1と等しく、かつ列BがWorksheet BのB1と等しい場合」をYes/Noで判定したい場合などです。
=IF(
COUNTIFS('Worksheet A'!A:A, 'Worksheet B'!A1, 'Worksheet A'!B:B, 'Worksheet B'!B1) > 0,
"Yes",
"No"
)- A列とB列、2つの列をセットでチェックする
- どちらの値も一致する行が1つでも見つかればYes、なければNo
3つ以上の条件でも使える
COUNTIFSは引数を増やすことで3つ以上の条件にも対応できます。複雑な絞り込みを行いたい場合に重宝しますが、そのぶん記述が長くなりがちです。可読性を考慮しながら、分かりやすい命名やコメントをつけると良いでしょう。
大量データでのパフォーマンスを気にするとき
COUNTIF、COUNTIFS、VLOOKUPなどは大量データ（数万件～数十万件）を扱う際にはパフォーマンス面の懸念が生じる場合があります。
以下のようなテクニックで高速化を図ることができます。
テーブル機能を活用
Excelのテーブル機能を使用すると、自動で拡張される範囲指定が可能になり、関数の読み込み時の処理が最適化されるケースがあります。また、列参照などがわかりやすくなるメリットもあります。
動的範囲名を使う
名前定義を活用し、「OFFSET」関数などで動的にデータ範囲を指定する方法です。実際には空白が多い列全体を参照するよりも、データが存在する範囲だけを対象とするほうが計算が速くなることがあります。
ピボットテーブルやPower Queryで事前集計
Excelの機能にピボットテーブルやPower Query（クエリと接続）があります。事前に集計や重複判定などを行っておき、結果を別シートに出力することで、シンプルなシート関数だけで済むように最適化できます。
実務での活用アイデア
最後に、COUNTIFを使ったシート間比較を実際の業務でどのように活用できるか、いくつか例を挙げてみます。
顧客リストや会員リストの照合
- 古いリストと新しいリストを比較して、「前回リストにあったのに今回リストにはない人」を探す
- メールアドレスの重複や、すでに登録されている会員の検出
受発注データの差分チェック
- 受注管理システムからの抽出結果と、実際の納品リストをつき合わせて、不足や過剰がないかをYes/No判定
外部データとの照合によるエラー検出
- 社内データベースと取引先から提供されたCSVリストを比較し、未登録のIDや不正なIDを発見する
- 大量の在庫リストを一括で比較して、棚卸し作業の効率を上げる
まとめ
Excelで複数のシートを比較し、「存在すればYes、存在しなければNo」を判定する方法は、COUNTIFとIF関数の組み合わせがシンプルで扱いやすいです。
基本式は以下のとおりです。
=IF(COUNTIF('Worksheet A'!A:A, 'Worksheet B'!A1) > 0, "Yes", "No")- シート名や範囲のスペルミス、データ型の不一致に注意しながら設定する
- 下方向にコピーする際の相対参照と絶対参照を適切に使い分ける
- 行数が違っていても列全体参照を使えば問題ない
- VLOOKUPやXLOOKUPを使う方法も検討すると、さらに柔軟な処理ができる
重複管理や複数条件検索など、実務の要件次第ではCOUNTIF系の関数以外の選択肢も視野に入れて、最適な方法を選びましょう。今回紹介した手法をマスターすれば、日々のExcel作業が格段にスムーズになり、生産性の向上につながります。ぜひ活用してみてください。
コメント