Excelでデータをクリーンアップする実践ガイド
Power Query、関数、自動化を活用してExcelのデータクレンジングをマスターしましょう。重複の削除からワークフローの効率化まで、実務で使えるテクニックを解説します。
CRMやアンケートツールから書き出したCSVファイルを開いたとしましょう。氏名の大文字・小文字がバラバラで、見えない空白を含む値があり、日付はなぜか並べ替えられず、重複レコードのせいで合計が膨らんでいます。目についた問題を元のファイルで直接直したくなりますが、その方法だと次回の書き出しがむしろ面倒になります。
Excelでのデータクレンジングのコツは、個々の関数を暗記することではなく、説明できて、再現できて、検証できるプロセスを作ることにあります。元データを保存し、変換ロジックと出力を分離し、値を意識的に標準化し、レポート化する前に結果を検証する。この基本を押さえましょう。
目次
- なぜデータクレンジングがワークフローに重要なのか
- クレンジング前の必須チェックリスト
- 重複の削除とテキストの標準化
- Power Queryで再現可能なワークフローを構築する
- AIで高度なクレンジング作業を行う
- 識別子の保護とデータの検証
なぜデータクレンジングがワークフローに重要なのか
スプレッドシートはきれいに見えても、信頼できない結果を生むことがあります。Retail、retail、RETAIL は人間には同じカテゴリに見えても、Excelは集計の中で別々のラベルとして扱います。末尾の空白ひとつで顧客の検索に漏れが出ますし、重複レコードは目に見える警告もなく合計を水増しします。
基本構造はシンプルです。1行は1つの観測、1列は1つの変数、各セルは1つの情報だけを持つ。整理前のコピーを作る前に、読み込んだ生データを別のワークシートに保存しておきましょう。こうしたスプレッドシートの衛生ルールによって、重複チェックや欠損値の確認、書式変更の検証が楽になります。詳しくはこのスプレッドシート準備の標準ガイダンスを参照してください。
値を変更する前に証拠を残す
まず受け取ったブックのコピーから始めます。元の見出しと値はそのまま残し、補助列、マッピング、数式、検証チェックは別のクレンジング用シートで行いましょう。分析に使える最終テーブルは、生データとも変換ロジックとも明確に分けておきます。
この分離が効いてくるのは、関係者から「なぜこのカテゴリが変わったのか」「なぜこの行が消えたのか」「この日付は修正されたのか、書式を変えただけなのか」と聞かれたときです。undoの履歴や記憶に頼らず、元の値とクレンジング後の値を並べて比較できます。
実践ルール: クレンジング作業を説明できず、次回の書き出しで再現できないなら、それは場当たり的な応急処置です。
きれいなデータセットは、その後の作業も守ります。ピボットテーブル、グラフ、数式、外部レポートはすべて、元になる行の品質を引き継ぎます。クレンジング済みデータを可視化する前に、特に元データに未標準化のカテゴリが含まれる場合は、このGoogle Sheetsでグラフを作るガイドと同じ規律に従いましょう。
クレンジング前の必須チェックリスト
関数や変換ツールを使う前に、安全な作業環境を作ります。MicrosoftはExcelデータクレンジングのガイダンスの中で、生ファイルのバックアップ、表構造の利用、個別の列を修正する前の全体クリーンアップを推奨しています。
元データを守る
- 別コピーを保存する。 受け取ったブックは変更しません。作業ファイルにはわかりやすい名前を付け、元のファイル名と日付をメモシートやクレンジングログに記録します。
- レイヤーを分ける。 触っていない取り込み用の
Rawシート、補助ロジック用のCleaningシート、完成テーブル用のOutputシートを使います。 - 作業範囲をExcelテーブルに変換する。 データを選択して 挿入 > テーブル を選び、見出しがあることを確認し、説明的な列名を付けます。テーブルにすると空白や見出しが確認しやすく、数式も安定して下まで適用されます。
- 構造上の障害を取り除く。 結合セル、データセットの横にある無関係な表、空白の見出しセル、1列に詰め込まれた複数の値は、並べ替え、フィルター、その後の取り込みの邪魔になります。
まず全体チェックを済ませる
既知の表記ゆれに対しては検索と置換を実行しますが、置換の前に選択範囲を限定しましょう。全体置換は正当なメモ、識別子、数式の参照まで書き換える恐れがあります。叙述フィールドにはスペルチェックを使い、提案をすべて鵜呑みにせず結果を目で確認します。
取り込んだテキストは、生のフィールドを上書きせず補助列を作ります。=TRIM(A2) は通常の前後の空白を、=CLEAN(A2) は印刷できない文字を除去します。詳細はMicrosoftのCLEAN関数リファレンスを参照してください。コピーしてきたテキストが頑固な場合は、これらの関数を適用する前に特殊な空白を置換する必要があるかもしれません。
変換する前に確認する
各列の実際の範囲を確認し、見出しが1行に収まっているか、空白、エラー、混在する書式、想定外の値がないかを見ます。日付表示のセルが本当にExcelの日付だとは限りませんし、他の数値と同じように右揃えの数値も数値として保存されているとは限りません。
最も安全な順序は バックアップ、確認、変換、検証、公開 です。クレンジング済みの値を元の位置に貼り付けるような便利なショートカットが、取り返しのつかないデータ判断にならないための保険です。
重複の削除とテキストの標準化
重複削除は、「何をもって行が一意か」を定義してからでないと信頼できません。全列の完全一致が常に正解とは限りません。メモ、タイムスタンプ、書式が異なっていても、顧客ID、注文番号、アンケートの回答キーが一意性を定義するケースもあります。
Excelには2つの便利なアプローチがあります。条件付き書式は重複値をハイライトして確認でき、データ > 重複の削除 は選択した列に従って一致するレコードを削除します。詳しくはMicrosoftの重複処理ドキュメントを参照してください。
確認してから削除する
調査が必要なときは条件付き書式を使います。データセットを変更せずに繰り返しの値を確認できるので、2つのレコードが同じ名前でも別のアカウントに属する場合などに役立ちます。真の重複を定義するフィールドを決めたら、該当データを作業テーブルにコピーし、正しい列にチェックを入れて重複の削除を実行します。
標準機能は確定的に動きますが、選択した範囲しか比較しません。全列を選べば、同一の識別子でもメモが異なる2つのレコードは生き残りますし、大まかなカテゴリだけを選ぶと正当なレコードが削除される恐れがあります。
重複とはビジネスルールであり、行の見た目の類似ではありません。
比較の前にテキストを標準化する
空白や隠れた文字は、等しい値を異なる値に見せます。重複排除の前に補助列でテキストを正規化しましょう。
- TRIMで通常の空白を除去:
=TRIM(A2)は前後の空白を削除し、繰り返される連続空白を正規化します。 - CLEANで取り込みテキストを処理:
=CLEAN(A2)は古いシステムやWebからコピーしたテキストに含まれる印刷できない文字を除去します。 - 特殊な空白を置換: コピーした内容に
TRIMだけでは処理できない特殊空白が含まれる場合はSUBSTITUTEを使います。 - 既知のバリエーションをマッピング: 表記やラベルのゆれを1つの承認カテゴリに対応付ける参照テーブルを作ります。
クレンジング後の列を生の値と照合したら、静的な成果物が必要な場合は値のみを出力レイヤーに貼り付けます。変換が理解可能な状態に保つよう、数式やクエリの手順は別の場所に文書化しておきましょう。
手作業による重複排除は、管理された1回限りのファイルには有効です。しかし同じ書き出しが繰り返し届くようになると脆くなります。数式パターンも役立ちますし、このExcel数式作成リソースは望みの変換を動く式に落とし込む助けになりますが、補助列に散らばった数式は元データのレイアウトが変わると保守が必要です。
反復作業には、Power Queryの方が強力な選択肢です。変換を記録して更新されたデータに再適用できるからです。学習コストはありますが、長い編集の連鎖よりずっと検証しやすいプロセスになります。
Power Queryで再現可能なワークフローを構築する
ファイルが小さく、内容をよく知っていて、二度と来ないなら手作業のクリーンアップで十分です。しかし同じレポートが毎月届く瞬間から、手作業の再構築は不要なリスクになります。Power Queryは、セルを編集する作業から、Excelが更新できる変換のシーケンスを定義する作業へとタスクを変えてくれます。
パイプラインとブックの表示を分離する
実践的なPower Queryワークフローは次のようになります。
- ソースに接続する。 CSV、ブック、フォルダー、データベースを取り込み、レポートシートに手で値をコピーしません。
- 取り込んだフィールドをプロファイルする。 修正を適用する前に、空白、エラー、想定外の型、重複候補を確認します。
- 変換を適用する。 テキストのトリミング、値の置換、列の分割、データ型の設定、エラーの除去、定義済みルールに基づく重複削除を行います。
- 結果を読み込む。 クレンジング済みテーブルをワークシートまたはデータモデルに出力し、生ソースは比較用に残しておきます。
Power Queryはこれらの操作を適用されたステップとして保存します。ソースが更新されたら、クエリを更新するだけで記録されたシーケンスが再実行され、アナリストが毎回クリックを繰り返す必要はありません。
これはアンケートの書き出しやCRMの抽出で特に有効です。同じフィールドに一貫しない大文字小文字、隠れた空白、不完全な値、変化するカテゴリラベルが含まれがちだからです。市場調査向けデータクレンジングツールのガイダンスでも、こうした問題は見た目の整形ではなく、明示的なクレンジングタスクとして扱うべきだと強調されています。
変換中に機密フィールドを守る
Power Queryは、こちらが定義しない限り識別子のビジネス上の意味を知りません。口座番号、郵便番号、会員コード、長いIDなどは、特に型を意識的に設定しましょう。数値に見えるフィールドでも、先頭のゼロや正確な文字並びが意味を持つため、テキストのままにすべき場合があります。
日付も同じ注意が必要です。表示されている日付が有効な日付値とは限りませんし、書式を変えても曖昧なソースの慣習は解決しません。まず意図する解釈を確定し、それから適切なロケールや変換でフィールドを解析します。
出力を公開する前に検証しましょう。
- 行数: 削除とフィルターが想定通りか確認します。
- キーの一意性: 一意であるはずの識別子が実際に一意かを確認します。
- 合計: 重要な数値の合計をソースと比較します。
- カテゴリの網羅性: 想定外のラベルと未マッピングを確認します。
- 日付の境界: 書き出しがカバーすべき期間外の値がないか確認します。
Power Queryは反復する書き出しに対して数式を作り直すより保守しやすいものの、それでも責任者が必要です。クエリに明確な名前を付け、前提を文書化し、ソースのレイアウトが変わったら更新をテストしましょう。更新可能なワークフローが自動的に正しいわけではありません。各ステップに明確な目的があり、出力が検証されて初めて信頼できるものになります。
実際のワークフローはこちらで確認できます。
AIで高度なクレンジング作業を行う
Excelの新しい支援機能は検査を速くできますが、定義済みのクレンジングワークフローの中で使うのが最も効果的です。MicrosoftのAIベースのClean Data機能は、一貫しないテキスト、一貫しない数値書式、余分な空白に対する修正案を提示します。データタブから利用できます。詳しくはClean Data in Excelドキュメントを参照してください。
提案は発見に使い、盲目的に置き換えない
AIの提案は、手作業では時間のかかるパターンを見つける助けになります。一貫しない大文字小文字、空白、数値の表示を検出してくれるので、集中して確認すべきキューができます。ただし、それぞれの修正案がフィールドの意味に合うかを確かめてから受け入れましょう。
すべてのバリエーションが同じラベルを指すなら、顧客セグメントを標準化するのは合理的です。しかし似たラベルでも異なるグループを表すことがあるため、分類にはビジネスルールが必要です。値が等価かどうか、空白は空白のままにするか、変わった入力はエラーか正当な例外かを決めましょう。
GPT WorkspaceはAIデータクレンジングとしてスプレッドシートの選択範囲を扱えるほか、数式の生成、エントリーの分類、スプレッドシート自動化用Apps Scriptのドラフト作成も支援します。自然言語のルールを、Sheetsワークフローや他のスプレッドシート処理用の変換ドラフトに変換できます。ただし、そのドラフトは例外を含む代表的なケースでテストしてから、反復パイプラインに組み込みましょう。
AIはパターン発見を加速できます。しかし、あなたのデータポリシーを代わりに定義してはくれません。
AIの出力を今回のブックを超えて役立てるには、受け入れた提案をそれぞれ名前付きルールとしてログに記録します。そうすれば次回の書き出しでは、同じパターンを手動レビューに回さず、そのルールを再利用できます。トリガー、意図する結果、既知の例外をクレンジングログに記録しましょう。
機密性の高い識別子、日付、アンケートのロジックには、やはり人の承認が必要です。修正は見た目をきれいにしながら値の意味を変えてしまうことがあるので、自動化の前にそうしたフィールドはより厳格なレビューの経路を通しましょう。
識別子の保護とデータの検証
最も被害の大きいクレンジングの失敗は、見た目が乱れていても意味を持つ値に起きます。郵便番号には先頭のゼロが含まれ得ますし、口座番号は普通の数値に似ており、長いIDはExcelの自動変換で意図した表現を失うことがあります。こうしたフィールドは、数値ではなく識別子としてまず扱いましょう。
識別情報と計算を分けて扱う
型を変更する前に、各列の役割を定義します。値が計算に使われるなら、意識的に変換して結果を検証します。レコードを識別する値なら、元システムが明示的に別の型を要求しない限りテキストのままにします。
安全なパターンは次の通りです。
- 生の識別子を保存する。 取り込んだフィールドを上書きしません。
- 型付きの補助フィールドを作る。 ビジネスルールが必要とする場合にのみ変換します。
- 値を並べて比較する。 失われた先頭文字、変わった書式、想定外の空白がないか確認します。
- 一意性をテストする。 フィルター、条件付き書式、定義したキーでの重複チェックを使います。
- レビュー後にのみ公開する。 照合のため元の値を残しておきます。
日付も条件付きの扱いが必要です。解析の前に、ソースが日-月-年なのか月-日-年なのかを確認しましょう。表示書式は見た目を変えますが、テキストを有効な日付値に変換するとは限りません。
入力規則で新しいエラーを防ぐ
データの入力規則はセルに入力できるデータの種類や値を制限するため、将来の一貫性の欠如を防ぐのに役立ちます。管理されたカテゴリにはドロップダウンリストを、日付フィールドには日付ルールを、ビジネスプロセスが許容値を定義している場合は数値の上限・下限を使いましょう。入力規則は既存の取り込みを修復しませんが、次の手動編集で別の表記ゆれが持ち込まれるのを防げます。
完全なワークフローはこうなります。
- 生レイヤー: 受け取ったデータを変更せず保存します。
- 検査レイヤー: 空白、エラー、重複、異常な書式、不審な値を特定します。
- 変換レイヤー: 補助列またはPower Queryでテキストをクレンジングし、カテゴリを標準化し、日付を解析し、型を設定します。
- 検証レイヤー: 行数、合計、キーの一意性、カテゴリ、日付範囲を比較します。
- 出力レイヤー: 分析可能なテーブルを公開し、簡潔なクレンジングログを残します。
重要なのは、個々の関数ではなく方法です。TRIM、CLEAN、条件付き書式、重複の削除、入力規則、Power Queryは、それぞれ違う問題を解決します。文書化されたワークフローの中で使えば、意味を保ちながら結果を再現可能にしてくれます。
GPT Workspaceは、スプレッドシートのデータクレンジング、数式生成、選択範囲の分析、分類、自動化支援など、Google WorkspaceにAI支援をもたらします。生データ、検証チェック、クレンジングの意思決定を自分で管理しながら変換のドラフトやレビューに活用できます。GPT Workspaceにアクセスして、ワークフローをぜひ試してみてください。