Excelのデータクレンジング手順｜空白・表記ゆれ・重複を確認する方法
目次
Excelのデータクレンジングは、空白や表記ゆれを直し、集計や照合に使える状態へ整える作業です。見た目を揃えるだけでなく、「何を同じデータとして扱うか」を決める必要があります。取引先名が同じ行を削除した結果、別の注文まで失うような処理は避けなければなりません。
この記事では、原本を残しながら空白、文字列と数値、表記ゆれ、重複を順に確認する方法を紹介します。日々の売上一覧や顧客マスタを整える担当者向けに、数式と作業記録の例をまとめます。
作業前に原本と処理ルールを残す
原本を直接上書きしない
最初に元ファイルを保管し、作業用コピーを作ります。列を削除したり値を置換したりする前に、元の行を識別するIDも残してください。並べ替えで行番号は変わるため、元ファイル名と元の行番号を値として控える方法もあります。
作業用シートには「元の値」「整形後の値」「確認結果」の列を分けて置きます。確認が終わるまで、整形後の数式を元列に貼り付けないようにします。複数人で使うファイルでは、誰がいつ作業し、どの版を正とするかも記録します。
列ごとに変えてよい内容を決める
すべての列へ同じ処理をかけるのは危険です。担当部署名の余分な空白は消せても、商品名の区切りや住所の建物名では意味があるかもしれません。顧客コードや郵便番号は計算する数値ではなく、先頭の0を含む文字列として管理する場合があります。
たとえば、作業前に次のルールを決めます。いずれも自社のデータ仕様に合わせて変更する例です。
顧客コード：文字列で6桁。先頭の0を保持し、空欄は確認対象
部署名：指定した空白の整形後、承認済みの部署一覧と照合
売上日：日付として扱い、曖昧な日月表記は元データへ確認
金額：円単位の数値。負数は返品などの理由を確認して保持
注文番号と明細番号：組み合わせで一つの明細を識別
この段階で決められないものは「保留」にします。見た目が揃わないからといって、意味まで書き換えないことが基本です。
手順1 空白の種類を見分ける
半角スペースはTRIMで整える
A2に元の文字列がある場合、B2へ次の式を入れます。
=TRIM(A2)TRIMは前後の半角スペースを取り除き、単語の間に連続した半角スペースがある場合は一つにします。たとえば「 営業 一課 」は「営業 一課」になります。文字列の途中にある空白をすべて削除する関数ではありません。
Microsoftの説明では、TRIMはASCIIのスペースを対象とし、Webからコピーしたデータに含まれる改行しないスペースは単独では除去できません。TRIMの公式説明
全角スペースは変換してから処理する
全角スペースを半角へ置き換えてよい列なら、SUBSTITUTEと組み合わせます。
=TRIM(SUBSTITUTE(A2," "," "))最初の引用符の中は全角スペース、次は半角スペースです。この式は、全角スペースを半角へ置き換えた後、TRIMで整えます。途中の区切りも変わるので、氏名、商品名、住所などへ無条件に適用しないでください。
SUBSTITUTEは、指定した文字列を別の文字列に置き換える関数です。SUBSTITUTEの公式説明
Web由来の空白が残る場合
通常の空白に見えても、Unicodeの160番に当たる改行しないスペースが混じることがあります。その文字を半角スペースへ置き換える必要がある場合は、次のようにします。
=TRIM(SUBSTITUTE(SUBSTITUTE(A2,UNICHAR(160)," ")," "," "))UNICHARは指定した番号に対応するUnicode文字を返します。UNICHARの公式説明
この式でも、タブや改行、ほかの種類の空白がすべて取り除かれるわけではありません。残る文字がある場合は元データを確認し、その文字をどう扱うか決めてから追加の処理を考えます。異なる文字をまとめて消すより、変更前後を追える手順にする方が原因を調べやすくなります。
手順2 空欄と数値の0を区別する
整形後の値がB2にある場合、「=LEN(B2)」で文字数を確認できます。空文字列なら0になり、数値の0は文字として数えると1文字です。入力が必要な列に対し、長さが0の行を確認対象として抽出します。LENの公式説明
セルが空に見える理由は、未入力、数式の結果が空文字列、空白だけの入力など複数あります。B列に空白整形の式を入れたからといって、元のA列が本当に未入力だったかは分かりません。「元から空欄」と「整形で空になった値」を区別すると、入力フォームの改善にも使えます。
金額欄の0円を欠損扱いにして、平均値や前月値で埋めることは避けます。無料の明細や調整行かもしれません。欠けている値を補う場合は、補完元と承認者を別に記録しましょう。
手順3 数字に見える文字列を列の意味に合わせる
金額を合計できないときは、空いている列で「=ISNUMBER(A2)」を使い、数値として保存されているかを確認します。FALSEだから必ず誤りというわけではありません。顧客コードが文字列なら、仕様どおりの場合があります。IS関数の公式説明
特に「000125」のようなコードを数値へ変換すると、先頭の0が失われます。一度失った0を、元の桁数が不明なまま自動で足すことはできません。CSV取り込み時点で列の型を指定し、コードとして保持できたか確かめます。
日付にも注意が必要です。「10/11」が10月11日か11月10日かは、入力元の書式によって変わります。Excelの表示形式だけを変えても、元データの解釈が正しくなるとは限りません。日付の不明点は推測で直さず、元ファイルの仕様や作成者へ確認します。
数値化する場合は、通貨記号、桁区切り、小数点、単位のルールを決め、変換前後の合計を照合します。「千円」の列を「円」の列へ混ぜるなど、型が数値でも意味が違う状態は、関数だけでは防げません。
手順4 表記ゆれは対応表で管理する
「営業1課」「営業一課」「営業第1課」を一つにしたい場合、まず同じ部署を意味するか確認します。旧組織名と新組織名では、適用期間が異なることもあります。会社名の略称や支店名も、似ているだけで統合しないようにします。
対応表には、元表記、標準表記、適用期間、承認者、確認日を記録します。未登録の表記は一律に置き換えず、確認待ちに残します。文字列の一部分をまとめて置換すると、別の正式名称まで変わる場合があるためです。
変更後は、標準表記ごとの件数を確認し、一つの項目へ不自然に集まっていないかを見ます。担当者が表記ルールを確認したうえで、後続の参照や集計に渡します。標準化の内容を記録しておけば、毎月同じ確認を一からやり直す必要を減らせます。
手順5 重複は削除する前に業務上の単位を確認する
同じ名前と同じ取引は分けて考える
顧客名が同じ行でも、注文日や明細番号が違えば別の取引です。たとえば「注文番号1001・明細1」と「注文番号1001・明細2」は二つの明細として残す必要があります。何列の組み合わせを重複判定に使うかを、処理前に決めます。
最初は条件付き書式やフィルターで候補を見つけます。重複候補について、元ファイル、更新時刻、金額、取消状態を並べて確認し、「両方残す」「旧版だけ除外する」「入力元へ問い合わせる」などに分類します。
重複の削除で残す行を決めたつもりにならない
Excelの「重複の削除」は、選んだ列を判定基準にして、重複する行を削除します。判定に使わない列のデータも一緒に削除される点に注意が必要です。Microsoftも、事前に原本を別シートやブックへコピーすることを勧めています。重複削除の公式説明
先頭にある行が最新・正確とは限りません。更新日時だけで残す行を決められるか、同時刻のデータはどう扱うかを決め、判断できない行は除外せず保留します。削除した行も、理由付きで別の記録へ残すと照合しやすくなります。
最後に件数と意味の変化を検証する
処理が終わったら、次を順に確認します。
1. 元の件数＝残した件数＋除外した件数になっている
2. 空白整形だけの工程で、行数や金額合計が変わっていない
3. 除外した取引の金額と理由を説明できる
4. コードの先頭の0や、意味のある区切りが残っている
5. 変換できなかった日付・数値・表記を抽出している
6. 原本、適用ルール、確認記録から処理を再現できる
一回限りの小さな表なら補助列の式から始め、毎月同じ手順を繰り返すなら、処理ルールが固まった段階で自動化を検討します。自動化の前にも、想定外の形式を止める確認は必要です。データがきれいに見えることと、業務上正しいことを、最後まで別々に確認しましょう。
データ整備から業務システムへの連携まで見直したい場合は、Miyabeeのシステム開発支援をご覧ください。