Jedan od problema koji se u Excelu često pojavljuje je brojanje ili zbrajanje na temelju više uvjeta ILI. Na primjer, možda trebate analizirati podatke i brojati narudžbe u Seattlu ili Denveru za crvene, plave ili zelene stavke? To može biti iznenađujuće zeznuto, pa prirodno predstavlja dobar izazov!
Izazov
Podaci u nastavku predstavljaju narudžbe, jednu narudžbu u retku. Tri su zasebna izazova.
Koje će formule u F9, G9 i H9 ispravno brojati narudžbe uz sljedeće uvjete:
- F9 - Majica ili kapuljača
- G9 - (majica s kapuljačom) i (crvena, plava ili zelena)
- H9 - (majica s kapuljačom) i (crvena, plava ili zelena) i (Denver ili Seattle)
Zeleno sjenčanje primjenjuje se s uvjetnim oblikovanjem i označava odgovarajuće vrijednosti za svaki skup OR kriterija u svakom stupcu.
Za vašu udobnost dostupni su sljedeći imenovani rasponi:
stavka = B3: B16
boja = C3: C16
grad = D3: D16
Radni list je u privitku. Odgovore ostavite u nastavku kao komentare!
Odgovor (kliknite za proširenje)Moje rješenje koristi SUMPRODUCT s ISNUMBER i MATCH ovako:
=SUMPRODUCT( ISNUMBER(MATCH(item,("Tshirt","Hoodie"),0))* ISNUMBER(MATCH(color,("Red","Blue","Green"),0))* ISNUMBER(MATCH(city,("Denver","Seattle"),0)) )
Koji će brojati narudžbe gdje …
- Predmet je (majica ili kapuljača) i
- Boja je (crvena, plava ili zelena) i
- Grad je (Denver ili Seattle)
Nekoliko je ljudi također predložilo isti pristup. Sviđa mi se ova struktura jer se lako skalira radi obrade više kriterija, a također radi i sa referencama na ćelije (umjesto s teško kodiranim vrijednostima). S referencama na stanice, formula u H9 je:
=SUMPRODUCT( ISNUMBER(MATCH(item,F3:F4,0))* ISNUMBER(MATCH(color,G3:G5,0))* ISNUMBER(MATCH(city,H3:H4,0)) )
Ključ ove formule je konstrukcija ISUMBER + MATCH. MATCH se postavlja "unatrag" - vrijednosti pretraživanja dolaze iz podataka, a za niz se koriste kriteriji. Rezultat je jedan niz stupaca svaki put kada se upotrijebi MATCH. Ovaj niz sadrži ili # N / A pogrešaka (nema podudaranja) ili brojeva (podudaranje), pa se ISNUMBER koristi za pretvaranje u logičke vrijednosti TRUE i FALSE. Operacija množenja nizova prisiljava vrijednosti TRUE FALSE na 1s i 0s, a konačni niz unutar SUMPRODUCT sadrži 1s gdje redovi zadovoljavaju kriterije. SUMPRODUCT zatim zbraja niz i vraća rezultat.
