Прескочи на основното съдържание

Преброяване на уникални стойности с критерии в Excel

Автор: Силувия Последна промяна: 2021-09-24

За да преброите само уникални стойности въз основа на определени критерии в друга колона, можете да приложите формула за масив въз основа на функциите SUM, FREQUENCY, MATCH и ROW. Това ръководство стъпка по стъпка ви помага да преминете през най-нервната употреба на формулата.

Как да броим уникални стойности с критерии в Excel?

Както е показано в таблицата с продукти по-долу, има някои дублирани продукти, продадени от един и същи магазин на различни дати, сега искам да получа уникалния брой на продукта, който е продаден от магазин А, можете да приложите формулата по-долу.

Генерични формули

{=SUM(--(FREQUENCY(IF(range=criteria,MATCH(vals,vals,0)),ROW(vals)-ROW(vals.firstcell)+1)>0))}

Аргументи

Обхват: Диапазонът от клетки съдържа стойността, която спрямо критериите;
Критерии: Критериите, въз основа на които искате да преброите уникалните стойности;
валс: Диапазонът от клетки, от които искате да броите уникални стойности;
Vals.първа клетка: Първата клетка от диапазона, от който искате да преброите уникалните стойности.

Забележка: Тази формула трябва да се въведе като формула за масив. След прилагане на формулата, ако има къдрави скоби, увити около формулата, формулата за масив е създадена успешно.

Как да използвате тези формули?

1. Изберете празна клетка, за да поставите резултата.

2. Въведете формулата по-долу в него и след това натиснете Ctrl + Превключване + Въведете клавиши едновременно, за да получите резултата.

=SUM(--(FREQUENCY(IF(E3:E16=H3,MATCH(D3:D16,D3:D16,0)),ROW(D3:D16)-ROW(D3)+1)>0))

бележки: В тази формула E3:E16 е диапазонът, който съдържа стойността, която спрямо критериите, H3 съдържа критериите, D3:D16 е диапазонът, който съдържа уникалните стойности, които искате да преброите, и D3 е първата клетка на D3:D16. Можете да ги промените според нуждите си.

Как работи тази формула?

{=SUM(--(FREQUENCY(IF(E3:E16=H3,MATCH(D3:D16,D3:D16,0)),ROW(D3:D16)-ROW(D3)+1)>0))}

  • IF(E3:E16=H3,MATCH(D3:D16,D3:D16,0)):
1) E3:E16=H3: Тук проверява дали стойност A съществува в диапазон E3:E16 и връща TRUE, ако бъде намерена, връща FALSE, ако не. Ще получите масив като този {TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;}.
2) MATCH(D3:D16;D3:D16,0;XNUMX): Функцията MATCH получава първото местоположение на всеки елемент в диапазон D3:D16 и връща масив като този {1;2;3;2;1;1;3;2;1;1;1;2;3; 2}.
  • IF({TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;},{1;2;3;2;1;1;3;2;1;1;1;2;3;2}): Сега за всяка стойност TRUE в масив 1 ще получим съответната позиция в масив 2, а за FALSE ще получим FALSE. Тук ще получите нов масив като {1;FALSE;FALSE;2;FALSE;FALSE;3;FALSE;FALSE;1;FALSE;FALSE;3;FALSE}.
  • РЕД(D3:D16)-РЕД(D3)+1: Тук функцията ROW връща номера на реда на препратка D3:D16 и D3 и ще получите {3;4;5;6;7;8;9;10;11;12;13;14;15;16} -{3}+1.
  • Всяко число в масива изважда число 3, след това добавя 1 и накрая връща {1;2;3;4;5;6;7;8;9;10;11;12;13;14}.
  • FREQUENCY({1;FALSE;FALSE;2;FALSE;FALSE;3;FALSE;FALSE;1;FALSE;FALSE;3;FALSE},{1;2;3;4;5;6;7;8;9;10;11;12;13;14}): Тук функцията FREQUENCY връща честотата на всяко число в даден масив: {2;1;2;0;0;0;0;0;0;0;0;0;0;0;XNUMX}.
  • =SUM(--({2;1;2;0;0;0;0;0;0;0;0;0;0;0}>0)):
1) {2;1;2;0;0;0;0;0;0;0;0;0;0;0}>0: Всяко число в масива се сравнява с 0 и връща TRUE, ако е по-голямо от 0, в противен случай връща FALSE. И ще получите TRUE FALSE масив като този {TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE};
2) --{ВЯРНО;ВЯРНО;ВЯРНО;НЕВЯРНО;НЕВЯРНО;НЕВЯРНО;ВЯРНО;ВЯРНО;ВЯРНО;ВЯРНО;ВЯРНО;ВЯРНО;ВЯРНО;ВЯРНО}: Тези два знака минус преобразуват „TRUE“ в 1 и „FALSE“ в 0. Тук ще получите нов масив като {1;1;1;0;0;0;0;0;0;0;0;0 ;0;0}.
3) SUM{1;1;1;0;0;0;0;0;0;0;0;0;0;0}: Функцията SUM сумира всички числа в масива и връща крайния резултат като 3.

Свързани функции

Функция SUM на Excel
Функцията SUM на Excel добавя стойности

Функция FREQUENCY на Excel
Функцията FREQUENCY на Excel изчислява колко често се срещат стойности в диапазон от стойности и след това връща вертикален масив от числа.

Excel IF функция
Функцията IF на Excel извършва прост логически тест, който в зависимост от резултата от сравнението и връща една стойност, ако резултатът е TRUE, или друга стойност, ако резултатът е FALSE.

Excel MATCH функция
Функцията MATCH на Excel търси конкретна стойност в диапазон от клетки и връща относителната позиция на тази стойност.

Функция РЕД на Excel
Функцията Excel Row връща номера на реда на справка.

Свързани формули

Преброяване на броя на видимите редове във филтриран списък
Този урок обяснява как да преброите броя на видимите редове във филтриран списък в Excel с функцията SUBTOTAL.

Преброяване на уникални стойности в диапазон
Този урок обяснява как да преброите само уникалните стойности сред дубликати в списък в Excel със зададени формули.

Преброяване на видимите редове с критерии
Този урок предоставя подробни стъпки, които да ви помогнат да преброите видимите редове с критерии.

Използвайте COUNTIF в несъседен диапазон
Това ръководство стъпка по стъпка демонстрира как да използвате функцията countif в несъседен диапазон в Excel.

Най-добрите инструменти за производителност в офиса

Kutools за Excel - помага ви да се откроите от тълпата

🤖 Kutools AI помощник: Революционизирайте анализа на данни въз основа на: Интелигентно изпълнение   |  Генериране на код  |  Създаване на персонализирани формули  |  Анализирайте данни и генерирайте диаграми  |  Извикване на функциите на Kutools...
Популярни функции: Намерете, маркирайте или идентифицирайте дубликати  |  Изтриване на празни редове  |  Комбинирайте колони или клетки без загуба на данни  |  Кръг без формула ...
Супер VLookup: Множество критерии  |  Множество стойности  |  В много листове  |  Размито търсене...
адв. Падащ списък: Лесен падащ списък  |  Зависим падащ списък  |  Падащ списък с множество избори...
Мениджър на колони: Добавете конкретен брой колони  |  Преместване на колони  |  Превключване на състоянието на видимост на скритите колони  |  Сравнете колони с Изберете Същите и различни клетки ...
Препоръчани функции: Мрежов фокус  |  Изглед на дизайна  |  Голям формула бар  |  Мениджър на работни книги и листове | Библиотека с ресурси (Автоматичен текст)  |  Избор на дата  |  Комбинирайте работни листове  |  Шифроване/декриптиране на клетки  |  Изпращайте имейли по списък  |  Супер филтър  |  Специален филтър (филтър получер/курсив/зачертано...) ...
Топ 15 комплекта инструменти:  12 Текст Инструменти (добавяне на текст, Премахване на символи ...)  |  50 + Графика Видове (диаграма на Гант ...)  |  40+ Практичен формули (Изчислете възрастта въз основа на рождения ден ...)  |  19 вмъкване Инструменти (Въведете QR код, Вмъкване на картина от пътя ...)  |  12 Конверсия Инструменти (Числа към думи, Валутен обмен ...)  |  7 Обединяване и разделяне Инструменти (Разширено комбиниране на редове, Разделете клетки на Excel ...)  |  ... и още

Kutools за Excel разполага с над 300 функции, Гарантираме, че това, от което се нуждаете, е само на един клик разстояние...

Описание

Раздел Office - Активиране на четене и редактиране с раздели в Microsoft Office (включително Excel)

  • Една секунда за превключване между десетки отворени документи!
  • Намалете стотиците кликвания на мишката за вас всеки ден, кажете сбогом на ръката на мишката.
  • Увеличава продуктивността ви с 50% при преглеждане и редактиране на множество документи.
  • Внася ефективни раздели в Office (включително Excel), точно като Chrome, Edge и Firefox.
Comments (1)
No ratings yet. Be the first to rate!
Justine
Justine
This comment was minimized by the moderator on the site
Looking for the same formula but with one more criteria... I tried adding AND() after the IF() to have my two criterias but it didn't work. Do you have a solution?
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations