Удаление дубликатов в excel с помощью таблиц

Поиск одинаковых значений в Excel

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

На рисунке – списки писателей. Алгоритм действий следующий:

• Выбрать ячейку I3 с записью «С. А. Есенин».
• Поставить задачу – выделить цветом ячейки с такими же записями.
• Выделить область поисков.
• Нажать вкладку «Главная».
• Далее группа «Стили».
• Затем «Условное форматирование»;
• Нажать команду «Равно».

Появится диалоговое окно:

• В левом поле указать ячейку с I2, в которой записано «С. А. Есенин».
• В правом поле можно выбрать цвет шрифта.
• Нажать «ОК».

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

Несложно понять, как в Экселе найти одинаковые значения в столбце. Просто выделить перед поиском нужную область – конкретный столбец.

Подсветка последовательных дубликатов ячеек.

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

Для этого выберите область с данными (не включая заголовок) и создайте условное форматирование с одной из следующих формул:

Чтобы выделить два идущих друг за другом заказа одного и того же менеджера:

Чтобы выделить второй заказ менеджера, если у него 2 заказа идут подряд:=$G1=$G2

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

при выделении только второго повтора

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

Сводные таблицы для обнаружения повторяющихся значений

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

11

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

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

12

Способ 4. Применение сводных таблиц

Данный метод предполагает соблюдение следующего пошагового алгоритма:

1. Добавить к исходной таблице вспомогательный столбец и пронумеровать его от 1 до N. N — это номер последней строчки в массиве.

Добавление вспомогательного столбца

1. Перейти в раздел «Вставка» и нажать по кнопке «Сводная таблица».

Путь к кнопке «Сводная таблица»

1. В следующем окошке поставить тумблер в строку «На существующий лист», в поле «Таблица или диапазон» указать конкретный диапазон ячеек.
2. В строчке «Диапазон» указать начальную ячейку, в которую будет добавлен исправленный табличный массив и нажать на «ОК».

Действия в окошке сводной таблички

1. В окне слева рабочего листа указать галочки напротив названий столбцов таблицы.

Действия в отобразившемся меню слева рабочего поля

1. Проверить результат.

Удаление дубликатов с помощью таблиц

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

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

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

1. Превратить диапазон в умную таблицу. Чтобы это сделать, достаточно просто нажать комбинацию клавиш CTRL + T.

2. Далее достаточно нажать по пункту «Удалить дубликаты», который находится на вкладке «Конструктор».

3. Чтобы удалить дубликаты, нужно предварительно выключить проверку по четвертому столбцу (или, естественно, тому, который соответствует вашему случаю).

4. Теперь строки с одинаковыми значениями были убраны из таблицы.

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

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

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

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

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

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

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

Поиск совпадений при помощи команды «Найти».

Еще один простой, но не слишком технологичный способ – использование встроенного поиска.

Зайдите на вкладку Главная и кликните «Найти и выделить». Откроется диалоговое окно, в котором можно ввести что угодно для поиска в таблице. Чтобы избежать опечаток, можете скопировать искомое прямо из списка данных.

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

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

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

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

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

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

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

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

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

Как удалить дубликаты, но оставить самое низкое значение в другом столбце Excel?

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

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

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

2. Затем выберите значения из вставленного списка и щелкните Данные > Удалить дубликаты, и проверьте Продолжить текущий выбор вариант из появившегося диалогового окна. Нажмите Удалить дубликаты, то в Удалить дубликаты выберите столбец, из которого нужно удалить дубликаты. Смотрите скриншот:

3. Затем нажмите OK, и появится диалоговое окно с напоминанием о том, сколько дубликатов было удалено, нажмите OK закрыть его.

4. Выберите ячейку рядом со списком, из которого вы удалили дубликаты, введите эту формулу. =MIN(IF($A$2:$A$12=D2,$B$2:$B$12)), Нажмите Shift + Ctrl + Enter клавиши и перетащите маркер заполнения в нужную ячейку. Смотрите скриншот:

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

Примечание:

1. Если вы хотите удалить все дубликаты, но оставить самые высокие, вы можете применить эту формулу =MAX(IF($A$2:$A$12=D2,$B$2:$B$12)), не забудьте нажать Shift + Ctrl + Enter ключи.

2. В приведенных выше формулах A2: A12 — это исходный список, из которого нужно удалить дубликаты.

Удалите дубликаты, но сохраните наименьшее / наибольшее значение с помощью Kutools for Excel

Если у вас есть Kutools for Excel, вы можете быстро удалить дубликаты, но сохранить только самое низкое или самое высокое значение с помощью Расширенные ряды комбинирования функции.

Kutools for Excel, с более чем 300 удобные функции, облегчающие вашу работу.

1. Перед применением Kutools лучше иметь копию исходных данных. Затем выберите ячейки диапазона, из которых вы хотите удалить дубликаты, и нажмите Kutools > Слияние и разделение > Расширенные ряды комбинирования. Смотрите скриншот:

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

3. Нажмите Ok, то повторяющиеся значения были удалены, но оставлено самое низкое.

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

Метод 2: удаление повторений при помощи «умной таблицы»

Теперь внимательно разберем еще один полезный метод удаления дубликатов, который основывается на использовании «умной таблицы». Достаточно следовать указанным рекомендациям:

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

Выделяем нужный диапазон таблицы

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

Переходим в панель инструментов для работы со стилем таблицы

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

Проверяем и подтверждаем информацию по диапазону таблицы

1. Осталось только приступить к поиску и дальнейшему удалению дублированных строк. Чтобы сделать это, необходимо выполнить дополнительные действия:
   • поставьте курсор на произвольную ячейку таблицы;
   • в верхней панели инструментов нужно выбрать раздел «Конструктор таблиц»;
   • ищем значок в виде двух столбцов ячеек с разным цветом, при наведении на которые будет высвечиваться надпись «Удалить дубликаты»;
   • выполните действия, которые мы указали в первом методе после использования данного значка.

Удаляем найденные дубликаты

Обнаруживаем одинаковые ячейки при помощи встроенных фильтров Excel.

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

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

В первую очередь советую отформатировать наши данные как «умную» таблицу. Напомню: Меню Главная – Форматировать как таблицу.

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

Вы можете убрать галочку с пункта «Выделить все», а затем отметить один или несколько нужных элементов. Excel покажет только те строки, которые содержат выбранные значения. Так можно обнаружить дубликаты, если они есть. И все готово для их быстрого удаления.

Но при этом вы видите дубли только по отфильтрованному. Если данных много, то искать таким способом последовательного перебора будет несколько утомительно. Ведь слишком много раз нужно будет устанавливать и менять фильтр.

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

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

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

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

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

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

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

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

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

Поиск дубликатов при помощи встроенных фильтров Excel

Организовав данные в виде списка, Вы можете применять к ним различные фильтры. В зависимости от набора данных, который у Вас есть, Вы можете отфильтровать список по одному или нескольким столбцам. Поскольку я использую Office 2010, то мне достаточно выделить верхнюю строку, в которой находятся заголовки, затем перейти на вкладку Data (Данные) и нажать команду Filter (Фильтр). Возле каждого из заголовков появятся направленные вниз треугольные стрелки (иконки выпадающих меню), как на рисунке ниже.

Если нажать одну из этих стрелок, откроется выпадающее меню фильтра, которое содержит всю информацию по данному столбцу. Выберите любой элемент из этого списка, и Excel отобразит данные в соответствии с Вашим выбором. Это быстрый способ подвести итог или увидеть объём выбранных данных. Вы можете убрать галочку с пункта Select All (Выделить все), а затем выбрать один или несколько нужных элементов. Excel покажет только те строки, которые содержат выбранные Вами пункты. Так гораздо проще найти дубликаты, если они есть.

После настройки фильтра Вы можете удалить дубликаты строк, подвести промежуточные итоги или дополнительно отфильтровать данные по другому столбцу. Вы можете редактировать данные в таблице так, как Вам нужно. На примере ниже у меня выбраны элементы XP и XP Pro.

В результате работы фильтра, Excel отображает только те строки, в которых содержатся выбранные мной элементы (т.е. людей на чьём компьютере установлены XP и XP Pro). Можно выбрать любую другую комбинацию данных, а если нужно, то даже настроить фильтры сразу в нескольких столбцах.

Расширенный фильтр для поиска дубликатов в Excel

На вкладке Data (Данные) справа от команды Filter (Фильтр) есть кнопка для настроек фильтра – Advanced (Дополнительно). Этим инструментом пользоваться чуть сложнее, и его нужно немного настроить, прежде чем использовать. Ваши данные должны быть организованы так, как было описано ранее, т.е. как база данных.

Перед тем как использовать расширенный фильтр, Вы должны настроить для него критерий. Посмотрите на рисунок ниже, на нем виден список с данными, а справа в столбце L указан критерий. Я записал заголовок столбца и критерий под одним заголовком. На рисунке представлена таблица футбольных матчей. Требуется, чтобы она показывала только домашние встречи. Именно поэтому я скопировал заголовок столбца, в котором хочу выполнить фильтрацию, а ниже поместил критерий (H), который необходимо использовать.

Теперь, когда критерий настроен, выделяем любую ячейку наших данных и нажимаем команду Advanced (Дополнительно). Excel выберет весь список с данными и откроет вот такое диалоговое окно:

Как видите, Excel выделил всю таблицу и ждёт, когда мы укажем диапазон с критерием. Выберите в диалоговом окне поле Criteria Range (Диапазон условий), затем выделите мышью ячейки L1 и L2 (либо те, в которых находится Ваш критерий) и нажмите ОК. Таблица отобразит только те строки, где в столбце Home / Visitor стоит значение H, а остальные скроет. Таким образом, мы нашли дубликаты данных (по одному столбцу), показав только домашние встречи:

Это достаточно простой путь для нахождения дубликатов, который может помочь сохранить время и получить необходимую информацию достаточно быстро. Нужно помнить, что критерий должен быть размещён в ячейке отдельно от списка данных, чтобы Вы могли найти его и использовать. Вы можете изменить фильтр, изменив критерий (у меня он находится в ячейке L2). Кроме этого, Вы можете отключить фильтр, нажав кнопку Clear (Очистить) на вкладке Data (Данные) в группе Sort & Filter (Сортировка и фильтр).

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

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

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

Удалить дубликаты в Excel — Использование дополнительного фильтра для удаления дубликатов

1. В диалоговом окне «Расширенный фильтр
   » выполните следующие действия:

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

• Выберите Только уникальные записи

Удалить дубликаты в Excel — Фильтр дубликатов

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

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

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

.

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

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

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

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

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

Как применить сводную таблицу для поиска дубликатов.

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

Для более опытных – сразу переходим к сути вопроса.

Создаем новый макет сводной таблицы. А затем в качестве строк и значений используем одно и то же поле. В нашем случае – «Товар». Поскольку название товара – это текст, то для подсчета таких значений Excel по умолчанию использует функцию СЧЕТ, то есть подсчитывает количество. А нам это и нужно. Если будет больше 1, значит, имеются дубликаты.

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

К примеру, откуда взялись 3 дубликата Sprite? Щелкаем на цифре 3, и видим такую картину:

Думаю, этот метод вполне можно использовать. Что приятно – никаких формул не требуется.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Метод 3: использование фильтра

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

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

1. Первым делом следует выделить полностью таблицу, в которой вы собираетесь провести манипуляции по удалению дубликатов.
2. Теперь перейдите в раздел «Данные» и сразу перейдите в подраздел «Фильтр».

Выделяем диапазон таблицы и используем фильтр

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

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

Расширенный фильтр для поиска дубликатов в Excel

Имеется еще дополнительный способ использования фильтров в программе Excel, для этого вам понадобится:

1. Выполнить все действия прошлого метода.
2. В окне инструментария воспользоваться значком «Дополнительно», который находится около того самого фильтра.

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

После использования данного значка вам достаточно будет обратить внимание на окно дополнительных настроек. Этот расширенный инструментарий позволит ознакомиться с первоначальной информацией:
поначалу следует проверить указанный диапазон таблицы, чтобы он совпадал с тем, что вы отмечали;
обязательно отметьте пункт «Только уникальные записи»;
как только все будет готово, остается лишь нажать на кнопку «ОК».. Проверяем и подтверждаем установки фильтра

Проверяем и подтверждаем установки фильтра

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

Проверяем дополнительную информацию после фильтрации

Способ 3. Использование расширенного фильтра

Данный метод удаления дубликатов отличается простой реализации. Для его выполнения потребуется:

1. В разделе «Данные» возле кнопки «Фильтр» кликнуть по слову «Дополнительно». Откроется окно «Расширенный фильтр».

Путь к окну «Расширенный фильтр»

1. Поставить тумблер рядом со строкой «Скопировать результаты в другое место» и нажать на пиктограмму, расположенную около поля «Исходный диапазон».
2. Выделить мышкой диапазон ячеек, где требуется найти дубликаты. Окно выбора автоматически закроется.
3. Далее в строчке «Поместить результат в диапазон» также надо нажать ЛКМ по пиктограмме в конце и выделит любую ячейку вне таблицы. Это будет начальный элемент, в который вставится отредактированная табличка.

Манипуляции в меню «Расширенный фильтр»

1. Установить галочку в строке «Только уникальные записи» и кликнуть «ОК». В итоге рядом с исходным массивом появится отредактированная таблица без дубликатов.

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

Поиск и выделение дубликатов цветом

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

В одном столбце

Условное форматирование – это наиболее простой способ определить, где находятся дубликаты в Excel и выделить их. Что нужно сделать для этого?

1. Найти ту область поиска дубликатов и выделить ее.

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

3. Далее появляется окно, в котором нужно выбрать пункт «Повторяющиеся» и нажать на клавишу ОК.

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

В нескольких столбцах

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

Последовательность действий, в целом, следующая:

1. Выделить колонки, в которых будет осуществляться поиск дубликатов.
2. Развернуть вкладку «Главная». После этого находим пункт «Условное форматирование» и выставляем правило «Повторяющиеся значения» так, как это было описано выше. 

3. Далее снова выбираем пункт «Повторяющиеся» в появившемся окошке, а в списке справа выбираем цвет заливки. После этого кликаем по «ОК» и радуемся жизни.

Дубликаты строк

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

Посмотрите на эти две таблицы.

1718

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

Итак, что нужно сделать для поиска повторяющихся значений в рядах?

1. Создаем еще одну колонку в правой части по отношению к таблице с исходной информацией. В нем записывается формула, которая выводит объединенную информацию со всех ячеек, входящих в состав строки. =A2&B2&C2&D2

2. После этого мы увидим информацию, которая была объединена.

3. После этого следует выбрать дополнительную колонку (а именно, те ячейки, которые содержат объединенные данные).
4. Далее переходим на «Главная», а затем снова выбираем пункт «Повторяющиеся значения» аналогично описанному выше. 
5. Далее появится диалоговое окно, где снова выбираем пункт «Повторяющиеся», а в правом перечне находим цвет, с использованием которого будет осуществляться выделение. 

После того, как будет нажата кнопка «ОК», повторы будут обозначены тем цветом, который пользователь выбрал на предыдущем этапе.

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

1. Аналогично предыдущему примеру, делаем вспомогательную колонну, где записываем формулу объединения предыдущих столбцов. =A2&B2&C2&D2

2. Далее мы получаем все содержащиеся в строке значения, указанные в соответствующих ячейках каждой из строк.

3. После этого осуществляем выделение всей содержащиеся информации, не включая дополнительный столбец. В случае с нами это такой диапазон: A2:D15. После этого переходим на вкладку «Главная» и выбираем пункт «Условное форматирование» – создать правило (видим, что последовательность немного другая).

4. Далее нас интересует пункт «Использовать формулу для определения форматируемых ячеек», после чего вставляем в поле «Форматировать значения, для которых следующая формула является истинной», такую формулу. =СЧЁТЕСЛИ($E$2:$E$15;$E2)>1

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

23

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

Ниже на рисунке изображена таблица с дублирующими значениями. Строка 3 содержит тоже значение, что и строка 6. А значение строки 4 = строке 7. Ячейки с числами в дублирующихся строках имеют одинаковые значения и разные форматы. У них отличается количество разрядов после запятой. Выполним 2 разные операции для удаления дубликатов.

Устранение дубликатов на основе значений колонки с текстом:

1. Создайте умную таблицу (CTRL+T) с повторяющимися значениями как на рисунке:
2. Щелкните по таблице и выберите инструмент «Работа с таблицами»-«Конструктор»-«Удалить дубликаты» в разделе инструментов «Сервис».
3. В появившемся окне «Удалить дубликаты», следует отключить проверку по 4-му столбцу «Цена».

Строки 6 и 7 распознаны как дублирующие и удалены из таблицы. Если в пункте 2 не отключить проверку по столбцу ни одна строка не будет удалена, так как для Excel все числа в колонке «Цена» считаются разными.

В заключение

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

• Фильтр – идеально подходит, когда в данных присутствуют несколько категорий, которые, возможно, Вам понадобится разделить, просуммировать или удалить. Создание подразделов – самое лучшее применение для расширенного фильтра.
• Удаление дубликатов уменьшит объём данных до минимума. Я пользуюсь этим способом, когда мне нужно сделать список всех уникальных значений одного из столбцов, которые в дальнейшем использую для вертикального поиска с помощью функции ВПР.
• Я пользуюсь командой Find (Найти) только если нужно найти небольшое количество значений, а инструмент Find and Replace (Найти и заменить), когда нахожу ошибки и хочу разом исправить их.

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