Совет: Попробуйте использовать новую функцию ПРОСМОТРX , улучшенную версию функции ВЛОП, которая работает в любом направлении и по умолчанию возвращает точные совпадения, что упрощает и удобнее в использовании, чем предшественницу.
Если вам нужно найти что-то в таблице или диапазоне по строкам, используйте В ПРОСМОТР. Например, можно найти цену автомобильной части по номеру части или имя сотрудника на основе его ИД.
Самая простая функция ВПР означает следующее:
=В.ПРОСМОТР(то, что вы хотите найти, где ее искать; номер столбца в диапазоне, содержащего возвращаемую величину, возвращает приблизительное или точное совпадение, обозначенные как 1/ИСТИНА или 0/ЛОЖЬ).
Совет: Секрет функции ВПР состоит в организации данных таким образом, чтобы искомое значение (Фрукт) отображалось слева от возвращаемого значения, которое нужно найти (Количество).
Используйте функцию ВПР для поиска значения в таблице.
Значение для поиска. Иного значения должно быть в первом столбце диапазона ячеек, который указан в table_array.
Например, если массив таблицы охватывает ячейки B2:D7, lookup_value должны быть в столбце B.
Искомое_значение может являться значением или ссылкой на ячейку.
Диапазон ячеек, в котором будет выполнен поиск искомого_значения и возвращаемого значения с помощью функции ВПР. Можно использовать именуемый диапазон или таблицу, а не ссылки на ячейки, а имена в аргументе.
Первый столбец в диапазоне ячеев должен содержать lookup_value . Диапазон ячеев также должен включать возвращаемую величину, которая требуется найти.
Номер столбца (начиная с 1 в левом большинстве столбцов table_array),содержащий возвращаемую величину.
Логическое значение, определяющее, какое совпадение должна найти функция ВПР, — приблизительное или точное.
Приблизительное совпадение: 1/ИСТИНА предполагает, что первый столбец таблицы отсортжен в алфавитном или числовом порядке, а затем будет выполнять поиск ближайшего значения. Это способ по умолчанию, если не указан другой. Например, =ВКП(90;A1:B100;2;ИСТИНА).
Точное совпадение: 0/ЛОЖЬ ищет точное значение в первом столбце. Например, =ВКП(«Кузнецов»;A1:B100;2;ЛОЖЬ).
Начало работы
Для построения синтаксиса функции ВПР вам потребуется следующая информация:
Значение, которое вам нужно найти, то есть искомое значение.
Диапазон, в котором находится искомое значение. Помните, что для правильной работы функции ВПР искомое значение всегда должно находиться в первом столбце диапазона. Например, если искомое значение находится в ячейке C2, диапазон должен начинаться с C.
Номер столбца в диапазоне, содержащий возвращаемое значение. Например, если в качестве диапазона указать B2:D11, следует посчитать B первым столбцом, C — вторым и так далее.
При желании вы можете указать слово ИСТИНА, если вам достаточно приблизительного совпадения, или слово ЛОЖЬ, если вам требуется точное совпадение возвращаемого значения. Если вы ничего не указываете, по умолчанию всегда подразумевается вариант ИСТИНА, то есть приблизительное совпадение.
Теперь объедините все перечисленное выше аргументы следующим образом:
=ВLOOKUP(искомые значения, диапазон, содержащий искомые значения, номер столбца в диапазоне, содержащий возвращаемую величину, приблизительное совпадение (ИСТИНА) или Точное совпадение (ЛОЖЬ)).
Примеры
Вот несколько примеров использования функции ВПР.
Пример 1
Пример 2
Пример 3
Пример 4
Пример 5
Функцию ВЛОП можно использовать для объединения нескольких таблиц в одну, если одна из таблиц имеет поля, общие для всех остальных. Это особенно полезно, если вам нужно поделиться книгой с людьми, у которых есть более старые версии Excel, которые не поддерживают функции данных с несколькими таблицами в качестве источников данных, путем объединения источников в одну таблицу и изменения источника данных функции данных в новую таблицу, функцию данных можно использовать в более старых версиях Excel (при условии, что функция данных поддерживается более старой версией).
Здесь столбцы A–F и H имеют значения или формулы, которые используют только значения на этом сайте, а остальные столбцы используют В ПРОСМОТР и значения столбцов A (код клиента) и B (Доверенность) для получения данных из других таблиц.
Скопируйте таблицу с общими полями на новый и придать ей имя.
Чтобы открыть диалоговое окно Управление отношениями, > в > управления отношениями нажмите кнопку Data > Data Tools (Управление отношениями).
Для каждой из указанных связей обратите внимание на следующее:
Поле, которое связывает таблицы (в скобки в диалоговом окне). Это первый lookup_value для формулы ВЛКП.
Имя связанной таблицы подсмотра. Это первый table_array в формуле ВЛИО.
Поле (столбец) в связанной таблице подытовки с данными, которые должны быть в новом столбце. Эта информация не отображается в диалоговом оке Управление связями. Чтобы узнать, какое поле нужно извлечь, необходимо посмотреть в связанной таблице подыска. Обратите внимание на номер столбца (A=1) — это col_index_num формуле.
Чтобы добавить поле в новую таблицу, введите формулу в первом пустом столбце СРОТ, используя сведения, собранные на шаге 3.
В нашем примере в столбце G для получения данных «Ставка выставления счета» из четвертого столбца (col_index_num = 4) из таблицы «Доверенности» используется столбец «Доверенность» lookup_value(table_array) с формулой =ВЛП([@Attorney];tbl_Attorneys;4;ЛОЖЬ).
В формуле также можно использовать ссылку на ячейку и ссылку на диапазон. В нашем примере это будет =ВЛВП(A2;’Поверенные’! A:D,4;ЛОЖЬ).
Продолжайте добавлять поля, пока не будут добавлены все необходимые поля. Если вы пытаетесь подготовить книгу, содержащую функции данных, которые используют несколько таблиц, измените источник данных функции на новую таблицу.
Источник
Функция ВПР (VLOOKUP) в Excel для чайников
Функция ВПР в Excel (на английском — VLOOKUP) по некоторому ключевому полю «подтягивает» данные из одного диапазона в другой. Ключевое поле должно присутствовать в обоих диапазонах данных (и там, куда «подтягиваем», и там, откуда берем данные).
Функция ВПР в Экселе: пошаговая инструкция
Представим, что перед нами стоит задача определить стоимость проданных товаров. Стоимость рассчитывается, как произведение количества и цены. Сделать это очень легко, если количество и цены находятся в соседних колонках. Однако данные могут быть представлены не в столь удобном виде. Исходная информация может находиться в совершенно разных таблицах и в другом порядке. В первой таблице указаны количества проданных товаров:
Во второй – цены:
Если перечень товаров в обеих таблицах совпадает, то, зная магическое сочетание Ctrl+C и Ctrl+V, данные о ценах можно легко подставить к данным о количестве. Однако очередность позиций в обеих таблицах не совпадает. Тупо скопировать цены и подставить к количеству не получится.
Поэтому мы не можем прописать формулу умножения и «протянуть» вниз на все позиции.
Что делать? Надо как-то цены из второй таблицы подставить к соответствующему количеству в первой, т.е. цену товара А к количеству товара А, цену Б к количеству Б и т.д.
Функция ВПР в Эксель легко справится с задачей.
Добавим вначале в первую таблицу новый столбец, куда будут подставляться цены из второй таблицы.
Для вызова функции с помощью Мастера нужно активировать ячейку, где будет прописана формула и нажать кнопку f(x) в самом начале строки формул. Появится диалоговое окно Мастера, где из списка всех функций нужно выбрать ВПР.
Кликаем по надписи «ВПР». Открывается следующее диалоговое окно.
Теперь нужно заполнить предлагаемые поля. В первом окошке «Искомое_значение» нужно указать критерий для ячейки, в которую мы вписываем формулу. В нашем случае это ячейка с наименованием товара «А».
Следующее поле «Таблица». В нем нужно указать диапазон данных, где будет осуществляться поиск нужных значений. В нашем случае это вторая таблица с ценой. При этом крайний левый столбец выделяемого диапазона должен содержать те самые критерии, по которым осуществляется поиск (столбец с наименованиями товаров). Затем таблица выделяется вправо минимум до того столбца, где находятся искомые значения (цены). Можно и дальше вправо выделить, но это уже ни на что не влияет. Главное, чтобы выделенная таблица начиналась со столбца с критериями и захватывала нужный столбец с данными. Также следует обратить внимание на тип ссылок, они должны быть абсолютными, т.к. формула будет копироваться в другие ячейки.
Следующее поле «Номер_столбца» — это число, на которое столбец с искомыми данными (ценами) отстоит от столбца с критерием (наименованием товара) включительно. То есть отсчет идет, начиная с самого столбца с критерием. Если у нас во второй таблице оба столбца находятся рядом, то нужно указать число 2 (первый – критерий, второй — цены). Часто бывает, что данные отстоят от критерия на 10 или 20 столбцов. Это не важно, Excel все сосчитает.
Последнее поле «Интервальный_просмотр», где указывается тип поиска: точное (0) или приблизительное (1) совпадение критерия. Пока ставим 0 (или ЛОЖЬ). Второй вариант рассмотрен ниже.
Нажимаем ОК. Если все правильно и значение критерия есть в обеих таблицах, то на месте только что введенной формулы появится некоторое значение. Остается только протянуть (или просто скопировать) формулу вниз до последней строки таблицы.
Теперь легко рассчитать стоимость простым умножением количества на цену.
Формулу ВПР можно прописать вручную, набирая аргументы по порядку, и разделяя точкой с запятой (см. видеоурок ниже).
Особенности использования формулы ВПР в Excel
Функция ВПР имеет свои особенности, о которых следует знать.
1. Первую особенность можно считать общей для функций, которые используются для многих ячеек путем прописывания формулы в одной из них и дальнейшим копированием в остальные. Здесь нужно обращать внимание на относительность и абсолютность ссылок. Конкретно в ВПР критерий (первое поле) должно иметь относительную ссылку (без знаков $), так как у каждой ячейки свой собственный критерий. А вот поле «Таблица» должно иметь абсолютную ссылку (адрес диапазона прописывается через $). Если этого не сделать, то при копировании формулы диапазон «поедет» вниз и многие значения просто не найдутся, так как искать будет негде.
2. Номер столбца, указываемый в третьем поле «Номер_столбца» при использовании Мастера функций, должен отсчитываться, начиная с самого критерия.
3. Функция ВПР из диапазона с искомыми данными выдает первое сверху значение. Это значит, что, если во второй таблице, откуда мы пытаемся «подтянуть» некоторые данные, присутствуют несколько ячеек с одинаковым критерием, то в рамках выделенного диапазона ВПР захватит первое сверху значение. Об этом следует помнить. К примеру, если мы хотим к цене товара подтянуть количество из другой таблицы, а там этот товар встречается несколько раз (в нескольких строках), то к цене подтянется первое сверху количество.
4. Последний параметр формулы, который 0 (нуль), ставить нужно обязательно. Иначе формула может криво работать.
5. После использования ВПР саму формулу лучше сразу удалить, оставив только полученные значения. Делается это очень просто. Выделяем диапазон с полученными значениями, нажимаем «копировать» и на это же место с помощью специальной вставки вставляем значения. Если таблицы находятся в разных книгах Excel, то очень удобно разорвать внешние связи (оставив вместо них только значения) с помощью специальной команды, которая находится по пути Данные → Изменить связи.
После вызова функции разрывания внешних связей появится диалоговое окно, где нужно нажать кнопку «Разорвать связь» и затем «Закрыть».
Это позволит удалить сразу все внешние ссылки.
Примеры функции ВПР в Excel
Для следующих примеров использования функции ВПР возьмем немного другие данные.
Требуется цены из второй таблицы подтянуть в первую. В качестве критерия здесь используется код. Ниже показаны этапы вычисления ВПР.
Вторая таблица меньше первой, т.е. некоторые коды в ней отсутствуют. Для отсутствующих позиций ВПР выдает ошибку #Н/Д.
Появление таких ошибок, кстати, можно использовать для пользы дела, когда нужно найти отличия в таблицах. Но, скорее всего, ошибки помешают.
Конструкция с функцией ЕСЛИОШИБКА
Вместе с функцией ВПР часто используют функцию ЕСЛИОШИБКА, которая «заглушает» ошибки #Н/Д и вместо них возвращает некоторое значение. Обычно это 0 или пусто.
Как видно, ошибок больше нет, а вместо них пустые ячейки.
Разные форматы критерия в таблицах
Одна из распространенных причин появления ошибок заключается в несовпадении форматов критериев в двух таблицах. Текстовый и числовой форматы воспринимаются функцией ВПР как разные значения. Возможны два варианта.
Первый случай, когда критерии в первой таблице сохранены как числа, а критерии во второй таблице – как текст.
В ячейках с числами, сохраненными как текст, в левом верхнем углу появляется зелененький треугольник. Можно выделить все такие числа и в раскрывающемся списке выбрать Преобразовать в число.
Такое решение используется довольно часто. Но оно не всегда подходит. Например, когда данные из второй таблицы регулярно выгружаются из какой-нибудь базы данных типа 1С. В подобных файлах вообще все сохранено как текст. И если мы планируем постоянно использовать такие данные, вставляя их в заранее подготовленный диапазон, то лучше, чтобы формулы работали без дополнительного вмешательства.
Автоматически изменить формат критерия во второй таблице нельзя, т.к. ссылка ведет на целый диапазон. Придется вмешиваться в ссылку на критерий в первой таблице. Для этого потребуется дописать функцию ТЕКСТ, которая изменит числовой формат на текстовый. Синтаксис функции ТЕКСТ предполагает обязательное указание формата. Достаточно задать формат #. Ниже картинка с готовой формулой.
Две ошибки по-прежнему связаны с тем, что эти товары отсутствую во второй таблице. Чтобы их заглушить, можно вновь воспользоваться функцией ЕСЛИОШИБКА.
Вторая ситуация, заключается в том, что «текстом» являются критерий из первой таблицы. Форматы снова не совпадают.
Как и в прошлый раз, будем вносить коррективы в функцию ВПР. Преобразовать «текст» в «число» еще проще. Достаточно к ссылке на «текстовый» критерий добавить 0 или умножить на 1.
Бывает еще и третья, смешанная ситуация. Она встречается гораздо реже. Это когда в первой и второй таблице критерии сохранены и как число, и как текст, вперемешку. Здесь потребуется задействовать сразу все описанные выше функции: ЕСЛИОШИБКА, ТЕКСТ и +0. Вначале прописываем ЕСЛИОШИБКА и в качестве первого аргумента этой функции записываем ВПР с какой-либо конструкцией для изменения формата. Например, ВПР с формулой ТЕКСТ. В качестве второго аргумента (т.е. того, что должно быть в случае ошибки) записываем вторую конструкцию ВПР с +0. Таким образом, если ВПР с функцией ТЕКСТ не выдает ошибку, значит все ОК. Но если первая конструкция возвращает ошибку #Н/Д, то функция ЕСЛИОШИБКА подставляет вторую конструкцию – ВПР с +0. Другими словами, мы вначале принудительно делаем все критерии текстовыми, а затем, числовыми. Таким образом, ВПР проверяет оба формата. Один из них совпадет с форматом во второй таблице. Немного громоздко получается, но в целом все работает.
Отсутствующие критерии по-прежнему вызывают ошибку #Н/Д. В таком случае всю формулу можно еще раз «обернуть» в ЕСЛИОШИБКА.
Функция СЖПРОБЕЛЫ для чистки текстового критерия
В качестве критерия рекомендуется брать уникальный код, в котором опечатки, характерные для текста, маловероятны. Но иногда все-таки кода нет и критерием выступает текст (названия организаций, фамилии людей и т.д.). В этом случае возможны случайные ошибки в написании. Одна из распространенных ошибок – лишние пробелы. Проблема решается просто с помощью функции СЖПРОБЕЛЫ для всех критериев. Сделать это можно внутри формулы ВПР, а можно и предварительно пройтись по всем критериям в обеих таблицах. Кому как удобней.
Подсчет номера столбца в большой таблице
Если во второй таблице много столбцов, да еще часть из них скрыта или сгруппирована, то подсчитать напрямую количество столбцов между критерием и нужными данными, весьма непросто. Есть прием, который позволяет вообще не считать эти столбцы. Для этого во время выделения второй таблицы следует посмотреть в нижний правый угол выделяемого диапазона. Там появляется подсказка о количестве выделенных строк и столбцов. Запоминаем число столбцов и вносим в формулу ВПР.
Здорово экономит время.
Интервальный просмотр в функции ВПР
Наступило время обсудить последний аргумент функции ВПР. Как правило, указываю 0, чтобы функция искала точное совпадение критерия. Но есть вариант приблизительного поиска, это называется интервальный просмотр.
Рассмотрим алгоритм работы ВПР при выборе интервального просмотра. Прежде всего (это обязательно), столбец с критериями в таблице поиска должен быть отсортированы по возрастанию (если числа) или по алфавиту (если текст). ВПР просматривает список критериев сверху и ищет равный, а если его нет, то ближайший меньший к указанному критерию, т.е. на одну ячейку выше (поэтому и нужна предварительная сортировка. После нахождения подходящего критерия ВПР отсчитывает указанное количество столбцов вправо и забирает оттуда содержимое ячейки, что и является результатом работы формулы.
Проще понять на примере. По результатам выполнения плана продаж каждому торговому агенту нужно выдать заслуженную премию (в процентах от оклада). Если план выполнен менее, чем на 100%, премия не положена, если план выполнен от 100% до 110% (110% не входит) – премия 20%, от 110% до 120% (120% не входит) – 40%, 120% и более – премия 60%. Данные находятся в следующем виде.
Требуется подставить премию на основании выполнения планов продаж. Для решения задачи в первой ячейке пропишем следующую формулу:
и скопируем вниз.
На рисунке ниже изображена схема, как работает интервальный просмотр функции ВПР.
Джеки Чан выполнил план на 124%. Значит ВПР в качестве критерия ищет во второй таблице ближайшее меньшее значение. Это 120%. Затем отсчитывает 2 столбца и возвращает премию 60%. Брюс Ли план не выполнил, поэтому его ближайший меньший критерий – 0%.
Предлагаю посмотреть видеоурок о работе ВПР из курса «Основные функции Excel».
