Существует несколько способов как найти в Excel таблице повторяющиеся значения, строки или столбцы. Чтобы найти их, нужно воспользоваться 1им из 3ёх способов: формулой (функцией) Excel, расширенным фильтром (filter advanced) или условным форматированием (conditional formatting). Все эти 3 способа с объяснением как найти повторы в Excel и описаны в этой статье.
3 Способа для поиска повторов в Excel
- Функция СЧЁТЕСЛИ (COUNTIF) в Excel
- Расширенный фильтр (advanced filter) в Excel
- Условное форматирование (condifional formatting) в Excel
Далее рассмотрим каждый способ подробно с примером ниже. Функции и формулы использовать можно в разном сочетании, и то как можно использовать функцию СЧЁТЕСЛИ я уже объяснял в другой своей статье. Однако для удобства распишу здесь её конкретное применение.
Находим повторы в Excel с помощью функции СЧЁТЕСЛИ (COUNTIF)
Для этого способа берём таблицу с инвойсами ниже. Цель — найти повторяющиеся строки в колонке № Инвойса.
- Добавляем в таблицу новую колонку «Ищем повторы инвойсов (по номеру инвойса)»
- Добавляем функцию COUNTIF (СЧЁТЕСЛИ) в первую клетку новой колонки — D2:
- после ввода функции, нажимаем на кнопку fx и заполняем Диапазон (Range) и Критерий (Criteria)
- в поле Диапазон (Range) выбираем колонку «№ Инвойса» и фиксируем диапазон (добавление долларового знака $ перед каждой буквой и каждым числом диапазона ИЛИ нажатием F4). В данном случае, это $A$2:$A$8
- в поле Критерий (Criteria) ставим первую клетку колонки «№ Инвойса», а именно A2
- нажимаем ОК
- Тянем функцию вниз до последней строки № 8. В результате мы видим, что формула выдаёт числа 1 для единичных строк (уникальных или недублированных). Все остальные значения выше единицы, например 3, означают сколько раз в таблице представлен номер инвойса напротив формулы. Значит в нашей таблице инвойс 360001 представлен 3 раза.
Удаляем повторы в 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=»https://tehnoslon.com.ua/Excel/povtory-excel.xlsx» download=»all» viewer=»microsoft»]Скачать Excel с 4мя примерами можно по ссылке ниже: