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

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

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

Когда нужен быстрый ответ: удалить дубликаты

Если вам нужно единовременно узнать количество уникальных значений и не важно сохранить исходный порядок или данные, можно удалить дубликаты:

1. Скопируйте столбец на новый лист, чтобы не потерять исходные данные.
2. Выделите диапазон или столбец.
3. На вкладке Данные в блоке «Инструменты данных» нажмите «Удалить дубликаты».

Если ваш признак уникальности состоит из двух столбцов (например, имя и фамилия), выделите оба столбца перед удалением дубликатов:

Важно: этот способ удаляет данные. Используйте копию таблицы, если хотите сохранить исходный набор.

Формула для подсчёта уникальных значений (подход для старых версий)

Если нужно сохранить исходные данные и получить число уникальных значений динамически, можно использовать массивную формулу. Простейший пример (вставляйте свой диапазон вместо A2:A13):

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

Примечание: в старых версиях Excel эту формулу нужно подтвердить сочетанием Ctrl + Shift + Enter. В новых версиях массивы обрабатываются автоматически.

Почему это работает — по шагам

Ниже кратко объяснено, какие операции выполняют функции внутри формулы.

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

Массив — это переменная, содержащая несколько значений одновременно (например, диапазон A2:A13). Массивные формулы оперируют со всем набором значений разом, а не с одной ячейкой.

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

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

MATCH возвращает позицию первого вхождения значения в диапазоне. Если в A2:A13 искать каждое значение из A2:A13, то MATCH вернёт массив позиций первых вхождений.

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

FREQUENCY — считаем, сколько раз встречается каждая первая позиция

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

IF — переводим «>0» в единицу

IF преобразует каждый элемент массива частот: если частота больше нуля — вернуть 1, иначе 0. Иными словами, каждый уникальный элемент превращается в 1.

SUM — суммируем единицы

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

Современные и альтернативные подходы

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

1) Excel 365 / Excel 2021: UNIQUE + COUNTA (рекомендуется)

UNIQUE возвращает массив уникальных значений из диапазона. Затем COUNTA считает непустые элементы.

Пример:

=COUNTA(UNIQUE(A2:A1000))

Чтобы исключить пустые строки:

=ROWS(UNIQUE(FILTER(A2:A1000, A2:A1000 <> "")))

UNIQUE автоматически обновляет результат при изменении данных.

2) SUMPRODUCT + COUNTIF — рабочий без CSE

Если вы не хотите использовать массивную формулу с Ctrl+Shift+Enter, SUMPRODUCT справится сам:

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

Эта версия обрабатывает пустые строки безопасно (за счёт конкатенации &””), но требует осторожности при больших диапазонах — может быть медленной.

3) Сводная таблица

Вставьте сводную таблицу и поместите интересующий столбец в область «Строки». Затем посмотрите количество элементов в области сводки (или добавьте поле и посчитайте значения). Это удобный визуальный способ без формул.

4) Power Query

Power Query (Получить и преобразовать данные) отлично подходит для больших наборов. Загрузите диапазон в Power Query → Удалить дубликаты → Закрыть и загрузить → получить таблицу уникальных значений. Power Query можно автоматизировать и обновлять.

5) VBA макрос (автоматизация)

Если вы часто подсчитываете уникальные значения в больших файлах, можно написать макрос, который будет быстро возвращать количество уникальных элементов, используя коллекции или Dictionary объектов (Scripting.Dictionary).

Чит‑шит: быстрые формулы

• Excel 365/2021: =COUNTA(UNIQUE(A2:A1000))
• Excel 365, без пустых: =ROWS(UNIQUE(FILTER(A2:A1000, A2:A1000<>””)))
• Старые версии (массив): {=SUM(IF(FREQUENCY(MATCH(A2:A13,A2:A13,0),MATCH(A2:A13,A2:A13,0))>0,1))}
• Без Ctrl+Shift+Enter: =SUMPRODUCT((A2:A1000<>”)/COUNTIF(A2:A1000,A2:A1000&””))

Используйте точные диапазоны, а не целые столбцы, чтобы избежать ненужной нагрузки на Excel.

Проверка на практике: чеклист для аналитика

Перед применением любой формулы выполните эти шаги:

• Создайте резервную копию листа.
• Убедитесь, что диапазон указан корректно (нет лишних строк).
• Очистите ведущие/замыкающие пробелы: TRIM или Power Query.
• Приведите регистр к единому виду, если сравнение чувствительно к регистру: =UPPER(A2).
• Убедитесь, что числа не хранятся как текст и наоборот.

Роли и задачи:

• Аналитик: использовать формулу в отдельной ячейке и показать источник (диапазон).
• Руководитель: запросить отчёт с явным указанием, считаются ли пустые/NULL значения.
• Автоматизатор ETL: реализовать подсчёт в Power Query или в базе данных на стороне источника.

Частые проблемы и способы их решения (галерея кейсов)

• Пустые ячейки: многие формулы будут делить на ноль. Исключайте пустые строки с помощью FILTER или условного разделения (A2:A1000<>””).
• Лишние пробелы: “John” ≠ “John “. Решение: TRIM().
• Различия в регистре: “anna” и “Anna” считаются разными. Решение: применить UPPER() или LOWER() для нормализации перед подсчётом.
• Числа как текст: “123” может не равняться 123. Решение: VALUE() или приведение типов.
• Несколько столбцов как ключ уникальности: объединяйте столбцы в вспомогательном столбце, например =A2&”|”&B2, и используйте этот столбец для подсчёта.

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

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

• Результат совпадает с контрольной выборкой ручного подсчёта (на 10–20 проверочных строк).
• Учтены пустые значения по требованию (исключены или включены).
• Формула обновляется при добавлении строк в указанный диапазон или при использовании структурированной таблицы Excel.

Мини‑методология: шаги для безопасного внедрения

1. Решите, нужно ли сохранять исходные данные.
2. Выберите метод: UNIQUE (новый Excel) / массивная формула / Power Query / сводная таблица.
3. Нормализуйте данные: TRIM, UPPER/LOWER, исправьте типы.
4. Примените формулу или загрузите в Power Query.
5. Проверьте результат на контрольной выборке.
6. Документация: укажите диапазон, версию Excel и допущения (учитываются ли пустые значения, регистр и т. д.).

Примеры альтернативных формул и привычки производительности

• Используйте структурированные таблицы Excel (Ctrl+T). Тогда формулы останутся корректными при добавлении строк: =COUNTA(UNIQUE(Table1[Email])).
• Избегайте указания целых столбцов (A:A) в COUNTIF/FREQUENCY, если не требуется. Это замедляет расчёт.
• Для очень больших наборов данных лучше применять Power Query или подсчёт на стороне БД.

Краткий глоссарий

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

Когда предложенные формулы не подойдут

• Если вам нужен учёт версий/дубликатов по сложным правилам (например, считать уникальным только если совпадают несколько полей с доп. логикой), простой подсчёт уникальных значений не подойдёт — потребуется Power Query, SQL или макрос с логикой.
• Если файл очень большой (милионы строк), Excel может упереться в ограничения памяти и работать медленно; лучше обработать данные в базе или с помощью Power Query/Power BI.

Пример пошагового кейса: посчитать уникальные клиенты

1. Скопируйте столбец “Клиент” на новый лист или в таблицу.
2. Удалите ведущие/хвостовые пробелы: вставьте рядом колонку =TRIM([@Клиент]).
3. Если нужно — нормализуйте регистр: =UPPER(TRIM([@Клиент])).
4. Если у вас Excel 365: =COUNTA(UNIQUE(диапазон)).
5. Проверьте результат вручную на 10 случайных строк.

Пара слов про безопасность и приватность

Если данные содержат персональные идентификаторы (имена, e‑mail, номера), убедитесь, что вы соблюдаете внутренние политики по защите данных и, при необходимости, анонимизируете данные перед анализом.

Резюме

Подсчитать уникальные значения в Excel можно несколькими способами: удалить дубликаты для одноразовой проверки, использовать формулы (UNIQUE, MATCH+FREQUENCY, SUMPRODUCT+COUNTIF) для динамического подсчёта, или применять Power Query/сводные таблицы для больших и повторяющихся процессов. Выбор зависит от версии Excel, размера данных и требований к обновлению.

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

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

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