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

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

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

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

Для того чтобы удалит дубликаты строк, необходимо:

  1. Выделить ячейки с нужной информацией.image
  2. Перейти на закладку «Данные».image
  3. Нажать «Удалить дубликаты». Появится информационное окно с указанием выделенного столбца.
  4. После выполнения, появится информация о количестве найденных и оставшихся элементов. В результате останутся только не повторяющиеся элементы.

Удаление дубликатов в умной таблице

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

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

Чтобы удалить повторяющиеся строки в такой таблице, следует:

  1. Установить курсор на любую ячейку области с записями.
  2. Перейти в «Конструктор».
  3. Выполнить операцию «Удалить дубликаты» (при необходимости откорректировать столбцы).
  4. Появится сообщение о количестве найденных и оставшихся дубликатов.

Используем фильтрацию

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

  Синий экран смерти: причины возникновения ошибки и его исправление

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

  1. Определить необходимую область.
  2. На закладке «Данные» установить фильтр.
  3. В том же блоке сортировки и фильтрации нажать «Дополнительно» для запуска расширенного фильтра. Проверить выбранный диапазон и установить галку «Только уникальные записи».
  4. После выполнения процедуры, дубли будут скрыты, а фильтрации уже не будет.
  5. Для возврата скрытых значений, нужно нажать кнопку «Фильтр».

Условное форматирование

Этим способом можно воспользоваться для поиска одинаковых записей в ячейках. Для удобства можно настроить цвет выделения. Для реализации нужно применить предустановленное правило выделения — «Повторяющиеся значения…».

Чтобы найти дубликаты в Excel, необходимо:

  1. Задать область поиска.
  2. На главной закладке, в разделе «Условное форматирование», выбрать правило.
  3. Задать цвет и тип данных — уникальные или повторяющиеся.
  4. В результате будут выделены все повторы.

Использование формулы

С помощью встроенной функции «ПОИСКПОЗ» можно найти повторяющиеся элементы в настраиваемом диапазоне. «ПОИСКПОЗ» — возвращает относительную позицию в массиве элемента, соответствующего указанному значению с учетом указанного порядка.

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

  1. Выбрать нужные ячейки.
  2. В разделе «Условное форматирование» создать новое правило.
  3. Выбрать «Использовать формулу для определения форматируемых ячеек».
  4. Вставить следующую формулу «=ПОИСКПОЗ(B3;$B:$B;0)<>СТРОКА(B3)» и задать формат (цвет, шрифт и т.д.).

Пояснение: В формуле, задается функция «ПОИСКПОЗ» которая ищет повторы по столбцу «B», начиная с ячейки «B3».

  1. Результатом будет выделение дублированных элементов в заданном формате. В будущем при изменении записей в столбце «B» (например добавление новых) заданная функция будет проверять эту запись по формуле каждый раз.

Копирование уникальных строк в новое расположение

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

  Несколько вариантов отключения DEP в Windows

Для выполнения, потребуется:

  1. Указать необходимый диапазон.
  2. На закладке «Данные», в разделе «Сортировка и фильтр», выбрать «Дополнительно». Установить – «скопировать результат в другое место», «только уникальные записи» и задать адреса ячеек нового места.
  3. Не повторяющиеся элементы будут скопированы в новое месторасположение.

Используем сводные таблицы

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

Для работы этого способа потребуется:

  1. Создать новый столбец со значением счетчика «1». Выбрать всю область.
  2. Используя закладку «Вставка», создать новую сводную таблицу.
  3. Перейти на новый лист и заполнить поля «Названия строк» и «Значения». Дубли будут подсчитаны мгновенно и отображаться будут на новом листе.

Кроме этого можно ознакомиться с видео по удалению дублей в Excel.

Удаление дубликатов в Google таблицах

В Google доступен только поиск уникальных записей в ячейках (методов удаления дублей нет):

  • используя сводные таблицы (подсчет дублированных значений);
  • с помощью функции unique (вывод результата из массива);
  • используя сторонние расширения;

Пример использования функции «unique»:

Для вывода уникальных записей, требуется применить формулу «=unique(диапазон проверки)»:

Читайте также:

Чтобы избежать ошибок при работе с таблицами, надо уметь удалять дубликаты в Excel. Для этого существует несколько способов. 

2 из 3 3 из 3

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

Простое удаление не позволит устранить повторы, если стоимость будет другой. Избежать ошибки можно, выбрав при настройке удаления дубликатов только те столбцы, которые будут сравниваться. Например, «Название» и «Марка» — но без «Цены» и «Количества». 

Применение расширенного фильтра 

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

Чтобы использовать фильтр, необходимо: 

Статья продолжится после рекламы

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

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

Выделение повторов 

Если пользователь не уверен, что программа автоматически удалит нужные строки, или в том, что она найдет все повторы построчно, можно только выделить дубликаты в Excel. А удалить их уже потом — вручную. Для этого нужно выполнить такие действия: 

  • Выделить таблицу, из которой удаляются повторы. 
  • Перейти на главной вкладке в группу стилей и выбрать условное форматирование. 
  • Перейти к правилам выделения ячеек и выбрать пункт «Повторяющиеся значения». 
  • Указать цвет текста и заливки, которыми будут выделены повторяющиеся сведения. Лучше всего сделать выделение заметно отличающимся от основного текста — так будет проще не пропустить дубликаты для удаления вручную. 

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

Создание сводной таблицы 

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

  • Советы

    Как сделать сводную таблицу в Excel

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

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

Теперь у пользователя получится сводная таблица, в которой все дубликаты будут сгруппированы вместе. Узнать о том, что среди информации есть повторы, можно по цифрам в последнем столбце. Если там будет не единица — значит, есть дубликаты. 

Читайте также:

Поделиться ссылкой Содержание

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

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

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

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

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

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

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

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

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

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

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

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

=А1=А2

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

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

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

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

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

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

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

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

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

=ЕСЛИ(СЧЁТЕСЛИ($A$2:A2;A2)>1;»»;A2)

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

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

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

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

(16)

Спросите у SEO-шника без чего он, как без рук! Он наверняка ответит: без Excel! Эксель – лучший друг и помощник и для специалиста в SEO, и для вебмастера.

Как в Эксель найти повторяющиеся значения?

Для примера я распределил фамилии прославленных футболистов российской эпохи в пару столбцов. Нарочно сделал повторы в столбиках (иллюстрации кликабельны).

Наша цель – найти повторы в столбцах Excel и выделить их цветом.

Действуем так:

Шаг №1. Выделяем весь диапазон.

Шаг №2. Кликаем на раздел «Условное форматирование» в главной вкладке.

Шаг №3. Наводим на пункт «Правила выделения ячеек» и в появившемся списке выбираем «Повторяющиеся значения».

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

Нажмите «ОК», и вы обнаружите: одинаковые ячейки в двух столбиках теперь выделены! Как видите, это вопрос 30 секунд.

Описанный вариант – самый удобный для пользователей Эксель версий 2013 и 2016.

Как вычислить повторы при помощи сводных таблиц

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

Вернёмся к нашим баранам футболистам. Я оставил один столбик, добавив в него ячейки-дубли, а также дописал заглавную строку (это обязательно).

Далее делаем следующее:

Шаг 1. В ячейках напротив фамилий проставляем единички. Вот так:

Шаг 2. Переходим в раздел «Вставка» главного меню и в блоке «Таблицы» выбираем «Сводная таблица».

Откроется окно «Создание сводной таблицы». Здесь нужно выбрать диапазон данных для анализа (1), указать, куда поместить отчёт (2) и нажать «ОК».

Только не ставьте галку напротив «Добавить эти данные в модель данных». Иначе Эксель начнёт формировать модель, и это парализует ваш комп на пару минут минимум.

Шаг 3. Распределите поля сводной таблицы следующим образом: первое поле (в моём случае «Футболисты») – в область «Строки», второе («Значение2») – в область «Значения». Используйте обычное перетаскивание (drag-and-drop).

Должно получиться так:

А на листе сформируется сама сводка – уже без дублированных ячеек. Зато во втором столбике будет указано, сколько ячеек-дублей с конкретным содержанием было обнаружено в первом столбике (например, Онопко – 2 шт.).

Этот метод «на бумаге» может выглядеть несколько замороченным, но уверяю: попробуете раз-два, набьёте руку, а потом все операции будете выполнять за минуту.

Заключение

При поиске дублей я, признаться, всегда пользуюсь первым из описанных мною способов – то есть действую через «Условное форматирование». Уж очень меня подкупает предельная простота этого метода.

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

Помогли ли тебе мои методы работы с данными? Или ты знаешь лучше? Поделись своим мнением в комментариях!

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

Специальный инструмент

В версиях редактора 2007, 2010, 2013 и 2016 годов присутствует специальная функция, которая расположена во вкладке Данные на Панели инструментов.

image

Процесс работы с инструментом следующий:

  1. Создаете таблицу данных, в которой присутствуют повторы.

image

  1. Выделяете весь диапазон и нажимаете кнопку Удалить дубликаты.

image

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

image

  1. Нажимаете ОК, и программа выдает сообщение о результатах работы.

image

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

Если вы используете так называемые умные таблицы, то одноименный инструмент необходимо искать во вкладке Конструктор на главной панели в блоке Сервис. Принцип работы и недостатки те же.

image

Другие методы

Существуют альтернативные способы избавления от повторов, которые также будут работать в excel 2003 года. Рассмотрим подробнее:

  1. Дубликаты можно удалить формулой. Создаете дополнительный столбец, в котором используете функцию СЧЁТЕСЛИ (на английском COUNTIF). В качестве первого аргумента выбираете анализируемый диапазон, который обязательно фиксируете клавишей F4 или значком доллара, а во втором блоке ставите ссылку на ячейку из столбца. Применяете формулу ко всем строкам при помощи маркера автозаполнения. Затем убираете строки, где стоит ИСТИНА, которая указывает на повтор.

image

Используя этот способ можно без смещения и удаления увидеть повторяющиеся значения.

  1. Расширенный фильтр, который расположен во вкладке Данные, позволяет показать уникальные записи, а остальные данные скрываются. Об этом свидетельствует синий цвет номеров строк.

image

Результат работы:

  1. На главной вкладке в настройках условного форматирования можно включить подсветку повторяющихся значений, что позволяет оценить количество дубликатов по всем столбцам без сдвига строк.
  1. Пользовательский макрос, написанный на языке программирования vba (Visual Basic). Этот способ требует определенных знаний в кодировке программы, синтаксисе языка, а также необходимо иметь идею реализации поиска дублей в базе данных, что под силу только опытным пользователям MS Excel.

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

Оцените статью
Рейтинг автора
5
Материал подготовил
Илья Коршунов
Наш эксперт
Написано статей
134
А как считаете Вы?
Напишите в комментариях, что вы думаете – согласны
ли со статьей или есть что добавить?
Добавить комментарий