Пребройте броя на клетките, които не съдържат много стойности
Обикновено е лесно да преброите клетки, които не съдържат една конкретна стойност с функцията COUNTIF. Тази публикация ще разгледа как да преброите броя на клетките, които не съдържат много стойности в определен диапазон в Excel.
Как да преброим клетки, които не съдържат много стойности?
Както е показано на екранната снимка по-долу, за да преброите клетки в B3:B11, които не съдържат стойности, изброени в D3:D4, можете да направите следното.
Генерична формула
{=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(criteria_range),range))),ROW(criteria_range)^0)>0))}
Аргументи
Обхват (задължително): диапазонът, от който искате да преброите клетки, които не съдържат много стойности.
Диапазон_на_критерия (задължително): диапазонът съдържа стойностите, които искате да изключите, когато броите клетки.
Забележка: Тази формула трябва да се въведе като формула за масив. След прилагане на формулата, ако има къдрави скоби, увити около формулата, формулата за масив е създадена успешно.
Как да използвате тази формула?
1. Изберете празна клетка, за да изведете резултата.
2. Въведете формулата по-долу в него и след това натиснете Ctrl + Превключване + Въведете клавиши едновременно, за да получите резултата.
=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))),ROW(D3:D4)^0)>0))
Как работят тези формули?
=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))),ROW(D3:D4)^0)>0))
1) --(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))):
- TRANSPOSE(D3:D4): Функцията TRANSPOSE завърта ориентацията на D3:D4 и се връща {“брой”,”празно”};
- SEARCH({“count”,”blank”},B3:B11): Функцията SEARCH тук намира позицията на подниза „count“ и „blank“ от диапазон B3: B11 и връща масив като {#VALUE!,#VALUE!;#VALUE!,#VALUE!;1,#VALUE!;#VALUE!,8;1,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE! ;1,
#VALUE!;1,7}. - В този случай всяка клетка в B3:B11 ще бъде претърсена два пъти, тъй като има две стойности, които искате да изключите при преброяването на клетките, така че ще получите 18 стойности в масива. Всяко число в масива показва позицията на първия знак от „count“ или „blank“ във всяка клетка на B3:B11.
- ISNUMBER{#VALUE!,#VALUE!;#VALUE!,#VALUE!;1,#VALUE!;#VALUE!,8;1,#VALUE!;#VALUE!,#VALUE!;#VALUE!,
#VALUE!;1,#VALUE!;1,7}: Функцията ISNUMBER връща TRUE, ако среща числа в масива, и връща FALSE, ако среща грешки. Тук връща резултата като {FALSE,FALSE;FALSE,FALSE;TRUE,FALSE;FALSE,TRUE;TRUE,FALSE;FALSE,FALSE;FALSE,FALSE;TRUE,FALSE;
ВЯРНО ВЯРНО}. - --({FALSE,FALSE;FALSE,FALSE;TRUE,FALSE;FALSE,TRUE;TRUE,FALSE;FALSE,FALSE;FALSE,FALSE;TRUE,
FALSE;TRUE,TRUE}): Тези два знака минус преобразуват „TRUE“ в 1 и преобразуват „FALSE“ в 0. Тук ще получите нов масив като {0,0;0,0;1,0;0,1;1,0;0,0;0,0;1,0;1,1}.
2) ROW(D3:D4)^0: Функцията ROW връща номерата на редовете на препратката към клетката: {3;4} и след това операторът за степенуване (^) връща резултата от числата 3 и 4, повдигнати на степен 0, и накрая връща резултата като {1;1}.
3) MMULT({0,0;0,0;1,0;0,1;1,0;0,0;0,0;1,0;1,1},{1;1}):Функцията MMULT връща матричния продукт на тези два масива: {0;0;1;1;1;0;0;1;2}, за да отговори на оригиналните данни. Всяко различно от нула число в масива представлява стойност, при която е намерен поне един от изключените низове, а нулата показва, че не са намерени изключени низове.
4) SUM(1-{0;0;1;1;1;0;0;1;2}>0):
- {0;0;1;1;1;0;0;1;2}>0: Тук се проверява дали всяко число в масива е по-голямо от 0. Ако дадено число е по-голямо от 0, връща TRUE, ако не, връща FALSE. След това ще получите нов масив като {FALSE;FALSE;TRUE;TRUE;TRUE;FALSE,FALSE,TRUE;TRUE}.
- 1-{FALSE;FALSE;TRUE;TRUE;TRUE;FALSE,FALSE,TRUE;TRUE}:Тъй като трябва да преброим само клетките, които не съдържат посочените стойности, трябва да обърнем тези стойности в масива, като ги извадим от 1. Тук математическият оператор автоматично преобразува стойностите TRUE и FALSE в 1s и 0s и накрая връща резултатът като {1;1;0;0;0;1;1;0;0}.
- SUM{1;1;0;0;0;1;1;0;0}: Функцията SUM сумира всички числа в масива и връща крайния резултат като 4.
Свързани функции
Функция SUM на Excel
Функцията SUM на Excel добавя стойности.
Функция MMULT на Excel
Функцията MMULT на Excel връща матричното произведение на два масива.
Функция ISNUMBER на Excel
Функцията ISNUMBER на Excel връща TRUE, когато клетка съдържа число, и FALSE, ако не.
Функция TRANSPOSE на Excel
Функцията TRANSPOSE на Excel завърта ориентацията на диапазон или масив.
Функция РЕД на Excel
Функцията Excel Row връща номера на реда на справка.
Свързани формули
Пребройте клетките, които не съдържат грешки
Този урок предоставя подробни стъпки, които да ви помогнат да преброите броя на клетките, които не съдържат грешки в определен диапазон в Excel.
Пребройте клетките, които не съдържат конкретен текст
Можете да използвате функцията COUNTIF със заместващ знак, за да преброите броя на клетките, които съдържат определен текст в диапазон. Напротив, също така е лесно да използвате функцията COUNTIF за преброяване на клетки, които не съдържат конкретен текст. Този урок предоставя подробни стъпки за решаване на проблема.
Пребройте броя на клетките с дата в диапазон по ден от седмицата
Excel предоставя комбинацията от функциите SUMPRODUCT и WEEKDAY, за да ни помогне лесно да преброим броя на посочените дни от седмицата в диапазон. Този урок предоставя ръководство стъпка по стъпка, за да ви помогне да преминете през него.
Пребройте броя на текстовите клетки
За да преброите броя на клетките, които съдържат текст в определен диапазон, функцията COUNTIF може да ви помогне лесно да го направите. Тук тази статия ще представи формулата в подробности, за да ви помогне да разрешите проблема.
Най-добрите инструменти за производителност в офиса
Kutools за Excel - помага ви да се откроите от тълпата
Kutools за Excel разполага с над 300 функции, Гарантираме, че това, от което се нуждаете, е само на един клик разстояние...
Раздел Office - Активиране на четене и редактиране с раздели в Microsoft Office (включително Excel)
- Една секунда за превключване между десетки отворени документи!
- Намалете стотиците кликвания на мишката за вас всеки ден, кажете сбогом на ръката на мишката.
- Увеличава продуктивността ви с 50% при преглеждане и редактиране на множество документи.
- Внася ефективни раздели в Office (включително Excel), точно като Chrome, Edge и Firefox.
