#excel #validation #excel-formula
#excel #проверка #excel-формула
Вопрос:
У меня есть динамический список параметров категории, которые я хочу представить в некоторых ячейках, используя раскрывающийся список проверки данных. Этот список доступен для редактирования пользователем (‘admin’) и находится на листе с именем Config в столбце D. Список имеет ячейку заголовка в D2 и начинается с ячейки D3, и может быть сколь угодно длинным.
Я заключил список в именованный диапазон под названием «Категории», который имеет формулу:
=Config!$D$3:OFFSET(Config!$D$3,COUNTA(Config!$D:$D)-1,0)
Ячейки проверки данных, которые я установил, имеют тип «список», и в качестве формулы я просто ссылаюсь на этот именованный диапазон:
=Categories
Результатом этого является то, что в этих ячейках теперь есть выпадающий список проверки данных, содержащий категории. И при обновлении списка категорий раскрывающиеся списки проверки данных обновляются соответствующим образом.
Однако сегодня я обнаружил, что в Excel 2019 выпадающий список работает как обычно (показывает текущие параметры), но теперь пользователи могут вводить практически все, что угодно, в этих ячейках, без того, чтобы Excel вызывал запрос «недопустимые данные». Раньше это работало просто отлично IIRC с тех пор, как я начал использовать эту технику (до Excel 2010), когда Excel давал пользователям пощечину, когда они вручную вводили несоответствующие данные. Но когда я сегодня перепроверил в Excel 2019 и даже в Excel 2010, теперь он допускает неверные данные, введенные пользователем, без каких-либо подсказок.
Глядя на то, что на самом деле сломалось, я сначала упростил формулу в именованном диапазоне «Категории», чтобы просто ссылаться на фиксированный диапазон (например Config!D3:D11 ), но это ничего не исправило; пользователь по-прежнему может вводить все, что захочет. Затем я заменил формулу в ячейках проверки данных на
=indirect("Config!D3:D11")
и это заставляет Excel снова распознавать неверные данные, введенные пользователем. Однако, используя indirect формулу, я не могу ссылаться на именованные диапазоны, не говоря уже об использовании формулы. И indirect не обновляет адреса ячеек, когда пользователи перемещают исходные ячейки списка. И использование этих формул динамического списка непосредственно в формуле проверки данных также не допускается.
Так это настоящая ошибка Excel, появившаяся где-то в последние несколько лет, или эта функциональность всегда работала таким образом, и я просто не знал об этом все это время?
Обновить
Как сказано в ответе @Ghislain, именно пробелы в списке параметров заставляют Excel внезапно принимать все, что вводит пользователь. И, похоже, флажок «Игнорировать пробелы» управляет этим поведением.
Я интуитивно всегда думал, что этот параметр означает что-то вроде «Разрешить пользователям оставлять ячейку пустой» или тому подобное. Чтобы не потерять рассудок, я также проверил, что, по мнению самих Microsoft, это делает. Цитата со страницы справки Excel 2019 для диалогового окна:
Установите флажок Игнорировать пробелы, если вы хотите игнорировать пробелы.
И цитата из файла справки Excel 2010 offline chm:
Если люди могут оставить ячейку пустой, установите флажок Игнорировать пустое поле.
Так что, похоже, они сами еще не разобрались с этим 🙂
Ответ №1:
Я полагаю, что ошибка заключается в том, что в вашем списке проверки у вас есть пустая ячейка, которая делает любое значение авторизованным: я подозреваю D3:D11 , что имеет пустую ячейку. Это действительно ошибка?
Комментарии:
1. Нет, пользователям разрешено вводить все, что им нравится, поэтому принимается все, кроме пустых ячеек и значений из списка. Не похоже, что это работает так, как задумано… Когда я разрешаю проверке данных указывать на эквивалентный жестко заданный диапазон вместо именованного диапазона, отображается тот же выпадающий список, но теперь пользователям не разрешается вводить случайные данные, как и должно быть.
2. Поразите это: проблема действительно такова, как вы сказали. Похоже, я неправильно протестировал жестко запрограммированный список, на который я ссылался выше. Я попробовал несколько альтернативных вариантов, чтобы посмотреть, смогу ли я найти обходной путь, и внезапно я увидел пробелы, приводящие к такому поведению, как вы сказали.
3. Я только что прочитал опцию «Игнорировать пробелы» в диалоговом окне проверки данных, и, по-видимому, это определяет, заставляют ли пробелы в списке Excel принимать все, что вводит пользователь, когда в списке присутствует пустой параметр …? После 15 лет создания решений Excel для клиентов я всегда думал, что это означает, что пользователи могут оставлять ячейку пустой без сбоев в проверке данных. И, таким образом, всегда оставлял этот флажок включенным и никогда особо не задумывался об этом. Никогда не бывает слишком старым, чтобы изучать что-то новое 🙂 Но одна демонстрация запутанного именования, если я когда-либо ее видел…
4. Чтобы попытаться сохранить свою самооценку, я просто просмотрел страницы справки для диалогового окна проверки данных и обнаружил, что Microsoft также ошиблась в справке как за 2019, так и за 2010 год… Подробности см. в моей правке в OP.