Viena iš „Excel“ daug iškylančių problemų yra skaičiavimas ar susumavimas pagal kelias ARBA sąlygas. Pavyzdžiui, galbūt jums reikia išanalizuoti duomenis ir suskaičiuoti užsakymus Sietle ar Denveryje, jei prekės yra raudonos, mėlynos ar žalios? Tai gali būti stebėtinai keblu, todėl natūraliai tai yra geras iššūkis!
Iššūkis
Žemiau pateikiami duomenys nurodo užsakymus, po vieną užsakymą eilutėje. Yra trys atskiri iššūkiai.
Kokios F9, G9 ir H9 formulės teisingai skaičiuos užsakymus su šiomis sąlygomis:
- F9 - marškinėliai arba gobtuvas
- G9 - (marškinėliai arba gobtuvas) ir (raudona, mėlyna arba žalia)
- H9 - (marškinėliai arba gobtuvas) ir (raudona, mėlyna arba žalia) ir (Denveris ar Sietlas)
Žalias šešėlis taikomas su sąlyginiu formatavimu ir nurodo kiekvieno OR stulpelio kiekvieno OR kriterijų rinkinio atitikimo vertes.
Jūsų patogumui galimi šie įvardyti diapazonai:
elementas = B3: B16
spalva = C3: C16
miestas = D3: D16
Darbalapis pridedamas. Atsakymus palikite žemiau kaip komentarus!
Atsakymas (spustelėkite, jei norite išplėsti)Mano sprendimas naudoja „SUMPRODUCT“ su „ISNUMBER“ ir „MATCH“ taip:
=SUMPRODUCT( ISNUMBER(MATCH(item,("Tshirt","Hoodie"),0))* ISNUMBER(MATCH(color,("Red","Blue","Green"),0))* ISNUMBER(MATCH(city,("Denver","Seattle"),0)) )
Kuris skaičiuos užsakymus ten, kur…
- Prekė yra (marškinėliai arba Hoodie) ir
- Spalva yra (raudona, mėlyna arba žalia) ir
- Miestas yra (Denveris arba Sietlas)
Keli žmonės taip pat pasiūlė tą patį požiūrį. Man patinka ši struktūra, nes ji lengvai pritaikoma pagal daugiau kriterijų, taip pat veikia su langelių nuorodomis (vietoj sunkiai užkoduotų reikšmių). Naudojant ląstelių nuorodas, H9 formulė yra:
=SUMPRODUCT( ISNUMBER(MATCH(item,F3:F4,0))* ISNUMBER(MATCH(color,G3:G5,0))* ISNUMBER(MATCH(city,H3:H4,0)) )
Šios formulės raktas yra „ISNUMBER + MATCH“ konstrukcija. MATCH nustatoma „atgal“ - paieškos vertės gaunamos iš duomenų, o masyvui naudojami kriterijai. Rezultatas yra vieno stulpelio masyvas kiekvieną kartą, kai naudojamas MATCH. Šiame masyve yra # N / A klaidos (be atitikties) arba skaičių (atitikimas), todėl ISNUMBER naudojamas norint konvertuoti į logines reikšmes TRUE ir FALSE. Masyvų padauginimo operacija TIKROS NETIESOS reikšmes verčia į 1s ir 0s, o SUMPRODUCT viduje esančiame paskutiniame masyve yra 1s, kur eilutės atitinka kriterijus. Tada SUMPRODUCT sumuoja masyvą ir grąžina rezultatą.
