Как воспользоваться VLOOKUP в Эксель

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

Что значит эта аббревиатура

Данный инструмент на британском языке расшифровывается как «Vertical LOOK UP». В российской версии Экселя эта функция именуется ВПР, которая в свою очередь значит «Вертикальный Просмотр».

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

Как применять VLOOKUP в Excel

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

VLOOKUP в Экселе

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

Для того чтоб составить подходящую формулу, от вас потребуются последующие деяния.

Введите какое-нибудь слово, по которому мы будем производить поиск других значений. Перейдите в соседнюю клеточку. Вызовите окно «Вставка функции». Изберите категорию «Полный алфавитный список». Найдите там функцию «ВПР» и нажмите на «OK».

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

Направьте внимание на то, что отчет происходит в границах выделенной области в поле «Таблица».

Интервальный просмотр отвечает за то, требуется ли четкое совпадение поисковых результатов (0 – полное совпадение, а 1 – допускаются погрешности и маленькие отличия). Для окончания опций необходимо надавить на «OK».

Благодаря сиим действиям функция отыщет подходящую строчку и вставит соответственное имя.

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

Благодаря этому формула скопируется во все другие ячейки. При всем этом номер искомого столбца скорректируется автоматом.

Внедрение выпадающих списков

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

Перейдите в клеточку, в которой мы вводили слово для поиска.
Откройте уже знакомую нам вкладку.
Кликните на обозначенный инструмент.
Изберите пункт «Проверка данных».

Раскройте выпадающий перечень и изберите соответственный пункт.

Сходу опосля этого покажется доп поле. Кликните на него, потом выделите колонку значений, которые можно применять для поиска инфы. Для продолжения кликните на «OK».

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

В качестве примера можно избрать хоть какого другого педагога.

Все данные автоматом будут найдены.

Поиск конфигурации данных с помощью VLOOKUP

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

Для начала необходимо сделать ещё один столбец.

Потом скопируем старенькую таблицу на иной лист. Конкретно там мы и будем заносить все обновления.

В качестве примера изменим какую-нибудь запись.

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

Потом жмем на кнопку [knopka]Enter[/knopka].

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

Копируем эту формулу во все другие строчки.

Итог выходит последующим.

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

Вероятные ошибки

Почти всегда у новичков возникает ошибка «#Н/Д». При этом биться с ней, как указывает практика, достаточно трудно. По сути ничего ужасного здесь нет. Основное – отыскать причину и всё сходу же станет на свои места. Быстрее всего, причина вашей препядствия заключается в последующем:

вы допустили ошибку при написании формулы;
у вас опечатка в слове для поиска;
включен неточный поиск (интервальный просмотр равен 1) и данные не отсортированы;
в аргументах функции употребляются разные типы данных;
некорректно указана таблица для поиска данных.

Версии MS Excel

Данную функцию можно применять полностью во всех версиях этого редактора (2016, 2013, 2010, 2007). Она есть даже в 2003 году.

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

Заключение

В данной статье мы разглядели механизм работы функции «VLOOKUP» в редакторе Excel. Направьте внимание на то, что для поиска какой-либо инфы довольно способностей этого инструмента. Для реализации сложного поиска не непременно применять макросы на VBA. Всё можно создать средствами находящимися под рукой.

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

Видеоинструкция

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

Как применять функцию Excel VLOOKUP

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

Интересно почитать: Таблица функция в excel

VLOOKUP обычно возвращает одно поле данных в качестве вывода. Как это происходит:

Вы предоставляете имя либо Lookup _value, который докладывает VLOOKUP , в какой строке либо записи таблицы данных находить подходящую информацию
Вы указываете номер столбца — узнаваемый как Col_index_num — данных, которые вы ищете
Функция отыскивает значение Lookup _value в первом столбце таблицы данных
VLOOKUP потом находит и возвращает информацию, которую вы ищете из другого поля той же записи, используя предоставленный номер столбца

Аннотации в данной статье используются к Excel для Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel для Mac и Excel Online.

Отыскать информацию в базе данных с VLOOKUP

На изображении, показанном выше, VLOOKUP употребляется для определения цены за единицу продукта по его наименованию. Имя становится значением поиска, которое VLOOKUP употребляет для поиска цены, расположенной во 2-м столбце.

Синтаксис и аргументы функции VLOOKUP

Синтаксис функции относится к макету функции и включает в себя имя функции, скобки и аргументы .

Синтаксис для функции VLOOKUP:

= VLOOKUP (lookup_value, Table_array, Col_index_num, Range_lookup)

Lookup _value — (непременно) значение, которое вы желаете отыскать в первом столбце аргумента Table_array .

Table_array — (непременно) это таблица данных, которую VLOOKUP отыскивает для поиска подходящей для вас инфы
— Table_array должен содержать как минимум два столбца данных;
— 1-ый столбец обычно содержит значение Lookup_value.

Col_index_num — (непременно) номер столбца значения, которое вы желаете отыскать
— нумерация начинается со столбца Lookup_value в качестве столбца 1;
— если Col_Index_Num установлен на число , большее число столбцов , избранных в диапазон_просмотра аргумента #REF! ошибка ворачивается функцией.

Range_lookup — (необязательно) показывает, сортируется ли спектр в порядке возрастания
— данные в первом столбце употребляются в качестве ключа сортировки
— логическое значение — TRUE либо FALSE являются единственными допустимыми значениями
— если не обозначено, значение устанавливается по дефлоту TRUE
— если установлено в TRUE либо опущено, и четкое совпадение для значения _ для поиска не найдено, то в качестве поискового ключа употребляется наиблежайшее совпадение, которое меньше по размеру либо значению
— если установлено в TRUE либо опущено, а 1-ый столбец спектр не сортируется в порядке возрастания, может появиться неверный итог
— если установлено значение FALSE, VLOOKUP воспринимает лишь четкое совпадение для Lookup _value .

Сортировка данных в первую очередь

Хотя это не постоянно требуется, обычно лучше поначалу отсортировать спектр данных, которые VLOOKUP отыскивает в порядке возрастания, используя 1-ый столбец спектра для ключа сортировки .

Если данные не отсортированы, VLOOKUP может возвратить неправильный итог.

Четкие и ориентировочные матчи

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

Определяющим фактором является аргумент Range_lookup :

при значении FALSE он возвращает лишь информацию, относящуюся к четким совпадениям, к значению Lookup _value
установлен в TRUE либо опущен, он возвращает точную либо ориентировочную информацию, относящуюся к Lookup _value

В приведенном выше примере для Range_lookup установлено значение FALSE, потому VLOOKUP обязана отыскать четкое совпадение для термина Widgets в таблице данных, чтоб возвратить стоимость за единицу для этого элемента. Если четкое совпадение не найдено, функция возвращает ошибку # N / A.

VLOOKUP не чувствителен к регистру — и виджеты, и виджеты являются применимым написанием для приведенного выше примера.

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

Ввод аргументов функции VLOOKUP в Excel при помощи наведения

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

= ВПР (А2, $ $ 5: $ B $ 8,2, ЛОЖЬ)

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

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

Последующие шаги были применены для ввода функции VLOOKUP в ячейку B2 при помощи диалогового окна функции.

Открытие диалогового окна VLOOKUP

Изберите ячейку B2, чтоб создать ее активной ячейкой — пространство, где показываются результаты функции VLOOKUP.
Изберите вкладку Формулы .
Изберите Lookup & Reference на ленте, чтоб открыть раскрывающийся перечень функций
Изберите VLOOKUP в перечне, чтоб вызвать диалоговое окно функции

Данные, введенные в четыре пустые строчки диалогового окна, сформировывают аргументы для функции VLOOKUP.

Указывая на ссылки на ячейки

Аргументы для функции VLOOKUP вводятся в отдельные строчки диалогового окна, как показано на рисунке выше.

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

Внедрение относительных и абсолютных ссылок на ячейки с аргументами

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

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

Интересно почитать: Как в excel называется функция деления

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

Это лучше, так как все копии VLOOKUP будут ссылаться на одну и ту же таблицу данных в качестве источника инфы.

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

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

Ввод аргументов функции

Изберите _value Уточняющий строчку в ВПР диалоговом окне
Изберите ячейку A2 на листе, чтоб ввести эту ссылку на ячейку в качестве аргумента search_key
Изберите строчку Table_array диалогового окна
Выделите ячейки от A5 до B8 на листе, чтоб ввести этот спектр в качестве аргумента Table_array — заглавия таблицы не включены
Нажмите кнопку F4 на клавиатуре, чтоб поменять спектр на абсолютные ссылки на ячейки.
Изберите строчку Col_index_num диалогового окна
Введите 2 в данной строке в качестве аргумента Col_index_num , так как ставки дисконтирования находятся в столбце 2 аргумента Table_array.
Изберите строчку Range_lookup диалогового окна
Введите слово False в качестве аргумента Range_lookup
Нажмите кнопку ввода на клавиатуре, чтоб закрыть диалоговое окно и возвратиться к рабочему листу.

Ответ 14.76 $ — стоимость за единицу для виджета — должен показаться в ячейке B2 рабочего листа.

Если щелкнуть ячейку B2, полная функция = VLOOKUP (A2, $ A $ 5: $ B $ 8,2, FALSE) покажется на панели формул над рабочим листом.

КАК: Левая функция VLOOKUP в Excel — 2021

Как применять формулу ВПР (VLOOKUP) (Октябрь 2021).

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

Но, сочитая VLOOKUP с функцией CHOOSE, можно сделать формулу поиска слева, которая:

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

Заметка: Эти аннотации относятся к версиям Excel 2019, 2016, 2013, 2010 и Excel для Office 365.

Учебное пособие: внедрение функций VLOOKUP и CHOOSE в левой форме поиска

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

= ВПР ($ D $ 2, ВЫБРАТЬ (<1,2>, $ F: $ F, $ D: $ D), 2, FALSE)

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

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

Введите данные учебника

Введите заголовок поставщик в клеточку D1.
Введите заголовок Часть в ячейке E1.
Введите таблицу данных, отображаемую на изображении выше, в ячейки От D4 до F9
Ряды 2 и 3 остаются пустыми для соответствия аспектам поиска и формулой поиска слева, сделанной во время этого урока

Откройте диалоговое окно VLOOKUP

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

В этом случае безопаснее применять диалоговое окно VLOOKUP. Практически все функции Excel имеют диалоговое окно, которое дозволяет для вас вводить любой аргумент функции в отдельной строке.

Нажмите на ячейку E2 рабочего листа. E2 — это пространство, где будут отображаться результаты левой формулы поиска.
Нажми на Формулы вкладку ленты.
Нажми на Поиск и ссылки в ленте, чтоб открыть раскрывающийся перечень функции.
Нажмите на ВПР в перечне, чтоб открыть диалоговое окно функции.

Ввод аргументов в диалоговое окно VLOOKUP

Аргументы функции — это значения, применяемые функцией для вычисления результата.

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

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

Значение поиска

Значение поиска — это поле инфы, которое употребляется для поиска в массиве таблиц. VLOOKUP возвращает другое поле данных из той же строчки, что и значение поиска.

В этом примере употребляется ссылка ячейки на пространство, где заглавие компании будет введено на рабочий лист. Преимущество этого заключается в том, что он упрощает изменение наименования компании без редактирования формулы.

Нажми на искомое_значение в диалоговом окне.
Нажмите на ячейку D2 добавить эту ссылку на ячейку искомое_значение линия.
нажмите F4 ключ на клавиатуре, чтоб создать ссылку на ячейку абсолютной — $ D $ 2.

Абсолютные ссылки на ячейки

Абсолютные ссылки на ячейки употребляются для аргументов lookup value и table array для предотвращения ошибок, если формула поиска копируется в остальные ячейки на листе.

Ввод функции CHOOSE

Аргумент массива таблицы — это блок смежных данных, из которых извлекается определенная информация.

Интересно почитать: Excel функция поиск

Обычно, VLOOKUP глядит лишь справа от аргумента value для поиска данных в массиве таблиц. Чтоб вынудить его глядеть на лево, VLOOKUP необходимо одурачить, переставив столбцы в массиве таблиц, используя функцию CHOOSE.

В данной формуле функция CHOOSE делает две задачки:

Он делает массив таблиц шириной всего два (столбцы D и F).
Он изменяет порядок столбцов в массиве таблицы справа влево, чтоб столбец F был первым, а столбец D 2-ой.

Ввод функций

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

В диалоговом окне функции VLOOKUP нажмите таблица_массив линия.
Введите последующее ВЫБИРАТЬ функция:ВЫБРАТЬ (<1,2>, $ F: $ F, $ D: $ D)

Индекс номера столбца

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

Функция CHOOSE делает массив таблиц шириной в два столбца, поначалу столбец F, а потом столбец D. Так как разыскиваемая информация — заглавие части — находится в столбце D, значение аргумента индекса столбца обязано быть установлено равным 2.

Нажми на Col_Index_Num в диалоговом окне.
Наберите "А 2 в данной строке.

Поиск спектра

Аргумент Range_lookup VLOOKUP — это логическое значение (лишь TRUE либо FALSE), которое показывает, желаете ли вы, чтоб VLOOKUP находил четкое либо ориентировочное соответствие для значения поиска.

Если TRUE либо если этот аргумент опущен, VLOOKUP возвращает или четкое соответствие Lookup_value, или, если четкое совпадение не найдено, VLOOKUP возвращает последующее наибольшее значение. Для данной формулы данные в первом столбце Table_array должны быть отсортированы в порядке возрастания.
Если FALSE, VLOOKUP употребляет четкое совпадение с Lookup_value. Если в первом столбце таблицы Table_array есть два либо наиболее значения, которые соответствуют значению поиска, употребляется 1-ое отысканное значение. Если четкое совпадение не найдено, ворачивается ошибка # N / A.

В этом уроке, так как мы отыскиваем конкретное имя части, Range_lookup настроен на Ересь так что формула возвращает лишь четкие совпадения.

Нажми на диапазон_просмотра в диалоговом окне.
Введите слово Ересь в данной строке, чтоб указать, что мы желаем, чтоб VLOOKUP возвращал четкое соответствие для данных, которые мы отыскиваем.
Нажмите Отлично для окончания формулы поиска слева и закрытия диалогового окна.
Так как мы еще не ввели заглавие компании в ячейку D2, в ячейке E2 возникает ошибка # N / A.

Возвращение данных при помощи формулы поиска слева

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

Заглавие детали отображается в ячейке E2.

Нажмите на ячейку D2 в вашем рабочем листе.
Тип Девайсы Плюс в клеточку D2 и нажмите ВОЙТИ на клавиатуре.
Текст «Девайсы» — часть, предоставленная компанией Gadgets Plus, — обязана отображаться в ячейке E2.

Снова проверьте формулу поиска, набрав остальные имена компаний в ячейку D2, и соответственное имя части обязано показаться в ячейке E2.

Если в ячейке E2 покажется сообщение о ошибке, такое как # N / A, проверьте наличие орфографических ошибок в ячейке D2.

Создание массива таблицы из 2-ух столбцов

Синтаксис функции CHOOSE:

= CHOOSE (Index_number, Value1, Value2, … Value254)

Функция CHOOSE обычно возвращает одно значение из перечня значений (Value1 — Value254) на базе введенного номера индекса.

Если номер индекса равен 1, функция возвращает Value1 из перечня; если номер индекса равен 2, функция возвращает Value2 из перечня и так дальше.

Когда вводится несколько номеров индексов, функция возвращает несколько значений в любом порядке. Получение CHOOSE для возврата нескольких значений осуществляется методом сотворения массива.

Чтоб ввести массив, соедините числа, введенные в фигурные скобки либо скобки. Для номера индекса вводятся два номера: .

Необходимо подчеркнуть, что CHOOSE не ограничивается созданием таблицы с 2-мя столбцами. Включив в массив доп номер, к примеру <1,2,3>, и доп спектр в аргументе value, CHOOSE делает таблицу с 3-мя столбцами.

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

Изменение порядка столбцов при помощи функции ВЫБОР

В функции CHOOSE, применяемой в данной формуле:

ВЫБРАТЬ (<1,2>, $ F: $ F, $ D: $ D)

спектр для столбца F указан перед столбцом D.

Так как функция CHOOSE устанавливает массив таблиц VLOOKUP (источник данных для данной функции), то переключение порядка столбцов в функции CHOOSE передается вкупе с VLOOKUP.

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

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

Функция volatile NOW Excel для данных и времени

Узнайте, как применять функцию volatile NOW Excel для прибавления текущего времени либо даты в рабочие листы либо использования их в функциях. Обновлен для включения Excel 2019.

Функция Excel Clean для удаления непечатаемых знаков

Используйте функцию CLEAN Excel для удаления непечатаемых знаков ASCII и Unicode. Не считая того, дескрипторы знаков, которые CLEAN обычно не могут удалить.

Функция сортировки ключей в Microsoft Excel

Узнайте о ключе сортировки, так как он употребляется в таблицах Excel и Гугл и как он полезен при работе с электрическими таблицами.