Общая формула
=SUM(INDEX(FILTER(range,logic),SEQUENCE(n,1,1,1)))
Резюме
Чтобы суммировать первые n совпадающих значений в наборе данных, вы можете использовать формулу, основанную на функциях ФИЛЬТР и ПОСЛЕДОВАТЕЛЬНОСТЬ. В показанном примере формула в ячейке G5, скопированная вниз, имеет следующий вид:
=SUM(INDEX(FILTER(score,name=F5),SEQUENCE(3,1,1,1)))
где имя (B5: B16) и оценка (C5: C16) являются именованными диапазонами.
Объяснение
Функция ФИЛЬТР, новая в Excel 365, может помочь упростить некоторые сложные задачи с формулами.
В этом примере цель состоит в том, чтобы суммировать первые 3 балла Джейка и Хейли, исходя из порядка их появления в таблице. Всего набрано 12 оценок, а у Джейка и Хейли по 6 оценок.
Работая изнутри, первая задача - создать список оценок для каждого имени. Это делается с помощью функции ФИЛЬТР:
FILTER(score,name=F5)
С «Джейком» в ячейке F5 результатом будет массив со всеми оценками Джейка, подобными этому:
(6;5;7;7;6;8)
Этот массив возвращается в функцию ИНДЕКС в качестве аргумента массива:
INDEX((6;5;7;7;6;8),SEQUENCE(3,1,1,1))
Функция ПОСЛЕДОВАТЕЛЬНОСТЬ используется для генерации значения номера строки и возвращает массив с 3 числами,
SEQUENCE(3,1,1,1) // returns (1;2;3)
На этом этапе мы можем записать часть формулы ИНДЕКС следующим образом:
INDEX((6;5;7;7;6;8),(1;2;3))
ИНДЕКС возвращает значения, связанные с первыми 3 строками в массиве, функции СУММ:
=SUM((6;5;7)) // returns 18
а СУММ возвращает сумму этих значений в качестве окончательного результата в G5. Когда формула копируется в ячейку G6, результатом является сумма первых трех оценок Хейли.
Суммировать последние n совпадающих значений
Чтобы суммировать последние n совпадающих значений, вы можете адаптировать формулу следующим образом:
=SUM(INDEX(FILTER(score,name=F5),SEQUENCE(3,1,SUM(--(name=F5)),-1)))
Эта формула объясняется более подробно здесь.
