Автозаполнение по разделам или по условию на другую ячейку

Question

У меня есть данные, которые выглядят так:

Class    Date   Yr1   Yr2   SeasonalityIndex
AUS     9-Aug     1     0        0.060465116
AUS    16-Aug     6     0        0.362790698
AUS    23-Aug     2     1        0.181395349
BVE     9-Aug     2     0                  ?

Я хочу автоматически заполнить столбец индекса сезонности. Тем не менее, код для этих ячеек выглядит так:

=(D55+E55)/SUM($D$55:$E$67)*$H$1

Где H1 является константой, но, как вы видите, каждый индекс зависит от суммы всех значений в этом КЛАССЕ для года 1(Yr1) и года 2(Yr2). Когда класс меняется, я хочу, чтобы автозаполнение изменилось на следующий раздел.

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

Answer 1

Я не уверен, что это будет работать для вас, но вы можете попробовать: 1.Назовите каждый диапазон точно таким же именем, как и имя класса. 2. Замените сумму на: =(D55+E55)/SUM(INDIRECT(A55))* $ H $ 1, предполагая, что A55 - это ячейка с именем класса в ней.

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

Answer 2

Если вам нужно выражение, которое оценивается в SUM($D$55:$E$67) когда оно оценивается в строках 55-67, и вычисляется в SUM($D$68:$E$80) когда оно оценивается в строках 68-80 и т.д. ., использовать

SUM(OFFSET($D$3, 13*INT((ROW()-3)/13), 0, 13, 2))

(Вставьте это в вашу полную формулу.)  Номера строк с 55 по 67 преобразуются в 52 через 64 (вычитая 3), что дает 4 - 4.923 (путем деления на 13), все из которых преобразуются в 4 с помощью INT() .  Затем умножьте на 13, чтобы вернуться к 52, и используйте это как смещение к D3 чтобы получить к D55 .

---

Или, чтобы добавить значения в столбцах D и E для строк, имеющих то же значение в столбце A, что и текущая строка, используйте

(SUMIF($A$1:$A$999, $A1, $D$1)+SUMIF($A$1:$A$999, $A1, $E$1))

который делает в значительной степени именно то, что я только что описал.  Замените 1 и 999 диапазоном строк, в которых у вас есть данные класса.

Кажется, что должно быть возможно объединить вышеупомянутое в один SUMIF (или возможно SUMIFS), который суммирует Столбцы D и E, но я не мог понять это.