今回は、Excelのデータのクレンジングを時短する整理術を紹介します。
Excelのデータのクレンジングで最初に整えること
Excelで名簿、売上表、問い合わせ一覧、在庫リストなどを扱うとき、集計や分析の前に必要になるのがデータのクレンジングです。データのクレンジングとは、表の中にある表記ゆれ、余分な空白、重複、入力ミス、形式の違いを整え、扱いやすい状態にする作業です。
作業を時短するには、いきなり修正を始めるより、先に元データ、作業用データ、完成データを分けることが大切です。元データを直接編集すると、後から確認したいときに戻れなくなります。まずシートを複製し、作業用シートで整えるだけでも、確認ややり直しの手間を減らせます。
おすすめの流れは次の通りです。
- 元データのシートを残す
- 作業用シートを複製する
- 列名を確認し、意味が分かる名前にする
- 不要な空白行やメモ行を除く
- 列ごとに入力形式をそろえる
この順番にしておくと、Excelのフィルター、検索と置換、関数、条件付き書式を使う場面で迷いにくくなります。
空白と余分な文字を先に処理する
データのクレンジングで時間を取られやすいのが、見た目では分かりにくい空白です。氏名や会社名の前後にスペースが入っていると、同じ文字に見えても別データとして扱われることがあります。集計結果が合わない、VLOOKUPやXLOOKUPで見つからない、といった原因にもなります。
まず確認したいのは、セルの前後に入った空白、全角スペースと半角スペースの混在、改行、不要な記号です。Excelでは、検索と置換だけで直せるものもありますが、列全体を安定して整えるなら関数を使う方法が便利です。
空白を整える基本の考え方
氏名や会社名の列では、次のような作業が役立ちます。
- 前後の余分な空白を削除する
- 全角スペースを半角スペースにそろえる
- セル内改行を削除する、または別の記号に置き換える
- 敬称や不要な注記が混ざっていないか確認する
前後の空白にはTRIM関数、全角と半角の変換にはASC関数やJIS関数が使えます。社内ルールで全角にそろえるのか、半角にそろえるのかを決めておくと、毎回判断する時間を短くできます。
表記ゆれは置換リストでまとめて直す
Excelのデータのクレンジングで多いのが、同じ意味なのに表記が違うケースです。たとえば「東京都」「東京」、「株式会社サンプル」「(株)サンプル」、「営業部」「営業一部」などです。これを目視で直していくと、量が増えたときに時間がかかります。
時短したい場合は、別シートに置換リストを作るのがおすすめです。左側に修正前、右側に修正後を書いておけば、同じパターンが出たときに再利用できます。
置換リストに入れておきたい項目
置換リストには、よく出るゆれから登録します。
- 都道府県名や市区町村名の省略表記
- 部署名、役職名、店舗名の表記
- 会社名の法人格表記
- 商品名やサービス名の略称
- 日付、電話番号、郵便番号の表記ルール
置換リストを作っておくと、検索と置換を使うときの判断が早くなります。Power
Queryを使える環境なら、置換リストを参照して変換する仕組みにしておくと、次回以降の作業も短くなります。
重複データは削除前に条件を決める
重複データの削除は便利ですが、先に条件を決めないと必要な行まで消してしまうことがあります。たとえば、同じ顧客名でも別拠点の可能性があります。同じメールアドレスでも、問い合わせ履歴として残すべき場合があります。
削除前には、何をもって重複と判断するかを決めます。氏名だけで判断するのか、氏名と電話番号を組み合わせるのか、メールアドレスを基準にするのかで結果は変わります。
重複チェックの手順
安全に進めるには、次の流れが向いています。
- 重複判定に使う列を決める
- 作業用列を追加し、判定用の文字列を作る
- COUNTIFやCOUNTIFSで重複を確認する
- フィルターで該当行を確認する
- 不要と判断した行だけ削除する
重複の削除機能を使う場合も、実行前にシートを複製しておくと安心です。特に顧客情報や取引データでは、削除よりも「重複候補」としてフラグを付け、担当者が確認できる形にすると運用しやすくなります。
日付と数値は形式をそろえてから集計する
Excelでは、見た目が日付でも中身が文字列になっていることがあります。これが混ざると、並べ替えやピボットテーブルで思った通りに集計できません。数値も同じで、金額にカンマや単位が混ざっていると計算対象にならない場合があります。
日付列では、まず表示形式だけでなく、セルの値として日付になっているかを確認します。文字列の日付が混ざっている場合は、DATEVALUE関数、区切り位置、Power
Queryなどで日付に変換します。
数値列では、円、個、件などの単位を別列に分けると扱いやすくなります。金額を集計したい列に文字が混ざっていると、合計や平均がずれる原因になります。
形式をそろえるときの確認ポイント
次の項目を見ておくと、後工程での手戻りを抑えられます。
- 日付が文字列ではなく日付データになっているか
- 年月日、年月、月日のどれで管理するか
- 金額や数量に単位が混ざっていないか
- 電話番号や郵便番号の先頭のゼロが消えていないか
- コード番号を数値ではなく文字列として扱う必要がないか
電話番号や社員番号のように計算しない番号は、文字列として扱う方が向いています。数値にしてしまうと先頭のゼロが消えることがあるため、列の役割に合わせて形式を決めます。
フィルターと条件付き書式で異常値を見つける
データのクレンジングでは、明らかな誤入力を見つける作業も欠かせません。空白、想定外の値、入力範囲から外れた数値、表記の違うカテゴリは、フィルターと条件付き書式で見つけやすくなります。
たとえば、部署名の列でフィルターを開くと、登録されている値の一覧を確認できます。そこに「営業部」と「営業
部」のような違いがあれば、表記ゆれに気づけます。条件付き書式では、空白セル、重複値、指定範囲外の数値を色で示せます。
目視で全行を見るのではなく、Excelに候補を出させるのが時短のコツです。人が見るのは、Excelが示した候補の確認に絞ると、作業の負担が軽くなります。
次回も使えるクレンジング手順を残す
一度だけ整えるなら手作業でも済みますが、同じ種類のデータを毎月、毎週、必要なタイミングで扱うなら、手順を残しておくと次回の作業が短くなります。メモとして残す内容は、難しいものでなくてかまいません。
残しておきたいのは、次のような情報です。
- 元データの入手元
- 削除する列と残す列
- 表記をそろえる項目
- 重複判定に使う列
- 日付や数値の変換ルール
- 確認が必要な例外
このメモがあると、担当者が変わったときにも作業の品質を保ちやすくなります。Power
Queryを使う場合は、変換手順そのものを保存できるため、同じ形式のデータなら読み込み直すだけで整形できます。
まとめ
Excelのデータのクレンジングを時短するには、作業前の整理、空白や表記ゆれの処理、重複条件の確認、日付と数値の形式統一が重要です。元データを残し、作業用シートで進めるだけでも、確認ややり直しの手間を減らせます。
特に効果が出やすいのは、置換リストの作成、判定用列の追加、フィルターと条件付き書式の活用です。毎回同じ判断をしないようにルールを残しておくと、Excelでのデータのクレンジングは進めやすくなります。
まずは、よく扱う表で「空白」「表記ゆれ」「重複」「日付」「数値」の5つを確認する流れを作ると、集計や分析の前準備を落ち着いて進められます。