Как на самом деле работают динамические именованные диапазоны

Question

Поэтому мой вопрос касается функциональности динамического именованного диапазона. Я провел некоторое исследование, и все, что я видел, похоже, связано с решением конкретных проблем, ни с одной из которых я не сталкиваюсь. Итак, я собираюсь заявить, что я хотел бы сделать, и, надеюсь, один из вас сможет помочь мне понять, почему я получаю результаты, которые я получаю!

Я пытаюсь использовать динамический именованный диапазон для создания списка для проверки данных. Я бы предпочел сделать это без макросов, чтобы я мог создать более удобный интерфейс для моих коллег, которые не разбираются в компьютерах. У меня есть проблема в том, что когда я использую формулы для извлечения из диапазона (который находится на отдельном скрытом листе), вместо того, чтобы вытягивать все элементы в диапазоне, формула извлекает один элемент из той же строки.

Например, если у меня есть "яблоки" в $J$6 как часть моих названных фруктов диапазона, формула:

`=SEARCH(fruits, "I ate fifteen bananas today.")` 

будет компилироваться как

`=SEARCH("apples", "I ate fifteen bananas today.")` 

вместо

=SEARCH({"pears";"bananas";"mangos";"grapes";"apricots";"apples";"oranges";"raspberries"}, "I ate fifteen bananas today.")

Я попытался ввести формулу в виде массива, но безрезультатно. Я непоследовательно заставляю свои формулы проходить через каждый элемент в DNR, а не только один, и мне хотелось бы получить некоторые пояснения о том, как они работают в Excel. Есть какой-то шаблон, который я пропускаю? Я собираюсь включить некоторые другие формулы, которые я пробовал для дополнительной информации; Я не знаю, поможет ли это, но больше информации - больше информации.

Я извлекаю их из отдельного рабочего листа, над которым работаю, потому что не хочу публиковать информацию о студентах. Это из игры, которую я разрабатываю, но это та же самая проблема (я фактически использую одну, чтобы помочь построить другую - хи хи).

Заранее спасибо всем, кто может предложить некоторое понимание! Большинство из того, что я знаю, я узнал от вас, ребята, онлайн, и я впервые задаю вопросы в Excel. Ура!

Дополнительный код

    `=SUMPRODUCT(--ISNUMBER(SEARCH(keywords, INDEX(skillsActive, MATCH(B3, OFFSET(skillsActive, 0,2),0)))))`

Я нашел эту формулу на этом сайте (однако я не помню ветку, извините программиста!). Он ищет каждый элемент в ключевых словах DNR, но преобразует все в 0 или 1, и я хотел бы подготовить результаты в виде строк и создать список на основе этих результатов.

    `{=IF(B2="","",IF(ISERROR(SEARCH(B2,skillsActive))=FALSE, OFFSET(skillsPassive, 0, 1), FALSE))}`

Диапазон skillActive также расширяется, как мне бы хотелось, но это не DNR; это именованный диапазон, значения ячеек которого являются необработанными строками. B2 - это список ключевых слов, использующих проверку данных. Эта формула возвращает только первую итерацию skillActive - она ​​либо возвращает Soldier, если B2="Blinded", либо FALSE для любого другого результата.

Answer 1

С помощью SEARCH, который должен рассматриваться как формула массива, в идеале с AGGREGATE, Мы можем использовать SUMPRODUCT заставить вычисление массива-формулы в нормальной формуле.

=SUMPRODUCT(AGGREGATE(15,6,SEARCH(fruits, "I ate fifteen bananas today."),1))

Разбивая это:

SEARCH(fruits, "I ate fifteen bananas today.")
В пределах SUMPRODUCT это оценивается как формула массива: SEARCH({"pears";"bananas";"mangos";"grapes";"apricots";"apples";"oranges";"raspberries"}, "I ate fifteen bananas today.")
и становится:
{#VALUE!;15;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}

AGGREGATE(15,6,{#VALUE!;15;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!},1)
Второй аргумент 6поэтому мы отбрасываем все значения ошибок:
AGGREGATE(15,6,{15},1)
Первый аргумент 15, поэтому мы считаем от наименьшего к наибольшему, и четвертый аргумент (1) сообщает нам, какой элемент получить, в данном случае самый маленький:
15

=SUMPRODUCT(15)
SUMPRODUCT в основном просто вынуждает это оценивать как формулу массива. Он умножает соответствующие элементы в каждом аргументе вместе, а затем суммирует результаты:
=15