Сумма наименьших N значений
=СУММПРОИЗВ(НАИМЕНЬШИЙ(rng; {1; 2; n}))
Если нужно суммировать наименьшие значения в диапазоне. Rng в сумме — диапазон с нужными числами, а {1; 2; n} — массив количества наименьших, которые нужно суммировать.
Формула в примере:
=СУММПРОИЗВ(НАИМЕНЬШИЙ(B3:B12;{1;2;3}))
НАИМЕНЬШИЙ в этом случае посчитает заданное количество наименьших значений, а СУММПРОИЗВ правильно просуммирует массив. Можно использовать и обычную СУММ, если ввести формулу массива:
{=СУММ(НАИМЕНЬШИЙ(B4: B13; { 1; 2; 3} )) }
Если вам нужно не 3, а 20 наименьших, прописывать 20 чисел будет сложно. В том случае создать ярлык — строку с числами:
=СУММПРОИЗВ(НАИМЕНЬШИЙ(rng; СТРОКА(ДВССЫЛ(«1:25»)))
Сумма наибольших N значений
=СУММПРОИЗВ(НАИБОЛЬШИЙ(rng ; { 1 ; 2 ; N } ))
Чтобы подсчитать верхние значения в диапазоне, вы можете использовать формулу, основанную на функции НАИБОЛЬШИЙ, завернутой внутри функции СУММПРОИЗВ. В общей форме формулы (выше), rng представляет собой диапазон ячеек, которые содержат числовые значения и N представляет идею значения N — го.
В примере, активная ячейка содержит следующую формулу:
=СУММПРОИЗВ( НАИБОЛЬШИЙ( B3: B12 ; 3 ))
В своей простейшей форме, НАИБОЛЬШИЙ рассчитает «N-ые величины» значение в диапазоне. Формула рассчитает 3ье по размеру значение в диапазоне В3: В12, которым, в приведенном выше примере, является число 8.
Тем не менее, если вы подставляете «константу массива» (например, константа в виде {1,2,3}) в НАИБОЛЬШИЙ в качестве второго аргумента, НАИБОЛЬШИЙ получает массив результатов вместо одного результата. Таким образом, формула:
=НАИБОЛЬШИЙ( В3: В12 ; { 1 ; 2 ; 3 })
получит 1, 2 и 3-е по величине значение в диапазоне B3: B12. В приведенном выше примере, где В3: В12 содержит номера 1-10, результат от НАИБОЛЬШИЙ будет массив {8,9,10}. СУММПРОИЗВ затем суммирует числа в этом массиве и получает общую сумму, которая равна 27.