Загальна формула
=SUMPRODUCT(LARGE((range=criteria)*(values),(1,2,3,N)))
Резюме
Щоб підсумувати верхні n значень у діапазоні, що відповідає критеріям, ви можете використовувати формулу, засновану на функції LARGE, загорнутій у функцію SUMPRODUCT. У загальній формі формули (вище) діапазон представляє діапазон комірок, які порівнюються з критеріями , значення представляє числові значення, з яких отримуються верхні значення, а N - ідею N-го значення.
У прикладі активна комірка містить таку формулу:
=SUMPRODUCT(LARGE((color=E5)*(value),(1,2,3)))
Де колір - іменований діапазон B5: B12, а значення - іменований діапазон C5: C12.
Пояснення
У найпростішій формі LARGE повертає значення "N-го за величиною" в діапазоні з такою конструкцією:
=LARGE (range,N)
Так, наприклад:
=LARGE (C5:C12,2)
поверне друге за величиною значення в діапазоні C5: C12, тобто 12 у наведеному прикладі.
Однак якщо ви надаєте "константу масиву" (наприклад, константу у формі (1,2,3)) у значення LARGE як другий аргумент, LARGE поверне масив результатів замість одного результату. Отже, формула:
=LARGE (C5:C12, (1,2,3))
поверне 1-е, 2-е і 3-те найбільше значення C5: C12 у масиві, подібному до цього: (12,12,10)
Отже, фокус тут полягає у фільтруванні значень на основі кольору перед запуском LARGE. Ми робимо це з виразом:
(color=E5)
Що призводить до масиву значень TRUE / FALSE. Під час операції множення ці значення змушуються до одиниць та нулів:
=LARGE((1;0;1;0;1;1;0;0)*(12;12;10;9;8;8;7;5),(1,2,3))
Отже, кінцевим результатом є те, що в операції виживають лише значення, пов’язані з кольором «червоний»:
=SUMPRODUCT(LARGE((12;0;10;0;8;8;0;0),(1,2,3)))
а інші значення змушені до нуля.
Примітка: ця формула не обробляє текст у діапазоні значень. Дивіться нижче.
Обробка тексту у значеннях
Якщо у вас є текст у будь-якому діапазоні значень, функція LARGE викличе помилку #VALUE і зупинить роботу формули.
Для обробки тексту в діапазоні значень можна додати функцію IFERROR наступним чином:
=SUM(IFERROR(LARGE(IF((color=E5),value),(1,2,3)),0))
Тут ми фіксуємо помилки LARGE, спричинені текстовими значеннями, і замінюємо нулем. Використання IF всередині LARGE вимагає введення формули з керуванням + shift + enter, тому ми перемикаємось на SUM замість SUMPRODUCT.
Примітка: Я натрапив на цю формулу, опубліковану дивовижним Баррі Гудіні на stackoverflow.
