Как удалить дубликаты в Excel: команда, формулы, Power Query

By Mirko Peters · 2026-11-12

Как удалить дубликаты в Excel: используйте Данные, Удалить дубликаты или сохраните исходные данные с помощью UNIQUE, формул и Power Query. Шаги, ограничения и типичные ошибки.

Automatically translated with AI from the original post. Read the English original

Чтобы удалить дубликаты в Excel, выделите данные, перейдите на вкладку Данные > Удалить дубликаты, отметьте столбцы, по которым определяется дубликат, и нажмите ОК. Excel удалит каждую строку, которая повторяет значения в этих столбцах, и оставит первую. Эта статья для тех, кто очищает списки, выгрузки и отчёты и хочет выбрать подходящий способ. Сначала вы найдёте команду, затем способы проверить дубликаты перед удалением, затем варианты, которые сохраняют исходные данные, и, наконец, несколько вариантов автоматизации для повторяющихся задач.

Ключевые выводы

• Выделите данные, откройте вкладку «Данные», затем «Удалить дубликаты», отметьте столбцы, определяющие дубликат, и нажмите «ОК». Excel удалит всю строку и оставит первое совпадение.
• Удаление необратимо, поэтому сначала скопируйте данные на другой лист. Ctrl+Z работает только до тех пор, пока вы не выполните другое действие.
• Excel сравнивает то, что отображается в ячейке, поэтому 3/8/2006 и 8 мар 2006 будут считаться разными значениями. Сначала приведите форматы в порядок.
• Если хотите просмотреть дубликаты перед удалением, выделите их с помощью условного форматирования или отметьте вспомогательным столбцом с функцией COUNTIF.
• UNIQUE и Power Query дают чистый список без изменения исходных данных, а Power Query повторяет очистку при каждом обновлении.

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

Удаление дубликатов командой на вкладке «Данные»

Это самый быстрый способ, и он нужен большинству людей. Он соответствует статье Microsoft о фильтрации уникальных значений и удалении повторяющихся значений.

1. Сделайте резервную копию данных. Скопируйте диапазон на другой лист или в другую книгу. Microsoft прямо предупреждает: повторяющиеся значения удаляются безвозвратно.
2. Выделите диапазон. Щёлкните любую ячейку данных, любую ячейку таблицы Excel или выделите диапазон вручную. Изменяется только выделенный диапазон или таблица. Ячейки за его пределами остаются на месте.
3. Откройте команду. Перейдите на вкладку Данные и выберите Удалить дубликаты в группе «Работа с данными».
4. Выберите столбцы. В диалоговом окне отметьте столбцы, которые Excel должен сравнивать. Можно использовать Выделить все или Снять выделение. Если в первой строке находятся названия столбцов, оставьте отмеченным пункт Мои данные содержат заголовки.
5. Нажмите «ОК». Excel покажет сообщение с количеством удалённых повторяющихся значений и количеством оставшихся уникальных значений.

Обратите внимание на шаг 4, на нём часто ошибаются. Строка считается дубликатом, только если совпадают все столбцы, которые вы отметили. Возьмём список с ID, именем и ценой. Если вы отметили ID и имя, но не цену, две строки с одинаковыми ID и именем считаются дубликатами, даже если их цены различаются, и Excel удаляет всю вторую строку вместе с ценой. Если вы хотите сохранить каждую строку, где что-то отличается, отметьте все столбцы. Если запись однозначно определяется одним ID, отметьте только ID и учитывайте, что именно вы при этом удаляете.

Если результат выглядит неправильно, сразу после выполнения команды нажмите Ctrl+Z. Не полагайтесь на отмену позже.

Какая строка останется: сначала сортировка

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

Что считается одинаковым

Excel сравнивает то, что отображается в ячейке, а не то, что хранится. В примере Microsoft значения 3/8/2006 и 8 мар 2006 считаются разными, хотя дата одна и та же. То же относится к числам с разным форматом и к тексту с пробелом в конце. В статье о поиске и удалении дубликатов также отмечено, что количество, которое Excel показывает после выполнения, может включать пустые ячейки или пробелы.

Перед удалением приведите ячейки к единому виду: примените один формат дат, удалите лишние пробелы (это делает функция СЖПРОБЕЛЫ) и проверьте, что числа не сохранены как текст. На страницах Microsoft не сказано, учитывает ли команда регистр букв, поэтому если регистр важен, проверьте это на небольшом диапазоне.

Когда команда не работает

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

Просмотрите дубликаты перед удалением

Microsoft рекомендует сначала отфильтровать или выделить дубликаты, а потом удалять. Это займёт минуту и избавит от большинства сожалений.

Выделение дубликатов условным форматированием

1. Выделите ячейки, которые хотите проверить.
2. На вкладке Главная откройте Условное форматирование, затем Правила выделения ячеек и выберите Повторяющиеся значения.
3. Выберите стиль выделения и нажмите ОК.

Теперь все повторяющиеся значения окрашены. Это нельзя применить к области значений сводной таблицы. Этот способ проверяет один столбец за раз, поэтому строка, которая повторяется только в нескольких столбцах, не будет выделена. Для такой проверки понадобится вспомогательный столбец.

Пометка повторяющихся строк вспомогательным столбцом

Вспомогательный столбец выводит ИСТИНА или ЛОЖЬ рядом с каждой строкой. С ним можно фильтровать, проверять и затем удалять.

1. Вставьте столбец рядом с данными и добавьте заголовок, например «Дубликат?».
2. Если проверяете столбец A, введите в B2: =СЧЁТЕСЛИ(A$2:A2;A2)>1
3. Протяните формулу вниз. При первом появлении значения будет ЛОЖЬ, при каждом последующем — ИСТИНА.
4. Отфильтруйте вспомогательный столбец по ИСТИНА. Просмотрите эти строки и удалите их, если это действительно дубликаты.

Поскольку диапазон A$2:A2 расширяется при копировании формулы вниз, помечаются только последующие вхождения, а первое остаётся без изменений. Это соответствует работе команды «Удалить дубликаты».

Чтобы проверить сразу несколько столбцов, создайте столбец-ключ, который объединяет их: =A2&"|"&B2&"|"&C2. Затем выполните СЧЁТЕСЛИ по этому ключу. Между полями ставьте разделитель, например вертикальную черту. Без него «12» + «34» и «1» + «234» дадут одну и ту же строку «1234» и будут выглядеть как одна запись.

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

Получение списка без дубликатов с сохранением исходных данных

Иногда ничего удалять не нужно. Нужен только чистый список для выпадающего списка, сводки или импорта. Здесь подходят три варианта.

Функция UNIQUE

В Excel для Microsoft 365, Excel 2024 и Excel 2021 формула =UNIQUE(A2:A100) возвращает все уникальные значения из диапазона, а результат выводится в ячейки ниже. Microsoft указывает, что функция доступна и в Excel для Интернета, и в мобильных приложениях. Синтаксис: =UNIQUE(array,[by_col],[exactly_once]):

• array — диапазон для проверки. Если он содержит несколько столбцов, UNIQUE сравнивает целые строки.
• by_col — TRUE, если нужно сравнивать столбцы, а не строки. Если аргумент опущен, выполняется обычное сравнение строк.
• exactly_once — TRUE, если нужны только те строки, которые встречаются в данных ровно один раз. Если аргумент опущен, вы получите все уникальные строки.

Результат обновляется при изменении исходных данных. Если источник — таблица Excel и вы используете структурированные ссылки, диапазон вывода меняет размер при добавлении или удалении строк. Для отсортированного списка оберните формулу: =SORT(UNIQUE(A2:A100)).

Пара аргументов важнее, чем многие думают. =UNIQUE(A2:A100,,TRUE) возвращает значения, которые встречаются ровно один раз, и это совсем другой вопрос, чем «удалить лишние копии». Сначала определите, что именно вам нужно.

Есть одно ограничение: в более старых версиях Excel (2019 и ранее) функции UNIQUE нет. На странице Microsoft указаны только версии, упомянутые выше.

Расширенный фильтр

Этот способ работает во всех настольных версиях и копирует результат в другое место.

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

Исходные данные останутся на месте, а уникальные строки появятся в новом месте.

Старый подход с формулой

Если UNIQUE недоступна, введите в новом столбце =IF(COUNTIF($A$2:A2,A2)=1,A2,"") и протяните формулу вниз. Каждое значение появится один раз, а повторы превратятся в пустые строки. Скопируйте столбец и вставьте его как значения, затем отфильтруйте пустые ячейки. Это неуклюже, но работает везде.

Частичные совпадения и разный регистр

Реальные данные редко бывают чистыми копиями. Снова и снова встречаются три ситуации.

Полные дубликаты. Совпадают все отмеченные столбцы. Команда справляется с этим без проблем.

Частичные совпадения. Ключевой столбец повторяется, но другие поля различаются, например один и тот же идентификатор клиента с двумя разными адресами. Здесь нужно решить, какая версия верна. Отсортируйте данные так, чтобы правильная запись оказалась первой, а затем удалите дубликаты только по ключевому столбцу. Или отметьте повторы вспомогательным столбцом и проверьте их вручную. Ни один инструмент не скажет, какой адрес правильный.

Различия в регистре. Согласно документации Microsoft, Power Query учитывает регистр, поэтому «Apple» и «apple» там не считаются дубликатами. Предлагаемый обходной путь — перевести столбец в верхний или нижний регистр с помощью преобразования перед удалением дубликатов. Это изменит сохраненные значения, поэтому сохраните исходный столбец, если вам по-прежнему нужно точное написание.

Автоматизация: Power Query и VBA

Для разовой очистки достаточно команды. Для файла, который вы очищаете каждую неделю, настройте повторяемый процесс.

Power Query

Если данные поступают из CSV-файла, базы данных или другой книги, удаляйте дубликаты во время импорта. Power Query записывает этот шаг и выполняет его заново при каждом обновлении.

1. Загрузите данные в редактор Power Query. Если они уже находятся на листе, выделите ячейку в данных и выберите Запрос > Изменить или используйте Данные > Получить данные, чтобы загрузить новый источник.
2. Щелкните заголовок столбца, чтобы выбрать его. Удерживайте Shift или Ctrl, чтобы выбрать несколько столбцов. Столбцы не обязательно должны идти подряд.
3. Перейдите на вкладку Главная > Удалить строки > Удалить дубликаты.
4. Закройте редактор и загрузите результат.

Страница Microsoft о сохранении и удалении повторяющихся строк в Power Query описывает эти шаги. Стоит учесть её рекомендации по дубликатам: то, какие столбцы выбраны, определяет, что считается дубликатом, а Power Query не гарантирует, что в наборе останется первая строка. Если важно, какая строка останется, не полагайтесь только на сортировку. Microsoft ссылается на раздел «Preserve sort» в статье о распространённых проблемах Power Query. Связанный параметр, Keep Duplicates, делает обратное: оставляет только строки, значения которых встречаются более одного раза, что удобно для быстрой проверки списка.

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

Макрос VBA

Если вы регулярно повторяете одну и ту же очистку в книгах, которые открываете каждый день, можно использовать макрос. У объекта Range есть метод RemoveDuplicates, описанный в справочнике Microsoft по VBA. Он принимает столбцы для сравнения и указывает, содержит ли первая строка заголовки. Пример от самой Microsoft:

ActiveSheet.Range("A1:C100").RemoveDuplicates Columns:=Array(1,2), Header:=xlYes

Эта строка сравнивает первые два столбца диапазона A1:C100 и считает первую строку заголовком. Чтобы её использовать, нажмите Alt+F11, добавьте модуль, вставьте строку внутрь Sub и сохраните файл как книгу с поддержкой макросов (.xlsm). Сначала запустите макрос на копии. Макрос удаляет данные так же безвозвратно, как и команда, и Ctrl+Z не вернёт строки. Ваш ИТ-отдел может также блокировать макросы, поэтому проверьте это, прежде чем создавать что-то, что не смогут запустить ваши коллеги. Если хотите узнать больше о языке, на странице VBA собраны связанные материалы.

Excel для веба и мобильных устройств

Статьи Microsoft об удалении дубликатов описывают только настольные версии Excel (Microsoft 365, 2024, 2021, 2019 и 2016) и ничего не говорят о веб-версии и мобильных приложениях. Microsoft не описывает команду «Удалить дубликаты» для этих версий, поэтому не стройте на ней процесс. Для веб-версии и мобильных устройств Microsoft документирует функцию UNIQUE, поэтому используйте её, чтобы получить очищенный список на этих устройствах, а безвозвратное удаление выполняйте на настольном компьютере.

Распространённые ошибки

• Запуск команды без резервной копии. Закройте файл, и строки будут удалены навсегда.
• Отметка не тех столбцов. Если пропустить столбец, меняется то, какие строки считаются дубликатами, и удаляется вся строка, а не только повторяющаяся ячейка.
• Забытая сортировка перед удалением: потом непонятно, почему сохранилась старая запись, а не новая.
• Сравнение ячеек, которые выглядят одинаково, но отформатированы по-разному, например даты, записанные двумя способами, или текст с пробелами в конце.
• Попытка выполнить команду над данными с группировкой или промежуточными итогами. Сначала удалите их.
• Использование UNIQUE с exactly_once, равным TRUE, как способа удалить лишние записи. Функция возвращает только значения, которые встречаются ровно один раз, поэтому повторяющиеся значения полностью исчезают из результата.

Связанные темы для изучения

• Выделяйте дубликаты условным форматированием и помечайте повторяющиеся строки вспомогательным столбцом с COUNTIF, прежде чем что-либо удалять. Оба способа описаны выше.
• Очищайте данные при импорте. Шаг Power Query не даёт дубликатам попадать в каждое обновление и хорошо подходит для CSV-файлов и данных из других систем.
• Предотвращайте дубликаты при вводе. Проверку данных с пользовательской формулой можно настроить так, чтобы пользователи не могли ввести значение, которое уже есть. Сначала проверьте это на небольшом листе.
• Автоматизируйте повторяющиеся задачи с помощью макроса и научитесь безопасно запускать его на копии.
• Объединение данных из нескольких источников, где часто и возникают дубликаты. Заранее спланируйте порядок очистки и объединения.

Следующий шаг

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