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

В Excel 2021, 2024 и Microsoft 365 список без повторов даёт формула =УНИК(A2:A11), в старых версиях — расширенный фильтр с флажком «Только уникальные записи».

Разобрали шесть способов на одном примере, разницу между уникальными и различными значениями и ошибки, из-за которых в списке остаются лишние строки.

Статью написал:
Ваня Буявец, продюсер, основатель Checkroi
Ваня Буявец
Основатель Checkroi, продюсер, эксперт в выборе онлайн-курсов
Все 3308 статей автора Подписаться на Телеграм-канал
Одобрено экспертом:
Наташа Буявец, основатель Checkroi, эксперт по онлайн-курсам
Наташа Буявец
Основательница Checkroi, продюсер Youtube-каналов, эксперт по онлайн-курсам
Все 3962 экспертных мнения Подписаться на Телеграм-канал
Обложка: Как получить уникальные значения из столбца в Excel: 6 способов

Чтобы получить уникальные значения из столбца в Excel 2021, 2024 или Microsoft 365, введите в свободную ячейку формулу =УНИК(A2:A11) и нажмите Enter: список без повторов сам разольётся вниз. В Excel 2019 и старше функции нет, там работает расширенный фильтр: «Данные» → «Дополнительно» → «Скопировать результат в другое место» и флажок «Только уникальные записи».

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

Уникальные и различные значения: какие вам нужны

Рой разбирает длинный столбец таблицы и собирает из него короткий список без повторов

В Excel слово «уникальные» означает две разные вещи. Перед выбором способа стоит понять, какую из них вы ищете.

Возьмём столбец A с десятью городами: Москва, Казань, Пермь, Москва, Самара, Казань, Москва, Томск, Пермь, Сочи. Москва встречается трижды, Казань и Пермь по два раза, остальные по одному.

Что ищем Результат на примере Чем получить
Различные значения: каждое по одному разу Москва, Казань, Пермь, Самара, Томск, Сочи (6) УНИК, расширенный фильтр, «Удалить дубликаты», сводная таблица
Уникальные в строгом смысле: только то, что встречается один раз Самара, Томск, Сочи (3) УНИК с третьим аргументом ИСТИНА

Чаще всего под «уникальными» имеют в виду первый вариант, то есть список без повторов. Его и дают почти все способы ниже. Если нужно не вывести список, а узнать, сколько в нём значений, это отдельная задача, она разобрана в статье о том, как посчитать количество уникальных значений.

Способ 1: функция УНИК в Excel 2021, 2024 и Microsoft 365

Функция УНИК возвращает список уникальных значений из диапазона. По справке Microsoft она есть в Excel для Microsoft 365, Excel 2024 и Excel 2021, в том числе в версиях для Mac и в веб-версии.

  1. Встаньте в пустую ячейку справа от данных, например C2.
  2. Введите =УНИК(A2:A11), где A2:A11 — столбец без заголовка.
  3. Нажмите Enter. Формулу не нужно протягивать: результат сам займёт столько строк, сколько получилось значений.

В нашем примере в C2:C7 появятся шесть городов в том порядке, в котором они впервые встречаются в столбце.

Функция UNIQUE в Google Таблицах: список городов без повторов и города, которые встречаются один раз
Формула =UNIQUE(A2:A11) в C2 вернула шесть городов, а с третьим аргументом TRUE в столбце D остались Самара, Томск и Сочи. В Excel та же функция называется УНИК

Отсортировать список по алфавиту

Оберните УНИК в функцию СОРТ:

=СОРТ(УНИК(A2:A11))

Результат: Казань, Москва, Пермь, Самара, Сочи, Томск. Другие варианты сортировки описаны в статье про сортировку по алфавиту в Excel.

Оставить только значения, которые встречаются один раз

Синтаксис функции такой: =УНИК(массив;[by_col];[exactly_once]). Второй аргумент управляет направлением сравнения: если он пропущен или равен ЛОЖЬ, сравниваются строки. Третий аргумент со значением ИСТИНА, по справке, возвращает строки, которые встречаются только один раз.

=УНИК(A2:A11;;ИСТИНА)

Результат: Самара, Томск, Сочи. Две точки с запятой подряд значат, что второй аргумент пропущен.

Уникальные значения по условию

Допустим, в столбце B записан канал заказа: «Сайт» или «Звонок». Нужно узнать, из каких городов заказывали через сайт, и каждый город назвать один раз. Сначала ФИЛЬТР оставляет нужные строки, потом УНИК убирает повторы:

=УНИК(ФИЛЬТР(A2:A11;B2:B11="Сайт"))

На нашем примере получится четыре города: Москва, Пермь, Казань, Томск. С условием «Звонок» список будет другим: Казань, Самара, Москва, Сочи.

Если ФИЛЬТР не нашёл ни одной строки, формула вернёт ошибку, в справке Microsoft она названа #CALC!. Добавьте третий аргумент, например ФИЛЬТР(A2:A11;B2:B11="Почта";"нет"), и вместо ошибки появится понятное «нет».

Уникальные сочетания по двум столбцам

Если передать УНИК сразу два столбца, она сравнит строки целиком. Формула =УНИК(A2:B11) вернёт пары «город + канал» без повторов, на примере их восемь: Москва и Казань встречаются в паре с обоими каналами.

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

=УНИК(A2:A11&" — "&B2:B11)

Результат начинается так: «Москва — Сайт», «Казань — Звонок», «Пермь — Сайт».

Список, который обновляется сам

Если данные лежат в умной таблице, Microsoft пишет, что массив будет автоматически изменять размер при добавлении и удалении данных. Например, для столбца «Город» таблицы «Заказы» формула выглядит так:

=УНИК(Заказы[Город])

Саму формулу ставьте на обычный лист рядом с таблицей, а не внутрь неё. Почему, разберём в разделе ошибок.

Канал основателя Checkroi Вани Буявца3 800 человек читают мой Телеграм-канал про нейросетиСобрал промпты для Claude Code и ChatGPT, разборы ИИ-инструментов и лайфхаки по продвижению бизнеса в одном местеПрисоединиться

Способ 2: расширенный фильтр в любой версии Excel

Этот способ работает и в Excel 2016 и 2019, где УНИК нет. Он копирует уникальные значения в новое место и, как сказано в справке Microsoft для Mac, исходные данные при этом не изменятся.

  1. Выделите столбец вместе с заголовком, например A1:A11.
  2. На вкладке «Данные» в группе «Сортировка и фильтр» нажмите кнопку «Дополнительно».
  3. Выберите «Скопировать результат в другое место».
  4. В поле «Копировать в» укажите первую ячейку будущего списка, например C1.
  5. Установите флажок «Только уникальные записи» и нажмите «ОК».

В C1 окажется заголовок «Город», под ним шесть городов. Итог — обычные значения, а не формула: если в столбце A что-то поменяется, фильтр придётся запустить ещё раз.

Если выбрать «Фильтровать список на месте», отдельного списка не будет: Excel временно скроет повторы в исходном столбце. Так можно глазами проверить результат перед чисткой.

Подробнее об отборе по условиям тем же окном — в статье про фильтр по столбцам в Excel.

Способ 3: «Удалить дубликаты» на копии столбца

Самый короткий путь по числу кликов, но с одной оговоркой: команда удаляет данные. Microsoft советует скопировать исходный диапазон на другой лист или в другую книгу, прежде чем удалять повторяющиеся значения.

  1. Скопируйте столбец A в свободный столбец или на новый лист.
  2. Выделите копию.
  3. На вкладке «Данные» в группе «Работа с данными» выберите «Удалить дубликаты».
  4. Оставьте флажок у нужного столбца и нажмите «ОК».

Excel покажет сообщение о том, сколько повторов удалено и сколько уникальных значений осталось. Ошиблись со столбцом — нажмите Ctrl+Z.

Скриншоты в этом разделе сделаны в Google Таблицах. Там команда лежит в другом месте: «Данные» → «Очистка данных» → «Удалить повторы», а смысл тот же.

Меню «Данные» → «Очистка данных» → «Удалить повторы» в Google Таблицах
В Google Таблицах удаление повторов спрятано в подменю «Очистка данных». В Excel та же команда стоит прямо на вкладке «Данные» и называется «Удалить дубликаты»

В открывшемся окне видно, сколько строк и столбцов попало в выделение. Если в первой строке заголовок, отметьте это в окне флажком, иначе он будет участвовать в сравнении наравне с городами.

Окно удаления повторов в Google Таблицах для столбца с городами
В выделение попали 11 строк вместе с заголовком. Если первая строка — заголовок, отметьте это флажком, иначе «Город» будет сравниваться наравне с названиями городов

Все тонкости удаления повторов, включая строки по нескольким столбцам и проверку результата, собраны в статье о том, как удалить повторяющиеся значения в Excel.

CheckroiCheckroiПодборка курсов по Microsoft Excel680 курсов • 64 школыСравните цены, школы, программу и найдите выгодные предложения по обучениюСравнить→

Способ 4: сводная таблица

Карточки одинаковых цветов сложены в стопки, по одной карточке каждого цвета вынесено в ряд

Сводная таблица группирует одинаковые значения в одну строку. Поэтому список уникальных значений в ней получается без единой формулы.

  1. Выделите столбец с заголовком. По справке, данные должны быть организованы в столбцы с одной строкой заголовков.
  2. На вкладке «Вставка» нажмите кнопку сводной таблицы.
  3. Выберите, куда поместить отчёт: новый или существующий лист, и нажмите «ОК».
  4. В области «Поля сводной таблицы» поставьте флажок у поля «Город».

Текстовое поле Excel по умолчанию отправит в область строк, и в отчёте появятся шесть городов.

Минус способа: при новых данных отчёт сам не меняется. По справке Microsoft о сводных таблицах, после изменения источника нужно щёлкнуть правой кнопкой по отчёту и выбрать «Обновить». Пошаговый разбор с примерами — в статье как сделать сводную таблицу в Excel.

Способ 5: Power Query для данных, которые приходят регулярно

Если каждую неделю вы получаете новую выгрузку и каждый раз чистите её от повторов, быстрее один раз настроить запрос в Power Query. В справке Microsoft он указан для Excel 2016, 2019, 2021, 2024 и Microsoft 365.

  1. Загрузите таблицу в редактор Power Query. Как это сделать и чем запрос отличается от формулы, рассказано в нашей статье о Power Query.
  2. Выделите столбец, щёлкнув его заголовок. Несколько столбцов выбирают с Shift или Ctrl.
  3. Выберите «Главная» → «Удалить строки» → «Удалить дубликаты».
  4. Верните результат на лист.

Дальше при новой выгрузке достаточно обновить запрос: шаги повторятся без вашего участия. По справке Power Query, результат зависит от выбранных столбцов, так что для списка городов выделяйте только «Город».

Ваня БуявецКанал основателя Checkroi Вани БуявцаЗабирайте промпты и обучение по нейросетям в моём Телеграм-каналеБольше 3 800 человек уже применяют Claude Code, ChatGPT и другие нейросети в работе, учёбе, бизнесе и жизниПерейти в канал

Способ 6: формула для Excel 2019 и старых версий

Когда список без повторов нужен формулой, а УНИК нет, помогает связка ИНДЕКС, ПОИСКПОЗ и СЧЁТЕСЛИ. Мы проверили её на примере из этой статьи, результат совпал с УНИК.

  1. В C1 напишите заголовок, например «Список». Он нужен формуле как стартовая точка.
  2. В C2 введите формулу:
=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$11;ПОИСКПОЗ(0;СЧЁТЕСЛИ($C$1:C1;$A$2:$A$11);0));"")
  1. Подтвердите её сочетанием Ctrl+Shift+Enter, а не простым Enter.
  2. Протяните C2 вниз до C11.

В C2:C7 появятся шесть городов, ниже пустые ячейки. Логика такая: СЧЁТЕСЛИ проверяет, какие города уже есть в списке выше, ПОИСКПОЗ находит первый ещё не выписанный, ИНДЕКС его возвращает, а ЕСЛИОШИБКА прячет ошибку, когда значения кончились.

Про Ctrl+Shift+Enter в справке о формулах массива сказано, что в устаревших формулах массива подтверждение шло через CTRL+SHIFT+ВВОД. В Microsoft 365 формулу можно ввести и обычным Enter, но там проще взять УНИК. Чем отличаются старые и новые формулы массива, мы разбирали в статье про массивы в Excel.

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

Иногда отдельный список не нужен: достаточно увидеть уникальные значения прямо в столбце. Для этого подходит условное форматирование.

  1. Выделите столбец A2:A11.
  2. На вкладке «Главная» в группе «Стили» нажмите «Условное форматирование» и выберите «Создать правило».
  3. Выберите тип «Форматировать только уникальные или повторяющиеся значения».
  4. В списке значений выберите «уникальные», задайте цвет и нажмите «ОК».

На примере подсветятся Самара, Томск и Сочи, то есть те же три города, что вернула УНИК с третьим аргументом ИСТИНА. Подсветка ничего не удаляет и не переносит, её можно снять в любой момент. Обратная задача, подсветка и поиск повторов, разобрана в статье о том, как найти повторяющиеся значения в столбце.

Какой способ выбрать

Ситуация Способ Обновляется сам
Excel 2021, 2024, Microsoft 365 УНИК да
Нужны значения, которые встречаются ровно один раз УНИК с третьим аргументом ИСТИНА да
Excel 2016 или 2019, разовая задача расширенный фильтр нет
Нужно быстро и не жалко копии «Удалить дубликаты» на копии нет
Данные уже разбираете сводной таблицей сводная таблица после «Обновить»
Одна и та же выгрузка приходит регулярно Power Query после обновления запроса
Excel 2019, нужен именно формульный список ИНДЕКС + ПОИСКПОЗ + СЧЁТЕСЛИ да

Если сомневаетесь, начните с УНИК: формулу легко поправить, а исходные данные остаются нетронутыми. Если её нет в вашей версии, берите расширенный фильтр для разовой задачи и Power Query для повторяющейся. «Удалить дубликаты» оставьте на случай, когда копия уже сделана и список нужен через минуту.

Если задача шире списка, посмотрите соседние инструкции: выборка из списка по условию и сравнение двух столбцов на совпадения.

Частые ошибки и что с ними делать

Рой радостно отмечает галочками чек-лист рядом с ровным списком без повторов

Одинаковые на вид значения не склеились

Чаще всего виноват пробел в конце ячейки: для Excel «Москва» и «Москва » с пробелом — разные значения, и в списке окажутся обе. Google в справке по UNIQUE называет ту же причину.

Решение: уберите пробелы функцией СЖПРОБЕЛЫ внутри формулы:

=УНИК(СЖПРОБЕЛЫ(ФИЛЬТР(A2:A13;A2:A13<>"")))

В списке появилась пустая строка

Если в диапазоне есть пустые ячейки, УНИК добавит в результат и пустое значение. Отсейте их функцией ФИЛЬТР: =УНИК(ФИЛЬТР(A2:A100;A2:A100<>"")). С этой обёрткой можно ссылаться на столбец с запасом, если данные ещё будут дописываться.

Ошибка #ПЕРЕНОС!

По справке Microsoft, она возникает, если диапазон переноса для формулы не пустой. На деле это значит, что под ячейкой с УНИК что-то лежит и мешает списку развернуться. Выделите ячейку с ошибкой, нажмите значок предупреждения и выберите «Выделить препятствующие ячейки», затем очистите их.

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

Формула сломалась после закрытия другой книги

Если УНИК берёт данные из другого файла, связь работает, только пока открыты обе книги. После закрытия источника формула при обновлении вернёт ошибку ссылки: в русском Excel это #ССЫЛКА!, в справке она подписана как #REF!. Скопируйте данные в свою книгу или откройте источник.

Даты разного формата не считаются повторами

Это касается расширенного фильтра и «Удалить дубликаты». По справке, сравнение зависит от того, что отображается в ячейке, а не от значения внутри: одна и та же дата в виде «08.03.2006» и «8 марта 2006 г.» останется двумя разными записями. Перед чисткой приведите столбец к одному формату через «Формат ячеек».

Функции УНИК нет в списке

Значит, у вас Excel 2019 или старше: в справке функция указана только для Excel 2021 и новее. Используйте расширенный фильтр, сводную таблицу или формулу из способа 6.

Как получить уникальные значения в Google Таблицах

В Google Таблицах есть та же функция, только с английским названием UNIQUE. По справке Google, она возвращает строки в том же порядке, в котором они располагаются в диапазоне, а третий аргумент exactly_once оставляет только записи без копий.

=UNIQUE(A2:A11)
=UNIQUE(A2:A11;FALSE;TRUE)

Первая формула вернёт шесть городов, вторая — Самару, Томск и Сочи, как на первом скриншоте. В русской локали аргументы разделяет точка с запятой. Удалить повторы без формулы можно через «Данные» → «Очистка данных» → «Удалить повторы», это описано в справке Google Таблиц.

Где освоить Excel системно

Список без повторов — одна из десятков задач, которые встречаются в работе с таблицами каждую неделю. Если хочется не искать каждую формулу заново, а уверенно собирать отчёты, сводные и запросы, посмотрите курсы по Excel в нашем каталоге: там можно сравнить программы по цене, длительности и формату.

КурсШколаСтоимость со скидкойВ рассрочкуДлитель­ностьОбзор курса от Checkroi
Excel
Перейти на сайт курса
НетологияНетология31 900 ₽1691 ₽/мес.8 недельОбзор курса
Power BI & Excel PRO
Перейти на сайт курса
НетологияНетология38 500 ₽3208 ₽/мес.2 месяцаОбзор курса
Excel для работы
Перейти на сайт курса
Академия ЭдюсонЭдюсон16 500 ₽1375 ₽/мес.2 неделиОбзор курса
Excel + Google Таблицы с нуля до PRO
Перейти на сайт курса
SkillboxSkillbox29 899 ₽2965 ₽/мес.4 месяцаОбзор курса
Excel для аналитика
Перейти на сайт курса
Moscow Business AcademyMoscow Business Academy17 200 ₽2866 ₽/мес.2 месяцаОбзор курса

Больше программ — в полном каталоге курсов по Microsoft Excel

А если нужен короткий справочник под рукой, начните с обзора основных функций Excel с примерами формул.

Часто задаваемые вопросы

Какая формула выводит уникальные значения из столбца Excel?

В Excel 2021, 2024 и Microsoft 365 — =УНИК(A2:A11): список без повторов сам разольётся вниз. В Excel 2019 и старше подойдёт формула =ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$11;ПОИСКПОЗ(0;СЧЁТЕСЛИ($C$1:C1;$A$2:$A$11);0));""), которую вводят сочетанием Ctrl+Shift+Enter и протягивают вниз.

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

Без формул это делает расширенный фильтр: «Данные» → «Дополнительно» → «Скопировать результат в другое место» и флажок «Только уникальные записи». Исходный столбец при этом не меняется. Если повторы нужно удалить на месте, используйте «Данные» → «Удалить дубликаты», но сначала сделайте копию.

Чем уникальные значения отличаются от различных?

Различные — это каждое значение по одному разу: в списке «Москва, Казань, Москва» их два. Уникальные в строгом смысле — только те, что встречаются один раз, здесь это одна Казань. Первый вариант даёт =УНИК(A2:A11), второй — =УНИК(A2:A11;;ИСТИНА).

Почему не работает функция УНИК?

Если функции нет в списке, у вас Excel 2019 или старше: УНИК есть в Excel 2021, 2024 и Microsoft 365. Если формула показывает #ПЕРЕНОС!, под ней заняты ячейки или она стоит внутри умной таблицы, где формулы с переносом не поддерживаются. Освободите место под формулой или вынесите её из таблицы.

Как выделить уникальные значения цветом?

Выделите столбец, на вкладке «Главная» нажмите «Условное форматирование» → «Создать правило», выберите тип «Форматировать только уникальные или повторяющиеся значения» и в списке укажите «уникальные». Подсветка ничего не удаляет и снимается в любой момент.

Как получить уникальные значения в Google Таблицах?

Функцией =UNIQUE(A2:A11). Чтобы оставить только значения без копий, добавьте третий аргумент: =UNIQUE(A2:A11;FALSE;TRUE). Без формулы повторы удаляют через «Данные» → «Очистка данных» → «Удалить повторы».

Читайте Checkroi первым в Google
Добавьте Checkroi в избранные источники — и наши разборы курсов и обзоры школ будут показываться выше в вашей выдаче Google.
Оставить комментарий
0 комментариев
Форма комментария

Оставьте комментарий

Напишите, что думаете. Нам важно ваше мнение!