Excelで重複データを見つけて消す｜条件付き書式・COUNTIF・重複の削除と消えない原因

数値・文字列・大文字小文字の判定

表示形式とデータの種類を分ける

見た目が123でも、数値と文字列が混ざる場合があります。=ISNUMBER(A2)は数値なら、=ISTEXT(A2)は文字列ならTRUEです。配置だけで判定せず、表示形式を「文字列」に変えても入力済みの値が変換されるとは考えないようにします。

関数や操作によって照合方法が異なるので、比較する列の種類をそろえます。金額など計算する値は数値、社員番号などの識別子は文字列と、用途から決めます。先頭のゼロが意味を持つ番号の数値化は避けます。

表示だけ00123にした数値と、文字列の00123も区別します。数値化した後では元のゼロの有無は判断できません。詳しくはExcelで日付や数字の表示がおかしいとき｜####・シリアル値・指数表示・先頭のゼロの原因と直し方でも整理しています。

重複の削除では表示形式も比較に関わる場合があります。日付や小数のキーは、必要な時刻や桁が隠れていないか確認します。丸めた見た目に合わせて、別の値を一件へまとめないようにします。

15桁を超える番号を保護する

Excelの数値は有効桁数が15桁までなので、長い識別番号を数値として取り込むと、末尾の桁が失われることがあります。本来違う番号が同じ値になってしまうと、重複削除の前提が崩れます。後から表示形式を文字列へ変えても、失われた桁は復元できません。Microsoftの文字列としての保存に関する説明も確認してください。

15桁を超える番号は原本から文字列として取り込み直します。COUNTIFでも長い数字の条件を厳密に扱えるとは限らないため、末尾の異なる例で確認します。文字列を厳密に比較するならEXACTを使います。

CSVは取り込み時に番号列を文字列へ指定します。CSVの文字化けの直し方｜Excelの取り込みからゼロ落ち・保存後の確認までで、文字コードや番号を守る手順を確認できます。

大文字小文字を区別したい場合

COUNTIFとデータタブの重複の削除は、英字の大文字小文字を区別しません。CodeAとcodeaが別の識別子なら、そのまま一件にしないようにします。メールも使用先の仕様を確認せず小文字へ統一するのは避けます。

=EXACT(A2,A3)は大文字小文字も含めて同じ文字列ならTRUEを返します。文字列にそろえたA列で2回目以降を調べるには、補助列の式を下へコピーします。

=IF(A2="","",SUMPRODUCT(--EXACT($A$2:A2,A2)))

EXACTで各セルを比較し、SUMPRODUCTで一致数を合計します。2以上が2回目以降です。長い番号も文字列で保持されていることが前提です。計算負荷を抑えるため列全体は参照せず、実データの範囲で使います。比較の性質はEXACT関数の説明で確認できます。

COUNTIFのワイルドカード

COUNTIFの条件に使う*は任意の長さの文字列、?は任意の一文字を表します。A2に商品コードのAB*1が入っていると、セル参照でも星印が文字として扱われず、別のコードまで数える場合があります。記号を含むキーでは、単純なCOUNTIFを厳密な文字比較と考えないようにします。

星印そのものは~*、疑問符そのものは~?で指定します。チルダ自体も含めて保護する場合は、先にチルダを置換します。文字列のコードを数える式の例は次のとおりです。

=IF(A2="","",COUNTIF($A$2:$A$100,"="&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"~","~~"),"*","~*"),"?","~?")))

先頭の"="&は、コードの先頭にある比較記号も条件の演算子として使わないための指定です。この式でも大文字小文字の区別や長い番号の問題まで解決するわけではありません。COUNTIFSにも条件の解釈があるため、記号を含む条件は同様に確認します。MicrosoftのCOUNTIFの説明に、記号の意味と大文字小文字の扱いが掲載されています。

二つの表をCOUNTIF・XLOOKUPで照合する

存在するかと、何件あるかを先に調べる

今月と先月の名簿では、表内の重複と、表をまたぐ共通のキーを分けて考えます。「追加された人」「連絡済みの人を除いた一覧」など、欲しい結果を決めます。先月にいることだけで今月の行を削除しないようにします。

同じブックの「今月」「先月」のA列を、文字列のキーで照合する例です。記号や長い数字を含まないキーとし、今月側の補助列に入れます。

=IF(A2="","",COUNTIF('先月'!$A$2:$A$100,A2))

0なら先月になく、1なら1件、2以上なら複数あります。今月側に同じキーが2行あれば、両方に同じ件数が出ます。「先月だけ」の人も探すには、参照先を逆にした列を先月側へ作ります。

XLOOKUPで対応する項目を取り出す

対応版のXLOOKUPなら、キーが一致した行から氏名や所属を取り出せます。先月のB列が所属で、今月の所属と比べたい場合の例です。

=IF(A2="","",XLOOKUP(A2,'先月'!$A$2:$A$100,'先月'!$B$2:$B$100,"未登録",0))

最後の0は完全一致の指定です。通常は最初に一致した行の値を返すため、先月に同じキーが2件あっても分かりません。先に件数を数え、一件のキーについて所属を比較します。

「未登録」はキーが見つからない場合の表示で、所属欄が空欄の状態とは別です。所属だけで登録の有無を決めません。XLOOKUP関数のページで対応版も確認してください。Excel 2016・2019では利用できないため、共有先も確認します。

削除前後の確認とよくある失敗

隠れた行・結合セル・範囲の抜け

非表示の行も、重複の確認や関数の集計範囲に含まれることがあります。表示中の行だけが対象なら、原本を残し、可視セルだけを別の作業用表へコピーして件数を確認します。

結合セルのある一覧では、見た目と各行の実際の値が一致せず、並べ替えや削除も難しくなります。コピーで結合を解除し、原資料と照合してキーを入れます。上の値を全空欄へ埋めると、区切り行まで記録に変えてしまうので避けます。

途中に空行があると、自動選択が最終行まで届かない場合があります。追加分が範囲外になっていないか、最後の記録と列まで確認します。

件数と残った内容を両方確認する

見出しを除く同じ範囲で「作業前の件数−削除件数＝結果の件数」を確かめます。件数が合っても必要な行が残ったとは限らないため、キーが複数あった組の内容も見ます。

最新を残す条件なら、該当する更新日が選ばれたか確認します。連絡先の欠落や金額のある行の消失、数式の参照エラー、関連データの有無も確かめます。

毎月行うなら、キーの列、変換、並べ替えの順番、確認件数を残します。「重複を削除した」だけでは手順を再現できません。保留した組も残し、未確認の行を処理済みと誤解されないようにします。

まとめ

重複整理は、キーの定義、残す行の選択、値の確認で結果が決まります。色で候補を見るなら条件付き書式、回数や複数条件ならCOUNTIF・COUNTIFS、表の整理なら重複の削除を使います。別の一覧には詳細設定や対応版のUNIQUEを選べます。

消えない重複は、空白や文字幅を一括で消す前に、何が違うかを調べます。長い番号の桁が失われている場合は原本へ戻り、大文字小文字や記号が意味を持つキーは、その違いを残して比較します。まずは対象シートを複製し、判定したいキーを一列決めて、出現回数の補助列を作るところから始めてください。