Как найти функцию впр в excel 2016

Краткий справочник: функция ВПР

​Смотрите также​​ прайса (новую цену)​ или нажав комбинацию​ формулу:​ функцию​ все же​возвратила фамилию «Панченко».​Источник: https://www.ablebits.com/office-addins-blog/2014/07/17/excel-vlookup-tutorial-beginner/​Надеюсь, эти примеры пролили​миль в час.​(ПРАВДА) или​ на «man»:​Table​ВПР​редко используются для​ функцией, то есть​ищет значение в​Примечание:​ и подставить их​ горячих клавиш SHIFT+F3.​=VLOOKUP(C9,C3:D7,2,TRUE)​MATCH​

​VLOOKUP​ Если бы мы​Перевел: Антон Андронов​ немного света на​ Я верю, что​FALSE​

​=VLOOKUP(“*man”,$A$2:$C$11,1,FALSE)​(Таблица) на вкладке​, а когда дело​

​ поиска данных на​


​ символы верхнего и​ первом столбце заданного​Мы стараемся как​ в ячейку С2.​ В категории «Ссылки​=ВПР(C9;C3:D7;2;ИСТИНА)​(ПОИСКПОЗ) или​(ВПР) — это​ задали «008», то​Автор: Антон Андронов​ работу с функцией​ вот такая формула​(ЛОЖЬ).​

support.office.com

Функция ВПР в Excel – руководство для начинающих: синтаксис и примеры

​=ВПР(“*man”;$A$2:$C$11;1;ЛОЖЬ)​Insert​ дойдёт до аргумента​ том же листе.​​ нижнего регистра считаются​​ диапазона и возвращает​ можно оперативнее обеспечивать​Данные, представленные таким образом,​ и массивы» находим​Если процент не найден​

​COUNTIF​​ первая функция поиска,​​ формула также вернула​Используя функцию​ВПР​ не вызовет у​Для начала давайте выясним,​~​(Вставка), то при​table_array​ Чаще всего Вы​ одинаковыми.Итак, наша формула​ результат из другого​​ вас актуальными справочными​​ можно сопоставлять. Находить​

• ​ функцию ВПР и​
• ​ в первом столбце​
  • ​(СЧЁТЕСЛИ), чтобы убедиться,​ которую пользователи пробуют​ бы «Панченко».​
  • ​ВПР​в Excel, и​ Вас затруднений:​
  • ​ что в Microsoft​Находим имя, начинающееся​ выделении диапазона мышью,​
  • ​(таблица), переключитесь на​ будете искать и​
• ​ будет искать значение​ столбца в той​
• ​ материалами на вашем​ численную и процентную​

Функция ВПР в Excel – общее описание и синтаксис

​ жмем ОК. Данную​​ таблицы, функция​​ что искомое значение​ для таких задач.​В случае, когда четвертый​при работе в​ Вы больше не​=VLOOKUP(50,$A$2:$B$15,2,FALSE)​ Excel понимается под​ на «ad» и​​ Microsoft Excel автоматически​​ другую рабочую книгу​ извлекать соответствующие значения​40​ же строке.​ языке. Эта страница​ разницу.​

​ функцию можно вызвать​VLOOKUP​​ вообще существует в​​ Кто-то её сразу​ аргумент функции​ Excel, Вы можете​ смотрите на неё,​=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)​ точным и приближенным​

​ заканчивающееся на «son»:​ добавит в формулу​​ и выделите в​​ из другого листа.​​в ячейках от​​В самом привычном применении,​​ переведена автоматически, поэтому​​До сих пор мы​ перейдя по закладке​​(ВПР) найдёт ближайшее​​ первом столбце (смотри​​ откладывает в сторону,​​ВПР​ извлекать требуемую информацию​ как на чужака.​Обратите внимание, что наш​​ совпадением.​​=VLOOKUP(“ad*son”,$A$2:$C$11,1,FALSE)​​ названия столбцов (или​​ ней нужный диапазон​

​Чтобы, используя​​A2​​ функция​ ее текст может​ предлагали для анализа​ «Формулы» и выбрать​ наибольшее значение, которое​ пример 3).​

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

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

​ диапазон поиска (столбец​
​Если аргумент​

​=ВПР(“ad*son”;$A$2:$C$11;1;ЛОЖЬ)​​ название таблицы, если​​ поиска.​ВПР​до​ВПР​ содержать неточности и​ только одно условие​

• ​ из выпадающего списка​​ меньше аргумента​Другие функции, такие как​ заставить работать. Да,​ ЛОЖЬ, функция ищет​ Для этих целей​ кратко повторить ключевые​ A) содержит два​range_lookup​~​ Вы выделите всю​На снимке экрана, показанном​​, выполнить поиск на​​A15​

  ​ищет в базе​
​ грамматические ошибки. Для​

​ – наименование материала.​ «Ссылки и массивы».​lookup_value​INDEX​ она имеет ряд​​ точное соответствие. Например,​​ Excel предлагает несколько​​ моменты изученного нами​​ значения​

• ​(интервальный_просмотр) равен​​Находим первое имя​ таблицу).​ ниже, видно формулу,​​ другом листе Microsoft​​, потому что A​ данных заданный уникальный​ нас важно, чтобы​ На практике же​​Откроется окно с аргументами​​(искомое_значение). Искомое значение​(ИНДЕКС) и​ недостатков, но лишь​ на рисунке ниже​ функций, но​ материала, чтобы лучше​50​FALSE​ в списке, состоящее​Готовая формула будет выглядеть​ в которой для​​ Excel, Вы должны​​ – это первый​​ идентификатор и извлекает​​ эта статья была​​ нередко требуется сравнить​​ функции. В поле​ в нашем примере​MATCH​ поняв, как работает​​ формула вернет ошибку,​​ВПР​

  ​ закрепить его в​
​– в ячейках​

• ​(ЛОЖЬ), формула ищет​​ из 5 символов:​ примерно вот так:​ поиска задан диапазон​ в аргументе​ столбец диапазона A2:B15,​ из базы какую-то​ вам полезна. Просим​ несколько диапазонов с​​ «Искомое значение» -​​ —​(ПОИСКПОЗ), могут быть​​ эта функция, Вы​​ поскольку точного соответствия​среди них самая​​ памяти.​​A5​ точное совпадение, т.е.​=VLOOKUP(“?????”,$A$2:$C$11,1,FALSE)​

  ​=VLOOKUP("Product 1",Table46[[Product]:[Price]],2)​
​ в рабочей книге​

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

  

​ распространенная. В этом​​Функция​​и​​ точно такое же​​=ВПР(“?????”;$A$2:$C$11;1;ЛОЖЬ)​​=ВПР(“Product 1”;Table46[[Product]:[Price]];2)​​PriceList.xlsx​​(таблица) указать имя​​table_array​ информацию.​ секунд и сообщить,​​ значение по 2,​​ столбца из таблицы​. Его нет в​​ извлечения значений из​​ и другие варианты.​

• ​Если четвертый аргумент функции​​ уроке мы познакомимся​ВПР​
  • ​A6​ значение, что задано​​Чтобы функция​​А может даже так:​
  • ​на листе​​ листа с восклицательным​​(таблица):​Первая буква в названии​

  ​ помогла ли она​ 3-м и т.д.​ с количеством поступивших​ столбце с процентами,​​ таблицы, и они​​Ну что ж, давайте​ВПР​ с функцией​в Excel не​. Формула возвращает значение​ в аргументе​

Примеры с функцией ВПР

​ВПР​​=VLOOKUP(“Product 1”,Table46,2)​​Prices​ знаком, а затем​=VLOOKUP(40,A2:B15,2)​ функции​​ вам, с помощью​​ критериям.​ материалов. Это те​

Как, используя ВПР, выполнить поиск на другом листе Excel

​ поэтому формула возьмет​ более эффективны. Мы​​ разберёмся с инструкцией​​содержит значение ИСТИНА​ВПР​ может смотреть налево.​ из ячейки​lookup_value​с символами подстановки​=ВПР(“Product 1”;Table46;2)​

​.​​ диапазон ячеек. К​​=ВПР(40;A2:B15;2)​ВПР​ кнопок внизу страницы.​Таблица для примера:​​ значения, которые Excel​​ значение​ рассмотрим их позже​ по применению​ или опущен, то​, а также рассмотрим​ Она всегда ищет​​B5​​(искомое_значение). Если в​ работала правильно, в​​При использовании именованных диапазонов,​​Функция​

​ примеру, следующая формула​
​col_index_num​

​(VLOOKUP) означает​ Для удобства также​Предположим, нам нужно найти,​ должен найти во​75​ в рамках нашего​​VLOOKUP​​ крайний левый столбец​ ее возможности на​ значение в крайнем​. Почему? Потому что​

​ первом столбце диапазона​ качестве четвёртого аргумента​ ссылки будут вести​ВПР​ показывает, что диапазон​(номер_столбца) – номер​​В​​ приводим ссылку на​

​ по какой цене​
​ второй таблице.​

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

​ t​​ всегда нужно использовать​​ к тем же​будет работать даже,​A2:B15​ столбца в заданном​ертикальный (​ оригинал (на английском​ привезли гофрированный картон​Следующий аргумент – «Таблица».​

Поиск в другой рабочей книге с помощью ВПР

​B​​ насколько мощны и​​ несколько примеров. Если​ в порядке возрастания.​Функция​ заданного аргументом​ совпадения функция​able_array​

​FALSE​ ячейкам, не зависимо​​ когда Вы закроете​​находится на листе​​ диапазоне, из которого​​V​​ языке) .​​ от ОАО «Восток».​

​ Это наш прайс-лист.​
​.​

​ гибки они могут​ у Вас есть​ Если этого не​​ВПР​​table_array​ВПР​

1. ​(таблица) встречается два​(ЛОЖЬ). Если диапазон​ от того, куда​ рабочую книгу, в​ с именем​ будет возвращено значение,​ertical). По ней Вы​ВПР — это одна​ Нужно задать два​
2. ​ Ставим курсор в​​Функция​​ быть.​ какие-то свои подсказки​​ сделать, функция​​(вертикальный просмотр) ищет​(таблица).​использует первое найденное​ или более значений,​ поиска содержит более​

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

​Еще раз напомню, что​​ или приёмы по​​ВПР​ значение в крайнем​В функции​ значение, совпадающее с​ совпадающих с аргументом​ одного значения, подходящего​ВПР​ а в строке​.​

​ строке.Крайний левый столбец​ВПР​ и полезных функций​ по наименованию материала​ на лист с​

​(ВПР) может работать​
​ функция​

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

​ работе с этой​может вернуть неправильный​ левом столбце исследуемого​​ВПР​​ искомым.​lookup_value​ под условия поиска​в пределах рабочей​ формул появится полный​​=VLOOKUP(40,Sheet2!A2:B15,2)​​ в заданном диапазоне​

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

​ результат.​ диапазона, а затем​все значения используются​Когда Вы используете функцию​​(искомое_значение), то выбрано​​ с символами подстановки,​

​ книги.​
​ путь к файлу​

​=ВПР(40;Sheet2!A2:B15;2)​ – это​ГПР​ редком использовании ее​Дело осложняется тем, что​ с наименованием материалов​​ точное совпадение текстовой​​(ВПР) ищет значение​ в комментариях. И​Для тех, кто любит​ возвращает результат из​ без учета регистра,​ВПР​ будет первое из​ то будет возвращено​Как и во многих​ рабочей книги, как​Конечно же, имя листа​

​1​
​(HLOOKUP), которая осуществляет​

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

​ не обязательно вводить​, второй столбец –​ поиск значения в​​Если вам нужен только​​ поступает несколько наименований.​​ какие значения функция​​ примере мы найдём​ столбце таблицы. В​ свои секреты!​ а горизонтальные таблицы,​ на пересечении найденной​ и большие буквы​ совпадения, т.е. когда​ не найдены, функция​

​А теперь давайте разберём​ВПР​

​Если название рабочей книги​
​ вручную. Просто начните​

​ это​

​ верхней строке диапазона​
​ синтаксис функции ВПР,​

​Добавляем в таблицу крайний​ должна сопоставить.​ цену выбранного товара​ этом примере мы​Функция​ в Excel существует​​ строки и заданного​​ эквивалентны.​ аргумент​

Использование символов подстановки в формулах с ВПР

​ сообщит об ошибке​ чуть более сложный​​Вы можете использовать​​ или листа содержит​ вводить формулу, а​

• ​2​ –​ вот он:​
• ​ левый столбец (важно!),​Чтобы Excel ссылался непосредственно​

​ без использования точного​ найдём цену выбранного​​VLOOKUP​​ аналог​ столбца.​

• ​Если искомое значение меньше​range_lookup​#N/A​
• ​ пример, как осуществить​ следующие символы подстановки:​ пробелы, то его​ когда дело дойдёт​​, третий столбец –​​Г​VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])​ объединив «Поставщиков» и​​ на эти данные,​​ совпадения. Чтобы предотвратить​ товара. Важно получить​
• ​(ВПР) ищет значение​ВПР​Например, на рисунке ниже​ минимального значения в​(интервальный_просмотр) равен​(#Н/Д).Например, следующая формула​ поиск с помощью​Знак вопроса (?) –​ нужно заключить в​

Пример 1: Ищем текст, начинающийся или заканчивающийся определенными символами

​ до аргумента​ это​оризонтальный (​Чтобы скачать справочник, объясняющий,​ «Материалы».​ ссылку нужно зафиксировать.​ ошибки:​ правильную цену, поэтому​ в первом столбце​, но для горизонтального​ приведен список из​

​ первом столбце просматриваемого​
​TRUE​

​ сообщит об ошибке​ функции​ заменяет один любой​ апострофы:​table_array​3​H​ что имеете аргументы​Таким же образом объединяем​​ Выделяем значение поля​​Таблица должна быть отсортирована​ используем такие настройки:​ таблицы и возвращает​ поиска.​ 10 фамилий, каждой​

​ диапазона, функция​
​(ИСТИНА) или пропущен,​

​#N/A​ВПР​

​ символ.​​=VLOOKUP(40,'[Numbers.xlsx]Sheet2′!A2:B15,2)​(таблица), переключитесь на​

​и так далее.​
​orizontal).​

​ и их использование,​​ искомые критерии запроса:​ «Таблица» и нажимаем​ по первому столбцу​

​Название товара введено в​
​ другое значение из​

​В Microsoft Excel существует​​ фамилии соответствует свой​ВПР​ первое, что Вы​

​(#Н/Д), если в​
​по значению в​

​Звёздочка (*) – заменяет​​=ВПР(40;'[Numbers.xlsx]Sheet2′!A2:B15;2)​​ нужный лист и​ Теперь Вы можете​Функция​ нажмите ссылку ниже.​​Теперь ставим курсор в​​ F4. Появляется значок​ по возрастанию.​ ячейке B7.​ той же строки​ функция​ номер. Требуется по​сообщит об ошибке​

Пример 2: Сочетаем символы подстановки и ссылки на ячейки в формулах с ВПР

​ должны сделать, –​ диапазоне A2:A15 нет​ какой-то ячейке. Представьте,​ любую последовательность символов.​Если Вы планируете использовать​​ выделите мышью требуемый​​ прочитать всю формулу:​ВПР​ Справочник ВПР открывается​ нужном месте и​ $.​Функция​Таблица поиска с ценами​ таблицы.​ГПР​ заданному номеру извлечь​#N/A​ выполнить сортировку диапазона​ значения​ что в столбце​

​Использование символов подстановки в​ один диапазон поиска​

​ диапазон ячеек.​
​=VLOOKUP(40,A2:B15,2)​

​доступна в версиях​ в виде PDF-файла​ задаем аргументы для​В поле аргумента «Номер​COUNTIF​ имеет два столбца​Поскольку функция​(горизонтальный просмотр), которая​ фамилию.​(#Н/Д).​ по первому столбцу​4​ A находится список​

​ функциях​ в нескольких функциях​​Формула, показанная на скриншоте​​=ВПР(40;A2:B15;2)​ Excel 2013, Excel​ в Программе Adobe​ функции: . Excel​ столбца» ставим цифру​

​(СЧЁТЕСЛИ) будет проверять​​ и занимает диапазон​​VLOOKUP​ очень похожа на​С помощью функции​Если 3-й аргумент​ в порядке возрастания.​:​

Точное или приближенное совпадение в функции ВПР

​ лицензионных ключей, а​ВПР​ВПР​ ниже, ищет текст​​Формула ищет значение​​ 2010, Excel 2007,​​ Reader. Вы можете​​ находит нужную цену.​ «2». Здесь находятся​ наличие значения, чтобы​ B3:C5.​(ВПР) может находить​ВПР​ВПР​col_index_num​Это очень важно, поскольку​​=VLOOKUP(4,A2:B15,2,FALSE)​​ в столбце B​​может пригодиться во​​, то можете создать​

​ «Product 1» в​40​ Excel 2003, Excel​ распечатать копию карточку​Рассмотрим формулу детально:​

• ​ данные, которые нужно​​ предотвратить ошибку.​​Цена записана в столбец​​ точное совпадение или​​, разница лишь в​сделать это достаточно​(номер_столбца) меньше​ функция​=ВПР(4;A2:B15;2;ЛОЖЬ)​​ список имён, владеющих​​ многих случаях, например:​ именованный диапазон и​ столбце A (это​​в диапазоне​​ XP и Excel​ или сохраните его​Что ищем.​​ «подтянуть» в первую​​В ячейке C7 запишем​ 2 таблицы.​ ближайшее значение в​ том, что диапазон​ просто:​​1​​ВПР​Если аргумент​​ лицензией. Кроме этого,​​Когда Вы не помните​ вводить его имя​ 1-ый столбец диапазона​​A2:A15​​ 2000.​

  ​ там, где на​
​Где ищем.​

• ​ таблицу. «Интервальный просмотр»​​ формулу:​​Значение последнего аргумента функции​​ столбце, то она​​ просматривается не по​Из формулы видно, что​, функция​​возвращает следующее наибольшее​​range_lookup​ у Вас есть​ в точности текст,​ в формулу в​ A2:B9) на листе​и возвращает соответствующее​Функция​​ компьютере, чтобы использовать​​Какие данные берем.​

​ – ЛОЖЬ. Т.к.​​=IF(COUNTIF(B3:B5,B7),VLOOKUP(B7,B3:C5,2,TRUE),0)​​ установлено FALSE (ЛОЖЬ),​​ сможет:​​ вертикали, а по​ первым аргументом функции​ВПР​ значение после заданного,​(интервальный_просмотр) равен​ часть (несколько символов)​ который нужно найти.​ качестве аргумента​​Prices​​ значение из столбца​ВПР​

​ впоследствии.​Допустим, какие-то данные у​​ нам нужны точные,​​=ЕСЛИ(СЧЁТЕСЛИ(B3:B5;B7);ВПР(B7;B3:C5;2;ИСТИНА);0)​​ чтобы найти точное​​Найти цену выбранного товара.​ горизонтали.​ВПР​​сообщит об ошибке​​ а затем поиск​TRUE​

Пример 1: Поиск точного совпадения при помощи ВПР

​ какого-то лицензионного ключа​Когда Вы хотите найти​table_array​​.​​ B (поскольку B​​(VLOOKUP) имеет вот​​Сегодня мы начинаем серию​

​ нас сделаны в​ а не приблизительные​Если наименование товара в​ совпадение при поиске.​Преобразовать успеваемость ученика, выраженную​ГПР​​является ячейка С1,​​#VALUE!​ останавливается. Если Вы​(ИСТИНА), формула ищет​ в ячейке C1,​ какое-то слово, которое​

​(таблица).​
​=VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)​

​ – это второй​ такой синтаксис:​ статей, описывающих одну​ виде раскрывающегося списка.​​ значения.​​ первом столбце таблицы​​Формула в ячейке C7​​ в процентах, в​​ищет заданное значение​​ где мы указываем​(#ЗНАЧ!). Если же​​ пренебрежете правильной сортировкой,​​ приблизительное совпадение. Точнее,​ и Вы хотите​ является частью содержимого​​Чтобы создать именованный диапазон,​​=ВПР(“Product 1”;Prices!$A$2:$B$9;2;ЛОЖЬ)​ столбец в диапазоне​VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])​

Пример 2: Используем ВПР для поиска приблизительного совпадения

​ из самых полезных​​ В нашем примере​​Нажимаем ОК. А затем​ не найдено, функция​ имеет вид:​​ буквенную систему оценок.​​ в верхней строке​​ искомый номер. Вторым​​ он больше количества​ дело закончится тем,​ сначала функция​ найти имя владельца.​ ячейки. Знайте, что​ просто выделите ячейки​

​Пожалуйста, помните, что при​ A2:B15).​​ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])​​ функций Excel –​ – «Материалы». Необходимо​ «размножаем» функцию по​VLOOKUP​=VLOOKUP(B7,B3:C5,2,FALSE)​Функция​ исследуемого диапазона и​ выступает диапазон A1:B10,​ столбцов в диапазоне​ что Вы получите​​ВПР​​Это можно сделать, используя​

​ВПР​ и введите подходящее​ поиске текстового значения​

​Если значение аргумента​​Как видите, функция​​ВПР​
​ настроить функцию так,​​ всему столбцу: цепляем​​(ВПР) возвратит​

​=ВПР(B7;B3:C5;2;ЛОЖЬ)​VLOOKUP​ возвращает результат из​ который показывает, где​​table_array​​ очень странные результаты​ищет точное совпадение,​ вот такую формулу:​ищет по содержимому​​ название в поле​​ Вы обязаны заключить​

​col_index_num​ВПР​​(VLOOKUP). Эта функция,​​ чтобы при выборе​​ мышью правый нижний​​0​Если название товара в​(ВПР) имеет следующий​​ ячейки, которая находится​​ следует искать. И​(таблица), функция сообщит​​ или сообщение об​​ а если такое​=VLOOKUP(“*”&C1&”*”,$A$2:$B$12,2,FALSE)​ ячейки целиком, как​Имя​ его в кавычки​(номер_столбца) меньше​в Microsoft Excel​​ в то же​​ наименования появлялась цена.​ угол и тянем​.​ первом столбце таблицы​

​ синтаксис:​ на пересечении найденного​ последний аргумент –​​ об ошибке​​ ошибке​ не найдено, выбирает​=ВПР(“*”&C1&”*”;$A$2:$B$12;2;FALSE)​ при включённой опции​, слева от строки​ («»), как это​1​ имеет 4 параметра​ время, одна из​Сначала сделаем раскрывающийся список:​

ВПР в Excel – это нужно запомнить!

1. ​ вниз. Получаем необходимый​​Урок подготовлен для Вас​​ не найдено, то​VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)​ столбца и заданной​ это номер столбца,​#REF!​#N/A​​ приблизительное. Приблизительное совпадение​​Эта формула ищет значение​
2. ​Match entire cell content​​ формул.​​ обычно делается в​, то​ (или аргумента). Первые​ наиболее сложных и​Ставим курсор в ячейку​
3. ​ результат.​ командой сайта office-guru.ru​ функция​ВПР(искомое_значение;таблица;номер_столбца;интервальный_просмотр)​​ строки.​​ из которого необходимо​​(#ССЫЛКА!).​​(#Н/Д).​
4. ​ – это наибольшее​​ из ячейки C1​​(Ячейка целиком) в​​Теперь Вы можете записать​​ формулах Excel.​​ВПР​​ три – обязательные,​​ наименее понятных.​​ Е8, где и​Теперь найти стоимость материалов​Источник: http://blog.contextures.com/archives/2011/01/10/30-excel-functions-in-30-days-09-vlookup/​​VLOOKUP​​lo​Если представить вышеприведенный пример​​ возвратить результат. В​​Используйте абсолютные ссылки на​
5. ​Вот теперь можно использовать​ значение, не превышающее​​ в заданном диапазоне​​ стандартном поиске Excel.​ вот такую формулу​Для аргумента​сообщит об ошибке​ последний – по​В этом учебнике по​ будет этот список.​
6. ​ не составит труда:​Перевел: Антон Андронов​(ВПР) выдаст результат​okup_value​ в горизонтальной форме,​ нашем примере это​
7. ​ ячейки в аргументе​ одну из следующих​ заданного в аргументе​​ и возвращает соответствующее​​Когда в ячейке содержатся​​ для поиска цены​​table_array​#VALUE!​ необходимости.​

​ВПР​Заходим на вкладку «Данные».​​ количество * цену.​​Автор: Антон Андронов​#N/A​(искомое_значение): значение, которое​ то формула будет​ второй столбец. Нажав​​table_array​​ формул:​lookup_value​ значение из столбца​ дополнительные пробелы в​ товара​(таблица) желательно всегда​(#ЗНАЧ!). А если​lookup_value​

​я постараюсь изложить​ Меню «Проверка данных».​
​Функция ВПР связала две​
​Функция ВПР в Excel​

​(#Н/Д).​

office-guru.ru

Функция ВПР в Excel на простых примерах

​ требуется найти. Может​​ выглядеть следующим образом:​​Enter​(таблица), чтобы при​=VLOOKUP(69,$A$2:$B$15,2,TRUE)​(искомое_значение).​ B. Обратите внимание,​ начале или в​Product 1​​ использовать абсолютные ссылки​​ оно больше количества​(искомое_значение) – значение,​ основы максимально простым​Выбираем тип данных –​​ таблицы. Если поменяется​​ позволяет данные из​Обычно при использовании функции​ быть значением или​

​Как видите, все достаточно​​, мы получим нужный​​ копировании формулы сохранялся​или​Если аргумент​ что в первом​ конце содержимого. В​:​ (со знаком $).​ столбцов в диапазоне​ которое нужно искать.Это​

Пример 1

​ языком, чтобы сделать​ «Список». Источник –​ прайс, то и​ одной таблицы переставить​VLOOKUP​ ссылкой на ячейку.​ просто!​

​ результат:​​ правильный диапазон поиска.​​=VLOOKUP(69,$A$2:$B$15,2)​range_lookup​

​ аргументе мы используем​ такой ситуации Вы​​=VLOOKUP(“Product 1”,Products,2)​​ В таком случае​table_array​ может быть значение​ процесс обучения для​ диапазон с наименованиями​ изменится стоимость поступивших​ в соответствующие ячейки​(ВПР) ищут точное​table_array​На этом наш урок​Рассмотрим еще один пример.​ Попробуйте в качестве​​=ВПР(69;$A$2:$B$15;2;ИСТИНА)​​(интервальный_просмотр) равен​ символ амперсанда (&)​

Пример 2

​ можете долго ломать​=ВПР(“Product 1”;Products;2)​ диапазон поиска будет​(таблица), функция вернет​ (число, дата, текст)​ неискушённых пользователей максимально​ материалов.​

​ на склад материалов​ второй. Ее английское​ совпадение, но иногда​(таблица): таблица поиска.​ завершен. Сегодня мы​ На рисунке ниже​ альтернативы использовать именованные​​или​

​TRUE​ до и после​​ голову, пытаясь понять,​​Большинство имен диапазонов работают​ оставаться неизменным при​ ошибку​ или ссылка на​ понятным. Кроме этого,​Когда нажмем ОК –​ (сегодня поступивших). Чтобы​ наименование – VLOOKUP.​ приблизительное совпадение подходит​ Может быть ссылкой​

​ познакомились, наверное, с​ представлены те же​ диапазоны или таблицы​=ВПР(69;$A$2:$B$15;2)​(ИСТИНА) или не​ ссылки на ячейку,​ почему формула не​ для всей рабочей​ копировании формулы в​​#REF!​​ ячейку (содержащую искомое​ мы изучим несколько​ сформируется выпадающий список.​ этого избежать, воспользуйтесь​Очень удобная и часто​

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

​ другие ячейки.​​(#ССЫЛКА!).​​ значение), или значение,​ примеров с формулами​Теперь нужно сделать так,​ «Специальной вставкой».​ используемая. Т.к. сопоставить​ нужно преобразовать успеваемость​ именованным диапазоном, содержащим​​ Microsoft Excel –​​ и раньше, вот​Когда выполняете поиск приблизительного​

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

Горизонтальный ВПР в Excel

​ Excel, которые продемонстрируют​ чтобы при выборе​​Выделяем столбец со вставленными​​ вручную диапазоны с​ учеников, выраженную в​​ 2 столбца или​​функцией ВПР​ только номера идут​ совпадения, не забывайте,​ из животных скорость​ диапазона должны быть​​Как видно на рисунке​​ найти определенного клиента​ имя листа для​ВПР​(интервальный_просмотр) – определяет,​ функцией Excel. Например,​ наиболее распространённые варианты​ определенного материала в​ ценами.​

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

​ с пропусками.​ что первый столбец​

​ ближе всего к​ отсортированы по возрастанию,​ ниже, функция​ в базе данных,​ аргумента​​работала между двумя​​ что нужно искать:​ вот такая формула​ использования функции​ графе цена появлялась​Правая кнопка мыши –​ проблематично.​ систему оценок. К​col_index_num​

​ возможности на нескольких​​Если попробовать найти фамилию​ в исследуемом диапазоне​69​ то есть от​ВПР​

​ показанной ниже. Вы​

office-guru.ru

30 функций Excel за 30 дней: ВПР (VLOOKUP)

​table_array​​ рабочими книгами Excel,​точное совпадение, аргумент должен​​ будет искать значение​ВПР​​ соответствующая цифра. Ставим​​ «Копировать».​Допустим, на склад предприятия​ счастью Вам не​(номер_столбца): столбец, содержащий​ простых примерах. Надеюсь,​ для несуществующего номера​

​ должен быть отсортирован​милям в час.​ меньшего к большему.​возвращает значение «Jeremy​ не помните его​(таблица), даже если​​ нужно указать имя​​ быть равен​40​.​ курсор в ячейку​Не снимая выделения, правая​ по производству тары​ придётся записывать в​ значение, которое должно​ что этот урок​ (например, 007), то​ по возрастанию.​ И вот какой​ Иначе функция​ Hill», поскольку его​

​ фамилию, но знаете,​ формула и диапазон​ книги в квадратных​​FALSE​​:​Общее описание и синтаксис​ Е9 (где должна​ кнопка мыши –​ и упаковки поступили​ таблицу все возможные​ быть возвращено функцией.​ был для Вас​ формула вместо того,​И, наконец, помните о​

Функция 09: VLOOKUP (ВПР)

​ результат мне вернула​​ВПР​​ лицензионный ключ содержит​ что она начинается​ поиска находятся на​ скобках перед названием​(ЛОЖЬ);​=VLOOKUP(40,A2:B15,2)​

Как можно использовать функцию VLOOKUP (ВПР)?

​Примеры с функцией ВПР​​ будет появляться цена).​​ «Специальная вставка».​ материалы в определенном​ варианты значений в​ Задается номером столбца​ полезным. Всего Вам​

• ​ чтобы выдать ошибку,​
• ​ важности четвертого аргумента.​ функция​может вернуть ошибочный​

Синтаксис VLOOKUP (ВПР)

​ последовательность символов из​​ на «ack». Вот​​ разных листах книги.​ листа.​

​приблизительное совпадение, аргумент равен​
​=ВПР(40;A2:B15;2)​

• ​Как, используя ВПР, выполнить​​Открываем «Мастер функций» и​​Поставить галочку напротив «Значения».​ количестве.​ процентах, вместо этого​ внутри таблицы.​
• ​ доброго и успехов​​ благополучно вернет нам​ Используйте значения​ВПР​ результат.​ ячейки C1.​ такая формула отлично​
• ​ Если же они​​Например, ниже показана формула,​TRUE​Если искомое значение будет​ поиск на другом​ выбираем ВПР.​
• ​ ОК.​​Стоимость материалов – в​ Вы укажите только​range_lookup​ в изучении Excel.​ результат.​TRUE​:​Чтобы лучше понять важность​Заметьте, что аргумент​ справится с этой​ находятся в разных​

Ловушки VLOOKUP (ВПР)

​ которая ищет значение​​(ИСТИНА) или вовсе​​ меньше, чем наименьшее​ листе Excel​Первый аргумент – «Искомое​Формула в ячейках исчезнет.​ прайс-листе. Это отдельная​ самый низкий процент​(интервальный_просмотр): для поиска​PS:​Как такое может быть?​(ИСТИНА) или​Как видите, формула возвратила​ выбора​​table_array​​ задачей:​​ книгах, то перед​​40​ не указан.​ значение в первом​Поиск в другой рабочей​ значение» – ячейка​

​ Останутся только значения.​​ таблица.​​ для каждой буквы,​​ точного совпадения используйте​​Интересуетесь функцией ВПР?​Дело в том, что​FALSE​ результат​TRUE​(таблица) на скриншоте​=VLOOKUP(“ack*”,$A$2:$C$11,1,FALSE)​ именем диапазона нужно​на листе​Этот параметр не обязателен,​ столбце просматриваемого диапазона,​

Пример 1: Найти цену для выбранного товара

​ книге с помощью​ с выпадающим списком.​​​​Необходимо узнать стоимость материалов,​ и воспользуетесь функцией​ FALSE (ЛОЖЬ) или​ На нашем сайте​ функция​(ЛОЖЬ) обдуманно, и​Антилопа​(ИСТИНА) или​

• ​ сверху содержит имя​=ВПР(“ack*”;$A$2:$C$11;1;ЛОЖЬ)​
• ​ указать название рабочей​Sheet2​ но очень важен.​ функция​
• ​ ВПР​ Таблица – диапазон​
• ​Функция помогает сопоставить значения​ поступивших на склад.​VLOOKUP​ 0; для приблизительного​

​ ей посвящен целый​ВПР​

​ Вы избавитесь от​
​(Antelope), скорость которой​

​FALSE​ таблицы (Table7) вместо​Теперь, когда Вы уверены,​ книги, к примеру,​​в книге​​ Далее в этом​​ВПР​​Как использовать именованный диапазон​

Пример 2: Преобразовать проценты в буквенную систему оценок

​ с названиями материалов​​ в огромных таблицах.​​ Для этого нужно​(ВПР) для приблизительного​ поиска — TRUE​ раздел с множеством​имеет еще и​ многих головных болей.​61​(ЛОЖЬ), давайте разберём​ указания диапазона ячеек.​ что нашли правильное​ вот так:​Numbers.xlsx​ учебнике по​сообщит об ошибке​ или таблицу в​ и ценами. Столбец,​ Допустим, поменялся прайс.​​ подставит цену из​​ поиска. В этом​ (ИСТИНА) или 1.​ самых интересных уроков!​

• ​ четвертый аргумент, который​В следующих статьях нашего​
• ​миля в час,​ ещё несколько формул​ Так мы делали​
• ​ имя, можно использовать​=VLOOKUP(“Product 1”,PriceList.xlsx!Products,2)​:​ВПР​
• ​#N/A​ формулах с ВПР​ соответственно, 2. Функция​
• ​ Нам нужно сравнить​ второй таблицы в​ примере:​ В последнем случае​

​Автор: Антон Андронов​ позволяет задавать так​

​ учебника по функции​
​ хотя в списке​

​ с функцией​ в предыдущем примере.​ эту же формулу,​​=ВПР(“Product 1”;PriceList.xlsx!Products;2)​​=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)​я покажу Вам​(#Н/Д).​​Использование символов подстановки в​​ приобрела следующий вид:​ старые цены с​ первую. И посредством​​Процент записан в ячейке​​ столбец, в котором​Девятый день марафона​ называемый интервальный просмотр.​ВПР​​ есть также​​ВПР​​И, наконец, давайте рассмотрим​​ чтобы найти сумму,​

Пример 3: Найти точную цену по приближенному совпадению

​Так формула выглядит гораздо​​=ВПР(40;[Numbers.xlsx]Sheet2!A2:B15;2)​​ несколько примеров, объясняющих​table_array​ формулах с ВПР​ .​ новыми ценами.​ обычного умножения мы​ C9.​ функция выполняет поиск,​30 функций Excel за​

• ​ Он может иметь​в Excel мы​Гепард​
• ​и посмотрим на​​ поподробнее последний аргумент,​​ оплаченную этим клиентом.​ понятнее, согласны? Кроме​Вот простейший способ создать​

​ как правильно составлять​(таблица) – два​

​Точное или приближенное совпадение​
​Нажимаем ВВОД и наслаждаемся​

​В старом прайсе делаем​ найдем искомое.​Таблица поиска процентов имеет​​ должен быть отсортирован​​ 30 дней​​ два значения: ИСТИНА​​ будем изучать более​

​(Cheetah), который бежит​ результаты.​
​ который указывается для​
​ Для этого достаточно​

​ того, использование именованных​

office-guru.ru

Функция ВПР в Excel для чайников и не только

​ в Excel формулу​ формулы для поиска​ или более столбца​ в функции ВПР​ результатом.​ столбец «Новая цена».​

​Алгоритм действий:​ два столбца и​ в порядке возрастания.​будет посвящён изучению​ и ЛОЖЬ. Причем,​

Как пользоваться функцией ВПР в Excel

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

​ диапазонов – это​ с​ точного и приблизительного​

​ с данными.Запомните, функция​ВПР в Excel –​Изменяем материал – меняется​Выделяем первую ячейку и​Приведем первую таблицу в​ занимает диапазон C3:D7.​Функция​ функции​

​ если аргумент опущен,​

1. ​ как выполнение различных​70​ поиска точного совпадения,​ВПР​ функции​ хорошая альтернатива абсолютным​
2. ​ВПР​ совпадения.​ВПР​ это нужно запомнить!​ цена:​ выбираем функцию ВПР.​ нужный нам вид.​Таблица поиска отсортирована по​VLOOKUP​VLOOKUP​ то это равносильно​ вычислений при помощи​миль в час,​ четвёртый аргумент функции​–​ВПР​ ссылкам, поскольку именованный​, которая ссылается на​
3. ​Я надеюсь, функция​всегда ищет значение​Итак, что же такое​Скачать пример функции ВПР​ Задаем аргументы (см.​ Добавим столбцы «Цена»​ возрастанию по столбцу​(ВПР) может работать​(ВПР). Как можно​ истине.​
4. ​ВПР​ а 70 ближе​ВПР​range_lookup​на номер нужного​ диапазон не меняется​ другую рабочую книгу:​ВПР​ в первом столбце​ВПР​
5. ​ в Excel​ выше). Для нашего​ и «Стоимость/Сумма». Установим​ с процентами (1-й​ медленно, особенно когда​ догадаться по её​В случае, когда четвертый​
6. ​, извлечение значений из​ к 69, чем​должен иметь значение​(интервальный_просмотр). Как уже​ столбца. В нашем​ при копировании формулы​Откройте обе книги. Это​стала для Вас​ диапазона, заданного в​? Ну, во-первых, это​

​Так работает раскрывающийся список​ примера: . Это​ денежный формат для​ столбец таблицы).​ выполняет поиск точного​ названию, это одна​ аргумент имеет значение​

​ нескольких столбцов и​ 61, не так​FALSE​

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

1. ​ значит, что нужно​ новых ячеек.​
2. ​Буквенные обозначения оценок содержатся​ совпадения текстовой строки​
3. ​ из функций поиска,​ ИСТИНА, функция сначала​ другие. Я благодарю​
4. ​ ли? Почему так​(ЛОЖЬ).​

​ урока, этот аргумент​ C (3-й в​

​ Значит, Вы можете​

Быстрое сравнение двух таблиц с помощью ВПР

​ так проще создавать​ давайте рассмотрим несколько​table_array​ она делает? Она​ функцией ВПР. Все​ взять наименование материала​

1. ​Выделяем первую ячейку в​ во втором столбце​
2. ​ в несортированной таблице.​ которая работает с​ ищет точное соответствие,​ Вас за то,​ происходит? Потому что​Давайте вновь обратимся к​ очень важен. Вы​ диапазоне):​ быть уверены, что​ формулу. Вы же​ примеров использования​(таблица). В просматриваемом​ ищет заданное Вами​ происходит автоматически. В​ из диапазона А2:А15,​ столбце «Цена». В​

​ таблицы.​ По мере возможности,​ вертикальными списками.​ а если такого​

Функция ВПР в Excel с несколькими условиями

​ что читаете этот​ функция​ таблице из самого​ можете получить абсолютно​=VLOOKUP(“ack*”,$A$2:$C$11,3,FALSE)​ диапазон поиска в​ не хотите вводить​ВПР​ диапазоне могут быть​ значение и возвращает​ течение нескольких секунд.​

​ посмотреть его в​

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

​ разные результаты в​=ВПР(“ack*”;$A$2:$C$11;3;ЛОЖЬ)​ формуле всегда останется​

1. ​ имя рабочей книги​в формулах с​ различные данные, например,​ соответствующее значение из​
2. ​ Все работает быстро​ «Новом прайсе» в​
3. ​ D2. Вызываем «Мастер​ TRUE (ИСТИНА), чтобы​ в таблице, отсортированной​ справятся с извлечением​ которое меньше чем​

​ встретить Вас снова​

1. ​при поиске приблизительного​
2. ​ выясним, какое животное​
3. ​ одной и той​

Функция ВПР и выпадающий список

​Вот ещё несколько примеров​ корректным.​ вручную? Вдобавок, это​ реальными данными.​ текст, даты, числа,​ другого столбца. Говоря​ и качественно. Нужно​ столбце А. Затем​

​ функций» с помощью​

1. ​ выполнялся приблизительный поиск​ по первой колонке​ данных из таблиц​
2. ​ заданное. Именно поэтому​ на следующей неделе!​
3. ​ совпадения возвращает наибольшее​ может передвигаться со​ же формуле при​ с символами подстановки:​
4. ​Если преобразовать диапазон ячеек​ защитит Вас от​

​На практике формулы с​ логические значения. Регистр​ техническим языком,​ только разобраться с​ взять данные из​ кнопки «fx» (в​ значений.​ по возрастанию. Вы​

1. ​ Excel (смотрите раздел​ функция​
2. ​Урок подготовлен для Вас​ значение, не превышающее​ скоростью​ его значении​~​ в полноценную таблицу​ случайных опечаток.​ функцией​ символов не учитывается​
3. ​ВПР​ этой функцией.​

​ второго столбца нового​ начале строки формул)​

​В ячейке D9 запишем​ можете сначала применить​

​ Ловушки ВПР), но​ВПР​ командой сайта office-guru.ru​ искомое.​50​TRUE​Находим имя, заканчивающееся​ Excel, воспользовавшись командой​Начните вводить функцию​

exceltable.com

​ВПР​

Иллюстрация: Meery Mary для Skillbox Media

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

ВПР (Vlookup, или вертикальный просмотр) — поисковая функция в Excel. Она находит значения в одной таблице и переносит их в другую. Функция ВПР нужна, чтобы работать с большими объёмами данных — не нужно самостоятельно сопоставлять и переносить сотни наименований, функция делает это автоматически.

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

Представьте, что вы продаёте автомобили. У вас есть каталог с характеристиками авто и их стоимостью. Также у вас есть таблица с данными клиентов, которые забронировали эти автомобили.

Вам нужно сообщить покупателям, сколько стоят их авто. Перед тем как обзванивать клиентов, нужно объединить данные: добавить во вторую таблицу колонку с ценами из первой.

Просто скопировать и вставить эту колонку не получится. Искать каждое авто вручную и переносить цены — долго.

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

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

Важно!

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

---

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

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

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

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

Дальше открываем окно для построения функции ВПР. Есть два способа сделать это. Первый — перейти во вкладку «Формулы» и нажать на «Вставить функцию».

Второй способ — нажать на «fx» в строке ссылок на любой вкладке таблицы.

Справа появляется окно «Построитель формул». В нём через поисковик находим функцию ВПР и нажимаем «Вставить функцию».

Появляется окно для ввода аргументов функции. Как их заполнять — разбираемся ниже.

---

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

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

Порядок действий, чтобы указать значение, выглядит так:

1. Ставим курсор в окно «Искомое значение» в построителе формул.
2. Выбираем первое значение столбца «Марка, модель» в таблице с клиентами. Это ячейка A2.

Выбранное значение переносится в построитель формул и одновременно появляется в формуле строки ссылок: fx=ВПР(A2).

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

В нашем случае нужно перенести цены автомобилей. Поэтому в диапазон обязательно нужно включить столбцы «Марка, модель» (искомое значение) и «Цена, руб.» (переносимое значение).

Важно!

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

Порядок действий для указания диапазона:

1. Ставим курсор в окно «Таблица» в построителе формул.
2. Переходим в таблицу «Каталог авто».
3. Выбираем диапазон, в который попадают столбцы «Марка, модель» и «Цена, руб.». Это A2:E19.
4. Закрепляем выбранный диапазон. На Windows для этого выбираем значение диапазона в строке ссылок и нажимаем клавишу F4, на macOS — выбираем значение диапазона в строке ссылок и нажимаем клавиши Cmd + T. Закрепить диапазон нужно, чтобы можно было протянуть функцию вниз и она сработала корректно во всех остальных строках.

Выбранный диапазон переносится в построитель формул и одновременно появляется в формуле строки ссылок: fx=ВПР(A2;’каталог авто’!$A$2:$E$19).

Номер столбца — порядковый номер столбца в первой таблице, в котором находится переносимое значение. Считается по принципу: номер 1 — самый левый столбец, 2 — столбец правее и так далее.

В нашем случае значение для переноса — цена — находится в пятом столбце слева.

Чтобы задать номер, установите курсор в окно «Номер столбца» в построителе формул и введите значение. В нашем примере это 5. Это значение появится в формуле в строке ссылок: fx=ВПР(A2;’каталог авто’!$A$2:$E$19;5).

Интервальный просмотр — условное значение, которое настроит, насколько точно сработает функция:

• Если нужно точное совпадение при поиске ВПР, вводим 0.
• Если нужно приближённое соответствие при поиске ВПР, вводим 1.

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

Ставим курсор в окно «Интервальный просмотр» в построителе формул и вводим значение: 0. Одновременно это значение появляется в формуле строки ссылок: fx=ВПР(A2;’каталог авто’!$A$2:$E$19;5;0). Это окончательный вид функции.

---

Чтобы получить результат функции, нажимаем кнопку «Готово» в построителе формул. В выбранной ячейке появляется нужное значение. В нашем случае — цена первой модели авто.

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

Получилась таблица с ценами — можно звонить клиентам и сообщать о стоимости авто. Данные перенесены без ошибок, а потратили мы на это несколько минут.

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

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

И по традиции есть таблица с клиентами, которые эти модели забронировали.

Если идти по классическому пути ВПР, получится такая функция: fx=ВПР(A29;’каталог авто’!$A$29:$E$35;5;0). В таком виде ВПР найдёт первую совпавшую модель и подтянет её стоимость. Параметр цвета не будет учтён.

Соответственно, цены у всех Nissan Juke будут 1 850 000 рублей, у всех Subaru Forester — 3 190 000 рублей, у всех Toyota C-HR — 2 365 000 рублей.

Поэтому в этом варианте нужно искать стоимость авто сразу по двум критериям — модель и цвет. Для этого нужно изменить формулу вручную. В строке ссылок ставим курсор сразу после искомого значения.

Дописываем в формулу фразу ЕСЛИ(‘каталог авто’!$B$29:$B$35=B29, где:

• ‘каталог авто’!$B$29:$B$35 — закреплённый диапазон цвета автомобилей в таблице, откуда нужно перенести данные. Это весь столбец с ценами.
• B29 — искомое значение цвета автомобиля в таблице, куда мы переносим данные. Это первая ячейка в столбце с цветом — дополнительным параметром для поиска.

Итоговая функция такая: fx=ВПР(A29;ЕСЛИ(‘каталог авто’!$B$29:$B$35=B29;’каталог авто’!$A$29:$E$35);5;0). Теперь значения цен переносятся верно.

Как использовать ВПР в «Google Таблицах»? В них тоже есть функция Vlookup, но нет окна построителя формул. Поэтому придётся прописывать её вручную. Перечислите через точку с запятой все аргументы и не забудьте зафиксировать диапазон. Для фиксации поставьте перед каждым символом значок доллара. В готовой формуле это будет выглядеть так: =ВПР(A2;’Лист1′!$A$2:$C$5;3;0).

Научитесь: Excel + Google Таблицы с нуля до PRO
Узнать больше

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

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

Допустим, есть таблица с количеством проданного товара и таблица с ценами на эти товары

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

Как создать функцию ВПР в Excel

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

Записываем формулу в столбик цены (С2). Это можно сделать двумя способами:

1. Выделить ячейку и вписать функцию.

2. Выделить ячейку → нажать на Fx (Shift +F3) → выбрать категорию «Ссылки и массивы» → выбрать функцию ВПР → нажать «ОК».

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

Синтаксис функции ВПР выглядит так:

=ВПР(искомое значение;таблица;номер столбца;интервальный просмотр)

В нашем случае получится такая формула:

=ВПР(A2;$G$2:$H$11;2;0)

Аргументы функции ВПР

Сейчас разберемся что и куда писать.

Со знаком равенства «=» и названием «ВПР» все понятно. Поговорим об аргументах. Они записываются в скобках через точку с запятой или заполняются в ячейки в окне функции. Формула ВПР имеет 4 аргумента: искомое значение, таблица, номер столбца и интервальный просмотр.

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

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

=ВПР(A2;

Таблица – это диапазон ячеек, из которых мы будем «подтягивать» данные для искомого значения. В этом аргументе используем абсолютные ссылки. Это значит, что в формуле таблица будет выглядеть как «$G$2:$H$11» вместо «G2:H11». Знаки «$» можно поставить вручную, а можно выделить «G2:H11» внутри формулы и нажать F4. Если этого не сделать, таблица не зафиксируется в формуле и изменится при копировании. 

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

=ВПР(A2;$G$2:$H$11

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

1. Формула сканирует таблицу по вертикали.

2. Находит в самом левом столбце совпадение с искомым значением.

3. Смотрит в столбец напротив, очередность которого мы указываем в этом аргументе.

4. Передает данные в ячейку с формулой.

В нашем случае – это столбец с ценой продуктов в прайсе. Формула ищет искомое значение ячейки A2 (Кофе) в первом столбце прайса и «подтягивает» данные из второго столбца (потому что мы указали цифру 2) в ячейку с формулой.

=ВПР(A2;$G$2:$H$11;2

Интервальный просмотр – это параметр, который может принимать 2 значения: «истина» или «ложь». Истина обозначается в формуле цифрой 1 и означает приблизительное совпадение с искомым значением. Ложь обозначается цифрой 0 и подразумевает точное совпадение. Приблизительный поиск и критерий «истина» обычно используют при работе с числами, а точный и «ложь» – в работе с наименованиями. 

В нашем случае искомое значение – это текстовое наименование. Поэтому используем точный поиск – ставим цифру 0 и закрываем скобку.

=ВПР(A2;$G$2:$H$11;2;0)

Автозаполнение

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

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

• В нашем случае искомое значение – A2. Это относительная ссылка на ячейку, потому что в ней нет знаков «$». Благодаря этому ссылка на искомое значение меняется относительно каждой строчки, когда происходит автозаполнение в другие ячейки: A2 → A3 → … → A11. Это удобно, когда необходимо повторить формулу на несколько строк, ведь ее не приходится писать заново.

• Таблица зафиксирована абсолютной ссылкой «$G$2:$H$11». Это означает, что ссылки на ячейки не изменятся во время автозаполнения. Таким образом, расчет каждый раз будет корректным и опираться на таблицу. 

ВПР и приблизительный интервальный просмотр

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

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

Товары такие же, как и в первом примере, но задача изменилась: нужно привязать формулу не к наименованию, а к количеству

Решение. Заполняем формулу ВПР в ячейке «Партия», как было показано в предыдущем примере.

Разница в том, что теперь искомое значение – число, а интервальный просмотр – истина, что означает приблизительный поиск. Получается вот такой результат:

Что произошло? Аргумент «интервальный просмотр» имеет значение 1. Это значит, что формула ВПР ищет в таблице ближайшее меньшее искомое значение. 

В нашем случае количество товара «Кофе» – 380. ВПР берет это число в виде искомого значения, после чего ищет ближайшее меньшее в соседней таблице – число 300. В конце функция «подтягивает» данные из столбца напротив («Крупная»). Если количество товара «Кофе» = 340 – это «Крупная партия». Важно, чтобы крайний левый столбец таблицы, которая указана в формуле, был отсортирован по возрастанию. В противном случае ВПР не сработает. 

Значения и данные во второй таблице отсортированы по убыванию – ВПР не работает

Итоги

• Функция ВПР означает вертикальный просмотр. Она просматривает крайний левый столбец таблицы сверху вниз. 

• Синтаксис функции: =ВПР(искомое значение;таблица;номер столбца;интервальный просмотр).

• Функцию можно вписать вручную или в специальном окне (Shift + F3).

• Искомое значение – относительная ссылка, а таблица – абсолютная. 

• Интервальный просмотр может искать точное или приблизительное совпадение с искомым значением. 

• Приблизительный поиск и критерий «истина» обычно используют при работе с числами, а точный и «ложь» – в работе с наименованиями.

• Порядок работы с функцией подходит для Гугл-таблиц.