Чтобы посчитать количество уникальных значений в столбце Excel, введите в свободную ячейку формулу =СЧЁТЗ(УНИК(A2:A13)). УНИК соберёт список без повторов, СЧЁТЗ посчитает его строки. Формула работает в Excel 365, 2021 и 2024.
В Excel 2019 и старше функции УНИК нет, там поможет =СУММПРОИЗВ(1/СЧЁТЕСЛИ(A2:A13;A2:A13)). Ниже все семь способов на одной таблице: формулы, сводная таблица, расширенный фильтр и подсчёт по условию. Если с формулами вы пока на «вы», начните с обзора основных функций Excel.
CheckroiПодборка курсов по Microsoft Excel685 курсов • 64 школыСравните цены, школы, программу и найдите выгодные предложения по обучениюСравнить→
Уникальные и различные значения: что именно считаем
Под «уникальными» люди имеют в виду две разные вещи, и от этого зависит формула.
- Различные значения: каждое имя по одному разу, сколько бы раз оно ни повторялось. Так считают клиентов, товары, города.
- Встречающиеся ровно один раз: только те, у кого нет повторов. Так ищут разовых покупателей.
Пример для всей статьи: в столбце A 12 заказов, в них шесть фамилий клиентов.
| Клиент | Сколько заказов |
|---|---|
| Иванова | 3 |
| Петров | 3 |
| Сидоров | 2 |
| Козлова | 2 |
| Морозов | 1 |
| Лебедев | 1 |
Различных клиентов шесть, а купивших ровно один раз двое: Морозов и Лебедев. Все формулы ниже дают именно эти числа.
Кадры сняты в Google Таблицах: формулы там пишутся так же, но разделитель аргументов в нашей песочнице запятая. В русском Excel аргументы разделяет точка с запятой, поэтому в тексте формулы даны в варианте для Excel.
Способ 1: функции УНИК и СЧЁТЗ
Самый короткий путь в Excel для Microsoft 365, Excel 2021 и 2024. Функция УНИК возвращает список значений без повторов, а СЧЁТЗ считает непустые ячейки этого списка.
- Щёлкните пустую ячейку, например D2.
- Введите
=СЧЁТЗ(УНИК(A2:A13)). - Нажмите Enter. В примере получится 6.
Хотите увидеть, какие именно значения уникальны, а не только их число? Введите в свободный столбец одну =УНИК(A2:A13). Список сам «рассыплется» вниз на столько строк, сколько нужно.
Если в диапазоне есть пустые ячейки
Диапазон часто берут с запасом, например до A100, чтобы новые заказы попадали в подсчёт сами. Но УНИК превращает пустые ячейки в ещё одно «значение», ноль. СЧЁТЗ посчитает и его, и итог окажется на единицу больше.
Решение: отфильтровать пустоты до подсчёта.
=СЧЁТЗ(УНИК(ФИЛЬТР(A2:A100;A2:A100<>"")))Только значения без повторов
У УНИК есть третий аргумент. Если поставить в него ИСТИНА, функция вернёт только значения, которые встречаются один раз:
=СЧЁТЗ(УНИК(A2:A13;;ИСТИНА))Второй аргумент пропущен, поэтому между точками с запятой пусто. Результат равен 2: это Морозов и Лебедев.
Способ 2: формула СУММПРОИЗВ и СЧЁТЕСЛИ для любой версии
Формула работает в любом Excel, включая 2010 и 2016, и не требует ввода массивом:
=СУММПРОИЗВ(1/СЧЁТЕСЛИ(A2:A13;A2:A13))Как она считает. СЧЁТЕСЛИ для каждой строки узнаёт, сколько раз её значение встречается в столбце. У Ивановой это 3, и каждая из трёх её строк даёт дробь 1/3.
Три трети в сумме дают единицу. Так любое значение, сколько бы раз оно ни повторялось, добавляет к итогу ровно 1, а СУММПРОИЗВ складывает все дроби.
Если в столбце есть пустые ячейки
Пустая ячейка ломает формулу: СЧЁТЕСЛИ вернёт для неё 0, а деление на ноль даст ошибку #ДЕЛ/0!.
Решение: добавить проверку на пустоту и склейку с пустой строкой:
=СУММПРОИЗВ((A2:A100<>"")/СЧЁТЕСЛИ(A2:A100;A2:A100&""))Пустые строки теперь дают ноль в числителе и не попадают в итог. Так можно взять диапазон с запасом, до A100, и не переписывать формулу, когда добавятся заказы.
Способ 3: ЧАСТОТА и ПОИСКПОЗ, вариант из справки Microsoft
Этот способ Microsoft приводит в своей справке по подсчёту уникальных значений. Он быстрее СУММПРОИЗВ на больших таблицах, но читается тяжелее.
Для столбца с числами, например с номерами заказов, хватит короткой формулы:
=СУММ(ЕСЛИ(ЧАСТОТА(A2:A13;A2:A13)>0;1))Для текста и смешанных данных нужен ПОИСКПОЗ, который превращает каждое значение в номер его первого появления:
=СУММ(ЕСЛИ(ЧАСТОТА(ЕСЛИ(ДЛСТР(A2:A13)>0;ПОИСКПОЗ(A2:A13;A2:A13;0);"");ЕСЛИ(ДЛСТР(A2:A13)>0;ПОИСКПОЗ(A2:A13;A2:A13;0);""))>0;1))- ДЛСТР отсекает пустые ячейки, у них длина ноль.
- ПОИСКПОЗ даёт каждой фамилии номер её первой строки: все три «Ивановой» получают 1.
- ЧАСТОТА считает, сколько раз встречается каждый номер, и пропускает текст и пустоты.
- СУММ(ЕСЛИ(…>0;1)) складывает единицы по номерам, которые встретились хотя бы раз.
В Microsoft 365 формулу вводят обычным Enter. В Excel 2019 и старше её надо подтвердить сочетанием Ctrl+Shift+Enter, иначе результат будет неверным. Фигурные скобки вокруг формулы Excel поставит сам.
Способ 4: сводная таблица с подсчётом различных элементов
Удобно, когда уникальные значения нужно посчитать по группам: сколько разных клиентов у каждого менеджера или в каждом городе. Обычная сводная так не умеет, нужна модель данных.
- Выделите таблицу с заголовками.
- На вкладке вставки выберите «Сводная таблица».
- В окне создания сводной таблицы отметьте флажок «Добавить эти данные в модель данных» (Add this data to the Data Model) и подтвердите.
- Перетащите поле с фамилиями клиентов в область значений, а группировку, например менеджера, в область строк.
- Щёлкните поле в области значений, откройте его параметры и выберите операцию подсчёта различных элементов. В справке Microsoft она называется «Число разных элементов».
Без флажка из третьего шага нужной операции в списке не будет. По справке Microsoft, эта функция сведения работает только при использовании модели данных. Подробнее про сами сводные написано в инструкции как сделать сводную таблицу в Excel.
Способ 5: без формул, через расширенный фильтр
Подходит для разовой задачи, когда формулу в книге оставлять не хочется. Исходные данные не меняются.
- Выделите столбец с данными вместе с заголовком.
- На вкладке «Данные» в группе сортировки и фильтра нажмите «Дополнительно».
- В окне расширенного фильтра выберите копирование результата в другое место и укажите свободную ячейку.
- Отметьте флажок «Только уникальные записи» и подтвердите.
- Выделите полученный список без заголовка и посмотрите на строку состояния внизу: там Excel покажет количество.
Подробно сам инструмент разобран в статье о фильтре по столбцам в Excel. Есть и второй путь без формул: скопировать столбец на новый лист и на вкладке «Данные» выбрать «Удалить дубликаты». Excel сам сообщит, сколько уникальных значений осталось. Только делайте это на копии. Все варианты разобраны в инструкции как удалить повторяющиеся значения.
Канал основателя Checkroi Вани БуявцаЗабирайте промпты и обучение по нейросетям в моём Телеграм-каналеБольше 3 700 человек уже применяют Claude Code, ChatGPT и другие нейросети в работе, учёбе, бизнесе и жизниПерейти в каналСпособ 6: уникальные значения по условию
Частая задача: сколько разных клиентов купили в Москве, если город записан в столбце B. В Excel 365 и 2021 УНИК объединяют с ФИЛЬТР:
=СЧЁТЗ(УНИК(ФИЛЬТР(A2:A13;B2:B13="Москва")))Есть подвох. Если под условие не попала ни одна строка, ФИЛЬТР вернёт ошибку, а СЧЁТЗ посчитает её как одно значение и покажет 1. Надёжнее так:
=ЕСЛИОШИБКА(СТРОК(УНИК(ФИЛЬТР(A2:A13;B2:B13="Москва")));0)Для старых версий подойдёт СУММПРОИЗВ с СЧЁТЕСЛИМН. Он считает повторы пары «клиент + город», а условие обнуляет чужие строки:
=СУММПРОИЗВ((B2:B13="Москва")/СЧЁТЕСЛИМН(A2:A13;A2:A13;B2:B13;B2:B13))Способ 7: вспомогательный столбец с отметкой
Самый наглядный вариант для новичков: формула простая, а каждую уникальную строку видно глазами. Его удобно использовать, когда потом нужно отфильтровать первые появления или проверить результат других формул.
- В свободный столбец рядом с данными, например в C2, введите
=ЕСЛИ(СЧЁТЕСЛИ($A$2:A2;A2)=1;1;0). - Протяните формулу вниз до последней строки.
- Под столбцом посчитайте сумму:
=СУММ(C2:C13). В примере получится 6.
Всё дело в смешанной ссылке $A$2:A2. Начало диапазона закреплено, а конец сдвигается вместе с формулой. В каждой строке СЧЁТЕСЛИ смотрит только на строки выше, поэтому единица встаёт лишь при первом появлении фамилии. Как работают такие ссылки и автосумма, показано в инструкции как посчитать сумму в столбце Excel.
Минус способа: лишний столбец и медленная работа на больших таблицах: каждая строка заново пересчитывает свой кусок диапазона.
Особые случаи подсчёта
Три ситуации, на которых базовые формулы дают неверный ответ или требуют доработки.
Уникальные сочетания по нескольким столбцам
Бывает, что уникальность задаёт пара значений. Например, сколько разных пар «клиент + город», если Петров покупал и в Москве, и в Казани. В Excel 365 и 2021 УНИК умеет сравнивать строки целиком:
=СТРОК(УНИК(A2:B13))Здесь нужна именно функция СТРОК, а не СЧЁТЗ. УНИК вернёт таблицу в два столбца, и СЧЁТЗ посчитает каждую ячейку, то есть удвоит ответ.
В старых версиях работает та же логика с дробями, только повторы считает СЧЁТЕСЛИМН по обоим столбцам:
=СУММПРОИЗВ(1/СЧЁТЕСЛИМН(A2:A13;A2:A13;B2:B13;B2:B13))Только текст или только числа
В выгрузках из учётных систем в одном столбце бывают перемешаны номера и названия. Посчитать уникальные числа поможет ЧАСТОТА, она сама пропускает текст и пустые ячейки:
=СУММ(--(ЧАСТОТА(A2:A100;A2:A100)>0))Двойной минус превращает ИСТИНА и ЛОЖЬ в единицы и нули. Для уникального текста в любой версии Excel подойдёт:
=СУММПРОИЗВ(ЕТЕКСТ(A2:A100)/СЧЁТЕСЛИ(A2:A100;A2:A100&""))В Excel 365 то же самое короче: =СЧЁТЗ(УНИК(ФИЛЬТР(A2:A100;ЕТЕКСТ(A2:A100)))).
Чтобы итог обновлялся сам
Если заказы дописываются каждый день, диапазон A2:A13 быстро устареет. Есть два решения.
- Умная таблица. Превратите данные в таблицу через Ctrl+T и сошлитесь на столбец по имени:
=СЧЁТЗ(УНИК(Продажи[Клиент])). Новые строки попадут в подсчёт автоматически. Как оформить данные таблицей, показано в инструкции как сделать таблицу в Excel. - Диапазон с запасом. Берите A2:A1000 и используйте варианты формул, которые не ломаются на пустых ячейках: с ФИЛЬТР для УНИК или с проверкой
<>""для СУММПРОИЗВ.
Сводная таблица сама не обновляется. После добавления данных её нужно обновить: щёлкните сводную правой кнопкой мыши и выберите «Обновить».
Как посчитать уникальные значения в Google Таблицах
В Google Таблицах для этого есть отдельная функция COUNTUNIQUE:
=COUNTUNIQUE(A2:A13)Она пропускает пустые ячейки и сразу возвращает число, в примере 6. В Excel такой функции нет, и если файл потом откроют в Excel, формула сломается. Если таблицей пользуются в обеих программах, берите СЧЁТЗ(УНИК()): она работает и там и там. Как работать с одним файлом в двух программах, мы рассказали в разборе Google Таблиц и Excel.
Как проверить, что формула посчитала верно
Ошибку в подсчёте уникальных значений не видно: формула возвращает правдоподобное число. Поэтому на важных отчётах стоит сверить результат вторым способом.
- Выведите список
=УНИК(A2:A13)и пробегите его глазами. Две похожие строки рядом, вроде «Петров» и «Петров », это сигнал, что в данных лишние пробелы. - Посчитайте тот же столбец формулой из другого способа. Если УНИК и СУММПРОИЗВ дали разные числа, ищите пустые ячейки или числа в текстовом формате.
- На небольших таблицах помогает сортировка по алфавиту: повторы встают рядом, и их легко пересчитать.
Какой способ выбрать
| Ситуация | Способ |
|---|---|
| Excel 365, 2021, 2024 | СЧЁТЗ(УНИК()) |
| Excel 2019 и старше | СУММПРОИЗВ(1/СЧЁТЕСЛИ()) |
| Десятки тысяч строк в старом Excel | ЧАСТОТА и ПОИСКПОЗ |
| Подсчёт по группам | Сводная таблица с моделью данных |
| Разовая проверка без формул | Расширенный фильтр |
| Нужно видеть, какие строки уникальны | Вспомогательный столбец |
| Уникальные пары по двум столбцам | СТРОК(УНИК()) или СЧЁТЕСЛИМН |
| Подсчёт по условию | УНИК + ФИЛЬТР или СУММПРОИЗВ + СЧЁТЕСЛИМН |
| Google Таблицы | COUNTUNIQUE |
Частые ошибки при подсчёте уникальных значений
Одинаковые значения считаются разными
Причина почти всегда в невидимых пробелах: «Петров» и «Петров » для Excel разные строки.
Решение: почистить столбец функцией =СЖПРОБЕЛЫ(A2) во вспомогательном столбце и считать уже по нему.
Та же история с числами, сохранёнными как текст. Номер заказа 101, записанный числом, и «101» в текстовом формате УНИК посчитает двумя значениями. Проверить помогут поиск повторяющихся значений и единый формат ячеек.
Регистр не учитывается
«ИВАНОВА» и «Иванова» все способы из статьи считают одним значением. Обычно это и нужно. Если регистр важен, например в артикулах, понадобится формула с функцией СОВПАД, но это уже редкая задача.
УНИК насчитала на одно значение больше
В диапазоне есть пустые ячейки, и УНИК вернула ноль как отдельное значение.
Решение: вариант с ФИЛЬТР из первого способа.
Ошибка #ДЕЛ/0! в формуле со СЧЁТЕСЛИ
В диапазоне есть пустая ячейка.
Решение: вариант с (A2:A100<>"") из второго способа.
Ошибка #ПЕРЕНОС! у функции УНИК
Списку некуда «рассыпаться»: ниже формулы есть занятые ячейки. Освободите место под результат или оберните УНИК в СЧЁТЗ, тогда формула займёт одну ячейку.
Формула тормозит книгу
СУММПРОИЗВ со СЧЁТЕСЛИ сравнивает каждую строку с каждой. На 50 тысячах строк это 2,5 миллиарда сравнений, и книга заметно задумается при каждом пересчёте. Решение — УНИК, ЧАСТОТА или сводная таблица.
Где освоить Excel системно
Подсчёт уникальных значений входит в десятки задач, которые аналитик решает каждый день. Если формулы приходится каждый раз искать заново, проще один раз пройти курс и разобраться в логике функций, сводных таблиц и Power Query. Начать можно с приёмов для быстрой работы в Excel, а ниже подборка курсов по Excel с ценами и отзывами.
| Курс | Школа | Стоимость со скидкой | В рассрочку | Длительность | Обзор курса от Checkroi |
|---|---|---|---|---|---|
| Excel Перейти на сайт курса |  Нетология | 31 900 ₽ | 1691 ₽/мес. | 8 недель | Обзор курса |
| Power BI & Excel PRO Перейти на сайт курса | Нетология | 38 500 ₽ | 3208 ₽/мес. | 2 месяца | Обзор курса |
| Excel для работы Перейти на сайт курса |  Эдюсон | 16 500 ₽ | 1375 ₽/мес. | 2 недели | Обзор курса |
| Excel + Google Таблицы с нуля до PRO Перейти на сайт курса |  Skillbox | 29 899 ₽ | 2965 ₽/мес. | 4 месяца | Обзор курса |
| Excel для аналитика Перейти на сайт курса |  Moscow Business Academy | 17 200 ₽ | 2866 ₽/мес. | 2 месяца | Обзор курса |
Больше программ — в полном каталоге курсов по Microsoft Excel
