Excel – универсальное приложение, которое значительно превзошло свои ранние версии и превратилось в простое решение для работы с электронными таблицами. Работая в качестве регистратора, адресной книги, инструмента прогнозирования и т. д., многие люди даже используют Excel так, как он никогда не предполагался.
Если вы часто используете Excel дома или в офисе, вы знаете, что иногда файлы Excel могут быстро стать громоздкими из-за огромного количества записей, с которыми вы работаете.
К счастью, в Excel есть встроенные функции, которые помогут вам найти и удалить повторяющиеся записи. К сожалению, при использовании этих функций есть несколько предостережений, поэтому будьте осторожны, иначе вы можете по незнанию удалить записи, которые не собирались удалять. Кроме того, оба приведенных ниже метода мгновенно удаляют дубликаты, не позволяя вам увидеть, что было удалено.
Я также упомяну способ сначала выделить повторяющиеся строки, чтобы вы могли видеть, какие из них будут удалены функциями, прежде чем запускать их. Вам необходимо использовать собственное правило условного форматирования, чтобы выделить строку, которая полностью повторяется.
Функция удаления дубликатов
Предположим, вы используете Excel для отслеживания адресов и подозреваете, что у вас есть повторяющиеся записи. Посмотрите на пример листа Excel ниже:
Обратите внимание, что запись «Джонс» появляется дважды. Чтобы удалить такие повторяющиеся записи, нажмите вкладку Данные на ленте и найдите функцию Удалить дубликаты в разделе Инструменты работы с данными . Нажмите Удалить дубликаты , откроется новое окно.
Здесь вам нужно принять решение на основе того, используете ли вы метки заголовков вверху столбцов. Если да, выберите параметр Мои данные имеют заголовки . Если вы не используете метки заголовков, вы будете использовать стандартные обозначения столбцов Excel, такие как столбец A, столбец B и т. д.
В этом примере мы выберем только столбец A и нажмем кнопку ОК . Окно параметров закроется, и Excel удалит вторую запись «Джонс».
Конечно, это был всего лишь простой пример. Любые записи адресов, которые вы продолжаете использовать в Excel, вероятно, будут намного сложнее. Предположим, например, что у вас есть адресный файл, который выглядит следующим образом.
Обратите внимание: хотя существует три записи «Джонс», только две из них идентичны. Если бы мы использовали описанные выше процедуры для удаления повторяющихся записей, осталась бы только одна запись «Джонс». В этом случае нам необходимо расширить наши критерии принятия решения, включив в них имена и фамилии, найденные в столбцах A и B соответственно..
Для этого еще раз нажмите вкладку Данные на ленте, а затем нажмите Удалить дубликаты . На этот раз, когда появится всплывающее окно с параметрами, выберите столбцы A и B. Нажмите кнопку ОК и обратите внимание, что на этот раз Excel удалил только одну из записей «Мэри Джонс».
Это потому, что мы сказали Excel удалять дубликаты путем сопоставления записей на основе столбцов A и B, а не только столбца A. Чем больше столбцов вы выберете, тем больше критериев должно быть выполнено, прежде чем Excel будет считать запись дубликатом. . Выберите все столбцы, если хотите удалить полностью повторяющиеся строки.
Excel выдаст вам сообщение о том, сколько дубликатов было удалено. Однако он не покажет вам, какие строки были удалены! Прокрутите вниз до последнего раздела, чтобы узнать, как сначала выделить повторяющиеся строки, прежде чем запускать эту функцию.
Расширенный метод фильтрации
Второй способ удалить дубликаты — использовать расширенный фильтр. Сначала выберите все данные на листе. Затем на вкладке «Данные» на ленте нажмите Дополнительно в разделе Сортировка и фильтр .
В появившемся диалоговом окне обязательно установите флажок Только уникальные записи .
Вы можете либо отфильтровать список на месте, либо скопировать неповторяющиеся элементы в другую часть той же таблицы. По какой-то странной причине вы не можете скопировать данные на другой лист. Если вы хотите, чтобы они были на другом листе, сначала выберите место на текущем листе, а затем вырежьте и вставьте эти данные на новый лист.
При использовании этого метода вы даже не получите сообщение о том, сколько строк было удалено. Строки удаляются и все.
Выделить повторяющиеся строки в Excel
Если вы хотите увидеть, какие записи являются повторяющимися, прежде чем удалять их, вам придется проделать небольшую работу вручную. К сожалению, в Excel нет возможности выделять полностью повторяющиеся строки. В разделе условного форматирования есть функция, которая выделяет повторяющиеся ячейки, но эта статья посвящена повторяющимся строкам.
Первое, что вам нужно сделать, это добавить формулу в столбец справа от набора данных. Формула проста: просто объедините все столбцы этой строки вместе.
= A1 & B1 & C1 & D1 & E1
В моем примере ниже у меня есть данные в столбцах от A до F. Однако первый столбец представляет собой идентификационный номер, поэтому я исключаю его из формулы ниже. Обязательно укажите все столбцы, содержащие данные, которые вы хотите проверить на наличие дубликатов..
Я поместил эту формулу в столбец H, а затем протянул ее вниз по всем строкам. Эта формула просто объединяет все данные в каждом столбце в один большой фрагмент текста. Теперь пропустите еще пару столбцов и введите следующую формулу:
=COUNTIF($H$1:$H$34, $H1) > 1
Здесь мы используем функцию СЧЕТЕСЛИ, и первый параметр — это набор данных, которые мы хотим просмотреть. В моем случае это был столбец H (который содержит формулу объединения данных) со строк 1 по 34. Также рекомендуется перед этим избавиться от строки заголовка.
Вам также обязательно нужно использовать знак доллара ($) перед буквой и цифрой. Если у вас 1000 строк данных и ваша объединенная формула строк находится, например, в столбце F, ваша формула будет выглядеть следующим образом:
=COUNTIF($F$1:$F$1000, $F1) > 1
Второй параметр имеет только знак доллара перед буквой столбца, поэтому он заблокирован, но мы не хотим блокировать номер строки. Опять же, вы перетащите это вниз для всех строк данных. Это должно выглядеть так, и в повторяющихся строках должно быть TRUE.
Теперь давайте выделим строки, в которых есть TRUE, поскольку это повторяющиеся строки. Сначала выберите весь лист данных, щелкнув маленький треугольник в левом верхнем углу пересечения строк и столбцов. Теперь перейдите на вкладку «Главная», затем нажмите Условное форматирование и нажмите Новое правило .
В диалоговом окне нажмите Использовать формулу, чтобы определить ячейки для форматирования .
В поле под Значения формата, для которых эта формула имеет значение true: введите следующую формулу, заменив P столбцом со значениями TRUE или FALSE. Обязательно добавьте знак доллара перед буквой столбца.
=$P1=TRUE
После этого нажмите «Формат» и перейдите на вкладку «Заливка». Выберите цвет, и он будет использоваться для выделения всей повторяющейся строки. Нажмите «ОК», и теперь вы увидите, что повторяющиеся строки выделены.
Если вам это не помогло, начните заново и повторите медленно. Чтобы все это работало, нужно сделать все правильно. Если вы пропустите хотя бы один символ $, он не будет работать должным образом.
Предостережения по поводу удаления повторяющихся записей
Конечно, есть несколько проблем с автоматическим удалением повторяющихся записей в Excel. Во-первых, вы должны быть осторожны и не выбирать слишком мало или слишком много столбцов, которые Excel будет использовать в качестве критериев для выявления повторяющихся записей..
Если их слишком мало, вы можете случайно удалить нужные записи. Столбец идентификатора слишком много или случайно включен, поэтому дубликаты найдены не будут.
Во-вторых, Excel всегда предполагает, что первая встречающаяся ему уникальная запись является основной записью. Любые последующие записи считаются дубликатами. Это проблема, если, например, вам не удалось изменить адрес одного из людей в вашем файле, а вместо этого создали новую запись.
Если новая (правильная) запись адреса появляется после старой (устаревшей) записи, Excel предположит, что первая (устаревшая) запись является основной, и удалит все последующие найденные записи. Вот почему вы должны быть осторожны, насколько свободно или консервативно вы позволяете Excel решать, что является повторяющейся записью, а что нет.
В таких случаях вам следует использовать метод выделения дубликатов, о котором я писал, и вручную удалить соответствующую повторяющуюся запись.
Наконец, Excel не просит вас подтвердить, действительно ли вы хотите удалить запись. Используя выбранные вами параметры (столбцы), процесс полностью автоматизирован. Это может быть опасно, если у вас огромное количество записей и вы уверены, что принятые вами решения были правильными, и позволяете Excel автоматически удалять повторяющиеся записи.
Не забудьте также прочитать нашу предыдущую статью о удаление пустых строк в Excel. Наслаждайтесь!.