Преброяване на уникални числови стойности въз основа на критерии в Excel
В работния лист на Excel може да страдате от проблем, който брои броя на уникалните числови стойности въз основа на конкретно условие. Например, как мога да преброя уникалните стойности на Qty на продукта „Тениска“ от отчета, както е показано на екранната снимка по-долу? В тази статия ще покажа някои формули за постигане на тази задача в Excel.
- Преброяване на уникални числови стойности въз основа на критерии в Excel 2019, 2016 и по-стари версии
- Преброяване на уникални числови стойности въз основа на критерии в Excel 365
Преброяване на уникални числови стойности въз основа на критерии в Excel 2019, 2016 и по-стари версии
В Excel 2019 и по-стари версии можете да комбинирате функциите SUM, FREQUENCY и IF, за да създадете формула за преброяване на уникални стойности въз основа на критерии, общият синтаксис е:
Array formula, should press Ctrl + Shift + Enter keys together.
- criteria_range: Диапазонът от клетки, който да отговаря на посочените от вас критерии;
- criteria: Условието, въз основа на което искате да преброите уникалните стойности;
- range: Диапазонът на клетките с уникални стойности, които трябва да бъдат преброени.
Моля, приложете формулата по-долу в празна клетка и натиснете Ctrl + Shift + Enter клавиши, за да получите правилния резултат, вижте екранната снимка:
Обяснение на формулата:
=SUM(--(FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)>0))
- IF(A2:A12=E2,C2:C12): Тази IF функция връща стойността в колона C, ако продуктът в колона A е „тениска“, резултатът е масив като този: {FALSE;300;500;FALSE;400;FALSE;300;FALSE;FALSE; FALSE;350}.
- FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)= FREQUENCY({FALSE;300;500;FALSE;400;FALSE;300;FALSE;FALSE;FALSE;350},{200;300;500;350;400;450;300;550;200;260;350}): Функцията FREQUENCE се използва за преброяване на всяка от числовите стойности в списъка с масиви и връща резултата като следния: {0;2;1;1;1;0;0;0;0;0;0;0} .
- --(FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)>0)=--({0;2;1;1;1;0;0;0;0;0;0;0}>0): Тествайте дали всяка стойност в масива е по-голяма от 0 и получете резултата като следния: {FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}. След това двойният отрицателен знак преобразува TRUE и FALSE в 1s и 0s, връщайки масив като този: {0;1;1;1;1;0;0;0;0;0;0;0}.
- SUM(--(FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)>0))=SUM({0;1;1;1;1;0;0;0;0;0;0;0}): Накрая използвайте функцията SUM, за да съберете тези стойности и да получите общото число: 4.
Съвет:
Ако искате да преброите уникалните стойности въз основа на повече от едно условие, просто трябва да добавите други критерии към формулата със знак *:
Преброяване на уникални числови стойности въз основа на критерии в Excel 365
В Excel 365 комбинацията от функциите ROWS, UNIQUE и FILTER може да помогне за преброяването на уникални числови стойности въз основа на критерии, общият синтаксис е:
- range: Диапазонът на клетките с уникални стойности, които трябва да бъдат преброени.
- criteria_range: Диапазонът от клетки, който да отговаря на посочените от вас критерии;
- criteria: Условието, въз основа на което искате да преброите уникалните стойности;
Моля, копирайте или въведете следната формула в клетка и натиснете Въведете ключ за връщане на резултата, вижте екранната снимка:
Обяснение на формулата:
=ROWS(UNIQUE(FILTER(C2:C12,A2:A12=E2)))
- A2:A12=E2: Този израз проверява дали стойността в клетка E2 съществува в диапазона A2:A12 и получава този резултат: {FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}.
- FILTER(C2:C12,A2:A12=E2): Функцията FREQUENCE се използва за преброяване на всяка от числовите стойности в списъка с масиви и връща резултата като следния: {0;2;1;1;1;0;0;0;0;0;0;0} .
- UNIQUE(FILTER(C2:C12,A2:A12=E2))=UNIQUE({300;500;400;300;350}): Тук функцията UNIQUE се използва за извличане на уникални стойности от списъчния масив, за да се получи този резултат: {300;500;400;350}.
- ROWS(UNIQUE(FILTER(C2:C12,A2:A12=E2)))=ROWS({300;500;400;350}): Функцията ROWS връща броя редове въз основа на диапазон от клетки или масив, така че резултатът е: 4.
Съвет:
1. Ако съответстващата стойност не съществува в диапазона от данни, ще получите стойност за грешка, за да замените стойността за грешка с 0, моля, приложете следната формула:
2. За да преброите уникалните стойности въз основа на повече от едно условие, просто трябва да добавите други критерии към формулата със знака * по следния начин:
Използвана относителна функция:
- СУММА:
- Функцията SUM на Excel връща сумата от предоставените стойности.
- ЧЕСТОТА:
- Функцията FREQUENCY изчислява колко често се срещат стойности в диапазон от стойности и след това връща вертикален масив от числа.
- РЕДОВЕ:
- Функцията ROWS връща броя на редовете в дадена препратка или масив.
- ЕДИНСТВЕН ПО РОДА СИ:
- Функцията UNIQUE връща списък с уникални стойности в списък или диапазон.
- ФИЛТЪР:
- Функцията FILTER помага за филтриране на диапазон от данни въз основа на определени от вас критерии.
Още статии:
- Пребройте уникални числови стойности или дати в колона
- Да предположим, че имате списък с числа, които съдържат някои дубликати, сега искате да преброите броя на уникалните стойности или стойностите се появяват само веднъж в списъка, както е показано на екранната снимка по-долу. В тази статия ще говорим за някои полезни формули за бързо и лесно решаване на тази задача в Excel.
- Пребройте всички съвпадения/дубликати между две колони
- Сравняването на две колони с данни и преброяването на всички съвпадения или дубликати в двете колони може да е обичайна задача за повечето от нас. Например, имате две колони с имена, някои имена се появяват както в първата, така и във втората колона, сега искате да преброите всички съвпадащи имена (съвпаденията, разположени навсякъде в двете колони) между две колони, както е показано на екранната снимка по-долу, този урок ще въведе някои формули за постигане на тази цел в Excel.
- Преброяване на броя на клетките, равни на една от много стойности
- Да предположим, че имам списък с продукти в колона A, сега искам да получа общия брой конкретни продукти Apple, Grape и Lemon, които са изброени в диапазон C4:C6 от колона A, както е показано на екранната снимка по-долу. Обикновено в Excel простите функции COUNTIF и COUNTIFS няма да работят в този сценарий. В тази статия ще говоря за това как да решите тази задача бързо и лесно с комбинацията от функции SUMPRODUCT и COUNTIF.
Най-добрите инструменти за производителност в офиса
Kutools за Excel - помага ви да се откроите от тълпата
Kutools за Excel разполага с над 300 функции, Гарантираме, че това, от което се нуждаете, е само на един клик разстояние...
Раздел Office - Активиране на четене и редактиране с раздели в Microsoft Office (включително Excel)
- Една секунда за превключване между десетки отворени документи!
- Намалете стотиците кликвания на мишката за вас всеки ден, кажете сбогом на ръката на мишката.
- Увеличава продуктивността ви с 50% при преглеждане и редактиране на множество документи.
- Внася ефективни раздели в Office (включително Excel), точно като Chrome, Edge и Firefox.
