Santrauka
Norėdami suskaičiuoti eilutes su OR logika, galite naudoti formulę, pagrįstą funkcija SUMPRODUCT. Parodytame pavyzdyje formulė G6 yra:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
kur grupė (B5: B15), spalva1 (C5: C15) ir spalva2 (D5: D15) yra pavadinta diapazonais.
Paaiškinimas
Viena iš sudėtingesnių „Excel“ problemų yra duomenų rinkinio eilučių skaičiavimas su „ARBA logika“. Yra du pagrindiniai scenarijai: (1) norite skaičiuoti eilutes, kurių vertė stulpelyje yra „x“ ARBA „y“ (2) norite skaičiuoti eilutes, kurių vertė „x“ yra viename stulpelyje ARBA kitame .
Šiame pavyzdyje tikslas yra suskaičiuoti eilutes, kuriose group = "a" IR spalva1 ARBA spalva2 yra "raudonos". Tai reiškia, kad mes dirbame su aukščiau pateiktu 2 scenarijumi.
Su COUNTIFS
Iš pradžių galite pasiekti funkciją COUNTIFS, kuri natūraliai tvarko kelis kriterijus. Tačiau funkcija COUNTIFS sujungia sąlygas su AND logika, todėl visi kriterijai turi būti TIKRI, kad būtų įtraukti į skaičių:
=COUNTIFS(group,"a",color1,"red",color2,"red") // returns 1
Dėl to COUNTIFS nebeveikia, nebent mes naudojame kelis COUNTIFS egzempliorius:
=COUNTIFS(group,"a",color1,"red")+COUNTIFS(group,"a",color2,"red")-COUNTIFS(group,"a",color1,"red",color2,"red")
Vertimas: skaičiuokite eilutes, kur grupė yra „a“, o spalva1 yra „raudona“ + skaičiuokite eilutes, kur grupė yra „a“ ir spalva2 yra „raudona“ - skaičiuokite eilutes, kur grupė yra „a“, o spalva1 yra „raudona“, o spalva2 yra “ raudona "(kad būtų išvengta dvigubo skaičiavimo).
Tai veikia, bet matote, kad tai yra šiek tiek sudėtinga ir nereikalinga formulė.
Su logine logika
Geresnis sprendimas yra naudoti loginę logiką ir apdoroti rezultatą naudojant funkciją SUMPRODUCT. (Jei jums reikia Būlo algebros pradmenų, šiame vaizdo įraše pateikiama įžanga.) Parodytame pavyzdyje formulė G6 yra:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
kur grupė (B5: B15), spalva1 (C5: C15) ir spalva2 (D5: D15) yra pavadinta diapazonais.
Pirmoji problemos dalis yra išbandyti grupę = "a", kuri mums patinka taip:
(group="a")
Kadangi diapazone B5: B15 yra 11 langelių, ši išraiška pateikia 11 TRUE ir FALSE reikšmių masyvą:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Kiekviena TRUE reiškia eilutę, kurioje grupė yra „A“.
Tada turime patikrinti, ar „raudona“ reikšmė yra 1 arba 2 stulpelyje. Tai darome su dviem posakiais, sujungtais pridėjimu (+), nes Būlo algebroje pridėjimas atitinka OR logiką:
(color1="red")+(color2="red")
„Excel“ atliekant bet kurią matematikos operaciją automatiškai įvertina TIKRAS ir NETIESOS reikšmes kaip 1 ir 0, todėl aukščiau pateiktos išraiškos rezultatas yra toks masyvas:
(2;0;0;1;1;0;1;0;0;0;1)
Pirmasis masyvo skaičius yra 2, nes tiek Spalva1, tiek Spalva2 yra „raudonos“ pirmoje eilutėje. Dėl toliau paaiškintų priežasčių turime apsisaugoti nuo šios situacijos, tikrindami, ar vertės yra didesnės nei nulis:
((2;0;0;1;1;0;1;0;0;0;1))>0
Dabar mes vėl turime TRUE ir FALSE reikšmių masyvą:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Toliau pateiktoje lentelėje apibendrinama, kaip „Excel“ vertina aukščiau paaiškintą spalvų logiką:
Šiuo metu mes turime rezultatus bandydami Group = "a" viename masyve:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Rezultatai bandant „raudoną“ spalva1 ar spalva2 kitame masyve:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Kitas žingsnis yra sujungti šias dvi masyvas su „IR logika“. Norėdami tai padaryti, mes naudojame dauginimą (*), nes dauginimas atitinka AND logiką Būlio algebroje.
Padauginę abu masyvus, turime vieną 1s ir 0s masyvą, kuris pristatomas tiesiai į funkciją SUMPRODUCT:
=SUMPRODUCT((1;0;0;0;1;0;0;0;0;0;0))
Funkcija SUMPRODUCT grąžina skaičių sumą 2 kaip galutinį rezultatą. Tai yra eilučių, kuriose group = "a" IR spalva1 ARBA spalva2 yra "raudonos", skaičius.
Kad būtų išvengta dvigubo skaičiavimo
Nenorime dvigubai skaičiuoti eilučių, kai ir spalva1, ir spalva2 yra „raudonos“. Štai kodėl mes patikriname rezultatus iš (spalva1 = "raudona") + (spalva2 = "raudona"), jei reikšmės yra didesnės nei nulis žemiau esančiame kode:
((color1="red")+(color2="red"))>0
Be šio patikrinimo 2 iš pirmos duomenų eilutės būtų rodomi paskutiniame masyve ir dėl to formulė neteisingai grąžins 3 kaip galutinį skaičių.
FILTRO parinktis
Vienas gražus Būlio logikos dalykas yra tas, kad jis puikiai veikia su naujausiomis „Excel“ funkcijomis, tokiomis kaip XLOOKUP ir FILTER. Pvz., Funkcija FILTER gali naudoti tą pačią logiką, kuri buvo paaiškinta aukščiau, norint išskleisti atitinkamas eilutes:
=FILTER(B5:D15,(group="a")*((color1="red")+(color2="red")>0))
Filtro rezultatas yra dvi eilutės, kurios atitinka kriterijus, kaip parodyta žemiau:
Jei norite sužinoti daugiau apie šias naujas funkcijas, turime apžvalgą ir vaizdo įrašų mokymą.
