Et problem som kommer mye i Excel er å telle eller summere basert på flere ELLER forhold. For eksempel må du kanskje analysere data og telle ordrer i Seattle eller Denver, for varer som er røde, blå eller grønne? Dette kan være overraskende vanskelig, så det er naturlig nok en god utfordring!
Utfordringen
Dataene nedenfor representerer ordrer, en ordre per rad. Det er tre separate utfordringer.
Hvilke formler i F9, G9 og H9 teller ordrer riktig med følgende betingelser:
- F9 - T-skjorte eller hettegenser
- G9 - (T-skjorte eller hettegenser) og (Rød, Blå eller Grønn)
- H9 - (T-skjorte eller hettegenser) og (Rød, Blå eller Grønn) og (Denver eller Seattle)
Den grønne skyggen brukes med betinget formatering og indikerer samsvarende verdier for hvert sett med ELLER-kriterier i hver kolonne.
For din bekvemmelighet er følgende navngitte områder tilgjengelige:
vare = B3: B16
farge = C3: C16
by = D3: D16
Regnearket er vedlagt. Legg igjen svarene nedenfor som kommentarer!
Svar (klikk for å utvide)Min løsning bruker SUMPRODUCT med ISNUMBER og MATCH slik:
=SUMPRODUCT( ISNUMBER(MATCH(item,("Tshirt","Hoodie"),0))* ISNUMBER(MATCH(color,("Red","Blue","Green"),0))* ISNUMBER(MATCH(city,("Denver","Seattle"),0)) )
Som vil telle ordrer hvor …
- Varen er (T-skjorte eller hettegenser) og
- Farge er (rød, blå eller grønn) og
- City is (Denver eller Seattle)
Flere personer foreslo også den samme tilnærmingen. Jeg liker denne strukturen fordi den skalerer lett for å håndtere flere kriterier, og fungerer også med cellereferanser (i stedet for hardkodede verdier). Med cellereferanser er formelen i H9:
=SUMPRODUCT( ISNUMBER(MATCH(item,F3:F4,0))* ISNUMBER(MATCH(color,G3:G5,0))* ISNUMBER(MATCH(city,H3:H4,0)) )
Nøkkelen til denne formelen er ISNUMBER + MATCH-konstruksjonen. MATCH er satt opp "bakover" - oppslagsverdier kommer fra dataene, og kriterier brukes for matrisen. Resultatet er en enkelt kolonnematrise hver gang MATCH brukes. Denne matrisen inneholder enten # N / A-feil (ingen samsvar) eller tall (samsvar), så ISNUMBER brukes til å konvertere til de boolske verdiene SANN og FALSK. Operasjonen med å multiplisere matriser sammen tvinger de SANNE FALSKE verdiene til 1s og 0s, og den endelige matrisen i SUMPRODUCT inneholder 1s der radene oppfyller kriteriene. SUMPRODUCT summerer deretter matrisen og returnerer resultatet.
