Приведенные выше таблицы содержат одни и те же данные. Разница в том, что в левом примере мы искали дублирующиеся ячейки, а в правом примере мы нашли целые повторяющиеся строки данных.
Как убрать повторяющиеся значения в Excel?
В этом уроке рассказывается, как удалить дубликаты значений в Excel. Вы узнаете множество различных методов поиска и удаления дубликатов, избавления от дублирующихся строк, поиска точных дубликатов и некоторых совпадений.
Хотя Microsoft Excel — это в первую очередь инструмент для работы с электронными таблицами, его электронные таблицы часто используются как базы данных для отслеживания инвентаризации, создания отчетов о продажах или ведения списков рассылки.
Частой проблемой, возникающей при увеличении размера базы данных, является наличие большого количества дубликатов. И даже если ваш огромный файл содержит всего несколько одинаковых записей, эти несколько повторений могут вызвать массу проблем. Например, если вы отправляете несколько копий одного и того же документа одному и тому же человеку или отображаете одни и те же данные в отчете более одного раза, вы, скорее всего, не будете удовлетворены.
Поэтому перед использованием базы данных имеет смысл проверить ее на наличие дубликатов, чтобы потом не тратить время на исправление ошибок.
В нескольких наших недавних статьях мы обсуждали различные способы обнаружения дубликатов в Excel и выделения ячеек или строк, которые не являются уникальными (см. ссылки в конце статьи). Однако могут возникнуть ситуации, когда вы захотите устранить дубликаты в своих электронных таблицах. И именно этому посвящено данное руководство.
Удаление повторяющихся строк вручную
Если вы используете последнюю версию Microsoft Excel от 2007 до 2019 года, у вас есть небольшое преимущество. Эти версии включают встроенную функцию поиска и удаления дубликатов значений.
С помощью этого инструмента можно находить и удалять как абсолютные совпадения (ячейки или целые строки), так и перекрывающиеся записи (с одинаковыми значениями в столбце или диапазоне).
Это важно: поскольку инструмент «Удаление дубликатов» навсегда удаляет идентичные записи, рекомендуется сделать копию исходных данных перед их удалением.
Для этого выполните следующие действия.
- Сначала выберите диапазон, с которым вы хотите работать. Чтобы выделить весь диапазон, нажмите Ctrl + A ,
- Указав диапазон, перейдите на вкладку «Данные» > и нажмите кнопку «Удалить копии».
- Откроется диалоговое окно. Выберите столбцы для проверки на наличие дублей и нажмите кнопку « ОК».
- Чтобы удалить дубликаты строк, которые содержат абсолютно одинаковые данные во всех столбцах, оставьте флажки напротив всех столбцов установленными, как показано на следующем рисунке.
- Чтобы удалить некоторые совпадения, основанные на одном или нескольких ключевых столбцах, выберите только их. Если в вашей таблице много столбцов, самый быстрый способ — нажать кнопку Снять выделение. Затем выберите те, которые вы хотите проверить.
- Если ваша таблица не имеет заголовков, снимите флажок Мои данные в правом верхнем углу диалогового окна, который обычно установлен по умолчанию.
- Если в диалоговом окне указать все столбцы, строка будет удалена только в том случае, если во всех столбцах есть дубликаты значений. Однако в некоторых случаях нет необходимости включать данные в некоторые столбцы. Поэтому снимите соответствующие флажки. Например, если каждая строка содержит уникальный идентификатор, программа не найдет дубликатов. Поэтому снимите флажок напротив столбца с этими кодами.
Готово! Все дублирующиеся строки в нашем диапазоне будут удалены, и будет выведено сообщение, указывающее, сколько дублирующихся записей было удалено и сколько уникальных записей осталось.
Важное замечание. Повторяющиеся значения основаны на том, что отображается в ячейке, а не на том, что на самом деле написано в ячейке. Представьте, что A1 и A2 содержат одну и ту же дату. Один из них отображается как 15.05.2020, а другой — как 15 мая 2020 года. При поиске повторяющихся значений Excel предполагает, что они не идентичны. Аналогично, значения, имеющие разное форматирование, считаются разными. Таким образом, 400 209.32 — это не то же самое, что 1209.32.
Поэтому рекомендуется применять формат ко всему столбцу для обеспечения успешного поиска и удаления дубликатов в таблице или диапазоне данных. Примечания. Функция «Удалить дубликаты» удаляет второе и все последующие совпадения, оставляя все уникальные и первые экземпляры идентичных записей.
Удаление дубликатов в «умной таблице».
Думаю, вы знаете, что когда мы преобразуем строку ячеек в таблицу, у нас появляется множество интересных дополнительных возможностей для обработки этих данных. По этой причине такая таблица Excel называется «умной» таблицей.
Выберите нужный диапазон, а затем на вкладке Главная выберите Формат как таблица. Затем вам будет предложено указать желаемый макет. После завершения работы автоматически откроется вкладка Designer.
Выберите нужную кнопку на ленте, как показано на скриншоте. Затем выберите столбцы, в которых вы хотите искать повторения. Происходит то же самое, что описано в предыдущем разделе.
Однако, в отличие от описанного выше инструмента удаления, эту функцию можно отменить, если что-то пошло не так.
Старая таблица данных остается. И результат использования такого специального фильтра будет рядом (включая новый документ или другой ранее созданный лист). В новой таблице все одинаковые строки будут удалены.
Поиск и удаление
Найти и удалить дубликаты значений таблицы можно несколькими способами. В каждом из этих вариантов поиск и удаление дубликатов связаны с одним и тем же процессом.
Самый простой способ удаления дубликатов — использовать выделенную кнопку на ленте.
- Выберите всю ширину доски. Перейдите на вкладку «Данные». Нажмите на кнопку «Удалить копии». Он находится на ленте в панели инструментов «Работа с данными».
- Откроется окно Удаление копий. Если у вас есть таблица со строкой заголовка (что характерно для большинства таблиц), то рядом с параметром «Мои данные содержат строки заголовка» должна стоять галочка. В основном поле окна находится список колонок, которые необходимо проверить. Строка считается дубликатом только в том случае, если данные всех столбцов, отмеченных флажком, совпадают. Это означает, что если вы снимите флажок в любом столбце, вероятность того, что запись будет считаться дубликатом, увеличится. После выполнения всех необходимых настроек нажмите на кнопку «OK».
- Excel выполнит поиск и удаление дубликатов. По завершении процесса отображается информационное окно, в котором указывается, сколько дублирующих значений было удалено и сколько уникальных записей осталось. Чтобы закрыть окно, нажмите кнопку OK.
Способ 2: удаление дубликатов в «умной таблице»
Дубликаты можно удалить из диапазона ячеек, создав интеллектуальную таблицу.
- Выберите весь диапазон таблицы.
- На вкладке Главная нажмите кнопку Формат как таблица, расположенную на ленте панели инструментов Стиль. Выберите нужный стиль из появившегося списка.
- Затем откроется небольшое окно, в котором нужно подтвердить выбранную область для формирования «умной доски». Если вы выбрали правильно, вы можете подтвердить, если вы допустили ошибку, вы должны исправить ее в этом окне. Также важно убедиться, что рядом с параметром «Таблица с заголовками» стоит галочка. Если его нет, то следует поставить галочку. Когда вы выполните все настройки, нажмите «OK». Интеллектуальная таблица теперь создана.
Этот метод является наиболее универсальным и функциональным из всех методов, описанных в данной статье.
Способ 3: применение сортировки
Этот метод фактически не удаляет дубликаты, поскольку сортировка только скрывает дублирующиеся записи в таблице.
- Выберите таблицу. Перейдите на вкладку «Данные». Нажмите на кнопку «Фильтр». которая находится в блоке конфигурации «Сортировка и фильтр».
- Фильтр активирован, что видно по символам в виде перевернутых треугольников в названиях столбцов. Теперь нам нужно настроить его. Нажмите на кнопку «Дополнительно». В той же группе инструментов рядом находится «Сортировка и фильтр».
- Откроется окно Дополнительные фильтры. Мы ставим галочку перед параметром «Только уникальные записи». Все остальные настройки остаются по умолчанию. Затем нажмите кнопку OK.
После этого дублирующиеся записи скрываются. Однако вы можете отобразить их снова в любое время, снова нажав кнопку «Фильтр».
Как уже было сказано, этот метод сложен и функционально ограничен, поскольку не позволяет удалять найденные столбцы. При прочих равных условиях рекомендуется использовать один из ранее описанных методов, которые логически проще для понимания и зачастую более эффективны.
Как убрать дубли в Excel через функцию «Удалить дубликаты»
Этот метод еще проще. Давайте поступим следующим образом:
Шаг 1: Перейдите в раздел «Данные» главного меню и нажмите на «Удалить копии» (в блоке «Работа с данными»).
Шаг 2: Появится следующее окно:
В этом окне необходимо сначала отметить пункт «Мои данные содержат заголовки» (конечно, только если столбцы имеют заголовки), а затем выбрать параметры (будьте внимательны!), которые при одновременном сочетании удалят строку из списка.
Вот пример. Мы хотим сохранить в таблице только один вариант женского пола (параметр «Пол») и роста 159 см (параметр «Рост»). Поэтому мы выбираем в окне столбцы «Пол» и «Рост». Нажимаем кнопку «ОК».
И вот результат:
Из девушек ростом 159 см осталась только первая Света. Катя и второй, такого же роста, были исключены из таблицы. Сама таблица была сокращена до 8 строк.
Заключение
Оба метода имеют свои преимущества и недостатки при удалении повторяющихся значений в столбце Excel:
- Расширенный фильтр» можно использовать для сохранения исходного образца, например, для дальнейших сравнений. Однако этому методу явно не хватает гибкости.
- Метод удаления дубликатов с помощью кнопки «Удалить дубликаты» является более гибким — например, можно исключить строки, которые не соответствуют всем параметрам. При использовании этого метода сохранить исходное выделение можно только путем его копирования на другой лист или область.
Вот что я советую тебе сделать, мой друг. Создайте чистый лист и сохраните таблицы в том формате, в котором они были, пока не доберетесь до них. Тогда вам не придется беспокоиться о том, что ваша ошибка приведет к потере важной информации.
Какой из способов удаления повторяющихся значений в Excel вы предпочитаете? Поделитесь своими предпочтениями в комментариях!
