商品コード、顧客ID、メールアドレス、会社名、カナ表記などを集計すると、同じ内容なのに全角と半角が混在していることがあります。たとえば 「ＡＢＣ１２３」 と 「ABC123」、「ｶﾀｶﾅ」 と 「カタカナ」は、人の目には同じ意味に見えてもExcelでは異なる文字列です。
この状態を放置すると、XLOOKUPで一致しない、UNIQUEで重複候補が増える、COUNTIFの件数が分かれる、名寄せで同一候補を見落とす、といった問題が起こります。
本稿では、最初に一つのサンプル表を作り、その同じ表へASC・JIS・LET・MAP・EXACTを順に適用します。途中で例を切り替えず、半角へ統一する列、全角へ統一する列、機械的に変換しない列の違いまで確認します。
| 半角化 | =ASC(A2) |
|---|---|
| 全角化 | =JIS(A2) |
| 用途別 | =IF(B2="検索キー",ASC(A2),IF(B2="カナ表記",JIS(A2),A2)) |
| 一括処理 | =MAP(A2:A9,B2:B9,LAMBDA(value,purpose,IF(purpose="検索キー",ASC(value),IF(purpose="カナ表記",JIS(value),value)))) |
最初に、記事全体で使用するサンプルデータを作る
A列に「元データ」、B列に「用途」を入力します。検索キーやコードは半角へ、カナ表記は全角へ、表示名は原文を維持するという判断ができるよう、用途の異なるデータを混ぜています。
| A | B | |
|---|---|---|
| 1 | 元データ | 用途 |
| 2 | ＡＢＣ１２３ | 検索キー |
| 3 | ABC123 | 検索キー |
| 4 | ｶﾀｶﾅ | カナ表記 |
| 5 | カタカナ | カナ表記 |
| 6 | ﾊﾟﾋﾟﾌﾟﾍﾟﾎﾟ | カナ表記 |
| 7 | Ｔｅｓｔ １２３ | 検索キー |
| 8 | 株式会社ＡＢＣ | 表示名 |
| 9 | user＠example．com | 検索キー |
以降はA2:A9を固定して使用します。C列へASC、D列へJIS、E列へ用途別の最終結果、F列以降へ検証結果を追加します。
POINT
全角・半角の統一では、最初に「どちらへそろえるか」を決めます。変換関数を先に選ぶのではなく、列の用途を先に決めることが重要です。
全角・半角の混在は、検索・集計・重複判定を不安定にする
A2の「ＡＢＣ１２３」とA3の「ABC123」は、意味としては同じ商品コードかもしれません。しかし文字コードが異なるため、そのままでは完全一致しません。
- XLOOKUP・VLOOKUP・MATCHで一致しない
- COUNTIF・SUMIFで同じカテゴリが分かれる
- UNIQUEで重複候補が複数表示される
- 重複の削除で実質的に同じ行が残る
- メールアドレスや商品コードの照合に失敗する
- 半角カナと全角カナが別カテゴリになる
したがって、全角・半角の統一は見た目を整える作業ではなく、同じ意味の値を同じ比較単位へそろえるデータクレンジングです。
ASC関数で英数字・カタカナを半角へ統一する
C1に「ASC後」と入力し、C2へ次の数式を入力してC9までコピーします。
| C2 | =ASC(A2) |
|---|
ASC関数は、全角の英数字やカタカナを半角へ変換します。ひらがなや漢字など、対応する半角文字がない文字は基本的にそのまま残ります。
| A | B | C | |
|---|---|---|---|
| 1 | 元データ | 用途 | ASC後 |
| 2 | ＡＢＣ１２３ | 検索キー | ABC123 |
| 3 | ABC123 | 検索キー | ABC123 |
| 4 | ｶﾀｶﾅ | カナ表記 | ｶﾀｶﾅ |
| 5 | カタカナ | カナ表記 | ｶﾀｶﾅ |
| 6 | ﾊﾟﾋﾟﾌﾟﾍﾟﾎﾟ | カナ表記 | ﾊﾟﾋﾟﾌﾟﾍﾟﾎﾟ |
| 7 | Ｔｅｓｔ １２３ | 検索キー | Test 123 |
| 8 | 株式会社ＡＢＣ | 表示名 | 株式会社ABC |
| 9 | user＠example．com | 検索キー | user@example.com |
A2・A7・A9のような検索キーでは、半角化によって照合しやすい形になります。A4は元から半角カナのため変化しませんが、A5の全角カナは半角カナへ変換されます。どちらも結果は「ｶﾀｶﾅ」ですが、変換の有無が異なります。表示用のカナ列へASCを無条件に適用すると、全角カナが半角カナに変わるため適切とは限りません。
PRACTICAL NOTE
商品コード、顧客ID、メールアドレス、URL、検索用キーなどは、半角へ統一する方が扱いやすいことが多くなります。
JIS関数で英数字・カタカナを全角へ統一する
D1に「JIS後」と入力し、D2へ次の数式を入力してD9までコピーします。
| D2 | =JIS(A2) |
|---|
JIS関数は、半角の英数字やカタカナを全角へ変換します。半角カナの濁点・半濁点も、全角カナとして結合された形へ変換されます。
| A | B | D | |
|---|---|---|---|
| 1 | 元データ | 用途 | JIS後 |
| 2 | ＡＢＣ１２３ | 検索キー | ＡＢＣ１２３ |
| 3 | ABC123 | 検索キー | ＡＢＣ１２３ |
| 4 | ｶﾀｶﾅ | カナ表記 | カタカナ |
| 5 | カタカナ | カナ表記 | カタカナ |
| 6 | ﾊﾟﾋﾟﾌﾟﾍﾟﾎﾟ | カナ表記 | パピプペポ |
| 7 | Ｔｅｓｔ １２３ | 検索キー | Ｔｅｓｔ １２３ |
| 8 | 株式会社ＡＢＣ | 表示名 | 株式会社ＡＢＣ |
| 9 | user＠example．com | 検索キー | ｕｓｅｒ＠ｅｘａｍｐｌｅ．ｃｏｍ |
A4・A6のようなカナ表記では、JISによって全角カナへそろえられます。一方、メールアドレスやコードまで全角になるため、検索キーへ一律適用するのは不向きです。
💡 全角化と半角化は、互いの完全な逆変換ではない
文字列には、変換対象外の漢字・ひらがな・記号が混在します。また、見た目の似たハイフンや長音符などは、ASC・JISだけでは同じ文字へそろわない場合があります。全角・半角の統一と、記号の表記ゆれ統一は別の作業として考えます。
列の用途に応じて、半角化・全角化・維持を使い分ける
同じ列へASCまたはJISを無条件に適用するのではなく、B列の用途を使って処理を分けます。E1に「用途別の最終結果」と入力し、E2へ次の数式を入力してE9までコピーします。
| E2 | =IF(B2="検索キー",ASC(A2),IF(B2="カナ表記",JIS(A2),A2)) |
|---|
| A | B | E | |
|---|---|---|---|
| 1 | 元データ | 用途 | 用途別の最終結果 |
| 2 | ＡＢＣ１２３ | 検索キー | ABC123 |
| 3 | ABC123 | 検索キー | ABC123 |
| 4 | ｶﾀｶﾅ | カナ表記 | カタカナ |
| 5 | カタカナ | カナ表記 | カタカナ |
| 6 | ﾊﾟﾋﾟﾌﾟﾍﾟﾎﾟ | カナ表記 | パピプペポ |
| 7 | Ｔｅｓｔ １２３ | 検索キー | Test 123 |
| 8 | 株式会社ＡＢＣ | 表示名 | 株式会社ＡＢＣ |
| 9 | user＠example．com | 検索キー | user@example.com |
| 検索キー | 英数字・記号を半角へ統一し、照合しやすくする |
|---|---|
| カナ表記 | 半角カナを全角カナへ統一し、読みやすくする |
| 表示名 | 正式表記を尊重し、機械的に変換しない |
データクレンジングでは、すべてを同じ規則で変換するより、列の役割ごとに正規化方針を決める方が安全です。
STEP 5のIF式はネストが深くなりやすいため、分岐が増える場合はIFS関数で書き直すと条件と処理の対応が読みやすくなります。
| E2 | =IFS(B2="検索キー",ASC(A2),B2="カナ表記",JIS(A2),B2="表示名",A2,TRUE,A2) |
|---|
最後の TRUE,A2 は、登録していない用途が入力された場合に元データを返す安全策です。想定外の用途を無理に変換しない設計にしています。
LET・MAPで用途別の変換を読みやすく、一括化する
LET関数を使うと、元データと用途へ英語の変数名を付けられます。処理が長くなっても、どの値を判定しているか追いやすくなります。
| E2 | =LET(value,A2,purpose,B2,IFS(purpose="検索キー",ASC(value),purpose="カナ表記",JIS(value),purpose="表示名",value,TRUE,value)) |
|---|
変数名は記事内で value と purpose に統一します。LET関数によって結果が変わるわけではありませんが、処理の意味が数式内に残ります。
F1に「MAP一括処理」と入力し、F2へ次の数式を入力します。
| F2 | =MAP(A2:A9,B2:B9,LAMBDA(value,purpose,IFS(purpose="検索キー",ASC(value),purpose="カナ表記",JIS(value),purpose="表示名",value,TRUE,value))) |
|---|
結果はF2:F9へスピルします。数式を行ごとにコピーせず、用途別の正規化ルールを一か所で管理できます。
💡 MAPは変換規則の管理場所を一つにする
ASCやJISの機能が強くなるわけではありません。利点は、列全体へ同じ判断規則を適用し、条件変更があったときに一つの数式だけを修正すれば済むことです。
変換後の一致・変更・重複を検証する
G1に「変更判定」と入力し、G2へ次の数式を入力してG9までコピーします。
| G2 | =IF(EXACT(A2,E2),"変更なし","修正あり") |
|---|
EXACT関数は、全角・半角を含めて文字列が完全に同じかどうかを判定します。見た目の似た文字を区別して検証したい場合に適しています。
| A | B | E | G | |
|---|---|---|---|---|
| 1 | 元データ | 用途 | 最終結果 | 変更判定 |
| 2 | ＡＢＣ１２３ | 検索キー | ABC123 | 修正あり |
| 3 | ABC123 | 検索キー | ABC123 | 変更なし |
| 4 | ｶﾀｶﾅ | カナ表記 | カタカナ | 修正あり |
| 5 | カタカナ | カナ表記 | カタカナ | 変更なし |
| 6 | ﾊﾟﾋﾟﾌﾟﾍﾟﾎﾟ | カナ表記 | パピプペポ | 修正あり |
| 7 | Ｔｅｓｔ １２３ | 検索キー | Test 123 | 修正あり |
| 8 | 株式会社ＡＢＣ | 表示名 | 株式会社ＡＢＣ | 変更なし |
| 9 | user＠example．com | 検索キー | user@example.com | 修正あり |
全角・半角を統一すると、A2とA3、A4とA5のように、それまで別文字列だったデータが同じ値になります。H1に「完全一致件数」と入力し、H2へ次の数式を入力してH9までコピーします。
| H2 | =SUMPRODUCT(--EXACT($E$2:$E$9,E2)) |
|---|
件数が2以上なら、正規化後に完全に同じ文字列が複数存在します。EXACT関数を使っているため、全角・半角だけでなく英字の大文字・小文字も区別して判定します。これは誤りとは限りません。むしろ、表記ゆれによって隠れていた重複候補が見つかった状態です。
IMPORTANT
正規化後に重複したからといって、すぐに行を削除してはいけません。同じ商品コードや同じカナ表記でも、別のレコードである可能性があります。重複削除と名寄せは、全角・半角統一の次に行う別工程です。
まとめ ―― 全角・半角は、列の用途を決めてから統一する
本稿ではA2:A9の同じサンプルデータを使い、ASCによる半角化、JISによる全角化、用途別の最終処理、LET・MAPによる一括化、EXACT・SUMPRODUCTによる厳密な検証まで進めました。
| 半角化 | =ASC(A2) |
|---|---|
| 全角化 | =JIS(A2) |
| 用途別 | =IF(B2="検索キー",ASC(A2),IF(B2="カナ表記",JIS(A2),A2)) |
| LET版 | =LET(value,A2,purpose,B2,IFS(purpose="検索キー",ASC(value),purpose="カナ表記",JIS(value),purpose="表示名",value,TRUE,value)) |
| MAP一括 | =MAP(A2:A9,B2:B9,LAMBDA(value,purpose,IFS(purpose="検索キー",ASC(value),purpose="カナ表記",JIS(value),purpose="表示名",value,TRUE,value))) |
| 変更判定 | =IF(EXACT(A2,E2),"変更なし","修正あり") |
| 重複確認 | =SUMPRODUCT(--EXACT($E$2:$E$9,E2)) |
- 検索キー・コード・メールアドレス：ASCで半角へ統一する。
- カナ表記：JISで全角カナへ統一する。
- 会社名などの表示名：正式表記を変える可能性があるため、原文維持も選択肢にする。
- 記号の表記ゆれ：ASC・JISだけで解決しない文字は、SUBSTITUTEなどによる別処理を検討する。
- 一括処理：LETで読みやすくし、MAPで範囲全体へ同じ規則を適用する。
- 検証：EXACTで変更を確認し、SUMPRODUCTとEXACTで正規化後の完全一致件数を確認する。
全角・半角統一の目的は、すべての文字を機械的に同じ幅へ変えることではありません。比較・検索・表示という列の目的に合わせ、同じ意味の値を同じ形式へそろえることが重要です。