Kao što sam spomenuo u mnogim svojim blogovima da je SUMPRODUCT vrlo svestrana funkcija i da se može koristiti u više svrha. U ovom ćemo članku vidjeti kako možemo koristiti ovu funkciju za brojanje vrijednosti s višestrukim ili kriterijima.
Opća formula sumarnog proizvoda za brojanje s više ili kriterijima
| = SUMPRODUCT (-(((kriterij1)+(kriterij2)+…)> 0) |
Kriteriji 1: Ovo je bilo koji kriterij koji vraća niz TRUE i FALSE.
Kriteriji 2: Ovo je sljedeći kriterij koji želite provjeriti. Slično, možete imati onoliko kriterija koliko želite.
Gore navedena opća formula često se mijenja kako bi zadovoljila zahtjeve za brojanje s više kriterija ILI. Ali osnovna formula je ova. Prvo ćemo kroz primjer vidjeti kako to funkcionira, a nakon toga ćemo razgovarati o drugim scenarijima u kojima ćete morati malo izmijeniti ovu formulu.
Problem:
Cijenu u ćeliji treba točno podijeliti na 5 sličnih plaćanja. Kada jednostavno podijelimo cijenu od 88,04 USD na 5 i zaokružimo rezultat na dvije decimale, dobivamo pojedinačna plaćanja od 17,61 USD. Međutim, to bi dodalo ukupnu uplatu od 88,05 USD, što nije točan iznos s kojim počinjemo. Stoga želimo izračunati 5 sličnih brojeva koji zbrajaju ukupni iznos.
Riješenje:
Prvo izračunajte prvu uplatu dijeljenjem cijene sa 5 i zaokruživanjem rezultata.
Upotrijebite funkciju ROUND u sljedećoj formuli:
= OKRUGLA (B1/5,2)
Korak 2:
Izračunajte svaku preostalu uplatu.
Koristite funkcije ROUND, SUM i ROW u sljedećoj formuli:
= ROUND (($ B $ 1-SUM ($ B $ 2: B2))/(5-ROW ()+ROW ($ B $ 2)), 2)
Primjer:
Brojanje korisnika ako se kôd trgovca podudara ili godina korištenjem SUMPRODUCT -a
Dakle, ovdje imamo skup podataka prodavača. Podaci sadrže mnogo stupaca. Ono što moramo učiniti je prebrojati broj korisnika koji imaju kôd "INKA" ili godina je "2016". Pobrinite se da se, ako netko ima oboje (kôd kao "inka" i 2016. godina), računa kao 1.
Dakle, čujte da imamo dva kriterija. Koristimo gore spomenutu formulu SUMPRODUCT:
| = SUMPRODUCT (-(((Kod = I3)+(Godina = K3))> 0)) |
Ovdje su kod i godina imenovani rasponi. Vraća se 7.
U podacima imamo 5 zapisa INKA koda i 4 zapisa 2016. godine. No 2 zapisa imaju "INKA" i 2016. kao šifru i godinu. Ti se zapisi broje kao 1. I ovako dobivamo 7.
Pogledajmo kako to funkcionira?
Pa pogledajmo kako se formula rješava korak po korak, a zatim ću raspravljati o tome kako funkcionira.
-
- = PODUZIMAK(-(((Kod = I3)+(Godina = K3))> 0))
- PODUZIMAK(-(({TRUE; FALSE; TRUE; TRUE; TRUE; TRUE;…}+{FALSE; FALSE; FALSE; TRUE; TRUE;…})> 0))
- PODUZIMAK(--(({1;0;1;2;2;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})>0))
- PODUZIMAK(-({TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE;…})
- PODUZIMAK({1;0;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})
7
Dvostruki negativni (-) znakovi koriste se za pretvaranje logičkih vrijednosti u 1s i 0s. Dakle, svaka TRUE vrijednost u nizu se pretvara u 1, a FALSE u 0.
Dodavanje više ili kriterija za brojanje pomoću SUMPRODUCT -a, pa ako trebate dodati više ili kriterije za brojanje, možete samo dodati kriterije pomoću znaka + u funkciju.
Na primjer, ako gornjoj formuli želite dodati još jedan kriterij tako da dodaje broj zaposlenih koji su prodali više od 5 proizvoda. Formula SUMPRODUCT jednostavno će izgledati ovako:
| = SUMPRODUCT (-(((Kod = I3)+(Godina = K3)+(Prodaja> 5))> 0)) |
Jednostavan! zar ne?
Ali recimo da želite imati dva kriterija iz raspona koda. Recimo da želite brojati "INKB". Kako onda to radite? Jedna metoda koristi gornju tehniku, ali to bi se ponavljalo. Recimo da želim dodati još 10 kriterija iz istog raspona. U takvim slučajevima ova tehnika nije toliko pametna za brojanje pomoću SUMPRODUCT -a.
Recimo da imamo podatke uređene ovako.
Kodovi kriterija nalaze se u jednom retku I2: J2. Ovdje je važno raspored podataka. Formula SUMPRODUCT za postavljanje broja 3 kriterija ILI bit će:
| = SUMPRODUCT (-(((Kod = I2: J2)+(Godina = I3: J3))> 0)) |
Ovo je formula SUMPRODUCT za brojanje s više kriterija kada je u niz upisano više kriterija iz jednog raspona.
Gornja formula vraća točan odgovor koji je 10.
Ako bilo koju godinu upišete u J3, formula će dodati i taj broj.
To se koristi kada su kriteriji u jednom redu. Hoće li funkcionirati ako su kriteriji u jednom stupcu za isti raspon? Ne. Neće.
U ovom primjeru imamo više kodova za brojanje, ali ti su tipovi kodova zapisani u jednom stupcu. Kada koristimo gornju formulu SUMPRODUCT, dobivamo grešku #N/A. Nećemo ulaziti u to kako dolazi do ove pogreške jer ovaj članak čini predugim. Pogledajmo kako možemo uspjeti.
Da bi ova formula funkcionirala, morate umotati kriterij koda u funkciju TRANSPOSE. Time će formula djelovati.
| = SUMPRODUCT (-(((Kod = TRANSPOZA (H3: H4))+(Godina = TRANSPOZA (I3: I4)))> 0)) |
Ovo je formula za brojanje s višestrukim ili uvjetima u istom rasponu kada su kriteriji navedeni u stupcu.
Ovdje su sve napomene o primjeni formule.
Bilješke:
- Formula radi i za tekst i za brojeve.
- Vrijednosti traženja mogu se dati kao referenca ćelije ili izravno pomoću znaka navoda (") u formuli kao argumenata.
Nadam se da ste razumjeli Kako podijeliti iznos na jednaka plaćanja izbjegavajući pogreške podjele / zaokruživanja u Excelu. Ovdje istražite više članaka o vrijednosti pretraživanja programa Excel i funkcijama programa Excel 2019. 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.
Upotrijebite INDEX i MATCH za traženje vrijednosti : INDEX & MATCH funkcija za traženje vrijednosti prema potrebi.
Raspon SUM s INDEX -om u Excelu : Upotrijebite INDEX funkciju za traženje SUM -a vrijednosti prema potrebi.
Kako koristiti funkciju INDEKS u Excelu : Pronađite INDEX niza pomoću funkcije INDEX objašnjene na primjeru.
Kako koristiti funkciju MATCH u Excelu : Pronađite MATCH u nizu koristeći vrijednost INDEX unutar funkcije MATCH objašnjeno na primjeru.
Kako koristiti funkciju LOOKUP u Excelu : Pronađite vrijednost pretraživanja u nizu pomoću funkcije LOOKUP objašnjene na primjeru.
Popularni članci:
Kako koristiti IF funkciju u Excelu : IF izraz u Excelu provjerava uvjet i vraća određenu vrijednost ako je uvjet TRUE ili vraća drugu određenu vrijednost ako je FALSE.
Kako koristiti funkciju VLOOKUP u Excelu : Ovo je jedna od najčešće korištenih i popularnih funkcija programa Excel koja se koristi za traženje vrijednosti iz različitih raspona i listova.
Kako koristiti funkciju SUMIF u Excelu : Ovo je još jedna bitna funkcija nadzorne ploče. To vam pomaže pri zbrajanju vrijednosti u posebnim uvjetima.
Kako koristiti funkciju COUNTIF u Excelu : Brojte vrijednosti s uvjetima pomoću ove nevjerojatne funkcije. Ne morate filtrirati svoje podatke da biste brojali određene vrijednosti. Funkcija Countif bitna je za pripremu vaše nadzorne ploče.