Как найти и удалить дубликаты в Excel
Три способа: пометить формулой и решить самому, вырезать кнопкой «Удалить дубликаты» или просто подсветить цветом. Готовый файл с первым.
Коротко
Способов три, и выбирать между ними надо до того, как что-то нажато.
- Пометить формулой. Рядом со списком встаёт столбец, который считает повторы и пишет «первый» или «дубль». Ничего не удаляется, решение остаётся за вами: можно отфильтровать, можно свести, можно проверить руками. Так устроен файл к этой странице.
- Данные → Удалить дубликаты. Быстро и необратимо: строки исчезают сразу, без списка того, что именно ушло. Отменить можно только сочетанием Ctrl+Z и только пока файл не закрыт.
- Условное форматирование → Повторяющиеся значения. Только подсветка, без счётчиков и без удаления. Годится, чтобы глазами понять масштаб проблемы.
Основной инструмент первого способа — СЧЁТЕСЛИ. =СЧЁТЕСЛИ($B$7:$B$31;B7) отвечает, сколько раз значение встречается во всём столбце: единица — повторов нет. А та же функция с диапазоном, закреплённым только сверху, — =СЧЁТЕСЛИ($B$7:B7;B7) — считает вхождения от начала списка до текущей строки, и единица в ней значит «это первое вхождение». На этой разнице и держится пометка «первый / дубль».
В файле восемь строк на два столбца — «Клиент» (B7:B31) и «Телефон» (C7:C31). «ООО Ромашка» в них встречается трижды, но у третьей записи телефон другой. По имени клиента это дубль, по паре «имя и телефон» — отдельная запись. Поэтому в файле есть столбец «Ключ строки» =B7&"|"&C7: когда дубль определяется сочетанием колонок, повторы считают по ключу, а не по одному столбцу.
То же самое формулой в Excel
=СЧЁТЕСЛИ($B$7:$B$31;B7) — сколько раз значение встречается в столбце; 1 — повторов нет=ЕСЛИ(СЧЁТЕСЛИ($B$7:B7;B7)=1;"первый";"дубль") — первое вхождение или повтор: диапазон закреплён только сверху и растёт вниз=B7&"|"&C7 — ключ строки, когда дубль определяется сочетанием нескольких колонок=СУММПРОИЗВ((B7:B31<>"")/СЧЁТЕСЛИ(B7:B31;B7:B31&"")) — сколько в столбце уникальных значений — без вспомогательных столбцов=СЧЁТЕСЛИ(E7:E31;"дубль") — сколько строк помечено дублями — столько же уйдёт при удаленииФайл, который помечает дубли и ничего не удаляет
Лист «Дубликаты» на двадцать пять строк: вставляете клиентов и телефоны, а файл сам считает число повторов, ставит напротив каждой строки «первый» или «дубль» (дубли краснеют), собирает ключ строки из двух колонок и внизу выводит три числа: всего строк, уникальных значений и дублей к удалению. Ни одна строка не исчезает — что с ними делать, решаете вы.
Файл
udalit-dublikaty.xlsx · формулы работают в Excel 2016 и новее
и в Google Таблицах · без макросов · формулы проходят автопроверку
перед публикацией
Пошагово
- Сделайте копию листа, если собираетесь удалять. Правый клик по ярлыку листа → «Переместить или скопировать» → галочка «Создать копию». Тридцать секунд против невосстановимой выгрузки.
- Пометьте повторы формулой. В свободный столбец напротив первой строки списка:
=СЧЁТЕСЛИ($B$7:$B$31;B7). Закрепите диапазон клавишей F4 и протяните вниз. Всё, где больше единицы, встречается не один раз. - Отделите первое вхождение от повторов. Рядом:
=ЕСЛИ(СЧЁТЕСЛИ($B$7:B7;B7)=1;"первый";"дубль"). Здесь у диапазона закреплено только начало, поэтому при протягивании он растёт:$B$7:B7,$B$7:B8и так далее. - Если дубль — это сочетание колонок, соберите ключ:
=B7&"|"&C7, и считайте повторы по столбцу ключа, а не по имени. Разделитель нужен обязательно, иначе «АБ»+«В» и «А»+«БВ» склеятся в одно. - Отфильтруйте столбец с пометкой по значению «дубль» и посмотрите, что именно собирается уйти. Часто на этом шаге выясняется, что половина «дублей» — разные люди с одинаковой фамилией.
- Удаляйте, если решили: выделите таблицу, Данные → Удалить дубликаты, отметьте столбцы, по которым сравнивать. Excel сообщит, сколько строк удалено и сколько осталось. Первое вхождение всегда остаётся, удаляются последующие.
Где обычно ломается
Пять мест, где эта задача ломается чаще всего.
- Кнопка «Удалить дубликаты» не показывает, что удаляет. Она сразу вырезает строки и отчитывается только числом. Отмена — Ctrl+Z и только до закрытия файла. Поэтому сначала пометка формулой и фильтр, потом удаление.
- Отмеченные столбцы в диалоге решают всё. Отметите один «Клиент» — строка уйдёт, даже если телефон в ней другой. Отметите оба — удалятся только полностью совпадающие пары. Именно поэтому «ООО Ромашка» с другим телефоном в примере то дубль, то нет: зависит от того, что вы считаете дублем.
- Пробелы и невидимые символы. «ООО Ромашка» и «ООО Ромашка » с пробелом на конце — для Excel разные значения, и дубль остаётся незамеченным. Регистр, наоборот, не важен: «ромашка» и «Ромашка» СЧЁТЕСЛИ считает одним значением. Перед сверкой прогоните столбец через
СЖПРОБЕЛЫ, а выгрузки из веб-форм — ещё и черезПЕЧСИМВ. - Длинные номера, записанные числом. СЧЁТЕСЛИ сравнивает числа с точностью пятнадцать значащих цифр. Номера карт, ИНН из двадцати цифр и телефоны, попавшие в ячейку числом, у него совпадают по первым пятнадцати знакам — и разные записи слипаются в дубли. Такие столбцы храните текстом: в файле телефоны лежат именно текстом.
- Подстановочные знаки внутри значений. СЧЁТЕСЛИ понимает
*и?как «любые символы»: артикулы видаА-12?он посчитает неверно. Обходится черезСУММПРОИЗВ(--(диапазон=значение))— она сравнивает буквально.
«Удалить дубликаты без смещения» — это просьба не трогать строки вовсе: соседние столбцы поедут вверх и разъедутся с данными. Решение то же самое: пометить формулой и отфильтровать или скопировать уникальные строки на новый лист, но ничего не вырезать из исходного.