Az egyik probléma, amely sokat felmerül az Excelben, az a számolás vagy összegzés több VAGY feltétel alapján. Például elemeznie kell az adatokat, és meg kell számlálnia a Seattle-i vagy Denver-i megrendeléseket a piros, kék vagy zöld termékek esetében? Ez meglepően trükkös lehet, így természetesen jó kihívást jelent!
A kihívás
Az alábbi adatok soronként egy rendelést jelentenek. Három különálló kihívás van.
Milyen képletek az F9, G9 és H9 formátumokban helyesen számolják a megrendeléseket az alábbi feltételekkel:
- F9 - póló vagy kapucnis
- G9 - (póló vagy kapucnis) és (piros, kék vagy zöld)
- H9 - (póló vagy kapucnis) és (piros, kék vagy zöld) és (Denver vagy Seattle)
A zöld árnyékolás feltételes formázással kerül alkalmazásra, és minden oszlopban feltünteti a VAGY kritériumkészlet megfelelő értékeit.
Az Ön kényelme érdekében a következő megnevezett tartományok állnak rendelkezésre:
elem = B3: B16
szín = C3: C16
város = D3: D16
A munkalap csatolva van. Hagyja meg válaszait alább kommentként!
Válasz (kattintson a kibontáshoz)Megoldásom a SUMPRODUCT-ot az ISNUMBER és a MATCH használatával használja:
=SUMPRODUCT( ISNUMBER(MATCH(item,("Tshirt","Hoodie"),0))* ISNUMBER(MATCH(color,("Red","Blue","Green"),0))* ISNUMBER(MATCH(city,("Denver","Seattle"),0)) )
Ami a megrendeléseket számolja, ahol…
- A termék (póló vagy kapucnis) és
- A szín: (piros, kék vagy zöld) és
- Város (Denver vagy Seattle)
Többen is ugyanazt a megközelítést javasolták. Szeretem ezt a struktúrát, mert könnyen skálázható több kritérium kezeléséhez, és cellahivatkozásokkal is működik (a keményen kódolt értékek helyett). Sejtreferenciákkal a H9 képlete a következő:
=SUMPRODUCT( ISNUMBER(MATCH(item,F3:F4,0))* ISNUMBER(MATCH(color,G3:G5,0))* ISNUMBER(MATCH(city,H3:H4,0)) )
Ennek a képletnek a kulcsa az ISNUMBER + MATCH konstrukció. A MATCH "visszafelé" van beállítva - a keresési értékek az adatokból származnak, és a tömbre kritériumokat használnak. Az eredmény egyetlen oszlop tömb, minden egyes MATCH használatakor. Ez a tömb vagy # N / A hibát (nincs egyezés), vagy számokat (egyezés) tartalmaz, ezért az ISNUMBER számot használják a TRUE és FALSE logikai értékekké konvertálásra. A tömbök összeszorításának művelete az IGAZ HAMIS értékeket 1-re és 0-ra kényszeríti, és a SUMPRODUCT belsejében található utolsó tömb 1-et tartalmaz, ahol a sorok megfelelnek a kritériumoknak. Ezután a SUMPRODUCT összegzi a tömböt és visszaadja az eredményt.
