Чтобы найти повторяющиеся значения в столбце Excel, выделите столбец и откройте «Главная» → «Условное форматирование» → «Правила выделения ячеек» → «Повторяющиеся значения». Нажмите «ОК», и Excel зальёт цветом каждую ячейку, значение которой встречается в выделенном диапазоне больше одного раза.
Сами данные не меняются. Если повторы нужно не только увидеть, а посчитать, отфильтровать или вынести отдельным списком, ниже ещё шесть способов: от поиска по Ctrl+F до формулы СЧЁТЕСЛИ и сводной таблицы. А если дубли надо сразу убрать, вам в соседнюю инструкцию о том, как удалить повторяющиеся значения в Excel.
Все примеры ниже на одной таблице: 12 строк складского учёта со столбцами «Артикул», «Товар» и «Склад». В столбце артикулов четыре значения повторяются (ART-101 трижды, ART-205, ART-317 и ART-422 по два раза), а три встречаются по одному разу.
CheckroiПодборка курсов по Microsoft Excel684 курса • 63 школыСравните цены, школы, программу и найдите выгодные предложения по обучениюСравнить→
Как выделить повторяющиеся значения цветом
Условное форматирование нужно, чтобы быстро пробежать список глазами. Справка Microsoft описывает тот же путь через «Условное форматирование».
- Выделите ячейки, в которых ищете повторы. В нашем примере это A2:A13, без заголовка.
- На вкладке «Главная» нажмите «Условное форматирование» → «Правила выделения ячеек» → «Повторяющиеся значения».
- В окне слева оставьте «повторяющиеся», справа выберите цвет. По умолчанию стоит светло-красная заливка с тёмно-красным текстом.
- Нажмите «ОК».
В нашей таблице подсветятся 9 ячеек из 12: все три ART-101 и по две ячейки остальных повторяющихся артикулов. Если в том же окне выбрать «уникальные», Excel покажет обратное, три артикула без пары.
Скриншоты в этой инструкции сделаны в Google Таблицах. Приём там тот же, но готового правила для повторов нет, его задают формулой: «Формат» → «Условное форматирование» → в списке «Форматировать ячейки, если…» пункт «Ваша формула» → =COUNTIF($A$2:$A$13;A2)>1. Английское название функции Таблицы принимают, а в ячейках с русским интерфейсом потом сами показывают её как СЧЁТЕСЛИ.
Убрать подсветку можно там же: «Условное форматирование» → «Удалить правила» → «Удалить правила из выделенных ячеек». Значения в ячейках останутся на месте.
У правила одно ограничение: оно красит и первое вхождение, и все следующие. Понять по цвету, какая ART-101 из трёх появилась в списке раньше, не получится. Для этого нужна формула из раздела про СЧЁТЕСЛИ ниже.
Нет под рукой Excel на компьютере? Правило работает и в браузерной версии, её можно открыть бесплатно через Excel онлайн.
Как найти все копии одного значения
Иногда интересует не весь столбец, а один код: сколько раз в списке встречается ART-101 и в каких строках. Для этого хватит обычного поиска.
- Нажмите Ctrl+F, введите значение и щёлкните «Найти все».
- Внизу окна появится список всех найденных ячеек с адресами и их количество.
- Щёлкните строку списка, и Excel перейдёт к этой ячейке.
Этот и другие сочетания клавиш, которые экономят время в таблицах, собраны в подборке 8 приёмов для быстрой работы в Excel.
Как найти повторы в столбце формулой СЧЁТЕСЛИ
Формула удобнее подсветки, когда список длинный и повторы надо потом отфильтровать или передать коллеге. Она пишет рядом с каждым значением число, сколько раз оно встречается в столбце.
Сколько раз встречается каждое значение
Добавьте справа пустой столбец, назовите его «Повторов» и введите в первую строку данных:
=СЧЁТЕСЛИ($A$2:$A$13;A2)Протяните формулу до конца списка. Знаки доллара закрепляют диапазон, поэтому при протягивании меняется только вторая часть, A2 → A3 → A4. Синтаксис функции в справке Microsoft по СЧЁТЕСЛИ: сначала диапазон, потом условие.
Всё, где результат больше 1, повторяется. У ART-101 будет 3, у ART-530 будет 1.
Нужна понятная метка вместо числа? Оберните формулу в функцию ЕСЛИ:
=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$13;A2)>1;"повтор";"")Как отметить только второе и следующие вхождения
Обе формулы выше метят все копии, включая первую. Часто нужно другое: оставить первую запись как оригинал и найти только лишние. Для этого диапазон делают растущим, закрепляя только его начало:
=ЕСЛИ(СЧЁТЕСЛИ($A$2:A2;A2)>1;"повтор";"")В строке 2 формула смотрит на одну ячейку, в строке 5 на четыре, в строке 13 на все двенадцать. Первая ART-101 получает пустую метку, вторая и третья «повтор». В нашем примере таких меток 5: это ровно те строки, которые можно удалить без потери данных.
В Google Таблицах формулы те же. Разница бывает в разделителе аргументов: он зависит от региона в настройках таблицы. В нашей песочнице это запятая, поэтому на кадре СЧЁТЕСЛИ($A$2:A9,A9). Если формула с точкой с запятой выдаёт ошибку, замените разделитель.
Как показать только строки с повторами
Когда метка стоит в отдельном столбце, остальное делает обычный автофильтр. Подробно он разобран в инструкции про фильтр по столбцам в Excel, здесь короткая версия:
- Щёлкните любую ячейку таблицы и нажмите «Данные» → «Фильтр» (или Ctrl+Shift+L).
- Откройте стрелку в заголовке столбца с меткой.
- Оставьте флажок только у «повтор» и нажмите «ОК».
Если вы ограничились подсветкой из первого раздела, столбец с меткой не обязателен. В меню фильтра есть пункт «Фильтр по цвету»: выберите в нём цвет заливки правила, и Excel оставит только подсвеченные строки.
Как вывести повторы отдельным списком
В Excel 2021, Excel 2024 и Microsoft 365 список повторяющихся значений собирается одной формулой. В справке по функции УНИК перечислены версии, где она есть; в Excel 2016 и 2019 её нет.
=УНИК(ФИЛЬТР(A2:A13;СЧЁТЕСЛИ(A2:A13;A2:A13)>1))=УНИК(A2:A13;;ИСТИНА)Результат: ART-530, ART-648 и ART-759. Список пересчитывается сам, когда вы правите исходный столбец.
Как найти повторяющиеся строки по нескольким
Одинаковый артикул ещё не значит одинаковую запись. ART-101 на складе в Казани и ART-101 на складе в Москве это две разные позиции, а две строки «ART-101, Казань» уже настоящий дубль.
Чтобы сравнивать сразу несколько столбцов, замените СЧЁТЕСЛИ на СЧЁТЕСЛИМН и добавьте пару «диапазон; условие» для каждого столбца:
=ЕСЛИ(СЧЁТЕСЛИМН($A$2:$A$13;A2;$C$2:$C$13;C2)>1;"дубль строки";"")В примере метку получат 6 строк: по паре для ART-101 в Казани, ART-205 в Москве и ART-422 в Самаре. ART-317 в Казани и в Самаре останется без метки, склады разные.
Найденные дубли строк потом убирают кнопкой «Удалить дубликаты» с отметкой нужных столбцов, это разобрано в инструкции по удалению повторов. Ту же проверку можно повесить на подсветку всей строки:
- Выделите A2:C13.
- «Условное форматирование» → «Создать правило» → «Использовать формулу для определения форматируемых ячеек».
- Введите
=СЧЁТЕСЛИМН($A$2:$A$13;$A2;$C$2:$C$13;$C2)>1, задайте цвет и нажмите «ОК».
Доллар перед буквой столбца ($A2) здесь обязателен: без него каждая ячейка строки будет проверять свой столбец, и строка подсветится кусками.
Как посчитать повторы сводной таблицей
Если формулы писать не хочется, а нужно увидеть, какие значения повторяются и сколько раз, подойдёт сводная таблица. Пошагово она описана в статье о том, как сделать сводную таблицу.
- Щёлкните в таблицу и выберите «Вставка» → «Сводная таблица».
- Перетащите поле «Артикул» в область «Строки», а потом ещё раз в область «Значения». Excel посчитает количество.
- Отсортируйте столбец с количеством по убыванию: повторы окажутся наверху.
Для нашего примера сводная покажет семь строк: ART-101 с числом 3, ещё три артикула с числом 2 и три артикула с единицей. Учтите, что подсветить повторы правилом «Повторяющиеся значения» внутри области «Значения» сводной таблицы Excel не даёт.
Канал основателя Checkroi Вани БуявцаКакой способ выбрать
| Задача | Способ | Меняет данные |
|---|---|---|
| Быстро посмотреть, есть ли повторы | Условное форматирование, «Повторяющиеся значения» | Нет |
| Посчитать, сколько раз встречается значение | СЧЁТЕСЛИ во вспомогательном столбце | Добавляет столбец |
| Найти только лишние копии, оставив первую | СЧЁТЕСЛИ с растущим диапазоном | Добавляет столбец |
| Получить список повторяющихся значений | УНИК + ФИЛЬТР (Excel 2021 и новее) | Нет |
| Сравнить строки по нескольким полям | СЧЁТЕСЛИМН | Добавляет столбец |
| Отчёт «что и сколько раз» без формул | Сводная таблица | Нет |
Сравнить два разных столбца или два списка на совпадения удобнее через ВПР: она ищет значение из одного списка в другом и возвращает #Н/Д, если пары нет.
Почему Excel не видит повторы или находит лишние
Самая частая жалоба: значения на экране одинаковые, а правило их не красит. Причины почти всегда одни и те же.
- Лишние пробелы. «ART-101» и «ART-101 » с пробелом в конце для Excel разные строки. Microsoft советует чистить такие данные функцией СЖПРОБЕЛЫ: сделайте рядом столбец
=СЖПРОБЕЛЫ(A2)и ищите повторы уже в нём. - Число записано как текст. Если часть кодов вставили из выгрузки, в ячейке может оказаться текст «1042» рядом с числом 1042. Такие ячейки обычно помечены зелёным треугольником в углу. Выделите их и выберите «Преобразовать в число».
- Регистр букв. СЧЁТЕСЛИ и условное форматирование регистр не различают, «art-101» и «ART-101» для них одно и то же. Если это разные коды, нужна формула с СОВПАД:
=СУММПРОИЗВ(--СОВПАД($A$2:$A$13;A2))>1. - Длинные номера, наоборот, сливаются. Номера карт, счетов и другие коды длиннее 15 цифр СЧЁТЕСЛИ сравнивает только по первым 15 знакам, даже если они записаны как текст. Разные номера с одинаковым началом формула посчитает повторами. Об этой особенности спрашивали на форуме Microsoft Tech Community. Обходной путь:
=СУММПРОИЗВ(--($A$2:$A$13=A2))>1, он сравнивает строки целиком. - Строки длиннее 255 символов. На таких значениях СЧЁТЕСЛИ возвращает неверный результат, это ограничение описано в справке по функции. Для длинных описаний подойдёт та же формула с СУММПРОИЗВ.
Ещё одна ловушка: правило подсветки срабатывает только в том диапазоне, к которому применено. Добавили строки ниже A13, а их не красит? Расширьте диапазон в «Условное форматирование» → «Управление правилами» или с самого начала оформите данные умной таблицей, она растёт сама.
Где освоить Excel системно
Поиск повторов закрывает одну задачу, а в работе с таблицами их десятки: сводные, ВПР, Power Query, проверка данных. Чем отличается человек, который знает Excel «на уровне пользователя», от того, кто на нём зарабатывает, мы разбирали в статье о специалисте по Excel.
Если Excel нужен для аналитики или отчётности каждый день, быстрее пройти курс с практикой на реальных файлах. В каталоге курсов по Excel программы разных школ с ценами и учебными планами, ниже пять из них.
| Курс | Школа | Стоимость со скидкой | В рассрочку | Длительность | Обзор курса от Checkroi |
|---|---|---|---|---|---|
| Excel Перейти на сайт курса |  Нетология | 29 000 ₽ | 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
