Ja vēlaties apkopot Excel trīs vērtības, varat to izdarīt, vienkārši aprēķinot 3 galvenās vērtības, izmantojot funkciju LARGE, un apkopojot tās.
Bet tas nav vienīgais veids. Izmantojot vairākas formulas, programmā Excel varat apkopot pirmās N vērtības. Visi kā galveno funkciju izmantos LARGE funkciju. Skatiet attēlu iepriekš. Izpētīsim tos visus.
Top 3 vai Top N vērtību summa, izmantojot SUMIF funkciju
Vispārīga formula
| = SUMIF (diapazons, "> =" & LARGE (diapazons, n)) |
Diapazons:Diapazons ir diapazons, no kura vēlaties summēt augstākās N vērtības.
n:Šis ir to galveno vērtību skaits, kuras vēlaties apkopot. Ja vēlaties apkopot 3 lielākās vērtībasnir 3.
Tātad, apskatīsim šo formulu darbībā.
1. piemērs: SUMIF izmantošana, lai summētu 3 populārākos pārdošanas apjomus.
Mums ir dati, kas satur pārdošanas apjomus dažādos mēnešos. Mums ir jāapkopo trīs labākie pārdošanas apjomi. Mēs izmantojam iepriekš minēto vispārīgo formulu.
The diapazons ir C2: C13 unn ir 3. Tad formula būs:
| = SUMIF (C2: C13, "> =" & LIELS (C2: C13,3)) |
Tas atgriežas pie 696020, kas ir pareizi. Ja vēlaties apkopot augšējo mainīgo N vērtības un N ir rakstīts D2, formula būs šāda:
| = SUMIF (C2: C13, "> =" & LIELS (C2: C13, D2)) |
tas apkopos visas vērtības, kas ir lielākas vai vienādas ar augstākās N vērtības vērtību.
Kā tas darbojas?
Funkcija LARGE atgriež augšējo N vērtību attiecīgajā diapazonā. Iepriekšminētajā formulā funkcija LARGE atgriež trešo lielāko vērtību diapazonāC2: C13kas ir 212940. Tagad formula ir šāda:
| = SUMIF (C2: C13, "> =" & 212940) |
Tagad funkcija SUMIF vienkārši atgriež vērtību SUM, kas ir lielāka vai vienāda ar 212940, kas ir 696020.
Ja 3 vai N vērtību summai vēlaties pievienot vairākus nosacījumus, izmantojiet funkciju SUMIFS.
Top 3 vai Top N vērtību summa, izmantojot SUM funkciju
Iepriekš minētā metode ir diezgan vienkārša un ātra lietošanā, kad tiek apkopotas saraksta 3 vai N galvenās vērtības. Bet kļūst sarežģīti, summējot patvaļīgās N vērtības, piemēram, 2., 4. un 6. vērtību no Excel diapazona. Šeit funkcija SUM vai SUMPRODUCT paveic darbu.
Vispārējā formula:
| = SUMMA (LIELA (diapazons, {1,2,3})) |
vai
| = SUMPRODUCT (LARGE (diapazons, {1,2,3})) |
Diapazons:Diapazons ir diapazons, no kura vēlaties apkopot augstākās N vērtības.
{1,2,3}: Šīs ir galvenās vērtības, kuras vēlaties apkopot. Šeit tas ir pirmais, otrais un trešais.
Tātad izmantosim šo formulu iepriekš minētajā piemērā.
2. piemērs: summējiet lielākās 1., 2. un 3. vērtības no Excel diapazona.
Tātad, mēs izmantojam to pašu tabulu, ko izmantojām iepriekš minētajā piemērā. The diapazons joprojām ir C2: C13 un trīs galvenās vērtības, kuras mēs gribam summēt, ir 1., 2. un 3. lielākā vērtība diapazons.
| = SUMPRODUCT (LIELS (C2: C13, {1,2,3})) |
Tas atgriež tādu pašu rezultātu kā iepriekš, kas ir 696020. Ja vēlaties apkopot 2. un 4. augstākās vērtības, formula būs šāda:
| = SUMPRODUCT (LIELS (C2: C13, {2,4})) |
Un rezultāts būs 425360.
Iepriekš minētajā gadījumā funkciju SUMPRODUCT varat aizstāt ar funkciju SUM. Bet, ja vēlaties norādīt uz augstākajām vērtībām, jums būs jāizmanto CTRL+SHIF+ENTER, lai padarītu to par masīva formulu.
| {= SUMMA (LIELA (C2: C13, E2: E4))} |
Kā tas darbojas?
Funkcija LARGE pieņem galveno vērtību masīvu un atgriež šo augstāko vērtību masīvu. Šeit lielā funkcija atgriež vērtības {241540; 183820; 38740} kā augstākās 2., 4. un 6. vērtības. Tagad formula kļūst šāda:
| = SUM ({241540; 183820; 38740}) |
vai
| = SUMPRODUCT ({241540; 183820; 38740}) |
Tādējādi tiek apkopotas funkcijas LARGE atdotās vērtības.
Apkopojot plašu Excel augstāko vērtību diapazonu
Pieņemsim, ka vēlaties summēt vērtības, kas ir visaugstākās 10–2o vērtības. Šajā gadījumā iepriekš minētās metodes kļūs nervozākas un ilgākas. Labākais veids ir izmantot zemāk esošo vispārīgo formulu, lai apkopotu augstākās vērtības:
| = SUMPRODUCT (LARGE (diapazons, Rinda (NETIEŠS ("sākums: beigas"))))) |
diapazons:Diapazons ir diapazons, no kura vēlaties summēt augstākās N vērtības.
"sākums: beigas":Tas ir augstāko vērtību diapazona teksts, kuru vēlaties apkopot. Ja vēlaties vērtības no 3 līdz 6, tad sākums: beigas būs "3: 6".
Apskatīsim šo formulu darbībā:
3. piemērs. Summējiet 3 līdz 6 lielākās pārdošanas vērtības:
Tātad mums ir tāda pati tabula kā iepriekš minētajā piemērā. Vienīgais vaicājums ir atšķirīgs. Izmantojot iepriekš minēto vispārīgo formulu, mēs uzrakstām šādu formulu:
| = SUMPRODUCT (LARGE (C2: C13, ROW (NETIEŠS ("3: 6")))) |
Tādējādi tiek atgriezta vērtība 534040. Kas ir 3., 4., 5. un 6. lielākās vērtības summa diapazonā C2: C13.
Kā tas darbojas?
Pirmkārt, funkcija INDIRECT pārvērš tekstu "3: 6" par faktisko diapazonu 3: 6. Turpmāk funkcija ROW atgriež rindu skaitļu masīvu diapazonā 3: 6, kas būs {3,4,5,6}. Tālāk funkcija LARGE atgriež 3., 4., 5. un 6. lielāko vērtību no diapazona C2: C13. Visbeidzot, funkcija SUMPRODUCT apkopo šīs vērtības, un mēs iegūstam rezultātu.
| = SUMPRODUCT (LARGE (C2: C13, ROW (NETIEŠS ("3: 6"))))
=SUMPRODUCT (LIELS (C2: C13,{3,4,5,6})) = SUMPRODUCT ({212940;183820;98540;38740}) = 534040 |
Bonuss: apkopojiet augšējās N vērtības, izmantojot funkciju SEQUENCE
Funkcija SEQUENCE ir ieviesta programmā Excel 2019 un 365. Tā atgriež skaitļu secību. Mēs to varam izmantot, lai iegūtu augstākās N vērtības un pēc tam tās apkopotu.
Vispārējā formula:
| = SUMPRODUCT (LARGE (diapazons, SEQUENCE (skaitļu vērtības ,, [sākuma_numurs], [soļi])))) |
diapazons:Diapazons ir diapazons, no kura vēlaties summēt augstākās N vērtības.
num_values: Tas ir to augstāko vērtību skaits, kuras vēlaties summēt.
[sākuma_numurs]:Tas ir sērijas sākuma numurs. Tas nav obligāts. Ja to izlaidīsit, sērija sāksies no 1.
[soļi]: Tā ir atšķirība starp nākamo skaitli no pašreizējā. Pēc noklusējuma tas ir 1.
Ja mēs izmantosim šo vispārīgo formulu, lai iegūtu tādu pašu rezultātu kā iepriekšējā piemērā, formula būs šāda:
| = SUMMA (LARGE (C2: C13, SEQUENCE (4,, 3)))) |
Tas atgriezīs vērtību 534040.
Kā tas darbojas?
Tas ir vienkārši. Funkcija SEQUENCE atgriež 4 vērtību sēriju, kas sākas ar 3 ar intervālu 1. Tā atgriež masīvu {3; 4; 5; 6}. Funkcija LARGE atgriež lielākās atbilstošo skaitļu vērtības. Visbeidzot, funkcija SUM apkopo šo vērtību, un mēs iegūstam rezultātu 534040.
| = SUMMA (LARGE (C2: C13, SEQUENCE (4,,3)))
=SUMMA (LIELA (C2: C13,{3;4;5;6})) =SUM ({212940;183820;98540;38740}) =534040 |
Tātad, jā, puiši, šādā veidā jūs varat apkopot Excel 3, 5, 10,… N vērtības. Es centos būt izskaidrojošs. Es ceru, ka tas jums palīdz. Ja jums ir šaubas par šo tēmu vai kādu citu ar excel VBA saistītu tēmu, jautājiet komentāru sadaļā zemāk. Es bieži atbildu uz jautājumiem.
Summa, ja Excel ir lielāka par 0 | Lai summētu tikai pozitīvas vērtības, mums jāsummē vērtības, kas ir lielākas par 0. Lai to izdarītu, mēs izmantosim Excel SUMIF funkciju.
Kā apkopot pēdējo 30 dienu pārdošanas apjomu programmā Excel Dažreiz tas tiek apkopots arī standarta 30 dienu intervālā. Tādā gadījumā laba ideja ir formula, kas dinamiski parāda pēdējo 30 dienu summu. Šajā rakstā mēs iemācīsimies apkopot Excel pēdējo 30 dienu vērtības.
Kā apkopot pēdējās n kolonnas programmā Excel | Dažreiz tiek dots nosacījums, ti, kad mums no kolonnas jāiegūst pēdējo 5 vai n skaitļu summa. Jūs varat viegli atrisināt šo problēmu, izmantojot Excel funkcijas.
Kā summēt, saskaņojot rindu un kolonnu programmā Excel | Kā mēs summējam vērtības, kad jāsaskaņo kolonna un rinda. Šajā rakstā mēs uzzināsim, kā izveidot atbilstošu rindu un kolonnu summu.
Populāri raksti:
50 Excel saīsnes, lai palielinātu produktivitāti | Ātrāk izpildiet savu uzdevumu. Šie 50 īsceļi ļaus jums strādāt vēl ātrāk programmā Excel.
Kā izmantot Excel VLOOKUP funkciju| Šī ir viena no visbiežāk izmantotajām un populārākajām Excel funkcijām, kas tiek izmantota, lai meklētu vērtību no dažādiem diapazoniem un lapām.
Kā lietot Excel Funkcija COUNTIF| Saskaitiet vērtības ar nosacījumiem, izmantojot šo apbrīnojamo funkciju. Jums nav jāfiltrē dati, lai uzskaitītu noteiktu vērtību. Skaitītāja funkcija ir būtiska, lai sagatavotu informācijas paneli.
Kā lietot SUMIF funkciju programmā Excel | Šī ir vēl viena būtiska informācijas paneļa funkcija. Tas palīdz apkopot vērtības noteiktos apstākļos.
Populāri raksti:
50 Excel saīsnes, lai palielinātu produktivitāti | Ātrāk izpildiet savu uzdevumu. Šie 50 īsceļi ļaus jums strādāt vēl ātrāk programmā Excel.
Kā izmantot Excel VLOOKUP funkciju| Šī ir viena no visbiežāk izmantotajām un populārākajām Excel funkcijām, kas tiek izmantota, lai meklētu vērtību no dažādiem diapazoniem un lapām.
Kā lietot Excel Funkcija COUNTIF| Saskaitiet vērtības ar nosacījumiem, izmantojot šo apbrīnojamo funkciju. Jums nav jāfiltrē dati, lai uzskaitītu noteiktu vērtību. Skaitītāja funkcija ir būtiska, lai sagatavotu informācijas paneli.
Kā lietot SUMIF funkciju programmā Excel | Šī ir vēl viena būtiska informācijas paneļa funkcija. Tas palīdz apkopot vērtības noteiktos apstākļos.