Yhteenveto
Voit laskea rivejä TAI-logiikalla käyttämällä SUMPRODUCT-funktioon perustuvaa kaavaa. Esitetyssä esimerkissä G6: n kaava on:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
missä ryhmä (B5: B15), väri1 (C5: C15) ja väri2 (D5: D15) on nimetty alueeksi .
Selitys
Yksi Excelin hankalimmista ongelmista on laskea rivit tietojoukossa "OR-logiikalla". Peruskenaarioita on kaksi: (1) haluat laskea rivit, joissa sarakkeen arvo on "x" TAI "y" (2) haluat laskea rivit, joissa arvo "x" on yhdessä sarakkeessa TAI toinen .
Tässä esimerkissä tavoitteena on laskea rivit, joissa group = "a" JA Väri1 TAI Väri2 ovat "punaiset". Tämä tarkoittaa, että työskentelemme yllä olevan skenaarion 2 kanssa.
Kanssa COUNTIFS
Aluksi saatat tavoittaa COUNTIFS-funktion, joka käsittelee useita ehtoja natiivisti. COUNTIFS-funktio kuitenkin yhdistää ehdot JA-logiikkaan, joten kaikkien ehtojen on oltava TOSI, jotta ne voidaan sisällyttää laskentaan:
=COUNTIFS(group,"a",color1,"red",color2,"red") // returns 1
Tämä tekee COUNTIFS: stä toimimattoman, ellei käytetä useita COUNTIFS-esiintymiä:
=COUNTIFS(group,"a",color1,"red")+COUNTIFS(group,"a",color2,"red")-COUNTIFS(group,"a",color1,"red",color2,"red")
Käännös: Laske rivit, joissa ryhmä on "a" ja väri1 on "punainen" + laske rivit, joissa ryhmä on "a" ja väri2 on "punainen" - laske rivit, joissa ryhmä on "a" ja väri1 on "punainen" ja väri2 on " punainen "(kaksinkertaisen laskennan välttämiseksi).
Tämä toimii, mutta voit nähdä, että tämä on jonkin verran monimutkainen ja turha kaava.
Boolen logiikalla
Parempi ratkaisu on käyttää loogista logiikkaa ja käsitellä tulos SUMPRODUCT-toiminnolla. (Jos tarvitset perustetta Boolen algebraan, tämä video tarjoaa johdannon.) Esitetyssä esimerkissä G6: n kaava on:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
missä ryhmä (B5: B15), väri1 (C5: C15) ja väri2 (D5: D15) on nimetty alueeksi .
Ongelman ensimmäinen osa on testata ryhmää = "a", jota teemme näin:
(group="a")
Koska alue B5: B15 sisältää 11 solua, tämä lauseke palauttaa 11 TOSI- ja EPÄTOSI-arvon taulukon seuraavasti:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Jokainen TOSI edustaa riviä, jossa ryhmä on "A".
Seuraavaksi meidän on tarkistettava arvo "punainen" joko sarakkeessa1 tai sarakkeessa2. Teemme tämän kahdella lausekkeella, jotka on liitetty yhteen (+), koska lisäys vastaa OR-logiikkaa Boolen algebrassa:
(color1="red")+(color2="red")
Excel arvioi TOSI- ja EPÄTOSI-arvot automaattisesti arvoiksi 1 ja 0 minkä tahansa matemaattisen operaation aikana, joten yllä olevan lausekkeen tulos on tällainen taulukko:
(2;0;0;1;1;0;1;0;0;0;1)
Matriisin ensimmäinen numero on 2, koska sekä Väri1 että Väri2 ovat "punaisia" ensimmäisellä rivillä. Seuraavassa selitetyistä syistä meidän on varottava tätä tilannetta tarkistamalla nollan suuremmat arvot:
((2;0;0;1;1;0;1;0;0;0;1))>0
Nyt meillä on jälleen joukko TOSI ja EPÄTOSI arvoja:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Alla olevassa taulukossa on yhteenveto siitä, miten Excel arvioi edellä selitetyn värilogiikan:
Tässä vaiheessa meillä on tuloksia ryhmän = "a" testaamisesta yhdessä taulukossa:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Ja tulokset testaamalla "punainen" väreissä Color1 tai Color2 toisessa taulukossa:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Seuraava askel on tuoda nämä kaksi taulukkoa yhteen AND-logiikan kanssa. Tätä varten käytämme kertomista (*), koska kertolasku vastaa Boolen algebran AND-logiikkaa.
Kertomalla nämä kaksi taulukkoa yhteen, meillä on yksi taulukko 1s ja 0s, joka toimitetaan suoraan SUMPRODUCT-funktiolle:
=SUMPRODUCT((1;0;0;0;1;0;0;0;0;0;0))
SUMPRODUCT-funktio palauttaa lopputuloksena numeroiden summan 2. Tämä on niiden rivien määrä, joissa group = "a" JA Väri1 TAI Väri2 ovat "punaisia".
Kaksinkertaisen laskennan välttämiseksi
Emme halua kaksinkertaista laskea rivejä, joissa sekä Väri1 että Väri2 ovat "punaisia". Siksi tarkistamme tulokset (väri1 = "punainen") + (väri2 = "punainen") alle nollan arvoista alla olevasta koodista:
((color1="red")+(color2="red"))>0
Ilman tätä tarkistusta tietojen ensimmäiseltä riviltä 2 näkyisi viimeisessä taulukossa ja aiheuttaisi kaavan palauttavan väärin 3 viimeisenä lukuna.
SUODATIN -vaihtoehto
Yksi hieno asia Boolen logiikassa on, että se toimii täydellisesti Excelin uusimpien toimintojen kanssa, kuten XLOOKUP ja FILTER. Esimerkiksi FILTER-toiminto voi käyttää täsmälleen samaa logiikkaa, joka on selitetty edellä, vastaavien rivien purkamiseksi:
=FILTER(B5:D15,(group="a")*((color1="red")+(color2="red")>0))
Suodattimen tulos on kaksi riviä, jotka täyttävät alla esitetyt ehdot:
Jos haluat oppia lisää näistä uusista toiminnoista, meillä on yleiskatsaus ja videokoulutus.
