Брои с ИЛИ логика за множество критерии в Excel
Обикновено можете да използвате функцията COUNTIFS, за да преброите клетки въз основа на едно или повече условия с И логика в Excel. Изпадали ли сте някога в ситуация, в която трябва да преброите повече от една стойност от една колона или диапазон от клетки? Това означава да броите с множество условия и ИЛИ логика. В този случай можете да приложите функциите SUM и COUNTIFS заедно или да използвате функцията SUMPRODUCT.
- Преброяване на клетки с условия ИЛИ в Excel
- Преброяване на клетки с множество набори от ИЛИ условия в Excel
Преброяване на клетки с условия ИЛИ в Excel
Например, имам набор от данни, както е показано на екранната снимка по-долу, сега искам да преброя броя на продукта, който е „Молив“ или „Линийка“, тук ще обсъдя две формули за решаване на тази задача в Excel.
Преброяване на клетки с условия ИЛИ с помощта на функциите SUM и COUNTIFS
В Excel, за да броите с множество условия ИЛИ, можете да използвате функциите SUM и COUNTIFS с константа на масив, общият синтаксис е:
- range: Диапазонът от данни съдържа критериите, от които броите клетките;
- criterion1, criterion2, criterion3…: Условията, въз основа на които искате да преброите клетките.
За да преброите броя на продуктите, които са „Молив“ или „Линийка“, моля, копирайте или въведете формулата по-долу в празна клетка и след това натиснете Въведете ключ за получаване на резултата:
Обяснение на формулата:
=SUM(COUNTIFS(B2:B13,{"Молив","Линийка"}))
- {"Молив","Линийка"}: Първо, трябва да пакетирате всички условия в константа на масив като тази: {"Молив","Линийка"}, разделяйки елементите със запетаи.
- COUNTIFS(B2:B13;{"Молив","Линийка"}): Тази функция COUNTIFS ще получи индивидуални преброявания за „Молив“ и „Линийка“ и ще получите следния резултат: {2,3}.
- SUM(COUNTIFS(B2:B13,{"Молив","Линийка"}))=SUM({2,3}): И накрая, тази функция SUM сумира всички елементи в масива и връща резултата: 5.
Съвети: Можете също да използвате препратки към клетки за критерии, моля, приложете формулата за масив по-долу и след това натиснете Ctrl + Shift + Enter ключове заедно, за да получите правилния резултат:
Пребройте клетки с условия ИЛИ, като използвате функцията SUMPRODUCT
Ето друга формула, създадена от функцията SUMPRODUCT, която може да помогне и за преброяване на клетки с логика ИЛИ. Общият синтаксис е:
- range: Диапазонът от данни съдържа критериите, от които броите клетките;
- criterion1, criterion2, criterion3…: Условията, въз основа на които искате да преброите клетките.
Моля, копирайте или въведете следната формула в празна клетка и натиснете Въведете ключ за връщане на резултата:
Обяснение на формулата:
=SUMPRODUCT(1*(B2:B13={"Молив","Линийка"}))
- B2:B13={"Молив","Линийка"}: Този израз сравнява всеки критерий „Молив“, „Линийка“ с клетка от диапазон B2:B13. Ако критерият е изпълнен, той връща TRUE, в противен случай ще се покаже FALSE, ще получите следния резултат: {TRUE,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,TRUE;FALSE,FALSE;TRUE,FALSE ;НЕВЯРНО,ВЯРНО;ВЯРНО,ВЯРНО;ВЯРНО,ВЯРНО;ВЯРНО,ВЯРНО;ВЯРНО,ВЯРНО;ВЯРНО,ВЯРНО}.
- 1*(B2:B13={"Молив","Линийка"}): Умножението преобразува логическите стойности – TRUE и FALSE в 1 и 0, така че ще получи резултата по следния начин: {1,0;0,0;0,0;0,1;0,0;1,0;0,0 ,0,1;0,0;0,0;0,1;0,0;XNUMX}.
- SUMPRODUCT(1*(B2:B13={"Pencil","Ruler"}))= SUMPRODUCT({1,0;0,0;0,0;0,1;0,0;1,0;0,0;0,1;0,0;0,0;0,1;0,0}): Най-накрая функцията SUMPRODUCT събира всички числа в масива, за да получи резултата: 5.
Преброяване на клетки с множество набори от ИЛИ условия в Excel
Понякога може да се наложи да преброите клетките с два или повече набора от условия ИЛИ, в този случай можете да използвате или SUM и COUNTIFS с константа на масив или SUMPRODUCT с функции ISNUMBER MATCH.
Преброяване на клетки с два набора от условия ИЛИ чрез използване на функциите SUM и COUNTIFS
За да работите само с два набора от критерии ИЛИ, просто трябва да добавите друга константа на масива към формулата COUNTIFS.
Например, имам диапазона от данни, както е показано на екранната снимка по-долу, сега искам да преброя хората, които са поръчали „Молив“ или „Линийка“, а сумата е или <100, или >200.
Моля, въведете или копирайте следната формула в клетка и след това натиснете Въведете ключ, за да получите резултата, вижте екранната снимка:
Забележка: Във формулата трябва да използвате точка и запетая за константата на втория масив, която създава вертикален масив.
Пребройте клетки с множество набори от условия ИЛИ, като използвате функцията SUMPRODUCT
Горната формула работи само за два набора от критерии ИЛИ, ако трябва да броите с повече критерии, сложна формула SUMPRODUCT заедно с функции ISNUMBER MATCH може да ви помогне.
Вземете например данните по-долу, за да преброите хората, които са поръчали „Молив“ или „Линийка“ и статусът е „Доставено“ или „В транзит“ и е подписано от „Боб“ или „Еко“, трябва да приложите сложна формула .
Моля, копирайте или въведете формулата по-долу в празна клетка и след това натиснете Въведете ключ, за да получите изчислението, вижте екранната снимка:
Обяснение на формулата:
=SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))*ISNUMBER(MATCH(C2:C13,{"Delivered","In transit"},0))*ISNUMBER(MATCH(D2:D13,{"Bob","Eko"},0)))
ISNUMBER(MATCH(B2:B13;{"Молив","Линийка"};0)):
- MATCH(B2:B13;{"Молив","Линийка"};0): Тази функция MATCH се използва за сравняване на всяка клетка в диапазон B2:B13 със съответната константа на масива. Ако съвпадението бъде намерено, то връща относителна позиция на стойността в масива, в противен случай се показва стойност за грешка. И така, ще получите списъка с масиви по следния начин: {1;#N/A;#N/A;2;#N/A;1;#N/A;2;1;#N/A;2;# N/A}.
- ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))= ISNUMBER({1;#N/A;#N/A;2;#N/A;1;#N/A;2;1;#N/A;2;#N/A}): Функцията ISNUMBER преобразува числата в TRUE и стойностите на грешката в FALSE по следния начин: {TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE}.
Горната логика може да се приложи и към втория и третия ISNUMBER израз.
SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))*ISNUMBER(MATCH(C2:C13,{"Delivered","In transit"},0))*ISNUMBER(MATCH(D2:D13,{"Bob","Eko"},0))):
- След това тези три масива се умножават заедно в SUMPRODUCT, който автоматично преобразува стойностите TRUE и FALSE в 1s и 0s като част от математическата операция като тази: SUMPRODUCT({1;0;0;1;0;1;0;1; 1;0;1;0}*{1;1;0;0;1;1;0;1;1;0;1;1} *{1;0;0;0;0;0;0; 1;0;0;1;0})=SUMPRODUCT({1;0;0;0;0;0;0;1;0;0;1;0;XNUMX}).
- Най-накрая функцията SUMPRODUCT сумира всички числа в масива, за да получи резултата: 3.
Използвана относителна функция:
- СУММА:
- Функцията SUM на Excel връща сумата от предоставените стойности.
- COUNTIF:
- Функцията COUNTIF е статистическа функция в Excel, която се използва за преброяване на броя клетки, които отговарят на критерий.
- SUMPRODUCT:
- Функцията SUMPRODUCT може да се използва за умножаване на две или повече колони или масиви заедно и след това да се получи сумата от продуктите.
- ISNUMBER:
- Функцията ISNUMBER на Excel връща TRUE, когато клетка съдържа число, и FALSE, ако не.
- СЪВПАДА:
- Функцията MATCH на Microsoft Excel търси конкретна стойност в диапазон от клетки и връща относителната позиция на тази стойност.
Още статии:
- Преброяване на уникални числови стойности въз основа на критерии
- В работния лист на Excel може да страдате от проблем, който брои броя на уникалните числови стойности въз основа на конкретно условие. Например, как мога да преброя уникалните стойности на Qty на продукта „Тениска“ от отчета, както е показано на екранната снимка по-долу? В тази статия ще покажа някои формули за постигане на тази задача в Excel.
- Пребройте клетки, които отговарят на два или повече критерия
- В Excel, за да преброите броя на клетките въз основа на два или множество критерии, функцията COUNTIFS може да ви помогне да решите тази задача бързо и лесно.
- Преброяване на броя редове с множество критерии ИЛИ
- За да преброите броя на редовете с множество критерии в различни колони, с логика ИЛИ, функцията SUMPRODUCT може да ви помогне. Например, имам продуктов отчет, както е показано на екранната снимка по-долу, сега искам да преброя редовете, където продуктът е „тениска“ или цветът е „черен“. Как да се справя с тази задача в Excel?
Най-добрите инструменти за производителност в офиса
Kutools за Excel - помага ви да се откроите от тълпата
Kutools за Excel разполага с над 300 функции, Гарантираме, че това, от което се нуждаете, е само на един клик разстояние...
Раздел Office - Активиране на четене и редактиране с раздели в Microsoft Office (включително Excel)
- Една секунда за превключване между десетки отворени документи!
- Намалете стотиците кликвания на мишката за вас всеки ден, кажете сбогом на ръката на мишката.
- Увеличава продуктивността ви с 50% при преглеждане и редактиране на множество документи.
- Внася ефективни раздели в Office (включително Excel), точно като Chrome, Edge и Firefox.
