Пребройте липсващите стойности
Да предположим, че имате два списъка, за да преброите общия брой стойности в един списък, които не съществуват в друг списък, можете да използвате SUMPRODUCT формула с помощта на MATCH и ISNA функции, или COUNTIF функция.
Пребройте липсващите стойности със SUMPRODUCT, MATCH и ISNA
Пребройте липсващите стойности със SUMPRODUCT и COUNTIF
Пребройте липсващите стойности със SUMPRODUCT, MATCH и ISNA
За да преброите общ брой стойности в списък B, които липсват в списък A както е показано по-горе, можете първо да използвате функцията MATCH, за да върнете масив от относителната позиция на стойностите от списък B в списък A. Ако дадена стойност не съществува в списък A, ще бъде върната грешка #N/A. След това функцията ISNA ще идентифицира грешките #N/A и SUMPRODUCT ще преброи общия брой на грешките.
Общ синтаксис
=SUMPRODUCT(--ISNA(MATCH(range_to_count,lookup_range,0)))
- диапазон_до_броене: Диапазонът, от който да се броят липсващите стойности. Тук се отнася до списък Б.
- диапазон_за_търсене: Диапазонът за сравнение с диапазон_до_броене. Тук се отнася до списък А.
- 0: - match_type 0 принуждава MATCH да извърши точно съвпадение.
За да преброите общ брой стойности в списък B, които липсват в списък A, моля, копирайте или въведете формулата по-долу в клетка H6 и натиснете Въведете за да получите резултата:
=SUMPRODUCT(--ISNA(MATCH(F6: F8,B6: B10,0)))
Обяснение на формулата
=SUMPRODUCT(--ISNA(MATCH(F6:F8,B6:B10,0)))
- MATCH(F6:F8;B6:B10,0;XNUMX): - match_type 0 принуждава функцията MATCH да върне числови стойности, които показват относителните позиции на стойностите в клетките F6 да се F8 в диапазона B6: B10. Ако дадена стойност не съществува в списък A, ще бъде върната грешка #N/A. Така че резултатите ще бъдат в масив като този: {2;3;#N/A}.
- ISNA(MATCH(F6:F8;B6:B10,0;XNUMX)) = ISNA({2;3;#N/A}): ISNA работи, за да разбере дали дадена стойност е грешка „#N/A“ или не. Ако да, функцията ще върне TURE; Ако не, ще върне FALSE. Така че формулата ISNA ще се върне {FALSE;FALSE;TRUE}.
- SUMPRODUCT(--ISNA(MATCH(F6:F8;B6:B10,0;XNUMX))) = SUMPRODUCT(--{FALSE;FALSE;TRUE}): Двойният отрицателен знак преобразува TRUE в 1 и FALSE в 0: {0;1;0}. Тогава функцията SUMPRODUCT връща сумата: 1.
Пребройте липсващите стойности със SUMPRODUCT и COUNTIF
За да преброите общ брой стойности в списък B, които липсват в списък A, можете също да използвате функцията COUNTIF, за да разберете дали дадена стойност съществува в списък A или не с условието „=0“, тъй като 0 ще се генерира, ако стойност липсва. След това SUMPRODUCT ще преброи общия брой липсващи стойности.
Общ синтаксис
=SUMPRODUCT(--(COUNTIF(lookup_range,range_to_count)=0))
- диапазон_за_търсене: Диапазонът за сравнение с диапазон_до_броене. Тук се отнася до списък А.
- диапазон_до_броене: Диапазонът, от който да се броят липсващите стойности. Тук се отнася до списък Б.
- 0: - match_type 0 принуждава MATCH да извърши точно съвпадение.
За да преброите общ брой стойности в списък B, които липсват в списък A, моля, копирайте или въведете формулата по-долу в клетка H6 и натиснете Въведете за да получите резултата:
=SUMPRODUCT(--(COUNTIF(B6: B10,F6: F8)=0))
Обяснение на формулата
=SUMPRODUCT(--(COUNTIF(B6:B10,F6:F8)=0))
- COUNTIF(B6:B10;F6:F8): Функцията COUNTIF преброява броя на срещанията на клетките F6 да се F8 в диапазона B6: B10. Резултатът ще бъде в масив като този: {1;1;0}.
- -(COUNTIF(B6:B10;F6:F8)= 0) = -({1;1;0}= 0): Фрагментът {1;1;0}=0 дава масив TRUE и FALSE {FALSE;FALSE;TRUE}. След това двойният отрицателен знак превръща TRUE в 1s и FALSE в 0s. Крайният масив е така: {0;0;1}.
- SUMPRODUCT(-(COUNTIF(B6:B10;F6:F8)= 0)) = SUMPRODUCT({0;0;1}): След това SUMPRODUCT връща сумата: 1.
Свързани функции
В Excel функцията SUMPRODUCT може да се използва за умножаване на две или повече колони или масиви заедно и след това да се получи сумата от продуктите. Всъщност SUMPRODUCT е полезна функция, която може да помогне за преброяване или сумиране на стойности на клетки с множество критерии като функцията COUNTIFS или SUMIFS. Тази статия ще представи синтаксиса на функцията и някои примери за тази функция SUMPRODUCT.
Функцията MATCH на Excel търси конкретна стойност в диапазон от клетки и връща относителната позиция на стойността.
Функцията COUNTIF е статистическа функция в Excel, която се използва за преброяване на броя клетки, които отговарят на критерий. Поддържа логически оператори (<>, =, > и <) и заместващи знаци (? и *) за частично съвпадение.
Свързани формули
Има случаи, когато трябва да сравните два списъка, за да проверите дали стойност от списък A съществува в списък B в Excel. Например, имате списък с продукти и искате да проверите дали продуктите във вашия списък съществуват в списъка с продукти, предоставен от вашия доставчик. За да изпълните тази задача, тук сме изброили три начина по-долу, не се колебайте да изберете този, който харесвате.
Тази статия ще се съсредоточи върху формулите на Excel за преброяване на клетките, които са точно равни на зададен от вас текстов низ или частично равни на дадения текстов низ, както са показани екранните снимки по-долу. Първо, ще обясни синтаксиса и аргумента на формулата, предоставя примери, които да разберете по-добре.
Пребройте броя на клетките, които не са между две дадени числа
Да преброим броя на клетките между две числа е обичайна задача за нас в Excel, но в някои случаи може да искате да преброите клетките не между две дадени числа. Например, имам списък с продукти с продажбите от понеделник до неделя, сега трябва да получа броя на клетките, които не са между конкретните ниски и високи числа, както е показано на екранната снимка по-долу. В тази статия ще представя някои формули за справяне с тази задача в Excel.
Най-добрите инструменти за производителност в офиса
Kutools за Excel - помага ви да се откроите от тълпата
Kutools за Excel разполага с над 300 функции, Гарантираме, че това, от което се нуждаете, е само на един клик разстояние...
Раздел Office - Активиране на четене и редактиране с раздели в Microsoft Office (включително Excel)
- Една секунда за превключване между десетки отворени документи!
- Намалете стотиците кликвания на мишката за вас всеки ден, кажете сбогом на ръката на мишката.
- Увеличава продуктивността ви с 50% при преглеждане и редактиране на множество документи.
- Внася ефективни раздели в Office (включително Excel), точно като Chrome, Edge и Firefox.
