☰

Получение формулы для ссылки на диапазон переменных

tag-icon • tag-icon microsoft-excel
Получение формулы для ссылки на диапазон переменных

Итак, я пытаюсь заставить уравнение работать с переменным числом ячеек. Итак, у меня есть лист с текущими затратами и прогнозируемыми затратами. Я бы хотел, чтобы прогнозируемое число работало только с текущими затратами. Это обозначено ACT в верхней части месяца. Моя первоначальная формула sumifs/countifs была отклонена из-за генерации циклической ошибки ссылки, поэтому мне интересно, есть ли способ динамически обновлять уравнения в зависимости от другой ячейки.

Я бы предпочел избежать использования динамических диапазонов, поскольку речь идет примерно о 400 строках, которые необходимо обновить, и, насколько я понимаю, диапазоны необходимо добавлять по одному за раз.

Пример того, что, как я надеюсь, будет делать формула: =Sum(A1:A(1+B1)), в котором диапазон увеличивается по мере увеличения B1.

Пример данных:

    Act   Act   Act   Proj   Proj  
A:   1     2     3      2      2
B:   1     3     5      3      3
C:   4     5     6      5      5
  • Проекция А: 2
  • Проекция B: 3
  • Проекция С: 5

Затем, когда я добавляю фактические цифры за 4-й месяц и меняю Proj на Act, диапазон, в котором рассчитываются проекция A, проекция B и т. д., изменяется с 3 до 4 без какого-либо дополнительного ввода с моей стороны.

решение1

Вы можете использовать OFFSET()для создания ссылки на диапазон столбцов X-Y, например:

=OFFSET(A1,0,0,10,10)создаст ссылку 10x10, которая ссылается на A1:J10.

Вы можете заменить жестко заданные десятки в приведенной выше формуле на , COUNTIF()который определяет, к скольким строкам и столбцам должен относиться диапазон.

Чтобы избежать ошибки цикличности, вы можете попробовать определить именованный диапазон ( AltMMD) с помощью этой формулы и использовать именованный диапазон везде, где вы используете формулу.

Альтернативно, вы можете создать текстовую строку со ссылкой X-by-Y, используя OFFSET()без параметров ширины и высоты (т. е. 10 в формуле выше), и вместо этого найдя первую и последнюю ячейку в диапазоне. Затем оберните их в вызов, CELL("address",...)который вернет их адрес.(Примечание: если ваша начальная точка $A$1, вам просто нужно найти конечную ячейку).

Объедините их с помощью :in между ними, а затем оберните все вокруг, INDIRECT()где вам нужно это использовать. В общем, предполагая жестко заданное начало в $A$1:

=INDIRECT("Sheet1!$A$1:"&CELL("address",OFFSET(Sheet1!$A$1,COUNTIF(..x-rows..),COUNTIF(..y-cols..))

COUNTIF()логика для нахождения количества строк и столбцов, оставленных в качестве упражнения для читателя

Связанный контент