Формула vlookup на русском

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1. Что ищем.
2. Где ищем.
3. Какие данные берем.

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

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

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

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

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

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

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

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

Автор: rf-webmaestro · Опубликовано 11.05.2018 · Обновлено 08.02.2019

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

Что означает эта аббревиатура

Данный инструмент на английском языке расшифровывается как «Vertical LOOK UP». В русской версии Экселя эта функция называется ВПР, которая в свою очередь означает «Вертикальный Просмотр».

Читайте также:  Один длинный два коротких сигнала bios asus

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

Как использовать VLOOKUP в Excel

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

Также нужно добавить несколько полей, в которых будут выводиться результаты поиска.

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

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

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

Обратите внимание на то, что отчет происходит в пределах выделенной области в поле «Таблица».

1. Интервальный просмотр отвечает за то, требуется ли точное совпадение результатов поиска (0 – полное совпадение, а 1 – допускаются погрешности и небольшие отклонения). Для завершения настроек нужно нажать на «OK».

1. Благодаря этим действиям функция найдет нужную строку и вставит соответствующее имя.

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

1. Благодаря этому формула скопируется во все остальные ячейки. При этом номер искомого столбца скорректируется автоматически.

Использование выпадающих списков

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

1. Перейдите в клетку, в которой мы вводили слово для поиска.
2. Откройте уже знакомую нам вкладку.
3. Кликните на указанный инструмент.
4. Выберите пункт «Проверка данных».

1. Раскройте выпадающий список и выберите соответствующий пункт.

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

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

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

1. Все данные автоматически будут найдены.

Поиск изменения данных при помощи VLOOKUP

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

1. Для начала нужно создать ещё один столбец.

1. Затем скопируем старую таблицу на другой лист. Именно там мы и будем вносить все обновления.

1. В качестве примера изменим какую-нибудь запись.

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

=ВПР($B$3:$B$11;’Последние данные’!$B$3:$G$11;6;ЛОЖЬ)

1. Затем нажимаем на клавишу Enter .

1. Благодаря этому мы получим обновленное соответствующее значение из новой таблицы, но оно совпадает со старым.

1. Копируем эту формулу во все остальные строки.

1. Результат получается следующим.

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

Возможные ошибки

В большинстве случаев у новичков появляется ошибка «#Н/Д». Причем бороться с ней, как показывает практика, довольно сложно. На самом деле ничего страшного тут нет. Главное – найти причину и всё сразу же станет на свои места. Скорее всего, причина вашей проблемы заключается в следующем:

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

Версии MS Excel

Данную функцию можно использовать абсолютно во всех версиях этого редактора (2016, 2013, 2010, 2007). Она есть даже в 2003 году.

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

Заключение

В данной статье мы рассмотрели принцип работы функции «VLOOKUP» в редакторе Excel. Обратите внимание на то, что для поиска какой-нибудь информации достаточно возможностей этого инструмента. Для реализации сложного поиска не обязательно использовать макросы на VBA. Всё можно сделать подручными средствами.

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

Видеоинструкция

Если вам что-то непонятно или остались какие-нибудь вопросы, вы можете попробовать найти на них ответы в следующем видеоролике.

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

Немного о функции ВПР

Итак, что же такое ВПР? Думаю, Вы уже догадались, что это одна из множества функций Excel.

Читайте также:  Знак восклицания в желтом треугольнике

Данная статья рассчитана на читателя, который владеет базовыми знаниями о функциях Excel и умеет пользоваться такими простейшими из них как SUM (СУММ), AVERAGE (СРЗНАЧ) и TODAY(СЕГОДНЯ).

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

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

Обычно в списках вроде этого каждый элемент имеет свой уникальный идентификатор. В данном случае уникальный идентификатор содержится в столбце Item Code.

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

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

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

Другими словами, если Вы введёте в ячейку функцию ВПР и передадите ей в качестве аргумента один из уникальных идентификаторов Вашей базы данных, то в результате в ячейке появится какой-то кусок информации, связанный с этим уникальным идентификатором. Применительно к примеру, приведенному выше: если бы мы ввели в качестве аргумента значение из столбца Item Code, то как результат могли бы получить соответствующее ему описание товара (Description), его цену (Price), или наличие (In Stock). Какую именно информацию должна вернуть формула, Вы сможете решить в процессе её создания.

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

Создаем шаблон

Давайте создадим шаблон счёта, который мы сможем использовать множество раз в нашей вымышленной компании.

Для начала, запустим Excel…

… и создадим пустой счёт.

Вот как это должно работать: пользователь шаблона будет вводить коды товаров (Item Code) в столбец А. После чего система будет извлекать для каждого товара описание и цену, а далее рассчитывать итог по каждой строке. Количество необходимо указать самостоятельно.

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

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

Вставляем функцию ВПР

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

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

Итак, выделите ячейку B11:

Нам требуется открыть список всех существующих функций Excel, чтобы найти в нём ВПР и получить некоторую помощь в заполнении формулы. Для этого зайдите на вкладку Formulas (Формулы) и выберите команду Insert Function (Вставить функцию).

Появляется диалоговое окно, в котором можно выбрать любую существующую в Excel функцию. Чтобы найти то, что нам необходимо, мы можем ввести в поле Search for a function (Поиск функции) слово lookup (или поиск в русскоязычной версии), поскольку нужная нам функция – это функция поиска. Система покажет список всех связанных с этим понятием функций Excel. Найдите в списке VLOOKUP (ВПР), выберите её мышкой и нажмите ОК.

Появится диалоговое окно Function Arguments (Аргументы Функции), предлагающее ввести все необходимые аргументы для функции ВПР. Представьте себе, что это сама функция задаёт Вам следующие вопросы:

1. Какой уникальный идентификатор Вы ищите в этой базе данных?
2. Где находится база данных?
3. Какую информацию Вы бы хотели извлечь из базы данных?

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

Заполняем аргументы функции ВПР

Первый аргумент, который надо указать, это Lookup_value (Искомое_значение). Функция просит нас указать, где искать значение уникального кода товара, описание которого надо извлечь. В нашем случае, это значение в столбце Item code, которое мы ввели раньше в ячейку A11.

Нажмите на иконку выбора справа от строки ввода первого аргумента.

Затем кликните один раз по ячейке, содержащей код товара и нажмите Enter.

Читайте также:  Как изменить рингтон на айфоне

Значение ячейки A11 взято в качестве первого аргумента.

Теперь нужно задать значение аргумента Table_array (Таблица). Другими словами, надо объяснить функции ВПР, где находится база данных, в которой необходимо выполнять поиск. Кликните по иконке выбора рядом со вторым аргументом:

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

Далее мы выделяем все ячейки таблицы, кроме строки заголовков…

… и нажимаем Enter. В строке для ввода второго аргумента автоматически отобразится диапазон ячеек, в котором содержится вся база данных. В нашем случае это ‘Product Database’!A2:D7.

Теперь займёмся третьим аргументом Col_index_num (Номер_столбца). С помощью этого аргумента мы указываем функции ВПР, какой именно кусок информации из базы данных мы хотим извлечь. В данном случае нам необходимо извлечь описание товара (Description). Если Вы посмотрите на базу данных, то увидите, что столбец Description это второй столбец в таблице. Это значит, что для аргумента Col_index_num (Номер_столбца) мы вводим значение 2:

Важно заметить, что мы указываем значение 2 не потому, что столбец Description находится во втором по счету столбце от начала листа Excel, а потому, что он второй по счёту в диапазоне, который указан в качестве аргумента Table_array (Таблица) функции ВПР (первым является столбец с уникальным идентификатором). Если наша база данных будет начинаться где-то со столбца K листа Excel, то мы всё равно укажем значение 2 в этом поле.

В завершение, надо решить, нужно ли нам указывать значение для последнего аргумента ВПР – Range_lookup (Интервальный_просмотр). Значение этого аргумента может быть либо TRUE (ИСТИНА), либо FALSE (ЛОЖЬ), либо вообще может быть не указано. Используя функцию ВПР в работе с базами данных, в 90% случаев принять это решение помогут следующие два правила:

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

Так как первый столбец нашей базы данных не отсортирован, мы вводим для этого аргумента значение FALSE (ЛОЖЬ):

Последний штрих…

Вот и всё! Мы ввели всю информацию, которая требуется функции ВПР, чтобы предоставить нам то значение, которое нас интересует. Жмите ОК и обратите внимание, что описание товара, соответствующее коду R99245, появилось в ячейке B11:

Созданная формула выглядит вот так:

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

Мы можем выполнить те же шаги, чтобы получить значение цены товара (Price) в ячейке E11. Заметьте, что в ячейке E11 должна быть создана новая формула. Результат будет выглядеть так:

… а формула будет выглядеть так:

Обратите внимание, что две созданные формулы отличаются только значением третьего аргумента, которое изменилось с 2 на 3, поскольку теперь нам нужно извлечь значение уже из третьего столбца таблицы.

Если мы решили приобрести 2 единицы товара, то запишем 2 в ячейку D11. Далее вводим простую формулу в ячейку F11, чтобы посчитать итог по этой строке:

… которая выглядит вот так:

Завершаем создание шаблона

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

Наш шаблон ещё не закончен полностью. Чтобы завершить его создание, сделаем следующее:

Удалим значение кода товара из ячейки A11 и значение 2 из ячейки D11. В результате созданные нами формулы сообщат об ошибке.Мы можем исправить это, разумно применив функции IF (ЕСЛИ) и ISBLANK (ЕПУСТО). Изменим нашу формулу с такого вида:

=VLOOKUP(A11,’Product Database’!A2:D7,2,FALSE)
=ВПР(A11;’Product Database’!A2:D7;2;ЛОЖЬ)

=IF(ISBLANK(A11),»»,VLOOKUP(A11,’Product Database’!A2:D7,2,FALSE))
=ЕСЛИ(ЕПУСТО(A11);»»;ВПР(A11;’Product Database’!A2:D7;2;ЛОЖЬ))
Нам нужно скопировать формулы из ячеек B11, E11 и F11 на оставшиеся строки нашего шаблона. Обратите внимание, что если мы просто скопируем созданные формулы, то новые формулы не будут работать правильно с нашей базой данных. Это можно исправить, записав ссылки на ячейки как абсолютные. Другой способ, более продвинутый, это создать именованный диапазон для всех ячеек, вмещающих нашу базу данных (назовём его Products) и использовать имя диапазона вместо ссылок на ячейки. Формула превратится из такой:

=IF(ISBLANK(A11),»»,VLOOKUP(A11,’Product Database’!A2:D7,2,FALSE))
=ЕСЛИ(ЕПУСТО(A11);»»;ВПР(A11;’Product Database’!A2:D7;2;ЛОЖЬ))

…теперь можно смело копировать формулы в ячейки остальных строк нашего шаблона.

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

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

Если подойти к работе с максимальной ответственностью, то можно создать базу данных всех наших клиентов еще на одном листе документа. А затем вводить идентификатор клиента в ячейку F5, чтобы автоматически заполнять ячейки B6, B7 и B8 данными о клиенте.