Фон
У меня проблема, когда я хочу сослаться на именованный диапазон и отобразить его значения. Диапазон — это список значений, но я заранее не знаю, сколько элементов в списке. Допустим, в списке от 1 до 8 значений.
Мой способ сделать это — использовать формулу массива на длинном диапазоне 8 и использовать IFNA() для маскировки вывода #N/A. Вполне приемлемо, что у меня есть некоторое пустое место в выводе, но отображение #N/A выглядит не так профессионально. ISNA не работает, и я подозреваю, что это из-за того, что формула массива меняет поведение ISNA. Пример ниже на изображении.
Ограничения решения
Данные будут использоваться в PivotTables на более позднем этапе, поэтому простое маскирование данных как невидимых не сработает - я действительно не хочу получать никаких значений #N/A. Хотя пустая строка - это нормально; пустая строка - тоже не совсем правильный ответ, но, по крайней мере, она терпит неудачу немного более изящно.
Решение должно быть относительно простым в обслуживании для других людей, поскольку я разрабатываю модель для некоторых коллег, и они могут захотеть внести дальнейшие изменения на более позднем этапе. Поэтому я хочу избежать очень длинных формул и обширного использования менеджера имен.
Вопрос
Каков «правильный» способ простого копирования массива» диапазона переменного размера без получения N/A?
... где «правильный» означает простой, компактный формат, который легко поддерживать и который не будет содержать в себе скрытых данных.
решение1
Вместо этого используйте INDEX. Поместите это в первую ячейку выходных данных и скопируйте вниз нужное количество строк:
=IFERROR(INDEX(rng_1,ROW(1:1)),"")
ROW(1:1) будет итерироваться по мере копирования вниз, вытягивая следующий элемент в строке. Когда он закончится, он выдаст ошибку и ""будет помещен на свое место.
решение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))),"")
решение3
С помощью ответа Скотта Крейнера я остановился на следующем:
Это должно быть немного менее подвержено ошибкам, поскольку ROWSкоманда ссылается на ту же область, куда записывается вывод. Таким образом, не следует вставлять строки, которые слишком сильно разрушают формулу, не осознавая этого.
Я не удовлетворен этим решением, но, полагаю, придется смириться...