Ena težava, ki se v Excelu zelo pojavlja, je štetje ali seštevanje na podlagi več pogojev ALI. Na primer, morda morate analizirati podatke in šteti naročila v Seattlu ali Denverju za elemente, ki so rdeči, modri ali zeleni? To je lahko presenetljivo zapleteno, zato je seveda dober izziv!
Izziv
Spodnji podatki predstavljajo naročila, eno naročilo na vrstico. Obstajajo trije ločeni izzivi.
Katere formule v F9, G9 in H9 bodo pravilno štele naročila pod naslednjimi pogoji:
- F9 - majica s kratkimi rokavi ali s kapuco
- G9 - (majica s kapuco) in (rdeča, modra ali zelena)
- H9 - (majica s kapuco) in (rdeča, modra ali zelena) in (Denver ali Seattle)
Zeleno senčenje se uporabi s pogojnim oblikovanjem in označuje ujemajoče se vrednosti za vsak niz meril ALI v vsakem stolpcu.
Za vaše udobje so na voljo naslednji imenovani obsegi:
postavka = B3: B16
barva = C3: C16
mesto = D3: D16
Delovni list je priložen. Odgovore pustite spodaj kot komentarje!
Odgovor (kliknite za razširitev)Moja rešitev uporablja SUMPRODUCT z ISNUMBER in MATCH, kot je ta:
=SUMPRODUCT( ISNUMBER(MATCH(item,("Tshirt","Hoodie"),0))* ISNUMBER(MATCH(color,("Red","Blue","Green"),0))* ISNUMBER(MATCH(city,("Denver","Seattle"),0)) )
Ki bodo šteli naročila, kjer …
- Artikel je (majica ali kapuca) in
- Barva je (rdeča, modra ali zelena) in
- Mesto je (Denver ali Seattle)
Tudi več ljudi je predlagalo enak pristop. Ta struktura mi je všeč, ker je enostavno prilagoditi več kriterijev in deluje tudi s sklici na celice (namesto s trdo kodiranimi vrednostmi). Pri referencah celic je formula v H9:
=SUMPRODUCT( ISNUMBER(MATCH(item,F3:F4,0))* ISNUMBER(MATCH(color,G3:G5,0))* ISNUMBER(MATCH(city,H3:H4,0)) )
Ključ te formule je konstrukcija ISNUMBER + MATCH. MATCH je nastavljen "nazaj" - iskalne vrednosti prihajajo iz podatkov, za matriko pa se uporabljajo merila. Rezultat je posamezna matrika stolpcev vsakič, ko se uporabi MATCH. Ta matrika vsebuje # N / A napak (brez ujemanja) ali številk (ujemanje), zato se ISNUMBER uporablja za pretvorbo v logične vrednosti TRUE in FALSE. Operacija množenja nizov prisili vrednosti TRUE FALSE na 1s in 0s, končno polje znotraj SUMPRODUCT pa vsebuje 1s, kjer vrstice izpolnjujejo merila. Nato SUMPRODUCT sešteje matriko in vrne rezultat.
