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

Для кого эта статья:

• специалисты, работающие с данными и аналитикой
• пользователи Excel и Google Sheets, желающие улучшить свои навыки

• бизнес-аналитики и финансовые эксперты, стремящиеся избежать ошибок в отчетности

  🔍 Представьте, что вам нужно сравнить два списка клиентов по 5000 фамилий каждый и выявить дубликаты. Ручная проверка займёт часы, если не дни! К счастью, табличные редакторы предлагают элегантные решения для поиска совпадающих значений, превращая многочасовую работу в задачу на пару минут. В этой статье я расскажу о пяти проверенных методах поиска одинаковых значений в Excel и Google Sheets, которые вы сможете применить уже сегодня — без сложных формул и программирования.

Хотите взять навыки работы с данными на новый уровень? Курс «Excel для работы» с нуля от Skypro — это именно то, что вам нужно. Вы научитесь не только находить дубликаты, но и освоите десятки других полезных функций и приёмов, которые автоматизируют вашу работу с таблицами. Студенты курса экономят до 2 часов ежедневно на рутинных задачах! Начните обучение сейчас и забудьте о ручной обработке данных навсегда.

Зачем искать одинаковые значения в столбцах

Поиск совпадений в таблицах — это не просто техническая задача. Это фундаментальный инструмент анализа и очистки данных, который решает несколько критических бизнес-задач:

• Удаление дубликатов: предотвращает повторный подсчёт клиентов, товаров или транзакций
• Сверка данных: выявляет несоответствия между разными базами данных
• Объединение информации: позволяет корректно совместить данные из разных источников
• Анализ пересечений: определяет общие элементы в разных категориях или группах
• Контроль качества: помогает найти и исправить ошибки ввода

Александр Петров, финансовый аналитик Однажды наш отдел едва не отправил клиенту отчет с двойным учётом транзакций на сумму более 2 миллионов рублей. Причина оказалась банальной — мы объединили данные из двух CRM-систем, не проверив их на дублирование. К счастью, перед отправкой я решил проверить отчёт, используя VLOOKUP для сравнения ID транзакций. Обнаружилось 126 дублей, которые полностью искажали картину. Этот случай стал поворотным для нашего отдела — теперь проверка на дубликаты является обязательным этапом подготовки любого финансового отчета. А я теперь сравниваю столбцы не дольше пяти минут, используя формулы и условное форматирование.

По данным исследования Gartner за 2024 год, компании теряют в среднем 14,2 миллиона долларов ежегодно из-за низкого качества данных. Один из главных источников проблем — дублирование информации, которое приводит к искажению аналитики и неэффективным бизнес-решениям.

Быстрые методы поиска совпадений в Excel

Excel предлагает несколько интуитивно понятных способов поиска совпадений, которые не требуют знания сложных формул. Рассмотрим три самых эффективных метода:

Эффективные формулы для сравнения данных

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

Существует несколько ключевых функций, которые незаменимы при поиске одинаковых значений:

• VLOOKUP / ВПР — вертикальный поиск значения в первом столбце таблицы с возможностью возврата данных из соседних столбцов
• MATCH / ПОИСКПОЗ — находит позицию (номер строки или столбца) искомого элемента
• COUNTIF / СЧЁТЕСЛИ — подсчитывает количество ячеек, соответствующих заданному условию
• INDEX / ИНДЕКС — возвращает значение из указанной позиции в массиве или диапазоне

Рассмотрим наиболее универсальные формулы для сравнения двух столбцов данных:

Наиболее простая и эффективная формула для начинающих пользователей — комбинация COUNTIF с IF:

=IF(COUNTIF(F:F,A2)>0,"Есть совпадение","Нет совпадения")

Эта формула проверяет, встречается ли значение из ячейки A2 хотя бы раз в диапазоне F:F. Если значение найдено, формула возвращает "Есть совпадение", иначе — "Нет совпадения".

Для более сложных сценариев, когда нужно не просто найти совпадение, но и получить связанные данные, используйте VLOOKUP:

=VLOOKUP(A2,F:G,2,FALSE)

Эта формула ищет значение из ячейки A2 в первом столбце диапазона F:G и возвращает соответствующее значение из второго столбца этого диапазона.

Мария Ковалёва, HR-менеджер Я столкнулась с настоящим HR-кошмаром: нужно было объединить две кадровые базы после слияния компаний и выявить сотрудников, которые числились в обеих организациях. Речь шла о более чем 3000 записей с разной структурой данных. Без автоматизации эта задача заняла бы недели.

Решение пришло неожиданно простое. Я создала сводную таблицу с ФИО и датами рождения из обеих баз, а затем применила условное форматирование для выделения дубликатов. Система мгновенно подсветила 47 совпадающих записей! Затем я использовала формулу CONCATENATE для создания уникальных идентификаторов (ФИО + дата рождения) и функцию COUNTIFS для подсчета точных совпадений по нескольким параметрам.

В итоге, задача, на которую выделили две недели, была решена за один рабочий день. А руководство даже не поверило, что всё было сделано без привлечения IT-отдела — только стандартными функциями Excel.

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

Визуальное выделение совпадений — это, пожалуй, самый интуитивно понятный и наглядный способ поиска одинаковых значений. Условное форматирование в Excel и Google Sheets позволяет мгновенно обнаруживать совпадения, подсвечивая их различными цветами.

Процесс настройки условного форматирования для поиска совпадений:

1. Выделите диапазон, в котором хотите найти совпадения
2. Перейдите в меню "Главная" → "Условное форматирование" → "Правила выделения ячеек" → "Повторяющиеся значения"
3. В появившемся диалоговом окне выберите "Повторяющиеся" и укажите формат (например, светло-красную заливку)
4. Нажмите "OK" для применения форматирования

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

1. Выделите диапазон для проверки (например, столбец A)
2. Выберите "Условное форматирование" → "Создать правило" → "Использовать формулу для определения форматируемых ячеек"
3. Введите формулу: =COUNTIF($F:$F,$A1)>0
4. Настройте формат и нажмите "OK"

Эта формула подсветит все значения из столбца A, которые встречаются хотя бы раз в столбце F.

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

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

Тест на профориентацию от Skypro поможет определить, насколько вам подходит аналитическая работа с данными. Если вам нравится находить закономерности, выявлять совпадения и работать с цифрами, возможно, карьера аналитика данных — ваше призвание. Пройдите тест и узнайте, какие профессии соответствуют вашему складу ума и могут открыть двери к востребованным специальностям с Excel и аналитикой в 2025 году!

Автоматизация поиска одинаковых значений

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

• Сводные таблицы для агрегирования и анализа совпадений
• Power Query для сложных сценариев сравнения и объединения данных
• VBA-макросы для полностью автоматизированной обработки
• Power Automate для интеграции с другими системами

Самый доступный метод автоматизации — использование сводных таблиц:

1. Скопируйте данные из обоих столбцов в один диапазон, добавив метку источника
2. Выделите полученный диапазон и создайте сводную таблицу (Вставка → Сводная таблица)
3. Перетащите поле со значениями в область строк, а метку источника — в область столбцов
4. Перетащите поле со значениями в область значений, выбрав "Количество"

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

Для более продвинутой автоматизации можно использовать Power Query (доступен в Excel 2016 и новее):

1. Перейдите на вкладку "Данные" → "Получить данные" → "Из таблицы/диапазона"
2. Выберите первый диапазон данных и нажмите "Загрузить в редактор"
3. Повторите для второго диапазона
4. В редакторе Power Query выберите "Главная" → "Объединить запросы" → "Внешнее объединение" (для нахождения всех значений из обоих столбцов)
5. Настройте параметры объединения и загрузите результат

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

Для частых, повторяющихся операций можно записать и настроить макрос VBA:

Sub FindDuplicatesBetweenColumns()
Dim rng1 As Range, rng2 As Range
Dim cell As Range
Dim lastRow1 As Long, lastRow2 As Long

' Определяем диапазоны данных
lastRow1 = Cells(Rows.Count, "A").End(xlUp).Row
lastRow2 = Cells(Rows.Count, "F").End(xlUp).Row
Set rng1 = Range("A1:A" & lastRow1)
Set rng2 = Range("F1:F" & lastRow2)

' Очищаем предыдущее форматирование
rng1.Interior.ColorIndex = xlNone

' Ищем и выделяем совпадения
For Each cell In rng1
If WorksheetFunction.CountIf(rng2, cell.Value) > 0 Then
cell.Interior.Color = RGB(255, 200, 200) ' Светло-красный
End If
Next cell

MsgBox "Поиск совпадений завершен!", vbInformation
End Sub

🚀 Этот макрос автоматически сравнивает значения в столбцах A и F, выделяя все совпадения светло-красным цветом. Вы можете настроить код под свои нужды, изменив диапазоны, цвета или добавив дополнительную логику.

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

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