Kopsavilkums
Lai uzskaitītu rindas ar loģiku OR, varat izmantot formulu, kuras pamatā ir funkcija SUMPRODUCT. Parādītajā piemērā formula G6 ir:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
kur grupa (B5: B15), krāsa1 (C5: C15) un krāsa2 (D5: D15) ir nosaukta par diapazoniem.
Paskaidrojums
Viena no sarežģītākajām problēmām programmā Excel ir rindu skaitīšana datu kopā ar “OR loģiku”. Ir divi pamata scenāriji: (1) vēlaties saskaitīt rindas, kur kolonnas vērtība ir "x" VAI "y" (2) vēlaties skaitīt rindas, kur vērtība "x" pastāv vienā kolonnā VAI citā .
Šajā piemērā mērķis ir saskaitīt rindas, kurās group = "a" UN Color1 OR Color2 ir "sarkanas". Tas nozīmē, ka mēs strādājam ar iepriekš minēto 2. scenāriju.
Ar COUNTIFS
Sākumā jūs varētu sasniegt funkciju COUNTIFS, kas dabiski apstrādā vairākus kritērijus. Tomēr funkcija COUNTIFS apvieno nosacījumus ar AND loģiku, tāpēc visiem kritērijiem jābūt PATIESIEM, lai tos iekļautu skaitā:
=COUNTIFS(group,"a",color1,"red",color2,"red") // returns 1
Tas padara COUNTIFS nedarbojamu, ja vien mēs neizmantojam vairākus COUNTIFS gadījumus:
=COUNTIFS(group,"a",color1,"red")+COUNTIFS(group,"a",color2,"red")-COUNTIFS(group,"a",color1,"red",color2,"red")
Tulkojums: saskaitīt rindas, kur grupa ir "a" un krāsa1 ir "sarkana" + skaitīt rindas, kur grupa ir "a" un krāsa2 ir "sarkana" - skaitīt rindas, kur grupa ir "a" un krāsa1 ir "sarkana" un krāsa2 ir " sarkans "(lai izvairītos no dubultas skaitīšanas).
Tas darbojas, bet jūs varat redzēt, ka šī ir nedaudz sarežģīta un lieka formula.
Ar Būla loģiku
Labāks risinājums ir izmantot Būla loģiku un rezultātu apstrādāt ar funkciju SUMPRODUCT. (Ja jums ir nepieciešams pamats Būla algebrā, šis video sniedz ievadu.) Parādītajā piemērā formula G6 ir:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
kur grupa (B5: B15), krāsa1 (C5: C15) un krāsa2 (D5: D15) ir nosaukta par diapazoniem.
Pirmā problēmas daļa ir pārbaudīt grupu = "a", kas mums patīk šādi:
(group="a")
Tā kā diapazonā B5: B15 ir 11 šūnas, šī izteiksme atgriež 11 TRUE un FALSE vērtību masīvu šādi:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Katra TRUE apzīmē rindu, kurā grupa ir "A".
Pēc tam mums ir jāpārbauda vērtība "sarkans" vai nu 1., vai 2. slejā. Mēs to darām ar divām izteiksmēm, kuras savieno papildinājums (+), jo Būla algebrā pievienošana atbilst OR loģikai:
(color1="red")+(color2="red")
Jebkuras matemātikas darbības laikā programma Excel automātiski novērtē TRUE un FALSE vērtības kā 1 un 0, tāpēc iepriekš minētās izteiksmes rezultāts ir šāds masīvs:
(2;0;0;1;1;0;1;0;0;0;1)
Masīva pirmais skaitlis ir 2, jo gan Color1, gan Color2 pirmajā rindā ir "sarkans". Tālāk izskaidroto iemeslu dēļ mums ir jānodrošina šī situācija, pārbaudot, vai vērtības pārsniedz nulli:
((2;0;0;1;1;0;1;0;0;0;1))>0
Tagad mums atkal ir TRUE un FALSE vērtību masīvs:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Zemāk esošajā tabulā ir apkopots, kā Excel novērtē iepriekš paskaidroto krāsu loģiku:
Šajā brīdī mums ir rezultāti, pārbaudot Group = "a" vienā masīvā:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Rezultāti, pārbaudot “sarkanu” Color1 vai Color2 citā masīvā:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Nākamais solis ir apvienot šos divus masīvus ar “UN loģiku”. Lai to izdarītu, mēs izmantojam reizināšanu (*), jo reizināšana atbilst Boolean algebras loģikai.
Pēc divu masīvu reizināšanas kopā mums ir viens masīvs 1s un 0s, kas tiek piegādāts tieši funkcijai SUMPRODUCT:
=SUMPRODUCT((1;0;0;0;1;0;0;0;0;0;0))
Funkcija SUMPRODUCT atgriež skaitļu summu 2 kā gala rezultātu. Tas ir to rindu skaits, kurās group = "a" UN Color1 OR Color2 ir "sarkans".
Lai izvairītos no dubultas skaitīšanas
Mēs nevēlamies dubultot rindas, kur gan Color1, gan Color2 ir "sarkanas". Tāpēc zemāk esošajā kodā mēs pārbaudām rezultātu (color1 = "red") + (color2 = "red") vērtības, kas lielākas par nulli:
((color1="red")+(color2="red"))>0
Bez šīs pārbaudes 2 no pirmās datu rindas tiks parādīti pēdējā masīvā un izraisīs formulas nepareizu atgriešanu 3 kā galīgo skaitli.
Filtra opcija
Viena jauka lieta Būla loģikā ir tā, ka tā lieliski darbojas ar Excel jaunākajām funkcijām, piemēram, XLOOKUP un FILTER. Piemēram, funkcija FILTER var izmantot tieši to pašu loģiku, kas paskaidrota iepriekš, lai izvilktu atbilstošās rindas:
=FILTER(B5:D15,(group="a")*((color1="red")+(color2="red")>0))
Rezultāts no FILTER ir divas rindas, kas atbilst zemāk redzamajiem kritērijiem:
Ja vēlaties uzzināt vairāk par šīm jaunajām funkcijām, mums ir pārskats un video apmācība.
