Резиме
Да бисте бројали редове са ИЛИ логиком, можете да користите формулу засновану на функцији СУМПРОДУЦТ. У приказаном примеру, формула у Г6 је:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
где су групе (Б5: Б15), боја1 (Ц5: Ц15) и боја2 (Д5: Д15) именоване опсезима.
Објашњење
Један од сложенијих проблема у програму Екцел је бројање редова у скупу података са „ИЛИ логиком“. Постоје два основна сценарија: (1) желите да пребројите редове где је вредност у колони „к“ ИЛИ „и“ (2) желите да пребројите редове у којима вредност „к“ постоји у једном ступцу ИЛИ другом .
У овом примеру циљ је бројање редова где је група = "а" И Боја1 ИЛИ Боја2 "црвена". То значи да радимо са сценаријем 2 горе.
Са ЦОУНТИФС
У почетку бисте могли посегнути за функцијом ЦОУНТИФС, која изворно обрађује више критеријума. Међутим, функција ЦОУНТИФС спаја услове са логиком АНД, па сви критеријуми морају бити ИСТИНА да би били укључени у бројање:
=COUNTIFS(group,"a",color1,"red",color2,"red") // returns 1
Ово чини ЦОУНТИФС неизводљивим, осим ако не користимо више инстанци ЦОУНТИФС:
=COUNTIFS(group,"a",color1,"red")+COUNTIFS(group,"a",color2,"red")-COUNTIFS(group,"a",color1,"red",color2,"red")
Превод: бројање редова где је група „а“, а боја1 „црвена“ + бројање редова где је група „а“, а боја 2 „црвена“ - бројање редова где је група „а“, а боја 1 „црвена“ и боја 2 „„ црвена “(да би се избегло двоструко бројање).
Ово ради, али видите да је ово донекле компликована и сувишна формула.
Са логичком логиком
Боље решење је употреба логичке логике и обрађивање резултата помоћу функције СУМПРОДУЦТ. (Ако вам треба почетни курс о логичкој алгебри, овај видео даје увод.) У приказаном примеру, формула у Г6 је:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
где су групе (Б5: Б15), боја1 (Ц5: Ц15) и боја2 (Д5: Д15) именоване опсезима.
Први део проблема је тестирање за групу = "а" што ми радимо овако:
(group="a")
Будући да опсег Б5: Б15 садржи 11 ћелија, овај израз враћа низ од 11 ТРУЕ и ФАЛСЕ вредности попут ове:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Свака ИСТИНА представља ред где је група „А“.
Даље, треба да проверимо вредност „црвена“ било у колони1 или у колони2. То радимо са два израза удружена сабирањем (+), јер сабирање одговара логици ОР у логичкој алгебри:
(color1="red")+(color2="red")
Екцел аутоматски процењује вредности ТРУЕ и ФАЛСЕ као 1с и 0с током било које математичке операције, тако да је резултат из горњег израза низ попут овог:
(2;0;0;1;1;0;1;0;0;0;1)
Први број у низу је 2, јер су и Цолор1 и Цолор2 "црвени" у првом реду. Из доле објашњених разлога, морамо се заштитити од ове ситуације провером вредности веће од нуле:
((2;0;0;1;1;0;1;0;0;0;1))>0
Сада поново имамо низ вредности ТРУЕ и ФАЛСЕ:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Табела у наставку сумира како Екцел процењује претходно објашњену логику боја:
У овом тренутку имамо резултате тестирања Гроуп = "а" у једном низу:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
И резултати тестирања „црвене боје“ у Цолор1 или Цолор2 у другом низу:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Следећи корак је повезивање ова два низа са „И логиком“. Да бисмо то урадили, користимо множење (*), јер множење одговара логици АНД у логичкој алгебри.
Након множења два низа заједно, имамо један низ од 1 и 0, који се испоручује директно функцији СУМПРОДУЦТ:
=SUMPRODUCT((1;0;0;0;1;0;0;0;0;0;0))
Функција СУМПРОДУЦТ враћа збир бројева, 2, као коначни резултат. Ово је број редова у којима је група = "а" И Боја1 ИЛИ Боја2 "црвена".
Да би се избегло двоструко бројање
Не желимо двоструко бројати редове у којима су и Цолор1 и Цолор2 "црвени". Због тога проверавамо резултате од (цолор1 = "ред") + (цолор2 = "ред") за вредности веће од нуле у доњем коду:
((color1="red")+(color2="red"))>0
Без ове провере, 2 из првог реда података приказало би се у коначном низу и узроковало да формула погрешно врати 3 као коначни број.
Опција ФИЛТЕР
Једна лепа ствар код логичке логике је да она савршено функционише са најновијим Екцел-овим функцијама, као што су КСЛООКУП и ФИЛТЕР. На пример, функција ФИЛТЕР може користити потпуно исту логику објашњену горе за издвајање одговарајућих редова:
=FILTER(B5:D15,(group="a")*((color1="red")+(color2="red")>0))
Резултат из ФИЛТЕР-а су два реда која испуњавају критеријуме, као што се види доле:
Ако желите да сазнате више о овим новим функцијама, имамо преглед и видео обуку.
