Формула vlookup: Использование функции ВПР (VLOOKUP) для подстановки значений

Содержание

Журнал «Пуш»: Карьерные статьи и советы

Статьи

Что анализировать с помощью функции ВПР

Тимофей Миненков — студент МГУ им. М.В. Ломоносова и выпускник онлайн-курса Changellenge >> ToolKit 2018. По словам Тимофея, курс помог ему в учебе, на кейс-чемпионатах, а также при отборе в крупные компании. В статье Тимофей рассказывает о тех функциях, которыми он пользуется чаще всего. Первая часть посвящена функции ВПР или VLOOKUP.

Тимофей Миненков

Функция ВПР или VLOOKUP

При помощи этой функции можно сводить данные из нескольких таблиц в одну: она подтягивает нужные значения. Объясню принцип ее работы на примере. На последнем чемпионате Oliver Wyman Impact мы анализировали, какие страховые компании в среднем росли быстрее остальных на протяжении последних пяти лет. Данные о выручке можно было найти на сайте Центрального Банка РФ, однако проблема заключалась в подготовке этих данных к анализу. В каждом из отчетов разное количество компаний и порядок их расположения, поэтому просто скопировать значения одного года и вставить рядом значения другого было не достаточно. Если же вручную искать каждую компанию, а затем копировать и вставлять ее показатели в нужное место, можно потратить целый день.

Используя функцию ВПР, мы автоматически находим нужную компанию и получаем значение ее выручки в нужной ячейке. Синтаксис формулы выглядит следующим образом: ВПР (Искомое значение; Таблица для поиска; Номер столбца в таблице; Модификатор поиска).

Искомое значение — это некий ключ, по которому происходит поиск данных. В нашем случае, это ID компании. Искомое значение не должно повторяться, иначе формула выберет только первое значение с данным ключом.

Таблица для поиска в нашем случае — это диапазон F1:h338. Ваш ключ всегда должен находиться в первом столбце таблицы.

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

Модификатор поиска — это точность поиска ключа. TRUE — для приблизительного поиска, FALSE — для точного. Мы выбираем FALSE, так как все ID в первой и второй таблице одинаковы.

Работать с формулами будет проще, если знать горячие клавиши. Во-первых, TAB. Она поможет быстро выбрать необходимую формулу в выпадающем списке, когда вы начинаете что-то писать после знака «равно». Во-вторых, Ctrl + Shift + стрелка, по направлению которой необходимо выделить ячейки. В-третьих, клавиша F4, при помощи которой можно фиксировать ячейки, на которые вы ссылаетесь, чтобы формула не «съезжала». После нажатия на эту клавишу в формуле появляется знак, похожий на доллар.

Чтобы не забыть то, что вы только что прочитали, важно отработать навык на практике. Для этого я советую курс «ToolKit Plus». В нем после каждой лекции вы выполняете упражнения по пройденной теме, а потом закрепляете знания в реальном проекте.

Узнать больше >>

Получите карьерную поддержку

Если вы не знаете, с чего начать карьеру, зашли в тупик или считаете, что совершили какие-то ошибки, спросите совета у специалистов. Заполните заявку и консультанты Changellenge >> окажут вам помощь. Это отличный шанс вместе экспертом проработать проблемные вопросы и составить карьерный план.

Стажировки

Сервисный менеджер SOC

Оператор QIWI-кошелек

Стажер\Младший менеджер по продвижению решений

Следите за новыми вакансиями в нашем Telegram-канале

CL-Offers

Смотрите также

Семь ресурсов, которые будут полезны при изучении Java

Рейтинг магистерских программ от Financial Times — лучшие магистратуры мира и прорыв СПбГУ

Технари в консалтинге: как я поменял профессию на 180 градусов

Топ самых забавных языков программирования

9 вещей, которые нужно сделать за остаток лета

«Альфа-Банк» занял первое место в списке 50 самых результативных российских банков

Networks, analytics, digital epidemiology and bots

Карьера в FMCG: Лидерство, SNICKERS и переговоры с Китаем

10 секретов успешной работы в консалтинге

Профессия аналитика данных

Кто такой ментор и зачем он нужен

Советы начинающему маркетологу от директора по маркетингу Яндекс.

Практикум

Шпаргалка по Excel для начинающих финансистов

Братья из BCG: как складываются истории успеха близнецов-консультантов Николая и Леонида Тузенко

Хороший ли вы командный игрок

Заметки о стратегии: 10 главных ошибок каждого стратега

Чек-лист: как не промахнуться при выборе магистратуры

Для специалистов будущего останется много важных задач: интервью Марии Голяндриной, вице-президента МТС по управлению персоналом

Создать аккаунт

Адрес e-mail :

Телефон :

+7+380+375+49+996+1+66+44+86+371+373+998+374+994+992+995+90+48+31

Фамилия :

Пароль :

Подтверждение пароля :

Получать рассылку Changellenge

Согласен с условиями обработки персональных данных

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

E-mail:

Номер телефона:

Как устранить ошибки VLOOKUP в Excel

Боретесь с функцией VLOOKUP в Microsoft Excel? Вот несколько советов по устранению неполадок, которые могут вам помочь.

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

Программы для Windows, мобильные приложения, игры — ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале — Подписывайтесь:)

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

Ограничения VLOOKUP

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

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

В приведенном ниже примере самый дальний столбец (колонка А) используется в качестве столбца поиска. В этом диапазоне нет дублированных значений и данных поиска (в данном случае данные из колонка B) находится справа от столбца поиска.

Функции INDEX и MATCH могли бы стать хорошей альтернативой, если возникнет проблема с любой из этих проблем, как и новая функция XLOOKUP в Excel, которая в настоящее время находится в стадии бета-тестирования.

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

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

Ошибки VLOOKUP и # N / A

Одной из самых распространенных ошибок VLOOKUP в Excel является # N / A ошибка. Эта ошибка возникает, когда VLOOKUP не может найти искомое значение.

Начнем с того, что значение поиска может отсутствовать в вашем диапазоне данных, или вы, возможно, использовали неправильное значение. Если вы видите ошибку N / A, дважды проверьте значение в формуле VLOOKUP.

Если значение верное, то вашего поискового значения не существует. Это предполагает, что вы используете VLOOKUP для поиска точных совпадений, с range_lookup аргумент установлен в ЛОЖНЫЙ,

В приведенном выше примере, поиск Студенческий билет с номер 104 (в клетке G4) возвращает Ошибка # N / A потому что минимальный идентификационный номер в диапазоне 105,

Если range_lookup аргумент в конце формулы VLOOKUP отсутствует или установлен в ПРАВДА тогда VLOOKUP вернет ошибку # N / A, если ваш диапазон данных не отсортирован в порядке возрастания.

Он также вернет ошибку # N / A, если ваше значение поиска меньше, чем самое низкое значение в диапазоне.

В приведенном выше примере Студенческий билет значения перепутаны. Несмотря на ценность 105 существующий в ассортименте, VLOOKUP не может выполнить правильный поиск с помощью range_lookup аргумент установлен в ПРАВДА так как колонка А не отсортировано по возрастанию

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

Устранение ошибок #VALUE

#ЗНАЧЕНИЕ ошибка обычно является признаком того, что формула, содержащая функцию VLOOKUP, в некотором роде неверна.

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

Если вы имеете дело с ячейками, которые содержат более длинные строки символов, VLOOKUP не сможет их обработать.

Единственный обходной путь для этого — заменить формулу VLOOKUP комбинированной формулой INDEX и MATCH. Например, если в одном столбце содержится ячейка со строкой из более чем 255 символов, для поиска этих данных можно использовать вложенную функцию MATCH внутри INDEX.

В приведенном ниже примере ПОКАЗАТЕЛЬ возвращает значение в ячейке B4 используя диапазон в колонка А определить правильный ряд. Это использует вложенный СОВПАДЕНИЕ функция для идентификации строки в колонка А (содержит 300 символов), который соответствует ячейке h5,

В данном случае это клетка A4, с ПОКАЗАТЕЛЬ возврате 108 (значение B4).

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

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

Если вы столкнулись с ошибкой #VALUE, дважды проверьте формулу VLOOKUP, чтобы убедиться, что ссылки на ваши ячейки верны.

#NAME и VLOOKUP

Если ваша ошибка VLOOKUP не является ошибкой #VALUE или ошибкой # N / A, то это, вероятно, #ИМЯ ошибка. Прежде чем паниковать при мысли об этом, будьте уверены — это самая простая ошибка VLOOKUP, которую нужно исправить.

Ошибка #NAME появляется, когда вы ошиблись в функции в Excel, будь то VLOOKUP или другая функция, такая как SUM. Нажмите на ячейку VLOOKUP и проверьте, правильно ли вы написали VLOOKUP.

Если других проблем нет, ваша формула VLOOKUP будет работать после исправления этой ошибки.

Использование других функций Excel

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

Если VLOOKUP вам не подходит, воспользуйтесь этими функциями Excel.

Функция ВПР в Google Таблицах: основное руководство

Функция ВПР в Google Таблицах представляет собой функцию вертикального поиска. Вы используете его для поиска элемента в столбце и возврата данных из этой строки, если совпадение найдено.

В следующем примере мы используем формулу ВПР для поиска слова «Чарльз Диккенс» в столбце 1. Когда мы его находим, формула возвращает значение из 4-го столбца таблицы поиска, что дает результат в размере 299 долларов США.

В этом примере функция ВПР:

=VLOOKUP(B8,A2:D5,4,false)

Давайте разберем эту формулу: столбец таблицы поиска : A2:D5

Если он находит условие поиска, , он затем просматривает эту строку до столбца, указанного номером Index : 4

Затем он возвращает значение из столбца 4 в качестве ответа, что составляет 29 долларов США. 9 в этом примере.

Последний аргумент — false , что означает точное совпадение.

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

Синтаксис функции ВПР

Вот синтаксис ВПР:

=ВПР(ключ_поиска, диапазон, индекс, [отсортировано])

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

search_key

search_key — это элемент исходной таблицы, который вы хотите найти в таблице поиска.

диапазон

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

индекс

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

Если номер индекса больше, чем количество столбцов в таблице поиска (т. е. вы просите формулу ВПР вернуть значения из столбца 4 таблицы поиска, которая имеет только 3 столбца), #ССЫЛКА! возвращается ошибка.

is_sorted

Последний аргумент принимает значение ИСТИНА или ЛОЖЬ, иногда записываемое как 1 (ИСТИНА) или 0 (ЛОЖЬ).

ЛОЖЬ означает:

Соответствует точному
Не чувствителен к регистру
При наличии нескольких совпадений обнаруживается первое совпадение, и возвращаемое содержимое берется из этой строки (т. е. если Бен Коллинз появляется дважды в таблице поиска в строке 5 и строке 10, то всегда возвращается информация из строки 5)
Если значение поиска не найдено, функция ВПР возвращает сообщение об ошибке #Н/Д

Параметр FALSE используется для 99% случаев VLOOKUP

Принимая во внимание, что TRUE означает:

Находит ближайшее совпадение, меньшее или равное ключу поиска
Эта опция используется редко

Шаблон функции ВПР

Щелкните здесь, чтобы открыть копию только для просмотра >>

Вы можете сделать копию: Файл > Создать копию…

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

ВПР Для объединения таблиц в Google Таблицах

Функция ВПР в Google Таблицах обычно используется для извлечения данных из одной таблицы данных и добавления их в другую.

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

Кроме того, у нас есть отдельная таблица клиентов, в которой указаны данные об их местонахождении:

Для полного понимания наши данные о продажах, мы хотим объединить эти таблицы, чтобы мы могли видеть доход по местоположению.

Мы используем эту функцию ВПР:

=ВПР( A4,$I$3:$J$7,2,false)

Знак $ вокруг ссылки на диапазон таблицы делает ее абсолютной ссылкой, которая блокирует ссылка на диапазон на месте.

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

Приблизительное сопоставление с использованием формулы ВПР с аргументом ИСТИНА

Этот урок ВПР взят из 6-го дня моего бесплатного 30-дневного курса Advanced Formulas Challenge.

Предположим, вы знаете процентные ставки в вашем банке:

И затем у вас есть куча счетов, по которым вы хотите рассчитать проценты:

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

Вы можете использовать ВПР с аргументом ИСТИНА.

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

Когда я ищу значение (например, 79 891 долл. США), функция ВПР ИСТИНА будет выполнять поиск вниз по столбцу B до тех пор, пока не достигнет числа, превышающего искомое значение, а затем остановится на этой строке перед этим большим значением. В этом примере он остановится на отметке 50 001 доллар, поскольку следующее значение, 100 001 доллар, больше, чем 79 долларов.,891. Таким образом, он возвращает процентную ставку из строки $50 001, поскольку она представляет собой диапазон от $50 001 до $100 000, в котором находится процентная ставка.

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

Результат ВПР:

Использование функции ВПР в Google Таблицах для сравнения списков данных отличия есть.

Вы сравниваете списки, ища имена из списка 1 в списке 2, а затем, наоборот, ищете имена из списка 2 в списке 1.

Формула ВПР может выглядеть следующим образом: E:E , 1 , false ) , «Нет в списке 2» )

Несколько замечаний по поводу этой формулы:

Таблица поиска представляет собой один столбец E:E
Значит индекс должен быть 1
С индексом 1 возвращает имя, если оно найдено
Используется функция ЕСЛИОШИБКА для отображения пользовательского сообщения об ошибке, когда имена не найдены

Вот практический пример сравнения списков (щелкните, чтобы увеличить):

Расширенные методы ВПР

ВПР в Google Таблицах Использование подстановочных знаков для частичных совпадений
Как использовать подстановочные знаки «*» для частичного совпадения в Google Таблицах .

Как выполнить ВПР слева в Google Таблицах?

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

Функция ВПР возвращает несколько столбцов в Google Таблицах
Вы когда-нибудь хотели вернуть более одного столбца с помощью ВПР? Возможно, вы хотите объединить две таблицы и перенести все данные. В этом руководстве показано, как это сделать, не используя несколько формул ВПР.

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

Вы также можете прочитать об этом в документации Google.

Как использовать функцию ВПР в Google Sheets

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

Если это немного сбивает с толку, не волнуйтесь. В этой статье подробно объясняется функция ВПР, включая ее синтаксис и способы ее применения к электронным таблицам. Читайте дальше, чтобы освоить эту часть Google Sheets.

Что такое ВПР в Google Таблицах?

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

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

Чем ВПР отличается от ГПР?

HLOOKUP в Google Таблицах находит данные в горизонтальной таблице. Однако функция ВПР находит данные в вертикальной таблице. У большинства из нас данные в электронных таблицах представлены в вертикальной форме, поэтому функция ВПР гораздо полезнее, чем ГПР. В остальном параметры, используемые в формулах, одинаковы, поэтому, если вы знаете, как работает ГПР, значит, вы знаете, как использовать ВПР. Верно и обратное.

В связи с этим, если вы знакомы с XLOOKUP в Excel, использование как HLOOKUP, так и VLOOKUP в Google Sheets должно быть простым.

Что нужно знать о формуле ВПР

Вот несколько моментов, которые необходимо знать перед использованием формулы ВПР в электронной таблице:

Формула ВПР не смотрит влево. Он ищет только в первом столбце диапазона. Если вы хотите выполнить поиск слева, используйте вместо этого формулы ИНДЕКС и ПОИСКПОЗ. Вы можете добавить параметры AND/OR к поиску с помощью этой комбинации.
Формула ВПР нечувствительна к регистру в Google Таблицах, то есть она не различает прописные и строчные буквы.
Если формула ВПР возвращает неверные результаты, убедитесь, что для параметра is-sorted установлено значение ЛОЖЬ . Это должно исправить вывод.
Если для параметра is-sorted установлено значение TRUE , помните, что первый столбец в диапазоне должен располагаться в порядке возрастания.
ВПР может искать частичное совпадение, используя операторы, представленные вопросительными знаками (?) или звездочками (*).

Синтаксис для ВПР

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

 = ВПР(ключ, диапазон, индекс, сортировка)

Вот что делает каждый из параметров, используемых в формуле ВПР:

ключ: значение, которое функция использует для поиска в другом столбце
Диапазон : указывает диапазоны ячеек для просмотра, чтобы найти данные.
index: указывает, в каком столбце искать. Первый столбец представлен как 1. Значение должно быть между 1 и общим количеством столбцов.
is-sorted: необязательный аргумент, указывающий, отсортирован диапазон или нет. Если оставить пустым, значение по умолчанию равно TRUE. Или вы можете ввести FALSE, если данные, которые у вас есть, не отсортированы.

Примеры ВПР в Google Таблицах

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

Выполнение простой функции ВПР

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

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

Вот как выполнить поиск с помощью функции VLOOKUP Google Sheets:

Щелкните соответствующую пустую ячейку.
Введите первую часть функции: =ВПР(
Введите первый аргумент. В этом примере ячейка с номером Сотрудник № 7 имеет вид A8 .
Введите запятую
Введите следующий аргумент, чтобы определить диапазон ячеек. В приведенном выше примере диапазон ячеек равен A1:C11 .
Введите еще одну запятую.
Введите аргумент, определяющий столбец для поиска. В данном примере это столбец 3 .
Нажмите Введите .

Вложенная функция в функции ВПР

Google Таблицы позволяют использовать функцию внутри другой формулы. Это называется вложением. В этом примере мы хотим найти сотрудника, совершившего наименьший объем продаж.

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

Нажмите на соответствующую пустую ячейку.
Введите первую часть формулы, =VLOOKUP(
Используйте функцию MIN в качестве первого аргумента, введя MIN( . Во вложенную формулу не нужно добавлять знак равенства.
Введите диапазон, который необходимо проверить, чтобы найти минимальное значение. В приведенном выше примере это A2:A11 .
Введите закрывающую скобку для формулы MIN.
Введите запятую.
Введите диапазон ячеек для формулы. В этом примере диапазон A1:C11 .
Введите еще одну запятую.
Добавьте порядковый номер столбца для поиска. В приведенном выше примере это 3 .
В качестве последнего аргумента введите FALSE , так как данные, которые у нас есть, не отсортированы.
Введите закрывающую скобку для формулы.
Нажмите Введите для выполнения поиска.

Продолжайте поиск с помощью функции ВПР в Google Таблицах

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