TL; WR Почему =if(column()=16, list1, list2) выдает ошибку при использовании в качестве функции проверки данных типа List?
Длинная версия:
Я весь день кричал на своем компьютере, потому что не могу заставить мою схему проверки данных работать правильно и, возможно, обнаружил проблему.
Фон:
У меня есть таблица со многими столбцами. Каждая группа из трех столбцов представляет различные характеристики общей недели. Таким образом, столбец 1 - "Элемент", столбец 2 - "Завершенные единицы", а столбец 3 - «Человеко-часы», все для недели 1. Затем столбец 4 - "Элемент", столбец 2 - "Завершенные единицы", а столбец 3 - «Человеко-часы», все для недели 2. Так далее и тому подобное. Я хочу, чтобы входные данные для этой таблицы были проверены, но, конечно, для "Предметов" требуется иное требование, чем для "Рабочих часов". И вместо того, чтобы выбрать все столбцы "Элемент" и применить одну проверку данных, затем выбрать все столбцы "Manhours" и применить другой (что утомительно и более подвержено ошибкам), я пытаюсь сделать умную функцию проверки, которая знает текущий столбец и соответственно применяет проверку.
Процесс мышления:
Если я хочу, чтобы какая-либо операция над ячейкой (например, условное форматирование) зависела от значения рассматриваемой ячейки, то в формуле форматирования необходимо использовать INDIRECT(address(row(),column())) [или, для энергонезависимая опция, я обычно использую INDEX($1:1000,row(),column()) ]. Кроме того, так как формула форматирования не может принимать INDIRECT напрямую (больше криков там), я должен создать именованную функцию. Хорошо, это работает.
Поэтому я попробовал то же самое с проверкой данных. Если у меня "Критерии проверки" установлен на "Пользовательский" с формулой
=IF(OR(COLUMN()=16,COLUMN()=18), INDEX($1:$1000,ROW(),COLUMN())<8)
и применить их к ячейкам $P$26:$R$26 , я получаю ожидаемые результаты: $ P $ 26 может быть 7, но не 8; $ Q $ 26 не может быть ничем; и $ R $ 26 может 7, но не 8. Потрясающие.
Поскольку я хочу, чтобы проверка была разными списками, я попытался сделать выбор списка динамическим. Итак, я попробовал простой пример: именованная функция list.sel = if( Table_Weekly[@1]="Bolts", list_bolts, list_nuts ) , "Критерии проверки" установлены в "List" с формулой =list.sel . Работает нормально и как положено.
Проблема:
Затем я попытался изменить именованную функцию: list.sel = if( column()=16, list1, list2 ) со всем остальным так же, и он сказал мне «Источник в настоящее время оценивается как ошибка». Я знаю, что не сделал опечатку, потому что я могу ввести =index(list.sel,1) в ячейку a в столбце 16, и это правильно дает мне первый элемент в list1.
Итак, что дает? Это как-то связано с контекстом, в котором оценивается формула? Для условного форматирования Excel знает, для какой ячейки он выполняет оценку функции форматирования, поэтому он знает, что делать с функциями row() и column(). Проверка данных не? Я весь день рвал на себе эти и другие проблемы и устал от этого.