В тази статия ще научим как да сумираме долните 5 или n стойности в колона в Excel.
Сценарий:
С прости думи, докато работите с числа с данни, понякога се дава условие, т.е. когато трябва да получим сумата от числата на най -долните или най -ниските 5 числа от масива или дъното на масива. Можете лесно да разрешите този проблем, като използвате функциите на Excel, както е обяснено по -долу.
Сума от долните N стойности
Как да решим проблема?
За тази статия ще трябва да използваме функцията SUMPRODUCT. Сега ще направим формула от тези функции. Тук ни е даден диапазон и трябва да намалим 5 стойности в диапазона и да получим сумата от стойностите.
Обща формула:
| = СУМПРОДУКТ (МАЛКИ (диапазон, {1, 2,…., N}}) |
range: диапазон от стойности
Стойности: числа, разделени със запетаи, например ако искате да намерите долните 3 стойности, използвайте {1, 2, 3}.
Пример:
Всичко това може да е объркващо за разбиране. Така че, нека тестваме тази формула, като я изпълним в примера, показан по -долу.
Тук имаме диапазон от стойности от A1: A23.
Тук диапазонът е даден като използване на инструмента за име на диапазон excel.
Първо, трябва да намерим най -долните пет стойности с помощта на функцията SMALL и след това да се извърши операция по сумата над тези 5 стойности. Сега ще използваме следната формула, за да получим сумата
Използвайте формулата:
| = СУМПРОДУКТ (МАЛКИ (диапазон, {1, 2, 3, 4, 5})) |
Обяснение:
- Функцията SMALL връща долните 5 цифрови стойности и връща масива към функцията SUMPRODUCT.
= СУМПРОДУКТ ({23, 27, 28, 28, 30}))
- Функцията SUMPRODUCT получава масив от долни 5 стойности, който има масив от долни 5 числа, връща SUM на тези числа.
Тук диапазонът е даден като именен диапазон. Натиснете Enter, за да получите SUM на долните 5 числа.
Както можете да видите в горната снимка, тази сума е 136. Сумата от стойностите 23 + 27 + 28 + 28 + 30 = 136.
Горният процес се използва за изчисляване на сумата от няколко числа от дъното. Но да се изчисли за н (голям) брой стойности в дълъг диапазон.
Използвайте формулата:
| = СУМПРОДУКТ (МАЛКИ (диапазон, ROW (ИНДИРЕКТНО ("1:10")) |
Тук генерираме сума от най -долните 10 стойности чрез получаване на масив от 1 до 10 {1; 2; 3; 4; 5; 6; 7; 8; 9; 10} с помощта на функциите ROW & INDIRECT Excel.
Тук имаме сумата от най -долните 10 числа, което води до 307.
Сума от ТОП N стойности
Как да решим проблема?
За тази статия ще трябва да използваме функцията SUMPRODUCT. Сега ще направим формула от тези функции. Тук ни е даден диапазон и трябва да надхвърлим 5 стойности в диапазона и да получим сумата от стойностите.
Обща формула:
| = SUMPRODUCT (ГОЛЯМ (диапазон, {1, 2,…., N}}) |
range: диапазон от стойности
Стойности: числа, разделени със запетаи, например ако искате да намерите първите 3 стойности, използвайте {1, 2, 3}.
Пример:
Всичко това може да е объркващо за разбиране. Така че, нека тестваме тази формула, като я изпълним в примера, показан по -долу.
Тук имаме диапазон от стойности от A1: A23.
Тук диапазонът е даден като използване на инструмента за име на диапазон excel.
Първо, трябва да намерим първите пет стойности с помощта на функцията LARGE и след това да се извърши операция по сумата над тези 5 стойности. Сега ще използваме следната формула, за да получим сумата
Използвайте формулата:
| = СУМПРОДУКТ (ГОЛЯМ (диапазон, {1, 2, 3, 4, 5})) |
Обяснение:
- Функцията LARGE връща първите 5 цифрови стойности и връща масива към функцията SUMPRODUCT.
= СУМПРОДУКТ ({149, 123, 100, 87, 85}))
- Функцията SUMPRODUCT получава масив от топ 5 стойности, който има масив от топ 5 числа връща SUM на тези числа.
Тук диапазонът е даден като именен диапазон. Натиснете Enter, за да получите SUM на първите 5 числа.
Както можете да видите в горната снимка, тази сума е 544. Сумата от стойностите 149 + 123 + 100 + 87 + 85 = 544.
Горният процес се използва за изчисляване на сумата от няколко числа отгоре. Но да се изчисли за н (голям) брой стойности в дълъг диапазон.
Използвайте формулата:
| = СУМПРОДУКТ (ГОЛЯМ (диапазон, ред) (ИНДИРЕКТНО ("1:10")) |
Тук генерираме сума от топ 10 стойности чрез получаване на масив от 1 до 10 {1; 2; 3; 4; 5; 6; 7; 8; 9; 10} с помощта на функциите ROW & INDIRECT Excel.
Тук имаме сумата от първите 10 числа {149; 123; 100; 87; 85; 82; 61; 58; 54; 51}), което води до 850.
Ето някои наблюдателни бележки, показани по -долу.
Бележки:
- Формулата работи само с числа.
- Формулата работи само когато няма дубликати в таблицата за търсене
- Функцията SUMPRODUCT разглежда нецифрени стойности (като текст abc) и грешки (като #NUM!, #NULL!) Като нулеви стойности.
- Функцията SUMPRODUCT разглежда логическата стойност TRUE като 1 и False като 0.
- Масивът на аргументите трябва да е със същата дължина, освен функцията.
Надявам се, че тази статия за това как да сумирате долните 5 или n стойности в колона в Excel е обяснима. Намерете още статии за изчисляване на стойности и свързани формули на Excel тук. Ако ви харесаха нашите блогове, споделете го с приятелите си във Facebook. Можете също така да ни следвате в Twitter и Facebook. Ще се радваме да чуем от вас, уведомете ни как можем да подобрим, допълним или обновим работата си и да я подобрим. Пишете ни на имейл сайта.
Как да използвате функцията SUMPRODUCT в Excel: Връща SUM след умножаване на стойности в множество масиви в Excel.
SUM, ако датата е между : Връща сумата на стойностите между датите или периода в Excel.
Сума, ако датата е по -голяма от датата: Връща сумата на стойностите след дадената дата или период в Excel.
2 начина за сумиране по месеци в Excel: Връща сумата на стойностите в рамките на даден конкретен месец в Excel.
Как да сумирате няколко колони с условие: Връща сумата на стойностите в множество колони, имащи условие в excel
Как да използвате заместващи символи в Excel : Пребройте клетки, съвпадащи с фрази, използвайки заместващи знаци в Excel
Популярни статии:
Как да използвате функцията IF в Excel : Изразът IF в Excel проверява условието и връща конкретна стойност, ако условието е TRUE или връща друга конкретна стойност, ако FALSE.
Как да използвате функцията VLOOKUP в Excel : Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.
Как да използвате функцията SUMIF в Excel : Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.
Как да използвате функцията COUNTIF в Excel : Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да броите конкретни стойности. Функцията Countif е от съществено значение за подготовката на вашето табло.