Обобщение
За да броите редове с логика ИЛИ, можете да използвате формула, базирана на функцията SUMPRODUCT. В показания пример формулата в G6 е:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
където група (B5: B15), color1 (C5: C15) и color2 (D5: D15) са наречени диапазони.
Обяснение
Един от по-сложните проблеми в Excel е да брои редове в набор от данни с "ИЛИ логика". Има два основни сценария: (1) искате да преброите редове, където стойността в колона е "x" ИЛИ "y" (2) искате да преброите редове, където стойност "x" съществува в една колона ИЛИ друга .
В този пример целта е да се преброят редове, където group = "a" И Color1 ИЛИ Color2 са "червени". Това означава, че работим със сценарий 2 по-горе.
С БРОЙКИ
Отначало бихте могли да достигнете до функцията COUNTIFS, която обработва няколко критерия. Функцията COUNTIFS обаче обединява условия с логика AND, така че всички критерии трябва да са TRUE, за да бъдат включени в броя:
=COUNTIFS(group,"a",color1,"red",color2,"red") // returns 1
Това прави COUNTIFS неработещи, освен ако не използваме множество копия на COUNTIFS:
=COUNTIFS(group,"a",color1,"red")+COUNTIFS(group,"a",color2,"red")-COUNTIFS(group,"a",color1,"red",color2,"red")
Превод: преброяване на редове, където групата е "а" и color1 е "червена" + преброяване на редове, където групата е "а", а color2 е "червена" - преброяване на редове, където групата е "а" и color1 е "червената" и color2 е " червено "(за да се избегне двойно броене).
Това работи, но можете да видите, че това е малко сложна и излишна формула.
С логическа логика
По-добро решение е да се използва логическа логика и да се обработи резултатът с функцията SUMPRODUCT. (Ако имате нужда от буквар по булева алгебра, това видео предоставя въведение.) В показания пример формулата в G6 е:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
където група (B5: B15), color1 (C5: C15) и color2 (D5: D15) са наречени диапазони.
Първата част на проблема е да тестваме за group = "a", което правим така:
(group="a")
Тъй като диапазонът B5: B15 съдържа 11 клетки, този израз връща масив от 11 TRUE и FALSE стойности по следния начин:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Всяка TRUE представлява ред, където групата е "A".
След това трябва да проверим стойността "червено" в колона1 или колона2. Правим това с два израза, обединени с добавяне (+), тъй като добавянето съответства на логиката ИЛИ в булева алгебра:
(color1="red")+(color2="red")
Excel автоматично оценява TRUE и FALSE стойности като 1s и 0s по време на всяка математическа операция, така че резултатът от горния израз е масив като този:
(2;0;0;1;1;0;1;0;0;0;1)
Първото число в масива е 2, тъй като и Color1 и Color2 са "червени" на първия ред. По причини, обяснени по-долу, трябва да се предпазим от тази ситуация, като проверим за стойности, по-големи от нула:
((2;0;0;1;1;0;1;0;0;0;1))>0
Сега отново имаме масив от TRUE и FALSE стойности:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Таблицата по-долу обобщава как Excel оценява цветната логика, обяснена по-горе:
Към този момент имаме резултати от тестване на Group = "a" в един масив:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
И резултати от тестване на "червено" в Color1 или Color2 в друг масив:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Следващата стъпка е да съберете тези два масива заедно с "И логика". За целта използваме умножение (*), тъй като умножението съответства на логиката И в булевата алгебра.
След като умножим двата масива заедно, имаме единичен масив от 1s и 0s, който се доставя директно на функцията SUMPRODUCT:
=SUMPRODUCT((1;0;0;0;1;0;0;0;0;0;0))
Функцията SUMPRODUCT връща сумата от числа 2 като краен резултат. Това е броят на редовете, където group = "a" И Color1 ИЛИ Color2 са "червени".
За да се избегне двойно броене
Не искаме да удвояваме броя на редовете, където и Color1 и Color2 са "червени". Ето защо проверяваме резултатите от (color1 = "red") + (color2 = "red") за стойности, по-големи от нула в кода по-долу:
((color1="red")+(color2="red"))>0
Без тази проверка 2 от първия ред в данните ще се покажат в крайния масив и ще накарат формулата да върне неправилно 3 като окончателен брой.
Опция FILTER
Едно хубаво нещо при логическата логика е, че тя работи перфектно с най-новите функции на Excel, като XLOOKUP и FILTER. Например, функцията FILTER може да използва точно същата логика, обяснена по-горе, за да извлече съвпадащи редове:
=FILTER(B5:D15,(group="a")*((color1="red")+(color2="red")>0))
Резултатът от FILTER е двата реда, които отговарят на критериите, както се вижда по-долу:
Ако искате да научите повече за тези нови функции, имаме общ преглед и видео обучение.
