Newcomposers.ru

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

Как найти дубликаты в таблице excel

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

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

Поиск и удаление

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

Способ 1: простое удаление повторяющихся строк

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

  1. Выделяем весь табличный диапазон. Переходим во вкладку «Данные». Жмем на кнопку «Удалить дубликаты». Она располагается на ленте в блоке инструментов «Работа с данными».

Открывается окно удаление дубликатов. Если у вас таблица с шапкой (а в подавляющем большинстве всегда так и есть), то около параметра «Мои данные содержат заголовки» должна стоять галочка. В основном поле окна расположен список столбцов, по которым будет проводиться проверка. Строка будет считаться дублем только в случае, если данные всех столбцов, выделенных галочкой, совпадут. То есть, если вы снимете галочку с названия какого-то столбца, то тем самым расширяете вероятность признания записи повторной. После того, как все требуемые настройки произведены, жмем на кнопку «OK».

  • Excel выполняет процедуру поиска и удаления дубликатов. После её завершения появляется информационное окно, в котором сообщается, сколько повторных значений было удалено и количество оставшихся уникальных записей. Чтобы закрыть данное окно, жмем кнопку «OK».
  • Способ 2: удаление дубликатов в «умной таблице»

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

      Выделяем весь табличный диапазон.

    Находясь во вкладке «Главная» жмем на кнопку «Форматировать как таблицу», расположенную на ленте в блоке инструментов «Стили». В появившемся списке выбираем любой понравившийся стиль.

    Затем открывается небольшое окошко, в котором нужно подтвердить выбранный диапазон для формирования «умной таблицы». Если вы выделили все правильно, то можно подтверждать, если допустили ошибку, то в этом окне следует исправить. Важно также обратить внимание на то, чтобы около параметра «Таблица с заголовками» стояла галочка. Если её нет, то следует поставить. После того, как все настройки завершены, жмите на кнопку «OK». «Умная таблица» создана.

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

    Способ 3: применение сортировки

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

      Выделяем таблицу. Переходим во вкладку «Данные». Жмем на кнопку «Фильтр», расположенную в блоке настроек «Сортировка и фильтр».

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

  • Открывается окно расширенного фильтра. Устанавливаем в нем галочку напротив параметра «Только уникальные записи». Все остальные настройки оставляем по умолчанию. После этого кликаем по кнопке «OK».
  • После этого, повторяющиеся записи будут скрыты. Но их показ можно в любой момент включить повторным нажатием на кнопку «Фильтр».

    Способ 4: условное форматирование

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

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

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

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

    Способ 5: применение формулы

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

    =ЕСЛИОШИБКА(ИНДЕКС(адрес_столбца;ПОИСКПОЗ(0;СЧЁТЕСЛИ(адрес_шапки_столбца_дубликатов: адрес_шапки_столбца_дубликатов (абсолютный); адрес_столбца;)+ЕСЛИ(СЧЁТЕСЛИ(адрес_столбца;; адрес_столбца;)>1;0;1);0));»»)

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

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

  • Выделяем весь столбец для дубликатов, кроме шапки. Устанавливаем курсор в конец строки формул. Нажимаем на клавиатуре кнопку F2. Затем набираем комбинацию клавиш Ctrl+Shift+Enter. Это обусловлено особенностями применения формул к массивам.
  • После этих действий в столбце «Дубликаты» отобразятся повторяющиеся значения.

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

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

    Отблагодарите автора, поделитесь статьей в социальных сетях.

    Поиск и удаление дубликатов в Excel: 5 методов

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

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

    Метод 1: удаление дублирующихся строк вручную

    Первый метод максимально прост и предполагает удаление дублированных строк при помощи специального инструмента на ленте вкладки “Данные”.

    1. Полностью выделяем все ячейки таблицы с данными, воспользовавшись, например, зажатой левой кнопкой мыши.
    2. Во вкладке “Данные” в разделе инструментов “Работа с данными” находим кнопку “Удалить дубликаты” и кликаем на нее.
    3. Переходим к настройкам параметров удаления дубликатов:
      • Если обрабатываемая таблица содержит шапку, то проверяем пункт “Мои данные содержат заголовки” – он должен быть отмечен галочкой.
      • Ниже, в основном окне, перечислены названия столбцов, по которым будет осуществляться поиск дубликатов. Система считает совпадением ситуацию, в которой в строках повторяются значения всех выбранных в настройке столбцов. Если убрать часть столбцов из сравнения, повышается вероятность увеличения количества похожих строк.
      • Тщательно все проверяем и нажимаем ОК.
    4. Далее программа Эксель в автоматическом режиме найдет и удалит все дублированные строки.
    5. По окончании процедуры на экране появится соответствующее сообщение с информацией о количестве найденных и удаленных дубликатов, а также о количестве оставшихся уникальных строк. Для закрытия окна и завершения работы данной функции нажимаем кнопку OK.

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

    Еще один способ удаления повторяющихся строк – использование “умной таблицы“. Давайте рассмотрим алгоритм пошагово.

    Читать еще:  Excel как скопировать без скрытых строк

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

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

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

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

    1. Как обычно, выделяем все ячейки таблицы.
    2. Во вкладке “Данные” в разделе инструментов “Сортировка и фильтр” ищем кнопку “Фильтр” (иконка напоминает воронку) и кликаем на нее.
    3. После этого в строке с названиями столбцов таблицы появятся значки перевернутых треугольников (это значит, что фильтр включен). Чтобы перейти к расширенным настройкам, жмем кнопку “Дополнительно“, расположенную справа от кнопки “Фильтр“.
    4. В появившемся окне с расширенными настройками:
      • как и в предыдущем способе, проверяем адрес диапазон ячеек таблицы;
      • отмечаем галочкой пункт “Только уникальные записи“;
      • жмем ОК.
    5. После этого все задвоенные данные перестанут отображаться в таблицей. Чтобы вернуться в стандартный режим, достаточно снова нажать на кнопку “Фильтр” во вкладке “Данные”.

    Метод 4: условное форматирование

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

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

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

    Метод 5: формула для удаления повторяющихся строк

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

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

    Давайте посмотрим, как с ней работать на примере нашей таблицы:

    1. Добавляем в конце таблицы новый столбец, специально предназначенный для отображения повторяющихся значений (дубликаты).
    2. В верхнюю ячейку нового столбца (не считая шапки) вводим формулу, которая для данного конкретного примера будет иметь вид ниже, и жмем Enter:
      =ЕСЛИОШИБКА(ИНДЕКС(A2:A90;ПОИСКПОЗ(0;СЧЁТЕСЛИ(E1:$E$1;A2:A90)+ЕСЛИ(СЧЁТЕСЛИ(A2:A90;A2:A90)>1;0;1);0));»») .
    3. Выделяем до конца новый столбец для задвоенных данных, шапку при этом не трогаем. Далее действуем строго по инструкции:
      • ставим курсор в конец строки формул (нужно убедиться, что это, действительно, конец строки, так как в некоторых случаях длинная формула не помещается в пределах одной строки);
      • жмем служебную клавишу F2 на клавиатуре;
      • затем нажимаем сочетание клавиш Ctrl+SHIFT+Enter.
    4. Эти действия позволяют корректно заполнить формулой, содержащей ссылки на массивы, все ячейки столбца. Проверяем результат.

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

    Заключение

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

    Читать еще:  Как выровнять текст в excel

    Как в Excel найти повторяющиеся и одинаковые значения

    При совместной работе с таблицами Excel или большом числе записей накапливаются дубли строк. Ста.

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

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

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

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

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

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

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

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

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

    Ищем в таблицах Excel все повторяющиеся значения

    Отметим все неуникальные записи в выделенной области. Для этого нужно:

    • Зайти в группу «Стили».
    • Далее «Условное форматирование».
    • Теперь в выпадающем меню выбрать «Правила выделения ячеек».
    • Затем «Повторяющиеся значения».

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

    • Нажать «ОК».

    Программа ищет повторения во всех столбцах.

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

    Удаление одинаковых значений из таблицы Excel

    Способ удаления неуникальных записей:

    1. Зайти во вкладку «Данные».
    2. Выделить столбец, в котором следует искать дублирующиеся строки.
    3. Опция «Удалить дубликаты».

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

    Список с уникальными значениями:

    Расширенный фильтр: оставляем только уникальные записи

    Расширенный фильтр – это инструмент для получения упорядоченного списка с уникальными записями.

    • Выбрать вкладку «Данные».
    • Перейти в раздел «Сортировка и фильтр».
    • Нажать команду «Дополнительно»:

    • В появившемся диалоговом окне ставим флажок «Только уникальные записи».
    • Нажать «OK» – уникальный список готов.

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

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

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

    В диалоговом окне выбрать размещение сводной таблицы на новом листе.

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

    Получаем упорядоченный список уникальных строк.

    3 способа как находить дубликаты в Excel

    Поиск дубликатов в 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

    Встроенный инструмент для удаления дубликатов в Excel

    В Excel есть встроенная функция Remove Duplicates (Удалить дубликаты). Вы можете выбрать столбец с данными и при помощи этой команды удалить все дубликаты, оставив только уникальные значения. Воспользоваться инструментом Remove Duplicates (Удалить дубликаты) можно при помощи одноименной кнопки, которую Вы найдёте на вкладке Data (Данные).

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

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

    Поиск дубликатов при помощи команды Найти

    Если Вам нужно найти в Excel небольшое количество дублирующихся значений, Вы можете сделать это при помощи поиска. Зайдите на вкладку Home (Главная) и кликните Find & Select (Найти и выделить). Откроется диалоговое окно, в котором можно ввести любое значение для поиска в Вашей таблице. Чтобы избежать опечаток, Вы можете скопировать значение прямо из списка данных.

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

    Если нужно выполнить поиск по всем имеющимся данным, возможно, кнопка Find All (Найти все) окажется для Вас более полезной.

    В заключение

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

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

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

    Exceltip

    Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

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

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

    1. Удаление повторяющихся значений в Excel (2007+)

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

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

    Щелкаем ОК, диалоговое окно будет закрыто и строки, содержащие дубликаты будут удалены.

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

    2. Использование расширенного фильтра для удаления дубликатов

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

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

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

    3. Выделение повторяющихся значений с помощью условного форматирования в Excel (2007+)

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

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

    4. Использование сводных таблиц для определения повторяющихся значений

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

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

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