Общая формула
=SUMPRODUCT(LARGE(rng,(1,2,N)))
Резюме
Чтобы суммировать верхние значения в диапазоне, вы можете использовать формулу, основанную на функции НАИБОЛЬШИЙ, заключенную в функцию СУММПРОИЗВ. В общей форме формулы (выше) rng представляет диапазон ячеек, содержащих числовые значения, а N представляет идею N-го значения.
В примере активная ячейка содержит эту формулу:
=SUMPRODUCT(LARGE(B4:B13,(1,2,3)))
Объяснение
В простейшей форме LARGE возвращает «N-е по величине» значение в диапазоне. Например, формула:
=LARGE(B4:B13, 2)
вернет 2-е по величине значение в диапазоне B4: B13, которое в приведенном выше примере является числом 9.
Однако, если вы предоставите «константу массива» (например, константу в форме (1,2,3)) для LARGE в качестве второго аргумента, LARGE вернет массив результатов вместо одного результата. Итак, формула:
=LARGE(B4:B13,(1,2,3))
вернет 1-е, 2-е и 3-е наибольшее значение в диапазоне B4: B13. В приведенном выше примере, где B4: B13 содержит числа от 1 до 10, результатом LARGE будет массив (8,9,10). Затем СУММПРОИЗВ суммирует числа в этом массиве и возвращает итог, равный 27.
СУММА вместо СУММПРОИЗВ
СУММПРОИЗВ - это гибкая функция, которая позволяет использовать ссылки на ячейки для k внутри функции НАИБОЛЬШИЙ.
Однако, если вы используете простую жестко закодированную константу массива, например (1,2,3), вы можете просто использовать функцию SUM:
=SUM(LARGE(B4:B13,(1,2,3)))
Обратите внимание, что вы должны ввести эту формулу как формулу массива, если вы используете ссылки на ячейки, а не константу массива для k внутри LARGE.
Когда N становится большим
Когда N становится большим, становится утомительно создавать константу массива вручную - если вы хотите суммировать до 20 или 30 верхних значений в большом списке, ввод константы массива с 20 или 30 элементами займет много времени. В этом случае вы можете использовать ярлык для построения константы массива, которая использует функции СТРОКА и ДВССЫЛ.
Например, если вы хотите СУММИРОВАТЬ 20 лучших значений в диапазоне под названием «rng», вы можете написать такую формулу:
=SUMPRODUCT(LARGE(rng,ROW(INDIRECT("1:20"))))
Переменная N
При недостаточном количестве данных фиксированное N может вызвать ошибки. В этом случае вы можете попробовать такую формулу:
=SUMPRODUCT(LARGE(rng,ROW(INDIRECT("1:"&MIN(3,COUNT(rng))))))
Здесь мы используем MIN с COUNT для суммирования трех верхних значений или количества значений, если их меньше 3.
