Et problem, der kommer meget i Excel, er at tælle eller opsummere baseret på flere ELLER-forhold. For eksempel skal du måske analysere data og tælle ordrer i Seattle eller Denver for varer, der er røde, blå eller grønne? Dette kan være overraskende vanskelig, så det er naturligvis en god udfordring!
Udfordringen
Dataene nedenfor repræsenterer ordrer, en ordre pr. Række. Der er tre separate udfordringer.
Hvilke formler i F9, G9 og H9 tæller ordrer korrekt med følgende betingelser:
- F9 - T-shirt eller hættetrøje
- G9 - (Tshirt eller hættetrøje) og (Rød, Blå eller Grøn)
- H9 - (Tshirt eller hættetrøje) og (Rød, Blå eller Grøn) og (Denver eller Seattle)
Den grønne skygge anvendes med betinget formatering og indikerer matchende værdier for hvert sæt OR-kriterier i hver kolonne.
For din bekvemmelighed er følgende navngivne områder tilgængelige:
vare = B3: B16
farve = C3: C16
by = D3: D16
Arbejdsarket er vedhæftet. Efterlad dine svar nedenfor som kommentarer!
Svar (klik for at udvide)Min løsning bruger SUMPRODUCT med ISNUMBER og MATCH sådan:
=SUMPRODUCT( ISNUMBER(MATCH(item,("Tshirt","Hoodie"),0))* ISNUMBER(MATCH(color,("Red","Blue","Green"),0))* ISNUMBER(MATCH(city,("Denver","Seattle"),0)) )
Hvilket tæller ordrer hvor …
- Varen er (Tshirt eller hættetrøje) og
- Farven er (rød, blå eller grøn) og
- City er (Denver eller Seattle)
Flere mennesker foreslog også den samme tilgang. Jeg kan godt lide denne struktur, fordi den let skaleres for at håndtere flere kriterier og fungerer også med cellereferencer (i stedet for hårdkodede værdier). Med cellereferencer er formlen i H9:
=SUMPRODUCT( ISNUMBER(MATCH(item,F3:F4,0))* ISNUMBER(MATCH(color,G3:G5,0))* ISNUMBER(MATCH(city,H3:H4,0)) )
Nøglen til denne formel er ISNUMBER + MATCH-konstruktionen. MATCH er opsat "baglæns" - opslagsværdier kommer fra dataene, og kriterier bruges til arrayet. Resultatet er et enkelt kolonnearray, hver gang MATCH bruges. Denne matrix indeholder enten # N / A-fejl (ingen match) eller tal (match), så ISNUMBER bruges til at konvertere til de boolske værdier SAND og FALSK. Funktionen med at multiplicere arrays sammen tvinger de SANDE FALSE-værdier til 1s og 0s, og den endelige matrix inde i SUMPRODUCT indeholder 1s, hvor rækker opfylder kriterier. SUMPRODUCT summerer derefter arrayet og returnerer resultatet.
