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

Question

Поэтому я пытаюсь получить уравнение для работы с переменным числом ячеек. Итак, у меня есть лист с текущими и прогнозируемыми затратами. Я бы хотел, чтобы число прогнозов работало только на основе текущих затрат. На это указывает 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
• Проекция Б: 3
• Проекция C: 5

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

Answer 1

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

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

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

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

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

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

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

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