Посчитать уникальные значения в Excel

Группа красных фишек и одна отдельная чёрная фишка

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

В этой статье показаны несколько способов посчитать уникальные значения: быстрый мануальный, формула для старых версий Excel, более простые формулы для динамических массивов, а также варианты через сводную таблицу и Power Query. Приведены советы о совместимости, контрольные тесты и чек-листы для аналитика и администратора.

Когда какой способ выбрать

Нужен быстрый, одноразовый результат: удаление дубликатов на копии листа.
Нужна динамическая метрика, которая обновляется при изменении данных: функции UNIQUE + COUNTA (Excel 365/2021) или формула массива с MATCH/FREQUENCY (старые Excel).
Большие наборы данных или сложные преобразования: Power Query или сводная таблица.

Важно: перед удалением дубликатов всегда работайте с копией листа. Так вы не потеряете исходные данные.

Быстрый способ: удалить дубликаты

Скопируйте столбец на новый лист.
Выделите диапазон или столбец.
На вкладке Данные в разделе Инструменты данных нажмите Удалить дубликаты.

Это быстро, но необратимо для оригинального диапазона (если вы не сохранили копию). Работает и для нескольких столбцов: выделите все столбцы, комбинация значений будет сравниваться построчно.

Удаление дубликатов в Excel

Удаление дубликатов в двух столбцах

Формула для классических версий Excel (массовая формула)

Если вы хотите отслеживать количество уникальных значений автоматически, используйте массивную формулу. Вставьте формулу, охватывающую ваш диапазон, и в старых версиях подтвердите её сочетанием клавиш Ctrl+Shift+Enter.

Формула (в примерах диапазон A2:A13 замените на нужный вам диапазон):

{=SUM(IF(FREQUENCY(MATCH(A2:A13, A2:A13, 0), MATCH(A2:A13, A2:A13, 0))>0,1))}

Ниже объясняю эту формулу по частям простым языком.

Что такое массив в контексте Excel

Один переменный объект, содержащий множество значений. Представьте, что вы ссылаетесь сразу на диапазон A2:A13 — это массив. Новые версии Excel обрабатывают массивы автоматически; в старых моделях нужно подтверждать формулы как массив.

Если у вас старый Excel, после ввода формулы нажмите Ctrl+Shift+Enter. Тогда Excel покажет формулу в фигурных скобках, как в примере выше.

Функция массива в Excel

MATCH — преобразуем значения к позициям

MATCH возвращает позицию первого вхождения значения в массиве. Для текста он возвращает индекс первой ячейки, где встретился элемент.

Пример: MATCH(A2, A2:A13, 0) вернёт 1 если A2 впервые встречается в первом элементе диапазона. Если применить MATCH к массиву A2:A13, вы получите массив индексов первых вхождений для каждого элемента.

MATCH в массиве

FREQUENCY — подсчёт повторов по индексам

FREQUENCY принимает массив значений и массив «бинов» и возвращает, сколько раз каждое значение попадает в соответствующий бин. Если в качестве обоих аргументов передать массив индексов первого вхождения (то есть результат MATCH для всего диапазона), FREQUENCY вернёт для каждого уникального индекса число повторов этого значения в исходном диапазоне.

FREQUENCY в Excel

IF — преобразуем частоты в единицы

IF(FREQUENCY(…)>0,1) заменяет любую частоту больше нуля на 1. Таким образом каждое уникальное значение представлено одной единицей.

IF с массивом FREQUENCY

SUM — считаем уникальные значения

SUM суммирует массив единиц и даёт итоговое число уникальных элементов.

Финиш: SUM вокруг всего выражения

{=SUM(IF(FREQUENCY(MATCH(A2:A13, A2:A13, 0), MATCH(A2:A13, A2:A13, 0))>0,1))}

Современный и самый простой способ (Excel с динамическими массивами)

В Excel 365 и в некоторых версиях Excel 2021 доступна функция UNIQUE, которая возвращает список уникальных значений. Чтобы посчитать их количество, достаточно вложить UNIQUE в COUNTA:

=COUNTA(UNIQUE(A2:A100))

Преимущества: простая, читаемая, автоматически обновляется и не требует подтверждения Ctrl+Shift+Enter.

Ограничения: требует версии Excel с динамическими массивами.

Альтернативы без массивных формул

SUMPRODUCT + COUNTIF: работает в большинстве версий и не требует подтверждения как массива. Пример:

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

Этот приём сработает, если в диапазоне нет пустых ячеек и значения встречаются как текст или числа. Для корректной работы с пустыми ячейками добавляют обработку ошибок или фильтрацию.

COUNTIF с вспомогательным столбцом: в дополнительном столбце отмечаете, является ли текущая строка первой появившейся (например, =IF(COUNTIF($A$2:A2,A2)=1,1,0)) и затем суммируете этот столбец.

Сводная таблица и Power Query

Сводная таблица: перетащите поле в область строк — сводная таблица автоматически агрегирует уникальные значения. В некоторых сценариях можно получить число уникальных элементов через специальное вычисляемое поле или добавив вспомогательную колонку.
Power Query: импортируйте таблицу в Power Query, используйте Remove Duplicates по нужным столбцам, затем верните результат в лист и посчитайте строки. Power Query удобен для ETL-процессов и больших наборов данных.

Примеры и тестовые случаи

Ниже приведены тестовые сценарии, которые помогут проверить корректность выбранного метода.

Тест 1 — простая текстовая колонка

Исходные данные:

A2: Иван
A3: Мария
A4: Иван
A5: Ольга

Ожидаемый результат: 3 уникальных значения.

Подтверждение:

UNIQUE+COUNTA вернёт 3.
Формула MATCH/FREQUENCY вернёт 3.
SUMPRODUCT вернёт 3.

Тест 2 — пустые ячейки и повторяющиеся

Исходные данные:

A2: Пётр
A3: (пусто)
A4: Пётр
A5: (пусто)

Ожидаемый результат: 1 уникальное значение, если пустые не считаются. Если нужно считать пустые как отдельную категорию — 2.

Рекомендация: заранее решите, учитывать ли пустые ячейки, и адаптируйте формулу. Для исключения пустых в UNIQUE используйте FILTER(UNIQUE(…),UNIQUE(…)<>””), для SUMPRODUCT добавьте условие (A2:A100<>””).

Тест 3 — числовые данные

Для чисел можно использовать те же формулы. MATCH/FREQUENCY работает быстрее с числовыми массивами, потому что не требуется промежуточное преобразование.

Когда формулы не сработают или введут в заблуждение

Если в списке значения, отличающиеся только пробелами или разными регистрами, считаются одинаковыми или разными в зависимости от метода. COUNTIF и MATCH нечувствительны к регистру, но пробелы влияют: “Иван” и “Иван “ — разные строки.
Неявные типы: числа, хранящиеся как текст, и настоящие числа будут считаться разными.
Пустые ячейки: некоторые формулы учитывают их, некоторые — нет; уточняйте поведение заранее.

Важно: приведите данные в консистентный вид перед анализом: TRIM уберёт лишние пробелы, VALUE преобразует текст-числа в числа, UPPER/LOWER нормализуют регистр.

Чек-листы по ролям

Аналитик:

Проверить, нужно ли считать пустые ячейки.
Нормализовать данные (TRIM, UPPER/LOWER, привести числа к числовому типу).
Выбрать метод с учётом частоты обновления и объёма данных.
Написать тесты на нескольких контрольных наборах и проверить согласованность результатов.

Администратор/автор отчёта:

Зафиксировать формулы в документации отчёта.
Если используется Power Query, сохранить шаги трансформации.
Разрешить доступ к исходным данным или версии с историей.

Таблица совместимости методов (кратко)

Excel 365 / Excel 2021: UNIQUE + COUNTA — да.
Excel 2019 и старше: MATCH+FREQUENCY (массив) или SUMPRODUCT+COUNTIF — да.
Power Query: работает в Excel 2010+ (как надстройка) и нативно в более новых версиях.

Маленькая методология: как внедрить подсчёт уникальных значений в отчёт

Оцените объём данных и частоту обновления.
Нормализуйте поля для сравнения (убрать пробелы, привести регистр, привести числа к числовому типу).
Выберите метод: UNIQUE для динамики и читаемости; Power Query для ETL и больших объёмов; формула массива для старых книг.
Протестируйте на контрольных примерах.
Документируйте допущения (учесть ли пустые, регистр, пробелы).
Автоматизируйте проверку корректности (сравнение с эталонным подсчётом раз в неделю).

Критерии приёмки

Формула/процесс возвращает ожидаемое число для тестовых наборов из раздела «Примеры и тестовые случаи».
Результат обновляется при добавлении строки с новым уникальным значением.
Результат не меняется при перезаписи значений эквивалентными вариациями (например, разные пробелы должны быть обработаны по указанному правилу).

Краткий глоссарий (одно предложение каждому термину)

UNIQUE: возвращает массив уникальных значений из диапазона.
COUNTA: считает непустые ячейки.
MATCH: возвращает позицию первого вхождения искомого значения в диапазоне.
FREQUENCY: возвращает распределение частот значений по бинам.
SUMPRODUCT: выполняет суммирование произведений массивов и часто используется для агрегирования без массивных формул.
COUNTIF: считает количество ячеек, соответствующих критерию.

Частые ошибки и как их избежать

Ошибка: формула массива не подтверждена в старой версии Excel.
Решение: нажмите Ctrl+Shift+Enter или используйте альтернативу SUMPRODUCT.
Ошибка: пустые значения учитываются, хотя не должны.
Решение: исключите пустые ячейки при помощи FILTER или добавьте условие (A2:A100<>””) в вычисления.
Ошибка: пробелы приводят к дублированию.
Решение: примените TRIM к входным данным.

Быстрые шаблоны формул (чек-лист формул)

Excel 365/2021 — динамический массив:

=COUNTA(UNIQUE(A2:A100))

Старые Excel — массивная формула (Ctrl+Shift+Enter):

{=SUM(IF(FREQUENCY(MATCH(A2:A13, A2:A13, 0), MATCH(A2:A13, A2:A13, 0))>0,1))}

Универсальная альтернатива без подтверждения массива:

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

Исключить пустые значения при использовании SUMPRODUCT:

=SUMPRODUCT((A2:A100<>")/COUNTIF(A2:A100,A2:A100&""))

Совет: адаптируйте диапазоны, чтобы они соответствовали реальным данным и не включали лишние ячейки.

Итог

Подсчёт уникальных значений в Excel можно решить несколькими путями. Для одноразовой операции вполне подойдёт удаление дубликатов на копии листа. Для динамического учёта в современных версиях Excel используйте UNIQUE + COUNTA. Для совместимости с более старыми версиями подойдёт формула массива с MATCH и FREQUENCY или приёмы с SUMPRODUCT и COUNTIF. При больших объёмах данных рассматривайте Power Query или сводные таблицы. Нормализуйте данные заранее и протестируйте выбранный метод на контрольных примерах.

Важно: задокументируйте допущения — учитываются ли пустые ячейки, пробелы и регистр — это влияет на результат.