Оглавление
Microsoft Excel остаётся мощнейшим инструментом для работы с данными, и в нём существует множество способов для анализа и сравнения информации. Один из распространённых сценариев – это сравнение двух столбцов данных между собой. На первый взгляд это может показаться просто, но неподготовленный пользователь может столкнуться со множеством проблем и поиском нужных формул.
В этой статье мы рассмотрим различные методы и функции, которые позволят вам сравнить два столбца в Excel. Мы покажем, как использовать встроенные функции Excel, такие как ЕСЛИ, ВПР, ИНДЕКС, а также рассмотрим использование условного форматирования. Вы узнаете, как быстро сравнивать значения в двух столбцах и находить идентичные и недостающие значения.
1. Сравнение двух столбцов на совпадения строк
Это самый простой и быстрый способ, в случае если вам необходимо сравнить между столбцами каждую строку.
Пример: сравнение ячеек в одной строке
Ниже приведено два столбца, которые имеют 9 строк.
Для того чтобы проверить оба столбца на совпадения, можно создать новый столбец (как на примере ниже в ячейке С1) и воспользоваться следующей формулой:
=A2=B2
Эта формула сравнивает два столбца А2 и В2 между собой и выдаёт значение: ИСТИНА (совпадает), либо ЛОЖЬ (не совпадает).
Для того чтобы применить эту формулу для всех ячеек, достаточно навестись на правый нижний угол ячейки С2 и с зажатием левой кнопкой мыши потянуть её до необходимой строки.
Пример: сравнение ячеек в одной строке с помощью формулы ЕСЛИ
Если вам необходимо показать произвольный текст в столбце результатов, то можно воспользоваться простой формулой:
=ЕСЛИ(A2=B2;”Совпадает”;”Не совпадает”)
В кавычки данной формулы можно вставить любой текст, который будет отображаться для совпадений и несовпадений.
Если вам необходимо сравнить два столбца с учётом регистра (например, чтобы учитывалось заглавное или строчное написание “IBM” и “ibm”), то можно воспользоваться следующей формулой:
=СОВПАД(A2;B2)
Приведённая формула покажет, в каких строках установлен одинаковый регистр, а в каких нет.
Пример: выделение строк с условным форматированием
Если вы хотите выделить строки с совпадающими данными (вместо того, чтобы получать результат в отдельном столбце), вы можете сделать это с помощью форматирования. Для этого необходимо выполнить несколько простых шагов:
- Выделите все необходимые данные для проверки.
- Перейдите на вкладку “Главная”.
- Теперь выбираем “Условное форматирование” в категории стилей и нажимаем “Создать правило”.
- В окне форматирования выбираем последнюю опцию “Использовать формулу для определения форматируемых ячеек”.
- В поле “Форматировать значения…” вставляем формулу: =$A2=$B2.
- Нажимаем кнопку “Формат…” и выбираем во вкладке “Заливка” произвольный цвет для ячеек.
- Подтверждаем создание правила кнопкой “ОК”.
В результате мы получим все выделенные ячейки, которые имеют одинаковые значения в каждой строке.
2. Сравнение двух столбцов с выделением совпадений
Если вы хотите сравнить два столбца и выделить совпадающие значения, то вы можете использовать функцию дублирования в условном форматировании. Данный способ позволит сравнивать все заданные значения в столбцах, вместо сравнения индивидуальных строк.
Пример: сравнение двух столбцов и выделение совпадающих данных
Иногда могут попадаться данные, которые имеют совпадения между собой, но они находятся на разных строках. Именно этот способ поможет отобразить соответствия в этих значения, несмотря на их расположение в строке.
Пример данных, с которыми предстоит работать указан ниже.
Для того чтобы выделить одинаковые значения в разных строках, можно воспользоваться условным форматированием:
- Выделяем все необходимые данные для проверки
- Во вкладке “Главная” выбираем “Условное форматирование”.
- В контекстном меню выбираем “Правила выделения ячеек” и затем “Повторяющиеся значения…”.
- В следующем окне выбираем “повторяющиеся” значения, а также выбираем цвет, который будет использоваться для одинаковых ячеек.
В результате мы получим выделение тех ячеек, которые имеют совпадения несмотря на то, что они находятся в разных строках.
Пример: сравнение двух столбцов и выделение несовпадающих данных
В случае если вы хотите выделить те ячейки, которые не повторяются в таблице, то вы можете использовать условное форматирование с уникальными значениями.
- Выделяем все необходимые данные для проверки
- Во вкладке “Главная” выбираем “Условное форматирование”.
- В контекстном меню выбираем “Правила выделения ячеек” и затем “Повторяющиеся значения…”.
- В следующем окне выбираем “уникальные” значения, а также выбираем цвет, который будет использоваться для одинаковых ячеек.
После этого мы получим выделение только тех ячеек, которые не повторяются в других строках или столбцах.
3. Сравнение двух столбцов и поиск отсутствующих значений в таблице
Предположим, у вас есть массив данных, где вам необходимо определить те ячейки, которые присутствуют в первом столбце, но отсутствуют во втором.
Для этого можно воспользоваться формулой ВПР (VLOOKUP). ВПР позволяет искать значение в левом столбце таблицы и возвращать значение ячейки, находящейся в указанном столбце той же строки.
=ЕСЛИОШИБКА(ВПР(A2,$B$2:$B$10,1,0))
Эта формула используется для проверки строк на наличие значений из левого столбца в правом столбце. В случае если значение будет присутствовать в обоих столбцах, будет возвращено отсутствие ошибки “ЛОЖЬ”. Если значение будет только в одном столбце, то это выдаст ошибку “ИСТИНА”.
4. Сравнение двух столбцов и извлечение совпадающих данных
Если у вас есть две таблицы с данными из которых вы хотите получить определённые значения из списка, то нам предстоит воспользоваться несколькими формулами.
Пример: выборка совпадающих данных (точная)
Например, в приведённой ниже таблице имеется два столбца в одном из которых имеется соответствующая сумма. Для того чтобы добавить эти значения во второй столбец, потребуется соответствующая формула:
=ВПР(D2,$A$2:$B$14,2,0)
Либо можно воспользоваться функцией поиска:
=ИНДЕКС($A$2:$B$14,ПОИСКПОЗ(D2,$A$2:$A$14,0),2)
Пример: выборка совпадающих данных (частичная)
Предположим, у вас есть тот же набор значений, как было показано в примере выше. Но вместо того, чтобы брать полные значения из первого столбца, можно создавать выборку из совпадающих слов (например, JPMorgan вместо JPMorgan Chase и Exxon вместо ExxonMobil).
В таком случае можно использовать функцию ВПР в следующей формуле:
=ВПР(“*”&D2&”*”,$A$2:$B$14,2,0)
или же использовать функцию для поиска совпадений:
=ИНДЕКС($A$2:$B$14,ПОИСКПОЗ(“*”&D2&”*”,$A$2:$A$14,0),2)
В последней формуле звёздочка (*) является символом подстановки, в котором можно указывать любое количество символов. Когда искомое значение окружено этим символом с обеих сторон, Excel будет искать любые значения подходящие под этот параметр. Например, при вводе в формулу *Exxon*, табличный процессор подберёт строку из ExxonMobil.
5. Функция Inquire
В последних версиях MS Excel появилась возможность отслеживать изменения между ячейками и просматривать данные между файлами формата .XLS или .XLSX. Эта функция может быть полезной для сверки и слияния данных из различных источников.
Для использования Inquire необходимо проделать несколько шагов:
- Выбрать первую вкладку “Файл” в Excel и нажать “Другие…” > “Параметры”.
- В окне параметров Excel перейти во вкладку “Надстройки” и найти в списке “Пакет анализа”.
- В нижней части списка выбрать “Надстройки COM” и нажать “Перейти…”.
- В доступных надстройках выбрать “Inquire” и нажать “ОК”.
- На главной панели Excel появится новая вкладка Inquire
- Открываем вторую книгу Excel, которую вы хотите сравнить с первой книгой и нажимаем “Compare Files”.
- Выбираем подходящие файлы для сравнения и нажимаем “Compare”.
- Excel сравнит два документа и отобразит различия между ними в столбцах.