データ整形・名寄せ・クレンジング
表記ゆれ・余分な空白・全角半角・重複が集計を壊す。TRIM/CLEAN/SUBSTITUTE/REGEX/UNIQUE/SPLIT/QUERY を使い「汚いデータ」を分析できる形に整える型と、原本を残す名寄せ手順を実務目線でまとめます。
集計が合わない原因の多くは「数式」ではなく「データ」にあります。 「営業部」と「営業部 」（末尾に空白）、「ＡＢＣ」と「ABC」（全角半角）、「(株)」と「株式会社」——人間には同じに見えても、スプレッドシートは別物として扱います。その結果、SUMIF の合計が抜けたり、COUNTIF の件数が二重に出たりします。本章は、こうした「汚いデータ」を分析できる形に整える定番の型を、実務の手順とともにまとめます。
扱うのは次の流れです。
- なぜクレンジングが必要か（表記ゆれが集計を壊す仕組み）
- 空白・不可視文字を落とす（TRIM / CLEAN / SUBSTITUTE）
- 表記ゆれを統一する（SUBSTITUTE 連鎖・UPPER/LOWER・REGEXREPLACE）
- 重複を扱う（UNIQUE / COUNTIF / メニュー「重複を削除」）
- 文字列を分割・抽出する（SPLIT / LEFT・MID・RIGHT / REGEXEXTRACT）
- 結合する（
&/ TEXTJOIN） - 縦横変換・整列（TRANSPOSE / QUERY / FLATTEN）
- 複数の表をまとめる（配列リテラル
{}） - 突合・差分を出す（名寄せの型）
- そもそも入口で汚さない（入力規則）
クレンジングは 第3章 壊れない設計 と表裏一体です。整形は「汚れてしまったデータの後始末」、設計は「最初から汚さない仕組みづくり」。本章は前者を扱い、最後に後者へ橋渡しします。なお、正規表現そのものを基礎から完全に理解したい人は、独立した専用章 正規表現 完全ガイド（RE2） を用意しています。
1. なぜクレンジングが必要か
スプレッドシートの一致判定は厳密です。見た目が同じでも、内部の文字が1つでも違えば「別の値」と判定されます。集計を壊す典型は次の4つです。
| 汚れの種類 | 具体例 | 起きること |
|---|---|---|
| 余分な空白 | 営業部 と 営業部 | SUMIF で別グループに分かれ合計が割れる |
| 全角・半角 | ABC123 と ＡＢＣ１２３ | キーが一致せず VLOOKUP が #N/A |
| 表記ゆれ | (株) ㈱ 株式会社 | 同じ会社が3社にカウントされる |
| 不可視文字 | コピペで混入した改行・制御文字 | 目視では原因が分からない不一致 |
2. 空白・不可視文字を落とす
最初の一手は空白と制御文字の除去です。掃除の三点セットを押さえましょう。
| 関数 | 役割 | 構文 | 例 |
|---|---|---|---|
| TRIM | 前後の空白＋連続空白を1つに | TRIM(文字列) | =TRIM(A2) |
| CLEAN | 印字できない制御文字を除去 | CLEAN(文字列) | =CLEAN(A2) |
| SUBSTITUTE | 指定文字を消す／置き換える | SUBSTITUTE(文字列, 検索, 置換) | =SUBSTITUTE(A2, " ", "") |
TRIM は「前後の空白」と「単語間の連続した空白」を整えますが、これは 半角スペース が対象です。全角スペース（ ）は TRIM では消えない 点に注意してください。全角空白は SUBSTITUTE で名指しして除去します。
氏名（A2）に半角・全角の空白や改行が混在しているケース。
=TRIM(A2) — 半角空白は整うが、全角空白「 」は残る
=TRIM(SUBSTITUTE(A2, " ", " ")) — 全角空白を半角に変えてから TRIM で整える
=TRIM(CLEAN(SUBSTITUTE(A2, " ", " "))) — 制御文字も含めて一括で掃除
空白を「すべて無くしたい」（例：電話番号の区切り）なら =SUBSTITUTE(A2, " ", "") のように置換先を空文字 "" にします。全角・半角の両方を消すなら SUBSTITUTE を2段重ねます（次節）。
3. 表記ゆれを統一する
SUBSTITUTE の連鎖
複数の置換をしたいときは SUBSTITUTE を入れ子にします。内側から順に処理されます。
電話番号（A2）から区切りの「-」「(」「)」と空白を一掃する。
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"-",""),"(",""),")","")
=REGEXREPLACE(A2, "[-()\s]", "") — 同じことを正規表現で簡潔に（後述）
大文字・小文字をそろえる
英字キーの突合では UPPER（大文字化）か LOWER（小文字化）でどちらかにそろえると、abc と ABC の不一致を防げます。並べ替えやマスタ照合の前処理として有効です。
REGEXREPLACE で柔軟に置換
REGEXREPLACE(文字列, 正規表現, 置換) は、パターンに合う箇所をまとめて置換します。「数字以外を全部消す」「連続する空白を1つにする」といった、SUBSTITUTE では手数のかかる処理を1本で書けます。Google スプレッドシートの REGEX 系（REGEXEXTRACT / REGEXMATCH / REGEXREPLACE）は RE2 構文 です。
=REGEXREPLACE(A2, "[^0-9]", "") — 数字以外をすべて削除（電話番号の正規化）=REGEXREPLACE(A2, "\s+", " ") — 連続する空白を半角1つにまとめる 4. 重複を扱う
UNIQUE で一意な値を取り出す
UNIQUE(範囲) は、範囲から重複を除いた値を返します。元データはそのままに、別の場所へ「重複なしのリスト」をスピル（自動展開）できるのが利点です。
=UNIQUE(A2:A1000) — A列から重複を除いた一覧を返す（元データは変更しない）=SORT(UNIQUE(A2:A1000)) — ついでに並べ替えて見やすく COUNTIF で重複に「フラグ」を立てる
どの行が重複なのかを残したまま把握したいときは、COUNTIF で出現回数を数えます。2 以上なら重複です。
A列にコードがある表で、B2 にフラグ用の数式を入れて下までコピー。
=COUNTIF(A:A, A2) — A2 と同じ値が表全体に何個あるか（2以上＝重複）
=IF(COUNTIF(A$2:A2, A2)>1, "重複", "") — 上から見て2件目以降だけ「重複」と表示
範囲の片側だけを A$2:A2 と固定する2つめの書き方は、「最初の1件は残し、2件目以降を重複としてマークする」ときの定番です。参照の固定（$）は 第4章 関数の文法と参照 を参照してください。
メニューの「重複を削除」
恒久的に重複行を消したいなら、メニューの ［データ］→［データ クリーンアップ］→［重複を削除］ が手軽です。ただし 元データを直接書き換える破壊的な操作 なので、必ず原本のコピーに対して行ってください（手順は本章末の Callout 参照）。
5. 文字列を分割・抽出する
SPLIT — 区切り文字で分ける
SPLIT(文字列, 区切り文字) は、1セルの文字列を区切り文字で複数セルに分解します。住所やCSV由来の連結データをばらすのに便利です。
=SPLIT(A2, ",") — カンマ区切りを複数セルに分割=SPLIT("田中,営業部,東京", ",") → 田中 営業部 東京 の3セルに展開 LEFT / MID / RIGHT — 位置で取り出す
桁数が決まったコードなら、位置で切り出すのが確実です。LEFT(文字列, 文字数)、MID(文字列, 開始位置, 文字数)、RIGHT(文字列, 文字数) を使います。たとえば商品コード 2026-A-001 から年を取るなら =LEFT(A2, 4) です。
REGEXEXTRACT — パターンで抜き出す
REGEXEXTRACT(文字列, 正規表現) は、パターンに合う最初の部分を取り出します。位置が一定でない情報（文中のメールアドレスや電話番号）の抽出に向きます。
| 抽出したいもの | 正規表現の例 | 説明 |
|---|---|---|
| メールアドレス | [\w.+-]+@[\w.-]+\.\w+ | ＠ の前後と末尾のドメインを拾う |
| 郵便番号（7桁） | \d{3}-?\d{4} | 3桁＋（任意のハイフン）＋4桁 |
| 電話番号（数字とハイフン） | [\d-]{10,} | 数字とハイフンが10文字以上連続 |
=REGEXEXTRACT(A2, "[\w.+-]+@[\w.-]+\.\w+") — 文中からメールアドレスを抽出=REGEXEXTRACT(A2, "\d{3}-?\d{4}") — 郵便番号（ハイフン有無どちらも可）を抽出 正規表現は最初だけ覚えれば応用が利きます。RE2 の基本記号を小さな表で押さえておきましょう。
| 記号 | 意味 | 例 |
|---|---|---|
\d | 数字1文字（0〜9） | \d\d\d は数字3つ |
\w | 英数字・アンダースコア1文字 | メールのユーザー名部分など |
\s | 空白文字（スペース・タブ等） | \s+ で連続空白 |
+ | 直前を1回以上繰り返し | \d+ は数字1つ以上 |
{n} {n,} | n 回／n 回以上 | \d{4} は数字ちょうど4つ |
[] | いずれか1文字 | [A-Za-z] は英字1文字 |
[^] | 以外の1文字 | [^0-9] は数字以外 |
^ $ | 行頭／行末 | ^\d は先頭が数字 |
() | グループ化（取り出す範囲） | (\d+)-(\d+) で2分割 |
| | または | 株|有限 は「株」か「有限」 |
正規表現（RE2）— 詳しくは専用章へ
上の REGEXEXTRACT / REGEXMATCH / REGEXREPLACE で使う 正規表現そのもの は、Google スプレッドシートでは RE2 エンジン で動きます。基礎から全記号、便利なパターン集、そして「RE2 に無い機能（先読み・後読み・後方参照・\p{} など）」までを 正規表現 完全ガイド（RE2） に独立した章としてまとめました。ネットで拾った正規表現が動かない原因の多くは、RE2 がこれらを持たないことにあります。データ整形で正規表現を本格的に使うなら、まず専用章を一読してください。
6. 結合する
分割の逆、複数のセルを1つにまとめる操作です。基本は & 演算子、区切り文字を挟むなら TEXTJOIN(区切り文字, 空を無視するか, 範囲…) が便利です。
都道府県（A2）・市区町村（B2）・番地（C2）を1つの住所に。
=A2&B2&C2 — 単純に連結
=TEXTJOIN("", TRUE, A2:C2) — 空セルを無視して連結（第2引数 TRUE）
=TEXTJOIN(" / ", TRUE, A2:C2) — 「 / 」を区切りに入れて連結
TEXTJOIN の第2引数 TRUE は「空セルを飛ばす」指定です。途中の項目が空でも区切り文字が連続しないので、住所やタグの組み立てに向きます。
7. 縦横変換・整列
TRANSPOSE — 行と列を入れ替える
TRANSPOSE(範囲) は、表の行と列を入れ替えます。横長の表を縦に直したいときの第一手です。
横持ち→縦持ちへ
「月ごとに列が並ぶ横持ち」の表は、集計に向きません。SUMIFS や QUERY で扱いやすい 縦持ち（1行＝1レコード） へ直すのが整形の定石です。複数列を1列に畳むには FLATTEN(範囲)（指定範囲を1列に並べる）が使えます。QUERY と組み合わせれば、必要な列だけを縦に再構成できます。QUERY / FILTER の詳細は 第6章 配列・スピル時代の関数 を参照してください。
8. 複数の表をまとめる
別々のシートや範囲を縦に積み上げたいときは、配列リテラル {} が使えます。日本語ロケールでは、縦に結合するときの区切りはセミコロン ; です（横に並べるときはカンマ ,）。
1月シートと2月シートの同じ形の表を縦に積む。
={'1月'!A2:C100; '2月'!A2:C100} — 上下に連結（列数をそろえること）
=QUERY({'1月'!A2:C100; '2月'!A2:C100}, "select * where Col3 > 0", 0) — 積んでから一気に絞り込む
縦結合では 各範囲の列数を必ずそろえる ことが条件です。列数が違うと #REF! などのエラーになります。積んだ後に QUERY でフィルタ・並べ替え・集計までまとめると、月次データの統合が1数式で完結します。
9. 突合・差分を出す（名寄せの型）
「Aリストにあって Bリストにない」を洗い出すのが、名寄せの中核です。COUNTIF か検索系（VLOOKUP / XLOOKUP / MATCH）に、「見つからない」を判定する ISNA を組み合わせます。
A列＝今月の会員、D列＝先月の会員。A列の各行が先月にいたかを判定。
=IF(COUNTIF(D:D, A2)=0, "新規", "継続") — D列にA2が無ければ「新規」
=IF(ISNA(MATCH(A2, D:D, 0)), "Bに無し", "両方にあり") — MATCH＋ISNA で差分判定
=XLOOKUP(A2, D:D, D:D, "Bに無し") — 見つからない場合の値を引数で直接指定
COUNTIF 方式は「件数で判定」、MATCH+ISNA 方式は「位置で判定」で、どちらも結果は同じです。突合の前に 両側のキーを同じ形に整える（空白除去・大文字小文字統一）ことが何より重要です。整形を飛ばすと、本当は一致するはずのキーが「無し」と判定され、差分が嘘になります。検索関数の使い分けは 第5章 主要関数カタログ、集計の組み立ては 第7章 集計・分析パターン も参照してください。
10. そもそも入口で汚さない
ここまでは「汚れた後の整形」でした。しかし最良のクレンジングは そもそも汚させないこと です。データの入力規則（プルダウン・リスト選択・範囲制限）を使えば、表記ゆれや全角半角の混入を入力の時点で防げます。部署名や区分のような「選ぶべき値が決まっている列」は、自由入力ではなくプルダウンにするだけで、後工程の名寄せがほぼ不要になります。設計で防ぐ具体策は 第3章 壊れない設計 にまとめています。
まず空白と全角半角
TRIM＋SUBSTITUTE(" "...)＋CLEAN で見えない汚れを落とす。突合の前に必ず実施。
位置なら LEFT、可変なら REGEX
桁が固定なら LEFT/MID/RIGHT、位置が不定なら REGEXEXTRACT。混在列は REGEXMATCH で先にふるい分け。
原本を残して別列で整える
コピー → 整形列 → 確認 → 値で置換。破壊的操作（重複削除）は必ず複製に対して行う。
汚いデータを整える型がそろいました。空白と全角半角を落とし、表記ゆれをそろえ、重複を見つけ、必要な部分を抽出・結合し、縦持ちに直してから突合する——この一連の流れが名寄せの骨格です。手を動かして定着させたいときは 第5部 演習、用語に迷ったら 用語集 を活用してください。次は、ここまでの設計・関数・整形を組み合わせた 第9章 業務テンプレート実例 で、現場で使える完成形を見ていきましょう。