Ако искате да обобщите 3 стойности в Excel, можете да го направите, като просто изчислите първите 3 стойности с помощта на функция LARGE и ги сумирате.
Но това не е единственият начин. Можете да сумирате горните N стойности в Excel, като използвате множество формули. Всички те ще използват функцията LARGE като ключова функция. Вижте изображението по -горе. Нека ги разгледаме всички.
Сума от топ 3 или топ N стойности, използващи функцията SUMIF
Обща формула
| = SUMIF (range, "> =" & LARGE (range, n)) |
Диапазон:Диапазонът е диапазонът, от който искате да сумирате горните N стойности.
н:Това е броят на горните стойности, които искате да обобщите. Ако искате да сумирате топ 3 стойности тогаване 3.
Така че нека видим тази формула в действие.
Пример 1: Използване на SUMIF за сумиране на първите 3 продажби.
Имаме данни, които съдържат продажбите, извършени през различни месеци. Трябва да обобщим първите 3 продажби. Използваме общата формула по -горе.
The диапазон е С2: С13 ин е 3. Тогава формулата ще бъде:
| = SUMIF (C2: C13, "> =" & ГОЛЯМ (C2: C13,3)) |
Това се връща към 696020, което е правилно. Ако искате да обобщите горните стойности на променливата N и N е записано в D2, тогава формулата ще бъде:
| = SUMIF (C2: C13, "> =" & ГОЛЯМ (C2: C13, D2)) |
той ще обобщи всички стойности, които са по -големи или равни на стойността на горната N стойност.
Как работи това?
Функцията LARGE връща горната N -та стойност в дадения диапазон. В горната формула функцията LARGE връща третата по големина стойност в диапазонаC2: C13което е 212940. Сега формулата е:
| = SUMIF (C2: C13, "> =" & 212940) |
Сега функцията SUMIF просто връща SUM на стойности, по -големи или равни на 212940, което е 696020.
Ако искате да добавите няколко условия за сумиране на горните 3 или N стойности, използвайте функцията SUMIFS.
Сума от топ 3 или топ N стойности, използващи функцията SUM
Горният метод е доста лесен и бърз за използване, когато става въпрос за сумиране на първите 3 или N стойности от списъка. Но става трудно, когато става въпрос за сумиране на горните произволни N стойности, да речем горната 2 -ра, 4 -та и 6 -та стойност от диапазон на Excel. Тук функцията SUM или SUMPRODUCT свършва работата.
Обща формула:
| = SUM (LARGE (диапазон, {1,2,3})) |
или
| = SUMPRODUCT (ГОЛЯМ (диапазон, {1,2,3})) |
Диапазон:Диапазонът е диапазонът, от който искате да обобщите горните N стойности.
{1,2,3}: Това са най -добрите стойности, които искате да обобщите. Тук е 1 -ви, 2 -ри и 3 -ти.
Така че нека използваме тази формула в горния пример.
Пример 2: Сумирайте най -големите 1 -ва, 2 -ра и 3 -та стойности от диапазон на Excel.
Така че, ние използваме същата таблица, която използвахме в горния пример. The диапазон все още е C2: C13 и първите 3 стойности, които искаме да сумираме, са 1 -ва, 2 -ра и 3 -та най -големи стойности в диапазон.
| = СУМПРОДУКТ (ГОЛЯМ (C2: C13, {1,2,3})) |
Той връща същия резултат като преди, който е 696020. Ако искате да сумирате втората и четвъртата най -горните стойности, формулата ще бъде:
| = СУМПРОДУКТ (ГОЛЯМ (C2: C13, {2,4})) |
И резултатът ще бъде 425360.
Можете да замените функцията SUMPRODUCT с функцията SUM в горния случай. Но ако искате да дадете препратка към горните стойности, тогава ще трябва да използвате CTRL+SHIF+ENTER, за да го направите формула за масив.
| {= SUM (ГОЛЯМО (C2: C13, E2: E4))} |
Как работи?
Функцията LARGE приема масив от ключови стойности и връща масив от тези най -големи стойности. Тук голямата функция връща стойностите {241540; 183820; 38740} като горни 2 -ри, 4 -ти и 6 -ти стойности. Сега формулата става:
| = SUM ({241540; 183820; 38740}) |
или
| = СУМПРОДУКТ ({241540; 183820; 38740}) |
Това обобщава стойностите, върнати от функцията LARGE.
Обобщаване на голям диапазон от най -високи стойности в Excel
Да предположим, че искате да сумирате стойностите на най-добрите 10-2o стойности, всичко включено. В този случай гореспоменатите методи ще станат придирчиви и по-дълги. Най -добрият начин е да използвате общата формула по -долу, за да обобщите горните стойности:
| = SUMPRODUCT (LARGE (диапазон, ROW (INDIRECT ("начало: край")))) |
диапазон:Диапазонът е диапазонът, от който искате да сумирате горните N стойности.
"начало: край":Това е текстът от обхвата на най -добрите стойности, който искате да обобщите. Ако искате някои топ 3 до топ 6 стойности, тогава start: end ще бъде "3: 6".
Нека разгледаме тази формула в действие:
Пример 3: Сумирайте първите 3 до 6 стойности от продажбите:
Така че имаме същата таблица като горния пример. Единствената заявка е различна. Използвайки горната формула, пишем тази формула:
| = СУМПРОДУКТ (ГОЛЯМ (C2: C13, ROW (INDIRECT ("3: 6")))) |
Това връща стойността 534040. Това е сумата от 3 -та, 4 -та, 5 -та и 6 -та най -голяма стойност от диапазон C2: C13.
Как работи това?
Първо, функцията INDIRECT превръща текст „3: 6“ в действителен диапазон 3: 6. След това функцията ROW връща масив от номера на редове в диапазон 3: 6, който ще бъде {3,4,5,6}. След това функцията LARGE връща 3 -та, 4 -та, 5 -та и 6 -та най -големи стойности от диапазона C2: C13. И накрая, функцията SUMPRODUCT обобщава тези стойности и получаваме нашия резултат.
| = СУМПРОДУКТ (ГОЛЯМ (C2: C13, ROW (INDIRECT ("3: 6"))))
=СУПРОДУКТ (ГОЛЯМ (C2: C13,{3,4,5,6})) = СУМПРОДУКТ ({212940;183820;98540;38740}) = 534040 |
Бонус: Обобщете горните N стойности, използвайки функцията SEQUENCE
Функцията SEQUENCE е въведена в Excel 2019 и 365. Тя връща поредица от числа. Можем да го използваме, за да получим горните N стойности и след това да ги обобщим.
Обща формула:
| = SUMPRODUCT (LARGE (диапазон, SEQUENCE (num_values ,, [start_num], [стъпки])))) |
диапазон:Диапазонът е диапазонът, от който искате да сумирате горните N стойности.
num_values: Това е броят на горните стойности, които искате да сумирате.
[начален_ номер]:Това е началният номер на поредицата. Не е задължително. Ако пропуснете това, поредицата ще започне от 1.
[стъпки]: Това е разликата между следващото число от текущото. По подразбиране е 1.
Ако използваме тази обща формула, за да получим същия резултат, както направихме в предишния пример, формулата ще бъде:
| = SUM (ГОЛЯМО (C2: C13, ПОСЛЕДОВАТЕЛНОСТ (4,, 3))) |
Той ще върне стойността 534040.
Как работи?
Това е просто. Функцията SEQUENCE връща поредица от 4 стойности, които започват с 3 с интервал 1. Връща масива {3; 4; 5; 6}. Сега функцията LARGE връща най -големите стойности на съответните числа. И накрая, функцията SUM сумира тези стойности и получаваме резултата като 534040.
| = SUM (ГОЛЯМО (C2: C13, ПОСЛЕДОВАТЕЛНОСТ (4,,3)))
=СУММА (ГОЛЯМА (C2: C13,{3;4;5;6})) =SUM ({212940;183820;98540;38740}) =534040 |
Така че да, момчета, ето как можете да обобщите първите 3, 5, 10,… N стойности в Excel. Опитах се да обясня. Надявам се да ви помогне. Ако имате някакви съмнения относно тази тема или друга тема, свързана с Excel VBA, попитайте в секцията за коментари по -долу. Често отговарям на запитвания.
Сума, ако е по -голяма от 0 в Excel | За да сумираме само положителни стойности, трябва да сумираме стойности, които са по -големи от 0. За да направим това, ще използваме функцията SUMIF на Excel.
Как да сумираме продажбите за последните 30 дни в Excel | Понякога той също се обобщава на стандартен 30 -дневен интервал. В този случай, наличието на формула, която динамично показва сумата от последните 30 дни, ще бъде добра идея. В тази статия ще научим как да сумираме стойностите за последните 30 дни в Excel.
Как да сумираме последните n колони в Excel | Понякога се дава условие, т.е. когато трябва да получим сумата от последните 5 или n числа от колоната. Можете лесно да разрешите този проблем, като използвате функциите на Excel.
Как да сумирате чрез съвпадение на ред и колона в Excel | Как да сумираме стойности, когато трябва да съпоставим колона и ред. В тази статия ще научим как да правим сумата от съвпадащи редове и колони.
Популярни статии:
50 преки пътища в Excel за повишаване на вашата производителност | Бъдете по -бързи в задачата си. Тези 50 преки пътища ще ви накарат да работите още по -бързо в Excel.
Как да използвате функцията Excel VLOOKUP| Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.
Как да използвате Excel COUNTIF функция| Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да преброите конкретна стойност. Функцията Countif е от съществено значение за подготовката на вашето табло.
Как да използвате функцията SUMIF в Excel | Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.
Популярни статии:
50 преки пътища в Excel за повишаване на вашата производителност | Бъдете по -бързи в задачата си. Тези 50 преки пътища ще ви накарат да работите още по -бързо в Excel.
Как да използвате функцията Excel VLOOKUP| Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.
Как да използвате Excel COUNTIF функция| Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да преброите конкретна стойност. Функцията Countif е от съществено значение за подготовката на вашето табло.
Как да използвате функцията SUMIF в Excel | Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.