O problemă care apare mult în Excel este numărarea sau însumarea pe baza mai multor condiții SAU. De exemplu, poate că trebuie să analizați datele și să numărați comenzile din Seattle sau Denver, pentru articolele care sunt roșii, albastre sau verzi? Acest lucru poate fi surprinzător de complicat, deci, în mod firesc, este o provocare bună!
Provocarea
Datele de mai jos reprezintă comenzi, o comandă pe rând. Există trei provocări separate.
Ce formule din F9, G9 și H9 vor număra corect comenzile cu următoarele condiții:
- F9 - Tricou sau hanorac
- G9 - (tricou sau hanorac) și (roșu, albastru sau verde)
- H9 - (tricou sau hanorac) și (roșu, albastru sau verde) și (Denver sau Seattle)
Umbrirea verde se aplică cu formatare condițională și indică valori potrivite pentru fiecare set de criterii SAU din fiecare coloană.
Pentru confortul dvs., sunt disponibile următoarele game denumite:
item = B3: B16
culoare = C3: C16
oraș = D3: D16
Foaia de lucru este atașată. Lasă răspunsurile tale mai jos ca comentarii!
Răspundeți (faceți clic pentru a extinde)Soluția mea folosește SUMPRODUCT cu ISNUMBER și MATCH astfel:
=SUMPRODUCT( ISNUMBER(MATCH(item,("Tshirt","Hoodie"),0))* ISNUMBER(MATCH(color,("Red","Blue","Green"),0))* ISNUMBER(MATCH(city,("Denver","Seattle"),0)) )
Care va conta comenzile unde …
- Elementul este (tricou sau hanorac) și
- Culoarea este (roșu, albastru sau verde) și
- Orașul este (Denver sau Seattle)
Mai multe persoane au sugerat, de asemenea, aceeași abordare. Îmi place această structură, deoarece scalează cu ușurință pentru a gestiona mai multe criterii și funcționează, de asemenea, cu referințe de celule (în loc de valori codificate). Cu referințe de celule, formula din H9 este:
=SUMPRODUCT( ISNUMBER(MATCH(item,F3:F4,0))* ISNUMBER(MATCH(color,G3:G5,0))* ISNUMBER(MATCH(city,H3:H4,0)) )
Cheia acestei formule este construcția ISNUMBER + MATCH. MATCH este configurat „înapoi” - valorile de căutare provin din date, iar criteriile sunt utilizate pentru matrice. Rezultatul este o matrice cu o singură coloană de fiecare dată când se utilizează MATCH. Această matrice conține fie erori # N / A (fără potrivire), fie numere (potrivire), deci ISNUMBER este utilizat pentru a converti la valorile booleene TRUE și FALSE. Operația de înmulțire a matricelor impune valorile TRUE FALSE la 1s și 0s, iar matricea finală din SUMPRODUCT conține 1s în care rândurile îndeplinesc criteriile. SUMPRODUCT apoi însumează matricea și returnează rezultatul.
