Как найти одинаковые значения в excel в разных столбцах

Как найти одинаковые значения в excel в разных столбцах

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

Как найти одинаковые значения в столбце Excel

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

Как найти повторяющиеся значения в Excel?

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

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

Пример дневного журнала заказов на товары:

Чтобы проверить содержит ли журнал заказов возможные дубликаты, будем анализировать по наименованиям клиентов – столбец B:

1. Выделите диапазон B2:B9 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
2. Вберете «Использовать формулу для определения форматируемых ячеек».
3. Чтобы найти повторяющиеся значения в столбце Excel, в поле ввода введите формулу: =СЧЁТЕСЛИ($B$2:$B$9; B2)>1.
4. Нажмите на кнопку «Формат» и выберите желаемую заливку ячеек, чтобы выделить дубликаты цветом. Например, зеленый. И нажмите ОК на всех открытых окнах.

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

Пример функции СЧЁТЕСЛИ и выделение повторяющихся значений

Принцип действия формулы для поиска дубликатов условным форматированием – прост. Формула содержит функцию =СЧЁТЕСЛИ(). Эту функцию так же можно использовать при поиске одинаковых значений в диапазоне ячеек. В функции первым аргументом указан просматриваемый диапазон данных. Во втором аргументе мы указываем что мы ищем. Первый аргумент у нас имеет абсолютные ссылки, так как он должен быть неизменным. А второй аргумент наоборот, должен меняться на адрес каждой ячейки просматриваемого диапазона, потому имеет относительную ссылку.

Самые быстрые и простые способы: найти дубликаты в ячейках.

После функции идет оператор сравнения количества найденных значений в диапазоне с числом 1. То есть если больше чем одно значение, значит формула возвращает значение ИСТЕНА и к текущей ячейке применяется условное форматирование.

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

Сравнение данных в двух столбцах для поиска дубликатов в Excel

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Office для бизнеса Excel 2010 Excel 2007 Еще. Меньше

Для сравнения данных в двух столбцах листа Microsoft Excel и поиска повторяющихся записей можно использовать следующие методы.

Способ 1. Использование формулы рабочего листа

Способ 2. Использование макроса Visual Basic

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

Чтобы использовать макрос Visual Basic для сравнения данных в двух столбцах, выполните действия, описанные в следующем примере.

1. Запустите Excel.
2. Нажмите ALT+F11, чтобы запустить редактор Visual Basic.
3. В меню Вставка выберите Модуль.
4. Введите следующий код на листе модуля:

Sub Find_Matches() Dim CompareRange As Variant, x As Variant, y As Variant ' Set CompareRange equal to the range to which you will ' compare the selection. Set CompareRange = Range("C1:C5") ' NOTE: If the compare range is located on another workbook ' or worksheet, use the following syntax. ' Set CompareRange = Workbooks("Book2"). _ ' Worksheets("Sheet2").Range("C1:C5") ' ' Loop through each cell in the selection and compare it to ' each cell in CompareRange. For Each x In Selection For Each y In CompareRange If x = y Then x.Offset(0, 1) = x Next y Next x End Sub

Примечание: Если вы не видите вкладку Разработчик, возможно, вам понадобится включить ее. Для этого выберите Файл > Параметры > Настроить ленту, а затем выберите вкладку Разработчик в поле настройки справа.

Как в Excel найти ВСЕ одинаковые значения в разных листах?

Множество листов в одном доке. Первый столбец у всех одинаковый (например, название). У меня есть макрос, который выделяет на одном листе повторяющиеся значения РАЗНЫМ цветом. Хотелось бы, чтобы и на других тоже выделялось ТЕМ ЖЕ цветом, что и на первом. Как реализовать?

Sub DuplicatesColoring() Dim Dupes() 'объявляем массив для хранения дубликатов ReDim Dupes(1 To Selection.Cells.Count, 1 To 2) Selection.Interior.ColorIndex = -4142 'убираем заливку если была i = 3 For Each cell In Selection If WorksheetFunction.CountIf(Selection, cell.Value) > 1 Then For k = LBound(Dupes) To UBound(Dupes) 'если ячейка уже есть в массиве дубликатов - заливаем If Dupes(k, 1) = cell Then cell.Interior.ColorIndex = Dupes(k, 2) Next k 'если ячейка содержит дубликат, но еще не в массиве - добавляем ее в массив и заливаем If cell.Interior.ColorIndex = -4142 Then cell.Interior.ColorIndex = i Dupes(i, 1) = cell.Value Dupes(i, 2) = i i = i + 1 End If End If Next cell End Sub

• Вопрос задан более трёх лет назад
• 3598 просмотров

Комментировать
Решения вопроса 1

Принципы быстродействия VBA в описании

Я бы использовал коллекции, в которых можно отсекать дубликаты значений. Раз вы привыкли к выделению блоков, то вот пример перебора выделенных блоков в листах справа от активного.

Option Explicit '123456789012345678901234567890123456h8nor@ya567890123456789012345678toster56789 Private IndexColors As New Collection, CurrentColor As Integer, BaseSheetIndex As Byte Sub DuplicatesColoring() Dim cell As Range, selected As Range Set selected = Selection If BaseSheetIndex = 0 Then _ BaseSheetIndex = ActiveSheet.Index: CurrentColor = 3 ' Начальные значения selected.Interior.ColorIndex = -4142 ' Убрать заливку For Each cell In selected If Not IsEmpty(cell.Value) Then On Error Resume Next ' Включить обработку исключений If BaseSheetIndex = ActiveSheet.Index _ And WorksheetFunction.CountIf(selected, cell.Value2) > 1 Then IndexColors.Add CurrentColor, CStr(cell.Value2) ' Заполнить коллекцию If Not Err.Number = 457 Then CurrentColor = CurrentColor + 1 End If cell.Interior.ColorIndex = IndexColors(CStr(cell.Value2)) On Error GoTo 0 End If Next cell Do Until ActiveSheet.Index > ActiveWorkbook.Sheets.Count - 1 ActiveWorkbook.Sheets(ActiveSheet.Index + 1).Activate DuplicatesColoring Exit Sub ' Предотвратить бесконечную рекурсию Loop Worksheets(BaseSheetIndex).Activate End Sub

Ответ написан более трёх лет назад
Нравится 1 5 комментариев
AlexCruel @AlexCruel Автор вопроса

Спасибо за ответ!
Получается, если на первом листе не будет значений больше 1, то он не будет подсвечиваться, даже, если они имеются на других?
Если прописать = 0, то на первом листе будут подсвечены ВСЕ значения => и на других листах тоже.
Придется выбирать между этими вариантами.

Да. Одновременно с этим участвует условие добавление значения в коллекции с исходного (активного) листа BaseSheetIndex = ActiveSheet.Index . Только учтите, что количество значений индекса палитры, который вы изначально решили применять, довольно ограничено Excel 2010 и 2013 по разному кодируют цвета? И вообще где почитать про различия для разных версий офиса?

AlexCruel @AlexCruel Автор вопроса

Дѣаволъ, простите, не могли бы вы мне помочь? Можно ли реализовать поиск дублей по ВСЕМ листам сразу? Чтобы не удалялось автоматически, а пользователь смог принять решение — удалять или нет? Или, хотя бы, одинаковым цветом выделить для всех, например, только розовым?
То есть: первый лист смотрит на другие и сверяет со своим списком; потом второй лист сверяет свои значения со всеми, включая ПЕРВЫЙ и т.д.
Буду очень благодарен, спасибо!

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