Дубликаты в Excel: как найти, выделить и удалить повторы

Коротко

Дубль — это не одинаковое значение в столбце, а две строки об одном и том же событии. Повторяющийся клиент в списке заказов нормален, повторяющийся номер заказа — нет. Поэтому любая чистка начинается с вопроса, какой столбец (или пара столбцов) однозначно определяет строку. Это и есть ключ.

• Посмотреть повторы: Главная → Условное форматирование → Правила выделения ячеек → Повторяющиеся значения.
• Пометить строки формулой: =СЧЁТЕСЛИ($A$2:$A$11;A2)>1 — ИСТИНА у каждой строки, ключ которой встречается больше одного раза.
• Отделить копии от оригиналов: =СЧЁТЕСЛИ($A$2:A2;A2) — у первого вхождения 1, у копий 2, 3 и дальше.
• Удалить: Данные → Работа с данными → Удалить дубликаты, отметить только столбцы ключа. Остаётся первое вхождение, остальные строки удаляются целиком.
• Не трогать исходные данные: =УНИК(A2:E11) в Microsoft 365, Excel 2021 и 2024 выводит строки без повторов в другое место листа.
• После удаления сверьте число строк и сумму: так видно, сколько ушло и сколько стоили удалённые строки.

Учебная таблица: выгрузка заказов с повторами

Лист «Заказы» — десять строк, данные учебные. В строке 5 повторно приехал заказ 5102, в строке 9 — заказ 5104: строки совпадают с оригиналами во всех столбцах. Заказы 5103, 5105 и 5107 — не копии, а повторные покупки клиентов anna, oleg и ivan. Сумма столбца E — 20 500 ₽, но два заказа на 1 800 и 950 ₽ в ней посчитаны дважды.

Как выделить повторяющиеся значения цветом

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

Теперь сделайте то же со столбцом C. Подсветятся восемь ячеек из десяти: anna встречается дважды, oleg и ivan — по три раза. Ни одна из этих строк, кроме двух копий, не лишняя. Правило не знает, что такое дубль; оно отмечает одинаковые значения в выбранном диапазоне. Отвечать на вопрос «эти строки описывают одно событие?» придётся вам.

Чтобы подсветить строку целиком, нужно правило с формулой. Выделите A2:E11, выберите Главная → Условное форматирование → Создать правило → «Использовать формулу для определения форматируемых ячеек» и введите =СЧЁТЕСЛИ($A$2:$A$11;$A2)>1. Знак $ перед A в $A2 закрепляет столбец ключа, поэтому каждая ячейка строки проверяет свой номер заказа. В области «Значения» сводной таблицы правило повторяющихся значений, по справке Microsoft, не работает: сначала соберите сводную, потом проверяйте исходный лист.

Как найти повторяющиеся значения в столбце формулой СЧЁТЕСЛИ

Цвет удобен глазу, но его не отфильтруешь в формуле и не посчитаешь. Столбец-флаг надёжнее. В G2 напишите =СЧЁТЕСЛИ($A$2:$A$11;A2)>1 и протяните до G11: ИСТИНА получат строки 3, 5, 6 и 9. Фильтр по ИСТИНА покажет все строки с повторяющимся ключом — и оригиналы, и копии.

Чтобы отличить копию от оригинала, нужен нарастающий счёт: =СЧЁТЕСЛИ($A$2:A2;A2) в H2. Начало диапазона закреплено, конец растёт вместе со строкой, поэтому формула считает, сколько раз номер встретился до текущей строки включительно. У строк 5 и 9 будет 2, у остальных 1. Фильтр по «больше 1» оставит ровно лишние строки — их можно проверить глазами, прежде чем удалять.

Если ключ состоит из двух столбцов, например номер заказа повторяется в разных магазинах, используйте СЧЁТЕСЛИМН: =СЧЁТЕСЛИМН($A$2:$A$11;A2;$C$2:$C$11;C2).

У СЧЁТЕСЛИ есть особенности, которые на ключах всплывают чаще, чем кажется. Регистр в условии функция не различает: «oleg@ya.ru» и «Oleg@ya.ru» для неё одно и то же. Знаки * и ? в условии работают как подстановочные; если они есть в самих значениях, например в артикуле «A*12», перед ними нужна тильда ~. Строки длиннее 255 знаков, по справке Microsoft, дают неверный результат.

Как удалить дубликаты в Excel

Встаньте в таблицу и выберите Данные → Работа с данными → Удалить дубликаты. Если в первой строке заголовки, оставьте отмеченным флажок «Мои данные содержат заголовки». В списке столбцов отметьте только те, что составляют ключ, — здесь order_id — и нажмите ОК. Excel сообщит, сколько повторяющихся значений удалено и сколько уникальных осталось. В Excel в браузере команда та же: Данные → Удалить дубликаты.

Три правила из справки Microsoft определяют результат. Первое: из каждой группы одинаковых строк остаётся первая сверху, остальные удаляются целиком, со всеми столбцами, даже теми, что вы не отмечали. Второе: удаление окончательное, вернуть строки можно только через Ctrl+Z, пока файл открыт, поэтому Microsoft советует сначала скопировать данные на другой лист. Третье: сравнивается отображаемое значение, а не хранимое. Одна и та же дата в форматах «08.03.2006» и «8 марта 2006 г.» дублем не считается. Таблицу со структурой или промежуточными итогами сначала нужно от них освободить.

Удаление в одном столбце — частый запрос, и в нём кроется ловушка. Если выделить только столбец A и нажать кнопку, Excel предложит расширить выделение. Откажетесь — повторы исчезнут из столбца A, а соседние столбцы останутся на месте, и номера заказов съедут относительно сумм. Для списка уникальных значений одного столбца надёжнее скопировать его на новый лист или воспользоваться УНИК.

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

УНИК: список без повторов, не трогая исходник

Функция УНИК возвращает уникальные строки или значения диапазона и выводит их рядом. Исходная выгрузка остаётся как есть, а при её обновлении результат пересчитается сам. По справке Microsoft функция есть в Excel для Microsoft 365, Excel 2021 и 2024 (в том числе для Mac), в Excel в браузере и мобильных версиях; в Excel 2016 и 2019 её нет. Результат занимает столько ячеек, сколько нужно: место под ним должно быть свободным.

Синтаксис: УНИК(массив; [по_столбцам]; [только_один_раз]). Второй аргумент переключает сравнение со строк на столбцы. Третий, если поставить ИСТИНА, оставляет только значения, которые встречаются ровно один раз. Это обратная задача: например, клиенты с единственной покупкой.

Почти дубли: пробелы, регистр и разные написания

Самые неприятные дубли выглядят как разные значения. Лист «Контакты» собран из двух форм заявок: в одной адрес ввели с заглавной буквы, в другой при копировании прилипли пробелы. Человек видит три раза одного Олега и два раза одну Анну. Excel видит иначе, и разные инструменты расходятся между собой.

СЧЁТЕСЛИ регистр не различает, поэтому «oleg@ya.ru» и «Oleg@ya.ru» для неё совпадают, а адрес с пробелом в конце — уже нет. Проверить, есть ли невидимый хвост, помогает ДЛСТР: у «oleg@ya.ru» длина 10, у копии с пробелом 11. Обычное сравнение =A2=A3 тоже не различает регистр и вернёт ИСТИНА; учитывает его только СОВПАД. «Удалить дубликаты» сравнивает то, что отображается в ячейке, поэтому строка с лишним пробелом для команды отличается от исходной.

Выход — вспомогательный столбец с нормализованным ключом: =СТРОЧН(СЖПРОБЕЛЫ(A2)). СЖПРОБЕЛЫ убирает пробелы в начале и в конце и сжимает повторяющиеся пробелы между словами до одного, СТРОЧН переводит всё в нижний регистр. Искать и удалять дубли дальше нужно по этому столбцу. СЖПРОБЕЛЫ не трогает неразрывный пробел (код 160), который приезжает с веб-страниц; как его убрать — в разборе ВПР в Excel.

Разные написания — «ООО Ромашка», «Ромашка ООО», «ромашка» — формулой не лечатся. Для них ведут справочник соответствий: исходное написание → каноническое, и подтягивают каноническое имя ВПР или ПРОСМОТРX. Регистр в ключах вроде промокодов, наоборот, бывает значимым, тогда переводить его в нижний нельзя.

Как сравнить два столбца в Excel на совпадения

Под «сравнить два столбца» понимают две разные задачи. Первая — построчно: совпадает ли значение в A2 со значением в B2. Формула =A2=B2 или, с учётом регистра, =СОВПАД(A2;B2). Вторая, и в работе она встречается чаще, — как списки: есть ли значение из A где-нибудь в столбце B, в любой строке.

Лист «Списки»: в A2:A7 шесть клиентов, получивших промокод, в B2:B6 пять оплативших. Построчное сравнение даёт ЛОЖЬ во всех пяти строках, хотя три человека есть в обоих списках: просто они стоят в разных строках. Для списков нужна СЧЁТЕСЛИ: =ЕСЛИ(СЧЁТЕСЛИ($B$2:$B$6;A2)>0;"оплатил";"нет"). Другой вариант — =ЕНД(ПОИСКПОЗ(A2;$B$2:$B$6;0)): ИСТИНА значит «в столбце B не найден». Ноль в ПОИСКПОЗ обязателен, без него поиск приблизительный.

Подсветить отсутствующих можно правилом с формулой =СЧЁТЕСЛИ($B$2:$B$6;$A2)=0 на диапазоне A2:A7. Итог одной ячейкой: =СУММПРОИЗВ(--(СЧЁТЕСЛИ(B2:B6;A2:A7)>0)) вернёт 3 совпадения. В Microsoft 365 и Excel 2021 сам список несовпавших выведет =ФИЛЬТР(A2:A7;СЧЁТЕСЛИ(B2:B6;A2:A7)=0). Не забудьте сравнить и в обратную сторону: двое оплатили без промокода, и из столбца A их не видно.

Как сравнить две таблицы в Excel и найти разницу

Конец месяца: сумма заказов в CRM — 14 950 ₽, у бухгалтерии — 14 600 ₽. Нужно найти, из чего складываются 350 ₽. Две таблицы сравнивают по ключу: для каждого заказа из CRM подтягивают сумму из бухгалтерии и сравнивают. Перед этим проверьте, что ключ в обеих таблицах уникален, той же СЧЁТЕСЛИ из раздела выше: с дублем ключа ВПР молча возьмёт первую строку.

На листе «CRM» в C2: =ЕСНД(ВПР(A2;Бухгалтерия!$A$2:$B$7;2;ЛОЖЬ);"нет в бухгалтерии"), в D2: =ЕСЛИ(ЕЧИСЛО(C2);B2-C2;""). В Excel 2021 и Microsoft 365 короче: =ПРОСМОТРX(A2;Бухгалтерия!$A$2:$A$7;Бухгалтерия!$B$2:$B$7;"нет в бухгалтерии"). Затем то же в обратную сторону: на листе «Бухгалтерия» =ЕСЛИ(СЧЁТЕСЛИ(CRM!$A$2:$A$7;A2)=0;"нет в CRM";""). Сравнение в одну сторону теряет строки, которые есть только во второй таблице.

Разница раскладывается полностью: заказ 5104 на 950 ₽ есть только в CRM, по 5103 суммы расходятся на 100 ₽, а заказ 5109 на 700 ₽ есть только в бухгалтерии. 950 + 100 − 700 = 350. Когда расхождение объяснено до рубля, сверка закончена. Если такую сверку вы делаете каждый месяц, перенесите её в Power Query: «Объединить запросы» с типом соединения «левое анти» оставит строки первой таблицы, у которых нет пары во второй.

Ловушка аналитика: какие числа меняются после удаления дублей

Вернёмся к выручке по каналам. Если посчитать её по сырой выгрузке, копии 5102 и 5104 добавят 2 750 ₽. Но сумма — не единственное, что сдвинется. Доли каналов тоже: копии пришлись на organic и email, поэтому доля email в сырых данных 9,3%, а в чистых — 5,4%. Решение «email приносит почти десятую часть выручки» оказалось бы построено на повторной выгрузке.

Обратная ошибка — удалить дубли по неверному ключу. Удаление по клиенту оставляет одну строку на человека, и выручка падает до 11 250 ₽: исчезли три честные покупки на 6 500 ₽. Число клиентов при этом не меняется ни в одном варианте — пять, — и именно поэтому ошибку легко не заметить. Средний чек тоже выглядит правдоподобно: 2 050, 2 218,75 и 2 250 ₽. Выдаёт ошибку показатель «заказов на клиента»: 2,0 в сырых данных, 1,6 в чистых и ровно 1,0 после удаления по клиенту.

Отсюда порядок работы. Определите ключ. Посчитайте строки и сумму до удаления. Удалите копии по ключу и посчитайте снова: разница должна совпасть с суммой строк, которые флаг нарастающего СЧЁТЕСЛИ пометил как копии. Только потом стройте сводную таблицу.

То же в SQL: GROUP BY и HAVING COUNT(*) > 1

В базе данных поиск дублей — один запрос: сгруппировать строки по ключу и оставить группы, где строк больше одной. GROUP BY здесь делает то же, что столбец СЧЁТЕСЛИ, а HAVING COUNT(*) > 1 — то же, что фильтр по ИСТИНА. Проверим на учебной базе «Маяк», на которой работает SQL-песочница. В таблице payments 1 251 платёж на 30 639, дублей там нет, поэтому повторную выгрузку собираем в запросе: платежи за 16 августа, 20 строк на 460, добавлены второй раз. Это учебная вставка.

Запрос вернёт 20 строк — все платежи 16 августа, у каждого copies = 2. Порядок «найти, посмотреть, потом удалять» тот же, что в Excel.

WITH loaded AS (
  SELECT payment_id, user_id, paid_at, amount FROM payments
  UNION ALL
  -- учебная вставка: платежи за 16 августа выгружены повторно
  SELECT payment_id, user_id, paid_at, amount FROM payments
  WHERE paid_at = DATE '2026-08-16'
)
SELECT payment_id, user_id, paid_at, amount, COUNT(*) AS copies
FROM loaded
GROUP BY payment_id, user_id, paid_at, amount
HAVING COUNT(*) > 1
ORDER BY payment_id;

Удаление по неверному ключу на всей базе

Теперь повторим ловушку на 1 271 строке. Удаление по payment_id возвращает исходные 1 251 платёж и 30 639. Удаление по user_id, аналог кнопки с отмеченным столбцом клиента, оставляет первый платёж каждого плательщика: 912 строк и 22 328. Ушли все 339 продлений подписки на 8 311 — 27% выручки. Средний платёж при этом почти не изменился (24,47, 24,49 и 24,48), и по нему ошибку не увидеть. Видно её по числу платежей на плательщика: 1,00.

Почему «повторяющийся user_id» не означает дубль, показывает третий запрос: в чистой таблице 290 плательщиков, у которых по два-три платежа, — 629 строк. Это продления, а не копии. Подробнее о группах и фильтре по ним — в статьях про GROUP BY и COUNT и HAVING, о дублях событий без явного ключа — в разборе дубликатов в SQL.

WITH loaded AS (
  SELECT payment_id, user_id, paid_at, amount FROM payments
  UNION ALL
  -- учебная вставка: платежи за 16 августа выгружены повторно
  SELECT payment_id, user_id, paid_at, amount FROM payments
  WHERE paid_at = DATE '2026-08-16'
),
by_payment AS (
  SELECT DISTINCT payment_id, user_id, paid_at, amount FROM loaded
),
by_user AS (
  SELECT payment_id, user_id, paid_at, amount
  FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY payment_id) AS rn
    FROM loaded
  ) AS ranked
  WHERE rn = 1
)
SELECT 'выгрузка как есть' AS variant, COUNT(*) AS rows_cnt, SUM(amount) AS revenue,
  COUNT(DISTINCT user_id) AS payers,
  ROUND(CAST(COUNT(*) AS numeric(18, 6)) / COUNT(DISTINCT user_id), 2) AS payments_per_payer
FROM loaded
UNION ALL
SELECT 'дубли по payment_id', COUNT(*), SUM(amount), COUNT(DISTINCT user_id),
  ROUND(CAST(COUNT(*) AS numeric(18, 6)) / COUNT(DISTINCT user_id), 2)
FROM by_payment
UNION ALL
SELECT 'дубли по user_id', COUNT(*), SUM(amount), COUNT(DISTINCT user_id),
  ROUND(CAST(COUNT(*) AS numeric(18, 6)) / COUNT(DISTINCT user_id), 2)
FROM by_user
ORDER BY rows_cnt DESC;

Повторяющиеся значения — ещё не дубли

Запрос считает плательщиков, чей user_id встречается в payments больше одного раза. Ответ — 290 человек и 629 платежей. Правило условного форматирования подсветило бы все 629 ячеек, а удалять из них нечего.

SELECT COUNT(*) AS users, SUM(payments_cnt) AS payments
FROM (
  SELECT user_id, COUNT(*) AS payments_cnt
  FROM payments
  GROUP BY user_id
  HAVING COUNT(*) > 1
) AS repeat_payers;

Частые ошибки

Почти все ошибки с дублями сводятся к одному: удаление сделано раньше, чем понятно, что считать одинаковой строкой.

• Удалять по столбцу, который не является ключом: клиент, дата, канал. Вместе с копиями уходят честные повторные события.
• Удалять в исходной выгрузке без копии. Отмена работает, пока файл открыт; после сохранения строки не вернуть.
• Считать, что Excel выберет правильную строку. Остаётся первая сверху, а не самая свежая. Если у заказа две версии со статусами «создан» и «отменён», отсортируйте по времени изменения перед удалением.
• Искать дубли по сырому тексту. Пробел в конце и заглавная буква делают одного клиента двумя; нормализуйте ключ СЖПРОБЕЛЫ и СТРОЧН.
• Сравнивать списки построчно =A2=B2, когда порядок строк в них разный.
• Сверять две таблицы в одну сторону и не видеть строк, которые есть только во второй.
• Не сверять число строк и сумму до и после чистки.

Частые вопросы

Итог

Дубли в Excel находят за минуту — условным форматированием или столбцом с СЧЁТЕСЛИ. Сложнее решить, что считать дублем: одинаковое значение в столбце ещё не значит одинаковую строку. Сначала определите ключ и нормализуйте его, посчитайте строки и сумму, удалите копии по ключу и сверьте разницу с помеченными строками. Для сравнения списков и таблиц нужен поиск по ключу в обе стороны, а не построчное равенство. Когда та же чистка нужна каждую неделю, её удобнее записать в Power Query или одним запросом с GROUP BY и HAVING.