Подробный ответ
Подсчёт уникальных значений в Excel — частая задача при анализе данных.
Классическая формула (в том числе Excel 2016/2019)
=СУММПРОИЗВ(1/СЧЁТЕСЛИ(A2:A100;A2:A100))
Простой вариант предполагает заполненные ячейки без ошибок и текстовых символов критериев *, ?, ~ или начальных операторов сравнения. СЧЁТЕСЛИ трактует такие строки как условия, поэтому формула не универсальна для произвольного текста.
Логика: СЧЁТЕСЛИ считает, сколько раз встречается каждое значение. 1/количество_повторений при суммировании даёт 1 для каждого уникального значения.
Внимание: пустые ячейки могут привести к делению на ноль. Для обычных значений без ошибок и специальных символов критериев вариант с исключением пустых выглядит так:
=СУММПРОИЗВ((A2:A100<>"")/СЧЁТЕСЛИ(A2:A100;A2:A100&""))
Microsoft 365, Excel 2021 и 2024 — функция УНИК
=ЧСТРОК(УНИК(A2:A100))
УНИК возвращает разные значения, ЧСТРОК считает строки результата. Формула выше включает пустое значение, если оно есть. Для подсчёта только непустых значений в диапазоне без ошибок:=ЕСЛИ(СУММПРОИЗВ(--(A2:A100<>""))=0;0;ЧСТРОК(УНИК(ФИЛЬТР(A2:A100;A2:A100<>""))))
При отсутствии непустых значений возвращается 0. Здесь «уникальные» означает разные значения, а не только встречающиеся ровно один раз.
Через сводную таблицу
- Создайте сводную таблицу из диапазона.
- Перетащите нужное поле в область «Строки».
- Подсчитайте разные элементы поля, исключив заголовки, общие итоги и при необходимости пустой элемент. Число всех видимых строк сводной не равно автоматически числу разных значений.
Через Power Query
Данные → Получить данные → Из таблицы, затем выберите нужный столбец → «Удалить строки» → «Удалить дубликаты» на вкладке «Главная». Для подсчёта непустых значений сначала исключите null и пустые строки. Если выбрано несколько столбцов, удаляются повторяющиеся сочетания этих столбцов.
Через расширенный фильтр
Данные → Дополнительно → Только уникальные записи → Скопировать в другое место. Затем СЧЁТЗ результата.
Совет для больших данных: используйте ограниченные диапазоны, УНИК или Power Query. Скорость зависит от структуры данных и книги; не рассчитывайте формулы массивов по целым столбцам без необходимости.
Практическая деталь
Не включайте заголовок в диапазон подсчёта. Заранее решите, считать ли пустые значения отдельной категорией; для списка клиентов их обычно исключают.
| A | |
|---|---|
| 1 | Клиент |
| 2 | Анна |
| 3 | Борис |
| 4 | Анна |
Список без повторов: Анна, Борис — два разных клиента. Значение, встречающееся ровно один раз, только одно: Борис.