Простая таблица Excel, такая как
A B
1 John 5
2 John 7
3 John 9
4 Jill 25
5 John 21
6 John 22
7 Jill 50
8 John 100
9 John 2000
10 Jack 4
Используя СУММЕСЛИ, мы можем вернуть сумму, назначенную Джону.
=SUMIF(A:A,"John",B:B)
Есть ли способ вернуть только первые 5 значений, соответствующих критериям? Или есть способ вернуть 5 наименьших значений для Джона? Либо сработает.
Ну что ж. Я предполагаю, что у вас есть Excel 2010 или новее.
Например, с «Джон» в D1, введите эту формулу в E1:
= СУММЕСЛИМН ($ B $ 1: $ B $ 10, $ A $ 1: $ A $ 10, D1, $ B $ 1: $ B $ 10, «‹ = »& AGGREGATE (15,6, $ B $ 1: $ B $ 10 / ($ A $ 1: $ A $ 10 = D1), 5))
Скопируйте, чтобы получить аналогичные результаты для имен в D2, D3 и т. Д.
С Уважением
=IF(COUNTIF($A$1:A1,A1)<=5,SUMIF($A$1:A1,A1,$B$1:B1),"")
Последнее значение, показанное для каждого человека, будет суммой первых (до) 5 значений для этого человека. Просто скопируйте и вставьте значения, а затем выполните сортировку.
Ваш образец данных покажет тот же результат для первых 5 или самых младших 5, поскольку числа Джона расположены в возрастающем порядке. Если это не всегда так или вам нужно обеспечить совместимость с версиями Excel до 2010 года, я бы предложил следующее. Обратите внимание, что в моем образце изображения я использовал числовые значения в порядке убывания, чтобы проиллюстрировать разницу.
Для первых 5 значений Джона (E2 на образце изображения):
=SUM(INDEX(($B$2:$B$11)*($A$2:$A$11=D2)*(ROW($1:$10)<=SMALL(INDEX(ROW($1:$10)+($A$2:$A$11<>D2)*1E+99,,), 5)),,))
Для 5 наименьших значений Джона (F2 на образце изображения):
=SUMPRODUCT(SMALL(INDEX(($B$2:$B$11)+($A$2:$A$11<>D2)*1E+99,,),ROW($1:$5)))
Это стандартные формулы. Любая обработка массива обеспечивается INDEX и / или SUMPRODUCT. Ctrl + Shift + Enter не требуется. Некоторая форма контроля ошибок может потребоваться, когда имеется менее 5 совпадающих значений; достаточно простого IF(COUNTIF(), <formula>). При расшифровке формул такого типа важно отметить, что СТРОКА (1:10) - это позиция внутри B2: B11 или A2: A11, а не фактическая строка на листе.
В C1 введите:
=IF(A1="John",1,0)
В C2 введите:
=IF(A2="John",1+MAX($C$1:C1),0)
и скопируйте. Затем используйте:
=SUMPRODUCT((A:A="John")*(B:B)*(C:C<6))
. 
Предполагая, что Джон в D1, вы можете получить сумму 5 наименьших значений для Джона с помощью этой формулы массива
=SUM(SMALL(IF(A$1:A$100=D1,B$1:B$100),{1,2,3,4,5}))
подтвердите с помощью CTRL + SHIFT + ENTER и скопируйте вниз, чтобы работать для всех имен в списке
