Excel プルダウンで不要な空白を表示させない方法

2020年5月13日

入力規則のプルダウン(ドロップダウン)リストの元の値にセルを指定した場合、

そのセル範囲内に空白セルが含まれると空白も含まれてしまいます。

このようなシートをリストとする場合、

元の値のリスト

特に元のシートの挿入・削除を考えると、このように列指定をしたいですが、

この指定ではどうしても空白が含まれてしまいます。

プルダウンに空白が含まれる状態

この記事ではここに空白を非表示にする方法を紹介します。

手順

元の値のセル範囲に行も指定する

かなり単純ですが対策の一つです。

ただしリストの値を増やす場合、セル範囲を調整する必要があるため

その可能性を考える必要があります。

行まで指定する例

OFFSET関数とCOUNTA関数で指定する

列指定で行追加にも自動対応しつつ、空白を除外したい場合、

OFFSET関数COUNTA関数で下記のような数式を指定します。

=OFFSET(リスト開始セル,,,COUNTA(リスト列))

今回の例ではこのように指定します。

=OFFSET(お供候補!$A$1,,,COUNTA(お供候補!$A:$A))

このように空白が無視されます。

空白が除外された例

ただし、空白でないセルの件数をCOUNTA関数で数えているため

途中で空白が混ざってしまうと余計な空白が出る上に必要なものが欠けてしまいます。

元の値リストの途中に空白行がある
元の値リストの途中に空白行がある
空白が入り、必要な候補値が削除される
空白が入り、必要な候補値が削除される

リストを更新する場合は、空白行は行削除するように更新する必要があります。

補足:「空白を無視する」チェック

「空白を無視する」というチェック項目があるため、

これを設定すれば解決するようにも感じますが、そうなりません。

「空白を無視する」チェック

ここの空白を無視するとは、リストの選択値ではなく、

入力規則を設定したセルへの入力内容のことです。

チェックを解除しセルの内容を空白で確定しようとした場合、

このようなエラーとなります。

よって表現が紛らわしいですが、別機能です。

関連記事

活用例

複数のプルダウンを連動させる方法

入力規則によるプルダウン(ドロップダウン)リストの記事一覧

よくあるトラブルの原因と対処方法の一覧