Сумирайте най-малките или долните N стойности въз основа на критерии в Excel
В предишния урок обсъдихме как да сумирате най-малките n стойности в диапазон от данни. В тази статия ще извършим допълнителна разширена операция – да сумираме най-ниските n стойности въз основа на един или повече критерии в Excel.
Сумирайте най-малките или долните N стойности въз основа на критерии в Excel
Да предположим, че имам набор от данни, както е показано на екранната снимка по-долу, сега искам да сумирам най-ниските 3 поръчки на продукта Apple.
В Excel, за да сумирате най-долните n стойности в диапазон с критерии, можете да създадете формула за масив, като използвате функциите SUM, SMALL и IF. Общият синтаксис е:
Array formula, should press Ctrl + Shift + Enter keys together.
- range=criteria: Диапазонът от клетки, които отговарят на конкретните критерии;
- values: Списъкът, който съдържа най-долните n стойности, които искате да сумирате;
- N: N-тата долна стойност.
За да разрешите горния проблем, моля, приложете формулата за масив по-долу в празна клетка:
И след това натиснете Ctrl + Shift + Enter клавиши заедно, за да получите правилния резултат, както е показано на екранната снимка по-долу:
Обяснение на формулата:
=SUM(SMALL(IF(($A$2:$A$14=D2), $B$2:$B$14),{1,2,3}))
- IF(($A$2:$A$14=D2), $B$2:$B$14): Ако продуктът в диапазон A2:A14 е равен на „Apple“, той ще върне относителния номер от списъка с поръчки (B2:B14); Ако продуктът не е „Apple“, ще се покаже FALSE. Ще получите резултата по следния начин: {800;FALSE;FALSE;FALSE;1000;230;FALSE;FALSE;1600;FALSE;900;FALSE;500}.
- SMALL(IF(($A$2:$A$14=D2), $B$2:$B$14),{1,2,3}): Тази функция SMALL ще игнорира FALSE стойностите и ще върне долните 3 стойности в масива, така че резултатът е следният: {230,500,800}.
- SUM(SMALL(IF(($A$2:$A$14=D2), $B$2:$B$14),{1,2,3}))=SUM({230,500,800}): И накрая, функцията SUM сумира числата в масива, за да получи резултата: 1530.
Съвети: Справете се с две или повече условия:
Ако трябва да сумирате най-долните n стойности въз основа на два или повече критерия, просто трябва да добавите друг диапазон и критерии чрез знак * в рамките на функцията IF по следния начин:
Array formula, should press Ctrl + Shift + Enter keys together.
- Range1=criteria1: Първият диапазон от клетки, отговарящ на първия критерий;
- Range2=criteria2: Вторият диапазон от клетки, който отговаря на втория критерий;
- Range3=criteria3: Третият диапазон от клетки, който отговаря на третия критерий;
- values: Списъкът, който съдържа най-долните n стойности, които искате да сумирате;
- N: N-тата долна стойност.
Например, искам да сумирам долните 3 поръчки на продукт Apple, който се продава от Kerry, моля, приложете формулата по-долу:
И след това натиснете Ctrl + Shift + Enter ключове заедно, за да получите желания резултат:
Използвана относителна функция:
- СУММА:
- Функцията SUM добавя стойности. Можете да добавите отделни стойности, препратки към клетки или диапазони или комбинация от трите.
- МАЛКИ:
- Функцията SMALL на Excel връща числова стойност въз основа на нейната позиция в списък, когато е сортирана по стойност във възходящ ред.
- IF:
- Функцията IF тества за конкретно условие и връща съответната стойност, която предоставяте за TRUE или FALSE.
Още статии:
- Сума на най-малките или долните N стойности
- В Excel за нас е лесно да сумираме диапазон от клетки с помощта на функцията SUM. Понякога може да се наложи да сумирате най-малките или долните 3, 5 или n числа в диапазон от данни, както е показано на екранната снимка по-долу. В този случай SUMPRODUCT заедно с функцията SMALL може да ви помогне да разрешите този проблем в Excel.
- Междинни суми по фактури по възраст в Excel
- Сумирането на сумите на фактурите въз основа на възрастта, както е показано на екранната снимка по-долу, може да е често срещана задача в Excel, този урок ще покаже как да сумирате сумите на фактурите по възраст с нормална функция SUMIF.
- Сумирайте всички числови клетки, като игнорирате грешките
- Когато сумирате диапазон от числа, които съдържат някои стойности на грешка, нормалната функция SUM няма да работи правилно. За да сумирате само числа и да пропуснете стойностите на грешката, функцията AGGREGATE или SUM заедно с функциите IFERROR могат да ви направят услуга.
Най-добрите инструменти за производителност в офиса
Kutools за Excel - помага ви да се откроите от тълпата
Kutools за Excel разполага с над 300 функции, Гарантираме, че това, от което се нуждаете, е само на един клик разстояние...
Раздел Office - Активиране на четене и редактиране с раздели в Microsoft Office (включително Excel)
- Една секунда за превключване между десетки отворени документи!
- Намалете стотиците кликвания на мишката за вас всеки ден, кажете сбогом на ръката на мишката.
- Увеличава продуктивността ви с 50% при преглеждане и редактиране на множество документи.
- Внася ефективни раздели в Office (включително Excel), точно като Chrome, Edge и Firefox.
