Как сделать ВПР в электронной таблице Excel

Дядя Влад

Вы хотите проверить конкретное значение в таблице Excel и одновременно просмотреть конкретную информацию о нем? В этом случае запуск ВПР экономит много усилий. Это один из лучших способов выполнить вертикальный запрос в Microsoft Excel.

Давайте посмотрим, как использовать ВПР в электронной таблице Excel.

Что такое ВПР и как это работает?

Использование ВПР для проверки электронной таблицы похоже на поиск элемента в Google или выполнение запроса к базе данных для возврата определенных элементов, если они есть.

По сути, VLOOKUP работает, просматривая набор элементов в столбце и предоставляя вам результат на основе информации, которую вы выбрали для просмотра об этом столбце. Например, вы можете узнать цену товара, выполнив поиск по названию модели.

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

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

Как написать формулу ВПР

Формула ВПР обычно требует четырех аргументов и выглядит так:

 =VLOOKUP(lookup_value, table_array, column_index_number, range_lookup)

Значение поиска – это целевой элемент, который вы хотите проверить, и он должен приходиться на первый столбец вашей электронной таблицы. Массив таблиц – это родительские данные, которые вы хотите найти.

При выполнении VLOOKUP вы также хотите вернуть результат на основе определенного значения. Позиция столбца, содержащего это значение, является порядковым номером столбца. Однако поиск диапазона принимает значение ИСТИНА для приблизительного совпадения или ЛОЖЬ для точного совпадения.

Связанный: Ускоренный поиск в таблицах Excel: замените ВПР на ИНДЕКС и ПОИСКПОЗ

Как использовать ВПР

Теперь, когда вы знаете архитектуру формулы ВПР. Давайте посмотрим, как это работает на практике, с помощью следующих примеров и шагов Excel VLOOKUP.

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

Используя этот пример VLOOKUP, давайте посмотрим, как это сделать, выполнив следующие шаги.

  1. Введите название продукта, который вы хотите найти, в любой ячейке ниже или рядом с вашими родительскими данными (убедитесь, что вы избегаете опечаток).
  2. Затем выберите пустую ячейку рядом с только что созданной ячейкой, которая теперь содержит продукт, который вы хотите найти.
  3. В этой новой ячейке введите = ВПР . Excel обычно рекомендует завершение. Когда вы это увидите, нажмите Tab на клавиатуре, чтобы продолжить формулу ВПР.
  4. Затем выделите только что созданную ячейку, содержащую название целевого продукта. Это добавит позицию ячейки в формулу ВПР. Введите запятую после выделенной ячейки в формуле ВПР. Например, если целевой элемент или продукт находится в ячейке E6 , формула будет иметь вид = ВПР (на данный момент E6 .
    Введите формулу Vlookup в следующую ячейку
  5. Затем выделите все родительские данные (массив таблиц), чтобы также добавить их в формулу. Если ваш массив таблицы находится между A2 и C9, ваша формула принимает вид = ВПР (E6, A2: C9, с запятой в конце.
  6. Введите номер столбца значения, для которого вы хотите просмотреть целевой элемент. Столбцы обычно связаны с буквами в Excel, но в этом случае вам нужно посчитать их количество (A – 1, B – 2 и т. Д.). Введите запятую после него.
  7. Введите FALSE, если вы хотите получить точное совпадение с введенным вами элементом. В противном случае введите TRUE, чтобы просмотреть его по наиболее доступному приблизительному соответствию.
  8. Окончательная формула должна выглядеть так: = ВПР (E6, A2: C9,2, FALSE) , в зависимости от введенных вами параметров.
  9. Затем нажмите Enter, чтобы получить результат.
    Выделите родительские данные, введите номер индекса столбца и выберите подходящую функцию.

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

Как сделать ВПР для нескольких элементов

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

  1. Введите все элементы, которые вы хотите найти, в отдельные ячейки (в данном случае названия выбранных продуктов).
  2. Выберите ячейку рядом с первым продуктом и введите = ВПР ( .
  3. Затем выделите первый элемент (значение поиска) из тех, которые вы только что ввели, чтобы добавить его в формулу ВПР. Введите запятую после этого.
    Выберите первую ячейку из списка элементов
  4. Выделите все родительские данные (массив таблиц), чтобы также добавить их диапазон в формулу. Затем убедитесь, что вы нажали клавишу F4 на клавиатуре, чтобы сделать результат абсолютным, чтобы он не изменился при копировании формулы. Отделите это от следующего аргумента запятой. Ваша формула должна измениться на что-то вроде этого: = ВПР (E6, $ A $ 2: $ C $ 13) .
  5. Введите номер столбца, в котором вы хотите найти данные. Например, если количество отзывов находится во втором столбце, введите 2. Затем поставьте запятую после этого.
  6. Затем введите FALSE, чтобы получить точные совпадения.
    Выделите всю родительскую таблицу, нажмите F4, введите номер индекса столбца и False
  7. Окончательная формула должна выглядеть примерно так: = ВПР (E6, $ A $ 2: $ C $ 13,2, FALSE) , в зависимости от выбранных вами параметров.
  8. Закройте круглые скобки и нажмите Enter .
  9. Как только результат появится напротив первого значения поиска, перетащите ячейку с результатами вниз, чтобы заполнить формулу и для всех других продуктов.
    Перетащите первую ячейку вниз, чтобы добавить результат к другим продуктам

Как создать связь между таблицами Excel с помощью ВПР

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

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

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

  1. Создайте новый столбец Excel в электронной таблице подмножества, убедитесь, что вы используете перефразированное имя столбца. Например, вы можете назвать его «Количество отзывов» вместо «Отзывы».
  2. Затем поместите курсор в первую ячейку (напротив первого продукта) в новом столбце и введите = VLOOKUP ( .
  3. Выберите первый продукт из подмножества данных, чтобы добавить его в формулу ВПР, и поставьте после него запятую.
    Выберите первую ячейку, содержащую название продукта
  4. Вернитесь к родительской таблице и выделите всю таблицу данных. Затем убедитесь, что вы нажали клавишу F4, чтобы результат можно было применить к другим выбранным продуктам. Теперь вы увидите формулу поиска в строке формул. Введите запятую после нажатия F4 .
  5. Находясь на родительском листе, посмотрите на строку формул и введите номер столбца для столбца отзывов. Затем отделите его от следующего аргумента запятой.
  6. На том же родительском листе введите FALSE, так как в этом случае требуется точное соответствие каждого продукта.
    Перейдите на родительский лист, чтобы заполнить формулу Vlookup, и нажмите Enter.
  7. Закройте круглые скобки и нажмите Enter . Затем Excel вернет вас к подмножеству данных и отобразит результат обзора для первого продукта.
  8. Затем перетащите вниз результат для первого продукта, чтобы увидеть результаты для других продуктов на листе подмножества.
    Перетащите ячейку вниз в подмножестве данных, чтобы увидеть результаты для других значений.

Управляйте запросом с помощью поиска в Excel

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

Например, HLOOKUP имеет горизонтальную ориентацию. Но XLOOKUP – это новая функция, которая может смотреть в таблице как вертикально, так и горизонтально. Большинство этих функций поиска следуют одному и тому же процессу с небольшими отличиями. Использование любого из них для любой конкретной цели поиска – разумный способ получить ваши запросы Excel.