Блог

У меня есть список значений для каждого месяца от 1 до 12 в Excel. Когда квартал заканчивается в декабре, он должен суммировать значения за квартал Октябрь, ноябрь и декабрь, т.Е. Месяц 10, 11 и 12. Текущая используемая функция — это функция смещения =SUM(OFFSET(INDEX($F$999:$F$1008,MATCH(MONTH($B1013),$E$999:$E$1008)),0,0,-3,1)) .Формула хорошо работает, когда начальная позиция — март, июнь, сентябрь и декабрь. Однако предположим, что месяц окончания квартала — январь. Затем с помощью приведенной выше функции будут суммироваться только значения за январь, потому что выше этого нет значений за 2 месяца до этого.

Желаемый результат: если квартал заканчивается в январе, тогда он должен проходить по списку и суммировать ноябрь, декабрь и январь. Цель состоит в том, чтобы выполнить анализ чувствительности, потому что месяцем, заканчивающимся на квартал, может быть любой месяц, и на основе месяца, заканчивающегося на квартал, он суммирует все значения за квартал.

Список приведен ниже, поэтому, если квартал заканчивается в феврале, он должен суммировать (декабрь) 10% (январь) 3% (февраль) 6% = 19%

 Jan 1   3%
Feb 2   6%
Mar 3   2%
Apr 4   9%
May 5   5%
Jun 6   8%
Jul 7   3.2%
Aug 8   12%
Sep 9   6.5%
Oct 10  5.6%
Nov 11  8%
Dec 12  10% 
 

Хитрость заключается в том, чтобы сгенерировать числовую последовательность, которая будет соответствовать требуемым месяцам.

Обратите внимание, что в вашей таблице номер месяца = индексу в таблице.

Формула, которая сгенерирует требуемую последовательность, такую, что если у вас месяцем, заканчивающимся кварталом, является январь (например, месяц номер 1) => {11,12,1} равно

   Office 365:  =SUM(INDEX(monthly,MOD(SEQUENCE(3, ,F2 12-2)-1,12) 1,3))
  
  older versions:  =MOD(ROW(INDEX($A:$A,1):INDEX($A:$A,3)) F2 12-4,12) 1
 

где F2 содержит номер месяца заканчивающегося квартала.

Так что для O365:

 =SUM(INDEX(monthly,MOD(SEQUENCE(3, ,F2 12-2)-1,12) 1,3))

  or      :  =SUM(XLOOKUP(MOD(SEQUENCE(3, ,F2 12-2)-1,12) 1,monthly[monthNum],monthly[perCent]))
 

где monthly имя вашей таблицы

Для более ранних версий попробуйте:

 =SUM(INDEX(monthly,MOD(ROW(INDEX($A:$A,1):INDEX($A:$A,3)) F2 12-4,12) 1,3))
 

или даже:

 =SUM(INDEX(monthly,N(IF(1,MOD(ROW(INDEX($A:$A,1):INDEX($A:$A,3)) F2 12-4,12))) 1,3))
 

В некоторых более ранних версиях Excel вам может потребоваться «подтвердить» эту формулу массива, удерживая нажатой клавишу ctrl shiftпри нажатии enter. Если вы сделаете это правильно, Excel заключит формулу в фигурные {...} скобки, как указано в строке формул