Пребройте броя на клетките, които не са равни на много стойности в Excel
В Excel можете лесно да получите броя на клетките, които не са равни на конкретна стойност, като използвате функцията COUNTIF, но опитвали ли сте някога да преброите клетките, които не са равни на много стойности? Например, искам да получа общия брой продукти в колона A, но да изключа конкретните елементи в C4:C6, както е показано на екранната снимка по-долу. В тази статия ще представя някои формули за решаване на тази задача в Excel.
- Пребройте броя на клетките, които не са равни на много стойности с формула1
- Пребройте броя на клетките, които не са равни на много стойности с формула2
Пребройте броя на клетките, които не са равни на много стойности с формула1
За да преброите клетки, които не са равни на много зададени стойности, можете да използвате комбинацията от функциите MATCH, ISNA и SUMPRODUCT, общият синтаксис е:
- range: Списъкът с клетки, които искате да преброите;
- exclude_values: Стойностите за игнориране от списъка с диапазони.
1. Въведете или копирайте формулата по-долу в празна клетка, където искате да изведете резултата:
2. След това натиснете Въведете ключ, за да получите правилния резултат, вижте екранната снимка:
Обяснение на формулата:
- MATCH(A2:A13;C2:C4,0;XNUMX): Тази функция MATCH връща позицията на всяка съвпадаща стойност като число и връща стойност за грешка #N/A, когато не е намерена. Това означава, че когато стойностите в A2:A13 са равни на стойностите в C2:C4, ще върне число, в противен случай ще върне грешка #NA. Тук резултатът е: {1;#N/A;#N/A;1;3;2;#N/A; #N/A;3;#N/A; #N/A; #N/A;}.
- ISNA(MATCH(A2:A13,C2:C4,0))= ISNA({1;#N/A;#N/A;1;3;2;#N/A; #N/A;3;#N/A; #N/A; #N/A;}): Тази функция ISNA принуждава стойностите на грешката да станат TRUE и числата да станат FASLE, така че ще получите това: {FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE} .
- ---(ISNA(MATCH(A2:A13,C2:C4,0))))=--({FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE}): Двойният отрицателен оператор -- преобразува всички TRUE в 1 и FALSE в 0 по следния начин: SUMPRODUCT(--(ISNA(MATCH(A2:A13,C2:C4,0))))=SUMPRODUCT({0;1;1 ;0;0;0;1;1;0;1;1;1;}).
- След това функцията SUMPRODUCT се използва за сумиране на числата в масива и ще получи резултата:7.
Пребройте броя на клетките, които не са равни на много стойности с формула2
За да разрешите тази задача в Excel, можете също да приложите комбинацията от функциите COUNTA, SUMPRODUCT и COUNTIF, общият синтаксис е:
- range: Списъкът с клетки, които искате да преброите;
- exclude_values: Стойностите за игнориране от списъка с диапазони.
1. Моля, въведете или копирайте формулата по-долу в празна клетка, за да поставите резултата:
2. След това натиснете Въведете ключ, за да получите резултата, както е показано на следната екранна снимка:
Обяснение на формулата:
- COUNTA(A2:A13): Тази функция COUNTA връща броя на всички непразни клетки в диапазон A2:A13. И ще получи резултата:12.
- COUNTIF(A2:A13;C2:C4): Тази функция COUNTIF преброява клетките в диапазон A2:A13, които са равни на критерия в C2:C4, така че ще получите този резултат: {2;1;2}, което означава, че Apple се появява два пъти, Grape се появява веднъж и Lemon се появява два пъти.
- SUMPRODUCT(COUNTIF(A2:A13,C2:C4))=SUMPRODUCT({2;1;2}): Тази функция SUMPRODUCT сумира всички елементи в масива и връща краен резултат:5.
- COUNTA(A2:A13)-SUMPRODUCT(COUNTIF(A2:A13,C2:C4))=12-5 : Резултатът (5) от функцията SUMPRODUCT се изважда от броя на всички непразни клетки (12), за да получите крайния резултат, от който се нуждаете.
Забележка: И двете горни формули не са чувствителни към главни и малки букви.
Използвана относителна функция:
- MATCH:
- Функцията MATCH търси конкретна стойност в диапазон от клетки и връща относителната позиция на тази стойност.
- ISNA:
- Функцията ISNA се използва за проверка дали дадена формула връща грешка #N/A.
- SUMPRODUCT:
- Функцията SUMPRODUCT може да се използва за умножаване на две или повече колони или масиви заедно и след това да се получи сумата от продуктите.
- COUNTIF:
- Функцията COUNTIF е статистическа функция в Excel, която се използва за преброяване на броя клетки, които отговарят на критерий.
- COUNTA:
- Функцията COUNTA връща броя на клетките без празни клетки.
Още статии:
- Пребройте броя на клетките, равни на една от многото стойности в Excel
- Да предположим, че имам списък с продукти в колона A, сега искам да получа общия брой конкретни продукти Apple, Grape и Lemon, които са изброени в диапазон C4:C6 от колона A, както е показано на екранната снимка по-долу. Обикновено в Excel простите функции COUNTIF и COUNTIFS няма да работят в този сценарий. В тази статия ще говоря за това как да решите тази задача бързо и лесно с комбинацията от функции SUMPRODUCT и COUNTIF.
- Пребройте броя на клетките между две стойности / дати в Excel
- Опитвали ли сте някога да получите или преброите броя на клетките между две дадени числа или дати в Excel, както е показано на екранната снимка по-долу? Тази статия ще говори за някои полезни формули за справяне с него.
- Пребройте броя на клетките, които не са между две дадени числа в Excel
- Да преброим броя на клетките между две числа е обичайна задача за нас в Excel, но в някои случаи може да искате да преброите клетките не между две дадени числа. Например, имам списък с продукти с продажбите от понеделник до неделя, сега трябва да получа броя на клетките, които не са между конкретните ниски и високи числа, както е показано на екранната снимка по-долу. В тази статия ще представя някои формули за справяне с тази задача в Excel.
Най-добрите инструменти за производителност в офиса
Kutools за Excel - помага ви да се откроите от тълпата
Kutools за Excel разполага с над 300 функции, Гарантираме, че това, от което се нуждаете, е само на един клик разстояние...
Раздел Office - Активиране на четене и редактиране с раздели в Microsoft Office (включително Excel)
- Една секунда за превключване между десетки отворени документи!
- Намалете стотиците кликвания на мишката за вас всеки ден, кажете сбогом на ръката на мишката.
- Увеличава продуктивността ви с 50% при преглеждане и редактиране на множество документи.
- Внася ефективни раздели в Office (включително Excel), точно като Chrome, Edge и Firefox.
