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

Быстрые ссылки

• Использование функций COUNTA и UNIQUE

• Использование функций SUM и COUNTIF

Вы можете использовать функцию COUNT и её варианты для подсчёта ячеек с числами. Но если нужно посчитать только различные значения в диапазоне, примените один из двух приёмов ниже.

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

См. также: Как использовать функцию COUNT в Microsoft Excel

Использование функций COUNTA и UNIQUE

Когда применять: современная версия Excel — Excel для Microsoft 365, Excel для веба, Excel 2021 или мобильные версии для iPhone/iPad/Android. Функция UNIQUE доступна в этих и более новых версиях.

Идея: UNIQUE возвращает массив уникальных значений из диапазона. COUNTA считает непустые ячейки. Объединив их, вы получите количество уникальных значений.

Пример: чтобы вывести уникальные значения из диапазона A2:A5, используйте формулу:

=UNIQUE(A2:A5)

В результате Excel вернёт список уникальных значений. На скриншоте видно три разных значения.

Чтобы не перечислять значения, а посчитать их, оберните формулу функцией COUNTA:

=COUNTA(UNIQUE(A2:A5))

Теперь формула вернёт число уникальных значений (в примере — 3).

Важно: если в диапазоне есть пустые ячейки, UNIQUE посчитает пустую ячейку как отдельное уникальное значение. Это может исказить результат. Чтобы исключить пустые ячейки, добавьте FILTER:

Функция FILTER также доступна только в современных версиях Excel.

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

Разбор FILTER: первая часть — диапазон A2:A5, вторая часть — критерий фильтрации A2:A5<>”” (не равно пустой строке). В сумме FILTER возвращает массив непустых значений, UNIQUE удаляет дубликаты, COUNTA считает результат.

На иллюстрации пустая строка не учитывается как уникальная.

Советы по удобству:

• Если ваши данные — текст с пробелами, используйте TRIM внутри FILTER, чтобы убрать лишние пробелы: FILTER(TRIM(A2:A5),TRIM(A2:A5)<>””).
• Для нечувствительности к регистру можно привести текст к одному регистру: UNIQUE(LOWER(…)) и т.д.

Использование функций SUM и COUNTIF

Когда применять: если у вас более старая версия Excel, где нет UNIQUE и FILTER, используйте комбинацию SUM и COUNTIF. Можно также заменить SUM на SUMPRODUCT при необходимости.

Идея: COUNTIF подсчитывает, сколько раз каждое значение встречается в диапазоне. Деление 1 на это число даёт вклад для суммирования — для уникальных значений вклад равен 1, для повторяющихся дробный. Сумма этих вкладов — количество уникальных значений.

Формула:

=SUM(1/COUNTIF(A2:A5,A2:A5))

Разбор: COUNTIF(A2:A5,A2:A5) создаёт массив частот каждого элемента. Деление 1/частота выдаёт вклад. SUM суммирует вклады и возвращает количество уникальных значений.

Проблема с пустыми ячейками: если есть пустые ячейки, выражение даст ошибку #DIV/0! (деление на ноль). Чтобы исключить пустые ячейки, используйте модифицированную формулу:

=SUM((A2:A5<>")/COUNTIF(A2:A5,A2:A5&""))

Объяснение: (A2:A5<>””) создаёт логический массив (TRUE/FALSE), который при арифметическом приведении становится 1 для непустых и 0 для пустых. CONCATENATION &”” в COUNTIF предотвращает деление на ноль, подставляя пустую строку в качестве критерия и переводя нулевые частоты в корректные делители.

Примечание: если ваши данные содержат текстовые представления чисел (“123”), а также числовые 123, и вы хотите считать их одинаковыми, приведите значения к одному типу (например, VALUE или TEXT) перед подсчётом.

Как выбрать метод — мини-методология

1. Проверьте версию Excel. Если в ней есть UNIQUE и FILTER — выбирайте метод с UNIQUE (он проще и стабильнее). Если нет — используйте SUM+COUNTIF.
2. Оцените данные: есть ли пустые ячейки, смешанные типы (текст и числа), скрытые пробелы или разный регистр. Добавьте TRIM, VALUE, LOWER/UPPER при необходимости.
3. Тестируйте формулу на небольшом фрагменте данных и на краевых случаях (пустые строки, только повторяющиеся значения, пропуски).
4. Зафиксируйте формулу как именованный диапазон, если будете использовать её часто.

Частые ошибки и как их исправить

• #DIV/0!: значит в формуле присутствует деление на ноль (обычно из-за пустых ячеек в SUM/COUNTIF варианте). Решение: использовать версию с фильтрацией пустых ячеек.
• Пустая ячейка считается уникальной: в UNIQUE-версии исключите пустые с помощью FILTER.
• Разный регистр или пробелы: примените TRIM и LOWER/UPPER.
• Текстовые числа и числовые значения считаются разными: приведите к одному типу с VALUE или TEXT.

Галерея крайних случаев

• Диапазон содержит только пустые ячейки. Результат должен быть 0 (при корректной формуле с фильтром).
• Все значения одинаковы. Результат 1.
• Комбинация чисел и текстовых представлений чисел. Проверьте приведение типов.
• Множество пробелов и невидимых символов. Используйте TRIM и CLEAN.

Контрольный чеклист для разных ролей

• Для пользователя:

  • Проверил версию Excel.
  • Испытал формулу на 5–10 строках.
  • Убедился, что пустые строки не считаются.

• Для аналитика/BI-специалиста:

  • Проверил типы данных (число/текст).
  • Добавил нормализацию (TRIM/LOWER/VALUE) при импорте.
  • Рассмотрел влияние на отчёты и сводные таблицы.

• Для администратора/автора отчёта:

  • Задокументировал формулу в README листа.
  • Создал именованный диапазон или функцию-обёртку.
  • Добавил тестовые кейсы при обновлении данных.

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

• Формула корректно считает уникальные значения для тестового диапазона из 20 строк.
• Пустые ячейки не учитываются, если это оговорено.
• Формула возвращает ожидаемые результаты в краевых сценариях (все дубликаты, все уникальны, смешанные типы).

Советы по производительности и совместимости

• Для очень больших диапазонов предпочитайте встроенные динамические функции (UNIQUE) — они быстрее и прозрачнее.
• Формулы с массивами и COUNTIF могут быть медленнее на больших наборах данных. Можно использовать компактные скрипты Power Query для предобработки и подсчёта уникальных значений.
• Если отчёты должны работать в разных версиях Excel, храните две версии формулы в документе и выбирайте нужную через инструкцию в шапке листа.

Короткое резюме

Найти и посчитать уникальные значения в Excel можно двумя основными способами: современный — UNIQUE+COUNTA(+FILTER), и универсальный — SUM+COUNTIF. Оба варианта можно адаптировать для исключения пустых ячеек и для нормализации данных. Тестируйте формулы на краевых случаях и документируйте выбор метода.

Изображения: логотип Excel и примеры формул с результатами.

ALT-пояснение: Логотип Microsoft Excel на зелёном фоне