表記ゆれの正規化｜見た目は同じなのに一致しない原因と直し方を体系的に解説し、JIS×TRIM×SUBSTITUTEでSUMIFを正しく動かす“データの揺れ”対策を実務目線で理解する
Excelでは、同じに見える文字でも内部コードが違うだけで一致判定が崩れます。
売上が合わない、SUMIFが返らない、VLOOKUPが一致しない──その多くが表記ゆれです。
半角・全角スペース、半角・全角カナ、不可視文字など、揺れは実務で必ず発生します。
この記事では、揺れの正体と、JIS・TRIM・SUBSTITUTEで正しく統一する方法を解説します。
■ データの揺れとは（内部コードの違い）
Excelでは、文字の見た目が同じでも、内部では別のコードとして扱われることがあります。
半角スペースと全角スペース、半角カナと全角カナ、不可視文字などが混在すると、
Excelは「同じ文字」と判断できず、SUMIFやVLOOKUPが一致しなくなります。
この内部コードの違いこそが、実務で売上が合わない原因の大半を占めています。
■ 揺れの代表例（実務で必ず発生するパターン）
・半角スペースと全角スペースの混在
・半角カタカナと全角カタカナの混在
・不可視文字（改行・制御文字）が紛れ込む
・濁点が結合文字として扱われるケース
・前後の余分なスペースが残っている
■ 揺れを除く正規化テクニック（関数で統一）
・スペースの統一：=SUBSTITUTE(セル,” ”,” “)
・カナの統一：=JIS(セル)
・不可視文字の除去：=CLEAN(セル)
・余分なスペース除去：=TRIM(セル)
・複数の揺れをまとめて除去：
=TRIM(SUBSTITUTE(JIS(セル),” ”,” “))
🧮実務例①：店舗名の揺れを正規化してSUMIF
■ なぜこの式で揺れが直るのか
表①の店名には、半角スペース・全角スペース・半角カナ・全角カナが混在しています。
このままでは Excel が「同じ店名」と判定できず、SUMIF が正しく集計できません。
そこで表②では、次の2段階で揺れを除いています。
① JIS関数で半角カナを全角カナに統一する
② SUBSTITUTEで全角スペースを“削除”して、余分な空白をなくす
今回の式はスペースを統一しているのではなく、
「全角スペースを空文字に置き換えて削除する」処理を行っています。
その結果、
『西 なんば店』『西 なんば店』などの表記ゆれがすべて
『西なんば店』という同一表記に正規化されます。
この“余分なスペースを削除して1つの表記に揃える”ことが、
SUMIFが正しく一致判定できる状態を作るポイントです。
■ 集計表③の仕組み
集計表③では、表②で正規化された店名を使って
「店名ごとの売上合計」を SUMIFS で求めています。
① =UNIQUE(B10#)
→ 正規化済みの店名（スピル範囲 B10#）から
重複を除いた店名リストを作成します。
② =SUMIFS(C10:C13, B10#, B20#)
→ 売上範囲 C10:C13 の中から、
店名範囲 B10# と一致する行の売上を合計します。
B20# は UNIQUE で作成した店名リストです。
この結果、
「西なんば店」「東エクストラ店」の2つの店名に対して
それぞれの売上合計が自動的にスピルして返されます。
❌ 揺れが起きるケース（SUMIFS・VLOOKUPが一致しない）
・中間スペースの違い（半角 / 全角）
例：鈴 木、鈴 木
・カナの種類が違う（半角 / 全角）
例：ｲｵﾝ、イオン
・英数字の種類が違う（半角 / 全角）
例：A123、Ａ１２３
・不可視文字が混入（改行・制御文字）
例：鈴木□（見えない文字）
・記号の種類が違う
例：-（ハイフン）とー（長音）と−（全角ハイフン）
・濁点が結合文字になっている
例：ば（結合） vs ば（通常）
✔ 揺れが起きないケース（SUMIFSが一致する）
・前後のスペース（末尾・先頭）
例：鈴木、鈴木␣、␣鈴木
・連続スペース（前後のみ）
例：鈴木␣␣␣
・見た目が同じで内部コードが同じもの
例：鈴木、鈴木（フォント違い）
今回のまとめ
■ データの揺れとは
Excelでは、見た目が同じでも内部コードが違うだけで一致判定が崩れます。
半角/全角スペース、半角/全角カナ、不可視文字などが混在すると、
SUMIFSやVLOOKUPが「同じ文字」と判断できなくなります。
■ 揺れの代表例
・半角スペースと全角スペースの混在
・半角カタカナと全角カタカナの混在
・半角/全角英数字の混在
・改行や制御文字などの不可視文字
■ 揺れを除く正規化の方法
・カナの統一：JIS(セル)
・スペースの統一／削除：SUBSTITUTE(セル,” ”,””)
・不可視文字の除去：CLEAN(セル)
・余分なスペース除去：TRIM(セル)
■ 代表的な揺れと直し方（関数一覧）
・全角スペース → 半角スペース =SUBSTITUTE(セル,” ”,” “)
・半角スペース → 全角スペース =SUBSTITUTE(セル,” “,” ”)
・半角カタカナ → 全角カタカナ =JIS(セル)
・全角カタカナ → 半角カタカナ =ASC(セル)
・不可視文字の除去 =CLEAN(セル)
・前後の余分なスペース除去 =TRIM(セル)
・複数の揺れをまとめて除去 =TRIM(SUBSTITUTE(JIS(セル),” ”,” “))
■ 正規化の重要性
データに半角/全角スペースや半角/全角カナなどの揺れが含まれていると、
SUMIFS や VLOOKUP は同じ文字列として判定できず、正しい集計結果が得られません。揺れを除いて表記を統一（正規化）することで、
検索範囲と検索条件が完全に一致するようになり、
SUMIFS や VLOOKUP が正しく一致判定を行えるようになります。その結果、同じ名称であるにもかかわらず揺れによって別データとして扱われていた値が、
正しく1つにまとめて集計されるようになります。
無料Excel教材ダウンロード｜関数練習・実務問題ファイル集の使い方
この記事の講習問題は、無料ファイル倉庫からダウンロードして、
実際のExcelシート上で手を動かしながら理解を深めてください。
安心して使える学習用ファイルで、初心者でもそのまま再現できます。
Excelの実務ロジックが自然と身につく、無料の学習教材です。
講習問題は全２問あります。理解できたら、黄色のセルに入っている計算式を一度消して、実際に自分で入力してみてください。
計算式を消しても、メモ欄に保存してある式を貼り付ければ元の状態を復元できます。
練習と復元を繰り返すことで、計算式の構造が安定して身につきます