Повторяющиеся значения в Excel — находим и выделяем быстро

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

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

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

  Найти и выделить дубликаты в Excel цветом

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

  

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

  

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

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

  

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

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

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

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

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

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

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

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

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

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

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

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

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

  Обратите внимание на пару моментов этой демонстрации:

  Быстрый поиск дубликатов с !SEMTools

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

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

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

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

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

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

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

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

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

  

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

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

  

  Мнение эксперта

  Знайка, самый умный эксперт в Цветочном городе

  Если у вас есть вопросы, задавайте их мне!

  Задать вопрос эксперту

  На панели инструментов быстрого доступа рядом с кнопкой отмены также есть кнопка повтора; ее значок представляет собой стрелку, направленную вправо. Если же вы хотите что-то уточнить, я с радостью помогу!
  Допустим, вы пытаетесь отменить некоторые недавние изменения форматирования, но случайно зашли на один шаг слишком далеко. Вместо того чтобы проходить все необходимые шаги форматирования, чтобы вернуть все обратно, при выборе пункта «Повторить» стек продвинется на один шаг вперед и вернет последнее изменение формата.
  

  Как использовать отмену, повтор и повторение в Excel

  • При работе в A1-адресации явным указателем признака абсолютной адресации является символ «$» (доллар) ввод с клавиатуры «$a$1:$a$1000».
  • При работе в R1C1-адресации абсолютные ссылки указываются без использования квадратных скобок – ввод с клавиатуры «R1C1:R1C1000».
  • Выделение диапазона, а затем последовательное нажатие клавиши F4 для подбора нужного типа адресации. В примере адрес будет меняться следующим образом:
  1. $A$1:$A$1000
  2. A$1:A$1000
  3. $A1:$A1000
  4. A1:A1000 (исходное состояние)

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

  XLS	XLSX
  Длина формулы	1000	8000
  Уровни вложенности	7	64

  Редактирование формул
  Вы можете повторить только 100 последних действий по отмене. Вы не можете повторить что-либо, если это действие не было затронуто действием отмены. Например, поскольку нельзя отменить удаление рабочего листа, повторное действие не может вносить изменения во вкладки рабочего листа.
  
  Мнение эксперта
  Знайка, самый умный эксперт в Цветочном городе
  Если у вас есть вопросы, задавайте их мне!
  Задать вопрос эксперту
  Например, можно улучшить эффективность формулы, добавив в нее функцию СЖПРОБЕЛЫ это позволит находить дубликаты, отличающиеся незаметными лишними пробелами. Если же вы хотите что-то уточнить, я с радостью помогу!
  Выбирайте максимально контрастные комбинации цвета и фона. Хорошо выглядит на проекторе тёмный фон и светлые буквы. Самое ужасное впечатление производит красный на чёрном и наоборот. Это сочетание крайне неконтрастно выглядит на проекторе — избегайте его.

  Как повторить действие в excel

  • Длина текстовой строки ограничена 32767 символами. Если будет больше, то формула сообщит об ошибке.
  • Если number_times (число_повторений) будет дробным, оно будет отсечено до целого.
  • Если number_times (число_повторений) будет равно 0, результатом окажется пустая строка.

  Еще одним способом является смешанный тип ввода, когда сначала выделяется начало диапазона при помощи мыши, а правильный размер вводитсья с клавиатуры. В предыдущем примере, можно выделить A1:A10 (десять видимых ячеек), потом сразу же ввести с клавиатуры «00» — получится искомый диапазон A1:A1000. Выражение будет работать верно, но скроллинг при помощи мыши при этом не требуется.
  

  Функция «ЕСЛИ» в Excel: пошаговая инструкция по работе
  Макрорекодер — это инструмент в Excel, который пошагово записывает все что вы выполняете в Excel и преобразует это в код на языке VBA. Макрорекодер создает очень подробный код (как мы увидим позже), который вы сможете при необходимости отредактировать в дальнейшем.
  
  Мнение эксперта
  Знайка, самый умный эксперт в Цветочном городе
  Если у вас есть вопросы, задавайте их мне!
  Задать вопрос эксперту
  Например, если вы наложили красный текст на одну ячейку, вы можете щелкнуть другую ячейку или даже несколько ячеек и повторить тот же стиль форматирования в этих ячейках. Если же вы хотите что-то уточнить, я с радостью помогу!
  Выполнить повтор можно с помощью сочетания клавиш Ctrl+Y в Windows или Command+Y на Mac. Как и действие отмены, повтор можно выполнять несколько раз, используя одну и ту же комбинацию клавиш снова и снова.

  Разнесение информации по разным листам книги «для удобства»

  Функция СУММЕСЛИМН позволяет суммировать значения по определенным условиям. Условий может быть несколько. В Excel также есть функция СУММЕСЛИ, которая позволяет суммировать по одному критерию. Призываю вас использовать более универсальную формулу.

  Технические характеристики → Полезное видео → Общие данные → Таблица размеров → Полезные советы → Как правильно рассчитать → Сыпучие продукты → Натяжные потолки и технологии→ Мера жидких продуктов