スプレッドシートで2列を比較し欠損値を見つける方法

クリーンな比較のためのデータの準備

比較を行う前に、両方の列がクリーンで一貫していることを確認してください。TRIM関数を使用して余分なスペースを削除し、先頭のゼロや隠し文字がないか確認し、比較を大文字と小文字を区別するかどうかを決定します。各列をアルファベット順に並べ替えることは厳密には必須ではありませんが、後で結果を視覚的に確認するのに役立ちます。

空白のセルは誤解を招く結果を引き起こす可能性があります。事前にどのように扱うかを決定してください。欠損値として扱うか、正当なデータとして扱うか、または除外するかを決めます。また、列内に重複がある場合、それらは個別に処理する必要があるかもしれません。単一の欠落アイテムが複数回表示され、カウントが歪む可能性があります。

1. 両方の列を隣接する列（例：列Aと列B）にコピーします。
2. TRIM関数をヘルパー列で使用して、誤ったスペースを削除します：=TRIM(A1)
3. 必要に応じてデータ型を変換します（例：数値がテキストとして保存されている場合）。VALUE関数またはTEXT関数を使用します。
4. 比較列に明確にラベルを付け、元のデータのバックアップを保存します。

VLOOKUPまたはXLOOKUPを使用した欠損値の検出

VLOOKUPとXLOOKUPは、最初の列の値が2番目の列に存在するかどうかをテストできます。XLOOKUPはGoogle Sheetsと最新のExcelリリースで使用できます。古いExcelインストールでは、VLOOKUP、MATCH、またはINDEX-MATCHを使用できます。

メンバーシップチェックの場合、ルックアップ値自体を返し、関数の未検出動作またはIFERRORを使用して「欠落」を表示します。これらの数式は入力行ごとに1つの結果を返し、重複キーが存在する場合に1対1の関係を証明するものではありません。

• VLOOKUP: =IFERROR(VLOOKUP(A2, B$2:B$100, 1, FALSE), "Missing")
• XLOOKUP: =IFERROR(XLOOKUP(A2, B$2:B$100, B$2:B$100), "Missing")
• #N/Aエラーは値が見つからないことを意味します。IFERRORはそれを読み取り可能なラベルに変換します。

=IFERROR(VLOOKUP(A2, B$2:B$100, 1, FALSE), "Missing")

=IFERROR(XLOOKUP(A2, B$2:B$100, B$2:B$100), "Missing")

柔軟性を高めるINDEX-MATCHの使用

INDEX-MATCHは、戻り範囲が検索範囲の右側にある必要がないため、VLOOKUPの柔軟な代替手段です。また、XLOOKUPがないワークブックでも便利です。

欠損値フラグの場合、MATCHだけでも十分です。ISNAまたはIFERRORでラップします。パフォーマンスはワークブック、範囲サイズ、数式設計、Excelバージョンに依存するため、特定の検索パターンが常に高速であるとは限りません。

• 数式: =IFERROR(INDEX(B$2:B$100, MATCH(A2, B$2:B$100, 0)), "Missing")
• MATCH関数は範囲内の値の相対位置を返します。
• INDEXはその位置にある2番目の列から実際の値を返します。見つからない場合、IFERRORは"Missing"を返します。

=IFERROR(INDEX(B$2:B$100, MATCH(A2, B$2:B$100, 0)), "Missing")

条件付き書式を使用した差異の強調表示

視覚的な概要を得るには、条件付き書式を使用して、一方の列に存在しないセルを強調表示できます。この方法は、ヘルパー列を追加せずに欠損値を一目で確認したい場合に便利です。ExcelとGoogle Sheetsの両方がカスタム数式でこれをサポートしています。

数式に基づくルールを適用します。たとえば、列Aの値が列Bにないものを強調表示するには、A2:A100を選択し、COUNTIFまたはMATCH数式を入力して、値が欠落している場合にTRUEを返すようにします。

1. 列Aの書式設定する範囲（例：A2:A100）を選択します。
2. 「ホーム」タブ > 「条件付き書式」 > 「新しいルール」（Excel）、または「表示形式」 > 「条件付き書式」（Sheets）に移動します。
3. 「数式を使用して、書式設定するセルを決定」を選択します。
4. =COUNTIF(B$2:B$100, A2)=0 のような数式を入力し、塗りつぶしの色を設定します。
5. 確認して適用します。列Bにない列Aのセルが強調表示されます。

=COUNTIF(B$2:B$100, A2)=0

=ISNA(MATCH(A2, B$2:B$100, 0))

一方または両方の列の重複の処理

重複は比較を曇らせる可能性があります。プライマリリストに同じ値が複数ある場合、単純な数式は各出現を個別のアイテムとして扱い、同じ欠損値を何度もフラグする可能性があります。同様に、ルックアップリストの重複はエラーを引き起こしませんが、1対1のマッチングを期待している場合に予期しない結果を生じる可能性があります。

重複を処理するには、まずそれらに意味があるかどうかを判断します。無視する場合は、比較する前にCOUNTIFを使用して重複をフラグします。次に、重複行を削除または集約します。あるいは、欠損した一意の値を見つけるには、一意のリストをプライマリ列として使用します。

1. プライマリリストの隣にヘルパー列を挿入し、=COUNTIF(A$2:A2, A2)>1 で重複をマークします。
2. フィルターまたは並べ替えを使用して、適切な重複エントリを確認および削除します。
3. UNIQUE関数（Excel 365/2021、Google Sheets）を使用して、比較用の重複排除リストを作成します。
4. 次に、一意のリストに対して比較数式を実行します。

=COUNTIF(A$2:A2, A2)>1

空白セルの処理

どちらかの列の空白セルは注意深い処理が必要です。多くの数式では、空白はゼロまたは空文字列として扱われ、誤った一致や欠落フラグを引き起こす可能性があります。たとえば、両方の列に同じ行に空白がある場合、VLOOKUPはそれらを同等としてマッチする可能性がありますが、空白が有効な値を表さないこともあります。

空白を管理するには、データを前処理して空白をプレースホルダー（例：「BLANK」）に置き換えるか、IF条件を使用して比較から空白セルを除外します。または、ISBLANKを使用して明示的に空白をチェックする数式を使用します。

1. 戦略を決定します。空白を比較対象の有効な値として扱うか、除外するかを決めます。
2. 空白を有効として扱う場合は、空白を一貫したプレースホルダーに置き換えます：=IF(A1="", "BLANK", A1)
3. 除外する場合は、比較を実行する前にどちらかの列が空白の行をフィルターで除外します。
4. 数式でIF条件を使用します：=IF(OR(A2="", B2=""), "除外", primary_comparison)

=IF(A2="", "BLANK", A2)

結果の検証と誤検出の回避

検証は重要です。隠れたスペース、大文字小文字の不一致、またはデータ型の違いから誤検出が発生する可能性があります。比較を適用した後、フラグが立てられたアイテムのサンプルを手動で確認してスポットチェックします。フィルターを使用して欠損値を分離し、書式設定の問題で存在しないことを確認します。

実践的な検証手順は、ピボットテーブルを使用してプライマリ列の各値の出現回数をカウントし、ルックアップ列のカウントと比較することです。また、Ctrl+Fまたは検索を使用してソース列で正確なテキストを検索して、いくつかのエントリを監査します。この追加チェックにより、アクションを実行する前に欠損リストが正確であることが保証されます。

1. UPPERとTRIMを使用してスペースを削除し大文字小文字を統一する一時的な列を追加します。
2. フラグが立てられたエントリのサンプルを手動で比較します。1つを強調表示し、値をコピーしてルックアップ列で検索します。
3. ピボットテーブルを使用します。プライマリ列の各値をカウントし、ルックアップ列でもカウントして、カウントを比較します。
4. CompareTwoListsのようなツールを使用する場合、完全にブラウザ内で実行され、一致したセットと一致しなかったセットが明確に表示されることに注意してください。

=UPPER(TRIM(A2))

=UPPER(TRIM(B2))

=IF(COUNTIF($D$2:$D$100,C2)>0,"Match","Missing")