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

Краткое определение

Уникальное (distinct) значение — это элемент, который учитывается один раз вне зависимости от количества повторений в диапазоне.

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

1. С помощью функции COUNTIF

1. Выделите диапазон, в котором нужно посчитать уникальные значения.
2. Введите формулу: =SUM(1/COUNTIF(A2:A10, A2:A10)).
3. Замените A2:A10 на ваш реальный диапазон.
4. Для старых версий Excel нажмите Ctrl + Shift + Enter, чтобы подтвердить формулу как массивную. Excel добавит фигурные скобки {} вокруг формулы.

Примечание: если в столбце есть пустые ячейки, исключите их с помощью IF. Например:

=SUM(IF(A2:A10<>"", 1/COUNTIF(A2:A10, A2:A10), 0))

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

=SUM(IF(ISTEXT(A2:A10), 1/COUNTIF(A2:A10, A2:A10), ""))

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

2. Уникальные с учётом регистра

1. Выделите диапазон.
2. Введите формулу в вспомогательный столбец, которая помечает первую встречу каждого значения с учётом регистра:

=IF(SUM((--EXACT($A$2:$A2,$A2)))=1, "Distinct", "")

3. Затем посчитайте метки:

=COUNTIF(B2:B10, "Distinct")

4. Для старых версий подтвердите массивные формулы сочетанием Ctrl + Shift + Enter.

Этот метод полезен, когда Two и TWO должны считаться разными значениями.

3. Функция UNIQUE (Excel 365 и Excel 2019)

1. Выберите ячейку для результата.
2. Введите:

=COUNTA(UNIQUE(A2:A10))

3. Замените A2:A10 на ваш диапазон и нажмите Enter.

Функция UNIQUE возвращает массив уникальных значений, а COUNTA считает непустые элементы, поэтому пустые ячейки автоматически исключаются.

4. Сводная таблица

1. Выделите диапазон данных.
2. На вкладке Вставка нажмите Сводная таблица.
3. Укажите место для отчёта.
4. Перетащите поле, которое хотите считать, в область Строки.
5. Перетащите то же поле в область Значения.
6. Нажмите на стрелку рядом с полем в области Значения и выберите Параметры поля значений.
7. Выберите Уникальные значения (Distinct Count) и нажмите OK.

Если данные изменились, обновите сводную таблицу через кнопку Обновить на вкладке Анализ.

5. Power Query

1. Выделите диапазон.
2. На вкладке Данные выберите Из таблицы/диапазона.
3. В Power Query выберите столбец для подсчёта уникальных значений.
4. На вкладке Преобразование используйте Группировка.
5. В диалоге Группировка перейдите в Дополнительно.
6. Добавьте агрегирование и выберите Подсчитать строки (Count Rows).
7. Нажмите OK, затем Закрыть и загрузить.

Power Query удобен при работе с большими таблицами и автоматизации повторяющихся задач.

6. Расширенный фильтр

1. Выберите столбец с данными.
2. На вкладке Данные в группе Сортировка и фильтр нажмите Расширенный.
3. Выберите Копировать в другое место.
4. Укажите Диапазон списка и куда копировать.
5. Отметьте Только уникальные записи и нажмите OK.
6. Посчитайте уникальные значения в новом диапазоне через COUNTA.

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

Альтернативные подходы и сниппеты

• SUMPRODUCT (без массивных формул):

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

В некоторых версиях Excel SUMPRODUCT выполняет операцию без необходимости подтверждать формулу как массивную.

• Вспомогательный столбец с меткой первой встречи (не массивная формула):

В ячейке B2: =IF(COUNTIF($A$2:A2, A2)=1, 1, 0) — затем =SUM(B2:B100).

• Для больших диапазонов и требований к скорости используйте Power Query или Сводную таблицу — формулы на больших массивах могут работать медленно.

Когда методы могут не сработать

• Дубликаты с пробелами или невидимыми символами: сначала примените TRIM и CLEAN.
• Разные форматы чисел и текста (например, “123” и 123): приведите к одному типу через VALUE или TEXT/TO_TEXT.
• Регистрозависимость: стандартные функции не различают регистр — используйте EXACT для проверки регистра.
• Большие наборы данных: формулы с массивами могут быть медленными, лучше Power Query или Сводная таблица.

Ментальные модели и подбор метода

• Если у вас Excel 365/2019 и нужна простота — используйте UNIQUE.
• Если нужно автоматическое обновление при изменении данных — Сводная таблица или Power Query.
• Если вы в старой версии Excel и хотите формулу в ячейке — используйте COUNTIF с массивной формулой или вспомогательный столбец.

flowchart TD
  A[Есть Excel 365/2019?] -->|Да| B[UNIQUE + COUNTA]
  A -->|Нет| C[Нужна автообновляемая сводка?]
  C -->|Да| D[Сводная таблица]
  C -->|Нет| E[Малые данные => COUNTIF массив]
  C -->|Нет 'большие'| F[Power Query]

Important: перед применением любого метода удалите лишние пробелы и приведите данные к единому типу. Это сэкономит время и исключит ложные дубликаты.

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

• Аналитик:

  • Проверил типы данных
  • [ ] Убрал лишние пробелы TRIM
  • Выбрал Power Query или Сводную таблицу для больших наборов

• Пользователь Excel 365:

  • [ ] Использует UNIQUE
  • Проверил пустые значения

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

  • Настроил обновление данных
  • Автоматизировал шаги в Power Query

Шпаргалка формул (cheat sheet)

• Быстрый подсчёт всех уникальных (в старых Excel): =SUM(1/COUNTIF(A2:A10, A2:A10))
• Текстовые только: =SUM(IF(ISTEXT(A2:A10),1/COUNTIF(A2:A10, A2:A10),""))
• С учётом регистра: вспомогательная метка + COUNTIF
• Excel 365/2019: =COUNTA(UNIQUE(A2:A10))
• Вспом. столбец первая встреча: =IF(COUNTIF($A$2:A2, A2)=1,1,0)

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

• Результат совпадает со сводной таблицей или Power Query при одинаковых входных данных.
• Пустые и пробельные строки корректно исключены.
• Формула работает быстро на выбранном объёме данных (или переведена в Power Query для больших объёмов).

Итог

Выбор метода зависит от версии Excel, объёма данных и требований к автоматизации. Для быстрого результата в новых версиях используйте UNIQUE; для контроля и автоматизации — Power Query; для простых листов и совместимости — COUNTIF/массив или вспомогательный столбец.

Если хотите, я могу:

• прислать готовые формулы для вашего реального диапазона;
• помочь собрать Power Query-шаги по вашим данным;
• показать, как убрать невидимые символы и привести данные к единому виду.

Запросите пример с вашими данными, и я подготовлю точное решение.