【Excel】データの入力規則リストから空白を削除する5つの方法

Excelのデータの入力規則でドロップダウンリストを作成するとき、元データに空白セルがあると、その空白までリストに表示されてしまいます。これは見た目が悪いうえに操作性も損なうため、できれば避けたいところです。

この記事では、データの入力規則リストから空白を除外する5つの方法を、具体的な手順とあわせてわかりやすく解説します。

空白セルを含むドロップダウンリストを作成すると起こる問題

まず、空白セルを含んだ範囲でドロップダウンリストを作ると何が起こるかを確認しましょう。通常の手順でリストを作成してみます。

手順：

• セル C5 を選択します。
• [データ]タブ >> [データツール]グループ >> [データの入力規則] をクリックします。

• ダイアログボックスが表示されたら、[入力値の種類]で「リスト」を選択します（下図参照）。

• [元の値]欄の右側にあるアイコンをクリックします。

• 続いて、セル B5～B14 を選択し、再度アイコンをクリックして範囲を確定させます。

• 最後に [OK] をクリックします。

これでドロップダウンリストが作成されました。

ご覧のとおり、リストには空白セルも含まれてしまっています。ここからは、これらの空白をドロップダウンリストから除外する方法を順番に紹介していきます。

Excelでデータの入力規則リストから空白を削除する5つの方法

方法1：OFFSET関数を使って空白を除外する

この方法を使えば、列内に空白を残したままでも、新しい項目を追加できる余裕を持たせたドロップダウンリストを作成できます。まずはFILTER関数で元データから空白を取り除いたリストを作りましょう。

データセットにいくつか変更を加えます。

• ドロップダウンリスト用の列の前に新しい列を挿入します。ここでは、元の列を「Name List with Blanks（空白ありの名前リスト）」、新しく作る列を「List without Blanks（空白なしのリスト）」としました。（ドロップダウンリストの作成方法は前述のセクションを参考にしてください。）

手順：

• まず、セル C5 に次の数式を入力します。

=FILTER(B5:B14,B5:B14<>"")

このFILTER関数は 範囲B5:B14 を受け取り、その中の空白セルを判定して、空のセルをリストから除外します。

• Enterキーを押すと、空白を含まない名前リストが表示されます。

• [数式]タブから「名前の管理」を選択し、[新規]をクリックします。

• 範囲に名前を付けます。ここでは「NameNonBlanks」としました。
• [参照範囲]に次の数式を入力します。

=offset(offset!$C$5,0,0,counta(offset!$C$4:$C$16)-1,1)

この例では、C12～C16に新しい名前を追加入力できるようにしつつ、その分の空白がドロップダウンリストに現れないようにしています。「offset!」の部分は使用しているシート名を指しているので、実際のシート名に合わせて書き換えてください。

• [OK] をクリックするとウィンドウが表示されますので、閉じます。

• 次にセル D5 を選択し、[データ] >> [データの入力規則] を開きます。
• [元の値]を「=NameNonBlanks」に変更します。
• [OK] をクリックします。

• セル D5 のドロップダウンリストを開くと、登録済みの名前だけが表示されます。

• 試しに、セル C12～C16 に新しい名前を入力してみましょう。
• そのうえで、データの入力規則を設定したセル D5 のリストを開きます。

新しく入力した名前がドロップダウンリストに自動的に反映されているのがわかります。ただし、C16より下に入力した項目は範囲外のためリストには表示されません。

このアプローチを使えば、データの入力規則リストに空白を出さずに、新規項目用のスペースを確保できます。

関連記事：Excelでドロップダウンリスト（データの入力規則）を作成する8つの方法

方法2：「ジャンプ」コマンドを使って空白セルを一括削除する

前述のセクションで作成したドロップダウンリストには空白が残っています。これを削除するには、次の手順を実行します。

手順：

• セル B5～B14 を選択し、[ホーム]タブ >> [編集]グループ >> [検索と選択] >> [ジャンプ] をクリックします。

• [ジャンプのオプション]で「空白セル」を選択し、[OK] をクリックします。

• これで範囲内の空白セルだけが選択されます。

• 選択された空白セルのどれかひとつを右クリックし、[削除] を選択して空白を削除します。

• ダイアログボックスが表示されたら、「上方向にシフト」を選んで [OK] をクリックします。

• この操作により、元のリストからもドロップダウンリストからも空白が取り除かれます。

この方法なら、ドロップダウンリストから空白セルを簡単かつ直感的に除去できます。

関連記事：複数選択可能なデータの入力規則ドロップダウンリストをExcelで作成する方法

方法3：FILTER関数を使って空白を除外する

この場面でもFILTER関数が活躍します。データセットは前述のセクションのものを使用します。（ドロップダウンリストの作成方法は最初のセクションをご覧ください。）

手順：

• セル C5 に次の数式を入力します。

=FILTER(B5:B14,B5:B14<>"")

FILTER関数は 範囲B5:B14 を対象に空白をチェックし、空のセルをリストから自動的に除外してくれます。

• Enterキーを押すと、空白を含まない名前リストが表示されます。

• しかし、ドロップダウンリストを開いてみると、まだC列由来の空白が残っています。

• そこで、[データ]タブから [データの入力規則] を開きます。
• フィルター後のリストが C5～C11 の範囲になっているため、[元の値]の最終セルを C11 に変更します。

• [OK] をクリックすれば、ドロップダウンリストから空白セルが消えます。

これは非常に効率的な、ドロップダウンリストから空白を取り除く方法です。

関連記事：フィルター付きのデータの入力規則ドロップダウンリスト（2つの実例）

合わせて読みたい記事：

• Excelのデータの入力規則でカスタムVLOOKUP数式を使う方法
• [解決済み] Excelでコピー＆ペースト時にデータの入力規則が機能しない場合の対処法
• テーブルからデータの入力規則リストを作る3つの方法
• 1つのセルに複数のデータの入力規則を適用する（3つの実例）
• Excelで英数字のみ入力を許可するデータの入力規則（カスタム数式を使用）

方法4：IF・COUNTIF・ROW・INDEX・SMALL関数を組み合わせて空白を除外する

IF、COUNTIF、ROW、INDEX、SMALL関数を組み合わせることでも、データの入力規則リストから空のセルを除外できます。少し複雑ですが、手順を追えば必ず理解できます。データセットは前述のセクションのものを使い、ドロップダウンリストの作成方法は最初のセクションを参照してください。

手順：

• まず、セル C5 に次の数式を入力します。

=IF(COUNTIF($B$5:$B$14,"?*")<ROW(B5)-4,"",INDEX(B:B,SMALL(IF(B$5:B$14<>"",ROW(B$5:B$14)),ROWS(B$5:B5))))

この数式は大きく2つの部分から構成されています。第1部は COUNTIF($B$5:$B$14,"?*")<ROW(B5)-4,""、第2部は INDEX(B:B,SMALL(IF(B$5:B$14<>"",ROW(B$5:B$14)),ROWS(B$5:B5))) です。

• COUNTIF関数が空白以外のテキストの個数を数えることで、C列に7件の名前が抽出されます。
• ROW関数はセルの行番号を返します。最初の空白セルは B5 から数えて5行目に位置するため、「-4」して比較条件を調整しています。
• ここで Enter キーを押します。

• フィルハンドルを使って、下のセルへ数式をオートフィルします。

• これで空白のない名前リストが完成しました。ただし、データの入力規則リストを開くと、ドロップダウンリストにはまだ空白が残っています。

• そこで、[データ]タブから [データの入力規則] を開きます。
• フィルター後のリストの範囲が C5～C11 なので、[元の値]の最終セルを C11 に変更します。

• [OK] をクリックすると、ドロップダウンリストから空白セルがなくなります。

以上のように、空白を含まないデータの入力規則リストを作成できます。

関連記事：Excel VBAでデータの入力規則リストに初期値を設定する方法（マクロとユーザーフォーム）

方法5：ADDRESS・INDIRECT・COUNTBLANK・IF・SMALL関数を組み合わせて空白セルを除外する

ADDRESS、INDIRECT、COUNTBLANK、IF、SMALL関数を組み合わせた入れ子の数式でも同様のことが可能です。データセットは前述のセクションのものを使用し、ドロップダウンリスト／データの入力規則リストの作成方法は最初のセクションを参照してください。

手順：

• まず、セル C5 に次の数式を入力します。

=IF(ROW()-ROW($B$5:$B$14)+1>ROWS($B$5:$B$14)-COUNTBLANK($B$5:$B$14),"",INDIRECT(ADDRESS(SMALL((IF($B$5:$B$14<>"",ROW($B$5:$B$14),ROW()+ROWS($B$5:$B$14))),ROW()-ROW($C$5:$C$14)+1),COLUMN($B$5:$B$14),4)))

この数式の仕組みを簡単に説明します。範囲B5:B14を走査し、COUNTBLANK関数によって空白セルの数を調べます。さらに、B5:B14の中で空白でないセルを特定することで、空白でないセルの値だけを返す仕組みです。

• これは配列数式であるため、Ctrl + Shift + Enter を押してください。セル C5 に次のような結果が表示されます。

• 続けて、フィルハンドルで下のセルへオートフィルします。

• しかし、ドロップダウンリストを開くと、C列由来の空白がまだ残っています。

• そこで、[データ]タブから [データの入力規則] を開きます。
• フィルター後のリストが C5～C11 の範囲なので、[元の値]の最終セルを C11 に変更します。

• [OK] をクリックすれば、ドロップダウンリストから空白セルが完全に消えます。

これがもうひとつの、空白を含まないドロップダウンリストを作る方法です。

関連記事：Excelのデータの入力規則の数式でIFステートメントを使う6つの方法

練習セクション

ここで紹介した方法をぜひご自身で試せるよう、練習用のデータセットを用意しました。下記のデータを使って、各手法を実際に操作しながらマスターしてみてください。

まとめ

今回は、Excelのデータの入力規則リストから空白を取り除くさまざまな方法を解説しました。どの方法も一見やや複雑に思えますが、手順どおりに進めば誰でも確実に実践できます。状況に応じて最適な方法を選び、快適なドロップダウンリスト運用にお役立てください。この記事に関するフィードバックや、独自のアイデア・質問などがあれば、ぜひコメント欄でお聞かせください。

関連記事

• 複数条件に対応するカスタムデータの入力規則をExcelで設定する（4つの実例）
• Excelで色を使ったデータの入力規則を設定する方法（4通り）
• 別シートからデータの入力規則リストを作成する6つの方法
• Excel VBAで配列からデータの入力規則リストを作成する
• VBAで名前付き範囲を使ったデータの入力規則リストを作る方法