Как правильно сделать IFNA в формуле массива?

Question

Фон

У меня проблема, когда я хочу сослаться на именованный диапазон и отобразить его значения. Диапазон представляет собой список значений, но я не знаю заранее, сколько элементов в списке. Допустим, в списке есть от 1 до 8 значений.

Мой способ сделать это - использовать формулу массива на дальнем расстоянии 8 и использовать IFNA() для маскировки вывода # N/A. Вполне допустимо, что у меня есть некоторое пустое место на выходе, но отображение # N/A не выглядит так профессионально. ISNA не работает, и я подозреваю, что это из-за формулы массива, изменяющей поведение ISNA. Пример ниже на картинке.

Ограничения по решению

Данные будут использоваться в сводных таблицах на более позднем этапе, поэтому просто маскируйте данные как невидимые - я действительно хочу не получать никаких значений # N/A. Пустая строка в порядке, хотя; пустая строка также не является правильным ответом, но, по крайней мере, она терпит неудачу немного более изящно

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

Вопрос

Каков "правильный" способ просто скопировать диапазон »с переменным размером, не получая N/A?

... где "правильный" означает простой, компактный форум, который прост в обслуживании и не будет содержать скрытых данных.

Answer 1

Вместо этого используйте INDEX, поместите это в первую ячейку вывода и скопируйте нужное количество строк:

=IFERROR(INDEX(rng_1,ROW(1:1)),"")

Строка (1:1) будет повторяться, поскольку она копируется, тянущая te в следующей строке. Когда он закончится, он выдаст ошибку, и "" будет поставлено на его место.

Answer 2

Как правильно просто "скопировать массив" диапазона переменного размера, не получая N/A?

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

Условное форматирование:

Условно отформатируйте столбец / диапазон, чтобы изменить формат текста ячейки с ошибкой на белый или любой другой цвет, смешивающийся с цветом вашей ячейки.

Пример формулы:

Используйте формулу, не являющуюся массивом, и используйте формулу для обработки ошибки.

=IFERROR(IF(INDEX(Pets,ROWS($J$1:J1),COLUMNS($J$1:J1))="","",INDEX(Pets,ROWS($J$1:J1),COLUMNS($J$1:J1))),"")

Answer 3

С помощью ответа Скотта Кранерса я согласился на следующее:

Он должен быть немного менее подвержен ошибкам, поскольку команда ROWS ссылается на ту же область, в которую записывается вывод. Таким образом, не следует вставлять строки, разрушающие формулу слишком сильно, не осознавая этого.

Я не доволен этим решением, но я думаю, что это должно будет сделать ...