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