По -рано научихме как да броим уникални стойности в диапазон. Научихме и как да извличаме уникални стойности от диапазон. В тази статия ще научим как да броим уникална стойност в диапазон с условие в excel.
Обща формула
{= SUM (-(FREQUENCY (IF (условие, MATCH (диапазон, диапазон, 0)), ROW (диапазон) -ROW (първа клетка в обхвата) +1)> 0))}
Това е формула за масив, използвайте CTRL+SHIFT+ENTER
Състояние : Критериите, по които искате да получите уникални стойности.
Обхват : диапазон, в който искате да получите уникални стойности.
firstCell в обхвата: Това е препратката към първата клетка в диапазон. Ако диапазонът е A2: A10, това е A2.
Пример:
Тук имам тези данни за имена. Съответните класове са споменати в съседната колона. Трябва да преброим уникални имена във всеки клас.
Използвайки горната обща формула, напишете тази формула в E2
{= SUM (-(FREQUENCY (IF (B2: B19 = "Class 1", MATCH (A2: A19, A2: A19,0)), ROW (A2: A19) -ROW (A2) +1)> 0 ))}
Горната формула връща уникалната стойност в Excel диапазон A2: A19 при условие B2: B19 = "Клас 1".
За да получите уникални стойности в различни класове, променете критериите. Тук сме го кодирали твърдо, но можете да дадете и препратка към клетка. Използвайте наименувани диапазони или абсолютни препратки към диапазони, ако не искате те също да се променят.
Как работи?
Нека го разбием отвътре.
АКО(B2: B19 = "Клас 1",СЪВПАДА(A2: A19, A2: A19,0))
B2: B19 = "Клас 1": Тази част ще връща масив от true и false. ИСТИНА за всеки мач.
{TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE….}
СЪВПАДА(A2: A19, A2: A19,0): тази част ще върне първото местоположение на всяка стойност в диапазона A2: A19 според собствеността на MATCH.
{1;2;1;4;5;4;1;8;9;1;2;1;4;5;4;1;8;9}.
Сега за всяка TRUE стойност ще получим позицията, а за false ще получим FALSE. Така че за цялото изявление IF ще получим
{1; FALSE; 1; FALSE; 5; 4; FALSE; FALSE; FALSE; FALSE; 2; FALSE; FALSE; 5; FALSE; 1; 8; FALSE}.
След това преминаваме към честотната част.
ЧЕСТОТА(АКО(B2: B19 = "Клас 1",СЪВПАДА(A2: A19, A2: A19,0)),ROW(A2: A19)-ROW(A2) +1)
ROW (A2: A19): Това връща номера на реда на всяка клетка в диапазон A2: A19.
{2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}
ROW (A2: A19) -ROW (A2): Сега изваждаме номера на първия ред от всеки номер на ред. Това връща масива със сериен номер, започващ от 0.
{0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17}
Тъй като искаме да имаме сериен номер, започващ от 1, добавяме 1 към него.
ROW (A2: A19) -ROW (A2) +1. Това ни дава масив от сериен номер, започващ от 1.
{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18}
Това ще ни помогне да получим уникален брой при условие.
Сега имаме: ЧЕСТОТА({1; FALSE; 1; FALSE; 5; 4; FALSE; FALSE; FALSE; FALSE; 2; FALSE; FALSE; 5; FALSE; 1; 8; FALSE},{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18})
Това връща честотата на всяко число в даден масив. {3; 1; 0; 1; 2; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}
Тук всяко положително число показва поява на уникална стойност, когато критериите са изпълнени. Трябва да броим стойности, по -големи от 0 в този масив. За това проверяваме с> 0. Това ще върне TRUE и FALSE. Преобразуваме истинско невярно с помощта на - (двоен двоичен оператор).
SUM (--({3;1;0;1;2;0;0;1;0;0;0;0;0;0;0;0;0;0;0})>0) това означава SUM ({1; 1; 0; 1; 1; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0})
И накрая получаваме уникалния брой имена в диапазона по критерии като 5.
Знам, че е малко сложно за разбиране, но го проверявате от опцията за оценка на формулата.
За да броим уникални стойности с множество критерии, можем да използваме логическа логика:
Пребройте уникалната стойност с множество критерии и логика
{= SUM (-(FREQUENCY (IF (condition1 * Condition2, MATCH (range, range, 0)), ROW (range) -ROW (firstCell in range) +1)> 0))}
Горната формула може да брои уникални стойности при множество условия и когато всички те са верни.
Пребройте уникалната стойност с множество критерии с или логика
{= SUM (-(FREQUENCY (IF (condition1 + Condition2, MATCH (range, range, 0)), ROW (range) -ROW (firstCell in range) +1)> 0))}
Тази обща формула може да се използва за преброяване на уникални стойности с логика Or. Това означава, че ще се брои, ако някое от условията е вярно.
Така че да, момчета, по този начин броите уникални стойности в диапазон при множество условия. Това е малко сложно, но е бързо. След като започнете да го използвате, ще разберете как работи.
Ако имате някакви съмнения относно тази статия с формула на Excel, уведомете ме в секцията за коментари по -долу.
Свали файл:
Как да броим уникални стойности в Excel с критерииФормула на Excel за извличане на уникални стойности от списък
Пребройте уникалните стойности в Excel
Популярни статии:
Функцията VLOOKUP в Excel
COUNTIF в Excel 2016
Как да използвате функцията SUMIF в Excel
