Excelで商品名、会社名、カテゴリ名、部署名などを集計すると、同じ意味の値が別々の表記で入力されていることがあります。たとえば「ABC株式会社」「ＡＢＣ株式会社」「ABC(株)」「ABC 株式会社」は、人の目には近い値に見えても、Excelでは別の文字列です。
表記ゆれを見つける目的は、いきなり値を置換することではありません。まず、同じ意味かもしれない候補を抽出し、人が確認できる状態にすることが重要です。
本稿では一つのサンプル表を最後まで使い、全角半角、空白、法人格、記号、略称が混在した会社名を、正規化キーと候補件数で見つける方法を確認します。
| 正規化キー | =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(JIS(ASC(A2)),"株式会社",""),"（株）",""),"(株)","")," ","")," ","") |
|---|---|
| 候補件数 | =COUNTIF($D$2:$D$10,D2) |
| 候補抽出 | =FILTER(A2:D10,E2:E10>1) |
| 一括処理 | =MAP(A2:A10,LAMBDA(value,...)) |
最初に、記事全体で使用するサンプルデータを作る
A列に「元データ」、B列に「分類」、C列に「メモ」を入力します。同じ会社かもしれない値、別会社かもしれない値、略称の可能性がある値を混ぜています。
| A | B | C | |
|---|---|---|---|
| 1 | 元データ | 分類 | メモ |
| 2 | ABC株式会社 | 会社名 | 標準表記 |
| 3 | ＡＢＣ株式会社 | 会社名 | 全角英字 |
| 4 | ABC 株式会社 | 会社名 | 空白あり |
| 5 | ABC（株） | 会社名 | 法人格略記 |
| 6 | XYZ商事 | 会社名 | 標準表記 |
| 7 | ＸＹＺ商事株式会社 | 会社名 | 全角英字＋法人格 |
| 8 | 東海物産 | 会社名 | 標準表記 |
| 9 | 東海物産 名古屋支店 | 会社名 | 支店名つき |
| 10 | サンプル工業 | 会社名 | 単独候補 |
以降はA2:A10を固定して使用します。D列に正規化キー、E列に候補件数、F列に処理区分、G列以降に抽出・検証用の情報を追加します。
POINT
表記ゆれの候補抽出では、元データを直接書き換えません。元データを残したまま、比較用のキー列を別に作ります。
表記ゆれは、集計・検索・名寄せを不安定にする
A2:A5はいずれも「ABC」という会社を指している可能性があります。しかし、全角英字、空白、法人格の略記が違うため、そのままでは別々の値として扱われます。
- ピボットテーブルで同じ会社が複数行に分かれる
- COUNTIF・SUMIF・SUMIFSの集計対象が分散する
- XLOOKUPやVLOOKUPで一致しない
- UNIQUEで似た値が複数表示される
- 名寄せ時に同一候補を見落とす
表記ゆれを見つけるには、文字列をいきなり統一するのではなく、まず比較しやすい形へならしたキーを作ります。
正規化キーを作って、比較しやすい形へならす
D1に「正規化キー」と入力し、D2へ次の数式を入力してD10までコピーします。
| D2 | =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(JIS(ASC(A2)),"株式会社",""),"（株）",""),"(株)","")," ","")," ","") |
|---|
この式では、JIS(ASC())で英数字・記号の全角半角をならしたうえで、「株式会社」「（株）」「(株)」と半角・全角スペースを取り除きます。
| A | D | |
|---|---|---|
| 1 | 元データ | 正規化キー |
| 2 | ABC株式会社 | ＡＢＣ |
| 3 | ＡＢＣ株式会社 | ＡＢＣ |
| 4 | ABC 株式会社 | ＡＢＣ |
| 5 | ABC（株） | ＡＢＣ |
| 6 | XYZ商事 | ＸＹＺ商事 |
| 7 | ＸＹＺ商事株式会社 | ＸＹＺ商事 |
| 8 | 東海物産 | 東海物産 |
| 9 | 東海物産 名古屋支店 | 東海物産名古屋支店 |
| 10 | サンプル工業 | サンプル工業 |
正規化キーは、同一候補を見つけるための補助列です。キーが一致しても同一とは限らず、キーが一致しなくても別物とは限りません。
💡 JIS(ASC())を使う理由
ASCだけを使うと、カタカナを半角カナへ変換します。会社名や商品名ではカタカナを全角のまま扱いたいことが多いため、ここではASCで英数字や記号をならしたあと、JISで全角側へ戻す方針にしています。
候補件数を数えて、表記ゆれらしい値を見つける
E1に「候補件数」と入力し、E2へ次の数式を入力してE10までコピーします。
| E2 | =COUNTIF($D$2:$D$10,D2) |
|---|
正規化キーが同じ行数を数えます。候補件数が2以上であれば、表記ゆれの候補として確認する価値があります。
| A | D | E | |
|---|---|---|---|
| 1 | 元データ | 正規化キー | 候補件数 |
| 2 | ABC株式会社 | ＡＢＣ | 4 |
| 3 | ＡＢＣ株式会社 | ＡＢＣ | 4 |
| 4 | ABC 株式会社 | ＡＢＣ | 4 |
| 5 | ABC（株） | ＡＢＣ | 4 |
| 6 | XYZ商事 | ＸＹＺ商事 | 2 |
| 7 | ＸＹＺ商事株式会社 | ＸＹＺ商事 | 2 |
| 8 | 東海物産 | 東海物産 | 1 |
| 9 | 東海物産 名古屋支店 | 東海物産名古屋支店 | 1 |
| 10 | サンプル工業 | サンプル工業 | 1 |
ABCグループとXYZ商事グループは候補としてまとまりました。一方、東海物産と東海物産 名古屋支店はキーが異なるため、今回の単純なルールでは候補になりません。
FILTER関数で、確認すべき候補だけを抽出する
G1に「表記ゆれ候補」と入力し、G2へ次の数式を入力します。
| G2 | =FILTER(A2:E10,E2:E10>1) |
|---|
結果はG2:K7へスピルします。元データ、正規化キー、候補件数をまとめて確認できるため、置換や統合の前に人が判断できます。
| 元データ | 分類 | メモ | 正規化キー | 候補件数 |
|---|---|---|---|---|
| ABC株式会社 | 会社名 | 標準表記 | ＡＢＣ | 4 |
| ＡＢＣ株式会社 | 会社名 | 全角英字 | ＡＢＣ | 4 |
| ABC 株式会社 | 会社名 | 空白あり | ＡＢＣ | 4 |
| ABC（株） | 会社名 | 法人格略記 | ＡＢＣ | 4 |
| XYZ商事 | 会社名 | 標準表記 | ＸＹＺ商事 | 2 |
| ＸＹＺ商事株式会社 | 会社名 | 全角英字＋法人格 | ＸＹＺ商事 | 2 |
IMPORTANT
FILTERで抽出した行は、削除対象ではなく確認対象です。正規化キーが一致しているだけで、同一と確定したわけではありません。
処理区分を作り、確認対象と単独候補を分ける
F1に「処理区分」と入力し、F2へ次の数式を入力してF10までコピーします。
| F2 | =IF(E2>1,"表記ゆれ候補","単独") |
|---|
候補件数が2以上の行を「表記ゆれ候補」、それ以外を「単独」とします。
| A | E | F | |
|---|---|---|---|
| 1 | 元データ | 候補件数 | 処理区分 |
| 2 | ABC株式会社 | 4 | 表記ゆれ候補 |
| 3 | ＡＢＣ株式会社 | 4 | 表記ゆれ候補 |
| 4 | ABC 株式会社 | 4 | 表記ゆれ候補 |
| 5 | ABC（株） | 4 | 表記ゆれ候補 |
| 6 | XYZ商事 | 2 | 表記ゆれ候補 |
| 7 | ＸＹＺ商事株式会社 | 2 | 表記ゆれ候補 |
| 8 | 東海物産 | 1 | 単独 |
| 9 | 東海物産 名古屋支店 | 1 | 単独 |
| 10 | サンプル工業 | 1 | 単独 |
「単独」は問題なしという意味ではありません。今回の正規化ルールでは候補にならなかった、という意味です。東海物産と東海物産 名古屋支店のように、支店名をどう扱うかは別途ルール化が必要です。
LET・MAPで、正規化キーを一括作成する
ここからは、D列の正規化キーを別の書き方で作る例です。STEP 3のD2式と同じ結果を返しますが、正規化式は長くなりやすいため、LET関数で処理段階へ名前を付けると読みやすくなります。
| D2 | =LET(source,A2,normalized,JIS(ASC(source)),noCorp,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(normalized,"株式会社",""),"（株）",""),"(株)",""),SUBSTITUTE(SUBSTITUTE(noCorp," ","")," ","")) |
|---|
ここでは、元データをsource、全角半角をならした結果をnormalized、法人格を除いた結果をnoCorpとしています。LETによって結果が変わるわけではありません。D2:D10へコピーして使う正規化キーの式を、読みやすく書き換えたものです。
MAP関数を使うと、D2:D10へコピーしていた正規化キーを一つの式でまとめて作れます。D列の結果と同じものを、スピルで確認するための代替式です。M1に「MAP正規化キー」と入力し、M2へ次の数式を入力します。
| M2 | =MAP(A2:A10,LAMBDA(value,LET(normalized,JIS(ASC(value)),noCorp,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(normalized,"株式会社",""),"（株）",""),"(株)",""),SUBSTITUTE(SUBSTITUTE(noCorp," ","")," ","")))) |
|---|
結果はM2:M10へスピルします。これは新しい判定列ではなく、D列の正規化キーをMAP関数で一括作成した確認用の列です。STEP 5の候補抽出結果がG2:K7へスピルしているため、ここでは重ならないM列へ出力します。
※MAP関数を利用できないExcelでは、STEP 7の数式をD2:D10へコピーしてください。STEP 7とSTEP 8は、どちらもSTEP 3で作ったD列の正規化キーを置き換えるための書き方です。
表記ゆれ候補を見つけたあとに確認すること
最後に、今回の正規化キーで何が拾えたかを確認します。
| 候補数 | =COUNTIF(F2:F10,"表記ゆれ候補") |
|---|---|
| 単独数 | =COUNTIF(F2:F10,"単独") |
| 候補抽出 | =FILTER(A2:F10,F2:F10="表記ゆれ候補") |
このサンプルでは、ABCグループ4件、XYZ商事グループ2件が表記ゆれ候補として見つかります。一方、東海物産と東海物産 名古屋支店は、今回のルールでは別キーになります。なお、候補抽出の式はSTEP 5と異なり、F列（処理区分）を追加した後の範囲（A2:F10）で絞り込んでいます。
PRACTICAL NOTE
表記ゆれ検出は、正規化ルールによって結果が変わります。法人格を除く、支店名を除く、記号を除く、略称を辞書で置き換えるなど、どこまで同じとみなすかを先に決めます。
まとめ ―― 表記ゆれは、置換する前に候補として抽出する
本稿ではA2:A10の同じ会社名リストを使い、正規化キーの作成、候補件数の確認、FILTERによる候補抽出、処理区分の作成まで進めました。LET関数とMAP関数は、どちらもD列の正規化キーを読みやすく、または一括で作るための代替表現です。
| 正規化キー | =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(JIS(ASC(A2)),"株式会社",""),"（株）",""),"(株)","")," ","")," ","") |
|---|---|
| 候補件数 | =COUNTIF($D$2:$D$10,D2) |
| 処理区分 | =IF(E2>1,"表記ゆれ候補","単独") |
| 候補抽出 | =FILTER(A2:E10,E2:E10>1) |
| MAP一括 | =MAP(A2:A10,LAMBDA(value,...)) |
- 表記ゆれ：同じ意味かもしれない値が、異なる文字列として入力されている状態。
- 正規化キー：全角半角、空白、法人格などをならした比較用のキー。
- 候補件数：同じ正規化キーを持つ値の件数。2以上なら確認対象になる。
- 候補抽出：FILTERで確認すべき値だけを取り出す。
- 単独：今回のルールでは候補にならなかった値。問題なしとは限らない。
- 確認：正規化キーの一致は同一確定ではない。置換・統合前に人が確認する。
表記ゆれを見つける目的は、機械的に値を置換することではありません。同じ意味かもしれない値を候補として集め、誤統合を避けながら確認できる状態にすることが重要です。