表記ゆれ(全角半角・株式会社)を関数でそろえる方法と限界【計算して確認済み】
顧客名簿や取引先リストで、「(株)山田商事」と「株式会社山田商事」が別のデータとして扱われてしまい、集計やVLOOKUPが合わない。こうした表記ゆれは、実務でとても多い困りごとです。この記事では、関数だけでそろえられる範囲と、そうでない範囲を、実際に計算して確かめた結果で整理します。
先に結論
- 全角英数を半角にするには
ASC、半角カナを全角にするにはJISが使えます。ただし、どちらも、ほしくない文字まで変えてしまいます。 - スペースの整理は、
TRIMとSUBSTITUTEの組み合わせで確実にできます。 - 「(株)」「㈱」を「株式会社」にそろえるのは、
SUBSTITUTEの入れ子で行えます。全角カッコと半角カッコは別の文字なので、両方を書く必要があります。 - 「全角英数は半角、カナは全角」という、よく望まれるルールを1つの関数で実現することは、確認した範囲ではできませんでした。
確認したデータ
次の8つのデータを、A列に入れて計算しました。
| 行 | 元のデータ(A列) | 内容 |
|---|---|---|
| 1 | (株)山田商事 | 半角カッコの(株) |
| 2 | （株）山田商事 | 全角カッコの（株） |
| 3 | ㈱山田商事 | 1文字の㈱ |
| 4 | 株式会社 山田商事 | 間に半角スペース |
| 5 | Ａ１２３-ｂｃ | 全角の英数字 |
| 6 | ﾔﾏﾀﾞ ﾀﾛｳ | 半角カナ |
| 7 | 山田 太郎 | 間に全角スペース |
| 8 | ␣山田商事␣ | 前後に半角スペース(␣で表示) |
ASCとJISの動き(確認した結果)
ASCは全角を半角に、JISは半角を全角にする関数です。Microsoftの公式ページにも、ASCが全角カナを半角カナに変える例が載っています(この関数は、2バイト文字を使う言語の環境向けのものとされています)。計算した結果は次のとおりでした。
| 元のデータ | =ASC(A)の結果 |
=JIS(A)の結果 |
|---|---|---|
| Ａ１２３-ｂｃ | A123-bc(全角英数が半角に) | Ａ１２３－ｂｃ(ハイフンも全角に) |
| ﾔﾏﾀﾞ ﾀﾛｳ | ﾔﾏﾀﾞ ﾀﾛｳ(変化なし) | ヤマダ タロウ(全角カナに) |
| ヤマダ タロウ | ﾔﾏﾀﾞ ﾀﾛｳ(半角カナになる) | ヤマダ タロウ(変化なし) |
| ＡＢＣ123 ヤマダ | ABC123 ﾔﾏﾀﾞ(カナまで半角に) | ＡＢＣ１２３ ヤマダ(数字まで全角に) |
つまり、ASCで全角英数を半角にそろえると、全角カナまで半角カナになってしまいます。逆にJISでカナを全角にすると、半角の英数字まで全角になります。「英数は半角、カナは全角」にしたい名簿では、どちらを使っても、ほしくない変換が起きます。
なお、㈱のような1文字の記号は、ASCでもJISでも変化しませんでした。
スペースの整理
TRIMは、前後のスペースと、連続したスペースを1つにします。ただし、全角スペースは、そのままでは消えません。次のように、先に全角スペースを半角に置き換えます。
=TRIM(SUBSTITUTE(A7," "," "))
行7の「山田 太郎」(全角スペース)は、この式で「山田 太郎」(半角スペース1つ)になりました。行8の前後のスペースも、TRIMで取り除かれ「山田商事」になりました。
姓名の間のスペースまで消したい場合は、さらにSUBSTITUTE(…," ","")を重ねます。「山田 太郎 様」は、SUBSTITUTEで「山田太郎様」になりました。
「(株)」を「株式会社」にそろえる
次の式で、3種類の「株」の書き方が、すべて「株式会社」になりました。
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(株)","株式会社"),"（株）","株式会社"),"㈱","株式会社")
行1・行2・行3は、すべて「株式会社山田商事」になり、EXACTで比べて、行1と一致する(TRUE)ことを確認しました。ここで大事なのは、半角カッコ「(」と全角カッコ「（」を、別々に書くことです。片方だけでは、もう片方の表記が残ります。
行4の「株式会社 山田商事」は、間にスペースがあるため、このままでは、行1と一致しません。スペースの整理(上の節)を重ねて、初めてそろいます。
数えて確認する
そろえたあとで、同じ会社が何件あるかを数えると、整形が効いたかを確認できます。次の式は、「株式会社山田商事」になったデータの数を返します。
=COUNTIF(E1:E4,"株式会社山田商事")
行1〜行3の整形後の結果をまとめると、結果は3になりました。SUMPRODUCT(--(E1:E4="株式会社山田商事"))でも、同じ3になることを確認しました。
関数だけでは難しいこと
- 「英数は半角、カナは全角」を同時に実現すること。 上のとおり、
ASCとJISのどちらも、一方を直すと他方が崩れます。 - 社名の言い換え。 「山田商事」と「ヤマダ商事」を同じものと判定することは、関数では扱えません。
- 会社名の前後どちらに「株式会社」があるか(前株・後株)の統一。
- 住所の表記ゆれ(丁目・番地の書き方など)。
こうした場面では、置換の一覧表を作ってルールを増やす方法や、専用のツールを使う方法があります。当サイトでは、ブラウザの中だけで処理する、無料の整形ツールも用意しています(入力したデータは外部に送信されません)。結果は、必ず目で確認してからお使いください。
まとめ
- スペースと「(株)」の統一は、
TRIM・SUBSTITUTEの組み合わせで、確実にできます。 ASCとJISは、ほしくない文字まで変えるので、対象のデータを確認してから使います。- 全角と半角のカッコは別の文字なので、両方を置換の対象にします。
- 整形したあとは、
COUNTIFで件数を数えて、効果を確かめます。