Я думаю, что проблема здесь заключается в том, что предложение, приведенное в этой ссылке для работы с результатами формул в строках, выходящих за пределы ожидаемого числа возвратов, заключается в том, чтобы просто заключить формулу в какое-то предложение IFERROR.
Однако эта настройка крайне неэффективна, особенно если рассматриваемый набор данных является динамическим, потенциально расширяемым.
Дело в том, что если у вас есть такие настройки, как:
= IFERROR([ some_large_array_formula ], "")
который предназначен для копирования достаточного количества строк, чтобы инкапсулировать все желаемые результаты, тогда вы столкнетесь с двумя вариантами.
Во-первых, вы можете выполнить некоторые вычисления, чтобы точно определить, сколько таких возвратов вы получите в любой момент времени, а затем перетащить эту формулу вниз на это количество строк. Очевидно, что это не идеально, тем более, если у вас, как я уже говорил, динамически изменяющийся набор данных.
Во-вторых, мы можем скопировать формулы в произвольно большое количество строк, чтобы мы гарантированно покрыли все возможные возвраты, даже если наш набор данных расширится в будущем, и поэтому не нужно беспокоиться об этом снова.
Очевидно, что этот второй метод предпочтительнее на практике. Проблема со структурой IFERROR (еще хуже, если вы используете 2003 или более раннюю версию и должны использовать повторяющийся IF (предложение ISERROR)) в том, что в строках, в которые формула копируется сверх того, что эффективно необходимо, нет ничего для предотвращать ненужные вычисления формулы большого массива ресурсов.
Дело в том, что в приведенной выше конструкции даже в строках, выходящих за пределы нашего последнего ожидаемого возврата, Excel все равно придется потратить все ресурсы на вычисление части формулы массива, прежде чем он сможет сам решить, является ли это ошибкой или нет. ,
Гораздо, намного лучше, чем этот неработающий подход IFERROR, который, к сожалению, почти повсеместно рекомендуется для этой установки вокруг различных источников в Интернете, - это, как указывает Джеймс, использовать одну "вспомогательную" ячейку, чтобы сначала определить число строки, которые мы ожидаем, будут возвращены, а затем ссылаются на это в формуле
Так, например, если опубликованные данные были в A1:C10 (с заголовками в строке 1) и мы добавили, например, NN в E1 и OUT в F1, мы сначала должны ввести одну формулу, не являющуюся массивом, например, в G1:
= SUMPRODUCT(0+(А2: А10 = Е1), 0+(В2: В10 = F1))
Формула массива ** ** в нашей первой ячейке выбора будет:
= IF(ЧСТРОК ($ 1:1)> $ G $ 1, "", ИНДЕКС ($ C $ 2:$ C $ 10, МАЛЫЙ (ЕСЛИ ($ A $ 2:$ A $ 10 = $ E $ 1, ЕСЛИ ($ B $ 2:$ В $ 10 = $ F $ 1, СТРОКА ($ C $ 2:$ C $ 10)-MIN (СТРОКА ($ C $ 2:$ C $ 10))+1)), ЧСТРОК ($ 1:1))))
и скопировать по мере необходимости.
Конечно, у нас все еще есть вопрос, как далеко скопировать эту формулу. И даже несмотря на то, что у нас есть расчет для определения необходимого количества строк, нам все равно не нужно вручную корректировать количество ячеек, содержащих формулы, каждый раз, когда мы хотим обновить результаты. Это должна быть разовая начальная работа.
Я, конечно, не рекомендую копировать до самого конца таблицы. Однако, если можно выбрать достаточно большую верхнюю границу, тогда это не должно иметь большого значения с точки зрения производительности, если мы получим даже несколько тысяч посторонних ячеек, содержащих формулы. Причина в том, что между этой установкой и "ленивым" подходом IFERROR существует огромная разница, что здесь начальное предложение:
= IF(РЯДЫ ($ 1:1)> $ G $ 1 ""
означает, что в строках, превышающих ожидаемое количество возвратов, предложение IF возвращает TRUE, поэтому возвращается пустое значение. Хорошая часть функции IF состоит в том, что если переданное ей предложение имеет значение TRUE, то часть FALSE - здесь формула массива с большими ресурсами - даже не учитывается при расчете.
Это совсем не так с версией IFERROR, которая продолжает отрабатывать, не обращая внимания на тот факт, что его вычисления бесполезны и обременительны для ресурса.
С уважением
** Формулы массива вводятся не так, как «стандартные» формулы. Вместо того, чтобы просто нажимать ENTER, вы сначала удерживаете CTRL и SHIFT, и только потом нажимаете ENTER. Если вы сделали это правильно, вы заметите, что Excel заключает в формулу фигурные скобки {} (хотя не пытайтесь вставить их вручную).
