Оглавление
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 сравнит два документа и отобразит различия между ними в столбцах.