Excel で欠損値を見つけて埋める方法(当て推量なしで)

欠損値は、スプレッドシートが最も静かに嘘をつく方法です。コスト列の空白セルは、数値が 1 つ欠けるだけでは済みません。すべての AVERAGE をこっそり縮め、あらゆるピボットを歪め、利益計算をエラーに変えてしまいます。

ここでは規律あるワークフローを紹介します。すべての空白を見つけ、それぞれが何を意味するのかを判断し、埋めるべきものだけを埋める——そして何を変更したかの記録を残す、という流れです。

ステップ 1:すべての欠損値を見つける

素早い方法を 3 つ、速い順に紹介します。

ジャンプ →「セル選択」。 データ範囲を選択し、F5 → セル選択 → 空白セル → OK を押します。範囲内のすべての空白セルが選択されるので、塗りつぶし色を付けて見えるようにしておきましょう。

列ごとに数える:

=COUNTBLANK(B2:B1000)

フィルターで絞り込む。 フィルターを追加し(Ctrl+Shift+L)、列のドロップダウンを開いて (空白セル) にチェックを入れると、影響を受けている行が正確にわかります。

偽の空白にも注意してください。スペースだけが入ったセルや、数式が返した空文字列 "" です。COUNTBLANK は "" を数えますが、ジャンプ →「セル選択」→ 空白セル はそれを選択しません。この食い違いは混乱の定番です。

=SUMPRODUCT(--(TRIM(B2:B1000)=""))

この式なら、本当の空白と空白文字だけのセルの両方を数えられます。

ステップ 2:各空白が何を意味するか判断する

多くの人が飛ばしてしまうのがこのステップです。空白には次の可能性があります。

意味	正しい対応
データは存在するが入力されていない	元データから埋める
本当にゼロ	明示的に 0 を入力する
該当なし	意図的であることがわかるよう N/A(文字列)と記す
不明・要確認	フラグを付ける。数字をでっち上げない

「不明」を作り話の数字で埋めるのは、空白のまま残すより悪い選択です。目に見える不確実性を、目に見えないエラーに変換してしまうからです。

ステップ 3:埋めるべきものを埋める

上のセルから下方向に埋める(カテゴリー名がグループごとに 1 回しか現れないレポートのエクスポートでよくあるケース):範囲を選択し、F5 → セル選択 → 空白セル、= を入力して上矢印キーを押し、Ctrl+Enter で確定します。すべての空白に上のセルの値がコピーされます。その後、形式を選択して貼り付けで値に変換しておきましょう。

他の列から計算する。 コストが欠けていて、売上と利益はある場合:

=IF(B2="", C2-D2, B2)

別のシートから検索する:

=IF(B2="", XLOOKUP(A2, Ref!A:A, Ref!B:B, "no match"), B2)

ステップ 4:監査証跡を残す

どの方法で空白を埋めるにしても、どのセルを変更したかを記録してください。ハイライト色でも、「補完済み」ステータス列でも、変更ログでも構いません。未来の自分は、元のデータと再構築したデータを区別する必要に迫られます。

指示ひとつで済ませる方法

ここまでのワークフロー全体は、ブックの中で動くアシスタントへの 1 回の依頼にまとめられます。AI for Excel をサイドバーで開いて、こう伝えます。

「この表の欠損値をすべて見つけて。コストは参照シートから埋められるものは埋め、本当のゼロは 0 にし、残りは新しい Status 列でフラグを付けて、何を変更したか教えて。」

アドインは範囲を読み取り、各補完を適用し、行ごとにステータスを書き込み、結果を要約します。ホームページのデモで、欠けたコストが補完され利益列が書き戻される様子と同じです。書き込み前にブックのスナップショットを取り、書いた内容を検証するので、「AI がデータを埋めた」が「データがどうなったかわからなくなった」を意味することは決してありません。

よくある質問

Excel ですべての空白セルをハイライトするには?

範囲を選択して F5 を押し、セル選択 → 空白セルを選び、選択されている間に塗りつぶし色を適用します。条件付き書式で数式 =ISBLANK(A2) を使えば、今後発生する空白も自動的にハイライトされ続けます。

欠損値はゼロにすべきですか、空白のままにすべきですか?

0 を入力するのは、値が本当にゼロのときだけです。空白は「データなし」を意味し、それをゼロとして扱うと平均や比率が変わってしまいます。値が不明なら、数字をでっち上げるのではなく、不明としてフラグを付けてください。

AI は Excel の欠損データを自動で埋められますか?

はい。ただし 3 つの安全装置を必ず確認してください。埋めた各値がどこから来たのかを示すこと、補完したセルを元データと区別できるようマークすること、書き込む前にシートをバックアップすること。AI for Excel はこの 3 つすべてを備え、必要なら変更全体をロールバックできます。