Как сравнить столбцы в Excel: простые способы и функции 📊

4,3K

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

В этой статье мы рассмотрим различные методы и функции, которые позволят вам сравнить два столбца в Excel. Мы покажем, как использовать встроенные функции Excel, такие как ЕСЛИ, ВПР, ИНДЕКС, а также рассмотрим использование условного форматирования. Вы узнаете, как быстро сравнивать значения в двух столбцах и находить идентичные и недостающие значения.

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

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

Пример: сравнение ячеек в одной строке

Ниже приведено два столбца, которые имеют 9 строк.

Для того чтобы проверить оба столбца на совпадения, можно создать новый столбец (как на примере ниже в ячейке С1) и воспользоваться следующей формулой:

=A2=B2

Эта формула сравнивает два столбца А2 и В2 между собой и выдаёт значение: ИСТИНА (совпадает), либо ЛОЖЬ (не совпадает).

Для того чтобы применить эту формулу для всех ячеек, достаточно навестись на правый нижний угол ячейки С2 и с зажатием левой кнопкой мыши потянуть её до необходимой строки. 

Пример: сравнение ячеек в одной строке с помощью формулы ЕСЛИ

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

=ЕСЛИ(A2=B2;”Совпадает”;”Не совпадает”)

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

Если вам необходимо сравнить два столбца с учётом регистра (например, чтобы учитывалось заглавное или строчное написание “IBM” и “ibm”), то можно воспользоваться следующей формулой:

=СОВПАД(A2;B2)

Приведённая формула покажет, в каких строках установлен одинаковый регистр, а в каких нет.

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

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

1. Выделите все необходимые данные для проверки.
2. Перейдите на вкладку “Главная”.
3. Теперь выбираем “Условное форматирование” в категории стилей и нажимаем “Создать правило”.
4. В окне форматирования выбираем последнюю опцию “Использовать формулу для определения форматируемых ячеек”.
5. В поле “Форматировать значения…” вставляем формулу: =$A2=$B2.
6. Нажимаем кнопку “Формат…” и выбираем во вкладке “Заливка” произвольный цвет для ячеек.
7. Подтверждаем создание правила кнопкой “ОК”.

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

2. Сравнение двух столбцов с выделением совпадений

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

Пример: сравнение двух столбцов и выделение совпадающих данных

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

Пример данных, с которыми предстоит работать указан ниже.

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

1. Выделяем все необходимые данные для проверки
2. Во вкладке “Главная” выбираем “Условное форматирование”.
3. В контекстном меню выбираем “Правила выделения ячеек” и затем “Повторяющиеся значения…”.
4. В следующем окне выбираем “повторяющиеся” значения, а также выбираем цвет, который будет использоваться для одинаковых ячеек.

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

Пример: сравнение двух столбцов и выделение несовпадающих данных

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

1. Выделяем все необходимые данные для проверки
2. Во вкладке “Главная” выбираем “Условное форматирование”.
3. В контекстном меню выбираем “Правила выделения ячеек” и затем “Повторяющиеся значения…”.
4. В следующем окне выбираем “уникальные” значения, а также выбираем цвет, который будет использоваться для одинаковых ячеек.

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

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 необходимо проделать несколько шагов:

1. Выбрать первую вкладку “Файл” в Excel и нажать “Другие…” > “Параметры”.
2. В окне параметров Excel перейти во вкладку “Надстройки” и найти в списке “Пакет анализа”.
   
3. В нижней части списка выбрать “Надстройки COM” и нажать “Перейти…”.
4. В доступных надстройках выбрать “Inquire” и нажать “ОК”.
   
5. На главной панели Excel появится новая вкладка Inquire
6. Открываем вторую книгу Excel, которую вы хотите сравнить с первой книгой и нажимаем “Compare Files”.
7. Выбираем подходящие файлы для сравнения и нажимаем “Compare”.
8. Excel сравнит два документа и отобразит различия между ними в столбцах.