Excel のドロップダウンリストを開くと、選択肢の末尾に何も書かれていない空欄が並びます。これは元データに空白セルが混ざっているのが原因で、3 つの方法で消せます。あわせて、ためしてみても効かない「空白を無視する」チェックボックスの本当の意味も、Microsoft 公式仕様と Excel 2024 の画面で整理します。
症状の確認 — どこから空欄が来ているか
ドロップダウンを開いたときに末尾に空欄が並ぶときは、入力規則の「元の値」に指定した範囲のなかに空白セルが入っています。原因は次の 3 パターンに分かれます。
原因A: 元データに空白セルが混ざっている
もっとも単純な原因です。たとえば A1:A10 をリストの元の値に指定したとき、A6〜A10 のセルが空のままだと、ドロップダウンには A1〜A5 のデータに加えて A6〜A10 ぶんの空欄が並びます。
原因B: 数式が空文字を返している
セルに =IF(B1="", "", B1) のような数式を入れて、B 列が空のときは空文字を表示するようにしているケースです。見た目は空欄でも、セル自体は数式の結果として空文字を返しているので、入力規則の元の値に含めるとドロップダウンの空欄候補として扱われます。
原因C: 範囲指定が広すぎる
あとから行を増やすつもりで、最初から A1:A100 のように広めに範囲を取っているケースです。実データが 5 件しかないと、残りの 95 セルがすべて空欄候補になります。
このあと、しばしば誤解される「空白を無視する」チェックボックスの本当の意味を整理します。3 パターンとも、このチェックの ON/OFF では消えません (理由はすぐあとで説明します)。具体的な対処法は対処 1〜3 として、このあと順番に紹介します。
「空白を無視する」チェックボックスの本当の意味
入力規則のダイアログには「空白を無視する(B)」というチェックボックスがあります。名前から「リストに混じった空白を無視 (= 表示しない) する設定」だと思われがちですが、実際は違います。
正しい意味: 入力先のセルを空のままにしておけるか
「空白を無視する」は、リストの中身を制御する設定ではなく、入力規則を設定したセルを空のままにしておけるかどうかを制御する設定です。具体的には次のように動きます。
| 「空白を無視する」 | セルを空のままにする | リスト内の値 (例: リンゴ) を入れる | リスト外の値 (例: メロン) を入れる |
|---|---|---|---|
| ON (チェックあり) | OK | OK | エラー |
| OFF (チェックなし) | エラー (空入力 NG) | OK | エラー |
つまり ON と OFF で違うのは、セルを空のまま放置できるかどうかだけです。リストの中身 (=ドロップダウンを開いたときに並ぶ候補) は、ON にしても OFF にしても変わりません。
実機で確かめる: ON でも OFF でも末尾の空欄は消えない
同じ元データ (空白セルを含む範囲) を指定したセルを 2 つ用意し、片方は「空白を無視する」を ON、もう片方を OFF にして、両方のドロップダウンを開いてみます。
並びを見比べると、ON でも OFF でもドロップダウンの中身は同じで、両方とも末尾に空欄が並んでいることがわかります。
違いがあらわれるのはセルに何も入力しないで Enter したときです。ON のセルはそのまま空のセルになりますが、OFF のセルは「この値は、このセルに定義されているデータ入力規則の制限を満たしていません。」というエラーが出て確定できません。
誤解されやすい理由: 既定で ON になっている
入力規則ダイアログを開いた時点で「空白を無視する(B)」はもとから ON になっています。新しいセルにこれから設定するときも、既に設定したセルを開きなおしたときも、同じく ON で表示されます。
そのため「空欄の候補が並んでいる → このチェックを外せば消えるかも」と試したくなり、ON / OFF を切り替えても変化がないので余計に混乱してしまいます。実際にはこのチェックはリストの並びと無関係なので、何度切り替えても表示は変わりません。
Microsoft の公式ヘルプ Apply data validation to cells の英語版にも、このチェックは「空白 (null) 値の扱い」を決める設定で、リストの中身を変えるものではないと書かれています。原文は To specify how you want to handle blank (null) values, select or clear the Ignore blank check box. という記述です。日本語の Microsoft サポートページでは「空白のチェックを無視する」と訳されている箇所もありますが、実機 Excel 2024 のダイアログでは「空白を無視する(B)」と表示されます。
では実際に末尾の空欄を消すには、入力規則の「元の値」のほうを工夫する必要があります。このあと、対処 1〜3 として 3 つの方法を順番に紹介します。
対処 1: 元データの範囲を絞る (もっとも簡単)
もっとも単純な方法は、入力規則の「元の値」に指定する範囲を、実データの行数だけに変えることです。たとえば A1:A100 としていたところを A1:A10 にすれば、空白セル A11:A100 はそもそも候補に入らなくなります。
この方法の欠点は、データを増やしたときに範囲を手動で広げる必要があることです。「行を追加するだけで自動でリストが広がる仕組み」がほしい場合は、このあと紹介する対処 2 (テーブル化) や対処 3 (FILTER) を選んでください。
動的に伸びるドロップダウンの作り方をまとめて知りたい場合は、 Excelで動的に更新されるドロップダウンリストの設定方法(入力規則：リスト) をあわせて参照してください。
対処 2: テーブル化で空欄が出ない仕組みにする (おすすめ)
元データをテーブル化すると、行を追加したときに自動でテーブル範囲が広がります。空白行を含めずに実データだけをテーブル化しておけば、ドロップダウンの末尾に空欄が並ばず、行追加にも追従できる状態になります。テーブル内に空白行や =IF(...,"",...) のように空文字を返す数式が混ざる場合は、対処 3 (FILTER) を組み合わせて除外してください。
手順
- 元データの範囲 (例: A1:A6、ヘッダー「果物」+ データ 5 件) を選択して
Ctrl + Tでテーブル化します。 - テーブル名を覚えやすい名前 (例:「果物テーブル」) にリネームします (テーブルを選択 → 「テーブルデザイン」タブ → 「テーブル名」)。
- 「数式」タブ → 「名前の管理」 → 「新規作成」で、名前 (例:「果物リスト」)、参照範囲に
=果物テーブル[果物]を指定して登録します。登録すると下のように「果物テーブル」(テーブル) と「果物リスト」(名前) の 2 行が並びます。
- 入力規則を設定したいセルで「データ」タブ → 「データの入力規則」を開き、「入力値の種類」を「リスト」、「元の値」に
=果物リストを入力して OK を押します。
OK を押したあとに、もう一度同じセルで入力規則を開き直すと、「元の値」が =果物リスト のまま保持されています。これでテーブルの値を間接的に参照できる状態になりました。
入力規則の「元の値」に =果物テーブル[果物] を直接入力すると、OK ボタンを押した直後に「元の値」欄が空に戻され、設定が反映されません。Excel の入力規則では、テーブルの構造化参照を直接書き込めない仕様になっています。必ず「名前の管理」で名前を作って =果物リスト のように渡してください。詳しい挙動は Excelで動的に更新されるドロップダウンリストの設定方法(入力規則：リスト) でも紹介しています。
登録できたら、テーブルに新しい行を追加するだけでドロップダウンの候補も増えます。たとえば A7 セルに「メロン」と入れると、テーブル範囲は A1:A7 に拡張され、入力規則のドロップダウンにも「メロン」が並びます。実データだけをテーブル化していれば、新しい行を追加してもテーブル範囲には空欄が含まれません。なお、テーブル内に空白行や空文字を返す数式が残っているケースまでは、テーブル化だけでは除外できません。その場合は対処 3 (FILTER) で空白を除外してください。
対処 3: FILTER 関数で空白を除外する (Excel 2021 以降の Windows 版)
本記事で対象としている Windows デスクトップ版では、Excel 2021 / 2024 / Microsoft 365 で FILTER 関数を使えます。もとの範囲から空白を除外した値だけを別の場所にスピル展開し、それを入力規則の元の値として使う流れです。対処 2 のテーブル化と組み合わせれば、行追加への追従と空白除外をまとめて実現できます。
手順
- 別シートまたはあいているセル (例: B1) に
=FILTER(果物テーブル[果物], 果物テーブル[果物]<>"")を入力します。空白を除いた値が B1 から下方向にスピル展開されます。 - 入力規則の「元の値」に
=$B$1#と入力します (末尾の#はスピル範囲全体を指す書き方です)。
この方法のいいところは、もとのデータが空文字を返す数式 (=IF(...,"",...)) で空欄を作っているケースでも、<>"" の条件で除外できることです。記事冒頭で挙げた原因 B (数式が空文字を返す) のケースでも使えます。
FILTER 関数のもっと詳しい使い方は ExcelでFILTER関数を使って空白や0を除外する方法｜左詰め・上詰めの式 でまとめています。UNIQUE と組み合わせて重複と空白を同時に除外する方法は UNIQUE関数の結果から空白やゼロを除外する計算設定 で扱っています。
本記事で対象としている Windows デスクトップ版では、FILTER 関数が使えるのは Excel 2021 / 2024 / Microsoft 365 です。Excel 2019 以前で同じ式を入れると #NAME? エラーになるため、その場合は対処 2 (テーブル化 + 名前定義) を選んでください。なお FILTER 関数自体は Excel for the web、Mac 版、iPad / iPhone / Android 版にも提供されています (Microsoft の FILTER 関数公式ヘルプ 参照)。本記事は画面構成が異なる Windows 版を前提に解説しています。
それでもうまく動かないとき + まとめ
3 つの方法を試してもうまく動かないときは、別の場所に原因があります。よくあるケースを下にまとめます。
- そもそもドロップダウンの▼が表示されない、リストが開かない場合は、 【Excel】ドロップダウンリスト（入力規則）が表示されない・選択肢が出ないときの対処法 を参照してください。
- セルに値を入れたときに「この値は、このセルに定義されているデータ入力規則の制限を満たしていません。」というエラーが出る場合は、 Excelで「この値は、このセルに定義されているデータ入力規則の制限を満たしていません。」と出る原因と対処法 でケース別に整理しています。
3 つの方法のおすすめ早見表
| 方法 | 向いている場面 | 制限 |
|---|---|---|
| 対処 1: 範囲を絞る | データの件数がほぼ固定 | 行を増やしたら手動で範囲を広げる必要あり |
| 対処 2: テーブル化 + 名前定義 | 行を追加して自動で増やしたい (どのバージョンでも使える) | テーブル化の準備が必要 |
| 対処 3: FILTER + スピル参照 | 行追加に追従しつつ、数式 "" や空白も同時に除外したい | Windows 版 Excel 2021 / 2024 / Microsoft 365 (Excel 2019 以前は #NAME?) |
「空白を無視する」のチェックは入力先のセルに空入力を許すかどうかの設定であって、リストの並びとは関係がない、という点をおさえておけば、リストの末尾に空欄が並んでも落ち着いて対処できます。元データの範囲をどう指定するかが本質なので、データの増え方に合わせて 3 つの方法から選んでください。
コメント