U prethodnom članku naučili smo kako zbrojiti gornje ili donje N vrijednosti. U ovom članku pokušavamo zbrojiti gornju ili donju N vrijednost s kriterijem.
Zbir TOP N vrijednosti s kriterijima
Kako riješiti problem?
Za ovaj ćemo članak morati koristiti funkciju SUMPRODUCT. Sada ćemo od ovih funkcija napraviti formulu. Ovdje nam se daje raspon i kriterij. Moramo dobiti prvih 5 vrijednosti u rasponu i dobiti zbroj vrijednosti na temelju danih kriterija.
Opća formula:
= SUMPRODUCT (VELIKI ((popis = kriteriji) * (raspon), {1, 2,…., N}})
popis: popis kriterija
Kriteriji: kriteriji koji odgovaraju
domet: raspon vrijednosti
vrijednosti: brojevi odvojeni zarezima, na primjer, ako želite pronaći prve 3 vrijednosti, koristite {1, 2, 3}.
Primjer:
Ovdje imamo vrijednosti skupa podataka od A1: D50.
Prvo, moramo pronaći prvih pet vrijednosti pomoću funkcije LARGE koja odgovara gradu "Boston", a zatim se operacija zbroja izvodi nad tih 5 vrijednosti. Sada ćemo koristiti sljedeću formulu za dobivanje zbroja
Koristite formulu:
= SUMPRODUCT (VELIKI ((Grad = "Boston") * (količina), {1, 2, 3, 4, 5}))
Obrazloženje:
- City "Boston" odgovara spomenutom City rangeu. Ovo vraća niz vrijednosti true i false.
- Funkcija LARGE vraća prvih 5 numeričkih vrijednosti iz raspona količina i vraća niz u funkciju SUMPRODUCT.
= PODIZVOD {193, 149, 138, 134, 123}
- Funkcija SUMPRODUCT dobiva niz od top 5 vrijednosti, s nizom od top 5 brojeva koji vraća SUM tih brojeva.
Ovdje je raspon grada i količine dat kao imenovani raspon. Pritisnite Enter da biste dobili ZBIR prvih 5 brojeva.
Kao što možete vidjeti na gornjoj snimci, taj zbroj je 737. Zbroj vrijednosti 193 + 149 + 138 + 134 + 123 = 737.
Gore navedene vrijednosti možete provjeriti u skupu podataka pomoću opcije filtra excel. Primijenite filtar na zaglavlje Grad i količina i kliknite gumb sa strelicom na zaglavlju grada koje se pojavi. Slijedite dolje navedene korake.
Koraci:
- Odaberite ćeliju zaglavlja Grada. Primijenite filtar pomoću prečaca Ctrl + Shift + L
- Pritisnite strelicu koja se pojavljuje kao opcija filtra.
- Odaberite opciju (Odaberi sve).
- Odaberite samo grad Boston.
- Odmah odaberite zaglavlje količine.
- Poredajte popis od najvećeg do najmanjeg i možete vidjeti svih prvih 5 vrijednosti koje smo izračunali pomoću formule.
Kao što možete vidjeti na gornjem gifu svih 5 vrijednosti koje odgovaraju zadanim kriterijima. To također znači da formula radi dobro kako bi se brojile te vrijednosti
VELIKI N broj
Gore navedeni postupak koristi se za izračun zbroja nekoliko brojeva s vrha. Ali izračunati za n (veliki) broj vrijednosti u velikom rasponu.
Koristite formulu:
= SUMPRODUCT (VELIKI ((Grad = "Boston") * (količina), RED (INDIREKTAN ("1:10"))
Ovdje generiramo zbroj prvih 10 vrijednosti dobivanjem niza od 1 do 10 {1; 2; 3; 4; 5; 6; 7; 8; 9; 10} pomoću funkcija ROW & INDIRECT Excel.
Ovdje imamo zbroj prvih 10 brojeva koji rezultiraju 1147.
Zbir donjih N vrijednosti s kriterijima
Kako riješiti problem?
Za ovaj ćemo članak morati koristiti funkciju SUMPRODUCT. Sada ćemo od ovih funkcija napraviti formulu. Ovdje nam je dat raspon i moramo dolje 5 vrijednosti u rasponu i dobiti zbroj vrijednosti.
Opća formula:
{= SUM (MALO (AKO (Grad = "Boston", količina), {1, 2, 3, 4, 5}))}
Raspon: raspon vrijednosti
Vrijednosti: brojevi odvojeni zarezima, primjerice ako želite pronaći donje 3 vrijednosti, koristite {1, 2, 3}.
Primjer:
Sve ovo može biti zbunjujuće za razumijevanje. Dakle, isprobajmo ovu formulu tako da je pokrenemo na dolje prikazanom primjeru.
Ovdje imamo raspon vrijednosti od A1: D50.
Ovdje je raspon grada i količine dat korištenjem imenovanog alata Excel excel.
Prvo, moramo pronaći pet donjih vrijednosti pomoću funkcije SMALL koja odgovara kriterijima, a zatim se operacija zbrajanja izvodi nad tih 5 vrijednosti. Sada ćemo koristiti sljedeću formulu za dobivanje zbroja
Koristite formulu:
{= SUM (MALO (AKO (Grad = "Boston", količina), {1, 2, 3, 4, 5}))}
NEMOJTE ručno koristiti kovrčave zagrade. Kovrčavi aparatić primijenjen pomoću Ctrl + Shift + Enter umjesto pravednog Unesi.
Obrazloženje:
- SMALL funkcija s funkcijom IF vraća donjih 5 numeričkih vrijednosti koje odgovaraju Cityju "Boston" i vraća niz u funkciju SUM.
= ZBIR ({23, 27, 28, 28, 30}))
- Funkcija SUM dobiva niz donjih 5 vrijednosti, koji ima niz donjih 5 brojeva, vraća SUM onih brojeva koji se koriste s CTRL + SHIFT + ENTER.
Ovdje je raspon grada i količine dat kao imenovani raspon. Pritisnite Ctrl + Shift + Enter da biste dobili ZBOR donjih 5 brojeva jer je ovo formula niza.
Kao što možete vidjeti na gornjoj snimci, taj iznos je 136.
Gore navedeni postupak koristi se za izračun zbroja nekoliko brojeva s dna. Ali izračunati za n (veliki) broj vrijednosti u velikom rasponu.
Koristite formulu:
{ = SUM (MALO (AKO (Grad = "Boston", količina), RED (INDIREKTNO ("1:10")))) }
NEMOJTE ručno koristiti zavojite zagrade. Koristite Ctrl + Shift + Enter umjesto Enter.
Ovdje generiramo zbroj donjih 10 vrijednosti dobivanjem niza od 1 do 10 {1; 2; 3; 4; 5; 6; 7; 8; 9; 10} pomoću funkcija ROW & INDIRECT Excel.
Ovdje imamo zbroj donjih 10 brojeva koji će rezultirati 155.
Evo nekoliko opservacijskih napomena prikazanih u nastavku.
Bilješke:
- Formula radi samo s brojevima.
- Formula funkcionira samo ako u tablici za pretraživanje nema duplikata
- Funkcija SUMPRODUCT smatra numeričke vrijednosti (poput teksta abc) i vrijednosti pogrešaka (poput #NUM!, #NULL!) Kao null vrijednosti.
- Funkcija SUMPRODUCT logičku vrijednost TRUE smatra 1, a False 0.
- Polje argumenata mora biti iste duljine kao i funkcija.
Nadamo se da je ovaj članak o tome kako vratiti zbroj prvih 5 vrijednosti ili donjih 5 vrijednosti s kriterijima u Excelu objašnjen. Ovdje pronađite više članaka o funkcijama SUMPRODUCT -a. Podijelite svoj upit ispod u okvir za komentare. Mi ćemo vam pomoći.
Ako vam se sviđaju naši blogovi, podijelite ih sa svojim prijateljima na Facebooku. Također nas možete pratiti na Twitteru i Facebooku. Voljeli bismo čuti vaše mišljenje, javite nam kako možemo poboljšati, nadopuniti ili inovirati svoj rad i učiniti ga boljim za vas. Pišite nam na web stranici e -pošte
Kako koristiti funkciju SUMPRODUCT u Excelu: Vraća SUM nakon množenja vrijednosti u više niza u Excelu.
ZBIRAJTE ako je datum između : Vraća ZBOR vrijednosti između zadanih datuma ili razdoblja u Excelu.
Zbroj ako je datum veći od navedenog datuma: Vraća ZBOR vrijednosti nakon zadanog datuma ili razdoblja u Excelu.
2 načina zbrajanja po mjesecima u Excelu: Vraća ZBOR vrijednosti unutar određenog određenog mjeseca u Excelu.
Kako zbrojiti više stupaca s uvjetom: Vraća ZBOR vrijednosti u više stupaca sa uvjetom u excelu
Kako koristiti zamjenske znakove u Excelu : Brojanje ćelija koje se podudaraju s izrazima pomoću zamjenskih znakova u Excelu
Popularni članci
50 Excel prečac za povećanje vaše produktivnosti
Uredite padajući popis
Apsolutna referenca u Excelu
Ako s uvjetnim oblikovanjem
Ako sa zamjenskim znakovima
Pogled prema datumu
Pretvorite inče u stope i inče u Excelu 2016
Pridružite se imenu i prezimenu u excelu
Prebrojite ćelije koje odgovaraju A ili B