Как подсчитать уникальные значения в Excel с критериями?

Posted on: 2020年10月22日 2020年11月12日
Tags: counting

0049

Ранее мы узнали, как подсчитывать уникальные значения в диапазоне. Мы также узнали, как извлекать уникальные значения из диапазона. В этой статье мы узнаем, как подсчитать уникальное значение в диапазоне с условием в Excel.

Общая формула

{=SUM(--(FREQUENCY(IF(condition,MATCH(range,range,0)),ROW(range)-ROW(firstCell in range)+1)>0))}

Это формула массива, используйте CTRL + SHIFT + ENTER Условие: критерии, по которым вы хотите получить уникальные значения.

Диапазон: диапазон, в котором вы хотите получить уникальные значения.

firstCell в диапазоне: это ссылка на первую ячейку в диапазоне.

Если диапазон A2: A10, то это A2.

Пример:

Вот эти данные имен. Соответствующие классы указаны в соседнем столбце. Нам нужно подсчитать уникальные имена в каждом классе.

0050

Используя приведенную выше общую формулу, запишите эту формулу в 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».

0051

Чтобы получить уникальные значения в разных классах, измените критерии. Мы жестко запрограммировали его здесь, но вы также можете указать ссылку на ячейку. Используйте именованные диапазоны или абсолютные ссылки для диапазонов, если вы не хотите, чтобы они тоже менялись.

Как это работает?

Разберем его изнутри.

link: / tips-if-condition-in-excel [IF] (B2: B19 = «Class 1», link: / lookup-formulas-excel-match-function [MATCH] (A2: A19, A2 : A19,0)) B2: B19 = «Класс 1» *: Эта часть возвращает массив истинных и ложных значений.

ИСТИНА для каждого матча.

\ {ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ИСТИНА; ИСТИНА; ЛОЖЬ….} Link: / lookup-formulas-excel-match-function [MATCH] (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}.

Далее мы переходим к частотной части.

link: / statistics-formulas-excel-frequency-function-2 [FREQUENCY] (link: / tips-if-condition-in-excel [IF] (B2: B19 = «Class 1», link: / lookup-formulas-excel-match-function [MATCH] `(A2: A19, A2: A19,0)), link: / lookup-and-reference-excel-row-function [ROW] (A2: A19 ) -`link: / lookup-and-reference-excel-row-function [ROW] `(A2) +1) link: / lookup-and-reference-excel-row-function [ROW] `(A2: A19 ): * Это возвращает номер строки каждой ячейки в диапазоне A2: A19.

\ {2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19} ссылка: / lookup-and-reference-excel- функция-строка [ROW] `(A2: A19) -`link: / lookup-and-reference-excel-row-function [ROW] (A2):

Теперь мы вычитаем номер первой строки из каждого номера строки. Это возвращает массив серийных номеров, начиная с 0.

\ {0; 1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17} Поскольку мы хотим, чтобы серийный номер начинался с 1, мы добавить к нему 1.

link: / lookup-and-reference-excel-row-function [ROW] (A2: A19) -`link: / lookup-and-reference-excel-row-function [ROW] `(A2) +1.

Это дает нам массив серийных номеров, начиная с 1.

\ {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18} Это поможет нам получить уникальное количество при условии.

Теперь у нас есть:

ссылка: / статистические-формулы-excel-частота-функция-2 [ЧАСТОТА] (\ {1; ЛОЖЬ; 1; ЛОЖЬ; 5; 4; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; 2; ЛОЖЬ; ЛОЖЬ; 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 в этом массиве.

Для этого проверяем на> 0. Это вернет ИСТИНА и ЛОЖЬ. Мы конвертируем истину в ложь, используя — (двойной бинарный оператор).

link: / math-and-trig-excel-sum-function [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))}

Эту универсальную формулу можно использовать для подсчета уникальных значений с помощью логики Или.

Это означает, что он будет засчитан, если выполняется какое-либо из условий.

Так что да, ребята, вот как вы подсчитываете уникальные значения в диапазоне при нескольких условиях. Это немного сложно, но быстро. Как только вы начнете его использовать, вы поймете, как это работает.

Если у вас есть какие-либо сомнения относительно этой статьи о формуле Excel, дайте мне знать в разделе комментариев ниже.

Статьи по теме:

link: / lookup-formulas-excel-formula-to-extract-unique-values-from-a-list [Формула Excel для извлечения уникальных значений из списка]

link: / counting-count-unique-values-in-excel [Подсчет уникальных значений в Excel]