Semenalidery.com

IT Новости из мира ПК
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Удаление одинаковых строк в excel

Удалить дубликаты в Excel

В данной статье мы разберем, как удалить дубликаты в Excel 2007, Excel 2010, Excel 2013 и Excel 2016. Вы узнаете несколько разных способов как найти и удалить дубликаты в Excel с или без первых вхождений, удалить дубликаты строк, как удалить дубликаты в столбце, как обнаружить абсолютные дубликаты и частичные совпадения. В статье рассмотрены следующие пункты:

Удалить дубликаты строк в Excel с помощью функции «Удалить дубликаты»

Если вы используете последними версиями Excel 2007, Excel 2010, Excel 2013 или Excel 2016, у вас есть преимущество, потому что эти версии содержат встроенную функцию для поиска и удаления дубликатов – функцию Удалить дубликаты.

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

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

  1. Для начала выберите диапазон, в котором вы хотите удалить дубликаты. Чтобы выбрать всю таблицу, нажмите Ctrl+A .
  2. Далее перейдите на вкладку « ДАННЫЕ » —> группа « Работа с данными » и нажмите кнопку «Удалить дубликаты».

Удалить дубликаты в Excel – Функция Удалить дубликаты в Excel
  1. Откроется диалоговое окно «Удалить дубликаты». Выберите столбцы для проверки дубликатов и нажмите « ОК ».
  • Чтобы удалить дубликаты строк, имеющие полностью одинаковые значения во всех столбцах, оставьте флажки рядом со всеми столбцами, как показано на изображении ниже.
  • Чтобы удалить частичные дубликаты на основе одного или нескольких ключевых столбцов, выберите только соответствующие столбцы. Если в вашей таблице много столбцов, лучше сперва нажать кнопку « Снять выделение », а затем выбрать столбцы, которые вы хотите проверить на предмет дубликатов.
  • Если в вашей таблице нет заголовков , уберите флаг с поля « Мои данные содержат заголовки » в правом верхнем углу диалогового окна, которое обычно выбирается по умолчанию.

Удалить дубликаты в Excel – Выбор столбца(ов), который вы хотите проверить на наличие дубликатов

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

Удалить дубликаты в Excel – Сообщение о том, сколько было удалено дубликатов

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

Удалить дубликаты, скопировав уникальные записи в другое место

Другой способ удалить дубликаты в Excel — это разделение уникальных значений и копирование их на другой лист или в выбранный диапазон на текущем листе. Разберем этот способ.

  1. Выберите диапазон или всю таблицу, которую вы хотите удалить дубликаты.
  2. Перейдите во вкладку « ДАННЫЕ » —> группа « Сортировка и фильтр » и нажмите кнопку « Дополнительно ».

Удалить дубликаты в Excel – Использование дополнительного фильтра для удаления дубликатов
  1. В диалоговом окне « Расширенный фильтр » выполните следующие действия:
  • Выберите пункт « скопировать результат в другое место ».
  • Проверьте, отображается ли правильный диапазон в Исходном диапазоне . Это должен быть диапазон, выбранный на шаге 1.
  • В поле Поместить результат в диапазон введите диапазон, в котором вы хотите скопировать уникальные значения (на самом деле достаточно выбрать верхнюю левую ячейку диапазона назначения).
  • Выберите Только уникальные записи

Удалить дубликаты в Excel – Фильтр дубликатов
  1. Наконец, нажмите « ОК ». Excel удалит дубликаты и скопирует уникальные значения в новое указанное место:

Удалить дубликаты в Excel – Уникальные записи, скопированные из другого места

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

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

Удалить дубликаты строк в Excel с помощью формул и фильтра

Еще один способ удалить дубликаты в Excel — это определить их с помощью формулы, отфильтровать и удалить дубликаты строк.

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

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

Формулы для поиска дубликатов в 1 столбце

  • Дубликаты за исключением 1-го вхождения:

=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A2; $A2)>1; «Дубликат»; «»)

  • Дубликаты с 1-го вхождения:

=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$10; $A2)>1; «Дубликат»; «Уникальное»)

Где ячейка A2 является первой, а A10 — последней ячейкой диапазона для поиска дубликатов.

Формулы для поиска дубликатов строк

  • Дубликаты строк, кроме 1-го вхождения. То есть, если в результате есть две или более одинаковых строки, то первая из них будет отмечена, как уникальная, а все последующие как дубликаты:

=ЕСЛИ(СЧЁТЕСЛИМН($A$2:$A2; $A2; $B$2:$B2; $B2; $C$2:$C2; $C2)>1;»Дубликат строки»; «Уникальное»)

  • Дубликаты строк с 1-ым вхождением. В данном случае, если в результате поиска есть две или более одинаковых строк, то все они будут отмечены как дубликаты:

=ЕСЛИ(СЧЁТЕСЛИМН($A$2:$A$10; $A2; $B$2:$B$10; $B2; $C$2:$C$10; $C2)>1; «Дубликат строки»; «Уникальное»)

Где A, B и C — столбцы, подлежащие проверке на дубликаты.

Например, так вы можете идентифицировать дубликаты строк, за исключением 1-го вхождения:

Читать еще:  Видео телефон айфон

Удалить дубликаты в Excel – Формула для идентификации дубликатов строк за исключением первых случаев
  1. Выберите любую ячейку в своей таблице и примените автоматический фильтр, нажав кнопку « Фильтр » на вкладке « ДАННЫЕ », или « Сортировка и фильтр » —> « Фильтр » на вкладке « ГЛАВНАЯ ».

Удалить дубликаты в Excel – Применение фильтра к ячейкам (Вкладка ДАННЫЕ)
  1. Отфильтруйте дубликаты строк, щелкнув стрелку в заголовке столбца «Дубликаты», а затем установите флажок « Дубликат строки ».

Удалить дубликаты в Excel – Фильтр дубликатов строки
  1. И, наконец, удалите дубликаты строк. Чтобы сделать это, выберите отфильтрованные строки, переместив указатель мыши на номера строк, щелкните по ним правой кнопкой мыши и выберите « Удалить строку » в контекстном меню. В данном случае для удаления дубликатов не стоит пользоваться клавишей « Delete » на клавиатуре, потому что нам необходимо удалить целые строки, а не только содержимое ячеек:

Удалить дубликаты в Excel – Фильтрация и удаление дубликатов строк

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

Удаление дубликатов строк в Excel

Работая с Microsoft Excel очень часто возникает ситуация, когда необходимо удалить повторяющиеся строки. Этот процесс может превратиться в бессмысленную, монотонную и трудоемкую работу, однако, существует ряд способов упростить задачу. Сегодня мы разберем несколько удобных методов нахождения и удаления повторяющихся строк в Excel. В качестве примера возьмем следующую таблицу с данными:

Вариант 1: Команда Удалить дубликаты в Excel

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

Перейдите на вкладку Date (Данные), а затем нажмите команду Remove Duplicates (Удалить дубликаты), как показано ниже.

Появится небольшое диалоговое окно Remove Duplicates (Удалить дубликаты). Можно заметить, что выделение первой строки снимается автоматически. Причиной тому является флажок, установленный в пункте My data has headers (Мои данные содержат заголовки).

В нашем примере нет заголовков, поскольку таблица начинается с 1-й строки. Поэтому снимем флажок. Сделав это, Вы заметите, что вся таблица снова выделена, а раздел Columns (Колонны) изменится с dulpicate на Column A, B и С.

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

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

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

Затем перейдите на вкладку Data (Данные), в группе команд Sort & Filter (Сортировка и фильтр) нажмите команду Advanced (Дополнительно), как показано ниже. Если Вы используете Excel 2003, вызовите раскрывающееся меню Data (Данные), выберите Filters (Фильтры), а затем Advanced Filters (Расширенные фильтры).

Теперь необходимо установить флажок Unique records only (Только уникальные записи).

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

Вариант 3: Замена

Этот метод удобно применять, когда необходимо найти повторяющиеся строки в небольших таблицах. Мы будем использовать инструмент Find and Replace (Поиск и замена), который встроен во все продукты Microsoft Office. Для начала необходимо открыть таблицу Excel, с которой планируется работать.

Открыв таблицу, выберите ячейку, содержимое которой требуется найти и заменить, и скопируйте ее. Для этого выделите нужную ячейку и нажмите сочетание клавиш Ctrl+C.

Скопировав слово, которое необходимо найти, воспользуйтесь сочетанием Ctrl+H, чтобы вызвать диалоговое окно Find and Replace (Поиск и замена). Вставьте скопированное слово с поле Найти, нажав Ctrl+V.

Нажмите кнопку Options (Параметры), чтобы открыть дополнительный список опций. Установите флажок у пункта Match entire cell contents (Ячейка целиком). Это необходимо сделать, поскольку в некоторых ячейках искомые слова находятся вместе с другими словами. Если не выбрать эту опцию, можно непреднамеренно удалить ячейки, которые требуется оставить. Убедитесь, что все остальные настройки соответствуют указанным на рисунке ниже.

Теперь необходимо ввести значение в поле Replace with (Заменить на). В данном примере мы воспользуемся цифрой 1. Введя нужное значение, нажмите Replace All (Заменить все).

Можно заметить, что все значения duplicate в ячейках таблицы, будут заменены на 1. Мы использовали значение 1, так как оно небольшое и выделяется в тексте. Теперь Вы можете визуально определить строки, которые имеют повторяющиеся значения.

Чтобы оставить один из дубликатов, достаточно вставить исходный текст обратно в строку, которая была заменена. В нашем случае мы восстановим значения в 1-й строке таблицы.

Определив строки с повторяющимся содержимым, поочередно выделите их, удерживая клавишу Ctrl.

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

Сделав это, Вы можете заметить, что все оставшиеся строки имеют уникальные значения.

Excel — как удалить дубликаты но оставить уникальные значения?

Всем добрый вечер! Случалось ли Вам когда нибудь работать с данными в excel строковое значение которых переваливает за пару десятков тысяч? А вот мне человеку который создает и продвигает сайты и интернет магазины приходится сталкиваться достаточно часто, особенно когда дело касается загрузки и выгрузки данных от поставщика на сайт интернет магазина. Данная заметка родилась не на пустом месте, а прямо так сказать с пылу жару! Сегодня делал загрузку на свой интернет магазин по интим тематике (см портфолио) и после того как скачал прайс от поставщика и открыв его в excel (перед загрузкой на сайт я сначала все сверяю, на случай ошибок и случайных изменений столбцов со стороны поставщика) и увидел что из 25 тыс строк более 6-8 тыс являются дубликатами, зачем и почему так делает поставщик мы сейчас обсуждать не будем, на это не хочется тратить не сил, ни времени, а просто понимаем, что так сделал программист и по другому это делать нельзя!

Читать еще:  Расчет ско в excel

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

Удаление дубликатов в Microsoft Excel

Для меня человека который проводит время в отпуске и работает с мобильного интернета скорость которого измеряется от 1-2 мегабита, прокачивать в пустую такое кол-во товара с фотографиями смысла не имеет и время пустое и трафика сожрет не мало, поэтому решил повторяющиеся товары просто удалить и тут столкнулся с тем, что удалить дублирующиеся значения в столбце не так то и просто, потому как стандартная функция excel 2010 делает это топорно и после удаления дубликата двигает вверх нижние значения и в итоге у нас все перепутается в документе и будет каша.

В данной статье будет представлено два варианта решения проблемы.

1 Вариант — Стандартная функция в эксель — Удалить дубликаты

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

Для этого выделим те столбцы или область в какой надо удалить дубликаты и зайдем в меню Данные и потом выберем Удалить дубликаты, после чего у нас удаляться дубликаты, но будет сдвиг ячеек, если для вас это не критично, то этот способ Ваш!

2 Вариант — Пометить дубликаты строк в Лож или Истина

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

для примера возьмем два столбика с данными, в одном (пример1) дублирующие значения повторяются, а в (примере2) в разнобой и не идут друг за другом.

В примере1 мы должны в стоящей рядом ячейки нажать знак ровно и выбрать первое и нижние значение что бы формула была такая:

и нажимаем энтер, и у нас в этой ячейки в зависимости от данных должно появится значение Лож или Истина

ЛОЖ — если А1 не будет равно А2

Истина — если А1 будет ровно А2

если применить этот вариант на столбце Пример2, то как вы поняли везде будет значение Лож

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

3 Вариант — Удалить дубликаты в столбе

Вот этот вариант уже более сложный, но он решит вашу проблему на все 100% и сразу ответит на все вопросы.

Как видим у нас имеется столбец в котором все значения идут не по порядку и они перемешаны

Мы как и в прошлый раз в соседнюю ячейку вставляем следующую формулу

После применения которой у нас будет либо пуская ячейка, либо значение из ячейки напротив.

из нашего примера сразу видно, что в этом столбце было два дубля и эта формула нам значительно сэкономила времени, а дальше фильтруем второй столбец и в фильтре выбираем пустые ячейки и дальше удаляем строки, вот и все)

Таким образом я в документе который который скачал у поставщика создал перед артикулом пустой столбце и далее применил эту формулу и после отфильтровав получил документ который был на 6-8 тыс строк меньше и самое главное после удаление дубликатов у меня не поднимались значения вверх, все стояло на своих местах

Надеюсь статья была полезная, если не поняли я прикрепил к каналу видео смотрите его или задавайте вопросы,

Как удалить повторяющиеся значения в таблице Excel

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

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

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

Способ №1

1. Удерживая левую кнопку мышки, обозначьте курсором границы области таблицы (отдельные строки, столбцы), где необходимо убрать повторы. Либо выделите весь проект.

2. В меню редактора перейдите на вкладку «Данные».

Читать еще:  Знакомство с excel 2020

3. В блоке «Работа с данными» нажмите кнопку «Удалить дубликаты».

4. В появившейся панели выберите столбцы, в которых нужно убрать одинаковые значения посредством установки меток. Если элементов много, используйте для быстрой настройки удаления опции «Выделить всё» и «Снять выделение». Нажмите «OK».

5. По завершении очистки строк и столбцов таблицы от дублей появится сообщение о том, сколько осталось уникальных значений.

Совет! Повторы сразу после удаления можно восстановить, щёлкнув в левом верхнем углу Excel значок «стрелочку влево».

Способ №2

1. Кликните по редактируемой таблице.

2. На вкладке «Данные», в блоке «Сортировка и фильтр», щёлкните подраздел «Дополнительно».

Если необходимо создать новую таблицу, содержащую только уникальные ячейки исходника:

1. В панели «Расширенный фильтр» клацните радиокнопку «Скопировать результат в другое место».

2. Нажмите кнопку, расположенную в правой части поля «Поместить результат в диапазон».

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

4. Кликните окошко «Только уникальные записи» и нажмите «OK».

5. После выполнения фильтрации в указанном месте появится версия исходной таблицы без повторов.

Чтобы отредактировать документ без создания копий:

  • в панели «Расширенный фильтр» установите режим обработки «Фильтровать список на месте»;
  • кликом мышки включите надстройку «Только уникальные записи»;
  • клацните «OK».

Способ №3

1. Кликните вкладку «Главная».

2. В разделе «Стили» откройте выпадающее подменю «Условное форматирование».

3. Откройте: Правила выделения ячеек → Повторяющиеся значения…

4. В ниспадающем меню «значения с» выберите наиболее удобный для вас вариант цветового подсвечивания повторов в таблице. Кликните «OK».

5. Удалите выборочно или группами обозначенные дубли через контекстное меню Excel (клик правой кнопкой → Удалить).

Комфортной вам работы в Excel!

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

Доброго времени суток!

С популяризацией компьютеров за последние 10 лет — происходит и популяризация создания отчетов (документов) в программе Excel.

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

Собственно, ко мне ни раз и ни два обращались с подобными вопросами, и я решил оформить работу с дубликатами в отдельную небольшую статью (которую вы сейчас читаете). Приведу самые часто-встречаемые задачи, и покажу их решение.

Примечание : все примеры ниже будут представлены в Office 2016/2019 (актуально также для Office 2013, 2010, 2007). Рекомендую всегда использовать относительно новые версии Office: в них и быстрее работать, и проще.

Простое авто-удаление дублей строк

Представим одну из самых популярных задач: есть список строк с ФИО людей, в строках много совпадений, нужно оставить только уникальные значения (кстати, это просто пример, все совпадения с реальными людьми — случайны) .

Задачка проиллюстрирована на скриншоте ниже (это пример: понятно, что 2 строки можно удалить и руками, но в реальности же строк может быть и 2000, и 20000, где «руки» будут бессильны. ).

Пример задачи в Excel

Чтобы в Excel удалить повторяющиеся строки, необходимо:

    выделить свою табличку (строки и столбцы) с данными;

Выделение таблицы в Excel

далее перейти в раздел «Данные» и нажать по инструменту «Удалить дубликаты» (см. скрин ниже);

Данные — удалить дубликаты // Excel

после чего появится окно с настройками: если у вас есть заголовки в таблице — рекомендую поставить галочку » Мои данные содержат заголовки» . После — нажмите кнопку OK;

Удаление (выделение заголовков)

в результате увидите сообщение: сколько строк было удалено, и сколько уникальных осталось. Пример выполненной задачи представлен ниже.

Лишние строки удалены!

Копирование уникальных строк (данных) в новое место

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

Как это делается:

    сначала необходимо выделить табличку, затем перейти в раздел «Данные» и нажать на кнопку «Дополнительно» (подраздел «Сортировка и фильтр», см. скриншот ниже);

Раздел «Данные» — дополнительно (Excel)

далее переключите ползунок в режим «Скопировать результат в другое место» , в строке «Поместить результат в диапазон» — выберите ячейку, где будет начало таблицы; и поставьте галочку напротив пункта «Только уникальные значения» ; нажмите OK. Пример представлен на скриншоте ниже;

Скопировать только уникальные значения в другое место

после чего вы увидите, как появились данные в выбранном вами месте, причем, строки останутся только уникальные. Дальше можно с ними работать в обычном режиме.

Поиск и выделение повторяющихся значений

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

Как выделить повторяющийся строки:

    сначала также выделяете все свои строки (пример ниже);

далее необходимо открыть раздел «Главная» в верхнем меню Excel, выбрать подраздел «Условное форматирование», затем «Правила выделения ячеек», «Повторяющиеся значения» (пример представлен на скриншоте ниже);

Повторяющиеся значения // Раздел «Главная» в Excel

после чего, вам останется выбрать какие строки вы будете выделять (уникальные или повторяющиеся), и цвет выделения (по умолчанию — красно-розовый).

Выбираем цвет, как выделим строки

Надеюсь, информация будет полезна для работы с большими таблицами.

На этом сегодня всё, всем удачной работы!

Ссылка на основную публикацию
Adblock
detector