実データには、値が入っていない「欠損」がつきものです。
アンケートの未回答、売上の未入力、GA4イベントの欠落——どれも珍しくありません。欠損をそのままにすると、件数の数え方や平均の計算結果が静かにズレ、気づかないまま誤った結論を出すことがあります。
本稿では、欠損値とはなにか、なぜ起きるのかを整理したうえで、「削除・補完・集計除外」という3つの対処を、どんなときにどれを選ぶかの判断軸とともに解説します。あわせて、SQL・BigQuery・Excelでの欠損の数え方と扱い方を、製品差を含めて確認します。
NULL やExcelの空白とどう対応するのか欠損値（missing value）とは、本来あるはずの値が記録されていない状態を指します。
実務での扱いはこう整理できます。「欠損は計算上の0でも空文字でもなく、『値がない』という独立した状態であり、集計の前にどう扱うかを必ず決めておく」。
たとえばアンケートで「年収」欄が空のとき、その人の年収が0円なのではなく、単に答えが得られていません。これを0として平均に含めれば平均は不当に下がり、逆に黙って無視すれば「何人ぶんの平均なのか」が変わります。
欠損は、放置すると分母と分子の両方を静かに動かす点が厄介です。
データベースの世界では、この「値がない」状態を NULL という特別なマーカーで表します。NULL は0でも空文字（''）でもなく、「未知・該当なし」を意味します。Excelでは多くの場合「空白セル」が欠損にあたります。
まずは、扱っている環境で欠損がどう表現されているかを掴むことが出発点です。
最もよくある事故が、欠損と0、欠損と空文字の混同です。これらは見た目が似ていても意味が異なります。
| 状態 | 意味 | 平均への影響 |
|---|---|---|
| NULL ／ 空白 | 値がない（未知・未入力） | 多くの集計関数は無視する（後述） |
| 0 | 値があり、それが0 | 件数にも合計にも含まれる |
| 空文字 ” | 長さ0の文字列という「値」 | 数値ではないが、NULLとは区別される |
たとえば「購入金額が NULL」は未決済や未入力を、「購入金額が 0」は無料購入や全額値引きを意味するかもしれません。この2つを取り違えると、売上の集計が実態とずれます。
前処理の最初に、欠損が NULL なのか0なのか空文字なのかを確認してください。
対処を選ぶ前に、欠損が「なぜ」生じたかを確認します。原因によって、無視してよいのか、埋めるべきなのか、そもそも分析の前提を疑うべきなのかが変わるためです。実務でよく出会うのは次のような欠損です。
ここで一段だけ踏み込むと、欠損の起き方は「ランダムに近いか、偏っているか」で性質が分かれます。たとえば高所得者ほど年収を答えない傾向があるなら、欠損は偏っており、欠損行を単純に捨てるだけで平均が下振れします。
欠損が偏っている可能性があるときは、削除や単純補完が分析結果を歪めうる、という警戒だけは持っておいてください。欠損の発生メカニズム（MCAR / MAR / MNAR）の理論的分類は、より進んだ推測統計の領域として別記事に譲ります。
欠損への対処は、大きく3つに分けられます。どれを選ぶかは、欠損の量・原因・分析の目的で決めます。
| 対処 | 何をするか | 向いている場面 |
|---|---|---|
| 削除 | 欠損を含む行（または列）を分析から外す | 欠損がごく少数で、ランダムに近いとき |
| 補完 | 欠損を代表値や推定値で埋める | 件数を保ちたい・後続処理が欠損を許さないとき |
| 集計除外 | 値は埋めず、集計時に欠損を無視する | その指標の集計だけ欠損を除きたいとき |
最も単純なのが、欠損を含む行を丸ごと除く方法（リストワイズ削除）です。手軽ですが、欠損が多いと大量の行が消え、サンプルが小さくなったり偏ったりします。目安として、まず「全体の何%が欠損か」「欠損行に偏りがないか」を確認してから判断します。
1列の欠損のために、他の列の有効なデータまで捨てている点にも注意が必要です。
補完（imputation）は、欠損を何らかの値で埋める方法です。代表的なのは平均値・中央値・最頻値での補完ですが、外れ値や歪んだ分布では平均より中央値が無難です。
時系列なら直前の値を引き継ぐ（前方補完）こともあります。注意点は、補完すると「実際には観測していない値」を増やすため、ばらつき（分散・標準偏差）が実態より小さく見えやすいことです。補完したことと、その方法は必ず記録します。
代表値そのものの選び方は別記事「平均値・中央値・最頻値の違いと使い分け」を参照してください。
3つ目が、値は埋めずに集計の段階で欠損を無視する方法です。ここが最も重要かつ誤解されやすい点ですが、SQLやBigQueryの集計関数、Excelの多くの関数は、欠損（NULL・空白）を最初から無視します。
たとえば AVG(amount) は NULL の行を分母にも分子にも入れず、「値がある行だけ」で平均を出します。つまり、特別な処理を書かなくても、既定で集計除外が起きているのです。
この挙動は便利な一方で、落とし穴にもなります。「全1,000人の平均」のつもりが、欠損を除いた「700人の平均」になっていることに気づかない、という事故が典型です。集計除外を使うときは、「いま何件を分母にしているのか」を常に意識してください。次章で、その数え方を具体的に確認します。
ここでは「欠損を数える → 除外して集計する → 補完する」の3段階で、各環境の書き方を示します。
欠損処理の基本は、まず欠損が何件あるかを数えることです。SQLでは COUNT(*) と COUNT(列名) の違いがそのまま使えます。COUNT(*) は行数を数え、COUNT(列名) はその列が NULL でない行だけを数えます。差を取れば欠損数が分かります。
SQL – PostgreSQL / 標準SQL：欠損数を数える
SELECT
COUNT(*) AS rows_total, -- 全行数
COUNT(amount) AS amount_filled, -- amountがNULLでない行数
COUNT(*) - COUNT(amount) AS amount_missing -- 欠損数
FROM purchases;欠損を除いて集計したいだけなら、特別な記述は不要です。AVG(amount) も SUM(amount) も NULL を自動的に無視します。欠損行を明示的に外したいとき、または欠損行だけを見たいときは IS NULL / IS NOT NULL を使います。
NULL は = や <> では判定できない（amount = NULL は真にならない）ため、必ず IS NULL を使う点に注意してください。
SQL – 欠損行の抽出とNULL補完
-- 欠損行だけを抽出して中身を確認する
SELECT * FROM purchases WHERE amount IS NULL;
-- 欠損を埋めて集計する（NULLを0とみなして合計）
SELECT SUM(COALESCE(amount, 0)) AS total_amount
FROM purchases;COALESCE(amount, 0) は「amount が NULL なら0を、そうでなければ amount を返す」関数です。
COALESCE は引数を左から見て最初の非 NULL を返すため、COALESCE(col_a, col_b, 0) のように複数の代替候補を並べられます。なお SUM(amount)（COALESCEなし）と SUM(COALESCE(amount,0)) は、NULL 以外の値が1件でもある通常ケースでは同じ合計値になりやすい一方、全行が NULL の場合は結果そのものが変わります。
SUM(amount) は NULL を返し、SUM(COALESCE(amount, 0)) は 0 を返します（前者は「集計対象が無い」、後者は「合計0」を意味する）。0として埋めてよいデータか、また欠損を0とみなした集計が妥当かは、必ず確認してください。
「欠損を別の値に置き換える」関数は、製品によって名前が分かれます。意味はほぼ同じでも書き方が違うため、環境に合わせます。
| 製品 | 欠損を置換する代表的な関数 | 備考 |
|---|---|---|
| 標準SQL / PostgreSQL | COALESCE(x, 代替) | 複数引数を取れる。標準SQLに準拠 |
| MySQL / BigQuery | IFNULL(x, 代替) | 2引数。COALESCEも利用可 |
| SQL Server | ISNULL(x, 代替) | 2引数。COALESCEも利用可 |
| Oracle | NVL(x, 代替) | 2引数。COALESCEも利用可 |
単純な「NULLを別の値に置き換える」用途では、これらは似た使い方ができます。
ただし戻り値の型の決まり方や引数の評価順には製品差があるため、複雑な式やネストした置換では各製品の仕様を確認してください。迷ったときは、どの主要製品でも使え、複数候補を並べられる COALESCE を選ぶと移植性が高くなります。
なお Oracle では空文字 '' が NULL として扱われるなど、空文字と NULL の扱いに製品差があります。空文字混じりのデータを扱うときは、対象製品の仕様を確認してください。
BigQueryでも COUNT(*) と COUNT(列) の差で欠損数が出せますが、COUNTIF を使うと条件を直接書けて読みやすくなります。
SQL – BigQuery：COUNTIFで欠損数を数える
SELECT
COUNT(*) AS rows_total,
COUNTIF(amount IS NULL) AS amount_missing,
COUNTIF(amount IS NOT NULL) AS amount_filled
FROM `project.dataset.purchases`;欠損を埋めるときは IFNULL(amount, 0) または COALESCE(amount, 0) を使います。
BigQueryで特に注意したいのが、GA4エクスポートのようなネスト構造です。event_params を UNNEST して特定のキー（例：value.int_value）を取り出すと、そのキーを持たないイベントでは値が NULL になります。
さらに「イベント自体が発生しなかった」ケースは、UNNEST しても行として現れないため、欠損（NULL）ではなく「行がない」状態になります。NULL の欠損と「行の不在」は別物で、COUNTIF(... IS NULL) では後者を数えられない点に注意してください。
BigQueryはスキャンした列のデータ量で課金されるため、欠損調査でも対象の列・期間を絞るのが基本です。
Excelでは、欠損はおもに空白セルとして現れます。数え方と扱い方は次の関数が対応します。
| やりたいこと | Excel 関数 / 操作 |
|---|---|
| 空白セルの数を数える | =COUNTBLANK(範囲) |
| 値が入っているセルの数 | =COUNT(範囲)（数値）／ =COUNTA(範囲)（空白以外すべて） |
| 空白を除いて平均 | =AVERAGE(範囲)（空白は自動で無視される） |
| 条件付きで平均（0を除くなど） | =AVERAGEIF(範囲, “<>0”) |
| 空白を固定値で補完 | =IF(セル=””, 0, セル) |
ここでも要注意なのが、AVERAGE は空白セルを無視しますが、0 が入ったセルは計算に含めるという点です。
SQLの集計と同じ考え方で、「空白＝除外、0＝計算対象」と覚えてください。逆に、本来は欠損なのに前工程で 0 が埋められていると、平均が下がって見えます。また、数式が返す空文字 "" は、見た目は空でも COUNTBLANK では空白として数えられる一方、COUNTA ではカウントされるなど扱いが分かれます。
空欄が「本当の空白」か「空文字」かで挙動が変わる点に注意してください。
欠損を黙って埋めない。 補完は手軽ですが、観測していない値を増やす操作です。とくに平均値補完はばらつきを実態より小さく見せます。補完したか、どの方法かを必ず記録し、可能なら補完前後の結果を比較します。
「集計除外」は既定で起きている。 AVG や AVERAGE は欠損を自動で無視します。便利な一方、分母が知らぬ間に減るため、「いま何件で計算しているか」を常に確認します。全体件数と有効件数を併記するのが安全です。
NULL の比較は = ではなく IS NULL。 列 = NULL は真になりません。欠損の抽出・条件分岐では IS NULL / IS NOT NULL を使います。Excelでも空白判定は ="" や ISBLANK を用います。
0・空文字・NULL を区別する。 「値がない」と「値が0」「空文字」は意味が違います。前処理の最初にどれなのかを確定させないと、合計・平均・件数のすべてがずれます。
欠損が偏っているときは削除・単純補完に注意。 欠損の起き方に偏りがあると、行削除や平均補完で結論が歪みます。欠損率と、欠損行の偏りの有無を確認してから対処を選びます。
欠損値は「値がない」という独立した状態で、データベースでは NULL、Excelでは空白として現れ、0や空文字とは区別されます。
対処は削除・補完・集計除外の3つで、欠損の量・原因・目的で選びます。とくに重要なのは、SQL・BigQuery・Excelの集計関数が欠損を既定で無視する（＝集計除外がデフォルト）という挙動で、これを知らないと分母が静かに変わります。
数えるときは COUNT(*) と COUNT(列) の差（Excelなら COUNTBLANK）、埋めるときは COALESCE 系の関数を使いますが、関数名や空文字の扱いには製品差があるため、最終的な構文は使用環境で検証してください。