Computer help - file server


[ Blog ] - [ File Server ] - [ Удаленная компьютерная помощь ]

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

Если вы часто используете Excel дома или в офисе, вы знаете, что иногда файлы Excel могут быстро стать громоздкими из-за огромного количества записей, с которыми вы работаете.

К счастью, в Excel есть встроенные функции, которые помогут вам найти и удалить повторяющиеся записи.К сожалению, есть несколько предостережений при использовании этих функций, поэтому будьте осторожны, иначе вы можете неосознанно удалить записи, которые не собирались удалять. Кроме того, оба приведенных ниже метода мгновенно удаляют дубликаты, не позволяя увидеть, что было удалено.

Я также упомяну способ выделения в первую очередь повторяющихся строк, чтобы вы могли видеть, какие из них будут удалены функциями, прежде чем вы их запустите. Вы должны использовать настраиваемое правило условного форматирования, чтобы выделить строку, которая полностью дублируется.Функция удаления дубликатов

Предположим, вы используете Excel для отслеживания адресов и подозреваете, что у вас есть повторяющиеся записи. Посмотрите на пример рабочего листа Excel ниже:



Обратите внимание, что запись «Джонс» появляется дважды. Чтобы удалить такие повторяющиеся записи, щелкните вкладку «Данные» на ленте и найдите функцию «Удалить дубликаты» в разделе «Инструменты для работы с данными». Щелкните Удалить дубликаты, и откроется новое окно.



Здесь вы должны принять решение в зависимости от того, используете ли вы метки заголовков в верхней части столбцов.Если вы это сделаете, выберите вариант с надписью Мои данные с заголовками. Если вы не используете метки заголовков, вы будете использовать стандартные обозначения столбцов Excel, такие как столбец A, столбец B и т. Д.



В этом примере мы выберем только столбец A и нажмем кнопку OK. Окно опций закрывается, и Excel удаляет вторую запись «Джонса».



Конечно, это был простой пример. Любые записи адресов, которые вы ведете с помощью Excel, скорее всего, будут намного сложнее.Предположим, например, у вас есть файл адресов, который выглядит так.



Обратите внимание, что, хотя есть три записи «Джонса», только две идентичны. Если бы мы использовали описанные выше процедуры для удаления повторяющихся записей, осталась бы только одна запись «Джонс». В этом случае нам необходимо расширить критерии принятия решения, включив в него как имя, так и фамилию, указанные в столбцах A и B соответственно.

Для этого еще раз щелкните вкладку «Данные» на ленте, а затем нажмите «Удалить дубликаты».На этот раз, когда появится окно параметров, выберите столбцы A и B. Нажмите кнопку «ОК» и обратите внимание, что на этот раз Excel удалил только одну из записей «Мэри Джонс».

Это потому, что мы сказали Excel удалять дубликаты, сопоставляя записи на основе столбцов A и B, а не только столбца A. Чем больше столбцов вы выберете, тем больше критериев необходимо выполнить, прежде чем Excel рассмотрит запись быть дубликатом. Выберите все столбцы, если вы хотите удалить полностью повторяющиеся строки.



Excel выдаст сообщение о том, сколько дубликатов было удалено.Однако он не покажет вам, какие строки были удалены! Прокрутите вниз до последнего раздела, чтобы увидеть, как сначала выделить повторяющиеся строки перед запуском этой функции.



Метод расширенного фильтра

Второй способ удаления дубликатов - использовать параметр расширенного фильтра. Сначала выберите все данные на листе. Затем на вкладке «Данные» на ленте нажмите «Дополнительно» в разделе «Сортировка и фильтр».



В появившемся диалоговом окне обязательно установите флажок Только уникальные записи.



Вы можете отфильтровать список на месте или скопировать недублирующиеся элементы в другую часть той же электронной таблицы. . По какой-то непонятной причине вы не можете скопировать данные на другой лист. Если вы хотите, чтобы они были на другом листе, сначала выберите место на текущем листе, а затем вырежьте и вставьте эти данные на новый лист.

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

Если вы хотите увидеть, какие записи дублируются, прежде чем удалять их, вам придется немного поработать вручную.К сожалению, в Excel нет возможности выделять полностью повторяющиеся строки. В нем есть функция условного форматирования, которая выделяет повторяющиеся ячейки, но эта статья посвящена повторяющимся строкам.

Первое, что вам нужно сделать, это добавить формулу в столбец справа от вашего набора данных. Формула проста: просто объедините все столбцы для этой строки вместе. = A1 & B1 & C1 & D1 & E1

В моем примере ниже у меня есть данные в столбцах от A до F. Однако первый столбец - это идентификационный номер, поэтому я исключаю его из приведенной ниже формулы.Обязательно включите все столбцы с данными, в которых вы хотите проверить наличие дубликатов.



Я поместил эту формулу в столбец H, а затем перетащил ее вниз для всех своих строк. Эта формула просто объединяет все данные в каждом столбце в один большой фрагмент текста. Теперь пропустите еще пару столбцов и введите следующую формулу: = СЧЁТЕСЛИ ($ H $ 1: $ H $ 34, $ H1)> 1

Здесь мы используем функцию СЧЁТЕСЛИ, а первым параметром является набор данных, который мы хотим изучить. Для меня это был столбец H (в котором есть формула объединения данных) от строк с 1 до 34.Также рекомендуется перед этим избавиться от строки заголовка.

Вы также должны убедиться, что используете знак доллара ($) перед буквой и цифрой. Например, если у вас есть 1000 строк данных и ваша формула объединенной строки находится в столбце F, ваша формула будет выглядеть так: = COUNTIF ($ F $ 1: $ F $ 1000, $ F1)> 1

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



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



В диалоговом окне нажмите "Использовать формулу", чтобы определить, какие ячейки нужно форматировать.



В поле в разделе "Значения формата, где эта формула истинна:" введите следующую формулу, заменив P столбцом, в котором значения ИСТИНА или ЛОЖЬ. Не забудьте поставить знак доллара перед буквой столбца. = $ P1 = TRUE

Как только вы это сделаете, нажмите "Форматировать" и перейдите на вкладку "Заливка". Выберите цвет, который будет использоваться для выделения всей повторяющейся строки. Нажмите OK, и теперь вы должны увидеть, что повторяющиеся строки выделены.



Если это не сработало для вас, начните сначала и сделайте это снова медленно.Это должно быть сделано правильно, чтобы все это работало. Если вы пропустите хотя бы один символ $, он не будет работать должным образом. Предостережения при удалении повторяющихся записей

Конечно, есть несколько проблем с тем, чтобы Excel автоматически удалял повторяющиеся записи за вас. Во-первых, вы должны быть осторожны, выбирая слишком мало или слишком много столбцов для Excel, чтобы использовать их в качестве критерия для выявления повторяющихся записей.

Слишком мало, и вы можете случайно удалить нужные записи. Слишком много или случайно включен столбец идентификатора, и дубликаты не будут найдены.

Во-вторых, Excel всегда предполагает, что первая встреченная уникальная запись является основной записью. Все последующие записи считаются дубликатами. Это проблема, если, например, вы не смогли изменить адрес одного из людей в своем файле, а вместо этого создали новую запись.

Если новая (правильная) запись адреса появляется после старой (устаревшей) записи, Excel будет считать, что первая (устаревшая) запись будет главной, и удалит все последующие находит записи. Вот почему вы должны быть осторожны, насколько либерально или консервативно вы позволяете Excel решать, что является дублирующейся записью, а что нет.

В таких случаях вам следует использовать метод выделения дубликатов, о котором я писал, и вручную удалить соответствующую дублирующую запись.

Наконец, Excel не просит вас подтверждать, действительно ли вы хотите удалить запись. Используя выбранные вами параметры (столбцы), процесс полностью автоматизирован. Это может быть опасно, если у вас огромное количество записей, и вы уверены, что принятые вами решения были правильными, и позволяете Excel автоматически удалять повторяющиеся записи за вас.

Также не забудьте ознакомиться с нашей предыдущей статьей об удалении пустых строк в Excel. Наслаждайтесь!

.
SETUP UA COMPUTER BLOG