Kokkuvõte
VÕI loogikaga ridade loendamiseks võite kasutada funktsiooni SUMPRODUCT põhinevat valemit. Näidatud näites on valem G6-s järgmine:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
kus rühm (B5: B15), värv1 (C5: C15) ja värv2 (D5: D15) nimetatakse vahemikeks.
Selgitus
Exceli üks keerulisemaid probleeme on "VÕI loogikaga" andmehulga ridade loendamine. On kaks põhistsenaariumi: (1) soovite lugeda ridu, kus veeru väärtus on "x" VÕI "y" (2) soovite loendada ridu, kus väärtus "x" on ühes veerus VÕI teine .
Selles näites on eesmärk lugeda ridu, kus group = "a" JA Värv1 VÕI Värv2 on "punane". See tähendab, et töötame ülaltoodud stsenaariumiga 2.
Koos COUNTIFS-iga
Esmalt võite jõuda funktsioonini COUNTIFS, mis käsitleb mitut kriteeriumit loomulikult. Funktsioon COUNTIFS ühendab tingimused aga loogikaga AND, mistõttu loendisse lisamiseks peavad kõik kriteeriumid olema TÕESED:
=COUNTIFS(group,"a",color1,"red",color2,"red") // returns 1
See muudab COUNTIFS-i toimimatuks, kui me ei kasuta COUNTIFS-i mitut eksemplari:
=COUNTIFS(group,"a",color1,"red")+COUNTIFS(group,"a",color2,"red")-COUNTIFS(group,"a",color1,"red",color2,"red")
Tõlge: lugege ridu, kus rühm on "a" ja värv1 on "punane", + loendage ridu, kus rühm on "a" ja värv2 on "punane" - loendage ridu, kus rühm on "a" ja värv1 on "punane" ja värv2 on " punane "(topeltloendamise vältimiseks).
See töötab, kuid näete, et see on mõnevõrra keeruline ja üleliigne valem.
Boole'i loogikaga
Parem lahendus on Boole'i loogika kasutamine ja tulemuse töötlemine funktsiooniga SUMPRODUCT. (Kui vajate Boole'i algebra aabitsat, pakub see video sissejuhatust.) Näidatud näites on valem G6-s järgmine:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
kus rühm (B5: B15), värv1 (C5: C15) ja värv2 (D5: D15) nimetatakse vahemikeks.
Probleemi esimene osa on testida group = "a", mis meile meeldib:
(group="a")
Kuna vahemik B5: B15 sisaldab 11 lahtrit, tagastab see avaldis 11 TRUE ja FALSE väärtuse massiivi järgmiselt:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Iga TRUE tähistab rida, kus rühm on "A".
Järgmisena peame kontrollima kas veerus1 või veerus2 väärtust "punane". Teeme seda kahe avaldisega, mis on ühendatud liitmisega (+), kuna liitmine vastab Boole'i algebras loogikale OR:
(color1="red")+(color2="red")
Excel hindab mis tahes matemaatikaoperatsioonide käigus TÕENE ja VALE väärtusi automaatselt 1-ks ja 0-ks, nii et ülaltoodud avaldise tulemus on selline massiiv:
(2;0;0;1;1;0;1;0;0;0;1)
Massiivi esimene number on 2, sest nii Color1 kui Color2 on esimeses reas "punased". Allpool selgitatud põhjustel peame selle olukorra eest kaitsma, kontrollides nullist suuremaid väärtusi:
((2;0;0;1;1;0;1;0;0;0;1))>0
Nüüd on meil jälle väärtused TRUE ja FALSE:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Allpool olev tabel võtab kokku, kuidas Excel hindab ülalpool selgitatud värviloogikat:
Siinkohal on meil tulemused Group = "a" testimisest ühes massiivis:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Ja selle tulemuseks on "punase" testimine värvis1 või värvis2 teises massiivis:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Järgmine samm on nende kahe massiivi ühendamine "JA loogikaga". Selleks kasutame korrutamist (*), kuna korrutamine vastab Boole'i algebras loogikale AND.
Pärast kahe massiivi korrutamist on meil üks massiiv 1s ja 0s, mis edastatakse otse funktsioonile SUMPRODUCT:
=SUMPRODUCT((1;0;0;0;1;0;0;0;0;0;0))
Funktsioon SUMPRODUCT tagastab lõpptulemuseks arvude summa 2. See on nende ridade arv, kus rühm = "a" JA Värv1 VÕI Värv2 on "punane".
Topeltloendamise vältimiseks
Me ei taha kahekordistada ridu, kus nii Värv1 kui Värv2 on "punased". Sellepärast kontrollime tulemuste (color1 = "punane") + (color2 = "punane") tulemusi allpool toodud koodide puhul suuremate kui nullide puhul:
((color1="red")+(color2="red"))>0
Ilma selle kontrollita kuvatakse andmete esimesest reast 2 viimases massiivis ja valem tagastab valesti 3 lõpliku loendina.
Suvand FILTER
Boole'i loogika üks tore asi on see, et see töötab suurepäraselt Exceli uusimate funktsioonidega, nagu XLOOKUP ja FILTER. Näiteks saab funktsioon FILTER sobitada ridade väljavõtmiseks täpselt sama loogikat, mida on eespool selgitatud:
=FILTER(B5:D15,(group="a")*((color1="red")+(color2="red")>0))
FILTERi tulemus on kaks rida, mis vastavad allpool toodud kriteeriumidele:
Kui soovite nende uute funktsioonide kohta lisateavet, on meil ülevaade ja videokoolitus.
