Функция впр в excel

Для приблизительного поиска данные должны быть отсортированы.

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

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

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

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

Сущность и назначение функции ВПР в Excel

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

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

Возможность работы с несколькими условиями

Ещё одним несомненным достоинством функции VLOOKUP является его способность работать с несколькими параметрами, присущими вашему товару. Чтобы найти товар по двум или более характеристикам, необходимо:

1. Создать два (или, при необходимости, более) условий для поиска.
2. Добавить новый столбец, в который в процессе работы функции добавятся все прочие столбцы, по которым происходит поиск товара.
3. В полученном столбце, по вышеописанному алгоритму, вводим уже знакомую нам формулу функции VLOOKUP.

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

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

Сегодня мы рассмотрим:

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

Впр excel — расшифровка и пояснение

Объяснение для чайников начать нужно с расшифровки названия функции. ВПР — всего лишь сокращение от Вертикального Поиска. На английском выглядит название с аббревиатурой так: VLOOKUP – от Vertical LOOKUP. По названию уже можно понять, что функция считывает информацию в столбцах по вертикали, начиная слева направо, и является идентификатором этой информации. ВПР в эксель — это один из самых быстрых и простых способов найти в таблице интересующие данные. Например, артикул определенного товара в списке. Или его цену. Простыми словами ВПР — это самая обычная команда, которую можно задать с помощью составленной определенным образом формулы — и получить искомый ответ.

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

Аргументы важно указать, чтобы отсортировать элементы с одинаковым названием, но различными параметрами, и получить точный ответ на поисковый запрос. Что еще можно сделать при помощи ВПР в эксель:

Что еще можно сделать при помощи ВПР в эксель:

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

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

Инструкция с примером, как использовать ВПР excel

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

1. Открыть таблицу эксель.
2. В любой свободной ячейке справа — на картинке-примере ячейка «Е», — указать формулу.
3. Формула **ВПР **начинается со знака «=».
4. Далее нужно задать функцию — это и есть ВПР (соответствует VLOOKUP).
5. Затем открываем круглые скобки, вводим аргументы и закрываем круглые скобки.

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

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

Важно! Функция ВПР в excel всегда ищет, начиная с левого столбца. То есть, сначала ВПР отсортирует элементы по названию

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

Есть еще четвертый аргумент. Он определяет, следует искать точное совпадение или все приблизительные. Если мы ищем точное совпадение, то четвертым аргументом указываем False (ложь). Если же достаточно приблизительных совпадений, то ввести следует True (истина). Вот и все. На этом формула для ВПР в эксель составлена. Осталось нажать клавишу «энтер», и получить нужную информацию.

ИНДЕКС и ПОИСКПОЗ могут больше и лучше, чем ВПР.

Если вы будете читать о работе в Excel, то вы, вероятно,  столкнетесь с дебатами «ВПР или ИНДЕКС+ПОИСКПОЗ ?».  Спор может быть на удивление горячим 🙂

Суть в следующем: ИНДЕКС+ПОИСКПОЗ может делать все, что может делать ВПР (и ГПР), с гораздо большей гибкостью, но за счет немного большей сложности. 

Таким образом, те, кто поддерживает ИНДЕКС и ПОИСКПОЗ, будут утверждать (и очень разумно), что вы могли бы также начать их изучать, так как в итоге это дает вам лучший набор инструментов.

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

Мой совет состоит в том, что если вы часто используете Excel, то обязательно нужно научиться использовать ИНДЕКС и ПОИСКПОЗ. Это очень мощная комбинация.

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

Еще об использовании функции ВПР:

Функция ВПР в Экселе: пошаговая инструкция

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

Во второй – цены:

Если перечень товаров в обеих таблицах совпадает, то, зная магическое сочетание Ctrl+C и Ctrl+V, данные о ценах можно легко подставить к данным о количестве. Однако очередность позиций в обеих таблицах не совпадает. Тупо скопировать цены и подставить к количеству не получится.

Поэтому мы не можем прописать формулу умножения и «протянуть» вниз на все позиции.

Что делать? Надо как-то цены из второй таблицы подставить к соответствующему количеству в первой, т.е. цену товара А к количеству товара А, цену Б к количеству Б и т.д.

Вот так.

Функция ВПР в Эксель легко справится с задачей.

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

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

Кликаем по надписи «ВПР». Открывается следующее диалоговое окно.

Теперь нужно заполнить предлагаемые поля. В первом окошке «Искомое_значение» нужно указать критерий для ячейки, в которую мы вписываем формулу. В нашем случае это ячейка с наименованием товара «А».

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

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

Следующее поле «Номер_столбца» — это число, на которое столбец с искомыми данными (ценами) отстоит от столбца с критерием (наименованием товара) включительно. То есть отсчет идет, начиная с самого столбца с критерием. Если у нас во второй таблице оба столбца находятся рядом, то нужно указать число 2 (первый – критерий, второй — цены). Часто бывает, что данные отстоят от критерия на 10 или 20 столбцов

Это не важно, Excel все сосчитает

Последнее поле «Интервальный_просмотр», где указывается тип поиска: точное (0) или приблизительное (1) совпадение критерия. Пока ставим 0 (или ЛОЖЬ). Второй вариант рассмотрен ниже.

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

Теперь легко рассчитать стоимость простым умножением количества на цену.

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

Двумерный поиск по известным строке и столбцу

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

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

Существует несколько способов выполнить двумерный поиск. Познакомьтесь с возможными вариантами и выберите наиболее подходящий.

Функции ВПР и ПОИСКПОЗ

Вы можете использовать связку из функций ВПР (VLOOKUP) и ПОИСКПОЗ (MATCH), чтобы найти значение на пересечении полей Название продукта (строка) и Месяц (столбец) рассматриваемого массива:

Формула выше – это обычная функция ВПР, которая ищет точное совпадение значения “Lemons” в ячейках от A2 до A9. Но так как Вы не знаете, в каком именно столбце находятся продажи за март, то не сможете задать номер столбца для третьего аргумента функции ВПР. Вместо этого используется функция ПОИСКПОЗ, чтобы определить этот столбец.

В переводе на человеческий язык, данная формула означает:

• Ищем символы “Mar” – аргумент lookup_value (искомое_значение);
• Ищем в ячейках от A1 до I1 – аргумент lookup_array (просматриваемый_массив);
• Возвращаем точное совпадение – аргумент match_type (тип_сопоставления).

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

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

Функции ИНДЕКС и ПОИСКПОЗ

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

Именованные диапазоны и оператор пересечения

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

1. Выделите таблицу, откройте вкладку Formulas (Формулы) и нажмите Create from Selection (Создать из выделенного).
2. Отметьте галочками Top row (в строке выше) и Left column (в столбце слева). Microsoft Excel назначит имена диапазонам из значений в верхней строке и левом столбце Вашей таблицы. Теперь Вы можете осуществлять поиск, используя эти имена, напрямую, без создания формул.

3. В любой пустой ячейке запишите =имя_строки имя_столбца, например, так:

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

При вводе имени, Microsoft Excel будет показывать подсказку со списком подходящих имен, так же, как при вводе формулы.

1. Нажмите Enter и проверьте результат

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

Как работает функция?

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

С одним условием

Рассмотрим функцию на простом примере поиска сотрудника по присвоенному ему коду. Таблицу нужно отсортировать в порядке возрастания.

1. Кликнуть по ячейке справа от таблицы или под ней и вписать искомый номер.
2. В ячейке рядом ввести функцию ВПР через знак «=», либо же использовать опцию «Вставить функцию» в разделе «Формулы». Проще использовать именно опцию – там есть подсказки, какие именно данные нужны.
3. Задать параметры поиска, заполнив соответствующие поля. Интервальный просмотр в данном случае пропустим.
4. Нажать «Enter» – и появится результат поиска.

Когда таблица не отсортирована и данные введены в хаотичном порядке, результат будет неправильный – программа найдет ближайшее соответствие («ИСТИНА»). Но можно не сортировать таблицу, а указать интервальный просмотр «ЛОЖЬ».

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

С несколькими условиями

Часто на практике требуется сравнить данные нескольких диапазонов и выбрать значение с учетом 2-х и более критериев. Здесь задействована также функция ЕСЛИ, которая отвечает как раз за условия.

Из таблицы нужно найти показатель выручки по конкретному менеджеру в определенный день:

1. В первую выделенную ячейку вписать дату, во вторую – фамилию менеджера. В третьей ячейке будет происходить поиск.
2. Кликнуть по пустой ячейке и ввести формулу =ВПР(G1;ЕСЛИ(C2:C12=G2;A2:D12;»»);4;0).
3. Для подтверждения действия зажать комбинацию клавиш Ctrl+Shift+Enter. Нажатие на «Enter» не сработает в этом случае, потому что формула должна быть выполнена в массиве (об этом свидетельствуют скобки «{}», в которые взята вся формула).

Поиск по нескольким столбцам

Объем данных расширен, и нужно найти конкретное значение среди нескольких столбцов, просуммировав данные с помощью функции СУММ.

1. Кликнуть по ячейке и ввести формулу =СУММ(ВПР(G1;A1:D12;{2;3;4};ЛОЖЬ)). Для третьего аргумента перечисление столбцов происходит в скобках «{}».
2. Одновременно зажать клавиши Ctrl+Shift+Enter. В результате формула будет взята в фигурные скобки «{}».

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

Таким же способом можно найти среднее значение с помощью СРЗНАЧ: =СРЗНАЧ(ВПР(G1;A1:D12;{2;3;4};ЛОЖЬ)).

Сравнение двух таблиц

ВПР помогает сопоставить значения в таблицах.

Необходимо сравнить зарплату сотрудников за 2 месяца, для этого:

1. В таблице с зарплатой за март добавить еще один столбец.
2. Клацнуть по первой ячейке в столбце и написать функцию ВПР со следующими аргументами: =ВПР($A$2:$A$12;ссылка_на_новый_лист!$A$2:$B$12;2;ЛОЖЬ). То есть нужно выделить диапазон с фамилиями менеджеров и сделать ссылки (строки и столбца) неизменными с помощью знака «$», посмотреть его в таблице с новой зарплатой, взять данные из второго столбца новой зарплаты и подставить их в ячейку С2. В результате отобразится первый результат.
3. При помощи маркера заполнения протянуть полученное значение вниз.

По желанию теперь можно найти численную и процентную разницу.

Поиск в выпадающем списке

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

Для создания раскрывающегося списка:

1. Поставить курсор в ячейку, где он будет располагаться.
2. Перейти в раздел книги «Данные» – «Проверка данных».
3. В «Типе данных» выбрать «Список», задать диапазон (в нашем случае – фамилии менеджеров).
4. Нажать «Ок». Отобразится список.
5. В следующую ячейку вписать функцию ВПР. Первый аргумент – ссылка на раскрывающийся список, второй – диапазон таблицы, третий – номер столбца, четвертый – «ЛОЖЬ». В итоге получится следующее: =ВПР(E1;A1:B12;2;ЛОЖЬ). Нажать «Enter».

Меняется фамилия в списке – меняется и зарплата.

Перенос данных

Есть таблица с менеджерами и объемом их продаж. Во второй таблице значится сумма премии за продажу для каждого менеджера. Необходимо перенести данные в левую таблицу, чтобы подсчитать общую выручку (произведение объема продаж и премии за 1 продажу: =ПРОИЗВЕД(C2*D2)).

1. Выделить первую ячейку с премией в левой таблице. Написать функцию с аргументами, сделать неизменными значения из второй таблицы, указать в третьем аргументе столбец 2, вместо «ЛОЖЬ» можно вписать 0: =ВПР(B2;$G$2:$H$12;2;0)
2. Протянуть формулу вниз, чтобы заполнить все ячейки.

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

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

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

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

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

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

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

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

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

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

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

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

lookup_value (искомое_значение) – значение, которое нужно искать.Это может быть значение (число, дата, текст) или ссылка на ячейку (содержащую искомое значение), или значение, возвращаемое какой-либо другой функцией Excel. Например, вот такая формула будет искать значение 40:
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)

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

table_array (таблица) – два или более столбца с данными.Запомните, функция ВПР всегда ищет значение в первом столбце диапазона, заданного в аргументе table_array (таблица). В просматриваемом диапазоне могут быть различные данные, например, текст, даты, числа, логические значения. Регистр символов не учитывается функцией, то есть символы верхнего и нижнего регистра считаются одинаковыми.Итак, наша формула будет искать значение 40 в ячейках от A2 до A15, потому что A – это первый столбец диапазона A2:B15, заданного в аргументе table_array (таблица):
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)

col_index_num (номер_столбца) – номер столбца в заданном диапазоне, из которого будет возвращено значение, находящееся в найденной строке.Крайний левый столбец в заданном диапазоне – это 1, второй столбец – это 2, третий столбец – это 3 и так далее. Теперь Вы можете прочитать всю формулу:
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)

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

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

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

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

Формулы подстановки Excel: ВПР, ИНДЕКС и ПОИСКПОЗ

Если произвести поиск по функциям подстановки, Google покажет, что ВПР намного популярнее функции ИНДЕКС. Оно и понятно, ведь чтобы придать функции ИНДЕКС тот же функционал, что и ВПР, необходимо воспользоваться еще одной формулой – ПОИСКПОЗ. Что касается меня, было всегда непросто попробовать и освоить две новые функции одновременно. Но они дают больше возможностей и гибкости в создании электронных таблиц. Но обо всем по порядку.

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

Предположим, у вас есть таблица с данными о работниках. В первой колонке хранится табельный номер сотрудника, в остальных – другие данные (ФИО, отдел и т.д.). Если у вас есть табельный номер, то можно воспользоваться функцией ВПР, чтобы вернуть определенную информацию о сотруднике. Синтаксис формулы =ВПР(искомое_значение; таблица; номер_столбца; ). Она говорит Excel: «Найди в таблице строку, первая ячейка которой совпадает с искомым_значением, и верни значение ячейки с порядковым номером номер_столбца».

Но случаются ситуации, когда у вас есть имя сотрудника и необходимо вернуть табельный номер. На рисунке в ячейке A10 – имя работника и требуется определить табельный номер в ячейке B10.

Когда ключевое поле находится правее данных, которые вы хотите получить, ВПР не поможет. Если, конечно, была бы возможность задать номер_столбца -1, тогда проблем бы не было. Одним из распространенных решений является добавление нового столбца A, копирование имен сотрудников в этот столбец, заполнить табельные номера с помощью ВПР, сохранить их как значения и удалить временную колонку A.

Функция ИНДЕКС()

Чтобы решить нашу проблему в один шаг, необходимо воспользоваться формулами ИНДЕКС и ПОИСКПОЗ. Сложность данного подхода заключается в том, что требуется применить две функции, которые, возможно, вы никогда не применяли до этого. Для упрощения понимания решим эту задачу в два этапа.

Начнем с функции ИНДЕКС. Кошмарное название. Когда кто-нибудь говорит «индекс», у меня в голове не возникает ни единой ассоциации, чем же занимается эта функция. А требует она целых три аргумента: =ИНДЕКС(массив; номер_строки; ).

Говоря по-простому, Excel идет в массив данных и возвращает значение, находящееся на пересечении указанной строки и столбца. Как будто бы просто. Таким образом, формула =ИНДЕКС($A$2:$C$6;4;2) вернет значение, находящееся в ячейке B5.

Применительно к нашей проблеме, чтобы вернуть табельный номер работника, формула должна выглядеть следующим образом =ИНДЕКС($A$2:$A$6;?;1). Выглядит как бессмыслица, но если мы заменим знак вопроса формулой ПОИСКПОЗ, у нас есть решение.

Функция ПОИСКПОЗ()

Синтаксис этой функции таков: =ПОИСКПОЗ(искомое_значение; просматриваемы_массив; ).

Она говорит Excel: «Найди искомое_значение в массиве данных и верни номер строки массива, в которой это значение встречается». Таким образом, чтобы найти в какой строке находиться имя сотрудника в ячейке A10, необходимо прописать формулу =ПОИСКПОЗ(A10; $B$2:$B$6; 0). Если в ячейке A10 будет имя «Колин Фарел», тогда ПОИСКПОЗ вернет 5-ю строку массива B2:B6.

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

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

Особенности использования формулы ВПР в Excel

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

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

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

А вот поле «Таблица» должно иметь абсолютную ссылку (адрес диапазона прописывается через $). Если этого не сделать, то при копировании формулы диапазон «поедет» вниз и многие значения просто не найдутся, так как искать будет негде.

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

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

4. Последний параметр формулы, который 0 (нуль), ставить нужно обязательно. Иначе формула может криво работать.

5. После использования ВПР саму формулу лучше сразу удалить, оставив только полученные значения. Делается это очень просто. Выделяем диапазон с полученными значениями, нажимаем «копировать» и на это же место с помощью специальной вставки вставляем значения. Если таблицы находятся в разных книгах Excel, то очень удобно разорвать внешние связи (оставив вместо них только значения) с помощью специальной команды, которая находится по пути Данные → Изменить связи.

После вызова функции разрывания внешних связей появится диалоговое окно, где нужно нажать кнопку «Разорвать связь» и затем «Закрыть».

Это позволит удалить сразу все внешние ссылки. 

Синтаксис и описание функции ВПР в Excel

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

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

Буква «В» в ВПР означает «вертикальный». Она используется для дифференциации функции ВПР и ГПР, которая ищет значение в верхней строке массива («Г» обозначает «горизонтальный»).

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

Синтаксис функции ВПР выглядит следующим образом:

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

1. искомое_значение – это значение для поиска.

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

Поиск числа : =ВПР(40; A2:B15; 2) — формула будет искать число 40.
Поиск текста : =ВПР(«яблоки»; A2:B15; 2) — формула будет искать текст «яблоки»

Обратите внимание, что вы всегда включаете текстовые значения в «двойные кавычки».
Поиск значения из другой ячейки : =ВПР(C2; A2:B15; 2) — формула будет искать значение в ячейке C2.

1. таблица — это два или более столбца данных.

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

Итак, наша формула =ВПР(40; A2:B15; 2) будет искать «40» в ячейках от A2 до A15, потому что A — это первый столбец таблицы A2: B15.

1. номер_столбца — номер столбца в таблице, из которой должно быть возвращено значение в соответствующей строке.

Самый левый столбец в указанной таблице равен 1, второй столбец — 2, третий — 3 и т. д.

Итак, теперь вы можете прочитать всю формулу =ВПР(40; A2:B15; 2). Формула ищет «40» в ячейках от A2 до A15 и возвращает соответствующее значение из столбца B (потому что B является вторым столбцом в указанной таблице A2:B15).

4. интервальный_просмотр определяет, ищете ли вы точное соответствие (ЛОЖЬ) или приблизительное соответствие (ИСТИНА или опущено). Этот последний параметр является необязательным, но очень важным.

Функция ВПР в Excel: инструкция для чайников

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

Таблица в «Excel»

В другой таблице мы имеем тот же самый список с указанием стоимости этих материалов:

Прайс-лист в «Excel»

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

Приступим к делу:

В первой таблице нам не хватает двух столбцов – «Цена» (то есть стоимость за 1 кв.м) и «Стоимость» (то есть общая стоимость завезенного материала). Добавим эти столбцы. Теперь выделите в столбце «Цена» верхнюю первую ячейку, запустите «Мастер функций» (нажать одновременно «F3» и «Shift»), во вкладке «Формулы» нажмите на «Ссылки и массивы» и в выпавшем списке выберите «ВПР».

Запустите «Мастер функций», во вкладке «Формулы» нажмите на «Ссылки и массивы» и в выпавшем списке выберите «ВПР»

Далее в новом открывшемся окне напротив пункта «Искомое значение» наблюдаем следующие показатели: A1:А15. То есть программа фиксирует диапазон наименований материалов в соответствующем столбе «Материалы». То же самое программа должна показать и во второй таблице.

Наблюдаем следующие показатели: A1:А15

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

Нажмите на пункт «Таблица»

Далее выделите результаты в пункте «Таблица» и нажмите на «F4», после чего в них появится новый символ «$» — таким образом программа будет ссылаться на показатели в «Таблица».

Выделите результаты в пункте «Таблица» и нажмите на «F4»

Далее переходим к третьему пункту – «Номер столбца». Укажите здесь «2». В последнем четвертом пункте «Интервальный просмотр» укажите «ЛОЖЬ». После всех этих манипуляций нажмите на «ОК».

В последнем четвертом пункте «Интервальный просмотр» укажите «ЛОЖЬ»

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

Курсором мышки нажмите на нижний правый угол таблицы и потяните вниз

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

• Выделите все показатели в столбце «Цена», нажмите на нем правой кнопкой мышки и далее – на «Копировать».
• Снова нажмите правой кнопкой мышки по уже выделенным ценам и затем – на «Специальная вставка».
• В открывшемся окошке поставьте галку, как показано на скриншоте, и нажмите на «ОК»

Поставьте галку на «Значения»