Excel популярна благодаря своей доступности и простоте, так как не требует особых знаний и навыков. Табличный вид предоставления информации понятен любому пользователю, а широкий включающих "Мастер функции", позволяет проводить любые манипуляции и расчеты с предоставленными данными.
Одной из широко известных формул Excel является вертикальный просмотр. Использование функции ВПР на первый взгляд кажется довольно сложным, но это только поначалу.
При работе с формулой ВПР следует учитывать, что она производит поиск искомого значения исключительно по столбцам, а не по строкам. Для применения функции требуется минимальное количество столбцов - два, максимальное отсутствует.
Функция ВПР производит поиск заданного критерия, который может иметь любой формат (текстовый, числовой, денежный, по дате и времени и т. д.) в таблице. В случае нахождения записи она выдает (подставляет) значение, занесенное в той же строке, но с искомого столбца таблицы, то есть соответствующее заданному критерию. Если искомое значение не находится, то выдается ошибка #Н/Д (в англоязычном варианте #N/А).
Функция ВПР приходит на помощь оператору, когда требуется быстро найти и применить в дальнейших расчетах, анализе или прогнозе определенное значение из таблицы больших размеров. Главное при использовании данной формулы - следить, чтобы заданная область поиска была правильно выбрана. Она должна включать все записи, то есть начиная с первой по последнюю.
Самый частый случай применения ВПР - это сравнение или добавление данных, находящихся в двух таблицах, при использовании определенного критерия. Причем диапазоны поиска могут быть большими и вмещать тысячи полей, размещаться на разных листах или книгах.
Показана функция ВПР, как пользоваться ею, как проводить расчеты, в качестве примера на рисунке выше. Здесь рассматривается таблица размеров розничных продаж в зависимости от региона и менеджера. Критерием поиска служит конкретный менеджер (его имя и фамилия), а искомым значением является сумма его продаж.
В результате работы функции ВПР (VLOOKUP) формируется новая таблица, в которой конкретному искомому менеджеру быстро сопоставляется его сумма продаж.
Расположена формула ВПР во вкладке "Мастер функций" и разделе "Ссылки и массивы". Диалоговое окно функции имеет следующий вид:
Аргументы в формулу вносятся в порядке очереди:
Функция ВПР пример использования может иметь следующий: при ведении дел торгового предприятия в таблицах Excel в столбце А записано наименование продукции, а в колонке В - соответствующая цена. Для составления предложения в столбце С нужно отыскать стоимость на определенный продукт, которую требуется вывести в колонке Д.
Формула, записанная в Д, будет выглядеть так: =ВПР (С1; А1:В5; 2; 0), то есть =ВПР (искомое значение; диапазон данных таблицы; порядковый номер столбца; 0). В качестве четвертого аргумента вместо 0 можно использовать ЛОЖЬ.
Для заполнения таблицы предложения полученную формулу необходимо скопировать на весь столбец Д.
Закрепить область рабочего диапазона данных можно при помощи абсолютных ссылок. Для этого вручную проставляются знаки $ перед буквенными и численными значениями адресов крайних левых и правых ячеек таблицы. В нашем случае формула принимает вид: =ВПР (С1; $А$1:$В$5; 2; 0).
Функция ВПР не работает, и тогда появляется сообщение в столбце вывода результата об ошибке (#N/A или #Н/Д). Это происходит в таких случаях:
Причем при организации новой сводной таблицы заданные искомые критерии могут находиться в любом порядке и последовательности и не обязательно вмещаться полным списком (частичная выборка).
Ошибка под №5 является довольно распространенной и наглядно изображена на рисунке ниже.
В данном примере список имен согласно нумерации отсортирован не по возрастанию, а по ниспадающему значению. Причем в качестве интервального просмотра использован (1), который сразу прерывает поиск при обнаружении значения большего, чем искомое, поэтому выдается ошибка.
При применении 1 или ИСТИНЫ в четвертом аргументе нужно следить, чтобы столбец с искомыми критериями был отсортирован по возрастанию. При использовании 0 или ЛЖИ данная необходимость отпадает, но также отсутствует тогда возможность интервального просмотра.
Просто следует учитывать, что особенно важно сортировать интервальные таблицы. Иначе функция ВПР будет выводить в ячейки неправильные данные.
Для удобства работы с такой формулой можно озаглавить диапазон таблицы, в которой проводится поиск (второй аргумент), как это показано на рисунке.
В данном случае область таблицы продаж озаглавлена. Для этого выделяется таблица, за исключением заголовков столбцов, и в поле имени (слева под панелью вкладок) присваивается ей название.
Другой вариант - озаглавить - подразумевает выделение диапазона данных, потом переход в меню "Вставка"- "Имя"- "Присвоить".
Для того чтобы использовать данные, размещенные на другом листе рабочей книги, при помощи функции ВПР, необходимо во втором аргументе формулы прописать расположение диапазона данных. Например, =ВПР 2; 0), где Лист2! - является ссылкой на требуемый лист книги, а $А$1:$В$5 - адрес диапазона поиска данных.
Довольно удобно в Excel ВПР-функцию применять не только фирмам, занимающимся торговлей, но и учебным учреждениям для оптимизации процесса сопоставления учеников (студентов) с их оценками. Примеры данных задач показаны на рисунках ниже.
Существуют две таблицы со списками студентов. Одна с их оценками, вторая указывает возраст. Необходимо сопоставить обе таблицы так, чтобы наравне с возрастом учащихся выводились и их оценки, то есть ввести дополнительный столбец во втором списке.
Функция ВПР отлично справляется с решением данной задачи. В столбце G под заголовком "Оценки" записывается соответствующая формула: =ВПР (Е4, В3:С13, 2, 0). Ее нужно скопировать на всю колонку таблицы.
В результате выполнения функция ВПР выдаст оценки, полученные определенными студентами.
Еще один пример применения функции ВПР - это организация поисковой системы, когда в базе данных согласно заданному критерию следует найти соответствующее ему значение. Так, на рисунке показан список с кличками животных и их принадлежность к определенному виду.
При помощи ВПР создается новая таблица, в которой легко найти по кличке животного его вид. Актуальны подобные поисковые системы при работе с большими списками. Для того чтобы вручную не пересматривать все записи, можно быстро воспользоваться поиском и получить требуемый результат.
Здравствуй уважаемый читатель!
В этой статье я хочу описать, наверное, одну из наиболее полезных функций в Excel – это функция ВПР (VLOOKUP). О функции можно сказать многое, но самое первое, что стоить отметить, это то, что функция относится к сложным и наименее понятной.
В статье я постараюсь простым и доступным языком описать, как работает функция ВПР, а также на примерах показать ее особенности, описание и синтаксис.
Итак, что же в сущности это за функция, что она делает, и как она пишется. Самое простое объяснение, о работе функции ВПР, она ищет в перечне значений, по заданному вами критерию, уникальному идентификатору, определенную информацию, которая с ним связана.
Если розшифровать название функции ВПР, то с первой буквой станет понятно, как работает, эта функция, В – означает «вертикальная»
, то есть она ищет значения в , а вот для горизонтальных списков у нас будет . Впервые функция ВПР стала доступна нам с Excel 2000.
Функция ВПР в Excel имеет следующий синтаксис:
=ВПР(_искомое значение_;_таблица_; _номер столбика_; _[интервальный просмотр]_) , где:
Теперь на примере рассмотрим, как произвести поиск, каких-либо данных на другом листе, так как на практике, функцию ВПР очень редко используют для поиска на текущем листе:
ВПР(«GM»;$A$5:$B$10;2)
Формула ищет текст «GM», в столбике А на текущем листе.
Совет!
При использовании аргумента «таблица», желательно использовать, такой вариант, как (это адрес ячейки со знаком $). В этом случае диапазон поиска станет закреплённым и не изменится при копировании формулы.
Рассмотрим пример поиска значений, как работает функция ВПР в другой рабочей книге:
ВПР(”GM”;[Путь к файлу]База!А2:В10;2)
Как видите ничего сложного, просто усложнился аргумент «таблица» , теперь к нему прикреплена ссылка на файл с его именем.
Совет! Ввод формулы лучше делать при включённых двух файлах, на одном вы вводите функцию ВПР, а когда дойдете до аргумента «таблица», перейдете на второй файл и мышкой выделите нужный вам диапазон для поиска.
Еще можно рассмотреть пример, когда точное значение нам неизвестно мы будем использовать в формуле ВПР символы подстановки. Иногда нам нужно найти не что-то конкретное, а нечто обладающее общими признаками, как вариант у нас есть много диванов разных моделей, как и прочей мебели, но выбрать нам надо только их.
Функция ВПР, при подстановке,может использовать такие символы:
ВПР(“A*”;$A$2:$B$10;1;ЛОЖЬ)
Совет!
Для того что бы функция ВПР, корректно работала нужно в качестве четвертого аргумента использовать параметр «ЛОЖЬ».
Ну, если мы уже затронули тему точного или приближенного совпадения в синтаксисе функции ВПР, то давайте ее рассмотрим поподробнее:
Совет! В случае, когда аргумент «интервальный просмотр» ровняется «ИСТИНА» или же не указан, значение в первом столбике нужно , от меньшего к большому. Иначе имеется возможность ошибочного результата от функции ВПР.
Рассмотрим, как работает функция ВПР, когда производится поиск по точному. Для примера, попытаемся найти, какой автомобиль движется со скоростью 200 км/час. Я думаю, такая формула не будет для вас сложной:
=ВПР(200;$A$2:$B$15;2;ЛОЖЬ)
Несмотря на то, что значений 200 у нас несколько, получили только одно, так как при точном совпадении функции ВПР, система использует только первое значение которое было найдено в указанном диапазоне.
Теперь испытаем, работу функции ВПР для приблизительного совпадения значений. Поищем, какой автомобиль ездит со скоростью 260 км/час. Первое что вы делаете в случае, когда «интервальный просмотр»
равняется «ИСТИНА»
— вы выполняете сортировку вашего диапазона значений по первому столбику по порядку его возрастания. Это необходимо и важно, поскольку функция ВПР находит следующее наибольшее значение от заданного условия, а после поиск прекращается. Если же вы не последуете совету по сортировке, итогом будет сообщение об или другие странные результаты.
Для поиска используем ВПР следующего вида:
=ВПР(260;$A$2:$B$15;2;ИСТИНА)
Итак, наша формула вернула найденное значение 240, хотя у нас есть и значение 270 и это значение вроде как ближе, но особенность того, как работает функция ВПР, это то что при использовании точного совпадения она ищет самое большое значение в списке, но не превышающие его.
Я думаю примеры, которые я рассматривал в статье, помогут вам понятьвсё о работе функции ВПР, как ее используют и для чего ее нужно использовать.
Хотя есть и множество более продвинутых примеров, которые мы рассмотрим в других статьях.
А на этом у меня всё! Я очень надеюсь, что всё вышеизложенное вам понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!
В Microsoft Excel очень много различных функций, которые значительно облегчают работу пользователю, и в данной статье мы поговорим про одну из таких. Называется она ВПР , а если на английском, то VLOOKUP .
Функция ВПР переносит значения из одной таблицы в определенные ячейки другой. Давайте объясню подробнее – в первой таблице Вы выбираете значение, которое нужно найти в левом столбце второй. Если есть совпадения, то ВПР переносит значение из указанного Вами столбца этой строки в первую таблицу. Хоть определение немного запутанное, пользоваться функцией не так уж и сложно. Рассмотрим несколько примеров.
Поскольку используют функцию чаще всего для заполнения столбца с ценой, которая указана в отдельных прайсах, то возьмем следующий пример. Например, есть таблица с фруктами (оранжевая), где для каждого указано, сколько килограмм мы хотим заказать. Соответственно, каждому фрукту необходимо выписать цену из прайса (зеленый), который дал поставщик, и потом рассчитать, сколько обойдется закупка. Просматривать каждое наименование и переносить данные сложно, тем более, если строчек с товарами тысячи, поэтому применим ВПР.
Нажмите по верхней ячейке в первой таблице в столбце Цена, а потом кнопочку «fx» в строке формул, чтобы открыть окно мастера функций.
Следующее, что мы делаем – прописываем аргументы в предложенные поля.
Ставьте курсив в поле «Искомое_значение» и выделяйте в первой таблице то значение, которое будем искать. У меня это яблоко.
В строке «Таблица» необходимо выделить ту, из которой будут браться данные – шапку не выделяйте. Учтите, что крайний левый столбец должен состоять из значений, которые мы ищем. То есть, мне нужно яблоко и другие фрукты, значит, их перечень должен быть в левом столбце выделяемой области.
Чтобы после того, как мы напишем формулу и растянем ее по всему столбцу, выбранный диапазон не смещался вниз, нужно сделать ссылки абсолютными: выделите данные в поле и нажмите «F4» . Теперь адрес на ячейки стал абсолютным, к ним добавился знак «$» , и диапазон смещаться не будет.
Там, где номер столбца, поставьте цифру, соответствующую во второй таблице тому столбцу, данные откуда нужно переносить. У меня прайс состоит из фруктов и цены, мне нужно второе, поэтому ставлю цифру «2» .
В «Интервальный_просмотр» пишем «ЛОЖЬ» – если искать нужно точные совпадения, или «Истина» – если значения могут быть приближенные. Для нашего примера выбираем первое. Если ничего не указать в данном поле, то по умолчанию выберется второе. Потом нажимайте «ОК» .
Здесь обратите внимание на следующее, если работаете с числами и указываете «Истина» , то вторая таблица (это наш прайс) обязательно должна быть отсортирована по возрастанию. Например, при поиске 5,25 найдется 5,27 и возьмутся данные с этой строки, хотя ниже может еще быть и число 5,2599 – но формула дальше смотреть не будет, поскольку она думает, что ниже числа только больше.
Как же работает ВПР? Она берет искомое значение (яблоко) и ищет его в крайнем левом столбце указанного диапазона (перечень фруктов). При совпадении берется значение из этой же строки, только того столбца, который указан в аргументах (2 ), и переносится в нужную нам ячейку (С2 ). Формула выглядит так:
ВПР(А2;$G$2:$H$12;2;ЛОЖЬ)
Теперь можете растянуть ее на необходимое количество строчек вниз, потянув за правый нижний угол.
Все цены перенесены из прайса в таблицу закупок в соответствии с названиями фруктов.
Если у Вас в первой таблице есть названия продуктов, которых нет в прайсе, у меня это овощи, то напротив данных пунктов формула ВПР выдаст ошибку #Н/Д .
При добавлении столбцов на лист, данные для аргумента «Таблица» функции автоматически изменятся. В примере прайс сдвинут на 2 столбца вправо. Выделим любую ячейку с формулой и видим, что вместо $G$2:$H$12 теперь $I$2:$J$14 .
Теперь давайте разберемся, как работать с функцией ВПР в Эксель и с выпадающими списками. Для начала нужно сделать выпадающий список . Выделяем ячейку, вверху открываем «Данные» и жмем по кнопочке «Проверка данных» .
В открывшемся окне «Тип данных» будет «Список» , ниже указываем область источника – это названия фруктов, то есть тот столбец, который есть и в первой и во второй таблице. Нажимайте «ОК» .
Выделяю F2 и вставляю функцию ВПР. Аргумент первый – это сделанный список (F1 ).
Второй – таблица поставщика с указанными ценами. Помните, что здесь левый столбец должен совпадать с теми данными, из которых состоит выпадающий список.
Получилось что-то вроде поиска: выбираем фрукт и ВПР находит в прайсе его цену.
Если у Вас были одни цены, а потом поставщики дали новый список, то нужно как-то просмотреть, что поменялось. Делать это вручную долго, а вот с помощью рассматриваемой функции все можно сделать очень быстро. Потребуется добавить один столбец и перенести в него новые значения, а потом просто сравнить данные.
Жмем по любой ячейке в столбце D и вставляем один новый.
Я назвала его Новый прайс – здесь будут новые цены, а в столбце слева, указаны старые. Новая таблица у меня находится на другом листе, чтобы Вам стало понятно, как использовать ВПР, если данные расположены на разных листах. В добавленном столбце выделяем первую пустую ячейку.
Вставляем функцию и указываем аргументы. Сначала то, что будем искать, в примере яблоко (А2 ). Для выбора диапазона из нового прайса, поставьте курсор в поле «Таблица» и перейдите на нужный лист, у меня «Лист1» .
Выделяем мышкой необходимые столбцы и строки, без заголовков.
Дальше делаем абсолютные ссылки на ячейки: «Лист1!$A$2:$B$12» . Выделите строчку и нажмите «F4» , чтобы к адресам ячеек добавился знак доллара. Указываем столбец (2 ) и пишем «ЛОЖЬ» .
В конце нажмите кнопку «ОК» .
Теперь два столбца с новой и старой ценой расположены рядом и можно сделать или визуальное сравнение, или применив определенные формулы, или условное форматирование.
Надеюсь, у меня получилась пошаговая инструкция по использованию и применению функции ВПР в Excel, и Вам теперь все понятно.
Оценить статью: (15
оценок, среднее: 5,00
из 5)
Вебмастер. Высшее образование по специальности "Защита информации".. Автор большинства статей и уроков компьютерной грамотности
Наверняка многим активным пользователям табличного редактора Excel периодически приходилось сталкиваться с ситуациями, в которых возникала необходимость подставить значения из одной таблицы в другую. Вот представьте, на ваш склад зашёл некий товар. В нашем распоряжении имеется два файла: один с перечнем наименований полученного товара, второй - прайс-лист этого самого товара. Открыв прайс-лист, мы обнаруживаем, что позиций в нём больше и расположены они не в той последовательности, что в файле с перечнем наименований. Вряд ли кому-то из нас понравится идея сверить оба файла и перенести цены из одного документа в другой вручную. Разумеется, в случае, когда речь идёт о 5–10 позициях, механическое внесение данных вполне возможно, но что делать, если число наименований переваливает за 1000? В таком случае справиться с монотонной работой нам поможет Excel и его волшебная функция ВПР (или vlookup, если речь идёт об англоязычной версии программы).
Итак, в начале нашей работы по преобразованию данных из одной таблицы в другую будет уместным сделать небольшой обзор функции ВПР. Как вы, наверное, уже успели понять, vlookup позволяет переносить данные из одной таблицы в другую, заполняя тем самым необходимые нам ячейки автоматически. Для того чтобы функция ВПР работала корректно, обратите внимание на наличие в заголовках вашей таблицы объединённых ячеек. Если таковые имеются, вам необходимо будет их разбить.
Допустим, нам необходимо заполнить «Таблицу заказов» данными из «Прайс листа»
Итак, перед нами стоит задача - перенести цены имеющихся товаров в таблицу с их наименованиями и рассчитать общую стоимость каждого товара. Чтобы это сделать, нам предстоит выполнить следующий алгоритм:
Теперь, когда все необходимые действия выполнены, нам остаётся лишь подтвердить их нажатием кнопки «ОК». Как только в первой ячейке изменятся данные, нам нужно будет применить функцию ВПР ко всему Excel документу. Для этого достаточно размножить VLOOKUP по всему столбцу «Цена». Сделать это можно при помощи перетягивания правого нижнего уголка ячейки с изменённым значением до самого низа столбца. Если все получилось, и данные изменились так, как нам было необходимо, мы можем приступить к расчёту общей стоимости наших товаров. Для выполнения этого действия нам необходимо найти произведение двух столбцов - «Количества» и «Цены». Поскольку в Excel заложены все математические формулы, расчёт можно предоставить «Строке формул», воспользовавшись уже знакомым нам значком «fx».
Казалось бы, всё готово и с нашей задачей VLOOKUP справилась, но не тут-то было. Дело в том, что в столбце «Цена» по-прежнему остаётся активной функция ВПР, свидетельством этого факта является отображение последней в строке формул. То есть обе наши таблицы остаются связанными одна с другой. Такой тандем может привести к тому, что при изменении данных в таблице с прайсом, изменится и информация, содержащаяся в нашем рабочем файле с перечнем товаров.
Подобной ситуации лучше избежать посредством разделения двух таблиц. Чтобы это сделать, нам необходимо выделить ячейки, находящиеся в диапазоне столбца «Цена», и щёлкнуть по нему правой кнопкой мыши. В открывшемся окошке выберите и активируйте опцию «Копировать». После этого, не снимая выделения с выбранной области ячеек, вновь нажмите правую кнопку мыши и выберите опцию «Специальная вставка».
Активация этой опции приведёт к открытию на вашем экране диалогового окна, в котором вам нужно будет поставить флажок рядом с категорией «Значение». Подтвердите совершённое вами действия, кликнув на кнопку «ОК».
Возвращаемся к нашей строке формул и проверяем наличие в столбце «Цена» активной функции VLOOKUP. Если на месте формулы вы видите просто числовые значения, значит, всё получилось, и функция ВПР отключена. То есть связь между двумя файлами Excel разорвана, а угроза незапланированного изменения или удаления прикреплённых из таблицы с прайсом данных нет. Теперь вы можете смело пользоваться табличным документом и не волноваться, что будет, если «Прайс-лист» окажется закрыт или перемещён в другое место.
При помощи функции ВПР вы сможете в считанные секунды сопоставить несколько различных значений, чтобы, к примеру, сравнить, как изменились цены на имеющийся товар. Чтобы сделать это, нужно прописать VLOOKUP в пустом столбце и сослать функцию на изменившиеся значения, которые находятся в другой таблице. Лучше всего, если столбец «Новая цена» будет расположен сразу за столбцом «Цена». Такое решение позволит вам сделать изменения прайса более наглядными для сравнения.
Ещё одним несомненным достоинством функции VLOOKUP является его способность работать с несколькими параметрами, присущими вашему товару. Чтобы найти товар по двум или более характеристикам, необходимо:
В заключение стоит сказать, что поддержание Excel такой функции, как ВПР, значительно упрощает работу с табличной информацией. Не бойтесь использовать VLOOKUP в работе с огромным количеством данных, ведь как бы они не были оформлены, принцип работы функции всегда один и тот же. Всё, что вам необходимо сделать - правильно определить её аргументы.
Что такое функция ВПР в Excel и как с ней работать? Для работы с таблицами в Excel существует ряд специальных функций. Одна из самых распространённых это ВПР, используемая для извлечения данных одной таблицы в другую. У нее есть английское наименование - VLOOKUP.
У нас есть две таблицы - товары и накладная. Необходимо, используя формулу, заполнить накладную. Используем для этого следующую формулу:
ВПР(искомое значение; таблица; номер_столбца; [интервальный просмотр])Важно! Аргументы функции отделяются друг от друга символом «;».
В нашем случае формула приобретает следующее значение:
ВПР(A3;F3:H13;3;0)Есть два варианта. Первый, используемый в основном опытными пользователями - это написание формулы в ручную. Им вы сможете воспользоваться, когда поймете, как формируется функция ВПР. Либо вариант с использованием всплывающих окон Excel.
С помощью функции можно быстро сопоставить значения, например, сравнивать изменения в ценах, для этого нужно разместить при помощи ВПР в соседней, новой колонке значение цен из другой таблицы. Таким образом, две колонки цен окажутся рядом для наглядного сравнения.
С несколькими условиями ВПР используется для нахождения товара по двум или более параметрам. Для этого:
Функцию также используют для работы с данными оформленными в виде выпадающего списка. В этом случае в качестве искомого значения выбирается ячейка с выпадающим списком.
Совет! Все варианты работы с функцией ВПР аналогичны, а сама работа производится автоматически, главное правильно определить ее аргументы.
Если у вас есть вопрос по работе с продуктами Microsoft, об этом. Вместе мы найдем ответ.
