はじめに
Excelでデータを集計しようとすると、値が入っていないセル、#N/Aと表示されたセル、想定より大きい数値などが見つかることがあります。これらをすべて「欠損データ」として削除・置換すると、本来必要な情報まで失うおそれがあります。
Excelでのデータクレンジングは、データの状態を見分けてから、必要な処理を判断する作業です。この記事では、空欄・数式エラー・範囲外の値をフィルターと関数で確認し、元データを残したまま修正・保留・除外を判断する基本手順を説明します。
Excelデータクレンジングの基本手順
最初に、取り込んだ元ファイルを変更せずに残し、作業用のコピーを作ります。削除や値の上書きから始めず、次の順序で進めると、問題が起きたときも元の状態へ戻せます。
- 正常なデータの条件を決める:対象の列、必須入力かどうか、数値の範囲、単位を確認する。
- 問題の候補を検出する:フィルターや判定用の列で、空欄・エラー・範囲外を探す。
- 原因と扱いを決める:原データを調べ、修正できるものと判断を保留するものを分ける。
- 処理結果を確認する:変更前後の件数・値・集計結果を比べる。
例えば、測定値の列では「0~100の数値が正常」という条件を設定する場合があります。ただし、これは説明用の条件です。実際には業務ルールや単位を確認してから範囲を決めます。
空白セル・空文字・#N/Aを区別する
Excelでは、画面上で何も表示されないセルが、必ずしも同じ状態とは限りません。特に、未入力の空白セル、数式が返す空文字列、#N/Aのエラー値は別のものです。
| データの状態 | 例 | 確認の手がかり | 最初に確認すること |
|---|---|---|---|
| 空白セル | 何も入力していないA2 | ISBLANK(A2)がTRUE |
未入力が許される項目か |
| 空文字列 | A2に=""の数式がある |
ISBLANK(A2)はFALSE |
数式が空文字を返す理由 |
#N/Aエラー |
検索関数が該当データを見つけられない | ISNA(A2)がTRUE |
参照先・検索値・照合条件 |
| 範囲外の数値 | 0~100が正常な列に150 |
数値か確かめてから範囲判定 | 単位や入力ミス、例外値の可能性 |
#N/Aは、値が見つからないときなどに生じるExcelのエラー値です。空白セルと同じ意味で処理することはできません。 また、文字列として入力された「#N/A」とエラー値#N/Aも区別が必要です。Power Queryで扱うnullも、Excelワークシート上の空白やエラーと無条件に同一視しないようにします。
空欄とエラー値を確認する方法
見出し行を含む表を選び、Excelの[データ]タブから[フィルター]を設定します。対象列の[(空白)]などで候補を絞りますが、見た目が空白でも数式が入っている場合があります。最終的な状態の区別は判定用の数式でも確認します。
たとえばA列に元データがある場合、隣のB列に「判定」という見出しを付け、B2へ次の式を入力して最終行までコピーします。元のA列は書き換えません。
=IF(ISBLANK(A2),"未入力","入力あり")
ISBLANKは、参照先が実際に空白セルである場合にTRUEを返します。A2に=""という数式が入っている場合、画面には何も表示されなくても、判定は「入力あり」になります。
#N/Aエラーだけを調べる場合は、次の式を使えます。
=ISNA(A2)
TRUEならA2は#N/Aエラー値です。検索値が見つからないことが原因なら、検索に使う値と参照範囲を確認します。エラーを一律に空欄やゼロへ置き換えると、原因を見失う場合があります。
空白・空文字・エラーを一つの判定列で区別したい場合には、次のように確認する順序を決められます。
=IF(ISNA(A2),"#N/A",IF(ISERROR(A2),"その他エラー",IF(ISBLANK(A2),"空白",IF(A2="","空文字","値あり"))))
この式は、エラー値を先に判定することで、エラーが混じる列でも空欄かどうかの比較へ進まずに分類できます。元データを直接変換する式ではありません。
範囲外の値を検出する方法
数値の列で正常な範囲が0~100と決まっている場合は、A列を参照して次の判定式を使えます。
=IF(ISNUMBER(A2),IF(OR(A2<0,A2>100),"範囲外","範囲内"),"要確認(数値以外)")
この例では、A2が80なら「範囲内」、150や-5なら「範囲外」と表示されます。一方、空白や#N/A、文字列として保存された"80"は「要確認(数値以外)」となります。数値かどうかを先に調べることで、異なる種類のデータを無理に数値として比較することを避けられます。
ただし、数値として入力されていても、単位の誤りや別の測定条件が原因で範囲から外れているかもしれません。範囲外と検出した時点で値が誤りだと決めつけず、元の記録を確認します。
検出したデータを修正・保留・除外する判断
異常の候補を見つけたら、まず「なぜその値になったか」を調べます。必要な入力が抜けているなら入力元へ確認し、検索式の#N/Aなら参照範囲や照合方法を見直します。範囲外の数値は、記録・単位・業務上の例外を確認してから判断します。
正しい値を根拠とともに特定できる場合は、作業用コピーを修正し、修正理由を残します。原因が分からない場合は勝手に補完せず「要確認」として保留します。除外が必要な場合も、除外の基準と件数を記録し、集計結果への影響を確認します。
例えば、数値の欠損をすべて0に置き換えると、「測定してゼロだった」という意味と「測定できなかった」という意味が混ざることがあります。後の平均値や件数に影響するため、一律置換は避けます。欠損値や異常値そのものの考え方は「外れ値・異常値・欠損値の違い」で整理できます。
作業後に確認すること
判定列で対象行を絞り、修正した件数、保留した件数、除外した件数を記録します。処理前後の行数や重要な集計値も比較し、必要なデータまで消していないかを確認してください。
元の列と判定列を残しておけば、どの条件でどの行を処理したかを後から追えます。再利用する手順では、正常範囲や判定式を作業者によって変えないことも重要です。
まとめ
Excelのデータクレンジングでは、まず元データを保護し、空白セル・空文字・#N/A・範囲外の数値を区別して検出することが基本です。ISBLANK、ISNA、ISNUMBERとIFなどを使えば、元の値を上書きせずに確認用の列を作れます。
そのうえで原因を調べ、根拠がある値だけを修正し、不明なものは保留、除外したものは件数と理由を記録します。検出と修正を分けることが、集計前のデータを安全に扱うための要点です。
関連記事
- Excel・業務自動化とは?関数・Power Query・VBAを体系的に解説 — Excelでのデータ整理から計算・自動化まで、学習の順序を確認できます。