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 članku ćemo vidjeti kako možemo koristiti ovu funkciju za brojanje vrijednosti s više kriterija ILI.
Opća formula sumarnog proizvoda za brojanje s više ili kriterijima
| = SUMPRODUCT (-(((kriterij1)+(kriterij2)+… )>0) |
Kriterij 1: Ovo su svi kriteriji koji vraćaju 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.
Primjer: Broj korisnika ako je trgovački kôd ili godinaUtakmice Korištenje 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, ovdje 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.
Kako radi?
Pa pogledajmo kako se formula rješava korak po korak, a zatim ću raspravljati o tome kako funkcionira.
| =PODUZIMAK(-(((Kod = I3)+(Godina = K3))> 0)) |
| 1=>PODUZIMAK(-(({TRUE; FALSE; TRUE; TRUE; TRUE; TRUE;…}+{FALSE; FALSE; FALSE; TRUE; TRUE;…})> 0)) |
| 2=>PODUZIMAK(--(({1;0;1;2;2;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})>0)) |
| 3=>PODUZIMAK(-({TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE;…}) |
| 4=>PODUZIMAK({1;0;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0}) |
| 5=>7 |
U prvom koraku, vrijednost I3 ("INKA") uspoređuje se sa svakom ćelijom u rasponu kodova. Ovo vraća niz TRUE i FALSE. ISTINA za svaku utakmicu. Kako bih uštedio prostor, nisam prikazao sve TRUE-FALSE. Slično, vrijednost K3 (2016) usklađena je sa svakom ćelijom u rasponu godina.
U sljedećem koraku dodajemo ova dva niza koji rezultiraju novim nizom numeričkih vrijednosti. Kao što možda znate, TRUE se u Excelu tretira kao 1, a FALSE kao 0. Dakle, kad se dodaju TRUE i TRUE dobivamo 2, a ostalo možete razumjeti.
U sljedećem koraku provjeravamo koja je vrijednost veća od 0 u nizu. Ovo opet pretvara niz u pravi lažni niz. Za svaku vrijednost 0 koju dobijemo, False i rest se pretvaraju u true. Sada je broj TRUE vrijednosti u nizu naš odgovor. Ali kako ih računamo? Evo kako.
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.
U posljednjem koraku SUMPRODUCT sažima ovaj niz i dobivamo naš odgovor kao 7.
Dodavanje više ili kriterija za brojanje pomoću SUMPRODUCT -a
Dakle, 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 Kodirati domet. Recimo da želite brojati "INKB". Pa kako to učiniti? 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 3 postavke kriterija ILI bit će:
| = SUMPRODUCT (-(((Kod = I2: J2)+(Godina = I3: J3))> 0)) |
Ovo je formula SUMPRODUCT za brojanje s više kriterija kada je više kriterija iz jednog raspona zapisano u niz.
Ovo 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 je došlo do ove pogreške jer će ovaj članak biti predug. 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.
Pa da kolega, nadam se da sam bio dovoljno jasan i da je imao smisla. Nadam se da služi vašoj svrsi što ste ovdje. Ako ova formula nije riješila vaš problem, javite mi svoje zahtjeve u donjem odjeljku komentara. Bit ću više nego sretan što ću vam pomoći na bilo koji način. Možete spomenuti sve sumnje vezane za Excel/VBA. Do tada nastavite učiti, nastavite napredovati.
Kako koristiti funkciju SUMPRODUCT u Excelu: Vraća SUM nakon množenja vrijednosti u više niza u Excelu. Ova se funkcija može koristiti za obavljanje više zadataka. Ovo je jedna od najsvestranijih funkcija.
COUNTIFS s dinamičkim rasponom kriterija : Za brojanje s rasponom dinamičkih kriterija jednostavno koristimo funkciju INDIRECT. Ova funkcija može
RAČUNI S ILI Za više kriterija : Brojanje ćelija koje imaju više kriterija koji se podudaraju pomoću funkcije ILI. Za stavljanje logike OR u funkciju COUNTIFS nećete morati koristiti funkciju OR.
Korištenje IF s funkcijama AND / OR u programu Microsoft Excel : Ove logičke funkcije koriste se za izračun više kriterija. Uz IF funkcije OR i AND koriste se za uključivanje ili isključivanje podudaranja.
Kako koristiti funkciju ILI u programu Microsoft Excel : Funkcija se koristi za uključivanje svih TRUE vrijednosti u više kriterija.
Kako brojati ćelije koje sadrže ovo ili ono u Excelu u Excelu : Za ćelije koje sadrže ovo ili ono, možemo koristiti funkciju SUMPRODUCT. Evo kako izračunate te izračune.
Popularni članci:
50 Excel prečaca za povećanje vaše produktivnosti | Budite brži u izvršavanju svojih zadataka. Ovih 50 prečaca učinit će da radite još brže na Excelu.
Kako koristiti Excel VLOOKUP funkciju| 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 Excel COUNTIF funkcija| 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.
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.