Содержание:
- Функция ВПР в Excel – общее описание и синтаксис
- Примеры ВПР
- Точное или приблизительное совпадение в функции ВПР.
- ВПР в Excel – об этом нужно помнить!
Сегодня мы начинаем серию статей, описывающих одну из самых полезных функций Excel — ВПР (ВПР). Эта функция в то же время является одной из самых сложных и наименее изученных.
В этом уроке по ВПР Я постараюсь изложить основы максимально просто, чтобы сделать процесс обучения максимально понятным для неопытных пользователей. Кроме того, мы изучим несколько примеров с формулами Excel, которые продемонстрируют наиболее распространенные варианты использования функции. ВПР.
Функция ВПР в Excel – общее описание и синтаксис
Так что это ВПР? Ну, во-первых, это функция Excel. Что она делает? Он ищет указанное вами значение и возвращает соответствующее значение из другого столбца. Технически говоря, ВПР ищет значение в первом столбце заданного диапазона и возвращает результат из другого столбца в той же строке.
В наиболее распространенном приложении функция ВПР ищет в базе данных заданный уникальный идентификатор и извлекает из базы данных некоторую информацию, связанную с ним.
Первая буква в названии функции ВПР (ВПР) означает Ввертикальный (Vвертикальный). По нему можно отличить ВПР от GPR (HLOOKUP), который ищет значение в верхней строке диапазона — Ггоризонтальный (Hпо горизонтали).
Функция ВПР доступно в Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP и Excel 2000.
Синтаксис функции ВПР
Функция ВПР (ВПР) имеет следующий синтаксис:
VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])
Как видите, функция ВПР в Microsoft Excel есть 4 параметра (или аргумента). Первые три являются обязательными, последний – необязательным.
- искомое_значение (lookup_value) – значение для поиска. Это может быть значение (число, дата, текст) или ссылка на ячейку (содержащую искомое значение) или значение, возвращаемое какой-либо другой функцией Excel. Например, эта формула будет искать значение 40:
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)
Если искомое значение меньше наименьшего значения в первом столбце искомого диапазона, функция ВПР сообщит об ошибке #В (#Н/Д).
- таблица_массив (таблица) – два или более столбца данных. Помните, функция ВПР всегда ищет значение в первом столбце диапазона, указанного в аргументе таблица_массив (стол). Доступный для просмотра диапазон может содержать различные данные, такие как текст, даты, числа, логические значения. Функция нечувствительна к регистру, то есть символы верхнего и нижнего регистра считаются одинаковыми. Итак, наша формула будет искать значение 40 в клетках из A2 в A15, поскольку A — это первый столбец диапазона A2:B15, указанного в аргументе. таблица_массив (стол):
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)
- номер_столбца (column_number) — номер столбца в заданном диапазоне, из которого будет возвращено значение в найденной строке. Крайний левый столбец в заданном диапазоне: 1, второй столбец 2, третий столбец 3 и так далее. Теперь вы можете прочитать всю формулу:
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)Формула поиска ценности 40 В диапазоне A2: A15 и возвращает соответствующее значение из столбца B (поскольку B — второй столбец в диапазоне A2:B15).
Если значение аргумента номер_столбца (column_number) меньше чем 1тогда ВПР сообщит об ошибке #СТОИМОСТЬ! (#ЦЕНИТЬ!). И если оно больше количества столбцов в диапазоне таблица_массив (таблица), функция вернет ошибку #REF! (#СВЯЗЬ!).
- диапазон_поиска (range_lookup) – определяет, что искать:
- точное совпадение, аргумент должен быть равен НЕПРАВДА (ЛОЖЬ);
- приблизительное совпадение, аргумент равен ИСТИННЫЙ КОД (ИСТИНА) или не указано вообще.
Этот параметр не является обязательным, но очень важным. Далее в этом уроке ВПР Я покажу вам несколько примеров, объясняющих, как писать формулы для поиска точных и приблизительных совпадений.
Примеры ВПР
Я надеюсь, что функция ВПР станет для вас немного яснее. Теперь давайте рассмотрим некоторые варианты использования ВПР в формулах с реальными данными.
Как использовать ВПР для поиска в другом листе Excel
На практике формулы с функцией ВПР редко используются для поиска данных на одном листе. Чаще всего вы будете искать и получать соответствующие значения из другого листа.
Для того чтобы использовать ВПР, выполните поиск в другом листе Microsoft Excel. Вы должны в аргументе таблица_массив (таблица) укажите имя листа с восклицательным знаком, за которым следует диапазон ячеек. Например, следующая формула показывает, что диапазон А2: В15 находится на листе с именем Sheet2.
=VLOOKUP(40,Sheet2!A2:B15,2)
=ВПР(40;Sheet2!A2:B15;2)
Конечно, имя листа не обязательно вводить вручную. Просто начните вводить формулу, и когда дело дойдет до аргумента таблица_массив (таблицу), переключитесь на нужный лист и выделите мышкой нужный диапазон ячеек.
Формула, показанная на снимке экрана ниже, ищет текст «Продукт 1» в столбце A (это первый столбец диапазона A1:B2) на листе. Цены.
=VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)
=ВПР("Product 1";Prices!$A$2:$B$9;2;ЛОЖЬ)
Помните, что при поиске текстового значения его необходимо заключить в кавычки («»), как это обычно делается в формулах Excel.
Для аргументации таблица_массив (таблица) желательно всегда использовать абсолютные ссылки (со знаком $). В этом случае диапазон поиска останется неизменным при копировании формулы в другие ячейки.
Поиск в другой книге с помощью VLOOKUP
Функционировать ВПР работал между двумя книгами Excel, перед именем листа необходимо указать имя книги в квадратных скобках.
Например, ниже приведена формула, которая ищет значение 40 на листе Sheet2 В книге Числа.xlsx:
=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)
=ВПР(40;[Numbers.xlsx]Sheet2!A2:B15;2)
Вот самый простой способ создать формулу в Excel с помощью ВПРкоторый ссылается на другую книгу:
- Откройте обе книги. Это не обязательно, но таким образом проще создать формулу. Вы не хотите вводить имя книги вручную, не так ли? Кроме того, это защитит вас от случайных опечаток.
- Начните вводить функцию ВПРи когда дело доходит до спора таблица_массив (таблицу), переключитесь на другую книгу и выберите в ней необходимый диапазон поиска.
На снимке экрана ниже показана формула с поиском по диапазону в книге. Прайс-лист.xlsx на листе Цены.
Функция ВПР будет работать, даже если вы закроете искомую книгу и полный путь к файлу книги появится в строке формул, как показано ниже:
Если имя книги или листа содержит пробелы, то его необходимо заключить в апострофы:
=VLOOKUP(40,'[Numbers.xlsx]Sheet2'!A2:B15,2)
=ВПР(40;'[Numbers.xlsx]Sheet2'!A2:B15;2)
Как использовать именованный диапазон или таблицу в формулах с помощью ВПР
Если вы планируете использовать один и тот же диапазон поиска в нескольких функциях ВПР, вы можете создать именованный диапазон и ввести его имя в формулу в качестве аргумента таблица_массив (Таблица).
Чтобы создать именованный диапазон, просто выделите ячейки и введите подходящее имя в поле. Имя, слева от строки формул.
Теперь вы можете записать следующую формулу для нахождения цены товара. Продукт 1:
=VLOOKUP("Product 1",Products,2)
=ВПР("Product 1";Products;2)
Большинство имен диапазонов подходят для всей книги Excel, поэтому нет необходимости указывать имя листа для аргумента. таблица_массив (таблица), даже если формула и диапазон поиска находятся на разных листах. Если они находятся в разных книгах, то перед названием диапазона нужно указать имя книги, например так:
=VLOOKUP("Product 1",PriceList.xlsx!Products,2)
=ВПР("Product 1";PriceList.xlsx!Products;2)
Так формула выглядит гораздо понятнее, согласитесь? Кроме того, использование именованных диапазонов является хорошей альтернативой абсолютным ссылкам, поскольку именованный диапазон не меняется при копировании формулы в другие ячейки. Это означает, что вы можете быть уверены, что диапазон поиска в формуле всегда останется правильным.
Если преобразовать диапазон ячеек в полноценную электронную таблицу Excel с помощью команды Настольные Вкладка (Таблица) Вносимые (Вставить), то при выборе диапазона с помощью мыши Microsoft Excel автоматически добавит имена столбцов (или имя таблицы, если вы выберете всю таблицу) в формулу.
Готовая формула будет выглядеть примерно так:
=VLOOKUP("Product 1",Table46[[Product]:[Price]],2)
=ВПР("Product 1";Table46[[Product]:[Price]];2)
Или, может быть, даже так:
=VLOOKUP("Product 1",Table46,2)
=ВПР("Product 1";Table46;2)
При использовании именованных диапазонов ссылки будут указывать на одни и те же ячейки независимо от того, куда вы копируете функцию. ВПР внутри рабочей книги.
Использование подстановочных знаков в формулах ВПР
Как и многие другие функции, ВПР Вы можете использовать следующие подстановочные знаки:
- Вопросительный знак (?) – заменяет любой одиночный символ.
- Звездочка (*) – заменяет любую последовательность символов.
Использование подстановочных знаков в функциях ВПР может быть полезен во многих случаях, например:
- Когда вы не помните точно текст, который вам нужно найти.
- Когда вы хотите найти какое-то слово, которое является частью содержимого ячейки. Знай это ВПР поиск по содержимому ячейки в целом, как если бы опция включена Соответствие всему содержимому ячейки (Вся ячейка) в стандартном поиске Excel.
- Когда ячейка содержит лишние пробелы в начале или конце содержимого. В такой ситуации можно долго ломать голову, пытаясь понять, почему формула не работает.
Пример 1. Поиск текста, который начинается или заканчивается определенными символами.
Допустим, вы хотите найти конкретного клиента в базе данных, показанной ниже. Вы не помните его фамилию, но знаете, что она начинается на «ак». Вот формула, которая отлично справится с этой задачей:
=VLOOKUP("ack*",$A$2:$C$11,1,FALSE)
=ВПР("ack*";$A$2:$C$11;1;ЛОЖЬ)
Теперь, когда вы уверены, что нашли правильное имя, вы можете использовать ту же формулу, чтобы найти сумму, уплаченную этим клиентом. Для этого достаточно изменить третий аргумент функции ВПР до нужного номера столбца. В нашем случае это столбец C (3-й в диапазоне):
=VLOOKUP("ack*",$A$2:$C$11,3,FALSE)
=ВПР("ack*";$A$2:$C$11;3;ЛОЖЬ)
Вот еще несколько примеров с подстановочными знаками:
~ Найдите имя, оканчивающееся на «мужчина»:
=VLOOKUP("*man",$A$2:$C$11,1,FALSE)
=ВПР("*man";$A$2:$C$11;1;ЛОЖЬ)
~ Найдите имя, которое начинается с «ad» и заканчивается на «son»:
=VLOOKUP("ad*son",$A$2:$C$11,1,FALSE)
=ВПР("ad*son";$A$2:$C$11;1;ЛОЖЬ)
~ Находим первое имя в списке, состоящем из 5 символов:
=VLOOKUP("?????",$A$2:$C$11,1,FALSE)
=ВПР("?????";$A$2:$C$11;1;ЛОЖЬ)
Функционировать ВПР с подстановочными знаками работает корректно, в качестве четвертого аргумента всегда следует использовать НЕПРАВДА (ЛОЖЬ). Если диапазон поиска содержит более одного значения, соответствующего условиям поиска с подстановочными знаками, будет возвращено первое найденное значение.
Пример 2. Объединение подстановочных знаков и ссылок на ячейки в формулах ВПР.
Теперь давайте рассмотрим чуть более сложный пример поиска с помощью функции ВПР по значению в ячейке. Представьте себе, что столбец A — это список лицензионных ключей, а столбец B — список имен, владеющих лицензией. Кроме того, у вас в ячейке С1 находится часть (несколько символов) какого-то лицензионного ключа, и вы хотите найти имя владельца.
Это можно сделать, используя следующую формулу:
=VLOOKUP("*"&C1&"*",$A$2:$B$12,2,FALSE)
=ВПР("*"&C1&"*";$A$2:$B$12;2;FALSE)
Эта формула ищет значение из ячейки C1 в заданном диапазоне и возвращает соответствующее значение из столбца B. Обратите внимание, что в первом аргументе мы используем символ амперсанда (&) до и после ссылки на ячейку, чтобы связать текстовую строку.
Как видно на рисунке ниже, функция ВПР возвращает «Джереми Хилл», поскольку его лицензионный ключ содержит последовательность символов из ячейки C1.
Обратите внимание, что аргумент таблица_массив (таблица) на скриншоте выше содержит имя таблицы (Таблица7) вместо указания диапазона ячеек. Это то, что мы сделали в предыдущем примере.
Точное или приблизительное совпадение в функции ВПР.
И, наконец, давайте подробнее рассмотрим последний аргумент, который указан для функции. ВПР – диапазон_поиска (интервал_просмотр). Как уже говорилось в начале урока, этот аргумент очень важен. В одной и той же формуле с ее значением можно получить совершенно разные результаты ИСТИННЫЙ КОД (ИСТИНА) или НЕПРАВДА (ЛОЖЬ).
Для начала давайте выясним, что означает Microsoft Excel под точными и приблизительными совпадениями.
- Если аргумент диапазон_поиска (range_lookup) равен НЕПРАВДА (FALSE), формула ищет точное совпадение, т. е. точно такое же значение, как указано в аргументе. искомое_значение (искомое_значение). Если в первом столбце диапазона tспособный_массив (таблица) встречает два или более значений, соответствующих аргументу искомое_значение (search_value), то будет выбран первый. Если совпадений не обнаружено, функция сообщит об ошибке. #В (#Н/Д). Например, следующая формула сообщит об ошибке #В (#Н/Д), если в диапазоне A2:A15 нет значения. 4:
=VLOOKUP(4,A2:B15,2,FALSE)=ВПР(4;A2:B15;2;ЛОЖЬ) - Если аргумент диапазон_поиска (range_lookup) равен ИСТИННЫЙ КОД (ИСТИНА), формула ищет приблизительное совпадение. Точнее, сначала функция ВПР ищет точное совпадение, а если ничего не найдено, выбирает приблизительное. Приблизительное совпадение — это наибольшее значение, не превышающее значение, указанное в аргументе. искомое_значение (искомое_значение).
Если аргумент диапазон_поиска (range_lookup) равен ИСТИННЫЙ КОД (TRUE) или не указано, то значения в первом столбце диапазона следует отсортировать по возрастанию, то есть от наименьшего к наибольшему. В противном случае функция ВПР может вернуть ошибочный результат.
Чтобы лучше понять важность выбора ИСТИННЫЙ КОД (ПРАВДА) или НЕПРАВДА (FALSE), давайте рассмотрим еще несколько формул с функцией ВПР и посмотрите на результаты.
Пример 1. Поиск точного совпадения с помощью VLOOKUP
Как вы помните, для поиска точного совпадения используется четвертый аргумент функции ВПР должно иметь значение НЕПРАВДА (ЛОЖЬ).
Вернёмся к таблице из самого первого примера и выясним, какое животное может двигаться со скоростью 50 миль в час. Полагаю, что эта формула не вызовет у вас затруднений:
=VLOOKUP(50,$A$2:$B$15,2,FALSE)
=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)
Обратите внимание, что наш диапазон поиска (столбец A) содержит два значения. 50 - в клетках A5 и A6. Формула возвращает значение из ячейки B5. Почему? Потому что при поиске точного совпадения функция ВПР использует первое найденное значение, соответствующее искомому.
Пример 2. Использование ВПР для поиска приблизительного совпадения
Когда вы используете функцию ВПР для поиска приблизительного совпадения, т.е. когда аргумент диапазон_поиска (range_lookup) равен ИСТИННЫЙ КОД (TRUE) или опущено, первое, что вам нужно сделать, это отсортировать диапазон по первому столбцу в порядке возрастания.
Это очень важно, поскольку функция ВПР возвращает следующее по величине значение после заданного, а затем поиск останавливается. Если вы пренебрегаете правильной сортировкой, то получите очень странные результаты или сообщение об ошибке. #В (#Н/Д).
Теперь вы можете использовать одну из следующих формул:
=VLOOKUP(69,$A$2:$B$15,2,TRUE) or =VLOOKUP(69,$A$2:$B$15,2)
=ВПР(69;$A$2:$B$15;2;ИСТИНА) or =ВПР(69;$A$2:$B$15;2)
Как видите, я хочу узнать, какое из животных имеет наибольшую скорость к 69 миль в час. И вот результат, который мне вернула функция ВПР:
Как видите, формула вернула результат Антилопа (Антилопа), скорость которой 61 миль в час, хотя в список также входят Гепард (Гепард), который бежит на скорости 70 миль в час, а 70 — это ближе к 69, чем к 61, не так ли? Почему это происходит? Поскольку функция ВПР при поиске приблизительного совпадения возвращает наибольшее значение, не превышающее искомое.
Надеюсь, эти примеры прольют свет на работу с функцией ВПР в Excel, и вы больше не смотрите на нее как на постороннего человека. Теперь не помешает кратко повторить ключевые моменты изученного нами материала, чтобы лучше закрепить его в памяти.
ВПР в Excel – об этом нужно помнить!
- Функция ВПР Excel не может смотреть налево. Он всегда ищет значение в крайнем левом столбце диапазона, заданного аргументом. таблица_массив (Таблица).
- В функции ВПР все значения нечувствительны к регистру, т.е. маленькие и большие буквы эквивалентны.
- Если искомое значение меньше минимального значения в первом столбце искомого диапазона, функция ВПР сообщит об ошибке #В (#Н/Д).
- Если третий аргумент номер_столбца (column_number) меньше чем 1функция ВПР сообщит об ошибке #СТОИМОСТЬ! (#ЦЕНИТЬ!). Если оно больше количества столбцов в диапазоне таблица_массив (таблица), функция сообщит об ошибке #REF! (#СВЯЗЬ!).
- Используйте абсолютные ссылки на ячейки в аргументе таблица_массив (таблица), чтобы при копировании формулы сохранялся правильный диапазон поиска. В качестве альтернативы попробуйте использовать именованные диапазоны или таблицы в Excel.
- При поиске приблизительного соответствия помните, что первый столбец искомого диапазона должен быть отсортирован по возрастанию.
- Наконец, помните о важности четвертого аргумента. Использовать значения ИСТИННЫЙ КОД (ПРАВДА) или НЕПРАВДА (ЛОЖЬ) сознательно, и вы избавитесь от многих головных болей.
В следующих статьях нашего руководства по функциям ВПР в Excel мы изучим более сложные примеры, например, выполнение различных вычислений с помощью ВПР, извлечение значений из нескольких столбцов и многое другое. Спасибо за прочтение этого урока и надеюсь увидеть вас снова на следующей неделе!