#excel #excel-formula
#превосходить #excel-формула
Вопрос:
У меня есть несколько значений, помещенных в несколько ячеек следующим образом. Я уже определил именованные диапазоны: name1 для D3, name2 для F3, name3 для J3, name4 для N3.
Теперь я хотел бы создать ячейку с проверкой данных; значения в ее раскрывающемся списке — v1 , v2 , v3 и v4 .
Я хотел бы, чтобы источник проверки этих данных основывался на именованных диапазонах name1 , name2 , name3 и name4 . В результате,
- Даже несмотря на то, что расположение ячеек меняется, до тех пор, пока названные диапазоны верны, мы уверены, что выпадающий список может содержать правильные значения.
- Мы могли бы изменить значения в названных диапазонах, и значения в раскрывающемся списке автоматически обновляются.
Кто-нибудь знает, как этого добиться?
Правка 1:
Чтобы сделать этот вопрос менее сложным, давайте предположим , что все эти именованные диапазоны всегда находятся в строке 3 , а в строке 3 нет других данных. Я попытался определить другой именованный диапазон all =TRANSPOSE(FILTER($3:$3,$3:$3lt;gt;"")) , в результате =all чего в ячейке были возвращены все значения.
Однако, как я написал =all в источнике проверки данных, он вернулся The Source currently evaluates to an error. Do you want to continue? . При нажатии на Yes возвращен пустой выпадающий список.
Я думаю, возможно, это связано с тем, что проверка источника данных плохо работает с такими функциями динамического массива, как FILTER . У кого-нибудь есть другая формула (для удаления пробелов в списке) с традиционными функциями, чтобы попробовать?
Комментарии:
1. вам понадобится вспомогательный столбец, в котором вы сложите четыре именованных диапазона, а затем обратитесь к этому диапазону.
2. Можно ли не отображать этот вспомогательный столбец на листах (например, определить его только в диспетчере имен)?
3. нет, это должно быть в простыне. Но вы могли бы поместить его в колонку, которая находится за пределами экрана.
4. или вы можете поместить его на очень скрытый лист, который пользователь не может видеть.
5. В ответ на ваше редактирование. Проверка данных/условное форматирование никогда не работали с формулой типа массива. Это было широко разрекламировано как «недостаток», и все же Microsoft при всей своей мудрости не «исправила» эту проблему, когда представила динамические массивы. Так что нет, нет формулы, которая сделает это напрямую. Вам нужно будет использовать вспомогательный столбец, чтобы делать то, что вы хотите.
Ответ №1:
Я бы создал отдельное местоположение (возможно, как вкладка «система») для «хранения» данных временного списка. Это можно сделать так:
Используйте фильтр, чтобы удалить пустые ячейки, как это:
=FILTER(D3:N3,D3:N3lt;gt;"")) Затем переместите этот вывод, чтобы сделать его вертикальным списком:
=TRANSPOSE(FILTER(D3:N3,D3:N3lt;gt;"")) Теперь это динамический список ваших ценностей. Теперь подумайте о том, насколько большим может быть этот список в будущем. В настоящее время это 4 значения, поэтому давайте предположим, что 10. Зарезервируйте до 10 ячеек для этого динамического списка, чтобы он мог расти. Теперь используйте эти 10 ячеек в качестве входных данных для вашего выпадающего списка.