データクレンジングをExcelで行うやり方！目的別の関数と3ステップ手順

データクレンジングはExcelのTRIM・CLEAN・ASC関数や置換機能を使えば、特別なツールなしで実行できます。CSVインポートや手入力で混入した空白・改行の除去から、全角半角の統一、重複データの削除まで、Excelの標準機能でカバーできる範囲は広く実用的です。

データクレンジングとは、データの表記ゆれ・重複・不要文字などを修正してデータ品質を高める作業のことです。分析やCRM活用の精度はデータ品質に直結するため、活用前のクレンジングは欠かせません。本記事では、Excelでデータクレンジングを行うための目的別の関数・機能と、作業を進める3つの手順をわかりやすく解説します。

データクレンジングとは？ダーティデータの原因と3ステップの進め方

なお、本記事を公開している株式会社ジーニーでは、Cookie規制下で重要性が増すファーストパーティデータの統合・活用基盤としてCDP（カスタマーデータプラットフォーム）「GENIEE CDP」を提供しています。CDPは、Webサイト・店舗・各種ツールに散らばった顧客データを同一人物として名寄せし、分析から施策実行までを一貫させるためのデータ基盤です。Excelでの属人的なクレンジングから脱却し、継続運用できるデータ基盤を整えたい方は、ぜひご検討ください。

名寄せとは？その意味やデータ統合に向けた具体的な進め方を解説

顧客データ統合とは？仕組みから名寄せ・要件定義まで基礎を解説

Excelデータクレンジングの関数・機能（目的別）

Excelでのデータクレンジングは、大きく4つの目的に分類できます。不要な空白・改行の除去、表記ゆれの統一、文字列の分割・抽出、重複データの検出と削除です。それぞれの目的に応じた関数と機能が異なるため、自分のデータの「汚れの種類」を先に把握しておくと、適切なアプローチをすぐに選べます。

1. 不要な空白・改行の除去（TRIM・CLEAN関数）

CSVインポートや手入力で混入した余分な空白・改行は、目で見ても気づきにくいうえに、VLOOKUP や COUNTIF が正しく機能しない原因になります。このような「見えない汚れ」に対応するのがTRIM関数とCLEAN関数です。

TRIM関数は文字列の前後にある半角空白と、単語間に連続して入っている余分な半角空白を除去します。全角スペースは除去できないため、全角スペースが混入している場合はSUBSTITUTE関数と組み合わせる必要があります。 構文は以下のとおりです。

=TRIM(A1)

たとえばA1セルに「　山田　太郎　」（前後に全角スペース）が入っていた場合、=TRIM(A1)は「山田　太郎」を返します。ただし、TRIMが除去できるのは半角スペースに限られるため、全角スペースが混入している場合はSUBSTITUTE関数と組み合わせる必要があります。

TRIM関数では除去できない改行や制御文字（印刷できない非表示文字）を取り除くのがCLEAN関数の役割です。

=CLEAN(A1)

実務では両方の問題が同時に起きていることが多いため、TRIMとCLEANを組み合わせたパターンが効果的です。

=TRIM(CLEAN(A1))

この数式で、改行・制御文字をCLEANで除去してから、余分な空白をTRIMで整理する処理を1つの数式で完結できます。データのインポート直後にこの数式を補助列に展開しておくだけで、後続のVLOOKUPや集計処理の精度が大きく改善します。

2. 表記ゆれの統一（ASC・JIS・置換機能）

住所や会社名のデータでは、全角・半角が混在していることがよくあります。「東京都」と「東京都」、「03-1234-5678」と「０３−１２３４−５６７８」のように同じ情報でも文字の種類が異なると、システム連携やデータ集計で意図しないトラブルが発生します。日本語データ特有のこの課題に対応する関数が、ASC関数とJIS関数です。

ASC関数は全角英数字・カタカナを半角に変換します。電話番号や郵便番号など、半角で統一したいフィールドに使います。

=ASC(A1)

JIS関数はASC関数の逆で、半角英数字・カタカナを全角に変換します。会社名の表記を全角カタカナに統一したい場合などに使います。

=JIS(A1)

ASC・JIS関数は変換先の文字種が決まっている場合に向いていますが、特定の文字列を別の文字列に置き換えるにはSUBSTITUTE関数か置換機能（Ctrl+H）を使います。この2つには明確な使い分けがあります。

SUBSTITUTE関数は元データを保持したまま、別のセルに変換結果を出力します。元データを残しておきたいとき、または変換処理を数式として記録しておきたいときに選択します。

=SUBSTITUTE(A1,”（”,”(“)

置換機能（Ctrl+H）は選択範囲または全シートの元データを直接書き換えます。一度に大量のセルを処理する際は素早く使えますが、元に戻せないため、バックアップを取ってから実行するのが原則です。

置換機能ではワイルドカードを使ったあいまい検索も可能です。「?」は任意の1文字、「」は任意の文字列に対応します。たとえば「?月?日」と指定すれば、「1月1日」「3月15日」のような日付パターンをまとめて検索できます。なお、ワイルドカード文字そのものを検索したい場合は「~」「~?」のように「~」（チルダ）でエスケープします。 

英字データの表記統一には、UPPER関数（すべて大文字）、LOWER関数（すべて小文字）、PROPER関数（先頭文字だけ大文字）も便利です。メールアドレスや商品コードを小文字に統一するといった用途で活用できます。

3. 文字列の分割・抽出（LEFT・RIGHT・MID関数）

1つのセルに「東京都渋谷区恵比寿1-2-3」と住所全体が入っていたり、「山田太郎」と姓名が分かれていなかったりするデータは、後続の処理で扱いにくくなります。このような結合されたデータを分解するのがLEFT・RIGHT・MID関数の役割です。

LEFT関数は文字列の先頭から指定した文字数を取り出します。

=LEFT(A1, 3)　※ 先頭から3文字を抽出

RIGHT関数は末尾から指定した文字数を取り出します。郵便番号の後半4桁を抽出するといった用途に向いています。

=RIGHT(A1, 4)　※ 末尾から4文字を抽出

MID関数は指定した開始位置から指定した文字数を取り出します。引数の構造は =MID(文字列, 開始位置, 文字数) です。

=MID(A1, 5, 3)　※ 5文字目から3文字を抽出

ただし、住所や氏名のように長さが一定でないデータには、固定の文字数では対応できません。そこでFIND関数を組み合わせて区切り文字の位置を動的に取得する方法が使えます。

たとえば姓と名の間にスペースがある「山田 太郎」から姓だけを取り出す場合、以下の数式でスペースの前までを抽出できます。

=LEFT(A1, FIND(” “, A1) – 1)

FIND関数がスペースの位置を返し、そこから1を引いた文字数をLEFTに渡しています。名を取り出すにはMIDと組み合わせて使います。

大量データを一括処理するなら、データタブの「区切り位置」機能も代替手段として使えます。スペース・カンマ・固定幅などの区切り方を指定して、選択した列を複数列に分割する機能で、関数を使わずに処理できます。

4. 重複データの検出と削除

同じ顧客が複数行に登録されているデータは、集計やメール配信で二重カウント・二重送信を引き起こします。重複の問題は削除の前に「何件重複しているか」を把握することから始めるのが安全です。

COUNTIF関数を使うと、各行が何件重複しているかを数式で確認できます。

=COUNTIF($A$2:$A$100, A2)

この数式を補助列に展開すると、2以上の値が返った行が重複レコードです。さらに条件付き書式（ホームタブ）で「セルの値が1より大きい」条件を設定すれば、重複行を色でハイライトできます。削除前に重複の全体像を目で確認してから操作できるため、予期しないデータ消失を防げます。

重複を確認したら、データタブの「重複の削除」機能で一括削除が可能です。対象列を選んで実行すると、最初に現れた行を残して重複行を削除します。この機能は元データを直接変更するため、必ずバックアップを取ってから実行してください。

Microsoft 365またはExcel 2021以降を使っている場合は、UNIQUE関数でユニークな値の一覧を別範囲に出力する方法もあります。元データを保持したまま重複のないリストを作れるため、比較・確認の用途に向いています。

なお、表記ゆれが残っている状態で重複削除を実行すると、「株式会社ABC」と「（株）ABC」が同一企業でも別レコードとして残ります。前のセクションで説明した表記ゆれの統一を済ませてから重複削除を行うのが正しい順序です。名寄せ（異なる情報から同一人物を特定して統合する処理）についてはExcel単体では対応できる範囲に限界があります。この点は後のセクションで取り上げます

名寄せロジックとは？キー設計・アルゴリズムの種類・実装手順を解説

Excelでデータクレンジングを進める手順は？3ステップで解説

Excelの関数や機能の使い方を知っていても、作業の順序を間違えるとクレンジングの精度が落ちたり、元データを誤って消してしまったりするリスクがあります。

データクレンジングは単発の作業ではなく、目的の確認からはじまる一連のプロセスです。クレンジングを進める手順は次の3つです。

1. 目的の明確化とバックアップ

クレンジングの範囲と優先度は、何のためにデータを整備するかによって変わります。DM発送が目的であれば住所の正規化と重複排除が最優先です。売上分析なら数値の異常値チェックと集計フィールドの整合性が先になります。CRMへのデータ移行であれば、文字数制限や必須フィールドの仕様に合わせた整形が求められます。目的を最初に明確にすることで、やらなくてよい作業に時間を使わずに済みます。

目的が決まったら、作業開始前にバックアップを取ります。元データを別シートにコピーしておくか、ファイルごと別名保存してから作業を始めてください。特に置換機能や「重複の削除」のように元データを直接変更する操作は、取り消しが効かない場合があります。

さらに、元データのシートにシート保護をかけておくとミスを防ぎやすくなります。リボンの「校閲」タブから「シートの保護」を設定すると、誤ってセルを編集してしまうリスクを減らせます。作業用シートを別途用意してそこで関数を展開するフローにしておくと、元データとの区別が明確になります。

2. データの整形・正規化・重複排除

クレンジングの作業は順序が精度に直結します。表記ゆれが残った状態で重複排除を実行すると、同じ会社名や同じ顧客名でも別レコードとして残ってしまいます。正規化を先に完了させてから重複排除に進むのが原則です。

推奨する作業順序は以下のとおりです。

1. 空白・改行の除去（TRIM・CLEAN関数）
2. 全角・半角の統一（ASC関数またはJIS関数）
3. 表記ゆれの修正（SUBSTITUTE関数・置換機能）
4. 重複の検出と削除（COUNTIF関数・重複の削除機能）

各ステップの具体的な関数操作については前のセクションで説明しています。ここで重要なのは順序の意識です。たとえばステップ3を飛ばして重複削除を先に実行すると、「（株）山田商事」と「株式会社山田商事」が別レコードのまま残り、後から統合し直す作業が発生します。

関数を使って整形した結果は補助列に出力し、内容を確認してから値貼り付けで元の列に反映させます。数式のまま残すと参照先が変わったときに意図しない結果になることがあるため、最終的なデータには値として固定することを勧めます。

3. 結果の検証と定期運用ルールの策定

クレンジング作業が終わっても、そのまま本番データとして使うのは早計です。処理の前後でレコード件数が意図どおりに変化しているかを確認し、意図しないデータ消失や変換ミスがないかチェックしてから次のステップへ進みます。

検証の手順としては、まずクレンジング前後のレコード件数を比較します。重複削除で想定以上に件数が減っていれば、必要なデータまで削除している可能性があります。次に数十件程度をサンプル抽出して目視で確認し、変換結果に違和感がないかを確かめます。COUNTBLANK関数で空白セルの件数を確認するのも有効です。

もう一つ整備しておきたいのが、定期運用のルールです。月次・四半期など定期的にクレンジングを実施するサイクルを決め、今回の手順を手順書として文書化しておきます。どの列に何の関数を適用するか、どの表記ゆれを修正対象とするかを記録しておくことで、担当者が変わっても同じ品質のクレンジングを再現できます。手順の属人化を防ぐことが、データ品質の継続的な維持につながります。

Excelでのデータクレンジング精度を高めるポイント

関数の使い方を習得しても、運用面の配慮が欠けるとデータを壊すリスクが残ります。特に現場でよく見られる失敗パターンを把握しておくと、同じミスを避けやすくなります。

元データを直接変更する操作の前には必ずバックアップを取る

置換機能（Ctrl+H）と「重複の削除」は元データを直接書き換えます。Ctrl+Zで戻せないケースもあります。一方、TRIM・SUBSTITUTE・ASC等の関数は別セルに出力するため元データを保持できます。処理の性質を意識して、どちらのアプローチを選ぶかを判断してください。
  

クレンジングの対象範囲を目的に限定する

「ついでに整形しておこう」という発想で目的外のフィールドまで変換すると、必要な情報が失われるリスクがあります。たとえば電話番号を半角に統一する際、ハイフンの有無まで変えてしまうと、既存システムの入力規則と不整合が起きることがあります。変更箇所は最小限にとどめ、対象フィールドを事前にリストアップしてから作業するのが安全です。

過剰クレンジングに注意する

空白や特殊文字を「汚れ」として一律に除去すると、本来必要だった情報を消してしまうことがあります。住所の「東京都 新宿区」のようにスペースが意味を持つ場合もあります。TRIM関数は単語間の空白を1つ残す仕様ですが、SUBSTITUTE関数でスペースをすべて削除すると意味が変わります。「何を除去するか」だけでなく「何を残すか」も意識してください。
  

クレンジングの基準と手順を文書化する

どの列にどの処理を行うか、表記ゆれの統一基準（「（株）」は「株式会社」に統一する等）を手順書として残してください。担当者が変わった際に同じ基準で作業を再現できれば、データ品質のばらつきを防げます。スプレッドシートのコメント機能やメモシートを活用するだけでも効果があります。

名寄せツールとは？3つのタイプと選び方・主要4製品の比較を解説  

Excelでのデータクレンジングの限界と自動化・ツール活用の選択肢

Excelでの手作業クレンジングは、データ量が少なく頻度が低い場合に有効な手段です。しかし次の3つのケースでは、Excel単体での対応が限界に達します。

• 数万件以上の大量データを繰り返し処理する
• 月次・週次など高頻度でクレンジングが発生する
• 複数システムの顧客データを統合して名寄せする必要がある

データ活用の課題は日本企業全体に広がっています。Gartner Japanが2025年9月に実施した内容によると、データ活用の成果を全社的に十分に得ている日本企業はわずか2.4%にとどまり、「データの品質・信頼性が低い」ことが積極的なデータ活用を妨げる要因の上位に挙げられています。

Excelで名寄せする方法は？関数の使い方と重複削除の手順を解説

データを整備する体制そのものを見直す必要があることを示しています。

VBA・Power Queryによる自動化

毎月同じクレンジング作業を手作業で繰り返しているなら、VBAまたはPower Queryによる自動化が現実的な解決策です。どちらもExcelの関数知識を土台にするため、ここまで習得した内容が直接活きます。

2つのアプローチには特性の違いがあります。

	VBA	Power Query
操作方法	コード記述が必要	GUIベース（ノーコード）
柔軟性	複雑な処理や条件分岐に対応しやすい	定型的なデータ変換に向いている
再実行	マクロを呼び出して実行	データ更新時にワンクリックで再実行
属人化リスク	作成者が不在になるとメンテナンスが困難になりやすい	手順が視覚的に記録されるため引き継ぎしやすい
利用条件	Excelすべてのバージョン	Microsoft 365 / Excel 2016以降

VBAは条件分岐や外部システムとの連携など、Power Queryでは対応しにくい複雑な処理を自動化できます。ただし、コードが属人化しやすく、作成者が不在になるとメンテナンスの担い手がいなくなるリスクがあります。コードにコメントを丁寧に記述しておくことが欠かせません。

Power Queryはデータ変換の手順をGUI上で記録し、データを更新したときにワンクリックで同じ処理を再実行できます。CSVや外部データベースからの取り込みから整形・集計まで一連のフローを自動化でき、ノーコードで操作できるため引き継ぎもしやすいです。定期的なデータ整形であればPower Queryを最初に検討することを勧めます。

データ基盤ツール（CDP）の活用

複数のシステム（ECサイト・実店舗・CRM・広告プラットフォームなど）に顧客データが分散していて、それを統合して分析や施策に使いたい場合、ExcelやPower Queryでは対応しきれない壁があります。

CDPはこのような課題を解決するために設計された専用ツールです。複数のデータソースを統合し、ID名寄せによって異なるチャネルの顧客データを同一人物に紐付けて一元管理します。顧客データの規模が大きく、複数チャネルにまたがって分析・施策を実行したい場合に適しています。

株式会社ジーニーが提供するGENIEE CDPはノーコードで多数のツールと連携でき、ID名寄せ・統合機能によって同一ユーザーの行動をチャネルをまたいで一元管理できます。AI・機械学習を活用した分析や自然言語でのデータ分析にも対応しており、蓄積したファーストパーティデータを施策に結びつけるまでのサイクルを短縮できます。

項目	内容
運営会社	株式会社ジーニー
サービス種別	カスタマーデータプラットフォーム（CDP）
主な機能	ノーコードデータ連携、ID名寄せ・統合、テンプレートダッシュボード、AI分析、リアルタイム処理
連携先	GENIEE MA、GENIEE ENGAGE、GENIEE ANALYTICS 等
料金	要問い合わせ

検討するメリットは主に3点あります。第一に、チャネルや部門をまたいだ顧客像の断片化を解消できること。第二に、蓄積したデータをセグメントとして切り出し、MAやメール配信などの施策にそのまま連携できること。第三に、Cookie規制に左右されない自社起点のデータ活用体制を構築できることです。

まとめ：Excelでデータクレンジングする方法と次のステップ

Excelの標準機能だけで、データクレンジングの基本的な処理を一通りカバーできます。

• 空白・改行の除去：TRIM関数・CLEAN関数・組み合わせ使用
• 表記ゆれの統一：ASC/JIS関数、SUBSTITUTE関数、置換機能（ワイルドカード活用）
• 文字列の分割・抽出：LEFT/RIGHT/MID関数、FIND関数との組み合わせ、区切り位置機能
• 重複の検出・削除：COUNTIF関数、条件付き書式、重複の削除機能

手順としては、目的の明確化とバックアップを最初に行い、正規化の順序（空白除去→全角半角統一→表記ゆれ修正→重複排除）を守って作業を進め、件数比較とサンプル目視で検証します。この流れを手順書に残しておけば、担当者が変わっても再現できます。

データ量が増加したり、同じ処理を繰り返す頻度が高くなったりしてきたら、Power QueryやVBAによる自動化を検討するタイミングです。複数システムにまたがる顧客データの統合やID名寄せが必要になった場合は、CDPのような専用ツールが選択肢になります。

株式会社ジーニーのGENIEE CDPは、散在した顧客データをノーコードで統合し、ID名寄せによってオンライン・オフラインをまたいだ同一顧客の行動把握を実現します。AI・自然言語による分析サポートでデータアナリストがいない組織でも活用でき、分析結果はMAやENGAGE等のジーニーマーケティングクラウド製品にそのままセグメントとして連携できるため、「データは集めたが施策に繋がらない」状態を解消できます。導入支援・活用支援チームによる伴走もあり、CDPの導入が初めての企業でも無理なく立ち上げられます。

クレンジングを恒常業務として仕組み化したい方は、まずはGENIEE CDPの製品ページから詳細をご確認ください。

複数システムのデータが散在｜CDP活用でデータクレンジングと名寄せを自動化

関連記事

DataSpiderとは？ETL・EAIの違いや機能を解説

AIを使った名寄せの方法｜プロンプト設計からツール選定まで解説

データクレンジングツール9選｜種類の違いと失敗しない選び方

データクレンジングと名寄せの違いとは？実施手順やツールの選び方を解説