Суммирование динамического диапазона столбцов

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

Это пример скриншота данных:

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

Это скриншот сводной вкладки:

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

Период выбирается из выпадающего меню. (когда выбран период, меняется ячейка F3) Класс также выбирается из выпадающего меню.

Мой SUMIF в ячейке F4: =SUMIFS(INDEX(data!$D:$K,,MATCH($B$2,data!$D$1:$K$1,0)),data!$B:$B,$D4,data!$A:$A,$B$5)

Сейчас я пытаюсь вычислить сумму периодов P1 и до выбранного периода. Например П4

Я загрузил тестовую таблицу на Google Диск https://drive.google.com/file/d/1NczIxxIXGVdAIpTfOTr7ys8BCOFh_RJj/view?usp=sharing

Я был бы очень признателен за вашу помощь.


excel indexing match sumifs
person neg    schedule 22.09.2020    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Если у вас есть O365 с функциями FILTER и SEQUENCE, вы можете использовать:

=SUM(INDEX(
             FILTER(data!$D:$K,
                      (data!$B:$B=$D4)*
                      (data!$A:$A=$B$5)),
              SEQUENCE(COUNTIFS(data!$A:$A,$B$5,data!$B:$B,$D4)),
              SEQUENCE(,MATCH($B$2,data!$D$1:$K$1,0))))

Если у вас более ранняя версия Excel, попробуйте:


=SUM(
     INDEX(data!$D:$K,
             AGGREGATE(15,6,1/((data!$B:$B=D4)*(data!$A:$A=$B$5))*ROW($A:$A),
                        ROW(INDEX($A:$A,1):INDEX($A:$A,COUNTIFS(data!$A:$A,$B$5,data!$B:$B,D4)))),
             TRANSPOSE(ROW(INDEX($A:$A,1):INDEX($A:$A,
                        MATCH($B$2,data!$D$1:$K$1))))))

Последнюю формулу может потребоваться подтвердить как формулу массива, удерживая нажатой ctrl + shift и нажимая enter. Если вы сделаете это правильно, Excel поместит фигурные скобки {...} вокруг формулы, отображаемой в строке формул.

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

person Ron Rosenfeld    schedule 22.09.2020