В тази статия ще научим как да сумираме горните или долните N стойности, дадени в масив от числа в Excel. Ако искате да сумирате топ 4, топ 5 или топ N стойности в даден диапазон, тази статия ще ви помогне да направите това.
Сума от ТОП N стойности
За тази статия ще трябва да използваме функцията SUMPRODUCT. Сега ще направим формула от тези функции. Тук ни е даден диапазон и трябва да надхвърлим 5 стойности в диапазона и да получим сумата от стойностите.
Обща формула:
= SUMPRODUCT (ГОЛЯМ (диапазон, {1, 2,…., N}})
диапазон: диапазон от стойности
Стойности: числа, разделени със запетаи, например ако искате да намерите първите 3 стойности, използвайте {1, 2, 3}.
Пример: Сумата на топ 5 стойности на обхвата
Нека тестваме тази формула, като я изпълним в примера, показан по -долу.
Тук имаме диапазон от стойности от A1: A23.
Обхватът A1: A23 съдържа стойности и е кръстен като стойности.
Първо, трябва да намерим първите пет стойности с помощта на функцията LARGE и след това да се извърши операция по сумата над тези 5 стойности. Сега ще използваме следната формула, за да получим сумата от първите 5 стойности.
Използвайте формулата:
= СУМПРОДУКТ (ГОЛЯМ (диапазон, {1, 2, 3, 4, 5}))
Обяснение:
- Функцията LARGE връща първите 5 цифрови стойности и връща масива към функцията SUMPRODUCT.
= СУМПРОДУКТ ({149, 123, 100, 87, 85}))
- Функцията SUMPRODUCT получава масив от топ 5 стойности за обобщение.
Тук диапазонът е даден като именен диапазон. Натиснете 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.
Сума от долните N стойности
Как да решим проблема?
Обща формула:
= СУМПРОДУКТ (МАЛКИ (диапазон, {1, 2,…., N}})
Диапазон: диапазон от стойности
Стойности: числа, разделени със запетаи, например ако искате да намерите долните 3 стойности, използвайте {1, 2, 3}.
Пример:
Всичко това може да е объркващо за разбиране. Така че, нека тестваме тази формула, като я изпълним в примера, показан по -долу.
Тук имаме диапазон от стойности от A1: A23.
Ето обхвата, който е даден с помощта на инструмента за име на range excel.
Първо, трябва да намерим най -долните пет стойности с помощта на функцията SMALL и след това да се извърши операция по сумата над тези 5 стойности. Сега ще използваме следната формула, за да получим сумата
Използвайте формулата:
= СУМПРОДУКТ (МАЛКИ (диапазон, {1, 2, 3, 4, 5}))
Обяснение:
- Функцията SMALL връща долните 5 цифрови стойности и връща масива към функцията SUMPRODUCT.
= СУМПРОДУКТ ({23, 27, 28, 28, 30}))
- Функцията SUMPRODUCT получава масив от най -долните 5 стойности за обобщаване.
Тук диапазонът е даден като именен диапазон. Натиснете 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.
Ето някои наблюдателни бележки, показани по -долу.
Бележки:
- Формулата работи само с числа.
- Формулата работи само когато няма дубликати в таблицата за търсене
- Функцията SUMPRODUCT разглежда нецифрени стойности (като текст abc) и грешки (като #NUM!, #NULL!) Като нулеви стойности.
- Функцията SUMPRODUCT разглежда логическата стойност TRUE като 1 и False като 0.
- Масивът на аргументите трябва да е със същата дължина като функцията.
Надявам се, че тази статия за това как да върнете сумата от горните n стойности или долните n стойности в Excel е обяснима. Намерете още статии за функциите SUMPRODUCT тук. Моля, споделете вашето запитване по -долу в полето за коментар. Ние ще ви съдействаме.
Как да използвате функцията SUMPRODUCT в Excel: Връща SUM след умножаване на стойности в множество масиви в Excel.
SUM, ако датата е между : Връща сумата на стойностите между датите или периода в Excel.
Сума, ако датата е по -голяма от датата: Връща сумата на стойностите след дадената дата или период в Excel.
2 начина за сумиране по месеци в Excel: Връща сумата на стойностите в рамките на даден конкретен месец в Excel.
Как да сумирате няколко колони с условие: Връща сумата на стойностите в множество колони, имащи условие в excel
Как да използвате заместващи символи в Excel : Пребройте клетки, съвпадащи с фрази, използвайки заместващи знаци в Excel
Популярни статии
50 Преки пътища в Excel за повишаване на вашата производителност
Редактиране на падащ списък
Абсолютна справка в Excel
Ако с условно форматиране
Ако с заместващи символи
Преглед по дата
Конвертирайте инчове в крака и инчове в Excel 2016
Присъединете се към собствено и фамилно име в Excel
Пребройте клетки, които съответстват или на А, или на В
