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

Чтобы быстро узнать количество неповторяющихся записей в столбце Excel, используйте функцию СЧЁТЗ в связке с УНИК (для новых версий Excel) или комбинацию СУММПРОИЗВ и СЧЁТЕСЛИ (для старых версий). Для визуального анализа без формул идеально подходит инструмент «Сводная таблица» с функцией «Отличительные числа». Выбор метода зависит от версии вашего ПО и необходимости динаического обновления данных.

Быстрые способы для современных версий Excel (Office 365, Excel 2021+)

Если вы пользуетесь актуальной подпиской Microsoft 365 или Excel 2021 и новее, задача решается одной простой формулой благодаря появлению динамических массивов.

Функция УНИК (UNIQUE)

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

Формула:

=СЧЁТЗ(УНИК(A2:A100))

Как это работает:

1. УНИК(A2:A100) создает временный массив, содержащий только неповторяющиеся значения из диапазона.
2. СЧЁТЗ подсчитывает количество элементов в этом новом массиве.

Универсальные формулы для старых версий Excel

В версиях Excel 2019, 2016 и более ранних функции УНИК нет. Здесь приходится использовать классические приемы с массивами или суммированием условий.

Комбинация СУММПРОИЗВ и СЧЁТЕСЛИ

Это самый надежный способ, работающий во всех версиях Excel без необходимости нажимать Ctrl+Shift+Enter.

Формула:

=СУММПРОИЗВ(1/СЧЁТЕСЛИ(A2:A100; A2:A100))

Логика расчета:

1. СЧЁТЕСЛИ(A2:A100; A2:A100) создает массив, где для каждой ячейки подсчитывается, сколько раз её значение встречается во всем диапазоне. Например, если имя "Иван" встречается 3 раза, для каждой ячейки с "Иван" будет число 3.
2. 1/... делит единицу на каждое полученное число. Для "Ивана" это будет 1/3 + 1/3 + 1/3, что в сумме дает 1.
3. СУММПРОИЗВ складывает все полученные единицы, давая итоговое количество уникальных записей.

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

Альтернативный вариант через функцию ЕСЛИ и СЧЁТЕСЛИ. Требует подтверждения через Ctrl+Shift+Enter в старых версиях.

{=СУММ(ЕСЛИ(СЧЁТЕСЛИ(A2:A100; A2:A100)=1; 1; 0))}

Примечание: Данная конкретная запись считает только те значения, которые встречаются ровно один раз (не дублируются). Для подсчета всех уникальных (включая те, что повторяются) лучше использовать метод с СУММПРОИЗВ выше.

Использование сводных таблиц

Сводные таблицы позволяют посчитать уникальные значения без написания сложных формул, что удобно для больших объемов данных. Однако здесь есть важный нюанс: стандартная функция «Количество» не подходит, так как она считает все строки, включая повторы.

Метод 1: Модель данных (Power Pivot) — Самый точный

Этот способ доступен в Excel 2013 и новее. Он позволяет использовать функцию «Отличительные числа» (Distinct Count).

1. Выделите вашу таблицу с данными.
2. Перейдите на вкладку Вставка -> Сводная таблица.
3. В появившемся окне поставьте галочку «Добавить эти данные в модель данных» (Add this data to the Data Model). Это ключевой шаг.
4. Нажмите ОК.
5. В поле «Значения» перетащите нужный столбец.
6. По умолчанию там будет «Количество». Нажмите на стрелочку рядом с полем -> Параметры полей значений.
7. Выберите операцию «Отличительные числа» (Distinct Count).

Теперь сводная таблица покажет точное количество уникальных значений.

Метод 2: Группировка в обычной сводной таблице

Если опция «Модель данных» недоступна или не нужна:

1. Создайте обычную сводную таблицу.
2. Перетащите целевой столбец в область «Строки».
3. Excel автоматически сгруппирует одинаковые значения.
4. Посмотрите на количество полученных строк в сводной таблице (исключая заголовок и итог, если он есть).

Сравнение методов подсчета

Выбор инструмента зависит от ваших задач и версии ПО.

Частые ошибки при подсчете

1. Лишние пробелы. Excel считает значения "Яблоко " (с пробелом в конце) и "Яблоко" разными. Решение: Перед подсчетом очистите данные функцией СЖПРОБЕЛЫ (TRIM) или используйте «Найти и заменить» (Ctrl+H), заменив пробел на пустоту, если это уместно.

2. Разный регистр. Стандартные функции Excel (СЧЁТЕСЛИ, УНИК) не различают регистр. "москва" и "Москва" будут считаться одним значением. Если регистр важен, потребуются сложные формулы с СОВПАД (EXACT).

3. Числа как текст. Иногда числа импортируются из внешних источников как текст. 100 (число) и "100" (текст) могут считаться разными уникальными значениями в некоторых контекстах или игнорироваться функциями счета чисел. Решение: Используйте инструмент «Текст по столбцам» для приведения типов данных к единому виду.

FAQ

Вопрос: Почему формула с СУММПРОИЗВ выдает ошибку #ДЕЛ/0! Ответ: В указанном диапазоне есть пустые ячейки. Функция СЧЁТЕСЛИ для пустой ячейки возвращает 0, а деление на ноль невозможно. Используйте модифицированную формулу с проверкой на пустоту: =СУММПРОИЗВ((A2:A100<>"")/СЧЁТЕСЛИ(A2:A100; A2:A100&"")).

Вопрос: Можно ли посчитать уникальные значения по условию (например, только для города "Москва")? Ответ: Да. В новых Excel используйте СЧЁТЗ(УНИК(ФИЛЬТР(Dиапазон_Значений; Диапазон_Условий="Москва"))). В старых версиях это требует использования формулы массива с функцией ЧАСТОТА (FREQUENCY) или сложной комбинации СУММПРОИЗВ.

Вопрос: Как увидеть сами уникальные значения, а не только их количество? Ответ: Скопируйте столбец, вставьте в новое место, затем перейдите на вкладку Данные и нажмите «Удалить дубликаты». Либо используйте формулу =УНИК(A2:A100) в соседнем столбце (для новых версий Excel), которая выведет весь список уникальных записей динамически.