Загальна формула
=SUMPRODUCT(SMALL(rng,(1,2,n)))
Резюме
Щоб підсумувати найнижчі n значень у діапазоні, ви можете використовувати формулу на основі функції SMALL та SUMPRODUCT. У загальній формі формули (вище) rng представляє діапазон комірок, що містять числові значення, а n - кількість найнижчих значень, що підсумовуються. У наведеному прикладі E5 містить цю формулу:
=SUMPRODUCT(SMALL(B4:B14,(1,2,3)))
який повертає суму трьох найменших значень у B5: B14, 60
Пояснення
У найпростішій формі SMALL поверне "n-те найменше" значення в діапазоні. Наприклад:
=SMALL(range,1) // smallest =SMALL(range,2) // 2nd smallest =SMALL(range,3) // 3rd smallest
Однак, якщо ви надаєте константу масиву (наприклад, константу у вигляді (1,2,3)) для SMALL як другий аргумент, SMALL поверне масив результатів замість одного результату. Наприклад:
=SMALL(A1:A10,(1,2,3))
поверне 1-е, 2-е і 3-те найменші значення в діапазоні A1: A10.
Працюючи з вивороту назовні у наведеному прикладі, SMALL повертає 3 найменші значення в діапазоні B5: B14:
=SMALL(B4:B14,(1,2,3))
Результат - такий масив:
(10,20,30)
Цей масив повертається безпосередньо до функції SUMPRODUCT, яка підсумовує числа і повертає загальну суму:
SUMPRODUCT((10,20,30)) // returns 60
Формула масиву з SUM
Зазвичай використовується SUMPRODUCT, як зазначено вище, оскільки він може обробляти масиви спочатку, не вводячи формулу масиву. Тим не менш, ви також можете написати формулу масиву з функцією SUM так:
(=SUM(SMALL(B4:B13,(1,2,3))))
Це формула масиву, яку потрібно вводити за допомогою control + shift + enter, за винятком Excel 365.
Коли n стає великим
Коли n стає великим, створювати константу масиву вручну стає нудно - введення константи масиву з 20 або 30 елементів займе багато часу. У цьому випадку ви можете використовувати ярлик для побудови константи масиву, яка використовує функції ROW та INDIRECT. Наприклад, для підсумовування нижчих 20 значень у діапазоні, що називається "rng", ви можете написати формулу наступною:
=SUMPRODUCT(SMALL(rng,ROW(INDIRECT("1:20"))))
Тут INDIRECT перетворює рядок "1:20" в діапазон 1:20, який повертається безпосередньо в SMALL.
Змінна n
Щоб налаштувати формулу, де n є змінною в іншій комірці, ви можете об'єднати всередині INDIRECT. Наприклад, якщо A1 містить N, ви можете використовувати:
=SUMPRODUCT(SMALL(rng,ROW(INDIRECT("1:"&A1))))
Це дозволяє користувачеві змінювати значення n безпосередньо на аркуші.
