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(). Проверка данных не? Я весь день рвал на себе эти и другие проблемы и устал от этого.

1 ответ1

0

Один из способов сделать ваш список динамическим и связанным со столбцом:

  • Назовите ваши различные списки именем, соответствующим образом связанным с заголовком столбца (или даже тем же, если вы не используете заголовок столбца в качестве именованного диапазона)

    • Например: Column: Items ValidList: listItems

         Column:  ManHours   ValidList:  listManHours
      
  • Затем используйте формулу проверки, например (с заголовками столбцов Items в J1):

    J2: =INDIRECT("list" & J$1)

Это заполнит выпадающий список содержимым именованного диапазона listItems

При необходимости скопируйте / вставьте проверку в другие столбцы.

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