Een probleem dat veel voorkomt in Excel is tellen of optellen op basis van meerdere OF-voorwaarden. Misschien moet u bijvoorbeeld gegevens analyseren en bestellingen tellen in Seattle of Denver, voor artikelen die rood, blauw of groen zijn? Dit kan verrassend lastig zijn, dus het is natuurlijk een goede uitdaging!
De uitdaging
De onderstaande gegevens vertegenwoordigen bestellingen, één bestelling per rij. Er zijn drie afzonderlijke uitdagingen.
Welke formules in F9, G9 en H9 zullen bestellingen correct tellen met de volgende voorwaarden:
- F9 - T-shirt of hoodie
- G9 - (T-shirt of hoodie) en (rood, blauw of groen)
- H9 - (T-shirt of hoodie) en (rood, blauw of groen) en (Denver of Seattle)
De groene arcering wordt toegepast met voorwaardelijke opmaak en geeft overeenkomende waarden aan voor elke set OF-criteria in elke kolom.
Voor uw gemak zijn de volgende genoemde bereiken beschikbaar:
item = B3: B16
kleur = C3: C16
stad = D3: D16
Het werkblad is bijgevoegd. Laat uw antwoorden hieronder achter als opmerkingen!
Antwoord (klik om uit te vouwen)Mijn oplossing gebruikt SUMPRODUCT met ISNUMBER en MATCH als volgt:
=SUMPRODUCT( ISNUMBER(MATCH(item,("Tshirt","Hoodie"),0))* ISNUMBER(MATCH(color,("Red","Blue","Green"),0))* ISNUMBER(MATCH(city,("Denver","Seattle"),0)) )
Dat telt bestellingen waar …
- Item is (T-shirt of Hoodie) en
- Kleur is (rood, blauw of groen) en
- Stad is (Denver of Seattle)
Meerdere mensen stelden dezelfde aanpak voor. Ik vind deze structuur leuk omdat deze gemakkelijk kan worden geschaald om meer criteria te verwerken en ook werkt met celverwijzingen (in plaats van hardgecodeerde waarden). Met celverwijzingen is de formule in H9:
=SUMPRODUCT( ISNUMBER(MATCH(item,F3:F4,0))* ISNUMBER(MATCH(color,G3:G5,0))* ISNUMBER(MATCH(city,H3:H4,0)) )
De sleutel tot deze formule is de ISNUMBER + MATCH-constructie. VERGELIJKEN is "achterstevoren" ingesteld - opzoekwaarden komen uit de gegevens en criteria worden gebruikt voor de array. Het resultaat is een matrix met één kolom, elke keer dat MATCH wordt gebruikt. Deze array bevat ofwel # N / A-fouten (geen overeenkomst) of getallen (overeenkomst), dus ISNUMBER wordt gebruikt om de booleaanse waarden TRUE en FALSE om te zetten. De bewerking van het samen vermenigvuldigen van de arrays dwingt de TRUE FALSE-waarden tot 1s en 0s, en de laatste array binnen SUMPRODUCT bevat 1s waar rijen voldoen aan criteria. SOMPRODUCT telt vervolgens de matrix op en retourneert het resultaat.
