Как посчитать уникальные значения в Excel (эксель)

=СЧЁТЗ(УНИК(A2:A9)) считает, сколько разных значений в A2:A9. УНИК (по-английски UNIQUE) возвращает каждое значение один раз, а СЧЁТЗ (COUNTA) считает этот список. Нужен Excel 2021 или Microsoft 365; старые версии разобраны ниже. В таблицах на этой странице формулы записаны по-английски, но их можно вводить и по-русски, с точкой с запятой.

ФормулыСбросить

D2fx

=COUNTA(UNIQUE(A2:A9))

	A	B	C	D
1	Customer		Unique list	Count
2	Ana		Ana	5
3	Ben		Ben
4	Ana		Cara
5	Cara		Dan
6	Ben		Eva
7	Dan
8	Ana
9	Eva

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =СЧЁТЗ(УНИК(A2:A9))

Восемь заказов пришли от пяти клиентов. C2 выводит список имён через УНИК, чтобы было видно, что считается, а D2 считает его, не выводя список на лист. Замените A9 на Ana, и количество упадёт до 4; введите новое имя, и оно вырастет.

УНИК не учитывает регистр, поэтому Ana и ana считаются одним клиентом.

Уникальные значения в старых версиях Excel

В Excel 2019 и более ранних нет УНИК. Классическая формула такая:

=SUMPRODUCT(1/COUNTIF(A2:A9,A2:A9))

В русском Excel: =СУММПРОИЗВ(1/СЧЁТЕСЛИ(A2:A9;A2:A9)). СЧЁТЕСЛИ, у которой критерием служит весь диапазон, возвращает для каждой строки, сколько раз встречается значение этой строки. Имя, которое встречается 3 раза, получает 3 в каждой своей строке, поэтому 1/3 прибавляется три раза, и имя в сумме даёт ровно 1. Столбец B показывает количество для каждой строки, а столбец C дробь.

ФормулыСбросить

E2fx

=SUMPRODUCT(1/COUNTIF(A2:A9,A2:A9))

	A	B	C	D	E
1	Customer	Times	1/Times		Count
2	Ana	3	0.33		5
3	Ben	2	0.50		5.00
4	Ana	3	0.33
5	Cara	1	1.00
6	Ben	2	0.50
7	Dan	1	1.00
8	Ana	3	0.33
9	Eva	1	1.00

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =СУММПРОИЗВ(1/СЧЁТЕСЛИ(A2:A9;A2:A9))

Три строки Ana прибавляют по 0,33, две строки Ben по 0,50, а три имени, которые встречаются один раз, по 1: всего 5, как и СУММ вспомогательного столбца. На десятках тысяч строк эта формула медленная, потому что СЧЁТЕСЛИ просматривает весь диапазон один раз на каждую строку; у УНИК такой платы нет.

Различные и уникальные: значения, которые встречаются один раз

Словом «уникальные» называют два разных подсчёта. Подсчёт выше считает различные значения: каждое имя один раз. Другой считает значения, которые встречаются ровно один раз, например клиентов, сделавших только один заказ. УНИК делает это, если её третий аргумент, exactly_once, равен ИСТИНА.

ФормулыСбросить

D3fx

=COUNTA(UNIQUE(A2:A9,,TRUE))

	A	B	C	D
1	Customer		Count	Result
2	Ana		Distinct	5
3	Ben		Exactly once	3
4	Ana		Exactly once, older Excel	3
5	Cara
6	Ben
7	Dan
8	Ana
9	Eva

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =СЧЁТЗ(УНИК(A2:A9;;ИСТИНА))

Различных клиентов пять, но только трое из них, Cara, Dan и Eva, сделали один заказ. Вариант для старого Excel считает строки, где СЧЁТЕСЛИ равна ровно 1. Если все значения повторяются, УНИК с exactly_once возвращает #ВЫЧИСЛ! (по-английски #CALC!, так ошибки показывают таблицы на этой странице), и СЧЁТЗ считает эту ошибку как 1; вариант с СУММПРОИЗВ даёт 0.

Уникальные значения по условию

Чтобы посчитать разных клиентов в одном регионе, сначала отфильтруйте строки, а потом считайте оставшееся. ФИЛЬТР оставляет строки North, а УНИК убирает повторы.

ФормулыСбросить

E2fx

=COUNTA(UNIQUE(FILTER(A2:A9,B2:B9=D2)))

	A	B	C	D	E
1	Customer	Region		Region	Customers
2	Ana	North		North	3
3	Ben	South		South	3
4	Ana	North		North, older Excel	3
5	Cara	North
6	Ben	North
7	Dan	South
8	Ana	North
9	Eva	South

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =СЧЁТЗ(УНИК(ФИЛЬТР(A2:A9;B2:B9=D2)))

В North пять заказов от трёх клиентов: Ana, Cara и Ben. E3 так же считает South. E4 это вариант для Excel 2019 и более ранних: СЧЁТЕСЛИМН считает каждую пару из клиента и региона, а условие оставляет только дроби North.

Если ни одна строка не подходит, ФИЛЬТР возвращает #ВЫЧИСЛ!, и СЧЁТЗ считает эту ошибку одним значением: введите West в D2, и E2 покажет 1, а не 0. Обёртка в ЕСЛИОШИБКА не помогает, потому что СЧЁТЗ ошибку не возвращает. Вместо этого считайте строки результата, а ЧСТРОК ошибку передаёт дальше: =ЕСЛИОШИБКА(ЧСТРОК(УНИК(ФИЛЬТР(A2:A9;B2:B9="West")));0) возвращает 0.

Уникальные значения без пустых ячеек

Пустая ячейка в диапазоне становится ещё одним «значением». УНИК возвращает её как 0, и СЧЁТЗ считает этот 0, поэтому для Ana, пустой ячейки, Ben, Ana, пустой ячейки, Cara и Ben Excel даёт:

=COUNTA(UNIQUE(A2:A8))    4   three names plus the 0 for the empty cells

В старой формуле пустая строка заставляет СЧЁТЕСЛИ вернуть 0, и 1/0 даёт #ДЕЛ/0! (#DIV/0!). Сначала уберите пустые ячейки:

ФормулыСбросить

D2fx

=COUNTA(UNIQUE(FILTER(A2:A8,A2:A8<>"")))

	A	B	C	D
1	Customer		Formula	Count
2	Ana		Skip blanks	3
3			Older Excel	3
4	Ben
5	Ana
6
7	Cara
8	Ben

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =СЧЁТЗ(УНИК(ФИЛЬТР(A2:A8;A2:A8<>"")))

Обе формулы считают трёх клиентов. ФИЛЬТР с A2:A8<>"" убирает пустые ячейки до того, как их увидит УНИК. В старой формуле A2:A8&"" превращает каждую пустую ячейку в пустую строку, поэтому СЧЁТЕСЛИ никогда не возвращает 0, а (A2:A8<>"") даёт этим строкам вес 0.

Практика: посчитать товары

ФормулыСбросить

E2fx

	A	B	C	D	E
1	Order	Product		Count	Result
2	1001	Apple		Products
3	1002	Pear
4	1003	Apple
5	1004	Plum
6	1005	Pear
7	1006	Apple
8	1007	Plum
9	1008	Fig

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.

Ваша очередь: Посчитайте, сколько разных товаров встречается в B2:B9. Напишите формулу в E2.

ПроверитьПоказать ответ

Какая формула подходит вашему Excel

В сводной таблице то же самое без формулы делает итог «Число различных элементов», но только если сводная таблица создана с флажком «Добавить эти данные в модель данных». Чтобы удалить повторы, а не считать их, см. удаление дубликатов.