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

Функция ВПР в Excel позволяет данные из одной таблицы переставить в соответствующие ячейки второй. Ее английское наименование – VLOOKUP.

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

Допустим, на склад предприятия по производству тары и упаковки поступили материалы в определенном количестве.

Стоимость материалов – в прайс-листе. Это отдельная таблица.

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

1. Приведем первую таблицу в нужный нам вид. Добавим столбцы «Цена» и «Стоимость/Сумма». Установим денежный формат для новых ячеек.
2. Выделяем первую ячейку в столбце «Цена». В нашем примере – D2. Вызываем «Мастер функций» с помощью кнопки «fx» (в начале строки формул) или нажав комбинацию горячих клавиш SHIFT+F3. В категории «Ссылки и массивы» находим функцию ВПР и жмем ОК. Данную функцию можно вызвать перейдя по закладке «Формулы» и выбрать из выпадающего списка «Ссылки и массивы».
3. Откроется окно с аргументами функции. В поле «Искомое значение» - диапазон данных первого столбца из таблицы с количеством поступивших материалов. Это те значения, которые Excel должен найти во второй таблице.
4. Следующий аргумент – «Таблица». Это наш прайс-лист. Ставим курсор в поле аргумента. Переходим на лист с ценами. Выделяем диапазон с наименованием материалов и ценами. Показываем, какие значения функция должна сопоставить.
5. Чтобы Excel ссылался непосредственно на эти данные, ссылку нужно зафиксировать. Выделяем значение поля «Таблица» и нажимаем F4. Появляется значок $.
6. В поле аргумента «Номер столбца» ставим цифру «2». Здесь находятся данные, которые нужно «подтянуть» в первую таблицу. «Интервальный просмотр» - ЛОЖЬ. Т.к. нам нужны точные, а не приблизительные значения.

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

Теперь найти стоимость материалов не составит труда: количество * цену.

Функция ВПР связала две таблицы. Если поменяется прайс, то и изменится стоимость поступивших на склад материалов (сегодня поступивших). Чтобы этого избежать, воспользуйтесь «Специальной вставкой».

1. Выделяем столбец со вставленными ценами.
2. Правая кнопка мыши – «Копировать».
3. Не снимая выделения, правая кнопка мыши – «Специальная вставка».
4. Поставить галочку напротив «Значения». ОК.

Формула в ячейках исчезнет. Останутся только значения.

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

1. В старом прайсе делаем столбец «Новая цена».
2. Выделяем первую ячейку и выбираем функцию ВПР. Задаем аргументы (см. выше). Для нашего примера. Это значит, что нужно взять наименование материала из диапазона А2:А15, посмотреть его в «Новом прайсе» в столбце А. Затем взять данные из второго столбца нового прайса (новую цену) и подставить их в ячейку С2.

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

До сих пор мы предлагали для анализа только одно условие – наименование материала. На практике же нередко требуется сравнить несколько диапазонов с данными и выбрать значение по 2, 3-м и т.д. критериям.

Таблица для примера:

Предположим, нам нужно найти, по какой цене привезли гофрированный картон от ОАО «Восток». Нужно задать два условия для поиска по наименованию материала и по поставщику.

Дело осложняется тем, что от одного поставщика поступает несколько наименований.

1. Добавляем в таблицу крайний левый столбец (важно!), объединив «Поставщиков» и «Материалы».
2. Таким же образом объединяем искомые критерии:
3. Теперь ставим курсор в нужном месте и задаем аргументы для функции. Excel находит нужную цену.

Рассмотрим формулу детально:

Допустим, какие-то данные у нас сделаны в виде раскрывающегося списка. В нашем примере – «Материалы». Необходимо настроить функцию так, чтобы при выборе наименования появлялась цена.

Сначала сделаем раскрывающийся список:

1. Ставим курсор в ячейку Е8, где и будет этот список.
2. Заходим на вкладку «Данные». Меню «Проверка данных».
3. Выбираем тип данных – «Список». Источник – диапазон с наименованиями материалов.
4. Когда нажмем ОК – сформируется выпадающий список.

Теперь нужно сделать так, чтобы при выборе определенного материала в графе цена появлялась соответствующая цифра. Ставим курсор в ячейку Е9 (где должна будет появляться цена).

1. Открываем «Мастер функций» и выбираем ВПР.
2. Первый аргумент – «Искомое значение» - ячейка с выпадающим списком. Таблица – диапазон с названиями материалов и ценами. Столбец, соответственно, 2. Функция приобрела следующий вид.
3. Нажимаем ВВОД и наслаждаемся результатом.

Изменяем материал – меняется цена:

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

Microsoft Excel

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

Сама функция имеет следующую структуру =ВПР(искомое_значение; таблица; номер_столбца; интервал просмотр). Рассмотрим, за что отвечает каждая строка:

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

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

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

интервал просмотр - это логическая единица, если вы укажите в качестве "ложь", то будет возвращаться значения совпадающие на 100%. Если вы укажите значение "истина", то будут вставляться значения приближенные к искомое_значение

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

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

модератор выбрал этот ответ лучшим

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

в избранное ссылка отблагодарить

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

в избранное ссылка отблагодарить

ВПР в excel

Функция ВПР позволяет быстро заполнить таблицы, имея в документе искомые данные. Рассмотрим этот процесс на примере Microsoft Excel 2007.

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

Работать ВПР будет при наличии двух условий:

1. Если в сопоставляемых элементах есть одинаковые элементы.
2. Если сопоставляемые данные в первой таблице будут отсортированы по возрастанию.

Примером будет являться изображение чуть ниже.

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

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

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

После нажатия ОК откроется еще одно окно, которое будет называться Аргументы функции. В графу Искомое значение нужно добавить фамилию, по которой будет производиться сопоставление. Для этого нужно выделить ту фамилию, рядом с которой был поставлен знак равенства. В данном случае это фамилия Иванов (А11).

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

В графе Номер столбца нужно поставить цифру 2. Это будет означать, что требуется перенести в новую таблицу сумму по фамилии, которая располагается во втором столбце выделенного диапазона первой таблицы. Соответственно, в зависимости от расположения таблицы, это число может быть иным. Кратко говоря, в данном случае «2» означает номер столбца в таблице (столбец В). Если данные располагаются в столбце С, то число будет не 2, а 3, D — 4 и так далее.

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

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

Теперь эту формулу нужно скопировать и вставлять в остальные пустые ячейки. Это позволит быстро заполнить оставшиеся пустые области. Обратите внимание, каждый раз, вставляя формулу, нужно будет менять Искомое значение. То есть, если мы скопировали формулу =ВПР(A11;$A$4:$B$6;2), где А11 — фамилия Иванов, то чтобы заполнить ячейку с Сидоровым, нам нужно А11 заменить на А12. Результатом будет =ВПР(A12;$A$4:$B$6;2). Адреса ячеек нужно брать из второй таблицы, а не первой.

Функция ВПР в Excel

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

Задача1. Дано. В одном файле хранится список ФИО сотрудников и транспортных средств (Таблица1). В Таблице2 для некоторых автомобилей заполнены номера накладных. Причем таблицы не совпадают по количеству строк. Цель. Для каждой строки Таблицы2 заполнить ФИО сотрудников. Для этого как раз пригодится функция ВПР.

Таблица 1 Таблица2

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

Функция ВПР ищет значение в левом столбце Таблицы1 и возвращает (записывает) значение ячейки, находящейся в столбце Таблицы2 под определенным номером, той же строки. Во как :) Но все на самом деле значительно проще.

• Искомое_значение — то самое значение, которое мы ищем в левом столбце Таблицы1
• Таблица — все столбцы Таблицы1
• Номер_столбца — номер столбца в Таблице1, из которого возвращаем значения
• [интервальный_просмотр] — может принимать только два значения Ложь или Истина: Ложь – ищет точное совпадение, Истина – приблизительное. В 95% случаев требуется искать точное значение, т.е. выбирать ЛОЖЬ.

Итак напишем функцию для нашей задачи:

E:E это как раз диапазон значений по которым Excel будет сравнивать условия с Таблицей 1. A:B — это как раз Таблица1 (обязательно, чтобы первым столбцом был столбец для поиска условий). Число 2 это тот, по счету, столбец в Таблице 1, который мы будем переносить в ячейку F2 и далее. Ложь — смотрите выше.

Я тысячу раз слышал как люди говорят: «Давайте заВПРим это» или «ну тут можно ВПРом сделать», и это отлично, значит люди экономят время, зная о простых и действенных методах! Не отставайте, если вы еще не подозреваете о чем идет речь!

Не забудьте растащить функцию для конца диапазона. Не знаете как это сделать быстро? Читайте .

А так же можно сделать ту же функцию с удобными диапазонами (на картинке ниже). Нравится? Читайте.

Если для одной ячейки в Таблице2 есть несколько значений — будет выдаваться первое значение в Таблице1.

Если совпадающих данных нет, будет возвращаться ошибка «не найдено» (Н/Д#), чтобы ее не отображать, используйте формулы =ЕСЛИОШИБКА()

Смотрите так же: «Как посчитать сумму для нескольких значений из таблицы СУММЕСЛИ «

Пишите комментарии, буду рад помочь!

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

Сегодня мы начинаем серию статей, описывающих одну из самых полезных функций Excel – ВПР (VLOOKUP). Эта функция, в то же время, одна из наиболее сложных и наименее понятных.

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

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

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

Первая буква в названии функции ВПР (VLOOKUP) означает В ертикальный (V ertical). По ней Вы можете отличить ВПР от ГПР (HLOOKUP), которая осуществляет поиск значения в верхней строке диапазона – Г оризонтальный (H orizontal).

Функция ВПР доступна в версиях Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP и Excel 2000.

Функция ВПР (VLOOKUP) имеет вот такой синтаксис:

Как видите, функция ВПР в Microsoft Excel имеет 4 параметра (или аргумента). Первые три – обязательные, последний – по необходимости.

• lookup_value (искомое_значение) – значение, которое нужно искать.

Это может быть значение (число, дата, текст) или ссылка на ячейку (содержащую искомое значение), или значение, возвращаемое какой-либо другой функцией Excel. Например, вот такая формула будет искать значение 40 :

Если искомое значение будет меньше, чем наименьшее значение в первом столбце просматриваемого диапазона, функция ВПР сообщит об ошибке #N/A (#Н/Д).

• table_array (таблица) – два или более столбца с данными.

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

Итак, наша формула будет искать значение 40 в ячейках от A2 до A15. потому что A – это первый столбец диапазона A2:B15, заданного в аргументе table_array (таблица):

• col_index_num (номер_столбца) – номер столбца в заданном диапазоне, из которого будет возвращено значение, находящееся в найденной строке.

  Крайний левый столбец в заданном диапазоне – это 1. второй столбец – это 2. третий столбец – это 3 и так далее. Теперь Вы можете прочитать всю формулу:

  Формула ищет значение 40 в диапазоне A2:A15 и возвращает соответствующее значение из столбца B (поскольку B – это второй столбец в диапазоне A2:B15).

  

  Если значение аргумента col_index_num (номер_столбца) меньше 1. то ВПР сообщит об ошибке #VALUE! (#ЗНАЧ!). А если оно больше количества столбцов в диапазоне table_array (таблица), функция вернет ошибку #REF! (#ССЫЛКА!).

  • range_lookup (интервальный_просмотр) – определяет, что нужно искать:
    • точное совпадение, аргумент должен быть равен FALSE (ЛОЖЬ);
    • приблизительное совпадение, аргумент равен TRUE (ИСТИНА) или вовсе не указан.

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

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

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

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

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

    Чтобы, используя ВПР. выполнить поиск на другом листе Microsoft Excel, Вы должны в аргументе table_array (таблица) указать имя листа с восклицательным знаком, а затем диапазон ячеек. К примеру, следующая формула показывает, что диапазон A2:B15 находится на листе с именем Sheet2 .

    Конечно же, имя листа не обязательно вводить вручную. Просто начните вводить формулу, а когда дело дойдёт до аргумента table_array (таблица), переключитесь на нужный лист и выделите мышью требуемый диапазон ячеек.

    Формула, показанная на скриншоте ниже, ищет текст "Product 1" в столбце A (это 1-ый столбец диапазона A2:B9) на листе Prices .

    =VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)
    =ВПР("Product 1";Prices!$A$2:$B$9;2;ЛОЖЬ)

    

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

    Для аргумента table_array (таблица) желательно всегда использовать абсолютные ссылки (со знаком $). В таком случае диапазон поиска будет оставаться неизменным при копировании формулы в другие ячейки.

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

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

    Например, ниже показана формула, которая ищет значение 40 на листе Sheet2 в книге Numbers.xlsx :

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

    1. Откройте обе книги. Это не обязательно, но так проще создавать формулу. Вы же не хотите вводить имя рабочей книги вручную? Вдобавок, это защитит Вас от случайных опечаток.
    2. Начните вводить функцию ВПР. а когда дело дойдёт до аргумента table_array (таблица), переключитесь на другую рабочую книгу и выделите в ней нужный диапазон поиска.

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

    

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

    

    Если название рабочей книги или листа содержит пробелы, то его нужно заключить в апострофы:

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

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

    Чтобы создать именованный диапазон, просто выделите ячейки и введите подходящее название в поле Имя. слева от строки формул.

    

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

    =VLOOKUP("Product 1",Products,2)
    =ВПР("Product 1";Products;2)

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

    =VLOOKUP("Product 1",PriceList.xlsx!Products,2)
    =ВПР("Product 1";PriceList.xlsx!Products;2)

    Так формула выглядит гораздо понятнее, согласны? Кроме того, использование именованных диапазонов – это хорошая альтернатива абсолютным ссылкам, поскольку именованный диапазон не меняется при копировании формулы в другие ячейки. Значит, Вы можете быть уверены, что диапазон поиска в формуле всегда останется корректным.

    Если преобразовать диапазон ячеек в полноценную таблицу Excel, воспользовавшись командой Table (Таблица) на вкладке Insert (Вставка), то при выделении диапазона мышью, Microsoft Excel автоматически добавит в формулу названия столбцов (или название таблицы, если Вы выделите всю таблицу).

    

    Готовая формула будет выглядеть примерно вот так:

    =VLOOKUP("Product 1",Table46[[Product]:[Price]],2)
    =ВПР("Product 1";Table46[[Product]:[Price]];2)

    А может даже так:

    =VLOOKUP("Product 1",Table46,2)
    =ВПР("Product 1";Table46;2)

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

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

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

    • Знак вопроса (?) – заменяет один любой символ.
    • Звёздочка (*) – заменяет любую последовательность символов.

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

    • Когда Вы не помните в точности текст, который нужно найти.
    • Когда Вы хотите найти какое-то слово, которое является частью содержимого ячейки. Знайте, что ВПР ищет по содержимому ячейки целиком, как при включённой опции Match entire cell content (Ячейка целиком) в стандартном поиске Excel.
    • Когда в ячейке содержатся дополнительные пробелы в начале или в конце содержимого. В такой ситуации Вы можете долго ломать голову, пытаясь понять, почему формула не работает.
    Пример 1: Ищем текст, начинающийся или заканчивающийся определенными символами

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

    

    Теперь, когда Вы уверены, что нашли правильное имя, можно использовать эту же формулу, чтобы найти сумму, оплаченную этим клиентом. Для этого достаточно изменить третий аргумент функции ВПР на номер нужного столбца. В нашем случае это столбец C (3-й в диапазоне):

    Вот ещё несколько примеров с символами подстановки:

    Находим имя, заканчивающееся на "man":

    Находим имя, начинающееся на "ad" и заканчивающееся на "son":

    Находим первое имя в списке, состоящее из 5 символов:

    =VLOOKUP(". ",$A$2:$C$11,1,FALSE)
    =ВПР(". ";$A$2:$C$11;1;ЛОЖЬ)

    Чтобы функция ВПР с символами подстановки работала правильно, в качестве четвёртого аргумента всегда нужно использовать FALSE (ЛОЖЬ). Если диапазон поиска содержит более одного значения, подходящего под условия поиска с символами подстановки, то будет возвращено первое найденное значение.

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

    А теперь давайте разберём чуть более сложный пример, как осуществить поиск с помощью функции ВПР по значению в какой-то ячейке. Представьте, что в столбце A находится список лицензионных ключей, а в столбце B список имён, владеющих лицензией. Кроме этого, у Вас есть часть (несколько символов) какого-то лицензионного ключа в ячейке C1, и Вы хотите найти имя владельца.

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

    Эта формула ищет значение из ячейки C1 в заданном диапазоне и возвращает соответствующее значение из столбца B. Обратите внимание, что в первом аргументе мы используем символ амперсанда (&) до и после ссылки на ячейку, чтобы связать текстовую строку.

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

    

    Заметьте, что аргумент table_array (таблица) на скриншоте сверху содержит имя таблицы (Table7) вместо указания диапазона ячеек. Так мы делали в предыдущем примере.

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

    И, наконец, давайте рассмотрим поподробнее последний аргумент, который указывается для функции ВПР – range_lookup (интервальный_просмотр). Как уже упоминалось в начале урока, этот аргумент очень важен. Вы можете получить абсолютно разные результаты в одной и той же формуле при его значении TRUE (ПРАВДА) или FALSE (ЛОЖЬ).

    Для начала давайте выясним, что в Microsoft Excel понимается под точным и приближенным совпадением.

    • Если аргумент range_lookup (интервальный_просмотр) равен FALSE (ЛОЖЬ), формула ищет точное совпадение, т.е. точно такое же значение, что задано в аргументе lookup_value (искомое_значение). Если в первом столбце диапазона table_array (таблица) встречается два или более значений, совпадающих с аргументом lookup_value (искомое_значение), то выбрано будет первое из них. Если совпадения не найдены, функция сообщит об ошибке #N/A (#Н/Д).

    Например, следующая формула сообщит об ошибке #N/A (#Н/Д), если в диапазоне A2:A15 нет значения 4 :

  • Если аргумент range_lookup (интервальный_просмотр) равен TRUE (ИСТИНА), формула ищет приблизительное совпадение. Точнее, сначала функция ВПР ищет точное совпадение, а если такое не найдено, выбирает приблизительное. Приблизительное совпадение – это наибольшее значение, не превышающее заданного в аргументе lookup_value (искомое_значение).

  Если аргумент range_lookup (интервальный_просмотр) равен TRUE (ИСТИНА) или не указан, то значения в первом столбце диапазона должны быть отсортированы по возрастанию, то есть от меньшего к большему. Иначе функция ВПР может вернуть ошибочный результат.

  Чтобы лучше понять важность выбора TRUE (ИСТИНА) или FALSE (ЛОЖЬ), давайте разберём ещё несколько формул с функцией ВПР и посмотрим на результаты.

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

  Как Вы помните, для поиска точного совпадения, четвёртый аргумент функции ВПР должен иметь значение FALSE (ЛОЖЬ).

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

  

  Обратите внимание, что наш диапазон поиска (столбец A) содержит два значения 50 – в ячейках A5 и A6. Формула возвращает значение из ячейки B5. Почему? Потому что при поиске точного совпадения функция ВПР использует первое найденное значение, совпадающее с искомым.

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

  Когда Вы используете функцию ВПР для поиска приблизительного совпадения, т.е. когда аргумент range_lookup (интервальный_просмотр) равен TRUE (ИСТИНА) или пропущен, первое, что Вы должны сделать, – выполнить сортировку диапазона по первому столбцу в порядке возрастания.

  Это очень важно, поскольку функция ВПР возвращает следующее наибольшее значение после заданного, а затем поиск останавливается. Если Вы пренебрежете правильной сортировкой, дело закончится тем, что Вы получите очень странные результаты или сообщение об ошибке #N/A (#Н/Д).

  

  Вот теперь можно использовать одну из следующих формул:

  =VLOOKUP(69,$A$2:$B$15,2,TRUE) или =VLOOKUP(69,$A$2:$B$15,2)
  =ВПР(69;$A$2:$B$15;2;ИСТИНА) или =ВПР(69;$A$2:$B$15;2)

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

  

  Как видите, формула возвратила результат Антилопа (Antelope), скорость которой 61 миля в час, хотя в списке есть также Гепард (Cheetah), который бежит со скоростью 70 миль в час, а 70 ближе к 69, чем 61, не так ли? Почему так происходит? Потому что функция ВПР при поиске точного совпадения возвращает наибольшее значение, не превышающее искомое.

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

  ВПР в Excel – это нужно запомнить!
  1. Функция ВПР в Excel не может смотреть налево. Она всегда ищет значение в крайнем левом столбце диапазона, заданного аргументом table_array (таблица).
  2. В функции ВПР все значения используются без учета регистра, то есть маленькие и большие буквы эквивалентны.
  3. Если искомое значение меньше минимального значения в первом столбце просматриваемого диапазона, функция ВПР сообщит об ошибке #N/A (#Н/Д).
  4. Если 3-й аргумент col_index_num (номер_столбца) меньше 1. функция ВПР сообщит об ошибке #VALUE! (#ЗНАЧ!). Если же он больше количества столбцов в диапазоне table_array (таблица), функция сообщит об ошибке #REF! (#ССЫЛКА!).
  5. Используйте абсолютные ссылки на ячейки в аргументе table_array (таблица), чтобы при копировании формулы сохранялся правильный диапазон поиска. Попробуйте в качестве альтернативы использовать именованные диапазоны или таблицы в Excel.
  6. Когда выполняете поиск приблизительного совпадения, не забывайте, что первый столбец в исследуемом диапазоне должен быть отсортирован по возрастанию.
  7. И, наконец, помните о важности четвертого аргумента. Используйте значения TRUE (ИСТИНА) или FALSE (ЛОЖЬ) обдуманно, и Вы избавитесь от многих головных болей.

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