Дубликаты в Excel: как найти, выделить или удалить

Когда необходимо отрезать, удалить строго определенное количество символов в тексте справа или слева. Существует система из двух функций…

Как подсветить дубликаты, используя встроенный инструмент (с 1-м появлением)

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

  1. Выберите данные, которые вы хотите проверить. Это может быть колонка, строка или диапазон ячеек.
  2. На вкладке « Главная » выберите « Условное форматирование» > «Правила выделения ячеек» > «Повторяющиеся значения».

Откроется диалоговое окно с форматом «Светло-красная заливка» и «Темно-красный текст», выбранными по умолчанию. Чтобы применить формат по умолчанию, просто нажмите ОК.

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

И вот что мы получили в итоге:

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

При использовании этого инструмента имейте в виду следующие две вещи:

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

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

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Извлечение первого слова из текста

Для того, чтобы выполнить эту задачу можно при помощи формулы:

=ЛЕВСИМВ(A2;НАЙТИ(«*»;ПОДСТАВИТЬ(A2;» «;»*»;1)))

Извлечение первого слова из ячейки Excel

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

Пример для трех.

=ЛЕВСИМВ(A2;НАЙТИ(«*»;ПОДСТАВИТЬ(A2;» «;»*»;3)))

Извлечение первых трех слов из таблицы Excel

Пример:

Как выделить повторы без 1-го вхождения

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

  1. Выберите «Условное форматирование» > « Новое правило»> «Использовать формулу», чтобы определить ячейки для форматирования .
  2. В поле « Форматировать значения», где эта формула возвращает ИСТИНА , введите:

=СЧЕТЕСЛИ($B$2:$B2;$B2)>1

Где B2 – самая верхняя ячейка выбранного диапазона.

  1. Нажмите кнопку «Формат» выберите нужный цвет заливки и/или шрифта.
  2. Наконец, нажмите кнопку ОК, чтобы сохранить и применить созданное.

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

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

Ищем в таблицах Excelвсе повторяющиеся значения

Отметимвсе неуникальные записи в выделеннойобласти. Для этого нужно:

  • Зайти в группу «Стили».
  • Далее «Условное форматирование».
  • Теперь в выпадающем меню выбрать «Правила выделения ячеек».
  • Затем «Повторяющиеся значения».

как в excel сравнить два столбца и найти различия

  • Появится диалоговое окно:

как в экселе отфильтровать повторяющиеся значения

  • Нажать «ОК».

Программаищет повторения во всех столбцах.

как в excel найти повторяющиеся строки

Еслив таблице много неуникальных записей,то информативность такого поискасомнительна.

Удаление одинаковых значенийиз таблицы Excel

Способудаления неуникальных записей:

  1. Зайти во вкладку «Данные».
  2. Выделить столбец, в котором следует искать дублирующиеся строки.
  3. Опция «Удалить дубликаты».

Как выделить повторяющиеся и одинаковые значения в Excel

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

Как выделить повторяющиеся и одинаковые значения в Excel

Списокс уникальными значениями:

Как выделить повторяющиеся и одинаковые значения в Excel

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

Расширенныйфильтр – это инструмент для полученияупорядоченного списка с уникальнымизаписями.

  • Выбрать вкладку «Данные».
  • Перейти в раздел «Сортировка и фильтр».
  • Нажать команду «Дополнительно»:

Как выделить повторяющиеся и одинаковые значения в Excel

  • В появившемся диалоговом окне ставим флажок «Только уникальные записи».
  • Нажать «OK» – уникальный список готов.

Пояснение

По сути, эта формула использует функцию ПСТР для извлечения символов, начиная с предпоследнего пробела. Функция ПСТР принимает 3 аргумента: текст, с которым нужно работать, начальную позицию и количество извлекаемых символов.
Текст берется из столбца B, и количество символов может быть любым большим числом, которое обеспечит извлечение последних двух слов. Задача состоит в том, чтобы определить начальную позицию, которая находится сразу после предпоследнего пробела. Умная работа выполняется в первую очередь с функцией ПОДСТАВИТЬ, у которой есть необязательный аргумент, называемый номером экземпляра. Эта функция используется для замены предпоследнего пробела в тексте символом “@”, который затем находится с функцией НАЙТИ.
В приведенном ниже фрагменте кода показано, сколько пробелов в общем тексте, из которого вычитается 1.

ДЛСТР(B5)ДЛСТР(ПОДСТАВИТЬ(B5;” “;“”))1

В показанном примере в тексте 5 пробелов, поэтому приведенный выше код возвращает 4. Это число передается во внешнюю функцию ПОДСТАВИТЬ как номер экземпляра:

Это заставляет ПОДСТАВИТЬ заменять четвертый пробел на “@”. Выбор символа @ произвольный. Вы можете использовать любой символ, которого нет в исходном тексте.
Затем функция НАЙТИ находит в тексте символ “@”:

НАЙТИ(“@”;“Баба с возу —@кобыле легче.”)

Результат функции НАЙТИ – 14, к которому добавляется 1, чтобы получить 15. Это начальная позиция, которая переходит в функцию ПСТР в качестве второго аргумента. Для простоты количество извлекаемых символов жестко задано как 100. Это число произвольно и может быть изменено в зависимости от ситуации.

Извлечь из ячейки последние N слов

Эту формулу можно обобщить для извлечения из ячейки последних N слов, заменив жестко запрограммированную 1 в примере на (N-1). Кроме того, если вы извлекаете много слов, вы можете заменить жестко запрограммированный аргумент в ПСТР, 100, на большее число. Чтобы гарантировать, что число достаточно велико, вы можете просто использовать функцию ДЛСТР следующим образом:

=ПСТР(B5;НАЙТИ(“@”;ПОДСТАВИТЬ(B5;” “;“@”;

ДЛСТР(B5)ДЛСТР(ПОДСТАВИТЬ(B5;” “;“”))(N1)))+1;ДЛСТР(B5))

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...