Как восстановить удаленные дубли в экселе

По получившемуся столбцу можно легко отфильтровать строки с повторами и работать потом с ними дальше уже вручную.

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

Способ 2. Выделение цветом повторов внутри ячейки макросом

Если дубликаты нужно именно наглядно показать, то удобнее будет использовать для этого специальный макрос. Откроем редактор Visual Basic одноимённой кнопкой на вкладке Разработчик (Developer — Visual Basic) или сочетанием клавиш Alt + F11 . Вставим в книгу новый пустой модуль через меню Insert — Module и скопируем туда вот такой код:

Sub Color_Duplicates() Dim col As New Collection Dim curpos As Integer, i As Integer On Error Resume Next For Each cell In Selection Set col = Nothing curpos = 1 'убираем лишние пробелы и разбиваем текст из ячейки по пробелам arWords = Split(WorksheetFunction.Trim(cell.Value), " ") For i = LBound(arWords) To UBound(arWords) 'перебираем слова в получившемся массиве Err.Clear 'сбрасываем ошибки curpos = InStr(curpos, cell, arWords(i)) 'позиция начала текущего слова col.Add arWords(i), arWords(i) 'пытаемся добавить текущее слово в коллекцию If Err.Number <> 0 Then 'если возникает ошибка - значит это повтор, выделяем красным cell.Characters(Start:=curpos, Length:=Len(arWords(i))).Font.ColorIndex = 3 cell.Characters(Start:=InStr(1, cell, arWords(i)), Length:=Len(arWords(i))).Font.ColorIndex = 3 End If curpos = curpos + Len(arWords(i)) 'переходим к следующему слову Next i Next cell End Sub

Теперь можно вернуться в главное окно Excel, выделить ячейки с текстом и запустить созданный макрос через кнопку Макросы на вкладке Разработчик (Developer — Macros) или сочетанием клавиш Alt + F8 . Этот макрос проходит по всем выделенным ячейкам и помечает повторения красным цветом шрифта прямо внутри ячейки:

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

Способ 3. Выводим повторы в соседний столбец

Если повторы внутри ячеек нужно не просто подсветить, а явным образом вывести, например, в соседний столбец, то удобнее будет использовать для этого макрофункцию, созданную по образу предыдущего макроса. Добавим в редакторе Visual Basic новый модуль и вставим туда код нашей функции GetDuplicates:

Function GetDuplicates(cell As Range) As String Dim col As New Collection Dim i As Integer, sDupes As String On Error Resume Next Set col = Nothing 'делим текст в ячейке по пробелам arWords = Split(WorksheetFunction.Trim(cell.Value), " ") 'проходим в цикле по всем получившимся словам For i = LBound(arWords) To UBound(arWords) Err.Clear 'сбрасываем ошибки col.Add arWords(i), arWords(i) 'пробуем добавить слово в коллекцию 'если ошибки не возникает, то это не повтор - добавляем слово к результату If Err.Number <> 0 Then sDupes = sDupes & " " & arWords(i) Next i GetDuplicates = Trim(sDupes) 'выводим результаты End Function

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

Способ 4. Удаление повторов внутри ячейки макросом

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

Sub Delete_Duplicates() Dim col As New Collection Dim i As Integer On Error Resume Next For Each cell In Selection Set col = Nothing sResult = "" 'делим текст в ячейке по пробелам arWords = Split(WorksheetFunction.Trim(cell.Value), " ") 'проходим в цикле по всем получившимся словам For i = LBound(arWords) To UBound(arWords) Err.Clear 'сбрасываем ошибки col.Add arWords(i), arWords(i) 'пробуем добавить слово в коллекцию 'если ошибки не возникает, то это не повтор - добавляем слово к результату If Err.Number = 0 Then sResult = sResult & " " & arWords(i) Next i cell.Value = Trim(sResult) 'выводим результаты без повторов Next cell End Sub

Способ 5. Удаление повторов внутри ячейки через Power Query

Этот способ использует бесплатную надстройку Excel для обработки данных под названием Power Query. Для Excel 2010-2013 скачать её можно с сайта Microsoft, а в Excel 2016-2019 она уже встроена по умолчанию. Огромным плюсом этого варианта является возможность автоматического обновления — если в будущем исходные данные изменятся, то нам не придется заново проделывать всю обработку (как в Способе 1) или запускать макрос (как в Способе 4) — достаточно будет просто обновить созданный запрос.

Сначала наши данные нужно загрузить в Power Query. Проще всего для этого превратить нашу таблицу в «умную» сочетанием клавиш Ctrl + T или кнопкой Форматировать как таблицу на вкладке Главная (Home — Format as Table) , а затем нажать кнопку Из таблицы/диапазона (From table/range) на вкладке Power Query (если у вас Excel 2010-2013) или на вкладке Данные (если у вас Excel 2016 или новее):

Поверх окна Excel откроется окно редактора запросов Power Query с загруженными туда нашими данными:

Дальше делаем следующую цепочку действий:

Удаляем ненужный пока шаг Измененный тип (Changed Type) справа в панели применённых шагов с помощью крестика слева от шага.

Чтобы можно было потом идентифицировать принадлежность каждого имени к исходной строке — добавляем столбец с нумерацией строк на вкладке Добавление столбца — Столбец индекса — От 1 (Add Column — Index Column — From 1) :

Выделяем столбец с именами и жмём на вкладке Преобразование — Разделить столбец — По разделителю (Transform — Split Column — By delimiter) , а в открывшемся окне выбираем деление по каждому пробелу и — главное — деление на строки, а не на столбцы в расширенных параметрах:

После нажатия на ОК увидим следующее:

Теперь выделяем оба столбца (удерживая клавишу Ctrl или Shift ) и удаляем дубликаты через Главная — Удалить строки — Удалить дубликаты (Home — Remove Rows — Remove Duplicates) .

Осталось собрать всё обратно в ячейки 🙂 Для этого выделим столбец Индекс и используем команду Группировать по на вкладке Преобразование (Transform — Group By) со следующими параметрами:

После нажатия на ОК наши имена сгруппируются во вложенные таблицы, имитирующие начальные ячейки — только уже без повторов. Увидеть содержимое свёрнутых таблиц можно, если щёлкнуть мышью в фон ячейки рядом со словом Table (но не в слово Table!):

Осталось вытащить все имена из первой колонки каждой таблицы и склеить их через пробел. Это можно сделать с помощью небольшой формулы на встроенном в Power Query языке М. Выберем на вкладке Добавление столбца команду Настраиваемый столбец (Add Column — Custom Column) и введём в открывшееся окно имя нового столбца и формулу (с соблюдением регистра!):

Здесь выражение [Ячейки][Имена] извлекает содержимое столбца Имена из каждой таблицы в колонке Ячейки, а функция Text.Combine склеивает затем их все через заданный разделитель (пробел). После нажатия на ОК мы, наконец, увидим желаемое:

Осталось удалить ненужные более столбцы Индекс и Ячейки, щелкнув по их заголовкам правой кнопкой мыши и выбрав команду Удалить столбцы (Remove Columns) и выгрузить результаты на лист через Главная — Закрыть и загрузить — Закрыть и загрузить в (Home — Close & Load — Close & Load to..) :

Задача решена! Если в будущем данные в исходной «умной» таблице изменятся или к ней будут дописаны новые строки, то достаточно будет просто обновить запрос, щёлкнув по результирующей зелёной таблице правой кнопкой мыши и выбрав команду Обновить или нажав сочетание клавиш Ctrl + Alt + F5 .

Ссылки по теме

• Как найти и вывести отличия в двух списках в Excel
• Как подсветить совпадающие пары дубликатов одинаковым цветом
• Сравнение двух таблиц с помощью ВПР, сводной или Power Query

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

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

Как определить дубликаты в таблице

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

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

Это лишь один из примеров условного форматирования в Microsoft Excel. Если вас заинтересовала эта тема, читайте другую мою статью, где рассказано все о данной функции.

Комьюнити теперь в Телеграм
Подпишитесь и будьте в курсе последних IT-новостей

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

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

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

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

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

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

Excel works!

Как найти и удалить повторы и дубликаты в Excel

Август 13, 2015 / Написал Izotov / No Comments

Распространенный вопрос: как найти и удалить дубликаты в Excel. Предположим, вы выгрузили месячный отчет из вашей учетной системы, но в итоге вам нужно понять какие контрагенты вообще взаимодействовали с компанией за этот период — составить список контрагентов без повтарений. Как отобрать уникальные значения?

1. Как проще всего удалить дубликаты в таблице Excel

Можно ли удалить задвоеные, затроенные и так далее значения в Excel по нескольким столбцам?

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

Далее будет предложено выбрать столбцы, по которым будут искаться дубликаты. После выбора столбцов — жмете ОК.

Выбираем первый столбец

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

2. Как выделить все дубликаты в Excel?

Уже слышали про Условное форматирование ? Здесь оно тоже поможет! Выделяете столбец, в котором надо пометить дубликаты, выбираете в меню Главное — Условное форматирование — Правила выделения ячеек — Повторяющиеся значения…

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

Нажимаете ОК, если не хотите изменять форматирование. Теперь все данные по выбранным условиям подкрасятся.

Отмечу, что инструмент применяется только для выбранного одного (!) столбца.

Кстати, если нужно увидеть уникальные, то в окне слева выберите — уникальные.

3. Уникальные значения при помощи сводных таблиц

Признаюсь честно, когда-то я не подозревал о существовании возможности «удалить дубликаты» и пользовался сводными таблицами. Как я это делал? Выделяете таблицу, в которой надо найти уникальные значения — Вставка — Сводная таблица — Выбираете нужный столбец из открывшегося списка (перетащите в область по строкам).

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

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

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

Похожие статьи

• 06.08.2015Автозаполнение ячеек в ExcelPosted in Работа с текстом, Работа с ячейками
• 25.10.2015Автофильтр в Excel. Расширенный фильтрPosted in Excel, Работа с текстом, Работа с ячейками
• 28.01.2018Абзац в Excel. Как начать с новой строки в ячейкеPosted in Работа с текстом, Формулы, Работа с ячейками
• 20.04.2015Как быстро найти файл в папке?Posted in Прочее, Поиск
• 11.12.2016Поиск по примечаниям в ExcelPosted in Поиск
• 25.04.2015Инструмент найти и заменить в Excel и WordPosted in Поиск, Word
• 11.10.2016Как найти номер недели в Excel?Posted in Дата_Время, Поиск
• 10.03.2015Задать имя диапазона Excel? Горячие клавишиPosted in Excel, Работа с ячейками