Как сравнить два списка в Excel с помощью 5 методов (плюс пример)
25 апреля 2022 г.
Microsoft Excel — популярный инструмент для организации и анализа данных. Одной из полезных функций Excel является возможность сравнивать наборы данных и находить различия между ними. Узнав, как сравнивать списки в Excel, вы часто можете выполнять такие процессы, как проверка данных, намного быстрее. В этой статье мы предоставим вам несколько методов сравнения двух списков в Excel, а также несколько примеров того, когда вам может понадобиться это сделать.
Зачем сравнивать два списка в Excel?
Сравнение двух списков в Excel позволяет определить различия между двумя наборами данных. Существует множество сценариев, в которых эта функция может помочь вам выполнять важные рабочие задачи. Например, у компании может быть электронная таблица адресов клиентов с двумя отдельными столбцами. Первый содержит адреса, хранящиеся в компьютерной системе компании, а другой содержит информацию, предоставленную самими клиентами. Сравнив эти два списка, вы можете быстро определить, имеет ли компания правильную адресную информацию, и исправить все орфографические ошибки.
Как сравнить два списка в Excel
Ниже приведены несколько методов, которые можно использовать для сравнения двух списков в Excel:
Используйте условное форматирование
Простой способ сравнить два списка в Excel — использовать условное форматирование. С помощью этого метода вы настраиваете внешний вид ячейки в зависимости от определенных условий. Например, вы можете выделить только уникальные значения в двух списках. Чтобы использовать этот метод, выполните следующие действия:
Выберите все ячейки в обоих списках.
В меню «Главная» выберите «Условное форматирование».
В этом меню выберите «Правила выделения ячеек», а затем «Повторяющиеся значения».
Выберите «Уникальный» в первом раскрывающемся меню, а затем предпочитаемое форматирование во втором меню.
Нажмите «ОК», чтобы просмотреть уникальные значения в каждом выделенном списке.
Создать новое правило форматирования
Вы также можете создать собственное правило форматирования для сравнения двух списков. Это позволяет выполнять множество задач, например проверять, появляются ли определенные элементы, которые появляются в одном списке, также и в другом. Чтобы создать новое правило форматирования, выполните следующие действия:
Выберите все ячейки в первом списке.
В меню «Главная» выберите «Условное форматирование», а затем «Новое правило».
Выберите параметр «Использовать формулу, чтобы определить, какие ячейки форматировать» в следующем меню, которое находится под заголовком «Выбор типа правила».
Напишите формулу «=СЧЁТЕСЛИ(B1:B10,A1)=0» в разделе «Редактировать описание правила», которая проверяет, существует ли значение в ячейке «A1» в диапазоне «B1:B1».
Замените «B1:B10» диапазоном для вашего второго списка, а «A1» — ссылкой на первую ячейку в вашем первом списке.
Нажмите кнопку «Формат», чтобы изменить способ форматирования ячеек Excel на основе вашего нового правила, затем нажмите кнопку «ОК», чтобы завершить новое правило.
Используйте метод разницы строк
Чтобы быстро сравнить два списка в одинаковых строках, вы можете использовать функцию различия строк в Excel. Сравнение строк позволяет сравнивать ячейки, расположенные рядом друг с другом, а не искать каждое значение во всем списке. Чтобы использовать эту функцию, выполните следующие действия:
Выберите все ячейки в обоих списках.
Нажмите клавишу «F5», чтобы открыть инструмент «Перейти к специальному».
Нажмите на кнопку с надписью «Особый».
Выберите параметр «Различия строк», затем нажмите «ОК», чтобы выделить все ячейки с различиями между двумя строками.
Выберите цвет заливки, чтобы выделить любые различия для дальнейшего использования.
Используйте третий столбец
Вы можете легко отобразить различия между строками, добавив третий столбец. Например, если у вас есть два списка с именами, вы можете использовать столбец рядом с ними, чтобы увидеть, совпадают ли имена в строке. Для этого выполните следующие действия:
Выберите ячейку вашего нового третьего столбца, где вы хотите, чтобы результат появился.
Используйте формулу «= ЕСЛИ (A1 = B1, «Совпадение», «Не совпадение»)», чтобы проверить, совпадает ли ячейка в A1 с ячейкой B1, заменив ссылки, чтобы они соответствовали вашим собственным данным.
Нажмите клавишу «Ввод» или выберите другую ячейку, чтобы применить формулу.
Определите, написано ли в вашей ячейке «совпадение» или «не совпадение», в зависимости от данных в ячейках A1 и B1.
Наведите указатель мыши на правый нижний угол этой ячейки, пока курсор не превратится в белый крест.
Нажмите и перетащите указатель мыши вниз, чтобы создать в этом столбце новый список, в котором будут сравниваться все данные из двух других списков.
Используйте метод ВПР
ВПР — это функция Excel, которая позволяет пользователям легко извлекать данные. Мы можем использовать эту функцию для сравнения двух списков путем поиска в одном списке на основе значения другого. Чтобы использовать функцию ВПР для сравнения двух списков, выполните следующие действия.
Выберите пустую ячейку, в которой должны отображаться результаты функции ВПР.
Откройте вкладку «Главная» на верхней панели и снова нажмите «Условное форматирование» и «Новое правило».
Введите первую часть формулы ВПР, а именно «=ВПР(», а затем комбинацию букв и цифр, представляющую пустую ячейку.
Включите ячейки для обоих целевых столбцов, а затем количество элементов в каждом.
Добавьте значение «1», чтобы указать, что вы просматриваете только один другой столбец для сравнения списков.
Укажите число ноль, чтобы формула искала точное совпадение, а затем добавьте круглые скобки, чтобы завершить его.
Выберите цвет, чтобы выделить несовпадающие значения.
Нажмите «Ввод», чтобы завершить окончательную формулу, которая может выглядеть примерно так: «=ВПР(B1,A1:A10,1,0)».
Введите эту формулу, чтобы просмотреть выделенные значения.
Скопируйте эту формулу во все ячейки под ней, чтобы найти все значения в списке столбца B.
Пример сравнения двух списков в Excel
Ниже приведен пример того, как вы можете использовать возможность сравнения двух списков в Excel на своей работе:
Молли работает в компании, которая занимается организацией большого сетевого мероприятия. Одна из задач Молли — рассылать напоминания по электронной почте всем, кто еще не ответил на приглашение. В настоящее время у нее есть список всех людей, которых ее компания хочет пригласить, и еще один список всех людей, которые уже ответили. Поскольку этот список содержит сотни имен, Молли потребовалось бы много времени, чтобы найти имя каждого участника, чтобы узнать, ответили ли они.
Вместо этого Молли добавляет каждый список в столбец Excel. Затем она использует формулу ВПР для создания третьего списка, который выводит информацию о том, фигурирует ли кто-либо в списке «Отвеченные». Получив этот список, она может быстро отсортировать новый список в алфавитном порядке, чтобы разделить его на тех, кто ответил, и тех, кто еще не ответил.
Обратите внимание, что ни одна из компаний или продуктов, упомянутых в этой статье, не связана с компанией Indeed.