Skip to content
Сравнение таблиц – это задача, которую в Excel приходится довольно часто решать. Например, у нас есть старый прайс-лист и его новая версия. Нужно просмотреть, цены на какие товары изменились и на сколько.
Давайте для сравнения этих двух таблиц попробуем использовать функцию ВПР. Но отметим при этом, что существуют и другие альтернативные варианты сравнения таблиц, на которых мы также остановимся.
- 1. Используем ВПР, чтобы сравнить две таблицы.
- 2. Сравнение при помощи сводной таблицы.
- 3. Стандартное сравнение.
- 4. Использование формул массива вместе с ВПР.
Итак, вот наши исходные данные.
По количеству строк сразу заметно, что во втором прайсе произошли какие-то изменения среди товаров. Да и цены на отдельные позиции также поменялись. Давайте сравним таблицы, попробуем определить изменения и представить их наиболее наглядно.
Для этого используем несколько способов.
- При помощи функции ВПР для каждого товара из прайс-листа №2 будем искать цену в первом прайс-листе и выводить ее рядом с новой. Так легче будет определить различия.
- Построим сводную таблицу по данным обоих прайс-листов, и там эти отличия увидим.
- Используем стандартную операцию сравнения.
- Применим формулу массива.
1. Используем ВПР, чтобы сравнить две таблицы.
Создадим именованный диапазон B4:C19 и назовем его «прайс1». Так нам будет проще ссылаться на первоначальные данные.
Добавим к новым данным еще одну колонку и назовем ее «Цена старая». Для каждого наименования из прайс-листа №2 найдем соответствующую ему цену в №1.
В Н4 вводим формулу
=ВПР(F4;прайс1;2;0)
и копируем ее вниз по столбцу.
Видим, что кое-где изменилась цена, и в четырех наименованиях формула ВПР возвратила ошибку #Н/Д. Это означает, что ранее этих товаров не было и цену для них обнаружить не удалось.
Чтобы придать результатам сравнения более красивый вид и чтобы можно было определить размер изменения цены, обработаем появившиеся сообщения об ошибке.
Для этого используем функцию ЕСЛИОШИБКА и вместо #Н/Д выведем ноль.
Изменим нашу формулу:
=ЕСЛИОШИБКА(ВПР(F4;прайс1;2;0);0)
Теперь мы можем рассчитать отклонения новой цены от старой.
Можно показать результаты сравнения двух таблиц с использованием ВПР более наглядно и красиво. Давайте результаты сравнения вынесем отдельно.
Согласитесь, что такое сравнение выглядит гораздо аккуратнее и нагляднее.
Формула в J4:
=ЕСЛИ(ЕСЛИОШИБКА(ВПР(F4;прайс1;2;0);0)=G4;»»; ЕСЛИОШИБКА(ВПР(F4;прайс1;2;0);0))
Выглядит сложно и громоздко, но на самом деле все просто. Основа здесь та же, что и ранее: поиск в первой таблице «старой» цены каждого товара из новых данных.
То есть, ключевым является выражение ЕСЛИОШИБКА(ВПР(F4;прайс1;2;0);0).
Если найденное значение равно «новой» цене из ячейки G4, то выводим пустой пробел “”.
Значения смежных ячеек привязаны к этому результату.
Если ячейка J4 пуста, тогда ничего не выводим и в остальных:
Формула в I4:
=ЕСЛИ(J4<>»»;F4;»»)
в К4:
=ЕСЛИ(J4<>»»;G4;»»)
в L4:
=ЕСЛИ(J4<>»»;K4-J4;»»)
В результате заполнены только те строки, в которых произошли изменения цены либо появился новый товар, которого первоначально не было.
Но есть один существенный недостаток в таком сравнении таблиц с использованием функции ВПР. Мы сравнили новые значения и старые, нашли изменения и новые товары. Но если какой-то товар ранее существовал, но теперь отсутствует, то этого мы не заметим. Придется повторить весь процесс в обратную сторону, взяв теперь за базу первую таблицу и сопоставляя ее со второй.
То есть, сравнивать придется в двух направлениях.
Согласитесь, не всегда хочется делать двойную работу.
2. Сравнение при помощи сводной таблицы.
Поскольку структура сравниваемых данных одинакова, то мы можем объединить их. Чтобы различить, откуда взяты какие значения, добавьте еще один столбец и укажите там источник данных – прайс1 или прайс2.
Используя наш предыдущий пример, это можно сделать следующим образом:
Теперь через меню Вставка-Сводная таблица создадим свод, можно на этом же листе для наглядности.
Как видите, сводная таблица выводит в алфавитном порядке все уникальные (неповторяющиеся) значения из обоих прайс-листов и напротив каждого из них проставляет соответствующую цену. Так очень легко можно отследить все изменения.
Чтобы не мешали, итоги по строкам и столбцам можно убрать. Для этого используйте вкладку Конструктор – Общие итоги – Отключить итоги для строк и столбцов.
Это еще один пример того, что у функции ВПР есть весьма достойные альтернативные варианты во многих случаях.
Главный недостаток здесь – данные нужно предварительно подготовить, объединив их в единый массив.
Следует также отметить, что с большими объемами данных сводные таблицы умеют работать гораздо быстрее, чем ВПР.
3. Стандартное сравнение.
Это самые простой и элементарный способ сравнить два столбца Excel на совпадения. Работать таким образом возможно как с числовыми значениями, так и с текстовыми.
Но для этого необходимо, чтобы наши таблицы имели одинаковую структуру. Проще говоря, у них должны быть одинаковые показатели по строкам (к примеру, фиксированный перечень товаров), и одинаковые показатели по столбцам (количество покупок товара).
Для примера сравним два прайса, записав в столбце I условие совпадения цены
=G4=C4
При равенстве мы получим ответ «ИСТИНА», а если совпадения нет, будет «ЛОЖЬ». Копируем из I4 вниз по столбцу.
Этот способ сравнения таблиц – самый элементарный, поэтому останавливаться на нем более не будем.
4. Использование формул массива вместе с ВПР.
Здесь все гораздо сложнее. Вновь вернемся к нашим исходным данным и разместим списки товаров и цен на двух листах рабочей книги: «Прайс1» и «Прайс2».
Создадим из наименований товаров в каждой из таблиц именованный диапазон, как это показано на рисунке.
Назовем их соответственно «прайс_1» и «прайс_2». Так нам легче будет разбираться в формулах.
Результаты сравнения таблиц вынесем также на отдельный лист «Сравнение».
В ячейке A5 запишем формулу
=ЕСЛИОШИБКА(ЕСЛИОШИБКА(ИНДЕКС(прайс_1; ПОИСКПОЗ(0;СЧЁТЕСЛИ(A$4:$A4;прайс_1);0)); ИНДЕКС(прайс_2;ПОИСКПОЗ(0;СЧЁТЕСЛИ(A$4:$A4;прайс_2);0)));»»)
Поскольку это формула массива, то не забудьте завершить ее ввод комбинацией клавиш Ctrl+Shift+Enter.
В результате получим список уникальных (неповторяющихся) значений из всех имеющихся у нас наименований товаров.
Рассмотрим процесс пошагово. Формула последовательно берет значения из списка наименований. Затем при помощи функции СЧЕТЕСЛИ определяется количество совпадений с каждым из значений в ячейках, находящихся выше этого значения. Если результат СЧЕТЕСЛИ равен нулю, значит это наименование ранее не встречалось и можно его занести в список.
Функция ПОИСКПОЗ вычисляет номер позиции этого уникального значения и передает его в функцию ИНДЕКС, которая, в свою очередь, по номеру позиции извлекает значение из массива и записывает его в ячейку.
Поскольку это формула массива, то мы последовательно проходим по всему списку от начала до конца, повторяя все эти операции.
Если первая таблица закончилась, то возникает ошибка. ЕСЛИОШИБКА реагирует на это и начинает таким же образом перебирать значения второй таблицы. Когда и там возникает ошибка, то возвращается пустая строка “”.
Скопируйте эту формулу по столбцу вниз. Список уникальных значений готов.
Затем добавим еще два столбца, в которых при помощи функции ВПР запишем результат сравнения двух таблиц по каждому наименованию товара.
Запишем в В5:
=ЕСЛИ(ЕНД(ВПР(A5;прайс_1;1;0));»Нет»;»Есть»)
Не забудьте, что это тоже формула массива (Ctrl+Shift+Enter).
Можно для наглядности выделить несовпадения цветом, используя условное форматирование.
Напомним, что для этого надо использовать меню Главная – Условное форматирование – Правила выделения ячеек – Текст содержит…
Ну и если значение существует в таблице, то логично было бы его вывести в таблице сравнения.
Заменим в нашей формуле значение «Есть» на функцию ВПР:
ВПР(A5;Прайс1!$B$5:$C$20;2;0)
В итоге наше формула преобразуется к виду:
=ЕСЛИ(ЕНД(ВПР(A5;прайс_1;1;0));»Нет»;ВПР(A5;Прайс1!$B$5:$C$20;2;0))
Аналогично в С5 :
=ЕСЛИ(ЕНД(ВПР(A5;прайс_2;1;0));»Нет»;ВПР(A5;Прайс2!$B$5:$C$23;2;0))
Напомним, что на листах Прайс1 и Прайс2 находятся наши сравниваемые таблицы.
Для сравнения двух таблиц, тем не менее вы можете выбрать любой из этих методов исходя из собственных предпочтений.
Примеры использования функции ВПР:
Поиск ВПР нескольких значений по нескольким условиям — В статье показаны способы поиска (ВПР) нескольких значений в Excel на основе одного или нескольких условий и возврата нескольких результатов в столбце, строке или в отдельной ячейке. При использовании Microsoft… 
Функция ЕСЛИОШИБКА – примеры формул — В статье описано, как использовать функцию ЕСЛИОШИБКА в Excel для обнаружения ошибок и замены их пустой ячейкой, другим значением или определённым сообщением. Покажем примеры, как использовать функцию ЕСЛИОШИБКА с функциями визуального… 
Как объединить две или несколько таблиц в Excel — В этом руководстве вы найдете некоторые приемы объединения таблиц Excel путем сопоставления данных в одном или нескольких столбцах. Как часто при анализе в Excel вся необходимая информация собирается на одном… 
Вычисление номера столбца для извлечения данных в ВПР — Задача: Наиболее простым способом научиться указывать тот столбец, из которого функция ВПР будет извлекать данные. При этом мы не будем изменять саму формулу, поскольку это может привести в случайным ошибкам.… 
4 способа, как сделать левый ВПР в Excel. — Функция ВПР – одна из самых популярных, когда нужно найти и извлечь из таблицы какие-либо данные. Но при этом она имеет один существенный недостаток. Поиск она производит в крайнем левом… 
Почему не работает ВПР в Excel? — Функция ВПР – это очень мощный инструмент поиска. Но если он по каким-то причинам завершился неудачно, то вы получите сообщение об ошибке #Н/Д (#N/A в английском варианте). Давайте постараемся вместе… 
ВПР с несколькими условиями: 5 примеров. — Очень часто наши требования к поиску данных не ограничиваются одним условием. К примеру, нам нужна выручка по магазину за определенный месяц, количество конкретного товара, проданного определенному покупателю и т.д. Обычными…
В этом руководстве показано, как использовать формулу ВПР в Excel для сравнения двух столбцов, чтобы получить общие значения (совпадения) или найти отсутствующие данные (различия).
Когда у вас есть данные в двух разных списках, вам часто может понадобиться сравнить их, чтобы увидеть, какая информация отсутствует в одном из списков или какие данные присутствуют в обоих. Сравнение можно проводить разными способами — какой метод использовать, зависит от того, что именно вы от него хотите.
Если у вас есть два столбца данных и вы хотите узнать, какие точки данных из одного списка существуют в другом списке, вы можете использовать функцию ВПР для сравнения списков на наличие общих значений.
Чтобы построить формулу ВПР в ее базовой форме, вам нужно сделать следующее:
- За искомое_значение (1-й аргумент), используйте самую верхнюю ячейку из списка 1.
- За таблица_массив (2-й аргумент), предоставьте весь Список 2.
- За col_index_num (3-й аргумент), используйте 1, так как в массиве всего один столбец.
- За range_lookup (4-й аргумент), установить FALSE — точное совпадение.
Предположим, у вас есть имена участников в столбце А (Список 1) и имена тех, кто прошел квалификационные раунды в столбце Б (Список 2). Вы хотите сравнить эти 2 списка, чтобы определить, какие участники из группы А пробились на главное событие. Для этого используйте следующую формулу.
=ВПР(A2, $C$2:$C$9, 1, ЛОЖЬ)
Формула помещается в ячейку E2, а затем вы перетаскиваете ее вниз через столько ячеек, сколько элементов в списке 1.
Обратите внимание, что таблица_массив заблокирована абсолютными ссылками ($C$2:$C$9), поэтому она остается неизменной, когда вы копируете формулу в ячейки ниже.
Как видите, имена квалифицированных спортсменов отображаются в столбце E. Для остальных участников появляется ошибка #N/A, указывающая на то, что их имена отсутствуют в списке 2.
Маскировка ошибок #Н/Д
Обсуждаемая выше формула ВПР отлично выполняет свою основную задачу — возвращает общие значения и идентифицирует отсутствующие точки данных. Однако он выдает кучу ошибок #N/A, которые могут сбить с толку неопытных пользователей, заставив их подумать, что с формулой что-то не так.
Чтобы заменить ошибки пустыми ячейками, используйте функцию ВПР в сочетании с функцией ЕСЛИНА или ЕСЛИОШИБКА следующим образом:
=ЕСЛИНА(ВПР(A2, $C$2:$C$9, 1, ЛОЖЬ), «»)
Наша улучшенная формула возвращает пустую строку («») вместо #N/A. Вы также можете вернуть собственный текст, например «Нет в списке 2», «Нет в наличии» или «Недоступно». Например:
=ЕСЛИНА(ВПР(A2, $C$2:$C$9, 1, ЛОЖЬ), «Нет в списке 2»)
Это основная формула ВПР для сравнения двух столбцов в Excel. В зависимости от вашей конкретной задачи его можно изменить, как показано в следующих примерах.
Сравните два столбца на разных листах Excel с помощью ВПР
В реальной жизни столбцы, которые вам нужно сравнить, не всегда находятся на одном листе. В небольшом наборе данных вы можете попробовать обнаружить различия вручную, просмотрев два листа рядом.
Для поиска на другом листе или в книге с формулами необходимо использовать внешнюю ссылку. Лучше всего начать вводить формулу на основном листе, затем переключиться на другой рабочий лист и выбрать список с помощью мыши — соответствующая ссылка на диапазон будет добавлена в формулу автоматически.
Предполагая, что список 1 находится в столбце A на Лист1 и список 2 находится в столбце A на Лист2вы можете сравнить два столбца и найти совпадения, используя эту формулу:
=ЕСЛИНА(ВПР(A2, Лист2!$A$2:$A$9, 1, ЛОЖЬ), «»)
Для получения дополнительной информации см.:
Сравните два столбца и верните общие значения (совпадения)
В предыдущих примерах мы обсуждали формулу ВПР в ее простейшей форме:
=ЕСЛИНА(ВПР(A2, $C$2:$C$9, 1, ЛОЖЬ), «»)
Результатом этой формулы является список значений, которые существуют как в столбцах, так и в пустых ячейках вместо значений, недоступных во втором столбце.
Чтобы получить список общих значений без пропусков, просто добавьте автофильтр к полученному столбцу и отфильтруйте пробелы.
В Excel для Microsoft 365 и Excel 2021, которые поддерживают динамические массивы, вы можете использовать функцию ФИЛЬТР для динамического отсеивания пробелов. Для этого используйте формулу IFNA VLOOKUP в качестве критерия для FILTER:
=ФИЛЬТР(A2:A14, ЕСЛИНА(ВПР(A2:A14, C2:C9, 1, ЛОЖЬ), «»)<>«»)
Обратите внимание, что в этом случае мы отправляем весь список 1 (A2:A14) в искомое_значение аргумент ВПР. Функция сравнивает каждое из значений поиска со списком 2 (C2:C9) и возвращает массив совпадений и ошибок #Н/Д, представляющих отсутствующие значения. Функция IFNA заменяет ошибки пустыми строками и передает результаты функции FILTER, которая отфильтровывает пробелы (<>«») и выводит массив совпадений в качестве конечного результата.
Кроме того, вы можете использовать функцию ISNA, чтобы проверить результат ВПР и отфильтровать элементы, оцениваемые как ЛОЖЬ, т. е. значения, отличные от ошибок #Н/Д:
=ФИЛЬТР(A2:A14, ISNA(ВПР(A2:A14, C2:C9, 1, ЛОЖЬ))=ЛОЖЬ)
Того же результата можно добиться с помощью функции XLOOKUP, которая делает формулу еще проще. Из-за способности XLOOKUP внутренне обрабатывать ошибки #N/A (необязательно если_не_найдено аргумент), мы можем обойтись без оболочки IFNA или ISNA:
=ФИЛЬТР(A2:A14, XLOOKUP(A2:A14, C2:C9, C2:C9,»»)<>«»)
Сравните два столбца и найдите пропущенные значения (различия)
Чтобы сравнить 2 столбца в Excel для поиска различий, вы можете действовать следующим образом:
- Напишите базовую формулу для поиска первого значения из списка 1 (A2) в списке 2 ($C$2:$C$9):
ВПР(A2, $C$2:$C$9, 1, ЛОЖЬ)
- Вложите приведенную выше формулу в функцию ISNA, чтобы проверить выходные данные ВПР на наличие ошибок #Н/Д. В случае ошибки ISNA возвращает TRUE, иначе FALSE:
ISNA(ВПР(A2, $C$2:$C$9, 1, ЛОЖЬ))
- Используйте формулу ISNA VLOOKUP для логической проверки функции ЕСЛИ. Если тест оценивается как ИСТИНА (ошибка #Н/Д), верните значение из списка 1 в той же строке. Если проверка дает FALSE (найдено совпадение в списке 2), возвращается пустая строка.
Полная формула принимает следующий вид:
= ЕСЛИ (ИСНА (ВПР (A2, $C$2:$C$9, 1, ЛОЖЬ)), A2, «»)
Чтобы избавиться от пробелов, примените фильтр Excel, как показано в приведенном выше примере.
В Excel 365 и Excel 2021 вы можете динамически фильтровать список результатов. Для этого просто поместите формулу ISNA VLOOKUP в поле включают аргумент функции ФИЛЬТР:
=ФИЛЬТР(A2:A14, ISNA(ВПР(A2:A14, C2:C9, 1, ЛОЖЬ)))
Другой способ — использовать XLOOKUP для критериев — функция возвращает пустые строки («») для отсутствующих точек данных, и вы фильтруете значения в списке 1, для которых функция XLOOKUP вернула пустые строки (=»»):
=ФИЛЬТР(A2:A14, XLOOKUP(A2:A14, C2:C9, C2:C9,»»)=»»)
Формула ВПР для определения совпадений и различий между двумя столбцами
Если вы хотите добавить в первый список текстовые метки, указывающие, какие значения доступны во втором списке, а какие нет, используйте формулу ВПР вместе с функциями ЕСЛИ и ЕСНА/ЕОШИБКА.
Например, чтобы идентифицировать имена, которые находятся в обоих столбцах A и D, а также те, которые находятся только в столбце A, используется следующая формула:
=ЕСЛИ(ISNA(ВПР(A2, $D$2:$D$9, 1, ЛОЖЬ)), «Не квалифицировано», «Квалифицировано»)
Здесь функция ISNA перехватывает ошибки #Н/Д, сгенерированные функцией ВПР, и передает этот промежуточный результат функции ЕСЛИ, чтобы она возвращала указанный текст для ошибок и другой текст для успешных поисков.
В этом примере мы использовали метки «Не квалифицировано», которые подходят для нашего примера набора данных. Вы можете заменить их на «Нет в списке 2», «Список 2», «Недоступно» или любые другие метки, которые посчитаете нужными.
Эту формулу лучше всего вставить в столбец, примыкающий к списку 1, и скопировать в столько ячеек, сколько элементов в вашем списке.
Еще один способ определить совпадения и различия в двух столбцах — использовать функцию ПОИСКПОЗ:
=ЕСЛИ(ИСНА(ПОИСКПОЗ(A2, $D$2:$D$9, 0)), «Нет в списке 2», «В списке 2»)
Сравните 2 столбца и верните значение из третьего
При работе с таблицами, содержащими связанные данные, иногда может потребоваться сравнить два столбца в двух разных таблицах и вернуть совпадающее значение из другого столбца. Фактически, это основное использование функции ВПР, цель, для которой она была разработана.
Например, чтобы сравнить имена в столбцах A и D в двух таблицах ниже и вернуть время из столбца E, используйте следующую формулу:
=ВПР(A3, $D$3:$E$10, 2, ЛОЖЬ)
Чтобы скрыть ошибки #Н/Д, используйте проверенное решение — функцию IFNA:
=ЕСЛИНА(ВПР(A3, $D$3:$E$10, 2, ЛОЖЬ), «»)
Вместо пробелов вы можете вернуть любой текст для отсутствующих точек данных — просто введите его в последнем аргументе. Например:
=IFNA(ВПР(A3, $D$3:$E$10, 2, ЛОЖЬ), «Недоступно»)
Помимо ВПР, эту задачу можно решить с помощью нескольких других функций поиска.
Лично я бы полагался на более гибкую формулу ИНДЕКС ПОИСКПОЗ:
=ЕСЛИНА(ИНДЕКС($E$3:$E$10, ПОИСКПОЗ(A3, $D$3:$D$10, 0)), «»)
Или используйте современный преемник ВПР — функцию XLOOKUP, доступную в Excel 365 и Excel 2021:
=XLOOKUP(A3, $D$3:$D$10, $E$3:$E$10, «»)
Чтобы получить имена квалифицированных участников из группы A и их результаты, просто отфильтруйте пустые ячейки в столбце B:
=ФИЛЬТР(A3:B15, B3:B15″»)
Инструменты сравнения
Если вы часто сравниваете файлы или данные в Excel, эти интеллектуальные инструменты, включенные в наш Ultimate Suite, могут значительно сэкономить ваше время!
Сравните таблицы — быстрый способ найти дубликаты (совпадения) и уникальные значения (различия) в любых двух наборах данных, таких как столбцы, списки или таблицы.
Сравните два листа — найдите и выделите различия между двумя листами.
Сравните несколько листов — найдите и выделите различия сразу на нескольких листах.
Практическая рабочая тетрадь для скачивания
ВПР в Excel для сравнения столбцов — примеры (файл .xlsx)
Вас также могут заинтересовать
Функция ВПР в Excel позволяет данные из одной таблицы переставить в соответствующие ячейки второй. Ее английское наименование – VLOOKUP.
Очень удобная и часто используемая. Т.к. сопоставить вручную диапазоны с десятками тысяч наименований проблематично.
Как пользоваться функцией ВПР в Excel
Допустим, на склад предприятия по производству тары и упаковки поступили материалы в определенном количестве.
Стоимость материалов – в прайс-листе. Это отдельная таблица.
Необходимо узнать стоимость материалов, поступивших на склад. Для этого нужно подставит цену из второй таблицы в первую. И посредством обычного умножения мы найдем искомое.
Алгоритм действий:
- Приведем первую таблицу в нужный нам вид. Добавим столбцы «Цена» и «Стоимость/Сумма». Установим денежный формат для новых ячеек.
- Выделяем первую ячейку в столбце «Цена». В нашем примере – D2. Вызываем «Мастер функций» с помощью кнопки «fx» (в начале строки формул) или нажав комбинацию горячих клавиш SHIFT+F3. В категории «Ссылки и массивы» находим функцию ВПР и жмем ОК. Данную функцию можно вызвать перейдя по закладке «Формулы» и выбрать из выпадающего списка «Ссылки и массивы».
- Откроется окно с аргументами функции. В поле «Искомое значение» — диапазон данных первого столбца из таблицы с количеством поступивших материалов. Это те значения, которые Excel должен найти во второй таблице.
- Следующий аргумент – «Таблица». Это наш прайс-лист. Ставим курсор в поле аргумента. Переходим на лист с ценами. Выделяем диапазон с наименованием материалов и ценами. Показываем, какие значения функция должна сопоставить.
- Чтобы Excel ссылался непосредственно на эти данные, ссылку нужно зафиксировать. Выделяем значение поля «Таблица» и нажимаем F4. Появляется значок $.
- В поле аргумента «Номер столбца» ставим цифру «2». Здесь находятся данные, которые нужно «подтянуть» в первую таблицу. «Интервальный просмотр» — ЛОЖЬ. Т.к. нам нужны точные, а не приблизительные значения.
Нажимаем ОК. А затем «размножаем» функцию по всему столбцу: цепляем мышью правый нижний угол и тянем вниз. Получаем необходимый результат.
Теперь найти стоимость материалов не составит труда: количество * цену.
Функция ВПР связала две таблицы. Если поменяется прайс, то и изменится стоимость поступивших на склад материалов (сегодня поступивших). Чтобы этого избежать, воспользуйтесь «Специальной вставкой».
- Выделяем столбец со вставленными ценами.
- Правая кнопка мыши – «Копировать».
- Не снимая выделения, правая кнопка мыши – «Специальная вставка».
- Поставить галочку напротив «Значения». ОК.
Формула в ячейках исчезнет. Останутся только значения.
Быстрое сравнение двух таблиц с помощью ВПР
Функция помогает сопоставить значения в огромных таблицах. Допустим, поменялся прайс. Нам нужно сравнить старые цены с новыми ценами.
- В старом прайсе делаем столбец «Новая цена».
- Выделяем первую ячейку и выбираем функцию ВПР. Задаем аргументы (см. выше). Для нашего примера: . Это значит, что нужно взять наименование материала из диапазона А2:А15, посмотреть его в «Новом прайсе» в столбце А. Затем взять данные из второго столбца нового прайса (новую цену) и подставить их в ячейку С2.
Данные, представленные таким образом, можно сопоставлять. Находить численную и процентную разницу.
Функция ВПР в Excel с несколькими условиями
До сих пор мы предлагали для анализа только одно условие – наименование материала. На практике же нередко требуется сравнить несколько диапазонов с данными и выбрать значение по 2, 3-м и т.д. критериям.
Таблица для примера:
Предположим, нам нужно найти, по какой цене привезли гофрированный картон от ОАО «Восток». Нужно задать два условия для поиска по наименованию материала и по поставщику.
Дело осложняется тем, что от одного поставщика поступает несколько наименований.
- Добавляем в таблицу крайний левый столбец (важно!), объединив «Поставщиков» и «Материалы».
- Таким же образом объединяем искомые критерии запроса:
- Теперь ставим курсор в нужном месте и задаем аргументы для функции: . Excel находит нужную цену.
Рассмотрим формулу детально:
- Что ищем.
- Где ищем.
- Какие данные берем.
Функция ВПР и выпадающий список
Допустим, какие-то данные у нас сделаны в виде раскрывающегося списка. В нашем примере – «Материалы». Необходимо настроить функцию так, чтобы при выборе наименования появлялась цена.
Сначала сделаем раскрывающийся список:
- Ставим курсор в ячейку Е8, где и будет этот список.
- Заходим на вкладку «Данные». Меню «Проверка данных».
- Выбираем тип данных – «Список». Источник – диапазон с наименованиями материалов.
- Когда нажмем ОК – сформируется выпадающий список.
Теперь нужно сделать так, чтобы при выборе определенного материала в графе цена появлялась соответствующая цифра. Ставим курсор в ячейку Е9 (где должна будет появляться цена).
- Открываем «Мастер функций» и выбираем ВПР.
- Первый аргумент – «Искомое значение» — ячейка с выпадающим списком. Таблица – диапазон с названиями материалов и ценами. Столбец, соответственно, 2. Функция приобрела следующий вид: .
- Нажимаем ВВОД и наслаждаемся результатом.
Изменяем материал – меняется цена:
Скачать пример функции ВПР в Excel
Так работает раскрывающийся список в Excel с функцией ВПР. Все происходит автоматически. В течение нескольких секунд. Все работает быстро и качественно. Нужно только разобраться с этой функцией.
Колонки сравнивают для того, чтобы, например, в отчетах не было дубликатов. Или, наоборот, для проверки правильности заполнения — с поиском непохожих значений. И проще всего выполнять сравнение двух столбцов на совпадение в Excel — для этого есть 6 способов.
1 Сравнение с помощью простого поиска
При наличии небольшой по размеру таблицы заниматься сравнением можно практически вручную. Для этого достаточно выполнить несколько простых действий.
- Перейти на главную вкладку табличного процессора.
- В группе «Редактирование» выбрать пункт поиска.
- Выделить столбец, в котором будет выполняться поиск совпадений — например, второй.
- Вручную задавать значения из основного столбца (в данном случае — первого) и искать совпадения.
Если значение обнаружено, результатом станет выделение нужной ячейки. Однако с помощью такого способа можно работать только с небольшими столбцами. И, если это просто цифры, так можно сделать и без поиска — определяя совпадения визуально. Впрочем, если в колонках записаны большие объемы текста, даже такая простая методика позволит упростить поиск точного совпадения.
2 Операторы ЕСЛИ и СЧЕТЕСЛИ
Еще один способ сравнения значений в двух столбцах Excel подходит для таблиц практически неограниченного размера. Он основан на применении условного оператора ЕСЛИ и отличается от других методик тем, что для анализа совпадений берется только указанная в формуле часть, а не все значения массива. Порядок действий при использовании методики тоже не слишком сложный и подойдет даже для начинающего пользователя Excel.
- Сравниваемые столбцы размещаются на одном листе. Не обязательно, чтобы они находились рядом друг с другом.
- В третьем столбце, например, в ячейке J6, ввести формулу такого типа: =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(H6;$I$6:$I$14;0));»;H6)
- Протянуть формулу до конца столбца.
Результатом станет появление в третьей колонке всех совпадающих значений. Причем H6 в примере — это первая ячейка одного из сравниваемых столбцов. А диапазон $I$6:$I$14 — все значения второй участвующей в сравнении колонки. Функция будет последовательно сравнивать данные и размещать только те из них, которые совпали. Однако выделения обнаруженных совпадений не происходит, поэтому методика подходит далеко не для всех ситуаций.
Еще один способ предполагает поиск не просто дубликатов в разных колонках, но и их расположения в пределах одной строки. Для этого можно применить все тот же оператор ЕСЛИ, добавив к нему еще одну функцию Excel — И. Формула поиска дубликатов для данного примера будет следующей: =ЕСЛИ(И(H6=I6); «Совпадают»; «») — ее точно так же размещают в ячейке J6 и протягивают до самого низа проверяемого диапазона. При наличии совпадений появится указанная надпись (можно выбрать «Совпадают» или «Совпадение»), при отсутствии — будет выдаваться пустота.
Тот же способ подойдет и для сравнения сразу большого количества колонок с данными на точное совпадение не только значения, но и строки. Для этого применяется уже не оператор ЕСЛИ, а функция СЧЕТЕСЛИ. Принцип написания и размещения формулы похожий.
Она имеет вид =ЕСЛИ(СЧЕТЕСЛИ($H6:$J6;$H6)=3; «Совпадают»;») и должна размещаться в верхней части следующего столбца с протягиванием вниз. Однако в формулу добавляется еще количество сравниваемых колонок — в данном случае, три.
Если поставить вместо тройки двойку, результатом будет поиск только тех совпадений с первой колонкой, которые присутствуют в одном из других столбцов. Причем, тройные дубликаты формула проигнорирует. Так же как и совпадения второй и третьей колонки.
3 Формула подстановки ВПР
Принцип действия еще одной функции для поиска дубликатов напоминает первый способ использованием оператора ЕСЛИ. Но вместо ПОИСКПОЗ применяется ВПР, которую можно расшифровать как «Вертикальный Просмотр». Для сравнения двух столбцов из похожего примера следует ввести в верхнюю ячейку (J6) третьей колонки формулу =ВПР(H6;$I$6:$I$15;1;0) и протянуть ее в самый низ, до J15.
С помощью этой функции не просто просматриваются и сравниваются повторяющиеся данные — результаты проверки устанавливаются четко напротив сравниваемого значения в первом столбце. Если программа не нашла совпадений, выдается #Н/Д.
4 Функция СОВПАД
Достаточно просто выполнить в Эксель сравнение двух столбцов с помощью еще двух полезных операторов — распространенного ИЛИ и встречающейся намного реже функции СОВПАД. Для ее использования выполняются такие действия:
- В третьем столбце, где будут размещаться результаты, вводится формула =ИЛИ(СОВПАД(I6;$H$6:$H$19))
- Вместо нажатия Enter нажимается комбинация клавиш Ctr + Shift + Enter. Результатом станет появление фигурных скобок слева и справа формулы.
- Формула протягивается вниз, до конца сравниваемой колонки — в данном случае проверяется наличие данных из второго столбца в первом. Это позволит изменяться сравниваемому показателю, тогда как знак $ закрепляет диапазон, с которым выполняется сравнение.
Результатом такого сравнения будет вывод уже не найденного совпадающего значения, а булевой переменной. В случае нахождения это будет «ИСТИНА». Если ни одного совпадения не было обнаружено — в ячейке появится надпись «ЛОЖЬ».
Стоит отметить, что функция СОВПАД сравнивает и числа, и другие виды данных с учетом верхнего регистра. А одним из самых распространенных способом использования такой формулы сравнения двух столбцов в Excel является поиска информации в базе данных. Например, отдельных видов мебели в каталоге.
5 Сравнение с выделением совпадений цветом
В поисках совпадений между данными в 2 столбцах пользователю Excel может понадобиться выделить найденные дубликаты, чтобы их было легко найти. Это позволит упростить поиск ячеек, в которых находятся совпадающие значения. Выделять совпадения и различия можно цветом — для этого понадобится применить условное форматирование.
Порядок действий для применения методики следующий:
- Перейти на главную вкладку табличного процессора.
- Выделить диапазон, в котором будут сравниваться столбцы.
- Выбрать пункт условного форматирования.
- Перейти к пункту «Правила выделения ячеек».
- Выбрать «Повторяющиеся значения».
- В открывшемся окне указать, как именно будут выделяться совпадения в первой и второй колонке. Например, красным текстом, если цвет остальных сообщений стандартный черный. Затем указать, что выделяться будут именно повторяющиеся ячейки.
Теперь можно снять выделение и сравнить совпадающие значения, которые будут заметно отличаться от остальной информации. Точно так же можно выделить, например, и уникальную информацию. Для этого следует выбрать вместо «повторяющихся» второй вариант — «уникальные».
6 Надстройка Inquire
Начиная с версий MS Excel 2013 табличный процессор позволяет воспользоваться еще одной методикой — специальной надстройкой Inquire. Она предназначена для того, чтобы сравнивать не колонки, а два файла .XLS или .XLSX в поисках не только совпадений, но и другой полезной информации.
Для использования способа придется расположить столбцы или целые блоки информации в разных книгах и удалить все остальные данные, кроме сравниваемой информации. Кроме того, для проверки необходимо, чтобы оба файла были одновременно открытыми.
Процесс использования надстройки включает такие действия:
- Перейти к параметрам электронной таблицы.
- Выбрать сначала надстройки, а затем управление надстройками COM.
- Отметить пункт Inquire и нажать «ОК».
- Перейти к вкладке Inquire.
- Нажать на кнопку Compare Files, указать, какие именно файлы будут сравниваться, и выбрать Compare.
- В открывшемся окне провести сравнения, используя показанные совпадения и различия между данными в столбцах.
У каждого варианта сравнения — свое цветовое решение. Так, зеленым цветом на примере выделены отличия. У совпадающих данных отсутствует выделение. А сравнение расчетных формул показало, что результаты отличаются все — и для выделения использован бирюзовый цвет.
Читайте также:
- 5 программ для совместной работы с документами
-
Как в Экселе протянуть формулу по строке или столбцу: 5 способов
-
- Как пользоваться ВПР в Excel
- Особенности работы функции ВПР
- Как использовать формулу ВПР в Excel для сравнения двух таблиц
- Почему не работает ВПР в Excel
- Когда Интервальный просмотр может быть = 1
Функция ВПР в Excel (в английской версии VLOOKUP) используется для подтягивания значений в таблицу из другой таблицы при условии совпадения критерия поиска. Расшифровывается ВПР как “вертикальный поиск результата”. В этой статье разберемся, как сделать ВПР в Excel, ниже будет понятная инструкция.
ВПР ищет первое совпадение по критерию в левом столбца указанной таблицы и подтягивает значение из указанного столбца этой таблицы.
Как сделать ВПР в Excel понятная инструкция
Чтобы понять, как пользоваться функцией ВПР в Excel, разберем ее синтаксис:
=ВПР(искомое значение; таблица; номер столбца; [интервальный просмотр])
Искомое значение — значение или ссылка на ячейку, по которой будем искать значения в другой таблице. Это критерий поиска.
Таблица — ссылка на таблицу, в которой будет искать.
Номер столбца — порядковый номер столбца относительно самого левого столбца из выделенного диапазона поиска.
Интервальный просмотр — указывается 0 (точный поиск) или 1 (приблизительный поиск)
Рассмотрим использование ВПР на примере:
Есть таблица с количеством гостей в отеле, необходимо понять, есть ли зависимость количества проживающих от того, насколько тепло в этом городе. Для этого нужно подтянуть значения температуры воздуха из таблицы справочника.
В качестве критерия будет использоваться название месяца.
Добавим в первую таблицу столбец Температура и напишем следующую формулу:
В примере формула ВПР ищет значение Январь из первой таблице в крайнем левом столбце второй таблицы. И подтягивает совпадающее значение из указанного столбца (в данном случае, из второго столбца).
Аргумент Интервальный просмотр задаем 0, чтобы функция искала точное совпадение.
Запомним, что в абсолютном большинстве случаем Интервальный просмотр ставим = 0.
Кстати, если не указать интервальный просмотр совсем, то формула не выдаст ошибку, т.к. этот аргумент необязательный. Но результат будет неожиданным, поскольку в этом случае ВПР считает не указанный Интервальный просмотр равным 1 (а это приблизительный поиск).
Не забываем добавить абсолютные ссылки (значки доллара $) к диапазону, иначе диапазон “съедет” при копировании формулы.
Итак, мы получили в нашу таблицу значение из таблицы-справочника — подтянули по критерию названия месяца. Осталось только протянуть или скопировать формулу во все ячейки этого столбца.
Особенности работы функции ВПР
Несмотря на простоту использования формулы ВПР в Excel, у нее есть ряд особенностей, которые нужно учитывать в работе.
-
Функция ВПР ищет совпадение строго в крайнем левом столбце выделенной таблицы.
Ключевые слова здесь — “выделенной” таблицы. Чтобы понять, что это значит, добавим к таблице-справочнику еще один столбец слева (Год).
Чтобы сделать такой же ВПР, как в предыдущем примере (по критерию Месяц), нужно выделять столбцы H:I, хотя таблица содержит столбцы G:I. Иначе Excel будет искать в крайнем левом столбце, в котором содержится не месяц, а год.
Вывод: аргумент Таблица в данном случае — это выделенный диапазон ячеек, а не то, что мы видим “глазами” и считаем таблицей.
-
ВПР в Excel ищет первое совпадение с указанным критерием.
Для примера добавим еще одну строку в таблицу-справочник.
Теперь в таблице содержится два значения температуры для месяца Январь.
Но при этом, даже если мы изменим интервал таблицы для поиска, затянув в нее новую строку Январь 2023, то значение, которое подтянула ВПР, не изменится.
ВПР взяло первое совпадение.
-
В качестве критерия поиска можно использовать не только ссылку на ячейку (как в предыдущих примерах), но и ввести значение напрямую в формулу. Это не очень удобно, но возможно.
Текстовые значения необходимо вводить в кавычках, числовые — просто число.
Пример функции ВПР с текстовым критерием поиска
Пример функции ВПР с числовым критерием поиска
Как использовать формулу ВПР в Excel для сравнения двух таблиц
Функцию ВПР часто используют для сравнения двух таблиц.
-
Сверка двух таблиц по пропускам значений
Для примера удалим из первой таблицы две строки. Наша задача — понять, каких месяцев не хватает в первой таблице.
Для этого во вторую таблицу при помощи функции ВПР подтянем значения из первой таблицы Excel.
Значения можно подтягивать любые, например, количество проживающих. В данном случае сами значения не важны, важны пропуски.
Значения по месяцам Июнь и Октябрь подтянулись с ошибкой #Н/Д (нет данных) — это значит, что в исходной таблице эти месяцы пропущены.
-
Сверка числовых значений
Предположим, у нас есть статистика по количеству проживающих в другом отеле сети по месяцам и годам. Сравним с первым отелем. Для этого добавим новый столбец, и в нем сделаем ВПР по критерию месяца.
А теперь обратите внимание, что месяц Январь повторяется в обоих таблицах (разный год). Но для второго значения Январь в первую таблицу подтянулись цифры из первого значения Январь второй таблицы.
Это произошло потому, что ВПР в Excel ищет первое совпадение.
Выходы из ситуации:
- убедиться, что значения в столбце поиски во второй таблице уникальные
- использовать функцию СУММЕСЛИ или СУММЕСЛИМН
- использовать ВПР по нескольким критериям
Почему не работает ВПР в Excel
То, что ВПР не работает, можно понять по появлению ошибки #Н/Д.
Не работает ВПР — значит, что функция не находит значение. Причины:
- Ошибка в написании критерия. Часто в текст закрадываются лишние пробелы или латинские буквы вместо кириллицы, и тогда появится ошибка.
В данном примере в конце слова Январь стоит невидимый пробел. А поскольку функция ВПР в Excel ищет точное совпадение Январь + пробел в конце и просто Январь — это два разных значения.
- Съехал или “не дотянут” диапазон таблицы для поиска.
В данном случае значение Январь не попало в диапазон поиска, поэтому появилась ошибка #Н/Д.
Также часто при появлении новых строк в таблице-источнике забывают исправлять диапазон в формуле ВПР (он как бы не дотягивается до конца таблицы), тогда тоже будет ошибка.
Когда Интервальный просмотр может быть = 1
Аргумент Интервальный просмотр в ВПР может принимать только два значения — 0 или 1 (если его не указать, то по умолчанию считается 1).
Во всех предыдущих примерах мы использовали интервальный просмотр = 0. Это подходит для большинства ситуаций на практике.
Но иногда необходимо использовать интервальный просмотр = 1. Как правило, он используется для числовых критериев, чтобы подтянуть значение из определенного диапазона критериев.
Более подробно об этом — в статье
В этой статье мы узнали, как сделать ВПР в Excel, надеюсь, это была понятная инструкция.
Сообщество Excel Analytics | обучение Excel
Канал на Яндекс.Дзен
Вам может быть интересно: