☰

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

tag-icon • tag-icon microsoft-excel worksheet-function
Запишите наибольшую сумму групп диапазонов в одну ячейку

Рассмотрим следующий вымышленный пример диапазона ячеек, которые представляют ежемесячные подсчеты чего-либо за ряд лет, и ведущий столбец со сводкой, например:

.|A    |B      |C    |D    |E    |F    |G    |H    |I    |J    |K    |...
-+-----+-------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----
1|     |Most/3M|Jan19|Feb19|Mar19|Apr19|May19|Jun19|Jul19|Aug19|Sep19|...
2|Foo  |     14|    1|    3|    7|    3|    4|    2|    1|    3|    1|...
3|Bar  |     13|    3|    4|    1|    2|    2|    2|    9|    1|    1|...
4|Baz  |     18|    2|    3|    8|    7|    3|    2|    3|    7|    1|...

Этот итоговый столбец B:Bпредставляет собой наивысшее количество за любой трехмесячный период, которое рассчитываетсянаивнос использованием:

=MAX(SUM($C2:$E2),SUM($D2:$F2),SUM($E2:$G2),
     SUM($F2:$H2),SUM($G2:$I2),SUM($H2:$J2),
     SUM($I2:$K2))

...а затем заполняются, но поддерживать это довольно раздражающе — поскольку значения добавляются в самую правую часть, например, L:L, M:M, формулу приходится обновлять с помощью SUM($J2:$L2)и SUM($K2:$M2)и т. д.

Одна из альтернатив:

={LARGE(($C2:$I2 + $D2:$J2 + $E2:$K2), 1)}

...что не такдовольнонастолько плохо, что изменение :$I2на :$J2, :$J2на :$K2и :$K2на :$L2является разумным для трехмесячного окна, и это само по себе может быть получено с ужасающим использованием INDIRECT, как указано в:

={LARGE(INDIRECT("R[0]C3:R[0]C"&COUNTA(OFFSET($C2,0,0,1,2000)),FALSE)+
        INDIRECT("R[0]C4:R[0]C"&COUNTA(OFFSET($C2,0,0,1,2000))+1,FALSE)+
        INDIRECT("R[0]C5:R[0]C"&COUNTA(OFFSET($C2,0,0,1,2000))+2,FALSE), 1)}

Однако это становится проблематичным по мере увеличения размера окна (т. е. необходимости добавлять все больше и больше +предложений) и/или если размер окна должен быть настраиваемым, например, на основе значения B1(которое на самом деле является просто значением 3с пользовательским числовым форматом "Most/"0"M").

Я подумал, что, возможно, есть один из вариантов:

={MAX(SUM(OFFSET($C2:$I2,0,0,1,3)))}
={LARGE(SUM(OFFSET($C2:$I2,0,0,1,3)), 1)}

...где мне нужно было бы только обновить диапазон и размер группы, заданные для OFFSET, с намерением в конечном итоге динамически вывести это, например, используя другой ужасный INDIRECT, такой как:

=LARGE(SUM(OFFSET(INDIRECT("R[0]C3:R[0]C"&
                           (COUNTA(OFFSET($C2,0,0,1,2000))+COLUMN($C:$C)-B$1),
                           FALSE),
                  0,0,1,B$1)),
       1)

Когда используешьОценить формулучтобы пройти через вышеуказанное чудовище внормальныйформе, я нахожу, что это действительно в конечном итоге сводится к следующему:

=LARGE(SUM(OFFSET(INDIRECT("R[0]C3:R[0]C"&(9+3-B$1),FALSE),0,0,1,3)),1)
=LARGE(SUM(OFFSET(INDIRECT("R[0]C3:R[0]C"&(12-3),FALSE),0,0,1,3)),1)
=LARGE(SUM(OFFSET(INDIRECT("R[0]C3:R[0]C9",FALSE),0,0,1,3)),1)
=LARGE(SUM(OFFSET($C$2:$I$2,0,0,1,3)),1)
=LARGE(SUM($C$2:$E$2),1)
=LARGE(11,1)
=11

...поэтому, по-видимому, диапазон, указанный в , OFFSETне расширяется как массив, а верхняя левая ячейка этого диапазона используется напрямую OFFSET.

Вмножествоформа (т.е. CTRLENTER), вместо этого она в конечном итоге сводится к:

=LARGE(SUM(OFFSET(INDIRECT("R[0]C3:R[0]C"&(9+{3}-B$1),FALSE),0,0,1,B$1)),1)
=LARGE(SUM(OFFSET(INDIRECT("R[0]C3:R[0]C"&({12}-3),FALSE),0,0,1,B$1)),1)
=LARGE(SUM(OFFSET(INDIRECT({"R[0]C3:R[0]C9"},FALSE),0,0,1,B$1)),1)
=LARGE(SUM(OFFSET({#VALUE!},0,0,1,B$1)),1)
=LARGE(SUM(OFFSET({#VALUE!},0,0,1,3)),1)
=LARGE(SUM({#VALUE!}),1)
=LARGE(1,1)
=1

...поэтому, по-видимому, расширение массива происходит слишком рано (т.е. в результате COLUMNS($C:$C)), что впоследствии приводит к сбою INDIRECTвызова.

(Оба метода LARGEи MAXвозвращают одинаковые значения — я использовал его, LARGEпотому что в его документации указано, что он ожидает массив, тогда как MAXожидает неограниченный список параметров из отдельных значений.)

Учитывая предпочтения не использовать VBA и не менять макет листа, есть ли:

  1. какие-нибудь трюки, чтобысиларасширение массива в нужном месте, так что SUM(OFFSET)создается массив, который может быть использован LARGE?
  2. есть ли альтернативные подходы к моему, которые требуют внесения поправок в формулы от нуля до минимума, поскольку данные добавляются в правую часть листа?

(P.S. Я использую Excel 2019, а не O365/Insiders, поэтому у меня пока нет динамических массивов/ SEQUENCEпрочего, что, похоже, может стать актуальным. Ответы, которые опираются на это, могут быть полезны для потомков, но не удовлетворят мою непосредственную потребность...)

решение1

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

введите описание изображения здесь

Как это работает:

  • Введите даты соответствующих месяцев Row 1и примените формат ячейки mmm-yy.
  • Вставьте название квартала в Rnage T9:T12.

  • Формула в ячейке U9, заполните ее.

    =IF(ROW(A1)>=5,"",(SUMPRODUCT((ROUNDUP(MONTH(T$1:AE$1)/3,0)=ROW(A1))*(T$2:AE$2))))

Примечание.

  • ROUNDUP(MONTH(T$1:AE$1)/3,0)=Row(A1)возвращается 1и 0s, 1когда месяц принадлежитQ1а 0когда нет и так далее для других кварталов, таких как 2, 3& 4.
  • SUMPRODUCT, обрабатывает все эти диапазоны данных, не моргнув глазом.
  • =ROW(A1))возвращает 1, представляет Q1и так далее 2, 3 & 4.
  • =IF(ROW(A1)>=5,""возвращается Blank, когда формула не может получить значение квартала после 4.

После получения данных за каждый квартал введите в ячейку формулу U14для СУММИРОВАНИЯ 3 наибольших значений.

{=SUM(LARGE(U$9:U$12,{1,2,3}))}

Примечание.

  • Лучше использовать формулу в виде массива (CSE), поэтому нужно закончить сCtrl+Shift+Enter, в противном случае она также работает как формула, не являющаяся массивом.

При необходимости измените ссылки на ячейки в формуле.

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