Üks probleem, mis Excelis palju esile kerkib, on loendamine või summeerimine mitme VÕI tingimuse põhjal. Näiteks peate võib-olla analüüsima andmeid ja loendama tellimusi Seattle'is või Denveris punaste, siniste või roheliste toodete jaoks? See võib olla üllatavalt keeruline, nii et loomulikult teeb see hea väljakutse!
Väljakutse
Allpool olevad andmed tähistavad tellimusi, üks tellimus rea kohta. On kolm eraldi väljakutset.
Mis valemites F9, G9 ja H9 loetakse tellimusi õigesti järgmiste tingimustega:
- F9 - särk või kapuuts
- G9 - (särk või kapuuts) ja (punane, sinine või roheline)
- H9 - (särk või kapuuts) ja (punane, sinine või roheline) ja (Denver või Seattle)
Rohelist varjutust rakendatakse tingimusliku vormindusega ja see näitab iga veeru iga VÕI-kriteeriumi komplekti vastavaid väärtusi.
Teie mugavuse huvides on saadaval järgmised nimevahemikud:
kirje = B3: B16
värv = C3: C16
linn = D3: D16
Tööleht on lisatud. Jäta oma vastused allpool kommentaarideks!
Vastus (klõpsake laiendamiseks)Minu lahendus kasutab SUMPRODUCTi koos ISNUMBER ja MATCH-ga järgmiselt:
=SUMPRODUCT( ISNUMBER(MATCH(item,("Tshirt","Hoodie"),0))* ISNUMBER(MATCH(color,("Red","Blue","Green"),0))* ISNUMBER(MATCH(city,("Denver","Seattle"),0)) )
Mis loeb tellimusi, kus…
- Üksus on (T-särk või Hoodie) ja
- Värv on (punane, sinine või roheline) ja
- Linn on (Denver või Seattle)
Ka mitmed inimesed soovitasid sama lähenemist. Mulle meeldib see struktuur, kuna see on hõlpsasti käsitletav rohkemate kriteeriumide käsitsemiseks ja töötab ka lahtriviidetega (raskekodeeritud väärtuste asemel). Lahtriviidete korral on H9 valem:
=SUMPRODUCT( ISNUMBER(MATCH(item,F3:F4,0))* ISNUMBER(MATCH(color,G3:G5,0))* ISNUMBER(MATCH(city,H3:H4,0)) )
Selle valemi võti on ISNUMBER + MATCH konstruktsioon. MATCH on seadistatud "tagurpidi" - otsinguväärtused pärinevad andmetest ja massiivi jaoks kasutatakse kriteeriume. Tulemuseks on iga veeru massiiv iga kord, kui kasutatakse MATCH-i. See massiiv sisaldab kas # N / A viga (vastet pole) või numbreid (vaste), nii et ISNUMBER-i kasutatakse loogeväärtusteks TRUE ja FALSE teisendamiseks. Massiivide korrutamise toiming sunnib TÕELISED VALE väärtused väärtusele 1 ja 0 ning SUMPRODUCTi sees olev viimane massiiv sisaldab 1, kus read vastavad kriteeriumidele. Seejärel summeerib SUMPRODUCT massiivi ja tagastab tulemuse.
