Home/データクレンジング

Excelで欠損値とゼロを区別する理由

OVERVIEW はじめに ―― 0は値であり、欠損は値がない状態である

Excelの集計では、空白セル、0、未回答、対象外、未測定、#N/Aが同じように扱われてしまうことがあります。しかし、これらは同じ意味ではありません。

0は「値が0だった」ことを表す有効なデータです。一方、欠損値は「値が分からない」「まだ入力されていない」「質問の対象ではない」など、値そのものが存在しない状態を表します。

この違いを無視して欠損を0に置き換えると、平均、合計、回答率、構成比、グラフの見え方が変わります。本稿では一つのサンプル表を最後まで使い、0と欠損を分けて扱う方法を確認します。

分類 =IFERROR(IF(ISNUMBER(C2),IF(C2=0,"ゼロ","数値"),IF(C2="","空白","欠損")),"欠損")
0の件数 =COUNTIF(D2:D10,"ゼロ")
欠損の件数 =COUNTIF(D2:D10,"欠損")
数値のみ平均 =AVERAGE(FILTER(C2:C10,ISNUMBER(C2:C10)))

最初に、記事全体で使用するサンプルデータを作る

STEP 1 0、空白、未回答、対象外が混在した9件のデータを用意する

A列に「ID」、B列に「元データ」、C列に「数値化後」を入力します。実務でよく混ざる、実際の0、空白、未回答、対象外、未測定、エラー値を含めます。

A B C
1 ID 元データ 数値化後
2 001 120 120
3 002 0 0
4 003 85 85
5 004
6 005 未回答 #N/A
7 006 対象外 #N/A
8 007 150 150
9 008 未測定 #N/A
10 009 0 0

Excelで再現する場合は、B2:B10に元データを入力し、C2へ次の数式を入力してC10までコピーします。

C2 =IF(ISNUMBER(B2),B2,IF(B2="", "",NA()))

数値はそのまま残し、空白は空白のまま、文字で入力された「未回答」「対象外」「未測定」は#N/Aとして残します。ここでは欠損を0へ変換しません。

POINT

欠損値を0へ置き換えるかどうかは、単なる見た目の問題ではありません。0として集計に入れると、平均や割合が実際より低く見えることがあります。

0と欠損は、データとしての意味が違う

STEP 2「値が0」と「値がない」を分けて考える

0は、測定・回答・入力の結果として値が0だったことを意味します。たとえば売上0円、来客数0人、購入回数0回は、それ自体が有効なデータです。

一方、空白、未回答、対象外、未測定は、値が0だったことを示しているわけではありません。値が得られていない、またはその項目を聞く必要がなかった状態です。

0 値が0である。集計に含めるべき有効な値。
空白 未入力、入力漏れ、まだ分からない値。
未回答 回答が得られなかった値。0とは限らない。
対象外 質問や測定の対象ではない値。欠損とは別管理する場合もある。
未測定 測定されていない値。測定すれば0以外の値かもしれない。

この区別を失うと、「0件だった」のか「分からなかった」のかを後から判断できなくなります。

COUNTIFで、0・空白・欠損の件数を分けて確認する

STEP 3D列へ値の分類を作る

D1に「分類」と入力し、D2へ次の数式を入力してD10までコピーします。

D2 =IFERROR(IF(ISNUMBER(C2),IF(C2=0,"ゼロ","数値"),IF(C2="","空白","欠損")),"欠損")

IFERRORを組み合わせることで、C列の#N/Aを「欠損」として分類できます。

B C D
1 元データ 数値化後 分類
2 120 120 数値
3 0 0 ゼロ
4 85 85 数値
5 空白
6 未回答 #N/A 欠損
7 対象外 #N/A 欠損
8 150 150 数値
9 未測定 #N/A 欠損
10 0 0 ゼロ
0の件数 =COUNTIF(D2:D10,"ゼロ")
空白の件数 =COUNTIF(D2:D10,"空白")
欠損の件数 =COUNTIF(D2:D10,"欠損")
有効数値の件数 =COUNTIF(D2:D10,"数値")+COUNTIF(D2:D10,"ゼロ")

欠損を0に置き換えると、平均が変わる

STEP 4正しい平均と、欠損を0にした平均を比較する

C列には数値、空白、#N/Aが混在しています。#N/Aがある範囲へAVERAGEをそのまま適用するとエラーになります。ここではFILTERで数値だけを取り出して平均します。

数値のみ平均 =AVERAGE(FILTER(C2:C10,ISNUMBER(C2:C10)))

この式では、120、0、85、150、0の5件を平均します。結果は71です。0は有効な数値なので平均に含めます。

一方、欠損や空白を0に置き換えて9件すべてで平均すると、結果は約39.44になります。#N/Aだけでなく、空文字列も0として扱うため、IF(C2:C10="",0,C2:C10)を挟んでいます。

欠損を0扱い =AVERAGE(IFERROR(IF(C2:C10="",0,C2:C10),0))
集計方法 対象 結果
数値だけを平均 120, 0, 85, 150, 0 71
欠損・空白を0扱い 120, 0, 85, 0, 0, 0, 150, 0, 0 39.44

同じデータでも、欠損を0として扱うかどうかで平均は大きく変わります。0への置換は、意味が確認できる場合だけ行います。

欠損の理由を分けて、処理方針を決める

STEP 5E列へ処理方針を作る

欠損をすべて同じ「欠損」としてまとめるだけでは、後から処理方針を判断しにくくなります。E1に「処理方針」と入力し、E2へ次の数式を入力してE10までコピーします。

E2 =IFS(ISNUMBER(B2),"集計対象",B2="","未入力として確認",B2="未回答","回答なしとして除外",B2="対象外","母数から除外",B2="未測定","再測定または欠損扱い",TRUE,"要確認")
B D E
1 元データ 分類 処理方針
2 120 数値 集計対象
3 0 ゼロ 集計対象
4 85 数値 集計対象
5 空白 未入力として確認
6 未回答 欠損 回答なしとして除外
7 対象外 欠損 母数から除外
8 150 数値 集計対象
9 未測定 欠損 再測定または欠損扱い
10 0 ゼロ 集計対象

「未回答」と「対象外」はどちらも数値ではありませんが、集計上の扱いは異なります。対象外は母数から除外する、未回答は回答率の分母に含める、というように分析目的によって処理方針を分けます。

LET・MAPで、分類と処理方針を一括化する

STEP 6LET関数で値と分類に名前を付ける

分類式は長くなりやすいため、LET関数で元データと数値化後の値へ名前を付けると読みやすくなります。

D2 =LET(value,C2,IFERROR(IF(ISNUMBER(value),IF(value=0,"ゼロ","数値"),IF(value="","空白","欠損")),"欠損"))

ここでは、数値化後の値をvalueとして定義しています。LETによって結果が変わるわけではありませんが、何を判定しているか追いやすくなります。

STEP 7MAP関数でB2:C10を一度に分類する

F1に「MAP分類」と入力し、F2へ次の数式を入力します。

F2 =MAP(B2:B10,C2:C10,LAMBDA(raw,value,IFERROR(IF(ISNUMBER(value),IF(value=0,"ゼロ","数値"),IF(value="","空白","欠損")),"欠損")))

結果はF2:F10へスピルします。分類規則を一つの式にまとめることで、欠損理由を追加した場合の修正箇所を限定できます。

※MAP関数を利用できないExcelでは、STEP 6の数式を各行へコピーしてください。

0、空白、欠損を別々に検証する

STEP 8集計前に件数を確認する

最後に、分類列を使って件数を確認します。

数値の件数 =COUNTIF(D2:D10,"数値")
0の件数 =COUNTIF(D2:D10,"ゼロ")
空白の件数 =COUNTIF(D2:D10,"空白")
欠損の件数 =COUNTIF(D2:D10,"欠損")

このサンプルでは、数値が3件、0が2件、空白が1件、欠損が3件です。数値3件とゼロ2件を合わせた5件が集計対象となります。0を欠損に含めてしまうと、有効な0の情報が消えてしまいます。

IMPORTANT

集計前に、0の件数と欠損の件数を別々に確認します。0が多いデータなのか、欠損が多いデータなのかで、解釈は大きく変わります。

まとめ ―― 欠損を0にする前に、0の意味を確認する

本稿ではA2:C10の同じサンプルデータを使い、0、空白、未回答、対象外、未測定、#N/Aを分けて扱う方法を確認しました。

数値化 =IF(ISNUMBER(B2),B2,IF(B2="", "",NA()))
分類 =IFERROR(IF(ISNUMBER(C2),IF(C2=0,"ゼロ","数値"),IF(C2="","空白","欠損")),"欠損")
数値のみ平均 =AVERAGE(FILTER(C2:C10,ISNUMBER(C2:C10))) → 71
欠損を0扱い =AVERAGE(IFERROR(IF(C2:C10="",0,C2:C10),0)) → 39.44
MAP分類 =MAP(B2:B10,C2:C10,LAMBDA(raw,value,...))
  1. 0：値が0であることを示す有効なデータ。欠損ではない。
  2. 空白：未入力、入力漏れ、まだ分からない値として確認する。
  3. 未回答：回答が得られなかった値。回答率の計算では分母・分子の扱いを決める。
  4. 対象外：質問や測定の対象ではない値。母数から除外する場合がある。
  5. 未測定：測定されていない値。再測定できるか確認する。
  6. #N/A：数値化できないことを明示するエラー値。グラフや集計の扱いに注意する。
  7. 検証：集計前に、数値、0、空白、欠損の件数を別々に確認する。

欠損値とゼロを区別する目的は、セルをきれいに埋めることではありません。値が0だったのか、値が得られなかったのかを区別し、分析結果を誤って解釈しないようにすることが重要です。

RELATED

データクレンジングの関連 How-to

このサイトの関連 How-to

⇧