В предишната статия научихме как да сумираме горните или долните N стойности. В тази статия се опитваме да обобщим горните или долните N стойности с критерии.
Сума от ТОП N стойности с критерии
Как да решим проблема?
За тази статия ще трябва да използваме функцията SUMPRODUCT. Сега ще направим формула от тези функции. Тук ни е даден диапазон и критерии. Трябва да получим първите 5 стойности в диапазона и да получим сумата от стойностите въз основа на дадените критерии.
Обща формула:
= SUMPRODUCT (LARGE ((списък = критерии) * (диапазон), {1, 2,…., N}})
списък: списък с критерии
Критерии: критерии, които да отговарят
диапазон: диапазон от стойности
стойности: числа, разделени със запетаи, например ако искате да намерите първите 3 стойности, използвайте {1, 2, 3}.
Пример:
Тук имаме стойностите на набора от данни от A1: D50.
Първо, трябва да намерим първите пет стойности с помощта на функцията LARGE, която съответства на града „Бостън“ и след това да се извърши операция по сумата над тези 5 стойности. Сега ще използваме следната формула, за да получим сумата
Използвайте формулата:
= СУМПРОДУКТ (ГОЛЯМ ((Сити = "Бостън") * (количество), {1, 2, 3, 4, 5}))
Обяснение:
- Сити "Бостън" съвпада със споменатия диапазон на Сити. Това връща масив от true и false.
- Функцията LARGE връща първите 5 цифрови стойности от диапазона на количествата и връща масива към функцията SUMPRODUCT.
= СУМПРОДУКТ {193, 149, 138, 134, 123}
- Функцията SUMPRODUCT получава масив от топ 5 стойности, като масив от топ 5 числа връща SUM на тези числа.
Тук градът и количественият диапазон е даден като именен диапазон. Натиснете Enter, за да получите SUM на първите 5 числа.
Както можете да видите в горната снимка, тази сума е 737. Сумата от стойностите 193 + 149 + 138 + 134 + 123 = 737.
Можете да проверите горните стойности в набора от данни, като използвате опцията за филтър на Excel. Приложете филтъра към заглавката за град и количество и щракнете върху бутона със стрелка на заглавката на града, който се показва. Следвайте стъпките, както е показано по -долу.
Стъпки:
- Изберете заглавната клетка на града. Приложете филтъра с помощта на пряк път Ctrl + Shift + L
- Щракнете върху стрелката, която се появява като опция за филтър.
- Изберете опцията (Избор на всички).
- Изберете само град Бостън.
- Изберете заглавката за количество сега.
- Сортирайте списъка от най -големия към най -малкия и можете да видите всичките топ 5 стойности, които сме изчислили по формулата.
Както можете да видите в горния gif всички 5 стойности, които отговарят на дадените критерии. Това също означава, че формулата работи добре, за да получи броя на тези стойности
ГОЛЯМИ N числа
Горният процес се използва за изчисляване на сумата от няколко числа отгоре. Но да се изчисли за н (голям) брой стойности в дълъг диапазон.
Използвайте формулата:
= СУМПРОДУКТ (ГОЛЯМ ((Сити = "Бостън") * (количество), РЕД (ИНДИРЕКТНО ("1:10"))
Тук генерираме сума от топ 10 стойности чрез получаване на масив от 1 до 10 {1; 2; 3; 4; 5; 6; 7; 8; 9; 10} с помощта на функциите ROW & INDIRECT Excel.
Тук имаме сумата от първите 10 числа, което води до 1147.
Сума от долните N стойности с критерии
Как да решим проблема?
За тази статия ще трябва да използваме функцията SUMPRODUCT. Сега ще направим формула от тези функции. Тук ни е даден диапазон и трябва да намалим 5 стойности в диапазона и да получим сумата от стойностите.
Обща формула:
{= SUM (МАЛКО (АКО (Град = "Бостън", количество), {1, 2, 3, 4, 5}))}
Диапазон: диапазон от стойности
Стойности: числа, разделени със запетаи, например ако искате да намерите долните 3 стойности, използвайте {1, 2, 3}.
Пример:
Всичко това може да е объркващо за разбиране. Така че, нека тестваме тази формула, като я изпълним в примера, показан по -долу.
Тук имаме диапазон от стойности от A1: D50.
Тук диапазонът за град и количество е даден, като се използва инструментът за име на диапазон excel.
Първо, трябва да намерим най -долните пет стойности, използвайки функцията SMALL, която отговаря на критериите, и след това да се извърши операция по сумата над тези 5 стойности. Сега ще използваме следната формула, за да получим сумата
Използвайте формулата:
{= SUM (МАЛКО (АКО (Град = "Бостън", количество), {1, 2, 3, 4, 5}))}
НЕ използвайте ръчно къдрави скоби. Къдрави скоби, приложени с помощта на Ctrl + Shift + Enter на мястото на просто Въведете.
Обяснение:
- SMALL функция с IF функция връща най -долните 5 числови стойности, които съответстват на City "Boston" и връща масива на функцията SUM.
= SUM ({23, 27, 28, 28, 30}))
- Функцията SUM получава масива от долни 5 стойности, който има масив от долните 5 числа, връща SUM на тези числа, използвани с CTRL + SHIFT + ENTER.
Тук градът и количественият диапазон е даден като именен диапазон. Натиснете Ctrl + Shift + Enter за да получите SUM на долните 5 числа, тъй като това е формула на масив.
Както можете да видите в горната снимка, тази сума е 136.
Горният процес се използва за изчисляване на сумата от няколко числа от дъното. Но да се изчисли за н (голям) брой стойности в дълъг диапазон.
Използвайте формулата:
{ = SUM (МАЛКО (АКО (Град = "Бостън", количество), ROW (ИНДИРЕКТНО ("1:10")))) }
НЕ използвайте ръчно къдравите скоби. Използвайте Ctrl + Shift + Enter вместо да използвате Enter.
Тук генерираме сума от най -долните 10 стойности чрез получаване на масив от 1 до 10 {1; 2; 3; 4; 5; 6; 7; 8; 9; 10} с помощта на функциите ROW & INDIRECT Excel.
Тук имаме сумата от най -долните 10 числа, които ще доведат до 155.
Ето някои наблюдателни бележки, показани по -долу.
Бележки:
- Формулата работи само с числа.
- Формулата работи само когато няма дубликати в таблицата за търсене
- Функцията SUMPRODUCT разглежда нецифрени стойности (като текст abc) и грешки (като #NUM!, #NULL!) Като нулеви стойности.
- Функцията SUMPRODUCT разглежда логическата стойност TRUE като 1 и False като 0.
- Масивът на аргументите трябва да е със същата дължина като функцията.
Надявам се, че тази статия за това как да върнете сумата от горните 5 стойности или долните 5 стойности с критерии в Excel е обяснима. Намерете още статии за функциите SUMPRODUCT тук. Моля, споделете вашето запитване по -долу в полето за коментар. Ние ще ви съдействаме.
Ако ви харесаха нашите блогове, споделете го с приятелите си във Facebook. Можете също така да ни следвате в Twitter и Facebook. Ще се радваме да чуем от вас, уведомете ни как можем да подобрим, допълним или обновим работата си и да я подобрим. Пишете ни на имейл сайта
Как да използвате функцията SUMPRODUCT в Excel: Връща SUM след умножаване на стойности в множество масиви в Excel.
SUM, ако датата е между : Връща сумата на стойностите между датите или периода в Excel.
Сума, ако датата е по -голяма от датата: Връща сумата на стойностите след дадената дата или период в Excel.
2 начина за сумиране по месеци в Excel: Връща сумата на стойностите в рамките на даден конкретен месец в Excel.
Как да сумирате няколко колони с условие: Връща сумата на стойностите в множество колони, имащи условие в excel
Как да използвате заместващи символи в Excel : Пребройте клетки, съвпадащи с фрази, използвайки заместващи знаци в Excel
Популярни статии
50 Преки пътища в Excel за повишаване на вашата производителност
Редактиране на падащ списък
Абсолютна справка в Excel
Ако с условно форматиране
Ако с заместващи символи
Преглед по дата
Конвертирайте инчове в крака и инчове в Excel 2016
Присъединете се към собствено и фамилно име в Excel
Пребройте клетки, които съответстват или на А, или на В