Функция УНИК в Excel (UNIQUE): как работает, пример и формула под вашу таблицу
Синтаксис в русском Excel
В английском Excel, Google Таблицах и внутри файла .xlsx
Аргументы
массив | откуда |
[по_столбцам] | ЛОЖЬ |
[только_один_раз] | ЛОЖЬ |
Пример
| A | B | |
|---|---|---|
| 1 | Менеджер | |
| 2 | Петрова | Петрова |
| 3 | Сидоров | |
| 4 | Петрова | |
| 5 | Ким |
Результат в ячейке B2 посчитан LibreOffice на сервере Формулника.
Частые ошибки
под ячейкой занято место — освободите ячейки ниже
сводная таблица или «Удалить дубликаты»
Где работает
| Программа | УНИК | |
|---|---|---|
| Excel 2010 | нет | появилась в Excel 2021 |
| Excel 2013 | нет | появилась в Excel 2021 |
| Excel 2016 | нет | появилась в Excel 2021 |
| Excel 2019 | нет | появилась в Excel 2021 |
| Excel 2021 | есть | |
| Excel 2024 | есть | |
| Excel 365 | есть | |
| LibreOffice Calc | есть | проверено |
| Google Таблицы | проверьте | новая функция Excel 2021 — в Google Таблицы не проверена |
| Яндекс Таблицы | проверьте | новая функция Excel 2021 — в Яндекс Таблицы не проверена |
| Р7-Офис | проверьте | новая функция Excel 2021 — в Р7-Офис не проверена |
| МойОфис | проверьте | новая функция Excel 2021 — в МойОфис не проверена |
| MOffice Sheets | есть | проверено |
Похожие функции
УНИК: сортированный список, без пустых, подсчёт и замена в старом Excel
УНИК — функция динамического массива: одна формула заполняет столько строк, сколько найдено разных значений, и сама пересчитывается при изменении данных. Она есть только в Microsoft 365, Excel 2021 и 2024; в Excel 2016 и 2019 будет #ИМЯ?. Отсортированный список без пустых строк даёт =СОРТ(УНИК(ФИЛЬТР(A2:A500;A2:A500<>""))), количество разных значений — =СЧЁТЗ(УНИК(A2:A500)).
По шагам
- Встаньте в ячейку, под которой свободно столько строк, сколько может быть значений, — иначе будет #ПЕРЕНОС!.
- Введите УНИК со ссылкой на столбец с данными и нажмите Enter — протягивать не нужно.
- Если в источнике есть пустые ячейки, оберните диапазон в ФИЛЬТР, иначе в списке появится 0.
- Нужен порядок по алфавиту — оберните результат в СОРТ.
- Чтобы сослаться на весь разлившийся список, используйте адрес первой ячейки со знаком #:
E2#.
Список без пустых и по алфавиту
Выгрузка обычно с запасом пустых строк внизу. УНИК считает пустоту отдельным значением и показывает её как 0. ФИЛЬТР убирает пустые до поиска повторов, СОРТ упорядочивает результат. Для сортировки по убыванию — СОРТ(...;1;-1).
=СОРТ(УНИК(ФИЛЬТР(A2:A500;A2:A500<>"")))Уникальные пары и значения, которые встретились один раз
Если передать два столбца, УНИК ищет уникальные сочетания: =УНИК(B2:C500) даст пары «менеджер — регион» без повторов. Третий аргумент ИСТИНА меняет смысл: остаются только значения, встретившиеся ровно один раз, — удобно найти клиентов с единственной покупкой.
=УНИК(A2:A500;ЛОЖЬ;ИСТИНА)Выпадающий список, который растёт сам
Поставьте УНИК на служебном листе, например в E2. В «Данные → Проверка данных → Список» в поле источника напишите =$E$2#. Новые менеджеры в данных сразу появятся в выпадающем списке, без ручного расширения диапазона.
=СЧЁТЗ($E$2#)В Excel 2016 и 2019: формула без УНИК
В старых версиях есть «Данные → Удалить дубликаты» и расширенный фильтр с флажком «Только уникальные записи», но они не обновляются сами. Формульная замена: в E2 (над ней в E1 заголовок) введите формулу ниже как формулу массива — Ctrl+Shift+Enter — и протяните вниз с запасом. Каждая строка ищет первое значение, которого ещё нет выше; когда значения кончатся, ячейки останутся пустыми.
=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$500;ПОИСКПОЗ(0;СЧЁТЕСЛИ($E$1:E1;$A$2:$A$500);0));"")Задача → формула (Microsoft 365, Excel 2021/2024)
| Задача | Формула |
|---|---|
| Сколько разных значений без пустых | =СЧЁТЗ(УНИК(ФИЛЬТР(A2:A500;A2:A500<>""))) |
| Уникальные без лишних пробелов | =УНИК(СЖПРОБЕЛЫ(A2:A500)) |
| Уникальные по условию (только Москва) | =УНИК(ФИЛЬТР(B2:B500;C2:C500="Москва")) |
| Уникальные значения из строки заголовков | =УНИК(B1:M1;ИСТИНА) |
| Итог по каждому уникальному | =СУММЕСЛИ(B2:B500;E2#;D2:D500) |
Частые ошибки
- #ПЕРЕНОС! внутри «умной таблицы» — формулы с разливом в таблицах Excel (Ctrl+T) не работают. Поставьте УНИК на обычные ячейки рядом с таблицей.
- «Петрова» и «ПЕТРОВА» стали одним значением — УНИК не различает регистр. Если для вас это разные записи, сначала разберитесь с данными — обычно это одна и та же фамилия, набранная по-разному.
- Значение повторяется в результате — отличается невидимое: пробел в конце или неразрывный пробел. Оберните диапазон в СЖПРОБЕЛЫ, а неразрывные пробелы замените через ПОДСТАВИТЬ.
- У коллеги в ячейке #ИМЯ?, а в строке формул
_xlfn.UNIQUE— у него Excel без динамических массивов. Для таких получателей вставьте результат как значения или используйте формулу из раздела про старые версии.
Вопросы и ответы
В какой версии Excel есть функция УНИК?
В Microsoft 365, Excel 2021, Excel 2024 и Excel в браузере. В Excel 2019 и раньше её нет, формула покажет #ИМЯ?.
Как посчитать количество уникальных значений в Excel?
В новых версиях — СЧЁТЗ от УНИК. В старых — СУММПРОИЗВ(1/СЧЁТЕСЛИ(диапазон;диапазон)) при условии, что в диапазоне нет пустых ячеек.
Как убрать 0 из результата УНИК?
Ноль — это пустые ячейки источника. Отфильтруйте их до УНИК через ФИЛЬТР с условием «не пусто».
Что делать с ошибкой #ПЕРЕНОС!?
Освободите ячейки ниже и правее формулы: в них что-то есть, хотя бы пробел. Щёлкните по ошибке — Excel подсветит мешающую ячейку.
Можно ли получить уникальные значения сразу по нескольким столбцам?
Да, передайте УНИК диапазон из нескольких столбцов — она вернёт неповторяющиеся строки целиком. Чтобы склеить несколько столбцов в один список, их сначала объединяют функцией ВСТОЛБИК.
Можно сделать список без повторов из моего файла, если у меня старый Excel?
Да, Формулник соберёт книгу под вашу версию Excel: с УНИК для новых версий или с совместимой формулой для 2016–2019.