Загальна формула
=SUMPRODUCT(LARGE(rng,(1,2,N)))
Резюме
Щоб підсумувати найвищі значення в діапазоні, ви можете використовувати формулу, засновану на функції LARGE, загорнутій у функцію SUMPRODUCT. У загальній формі формули (вище) rng представляє діапазон комірок, що містять числові значення, а N - ідею N-го значення.
У прикладі активна комірка містить таку формулу:
=SUMPRODUCT(LARGE(B4:B13,(1,2,3)))
Пояснення
У найпростішій формі LARGE поверне значення "N-го за величиною" в діапазоні. Наприклад, формула:
=LARGE(B4:B13, 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). Потім SUMPRODUCT підсумовує числа в цьому масиві і повертає загальну суму, яка дорівнює 27.
SUM замість SUMPRODUCT
SUMPRODUCT - це гнучка функція, яка дозволяє використовувати посилання на клітинки для k всередині функції LARGE.
Однак якщо ви використовуєте просту кодовану константу масиву, як (1,2,3), ви можете просто використовувати функцію SUM:
=SUM(LARGE(B4:B13,(1,2,3)))
Зверніть увагу, що ви повинні ввести цю формулу як формулу масиву, якщо ви використовуєте посилання на клітинки, а не константу масиву для k всередині LARGE.
Коли N стає великим
Коли N стає великим, створювати константу масиву вручну стає нудно - якщо ви хочете підвести до перших 20 або 30 значень у великому списку, введення константи масиву з 20 або 30 елементів займе багато часу. У цьому випадку ви можете використовувати ярлик для побудови константи масиву, яка використовує функції ROW та INDIRECT.
Наприклад, якщо ви хочете підсумувати 20 найпопулярніших значень у діапазоні, що називається "rng", ви можете написати таку формулу:
=SUMPRODUCT(LARGE(rng,ROW(INDIRECT("1:20"))))
Змінна N
При недостатній кількості даних фіксований N може спричинити помилки. У цьому випадку ви можете спробувати таку формулу:
=SUMPRODUCT(LARGE(rng,ROW(INDIRECT("1:"&MIN(3,COUNT(rng))))))
Тут ми використовуємо MIN з COUNT для підсумовування 3-х основних значень або підрахунку значень, якщо менше 3.
