Excelで表記ゆれと重複を見つける方法

この記事は、Excelで顧客リストや名簿を管理しているご担当者に向けて書きました。

同じ会社が別の書き方で何行も入り、件数が合わないという困りごとに答えます。

関数と条件付き書式で、表記ゆれと重複を見つける手順をご紹介します。

■ 作業の順番：先に書き方を揃え、その後に重複を探す

表記ゆれ（同じ内容で書き方が異なる状態）と重複（同じデータが2行以上ある状態）は、別の問題です。

例えば次の3つは同じ会社ですが、Excelは別の値として数えます。

・株式会社サンプル

・（株）サンプル

・㈱サンプル

このため、先に書き方を揃え、その後に重複を探す順番が大切です。

作業の前に元のシートをコピーし、揃えた結果は隣の列に入れます。

元の値と見比べられるため、揃え方の誤りを確かめられます。

■ TRIM関数：余分な空白を取り除く

TRIM関数（文字の前後の空白を取り除き、語の間の空白を1つにする関数）で空白を整えます。

A列に会社名がある場合、B2セルに次の式を入れて、下の行までコピーします。

=TRIM(A2)

全角の空白が混ざる場合は、SUBSTITUTE関数（指定した文字を別の文字に置き換える関数）と組み合わせます。

=TRIM(SUBSTITUTE(A2,"　"," "))

この式は、全角の空白を半角に置き換えてから、余分な空白を取り除きます。

■ ASC関数とJIS関数：全角と半角を揃える

ASC関数（全角の英数字やカタカナを半角にする関数）と、JIS関数（半角の文字を全角にする関数）を使用します。

比較用の列では、項目ごとにどちらに揃えるかを決めておくことが大切です。

・電話番号や郵便番号：ASC関数で半角に揃える

・会社名や住所：JIS関数で全角に揃える

TRIM関数と組み合わせる場合は、次のように書きます。

=JIS(TRIM(A2))

電話番号には、ハイフンに似た記号が何種類も混ざることがあります。

その場合は、次に紹介する置換の機能で1つの記号に揃えてから比べます。

■ 置換の機能：「株式会社」の書き方を揃える

関数で揃えた列は、コピーして「値」として貼り付けてから置換します。

式が入ったままのセルでは、表示されている文字は置き換わりません。

置換の前に、比較用の列だけを選んでおきます。

ホームタブの「検索と選択」から「置換」を開きます。

「検索する文字列」に（株）、「置換後の文字列」に株式会社を入れ、「すべて置換」を押します。

・括弧の全角と半角：（株）と(株)の両方を置き換える

・1文字の表記：㈱も同じ手順で置き換える

・有限会社など：（有）や㈲も同じ手順で揃える

■ COUNTIF関数：同じ値の数を数える

COUNTIF関数（条件に合うセルの数を数える関数）で、同じ値が何回出てくるかを数えます。

揃えた会社名がB列にある場合、C2セルに次の式を入れて、下の行までコピーします。

=COUNTIF($B$2:$B$1001,B2)

結果が2以上の行は、同じ会社名がリストに2行以上あることを示します。

範囲の1001は、データの最後の行の番号に合わせて変えます。

2回目以降に出てくる行だけに印を付けたい場合は、次の式を使用します。

=COUNTIF($B$2:B2,B2)

範囲が1行ずつ下に伸びるため、最初に出てくる行は1、2回目以降の行は2以上になります。

会社名だけで比べると、同じ名前の別の会社も重複として数えられます。

住所も一致するかを確かめるには、COUNTIFS関数（複数の条件に合うセルの数を数える関数）を使用します。

=COUNTIFS($B$2:$B$1001,B2,$D$2:$D$1001,D2)

この例では、B列が会社名、D列が住所です。

■ 条件付き書式：重複に色を付ける

条件付き書式（条件に合うセルに色を付ける機能）を使用すると、重複を目で確かめられます。

揃えた会社名が入ったB列を選び、ホームタブの「条件付き書式」を開きます。

「セルの強調表示ルール」から「重複する値」を選ぶと、重複したセルに色が付きます。

行全体に色を付けたい場合は、見出しを除いた表全体を選び、「条件付き書式」の「新しいルール」を開きます。

「数式を使用して、書式設定するセルを決定」を選び、次の式を入れます。

=COUNTIF($B$2:$B$1001,$B2)>1

列の記号の前にだけ$を付けると、どの列のセルもB列の会社名を基準に判定されます。

■ 削除の前に：他の項目も見比べて判断する

色が付いた行は、削除する前に他の項目も見比べて判断します。

・会社名が同じで住所が異なる場合は、支店や別の会社の可能性があります

・電話番号やWebサイトのURLも一致すれば、同じ会社の可能性が高くなります

残す行が決まったら、データタブの「重複の削除」を使用する方法もあります。

「重複の削除」は、選んだ列の値が全て一致する行のうち、最初の行を残して他の行を削除します。

■ まとめ

表記ゆれと重複の整理は、書き方を揃えてから重複を探す順番で進めます。

・TRIM・ASC・JIS関数と置換で、書き方を揃える

・COUNTIF関数と条件付き書式で、重複を探す

・削除の前に、他の項目も見比べて判断する

私はこれまで、数万行規模の業務データを集計し、店舗別・製品別の集計表を作成してきました。

リストの整理をお引き受けする際も、納品前に件数・重複・表記ゆれ・空欄を確認してからお渡しします。

リストの整理や重複の削除のご依頼、ご相談は、プロフィールのサービス一覧から受け付けております。