Очевидно, что пустые и пустые строки - это две разные вещи в Excel. Когда я хочу использовать диапазон ячеек, содержащих пустые строки, для раскрывающегося списка проверки данных в ячейке и сказать ему игнорировать пробелы, он по-прежнему показывает пустые строки.

Вы можете воспроизвести это, введя ="" в ячейку A1, а затем введя =ISBLANK(A1) в другой ячейке, и он вернет FALSE . Удалите формулу из A1, и она вернет TRUE .

Было бы хорошо, если бы была функция =BLANK() .

Я также попытался вернуть =NA() , но оказалось, что выпадающий список также не игнорирует ошибки.

2 ответа2

1

Раскрывающийся список проверки данных не будет сотрудничать с вами в этом. Он просто хочет посмотреть на статический список или на те ячейки, о которых вы говорите, не думая о том, что в них.

Поскольку мы не можем заставить его делать то, что мы хотим, давайте пойдем на компромисс - мы дадим ему непрерывный диапазон, который показывает только те значения, которые мы хотим видеть (плюс пробелы в конце, куда люди в основном не смотрят).

В моем примере ячейками, которые содержат значения (и пробелы) для моего списка проверки данных, являются A1:A15. Я собираюсь перейти к другому столбцу и ввести следующую формулу:

=IFERROR(INDEX($A$1:$A$15,SMALL(IF($A$1:$A$15<>"",ROW($A$1:$A$15),999),ROW(1:1))),"")

Мне нужно ввести это как формулу массива, поэтому я нажму CTRL + SHIFT + ENTER, когда закончу вводить формулу. Далее я скопирую / вставлю или заполню 15 строк. Результатом будут все мои непустые значения из A1:A15, а затем все мои пробелы в конце.

Теперь я открою диалоговое окно «Проверка данных» и укажу свой список на ячейки, содержащие мои формулы (не A1:A15). Пробелы все еще там, но в конце, так что никому не нужно их видеть, если они не прокрутят там внизу, чтобы ничего не посмотреть.

Он не совсем чистый, но он динамический и избегает макросов.


Так что же сделала эта формула?

=INDEX($A$1:$A$15,

смотрит на исходный диапазон, который имеет ваши значения и пробелы, и позволяет вам сказать, на какую ячейку вы хотите посмотреть, по номеру.

SMALL(

смотрит на набор чисел, отсчитывает от самых маленьких и возвращает обратно,

IF($A$1:$A$15<>"",

проверяет, соответствует ли каждая ячейка в вашем диапазоне чему-либо, кроме пустой строки

ROW($A$1:$A$15)

возвращает номер строки, если это так,

,999),

и возвращает 999, если это не так. Если у вас более 1000 строк, вам понадобится большее число, но этого (надеюсь) более чем достаточно для ваших вариантов проверки данных. Функция SMALL теперь имеет список номеров строк с 999 вместо фактического номера строки для любых пробелов.

ROW(1:1)

динамический способ начать отсчет с любой ячейки, в которой вы вводите эту формулу. Когда вы копируете формулу вниз, число растет. Это сообщает маленькому, что вам нужно 1-е наименьшее число в 1-й строке, содержащей формулу, 2-е наименьшее в строке 2 и т.д.

SMALL возвращает номер строки обратно в INDEX , который дает вам значение для этой строки. Все эти 999-е в конце превращаются в ошибки #REF, но мы спокойно возвращаем их к пустым местам с помощью IFERROR .

Эту формулу сложно понять. Если у вас возникли проблемы, попробуйте настроить образцы данных в A1:A15 на новом листе, выбрать ячейку в другом столбце и скопировать мою формулу точно в строку формул (не забудьте CTRL+SHIFT+ENTER). Поиграйте с этим немного, и вы должны научиться этому.

Удачи!

0

Будет ли что-то подобное работать?

=IF(AND(NOT(ISFORMULA(J3)),J3=""),"Truly Empty","Blank via Formula")

Примечание: если вам нужна пользовательская функция, например, буквально =blank(A1) , вам понадобится VBA для создания UDF. Приведенная выше формула проверяет, имеет ли ячейка, в которой вы просматриваете, формулу и также является пустой.

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .