Поиск дублей в excel

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

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

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

Условное форматирование – это наиболее простой способ определить, где находятся дубликаты в 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

Организовав данные в виде списка, Вы можете применять к ним различные фильтры. В зависимости от набора данных, который у Вас есть, Вы можете отфильтровать список по одному или нескольким столбцам. Поскольку я использую 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?

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

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

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

2. Нажмите Главная > Условное форматирование > Новое правило, см. снимок экрана:

3. В Новое правило форматирования диалоговое окно, выберите Используйте формулу, чтобы определить, какие ячейки следует форматировать. вариант под Выберите тип правила список, затем введите эту формулу = СЧЁТЕСЛИ ($ A $ 2: $ A2; A2)> 1 (A2 — первая ячейка того списка, к которому вы хотите применить условное форматирование, вы можете изменить его по своему усмотрению) в Формат значений, где эта формула истинна текстовое поле, см. снимок экрана:

4. Затем нажмите Формат кнопку, чтобы перейти к Формат ячеек диалоговое окно под Заполнять на вкладке укажите один цвет, который вы хотите отформатировать дубликаты, см. снимок экрана:

5. Затем нажмите OK > OK чтобы закрыть диалоговые окна, и все повторяющиеся значения в выбранном столбце были выделены, за исключением первой записи, см. снимок экрана:

Выделите повторяющиеся значения, кроме первого экземпляра, с помощью Kutools for Excel

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

Kutools for Excel : с более чем 300 удобными надстройками Excel, бесплатно и без ограничений в течение 30 дней.

Перейти к загрузкеБесплатная пробная версия 30 днейпокупкаPayPal / MyCommerce

После установки Kutools for Excel, пожалуйста, сделайте следующее:

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

2. Затем нажмите Kutools > Выберите > Выберите повторяющиеся и уникальные ячейки, см. снимок экрана:

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

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

Советы: если вы не проверяете Заполнить фон ни Цвет заливки шрифта параметры, он выберет только совпадающие результаты без какого-либо цвета.

Удалить дубликаты строк в 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
. И можете пользоваться одним из них в зависимости от вашей ситуации.

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

Способ 2: использование “умной таблицы”

Дубликаты можно удалить подобным способом, который имеет место быть в этой статье. На сей раз, в рассказе, как в Excel удалить повторяющиеся строки, будет использоваться “умная таблица”.

Для удаления повторов в Excel, вам нужно сделать следующее:

1. Как и в предыдущем способе, сначала выделить диапазон ячеек, где необходимо удалить дубликаты.
2. Нажать кнопку “Форматировать как таблицу”, что располагается на вкладке “Главная” в группе инструментов “Стили”.
3. Выбрать из выпадающего меню понравившейся стиль (любой).
4. В появившемся окне необходимо подтвердить указанный ранее диапазон ячеек, а если он не совпадает, то переназначить. Также установите галочку рядом с “Таблица с заголовками», если она такова, по итогу нажмите кнопку “ОК”.
5. “Умная таблица” создана, но это еще не все. Теперь вам необходимо выделить любую ячейку таблицы, чтобы во вкладках появился пункт “Конструктор” и непосредственно перейти в эту вкладку.
6. В ленте инструментов нужно нажать кнопку “Удалить дубликаты”.

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

Объединение и суммирование данных с помощью сводных таблиц

Сводная таблица — это швейцарский армейский нож для нарезки и нарезки данных в Excel.

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

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

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

Ниже приведены шаги по созданию сводной таблицы:

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

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

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

Ниже приведены шаги для этого:

• Щелкните в любом месте области сводной таблицы, и откроется панель сводной таблицы справа.
• Перетащите поле Country в область Row.
• Перетащите и поместите поле «Продажи» в область «Значения».

Вышеупомянутые шаги суммируют данные и дают вам сумму продаж по всем странам.

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

Это также поможет вам уменьшить размер вашей книги Excel.

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

Надеюсь, вы нашли этот урок полезным!

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

Обработка найденных дубликатов

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

Показать только повторяющиеся строки в столбце А

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

Кликните правой кнопкой мыши и в контекстном меню выберите Insert (Вставить):

Дайте названия столбцам, например, “Name” и “Duplicate?” Затем откройте вкладку Data (Данные) и нажмите Filter (Фильтр):

После этого нажмите меленькую серую стрелку рядом с “Duplicate?“, чтобы раскрыть меню фильтра; снимите галочки со всех элементов этого списка, кроме Duplicate, и нажмите ОК.

Вот и всё, теперь Вы видите только те элементы столбца А, которые дублируются в столбце В. В нашей учебной таблице таких ячеек всего две, но, как Вы понимаете, на практике их встретится намного больше.

Чтобы снова отобразить все строки столбца А, кликните символ фильтра в столбце В, который теперь выглядит как воронка с маленькой стрелочкой и выберите Select all (Выделить все). Либо Вы можете сделать то же самое через Ленту, нажав Data (Данные) > Select & Filter (Сортировка и фильтр) > Clear (Очистить), как показано на снимке экрана ниже:

Изменение цвета или выделение найденных дубликатов

Если пометки “Duplicate” не достаточно для Ваших целей, и Вы хотите отметить повторяющиеся ячейки другим цветом шрифта, заливки или каким-либо другим способом…

В этом случае отфильтруйте дубликаты, как показано выше, выделите все отфильтрованные ячейки и нажмите Ctrl+1, чтобы открыть диалоговое окно Format Cells (Формат ячеек). В качестве примера, давайте изменим цвет заливки ячеек в строках с дубликатами на ярко-жёлтый. Конечно, Вы можете изменить цвет заливки при помощи инструмента Fill (Цвет заливки) на вкладке Home (Главная), но преимущество диалогового окна Format Cells (Формат ячеек) в том, что можно настроить одновременно все параметры форматирования.

Теперь Вы точно не пропустите ни одной ячейки с дубликатами:

Удаление повторяющихся значений из первого столбца

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

Если 2 столбца, которые Вы сравниваете, находятся на разных листах, то есть в разных таблицах, кликните правой кнопкой мыши выделенный диапазон и в контекстном меню выберите Delete Row (Удалить строку):

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

Если 2 столбца расположены на одном листе, вплотную друг другу (смежные) или не вплотную друг к другу (не смежные), то процесс удаления дубликатов будет чуть сложнее. Мы не можем удалить всю строку с повторяющимися значениями, поскольку так мы удалим ячейки и из второго столбца тоже. Итак, чтобы оставить только уникальные записи в столбце А, сделайте следующее:

1. Отфильтруйте таблицу так, чтобы отображались только дублирующиеся значения, и выделите эти ячейки. Кликните по ним правой кнопкой мыши и в контекстном меню выберите Clear contents (Очистить содержимое).
2. Очистите фильтр.
3. Выделите все ячейки в столбце А, начиная с ячейки А1 вплоть до самой нижней, содержащей данные.
4. Откройте вкладку Data (Данные) и нажмите Sort A to Z (Сортировка от А до Я). В открывшемся диалоговом окне выберите пункт Continue with the current selection (Сортировать в пределах указанного выделения) и нажмите кнопку Sort (Сортировка):
5. Удалите столбец с формулой, он Вам больше не понадобится, с этого момента у Вас остались только уникальные значения.
6. Вот и всё, теперь столбец А содержит только уникальные данные, которых нет в столбце В:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Метод второй — расширенный фильтр.

Этот метод доступен уже и для версии Excel 2003, ниже не проверял, но насколько помню эта версия не сильно прогрессировала по сравнению с 2000й версией.

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

• Встаем на таблицу. Нажимаем Дополнительно в группе Сортировка и Фильтр на вкладке Данные
• Появляется окошко, где нам надо выбрать диапазон (который не надо выбирать, если ваша таблица не имеет разрывов и вам нужно удалить дубликаты не по отдельным столбцам) и отметить галочкой пункт «Только уникальные записи». Правда, надо еще определиться, где вы хотите видеть список без дубликатов — на отдельном листе или пусть список отфильтруется на месте. Во втором случае дубликаты не удалятся, а просто скроются, так что можно будет еще с ними поработать. Результат копирования результата в другое место практически аналогичен выше описанному методу с использованием инструмента Удалить дубликаты.
• После этого нажимаете «Ок» и ваши данные отфильтруются или скопируются, в зависимости от того, что вы выбрали. Я буду рассматривать случай фильтрации как видите, данные отфильтрованы — это можно видеть по синим номерам строк и нарушенной нумерации (после 19 идет 21, 20я строка скрыта).
• Теперь уникальные значения можно выделить цветом или забить в отдельном столбце какой-то признак (я ставлю единицу, так проще потом анализировать).
• Теперь выбираем команду «Очистить» и у вас сразу видно, где дубликаты — они или не отмечены никаким цветом либо у них пустые поля. Теперь можно с помощью автофильтра спокойно получить список дубликатов или уникальных значений.

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

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

6-минутное видео по статье:

В заключение

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

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

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