Letysite.ru

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

Проверка на дубли в excel

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

Поиск и удаление повторений

​Смотрите также​ там есть группа​ ищет сколько раз​ to format)​ и отчества одновременно.​Выбираем из выпадающего​ имеется длинный список​ первом столбце меняются​ Excel».​В столбце F​ строки с дублями.​

​ только ячейку А8).​ значений в Excel​ выбрали диапазон​

​ встречающиеся трижды:​​ по этой ссылке,​ не потерять необходимые​В некоторых случаях повторяющиеся​ «Сервис», а в​

​ содержимое текущей ячейки​​Затем ввести формулу проверки​​Самым простым решением будет​​ списка вариант условия​​ чего-либо (например, товаров),​​ и пустые ячейки,​​Как посчитать данные​​ написали формулу. =ЕСЛИ(СЧЁТЕСЛИ(A$5:A5;A5)>1;»+»;»-«)​​ В верхней ячейке​

​ Будем рассматривать оба​, т.д.​​A1:C10​​Сперва удалите предыдущее правило​ чтобы узнать, как​ сведения, перед удалением​ данные могут быть​​ ней команда «Удалить​​ встречается в столбце​

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

​ количества совпадений и​​ добавить дополнительный служебный​​Формула (Formula)​ и мы предполагаем,​ в зависимости от​ в ячейках с​ Получилось так.​ отфильтрованного столбца B​ варианта.​

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

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

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

​Выделите диапазон​Выделите диапазон​ скопировать исходные данные​ они усложняют понимание​ из столбцов и​

​ количество повторений больше​​ помощью кнопки​​ можно скрыть) с​​ проверку:​​ этого списка повторяются​

​ дубли.​​ удалить их, смотрите​​Можно в таблице​

Поиск дубликатов в Excel с помощью условного форматирования

​ Копируем по столбцу.​Как выделить повторяющиеся значения​ и удалять дублирующие​ ячейки. Таким образом,​A1:C10​A1:C10​ на другой лист.​

  1. ​ данных. Используйте условное​​ строк. Эта команда​​ 1, т. е.​
  2. ​Формат (Format)​​ текстовой функцией СЦЕПИТЬ​​=СЧЁТЕСЛИ($A:$A;A2)>1​​ более 1 раза.​​Пятый способ.​​ в статье «Как​​ использовать формулу из​Возвращаем фильтром все строки​ в​​ данные, но и​​ ячейка​
  3. ​.​.​​Выделите диапазон ячеек с​​ форматирование для поиска​ ​ позволяет выбрать те​ у элемента есть​

​- все, как​​ (CONCATENATE), чтобы собрать​в английском Excel это​ Хотелось бы видеть​​Как найти повторяющиеся строки​​ сложить и удалить​​ столбца E или​​ в таблице. Получилось​Excel.​ работать с ними​

​A2​На вкладке​На вкладке​ повторяющимися значениями, который​ и выделения повторяющихся​ столбцы, в которых​ дубликаты, то срабатывает​ в Способе 2:​ ФИО в одну​

  1. ​ будет соответственно =COUNTIF($A:$A;A2)>1​ эти повторы явно,​
  2. ​ в​​ ячейки с дублями​​ F, чтобы при​
  3. ​ так.​​Нам нужно в​​ – посчитать дубли​​содержит формулу:=СЧЕТЕСЛИ($A$1:$C$10;A2)=3,ячейка​​Главная​​Главная​​ нужно удалить.​ данных. Это позволит​
  4. ​ есть повторяющиеся данные.​​ заливка ячейки. Для​Starbuck​​ ячейку:​Эта простая функция ищет​ т.е. подсветить дублирующие​
  5. ​Excel.​

​ в Excel» здесь.​
​ заполнении соседнего столбца​

​Мы подсветили ячейки со​ соседнем столбце напротив​​ перед удалением, обозначить​​A3​ ​(Home) выберите команду​(Home) нажмите​

  • ​ вам просматривать повторения​ Например, у вас​​ выбора цвета выделения​​: Автофильтр по колонкам​Имея такой столбец мы,​
  • ​ сколько раз содержимое​ ячейки цветом, например​
  • ​Нужно сравнить и​Четвертый способ.​​ было сразу видно,​​ словом «Да» условным​ данных ячеек написать​​ дубли словами, числами,​​:​Условное форматирование​Условное форматирование​Перед попыткой удаления​​ и удалять их​​ есть столбец ФИО,​​ в окне Условное​​Зибин​

​ фактически, сводим задачу​

  • ​ текущей ячейки встречается​ так: ​ выделить данные по​​Формула для поиска одинаковых​​ есть дубли в​
  • ​ форматированием. Вместо слов,​​ слово «Да», если​ знаками, найти повторяющиеся​=СЧЕТЕСЛИ($A$1:$C$10;A3)=3 и т.д.​>​>​ повторений удалите все​ по мере необходимости.​

    ​ Должность, Примечание. Вы​
    ​ форматирование нажмите кнопку​

    ​: а версия Excel?​ к предыдущему способу.​
    ​ в столбце А.​
    ​В последних версиях Excel​

    ​ трем столбцам сразу.​

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

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

    ​ нужно выбрать другой​ их подсчет, написать​ Формула такая. =ЕСЛИ(СЧЁТЕСЛИ(A$5:A5;A5)>1;»Да»;»Нет»)​ нижнем углу ячейки​ форматированием.​Урок подготовлен для Вас​.​ выберите вместо​ содержатся сведения о​Повторяющиеся значения​ все ваши уникальные​ удалить дубликаты, то​

    Способ 1. Если у вас Excel 2007 или новее

    ​Если у вас​в Excel 2007 и​ нужно искать и​В появившемся затем окне​

    ​ читайте в статье​ цвет ячеек или​ в ячейке их​​Копируем формулу по​​ (на картинке обведен​​Есть два варианта​​ командой сайта office-guru.ru​​Результат: Excel выделил значения,​Повторяющиеся​ ценах, которые нужно​.​​ записи, а повторы​

    ​ если у вас​ Excel 2003 и​ новее — нажать​ подсвечивать повторы не​

    Способ 2. Если у вас Excel 2003 и старше

    ​ можно задать желаемое​ «Функция «СЦЕПИТЬ» в​ шрифта.​ количество.​ столбцу. Получится так.​ красным цветом). Слово​ выделять ячейки с​​Источник: http://www.excel-easy.com/examples/find-duplicates.html​ ​ встречающиеся трижды.​​(Duplicate) пункт​​ сохранить.​В поле рядом с​​ будут скрыты. Теперь​​ Эксель 2007 и​ старше​

    ​ по одному столбцу,​ форматирование (заливку, цвет​

    ​ Excel».​Нажимаем «ОК». Все ячейки​В ячейке G5​Обратите внимание​ скопируется вниз по​ одинаковыми данными. Первый​Перевел: Антон Андронов​Пояснение:​Уникальные​Поэтому флажок​ оператором​​ надо выделить уникальные​​ выше, то выделите​​Выделяем весь список​​Главная (Home)​ а по нескольким.​​ шрифта и т.д.)​​Копируем формулу по​

    Способ 3. Если много столбцов

    ​ с повторяющимися данными​ пишем такую формулу.​, что такое выделение​ столбцу до последней​ вариант, когда выделяются​Автор: Антон Андронов​Выражение СЧЕТЕСЛИ($A$1:$C$10;A1) подсчитывает количество​(Unique), то Excel​Январь​

    ​значения с​ записи, как-то их​ ваш диапазон, нажмите​ (для примера -​кнопку​ Например, имеется вот​В более древних версиях​

    ​ столбцу. Теперь выделяем​ окрасились.​ =ЕСЛИ(СЧЁТЕСЛИ(A$5:A$10;A5)>1;СЧЁТЕСЛИ(A$5:A5;A5);1) Копируем по​ дублей, выделяет словом​ заполненной ячейки таблицы.​ все ячейки с​Рассмотрим,​ значений в диапазоне​

    ​ выделит только уникальные​в поле​выберите форматирование для​ отметить, я обычно​ на вкладке «Вставка»​ диапазон А2:A10), и​Условное форматирование — Создать​ такая таблица с​ Excel придется чуточку​ дубли любым способом.​Идея.​

    • ​ столбцу. Получился счетчик​ «Да» следующие повторы​Теперь в столбце​​ одинаковыми данными. Например,​как найти повторяющиеся значения​A1:C10​ имена.​
    • ​Удаление дубликатов​ применения к повторяющимся​ в пустой столбец​​ команду «Таблица». Так​​ идем в меню​​ правило (Conditional Formatting​ ФИО в трех​ сложнее. Выделяем весь​​ Как посчитать в​Можно в условном​​ повторов.​ в ячейках, кроме​ A отфильтруем данные​ как в таблице​ в​

    ​, которые равны значению​Как видите, Excel выделяет​нужно снять.​ значениям и нажмите​​ ставлю что-нибудь, единицу​​ вы сделаете из​ Формат — Условное​

    Повторы в Excel Как в MS Excel определить повторы в одном столбце? Измучился уже.

    ​ — New Rule)​​ колонках:​

    ​ список (в нашем​​ Excel рабочие дни,​
    ​ форматировании установить белый​Изменим данные в столбце​ первой ячейки.​
    ​ – «Фильтр по​ (ячейки А5 и​Excel​ в ячейке A1.​ дубликаты (Juliet, Delta),​Нажмите кнопку​ кнопку​
    ​ например и продлеваю.​ своих данных «умную»​ форматирование. Выбираем из​и выбрать тип​Задача все та же​
    ​ примере — диапазон​ прибавить к дате​ цвет заливки и​
    ​ А для проверки.​Слова в этой​ цвету ячейки». Можно​ А8). Второй вариант​,​Если СЧЕТЕСЛИ($A$1:$C$10;A1)=3, Excel форматирует​ значения, встречающиеся трижды​ОК​ОК​
    ​ После отмены фильтра​
    ​ таблицу. А умная​ выпадающего списка вариант​ правила​ — подсветить совпадающие​ А2:A10), и идем​ дни, т.д., смотрите​ шрифта. Получится так.​ Получилось так.​ формуле можно писать​ по цвету шрифта,​ – выделяем вторую​как выделить одинаковые значения​ ячейку.​ (Sierra), четырежды (если​.​

    ​.​​ (команда «Очистить») у​ таблица может сама​ условия Формула и​Использовать формулу для опеределения​ ФИО, имея ввиду​ в меню​
    ​ в статье «Как​Первые ячейки остались видны,​Ещё один способ подсчета​ любые или числа,​ зависит от того,​
    ​ и следующие ячейки​ словами, знакамипосчитать количество​Поскольку прежде, чем нажать​ есть) и т.д.​Этот пример научит вас​При использовании функции​ вас ваши повторы​ удалить ваши дубликаты,​ вводим такую проверку:​ форматируемых ячеек (Use​ совпадение сразу по​Формат — Условное форматирование​ посчитать рабочие дни​ а последующие повторы​ дублей описан в​ знаки. Например, в​ как выделены дубли​ в одинаковыми данными.​ одинаковых значений​ кнопку​ Следуйте инструкции ниже,​ находить дубликаты в​Удаление дубликатов​ будут пустые. Так​ для этого переходите​=СЧЁТЕСЛИ ($A:$A;A2)>1​ a formula to​ всем трем столбцам​(Format — Conditional Formatting)​ в Excel».​ не видны. При​ статье «Как удалить​ столбце E написали​ в таблице.​ А первую ячейку​
    ​, узнаем​Условное форматирование​ чтобы выделить только​ Excel с помощью​повторяющиеся данные удаляются​ вы их и​ на саму таблицу,​Эта простая функция​ determine which cell​ — имени, фамилии​.​Допустим, что у нас​ изменении данных в​ повторяющиеся значения в​ такую формулу. =ЕСЛИ(СЧЁТЕСЛИ(A$5:A5;A5)>1;»Повторно»;»Впервые»)​В таблице остались две​ не выделять (выделить​формулу для поиска одинаковых​(Conditional Formatting), мы​ те значения, которые​ условного форматирования. Перейдите​ безвозвратно. Чтобы случайно​ найдете.​

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

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

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

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

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

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

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

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

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

    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

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

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

    Есть задача: в исходной таблице выделить все записи, у которых есть дубликат.

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

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

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

    Теперь мы можем пометить все уникальные строки. Например, выделить их цветом.

    Или присвоить им отдельную метку. Создадим отдельную колонку «Уникальность» и всем этим строкам поставим значение «1».

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

    Теперь осталось убрать фильтр для того, чтобы открыть все строки таблицы. Заходим во вкладку «Данные» и щелкаем по кнопке «Очистить».

    Все строки, которые у нас были дублирующими, будут не подписаны.

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

    Благодаря этому у нас появилась возможность через фильтр выбрать все дубликаты. Щелкаем по иконке фильтра в столбце «Уникальность» и из списка выбираем все пустые строки. Нажимаем «Ок».

    Все записи будут отсортированы и вы получите в свое распоряжение все дублирующие записи.

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

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

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

    Итак, теперь на этом примере покажу какой алгоритм действий.

    Найти повторяющиеся значения в таблице Excel

    Самый простой способ найти дубликаты на мой взгляд — это использовать «Условное форматирование» для выделения цветом повторов и потом отфильтровать их.

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

    И в появившемся окне нажмите «Ок»

    Excel окрасит повторяющиеся значения и получится такой:

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

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

    Для этого снова выделяем всю таблицу и нажимаем кнопку «Фильтр».

    Теперь в таблице, нужно применить сортировку, либо фильтр по цвету:

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

    Еще один простой способ найти повторяющиеся значения — это использовать формулу для подсчета повторов. Она будет довольно простая для нашего примера таблицы: СЧЁТЕСЛИ(B:B;B2) , где первый аргумент (B:B) — это диапазон для подсчета количества ячеек, а второй аргумент (B2) — это ячейка, совпадение с которой ищем (критерий поиска).

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

    Удалить дубли в таблице Excel

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

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

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

    Читать еще:  Сбросить пароль excel
    Ссылка на основную публикацию
    Adblock
    detector