Поиск по двум столбцам Excel

Обновлено: 21.11.2024

Я часто получаю запрос: «Как сравнить два столбца в Excel?».

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

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

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

Если вы найдете это полезным, передайте его другим пользователям Excel.

Это руководство охватывает:

Обратите внимание, что методы сравнения столбцов, показанные в этом руководстве, не единственные.

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

Если вы считаете, что к этому руководству можно что-то добавить, дайте мне знать в разделе комментариев

Сравнить два столбца для точного совпадения строк

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

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

Ниже приведен набор данных, в котором мне нужно проверить, совпадает ли имя в столбце A с именем в столбце B или нет.

Если есть совпадение, мне нужен результат как «ИСТИНА», а если не совпадает, мне нужен результат как «ЛОЖЬ».

Приведенная ниже формула сделает это:

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

Если вы хотите получить более наглядный результат, вы можете использовать простую формулу ЕСЛИ, чтобы возвращать «Совпадение», когда имена совпадают, и «Несоответствие», когда имена разные.

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

В приведенной выше формуле «IBM» и «ibm» будут считаться двумя разными именами, а приведенная выше формула вернет «Несоответствие».

Пример: выделение строк с совпадающими данными

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

Вот как это сделать:

При этом будут выделены все ячейки с одинаковыми именами в каждой строке.

Сравнить два столбца и выделить совпадения

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

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

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

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

Как показано ниже:

Обратите внимание, что список в столбце A больше, чем в B. Также некоторые имена присутствуют в обоих списках, но не в одной строке (например, IBM, Adobe, Walmart).

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

Вот как это сделать:

Вышеуказанные шаги дадут вам результат, показанный ниже.

Примечание. Правило дублирования условного форматирования не чувствительно к регистру. Таким образом, «Apple» и «apple» считаются одним и тем же и будут выделены как повторяющиеся.

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

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

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

Сравнить два столбца и найти недостающие точки данных

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

Предположим, у вас есть набор данных, как показано ниже, и вы хотите определить компании, которые присутствуют в столбце A, но не в столбце B,

Для этого я могу использовать следующую формулу ВПР.

Функция ЕОШИБКА возвращает ИСТИНА, если результат ВПР является ошибкой, и ЛОЖЬ, если это не ошибка.

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

Вы также можете использовать функцию ПОИСКПОЗ, чтобы сделать то же самое;

Примечание. Лично я предпочитаю использовать функцию сопоставления (или комбинацию ИНДЕКС/ПОИСКПОЗ) вместо функции ВПР. Я нахожу его более гибким и мощным. Вы можете прочитать разницу между Vlookup и Index/Match здесь.

Сравнить два столбца и получить совпадающие данные

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

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

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

Ниже приведена формула, которая это сделает:

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

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

Для получения правильного результата этим формулам поиска требуется точное совпадение. В функциях ВПР или ПОИСКПОЗ есть вариант приблизительного совпадения, но его нельзя использовать здесь.

Предположим, у вас есть набор данных, как показано ниже. Обратите внимание, что в столбце 2 есть неполные названия (например, JPMorgan вместо JPMorgan Chase и Exxon вместо ExxonMobil).

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

В этом случае правильный результат даст следующая формула:

В приведенном выше примере звездочка (*) — это подстановочный знак, который может представлять любое количество символов. Когда искомое значение окружено им с обеих сторон, любое значение в столбце 1, содержащее искомое значение в столбце 2, будет рассматриваться как совпадение.

Например, *Exxon* будет соответствовать ExxonMobil (поскольку * может представлять любое количество символов).

Функция Excel VLOOKUP позволяет просматривать по одному столбцу за раз. А что, если вам нужно вернуть совпадающие значения из двух или более столбцов? Это можно сделать довольно легко.

Excel vlookup по нескольким столбцам — логика поиска

У нас есть набор данных, импортированный из BigQuery в Excel с помощью Coupler.io, решения для автоматического экспорта данных из нескольких приложений и источников.

Узнайте больше о Coupler.io и ознакомьтесь с другими интеграциями Microsoft Excel, доступными для экспорта данных по расписанию.

Наша цель — узнать автомобиль, цвет и страну для определенного имени пользователя. Для этого нам нужно найти эти три столбца.

Базовый формат ВПР возвращает только одно значение. Но небольшая настройка сделает работу за нас.

Синтаксис ВПР Excel для нескольких столбцов

"lookup_value" – значение, которое вы хотите найти по вертикали. Кавычки не используются, если вы ссылаетесь на ячейку в качестве значения поиска.
диапазон искомого_диапазона – диапазон данных, в котором следует искать "искомое_значение" и совпадающее значение.
номера столбцов, содержащих возвращаемые совпадающие значения. Например, если ваш диапазон — D7:G18, D — первый столбец, E — второй и так далее. Вы можете изменить порядок столбцов на тот, который вам нужен, например,
Чтобы вернуть ближайшее совпадение, укажите TRUE; чтобы вернуть точное совпадение, укажите FALSE. Ближайшее соответствие (TRUE) установлено по умолчанию.

Как искать несколько столбцов в Excel — пример

Вот формула ВПР, которая у нас есть:

Но вы не можете просто вставить эту формулу в ячейку J2 и нажать Enter. Это вернет только одно значение. Что вам нужно сделать, так это выбрать вертикальный массив, соответствующий количеству столбцов в вашей формуле ВПР. В нашем случае нам нужно выделить три ячейки. После этого вы можете вставить формулу в строку формул и нажать Ctrl+Shift+Enter для Windows (Command+Return для Mac) — это применит формулу массива в Excel.

Excel Online vlookup для возврата нескольких столбцов

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

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

Массив Excel vlookup для нескольких столбцов в разных книгах

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

Например, у нас есть две книги

Чтобы найти диапазон в отдельной книге, выполните следующие действия:

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

[dataset.xlsx] — имя электронной таблицы
набор данных! – название рабочего листа
$A:$F – заблокированный диапазон для поиска.

Чтобы завершить формулу ВПР, нам нужно ввести номера столбцов для поиска и ввести ЛОЖЬ, чтобы получить точное совпадение. Закройте квадратную скобку и нажмите Ctrl+Shift+Enter для Windows (Command+Return для Mac)

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

Excel vlookup сравнивает несколько столбцов

Мы уже писали в блоге о том, как сравнивать два столбца в Excel с помощью функции ВПР. Давайте посмотрим, как мы можем сравнить три столбца.

В наборе данных у нас есть три столбца: Старые пользователи, Новые пользователи и Ожидаемые пользователи. ВПР поможет нам сравнить значения из этих столбцов, чтобы определить значения, присутствующие во всех столбцах.

Логика формулы следующая:

Сначала нам нужно сравнить два столбца и найти совпадения.
Затем нам нужно сравнить третий столбец с найденными совпадениями.

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

Чтобы реализовать формулу, выберите массив, который будет не меньше, чем массивы в вашей формуле ВПР, вставьте следующую формулу в строку формул и нажмите Ctrl+Shift+Enter для Windows ( Command+Return для Mac):

Также было бы здорово исключить из массива пустые ячейки. Вы можете сделать это с помощью функции UNIQUE, которая доступна в Excel 365 или Excel Online. Другой способ — применить эту расширенную формулу массива:

Вы можете использовать эту формулу массива для своих случаев, заменив H2:H66 своим диапазоном.

Для поиска значения путем сопоставления нескольких столбцов можно использовать формулу массива, основанную на нескольких функциях, включая МУМНОЖ, ТРАНСП, СТОЛБЦ и ИНДЕКС. В показанном примере формула в H4 выглядит так:

где «имена» — это именованный диапазон C4:E7, а «группы» — это именованный диапазон B4:B7. Формула возвращает группу, к которой принадлежит каждое имя.

Примечание. Это формула массива, и ее необходимо вводить с клавишей Control Shift.

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

где имена — это именованный диапазон C4:E7. Это генерирует результат TRUE/FALSE для каждого значения в данных, а двойное отрицательное значение приводит значения TRUE и FALSE к 1 и 0, чтобы получить такой массив:

Этот массив состоит из 4 строк и 3 столбцов, что соответствует структуре "имен".

Второй массив создается с помощью этого выражения:

Функция COLUMN используется для создания числового массива с 3 столбцами и 1 строкой, а функция TRANSPOSE преобразует этот массив в 1 столбец и 3 строки. Возведение результата в нулевую степень просто преобразует все числа в массиве в 1. Затем функция МУМНОЖ используется для выполнения матричного умножения:

и результат передается функции ПОИСКПОЗ в качестве аргумента массив с 1 в качестве значения поиска:

Функция ПОИСКПОЗ возвращает позицию первого совпадения, которая соответствует первой совпадающей строке, удовлетворяющей заданным критериям. Это передается в ИНДЕКС как номер строки с именованным диапазоном «группы» в качестве массива:

Наконец, ИНДЕКС возвращает "Медведь", группу, к которой принадлежит Адам.

Литерал содержит для критериев

Чтобы проверить конкретные текстовые значения вместо точного совпадения, вы можете использовать функции ISNUMBER и SEARCH вместе. Например, для сопоставления ячеек, содержащих слово «яблоко», вы можете использовать:

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

Рисунок 1. Окончательный результат формулы

Синтаксис формулы ИНДЕКС

Общая формула для функции ИНДЕКС:

=INDEX(массив, номер_строки, номер_столбца)

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

Синтаксис формулы ПОИСКПОЗ

Общая формула для функции ПОИСКПОЗ:

=ПОИСКПОЗ(искомое_значение, искомый_массив, [тип_сопоставления])

lookup_value — значение, которое мы хотим найти в lookup_array
lookup_array — массив, в котором мы хотим найти значение
[match_type] — тип соответствия. Ставим 0, так как хотим точного совпадения.

Настройка наших данных для формулы

Наша таблица состоит из 4 столбцов: «Номер доставки» (столбец B), «Идентификатор продукта» (столбец C), «Дата доставки» (столбец D) и «Сумма» (столбец E).В ячейке H4 мы хотим получить сумму из таблицы, где Номер доставки равен H2, а Product ID равен H3.

Рисунок 2. Данные, которые мы будем использовать в примере

Выполнение ИНДЕКС и ПОИСКПОЗ с двумя критериями

Мы хотим получить сумму из справочной таблицы B3:E9, где номер доставки — 1004, а идентификатор продукта — 101.

Формула выглядит следующим образом:

Параметр искомое_значение функции ПОИСКПОЗ равен 1. Искомый_массив равен (H2=B3:B9) * (H3=C3:C9). Ячейки H2 и H3 сравниваются с массивами, и в результате возвращаются массивы с 0 и 1. Match_type равен 0, что означает приблизительное совпадение. Результатом функции ПОИСКПОЗ является параметр row_num функции ИНДЕКС. Массив представляет собой диапазон E3:E9.

Чтобы применить формулу, нам нужно выполнить следующие шаги:

Выберите ячейку H5 и нажмите на нее.
Вставьте формулу: =ИНДЕКС(E3:E9,ПОИСКПОЗ(1,(H2=B3:B9)*(H3=C3:C9),0))
Нажмите одновременно клавиши Ctrl, SHIFT и Enter, чтобы преобразовать формулу в функцию массива.

Когда мы оцениваем все три сравнения в функции ПОИСКПОЗ, lookup_array выглядит так, как показано на рис. 3.

Рисунок 3. Вычисление формулы

Поэтому функция ПОИСКПОЗ возвращает 4.

Рис. 4. Использование формул ИНДЕКС и ПОИСКПОЗ

Четвертая строка — это параметр row_num функции ИНДЕКС. Наконец, сумма из третьей строки lookup_table является результатом функции. Результат в ячейке H4 – 650 долларов США.

В большинстве случаев проблема, которую вам нужно решить, будет более сложной, чем простое применение формулы или функции. Если вы хотите сэкономить часы исследований и разочарований, попробуйте наш онлайн-сервис Excelchat! Наши эксперты по Excel доступны круглосуточно и без выходных, чтобы ответить на любой вопрос, который у вас может возникнуть. Мы гарантируем подключение в течение 30 секунд и индивидуальное решение в течение 20 минут.

Читайте также: