Видимо, пробелы и пустые строки — это две разные вещи в Excel. Когда я хочу использовать диапазон ячеек, содержащий пустые строки, для раскрывающегося списка проверки данных в ячейке и указать ему игнорировать пробелы, он все равно показывает пустые строки.
Вы можете воспроизвести это, введя =""в ячейку A1, а затем введя =ISBLANK(A1)в другую ячейку, и она вернет FALSE. Удалите формулу из A1, и она вернет TRUE.
Было бы неплохо, если бы была такая =BLANK()функция.
Я также пробовал возвращать =NA(), но оказалось, что раскрывающийся список тоже не игнорирует ошибки.
решение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)
это динамический способ начать отсчет с любой ячейки, в которую вы вводите эту формулу. По мере того, как вы копируете формулу вниз, число растет. Это говорит small, что вам нужно 1-е наименьшее число в 1-й строке, содержащей формулу, 2-е наименьшее число в строке 2 и т. д.
Возвращает SMALLномер строки обратно в INDEX, что дает вам значение для этой строки. Все эти 999 превращаются в ошибки #REF в конце, но мы тихо превращаем их обратно в пробелы с помощью IFERROR.
Эту формулу может быть сложно понять. Если у вас возникли проблемы, попробуйте настроить образец данных в A1:A15 на новом листе, выбрать ячейку в другом столбце и скопировать мою формулу точно в строку формул (не забудьте CTRL+SHIFT+ENTER). Поиграйте с этим немного, и вы должны освоиться.
Удачи!
решение2
Будет ли что-то подобное работать?
=IF(AND(NOT(ISFORMULA(J3)),J3=""),"Truly Empty","Blank via Formula")
Примечание: если вам нужна пользовательская функция, например, делающая буквально =blank(A1), вам понадобится VBA для создания UDF. Приведенная выше формула проверяет, содержит ли ячейка, в которой вы ищете, формулу и является ли она пустой.
решение3
Я столкнулся с этой же проблемой и нашел способ ее решения. Следуя примеру, представленному AjimOthy, включите формулу подсчета ниже A1:A15 (допустим, A16 будет этой ячейкой). Она будет подсчитывать только ячейки выше (A1:A15) и выдаст вам число там
.
Затем я использую косвенный вариант, например:
когда проверка данных просит вас выбрать его источник. Использование косвенного позволяет изменять жидкость на основе подсчета, а также удаляет пробелы, которые появляются в списке с функцией подсчета, поскольку она не учитывает пробелы "".