Check-moscow.ru

Финансы и учет
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как выделить повторяющиеся строки в нескольких столбцах в Excel?

8 приемов для ускоренной работы в Excel

Как быстро удалить дубликаты, закрепить линию и сортировать данные.

cover-excel-8-609be840b10b5087304102.jpg

В Excel есть много команд, которые значительно облегчают работу.

Вместе с аналитиком Laba Group Валерией Петренко мы рассмотрим 8 простых функций, которые повысят вашу продуктивность в программе.

Выделите повторяющиеся ячейки в столбце

Наиболее распространенная ситуация — это когда у вас есть набор данных в столбце, и вы хотите быстро выделить дубликаты.

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

Ниже приведены шаги по выделению дубликатов в столбце:

  • Выберите набор данных names (без заголовков)
  • Выберите в меню опцию Формат.
  • В появившихся параметрах щелкните Условное форматирование. Это откроет панель правил условного формата справа.
  • Нажмите на опцию «Добавить другое правило».
  • Убедитесь, что диапазон (где нам нужно выделить дубликаты) правильный. Если это не так, вы можете изменить его в разделе «Применить к диапазону».
  • Щелкните раскрывающееся меню «Форматировать ячейки, если», а затем выберите параметр «Пользовательская формула есть».
  • В поле ниже введите следующую формулу: =COUNTIF($A$2:$A$10,A2)>1
  • В параметрах «Стиль форматирования» укажите форматирование, в котором вы хотите выделить повторяющиеся ячейки. По умолчанию он будет использовать зеленый цвет, но вы можете указать другие цвета, а также стили, такие как полужирный или курсив.
  • Нажмите Готово

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

Поскольку условное форматирование является динамическим, оно имеет много преимуществ. Это означает, что если вы измените данные в любой из ячеек, форматирование обновится автоматически. При удалении одного из дубликатов выделение этого имени (в другой ячейке) исчезнет, поскольку теперь оно уникально.

Как она работает?

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

Если формула возвращает TRUE для ячейки, она будет выделена в указанном формате, но если возвращает FALSE, то нет.

Читайте так же:
Как заполнить пустые ячейки значением выше / ниже / слева / справа в Excel?

В приведенном выше примере проверяется каждая ячейка, и если имя встречается в диапазоне более одного раза, то для формулы СЧЕТ возвращается значение TRUE и ячейка выделяется. В противном случае он остается неизменным.

Был использован диапазон $ A $ 2: $ A $ 10, который состоит из алфавита столбцов и номера строки, которому предшествует знак доллара. Это действительно важно, поскольку гарантирует, что когда формула переходит к следующей ячейке (в строке ниже), общий диапазон, который проверяется на количество имен, остается неизменным.

Чтобы удалить выделенные ячейки, необходимо удалить условное форматирование. Это можно сделать, выделив ячейки, к которым было применено форматирование, нажав кнопку Формат, выбрав Условное форматирование и удалив правило в открывшемся справа окне.

Обработка найденных дубликатов

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

Показать только повторяющиеся строки в столбце А

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

Сравнить столбцы и удалить дубликаты в Excel

Щелкнув правой кнопкой мыши и выбрав пункт Insert из контекстного меню:

Сравнить столбцы и удалить дубликаты в Excel

Дайте названия столбцам, например, «Name» и «Duplicate?» Затем откройте вкладку Data (Данные) и нажмите Filter (Фильтр):

Сравнить столбцы и удалить дубликаты в Excel

После этого нажмите меленькую серую стрелку рядом с «Duplicate?«, чтобы раскрыть меню фильтра; снимите галочки со всех элементов этого списка, кроме Duplicate, и нажмите ОК.

Сравнить столбцы и удалить дубликаты в Excel

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

Сравнить столбцы и удалить дубликаты в Excel

Чтобы снова отобразить все строки в столбце A, нажмите на символ фильтра в столбце B, который теперь выглядит как воронка со стрелкой. Или вы можете сделать то же самое через ленту, нажав Данные > Выбрать и фильтр > Очистить, как показано на скриншоте ниже:

Читайте так же:
Как вставить разрыв страницы каждые x строк в Excel?

Сравнить столбцы и удалить дубликаты в Excel

Изменение цвета или выделение найденных дубликатов

Если пометки «Duplicate» не достаточно для Ваших целей, и Вы хотите отметить повторяющиеся ячейки другим цветом шрифта, заливки или каким-либо другим способом…

В этом случае отфильтруйте дубликаты, как показано выше, выделите все отфильтрованные ячейки и нажмите Ctrl+1, чтобы открыть диалоговое окно Формат ячеек. Подумайте о том, чтобы изменить цвет заливки дублированных строк, например, на желтый. Заливка на вкладке Главная позволяет изменить цвет заливки, но диалоговое окно Формат ячеек позволяет изменить все одновременно.

Сравнить столбцы и удалить дубликаты в Excel

Отныне вы никогда не пропустите ни одного дубликата:

Сравнить столбцы и удалить дубликаты в Excel

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

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

Если два сравниваемых столбца находятся на разных листах, т.е. в разных таблицах, щелкните правой кнопкой мыши на выбранном диапазоне и выберите в контекстном меню команду Удалить строку:

Сравнить столбцы и удалить дубликаты в Excel

Удалите всю строку листа, а затем очистите фильтр, нажав OK, когда Excel спросит, уверены ли вы, что хотите это сделать. Ниже приведены оставшиеся строки с уникальными значениями:

Сравнить столбцы и удалить дубликаты в Excel

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

  1. Отфильтруйте таблицу так, чтобы отображались только дублирующиеся значения, и выделите эти ячейки. Кликните по ним правой кнопкой мыши и в контекстном меню выберите Clear contents (Очистить содержимое).Сравнить столбцы и удалить дубликаты в Excel
  2. Очистите фильтр.
  3. Выделите все ячейки в столбце А, начиная с ячейки А1 вплоть до самой нижней, содержащей данные.
  4. Откройте вкладку Data (Данные) и нажмите Sort A to Z (Сортировка от А до Я). В открывшемся диалоговом окне выберите пункт Continue with the current selection (Сортировать в пределах указанного выделения) и нажмите кнопку Sort (Сортировка):Сравнить столбцы и удалить дубликаты в Excel
  5. Удалите столбец с формулой, он Вам больше не понадобится, с этого момента у Вас остались только уникальные значения.
  6. Вот и всё, теперь столбец А содержит только уникальные данные, которых нет в столбце В:Сравнить столбцы и удалить дубликаты в Excel
Читайте так же:
Как выделить заблокированные ячейки в Excel

Как видите, удалить дубликаты из двух колонок в Excel легко с помощью формул.

Как выделить повторяющиеся строки в нескольких столбцах в Excel?

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

Выделите повторяющиеся строки в столбцах Kutools for Excelхорошая идея3

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

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

1. Выберите диапазон данных, щелкните Главная > Условное форматирование > Новое правило. И в появлении Новое правило форматирования диалоговое окно, выберите Используйте формулу, чтобы определить, какие ячейки следует форматировать., и вставьте эту формулу =COUNTIFS($A$2:$A$10,$A2,$B$2:$B$10,$B2,$C$2:$C$10,$C2)>1 в поле Форматировать значения, в которых эта формула верна. Смотрите скриншот:
документ выделить дубликаты в столбцах 2

2. Нажмите Формат в Новое правило форматирования диалог, чтобы открыть Формат ячеек диалог. В диалоговом окне нажмите Заполнять вкладку и выберите один цвет фона, которым вы хотите выделить повторяющиеся строки. Смотрите скриншот:
документ выделить дубликаты в столбцах 3

3. Нажмите OK > OK , чтобы закрыть диалоги, и теперь повторяющиеся строки в нескольких столбцах были выделены указанным цветом заливки. Смотрите скриншот:
документ выделить дубликаты в столбцах 4

Примечание: В формуле A2:A10, B2:B10 и C2:C10 — это диапазоны столбцов, с которыми вы работаете, вы можете изменять их по своему усмотрению.

Выделите повторяющиеся строки в столбцах Kutools for Excel

Если вы хотите работать с десятками столбцов, условное форматирование — не лучший выбор для этой задачи. Однако вы можете применить утилиту Kutools for ExcelAuthor Select Repeating and Single Cells для быстрого выделения строк из десятков столбцов.

Читайте так же:
Как вывести список всех открытых (запущенных в данный момент) приложений в Excel?

После установки Kutools for Excel необходимо выполнить приведенные ниже инструкции. (Скачайте Kutools for Excel бесплатно прямо сейчас!)

1. Выберите диапазон данных, щелкните Кутулс > Выбрать>Выберите повторяющиеся и уникальные ячейки. Смотрите скриншот:
документ выделить дубликаты в столбцах 5

2. в Выберите повторяющиеся и уникальные ячейки диалог, проверьте Дубликаты (кроме 1-го) or Все дубликаты (включая 1-й) варианты, которые вам нужны, и перейдите к Обработка результатов раздел, чтобы указать цвет выделения и цвет шрифта. Нажмите Ok, и появится диалоговое окно, напоминающее количество выбранных повторяющихся строк. Смотрите скриншот:
документ выделить дубликаты в столбцах 6

3. Нажмите OK, теперь повторяющиеся строки выделены. Смотрите скриншот:
документ выделить дубликаты в столбцах 78

Поиск и удаление

Существуют различные методы для поиска дубликатов значений таблиц и их удаления из базы данных. Поиск и удаление дубликатов — это один и тот же процесс в каждом из этих вариантов.

Способ 1: простое удаление повторяющихся строк

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

  1. Выберите весь диапазон таблицы. Перейдите на вкладку «Данные». Нажмите на кнопку «Удалить дубликаты». Он находится на ленте панели инструментов «Работа с данными».

Удаление дубликатов в Microsoft Excel

Окно удаления дубликатов в Microsoft Excel

Информационное окно в Microsoft Excel

Способ 2: удаление дубликатов в «умной таблице»

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

    Выделяем весь табличный диапазон.

Выделение таблицы в Microsoft Excel

Создание умной таблицы в Microsoft Excel

Подтверждение диапазона для создание умной таблицы в Microsoft Excel

Переход к удалению дубликатов в Microsoft Excel

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

Способ 3: применение сортировки

Этот метод не совсем удаляет дубликаты, поскольку сортировка только скрывает дубликаты в таблице.

    Выделяем таблицу. Переходим во вкладку «Данные». Жмем на кнопку «Фильтр», расположенную в блоке настроек «Сортировка и фильтр».

Включение фильтра в Microsoft Excel

Переход в раздел Дополнительно в Microsoft Excel

Окно расширенного фильтра в Microsoft Excel

После этого повторяющиеся записи будут скрыты. Но вы можете активировать их в любое время, снова нажав кнопку «Фильтр».

Включение показа дублей в Microsoft Excel

Способ 4: условное форматирование

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

Читайте так же:
Как добавить горизонтальные границы только в определенном диапазоне в Excel?

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

Переход к условному форматипррованию в Microsoft Excel

Настройка форматирования в Microsoft Excel

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

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

Способ 5: применение формулы

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

= ЕСЛИОШИБКА(ИНДЕКС(адрес_столбца;ПОИСКПОЗ(0;СЧЁТЕСЛИ(адрес_шапки_столбца_дубликатов: адрес_шапки_столбца_дубликатов (абсолютный); адрес_столбца;)+ЕСЛИ(СЧЁТЕСЛИ(адрес_столбца;; адрес_столбца;)>1;0;1);0));»»)

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

Столбец для дубликатов в Microsoft Excel

Формула в Microsoft Excel

Выделение сторлбца в Microsoft Excel

После этих действий в столбце «Дубликаты» будут показаны дубликаты.

Отображение дубликатов в Microsoft Excel

Но этот метод все еще слишком сложен для большинства пользователей. Более того, он только ищет дубликаты и не удаляет их. Поэтому рекомендуется использовать более простые и функциональные решения, описанные выше.

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

ЗакрытьМы рады, что смогли помочь Вам в решении проблемы.

Помимо этой статьи, на сайте еще 12677 инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.

Поблагодарить автора, поделившись статьей в социальных сетях.

ЗакрытьОпишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector