Как найти все повторы в excel - Сайт, где вы сможете решить свои вопросы

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

Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Excel Starter 2010 Еще…Меньше

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

Выберите ячейки, которые нужно проверить на наличие повторений.

Примечание: В Excel не поддерживается выделение повторяющихся значений в области “Значения” отчета сводной таблицы.
На вкладке Главная выберите Условное форматирование > Правила выделения ячеек > Повторяющиеся значения.
В поле рядом с оператором значения с выберите форматирование для применения к повторяющимся значениям и нажмите кнопку ОК.

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

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

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

Например, на данном листе в столбце “Январь” содержатся сведения о ценах, которые нужно сохранить.

Поэтому флажок Январь в поле Удаление дубликатов нужно снять.
Нажмите кнопку ОК.

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

Дополнительные сведения

Нужна дополнительная помощь?

Нужны дополнительные параметры?

Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.

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

Источник

Спросите у SEO-шника без чего он, как без рук! Он наверняка ответит: без Excel! Эксель — лучший друг и помощник и для специалиста в SEO, и для вебмастера.

Одна из задач, которую тебе точно придётся решать при работе с большими массивами данных – это поиск дублей в Excel. Не вариант проверять тысячи ячеек руками – угробишь на это часы и выйдешь с работы, пошатываясь, будто пьяный. Я предложу тебе 2 способа, как выполнить эту работу в десяток раз быстрее. Они дают немного разные результаты, но в равной степени просты.

Оглавление

1 Как в Эксель найти повторяющиеся значения?
2 Как вычислить повторы при помощи сводных таблиц
3 Заключение

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

Для примера я распределил фамилии прославленных футболистов российской эпохи в пару столбцов. Нарочно сделал повторы в столбиках (иллюстрации кликабельны).

Наша цель – найти повторы в столбцах Excel и выделить их цветом.

Действуем так:

Шаг №1. Выделяем весь диапазон.

Шаг №2. Кликаем на раздел «Условное форматирование» в главной вкладке.

Шаг №3. Наводим на пункт «Правила выделения ячеек» и в появившемся списке выбираем «Повторяющиеся значения».

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

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

Описанный вариант — самый удобный для пользователей Эксель версий 2013 и 2016.

Как вычислить повторы при помощи сводных таблиц

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

Вернёмся к нашим баранам футболистам. Я оставил один столбик, добавив в него ячейки-дубли, а также дописал заглавную строку (это обязательно).

Далее делаем следующее:

Шаг 1. В ячейках напротив фамилий проставляем единички. Вот так:

Шаг 2. Переходим в раздел «Вставка» главного меню и в блоке «Таблицы» выбираем «Сводная таблица».

Откроется окно «Создание сводной таблицы». Здесь нужно выбрать диапазон данных для анализа (1), указать, куда поместить отчёт (2) и нажать «ОК».

Только не ставьте галку напротив «Добавить эти данные в модель данных». Иначе Эксель начнёт формировать модель, и это парализует ваш комп на пару минут минимум.

Шаг 3. Распределите поля сводной таблицы следующим образом: первое поле (в моём случае «Футболисты») – в область «Строки», второе («Значение2») – в область «Значения». Используйте обычное перетаскивание (drag-and-drop).

Должно получиться так:

А на листе сформируется сама сводка — уже без дублированных ячеек. Зато во втором столбике будет указано, сколько ячеек-дублей с конкретным содержанием было обнаружено в первом столбике (например, Онопко – 2 шт.).

Этот метод «на бумаге» может выглядеть несколько замороченным, но уверяю: попробуете раз-два, набьёте руку, а потом все операции будете выполнять за минуту.

Заключение

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

Хотя на самом деле функционал программы Эксель настолько широк, что можно не только подсветить повторяющиеся значения в столбике, но и автоматически их все удалить. Я знаю, как это делается, но сейчас вам не скажу. Теперь на сайте есть отдельная статья об удалении повторяющихся строк в Excel — там и смотрите 😉.

Помогли ли тебе мои методы работы с данными? Или ты знаешь лучше? Поделись своим мнением в комментариях!

Источник

Найти и выделить цветом дубликаты в Excel
Формула проверки наличия дублей в диапазонах
- Внутри диапазона
!SEMTools, поиск дублей внутри диапазона
- Найти дубли ячеек в столбце, кроме первого
- Найти в столбце дубли ячеек, включая первый
- Найти дубли в столбце без учета лишних пробелов

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

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

Ключевых моментов несколько:

Какие конкретно повторяющиеся значения — повторы слов в ячейках, сами повторяющиеся ячейки или повторяющиеся строки?
Если ячейки, то:
- Какие ячейки мы готовы считать дубликатами — все кроме первой или включая ее?
- Считаем ли дублями строки, отличающиеся только пробелами до/после слов или лишними пробелами между словами?
- Где мы будем искать дубли — в одном столбце, в двух столбцах или в нескольких?
- А может, нам нужно найти неявные дубли?

Сначала рассмотрим простые примеры.

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

Найти инструмент можно на вкладке программы “Главная”:

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

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

Процедура интуитивно понятна:

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

Второй шаг процедуры – выбор цветов заливки и текста

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

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

Условное форматирование меняет цвет дубликатов внутри столбца

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

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

Но есть и другие решения. О них дальше.

Формула проверки наличия дублей в диапазонах

Использование собственной формулы для проверки дубликатов в списке или диапазоне имеет ряд преимуществ, единственная задача — составление такой формулы. Но её я возьму на себя.

Внутри диапазона

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

=СУММПРОИЗВ(СЧЁТЕСЛИ(диапазон;тот-же-диапазон)-1)>0

Так выглядит на практике применение формулы:

Формула возвращает ИСТИНА, если в адресованном диапазоне появляется дубликат

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

А дело все в том, что формулу несложно видоизменить и улучшить.

Например, можно улучшить эффективность формулы, добавив в нее функцию СЖПРОБЕЛЫ .Это позволит находить дубликаты, отличающиеся незаметными лишними пробелами:

=СУММПРОИЗВ(--(СЖПРОБЕЛЫ(ячейка)=СЖПРОБЕЛЫ(диапазон)))>1

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

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

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

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

Обратите внимание на один момент в этой демонстрации: диапазон закреплен ($A$1:$B$4), а искомая ячейка (A1) нет. Именно это позволяет условному форматированию находить все дубликаты в диапазоне.

!SEMTools, поиск дублей внутри диапазона

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

Давайте покажу, как они работают.

Найти дубли ячеек в столбце, кроме первого

Процедура позволяет выделить все вторые, третьи и т.д. повторяющиеся значения в столбце.

Найти в столбце дубли ячеек, включая первый

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

Найти дубли в столбце без учета лишних пробелов

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

Для первой операции есть отдельный инструмент «Удалить лишние пробелы»:

Как найти дубли ячеек, не учитывая лишние пробелы

Найти повторяющиеся значения в Excel и решить сотни других задач поможет надстройка !SEMTools.

Скачайте прямо сейчас и убедитесь сами!

Смотрите также:

Удалить дубли без смещения строк;
Удалить неявные дубли;
Найти повторяющиеся слова в Excel;
Удалить повторяющиеся слова внутри ячеек.

Источник

На чтение 5 мин Опубликовано 12.01.2021

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

Содержание

Способ 1 Как проверить таблицу на наличие дубликатов и удалить их с помощью инструмента «Условное форматирование»
Способ 2. Поиск и удаление повторяющихся значений с помощью кнопки «Удалить дубликаты»
Способ 3. Использование расширенного фильтра
Способ 4. Применение сводных таблиц
Заключение

Способ 1 Как проверить таблицу на наличие дубликатов и удалить их с помощью инструмента «Условное форматирование»

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

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

Путь к активации условного форматирования в Excel. Порядок действий на одном скриншоте

В следующем меню в разделе «Выберите тип правила» потребуется указать на строчку «Использовать формулу для определения форматируемых ячеек».
Теперь в строке ввода, расположенной ниже данного подраздела, необходимо вручную с клавиатуры прописать формулу «=СЧЕТЕСЛИ($B$2:$B$9; B2)>1». Буквы в скобках указывают на диапазон ячеек, среди которых будет производиться форматирование и поиск дубликатов. В скобках необходимо прописать конкретный диапазон элементов таблицы и навесить на ячейки знаки долларов, чтобы формула не «съехала» в процессе форматирования.

Действия в окошке «Создание правила форматирования»

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

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

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

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

Способ 2. Поиск и удаление повторяющихся значений с помощью кнопки «Удалить дубликаты»

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

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

Путь к кнопке «Удалить дубликаты»

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

Необходимые действия в окне удаления дубликатов

На экране появится уведомление о найденных дубликатах. Они автоматически удалятся.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Заключение

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

Оцените качество статьи. Нам важно ваше мнение:

Источник

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

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