Индекс Excel против смещения / косвенного - я не понимаю, почему смещение / косвенное выполняется быстрее в этом случае

У меня есть рабочая книга, в которой первая вкладка содержит список параметров. Каждый столбец представляет собой набор параметров для отдельного экземпляра.

Затем у меня есть лист шаблона для одного экземпляра, и пользователь делает множество копий этого шаблона. Одна из функций шаблона - динамическое вытягивание параметров из первого листа. Пользователь вводит номер столбца (от 1 до n), и этот столбец параметров извлекается из первого листа, который будет использоваться в этом экземпляре. Затем экземпляр выполняет вычисления сотен тысяч строк с использованием этих параметров.

Таблица параметров

Пример экземпляра

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

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

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

Есть ли способ настроить это так, чтобы при изменении параметра на лицевой странице только те листы, на которые воздействовали этот параметр пересчитать?

Решения

Я рассмотрел сложное решение, включающее VBA, после чего при копировании экземпляра шаблона отслеживается его ячейка «Номер экземпляра». При его изменении код VBA может копировать соответствующие параметры на лист и печатать значения здесь. Мне также понадобится VBA, отслеживающая событие изменения на листе основных параметров. Когда что-либо изменяется, ему нужно будет проверить номер столбца, перебрать все шаблоны и повторно скопировать значения, если имеется ссылка на этот номер экземпляра. Я хочу избежать этого решения по обычным причинам, чтобы исключить VBA из уравнения, но оно может оказаться необходимым, если нет способа сделать пересчет в Excel более разумным в отношении изменений параметров.