Как найти и убрать повторы в excel

Существует несколько способов как найти в Excel таблице повторяющиеся значения, строки или столбцы. Чтобы найти их, нужно воспользоваться 1им из 3ёх способов: формулой (функцией) Excel, расширенным фильтром (filter advanced) или условным форматированием (conditional formatting). Все эти 3 способа с объяснением как найти повторы в Excel и описаны в этой статье.

3 Способа для поиска повторов в Excel

  1. Функция СЧЁТЕСЛИ (COUNTIF) в Excel
  2. Расширенный фильтр (advanced filter) в Excel
  3. Условное форматирование (condifional formatting) в Excel

Далее рассмотрим каждый способ подробно с примером ниже. Функции и формулы использовать можно в разном сочетании, и то как можно использовать функцию СЧЁТЕСЛИ я уже объяснял в другой своей статье. Однако для удобства распишу здесь её конкретное применение.

Находим повторы в Excel с помощью функции СЧЁТЕСЛИ (COUNTIF)

Для этого способа берём таблицу с инвойсами ниже. Цель — найти повторяющиеся строки в колонке № Инвойса.

  • Добавляем в таблицу новую колонку «Ищем повторы инвойсов (по номеру инвойса)»
  • Добавляем функцию COUNTIF (СЧЁТЕСЛИ) в первую клетку новой колонки — D2:
    • после ввода функции, нажимаем на кнопку fx и заполняем Диапазон (Range) и Критерий (Criteria)
    • в поле Диапазон (Range) выбираем колонку «№ Инвойса» и фиксируем диапазон (добавление долларового знака $ перед каждой буквой и каждым числом диапазона ИЛИ нажатием F4). В данном случае, это $A$2:$A$8
    • в поле Критерий (Criteria) ставим первую клетку колонки «№ Инвойса», а именно A2
    • нажимаем ОК
  • Тянем функцию вниз до последней строки № 8. В результате мы видим, что формула выдаёт числа 1 для единичных строк (уникальных или недублированных). Все остальные значения выше единицы, например 3, означают сколько раз в таблице представлен номер инвойса напротив формулы. Значит в нашей таблице инвойс 360001 представлен 3 раза.

Используем функцию счётесли (countif) для поиска повторов

Удаляем повторы в Excel с помощью Расширенного фильтра (advanced filter)

Расширенный фильтр можно использовать для фильтрования таблицы используя несколько фильтров одновременно и для удаления дубликатов. Например, вы можете одним нажатием клавиши отфильтровать таблицу по заданному списку фильтров (например отфильтровать в таблице 5 колонок по 6 фильтрам).

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

  • отфильтровать в таблице значения, удалив все повторяющиеся строки
  • скопировать таблицу с только уникальными и неповторяющимися значениями в другое место (но только в том же листе)

Чтобы использовать расширенный фильтр, нужно:

  • Выделить таблицу.
  • Зайти на вкладку Данные (Data) и выбрать в графе Сортировка и Фильтр нажимаем Дополнительно (Advanced)
  • Далее выбираем пункты открывшегося меню «Расширенный фильтр»:
    • выбираем фильтровать на месте в таблице (Filter the list, in-place) или скопировать в новое место листа таблицу (Copy to another location)
    • можем заново выбрать таблицу (если выбрали не все строки например) (List range)
    • если выбрали копировать в новое место, то выбрать в какую ячейку (Copy to)
    • обязательно поставить галочку на последнем пункте Только уникальные записи (Unique records only)
  • после этого, нажав на ОК, мы получим мгновенный результат.

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

Окрашиваем повторы в Excel с помощью Условного форматирования (condifional formatting)

Условное форматирование — мощный и очень удобный визуальный инструмент Excel. Если уметь им пользоваться, то в зависимости от того, какие данные в таблице они будут подсвечены тем или цветом (какой вы сами выберете). Также в Excel 2010 возможны такие интересные дополнительные возможности Условного форматирования как отображение в одной колонке цветных гистограммных линий, в зависимости от того какое место значения в колонке (например ячейка с 75% прибыли буде иметь самое длинное линейное отображение).

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

  • Выбрать таблицу
  • Зайти в меню Условного форматирования
  • Выбрать пункт «Форматировать только уникальные значения или дубликаты»
  • Выбрать ниже Форматировать «дубликаты»
  • Выбрать формат: заполнение (fill) жёлтого цвета.
  • 2 раза нажимаем на ОК и Готово.

Создаём Правило Условного Форматирования

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

Ищем дубликаты и выбираем для них формат в Условном Форматировании

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

Для просмотра онлайн примеров всех выше-перечисленных способов, ниже представлено в окне онлайн-инструмента Microsoft в таблице ниже.

[embeddoc url=»http://www.tehnoslon.com.ua/Excel/povtory-excel.xlsx» download=»all» viewer=»microsoft»]

Скачать Excel с 4мя примерами можно по ссылке ниже:

Скачать

Добавить комментарий