Ett problem som kommer upp mycket i Excel är att räkna eller summera baserat på flera ELLER villkor. Till exempel, kanske du behöver analysera data och räkna order i Seattle eller Denver, för artiklar som är röda, blåa eller gröna? Detta kan vara förvånansvärt knepigt, så det är naturligtvis en bra utmaning!
Utmaningen
Uppgifterna nedan representerar order, en order per rad. Det finns tre separata utmaningar.
Vilka formler i F9, G9 och H9 räknar ordrar korrekt med följande villkor:
- F9 - T-shirt eller luvtröja
- G9 - (T-shirt eller hoodie) och (röd, blå eller grön)
- H9 - (T-shirt eller luvtröja) och (röd, blå eller grön) och (Denver eller Seattle)
Den gröna skuggningen appliceras med villkorlig formatering och anger matchande värden för varje uppsättning ELLER-kriterier i varje kolumn.
För din bekvämlighet finns följande namngivna områden tillgängliga:
artikel = B3: B16
färg = C3: C16
stad = D3: D16
Kalkylbladet bifogas. Lämna dina svar nedan som kommentarer!
Svar (klicka för att expandera)Min lösning använder SUMPRODUCT med ISNUMBER och MATCH så här:
=SUMPRODUCT( ISNUMBER(MATCH(item,("Tshirt","Hoodie"),0))* ISNUMBER(MATCH(color,("Red","Blue","Green"),0))* ISNUMBER(MATCH(city,("Denver","Seattle"),0)) )
Som kommer att räkna order där …
- Objektet är (T-shirt eller luvtröja) och
- Färgen är (röd, blå eller grön) och
- City is (Denver eller Seattle)
Flera personer föreslog också samma tillvägagångssätt. Jag gillar den här strukturen eftersom den enkelt skalas för att hantera fler kriterier, och fungerar också med cellreferenser (istället för hårdkodade värden). Med cellreferenser är formeln i H9:
=SUMPRODUCT( ISNUMBER(MATCH(item,F3:F4,0))* ISNUMBER(MATCH(color,G3:G5,0))* ISNUMBER(MATCH(city,H3:H4,0)) )
Nyckeln till denna formel är ISNUMBER + MATCH-konstruktionen. MATCH är inställt "bakåt" - uppslagsvärden kommer från data och kriterier används för matrisen. Resultatet är en enda kolumnmatris varje gång MATCH används. Denna matris innehåller antingen # N / A-fel (ingen matchning) eller siffror (matchning), så ISNUMBER används för att konvertera till de booleska värdena SANT och FALSKT. Funktionen att multiplicera matriserna tillsammans tvingar de Sanna FALSKA värdena till 1s och 0s, och den slutliga matrisen i SUMPRODUCT innehåller 1s där rader uppfyller kriterierna. SUMPRODUCT summerar sedan matrisen och returnerar resultatet.
