Light-electric.com

IT Журнал
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Что такое формула впр в excel

Как работать с функцией ВПР в Эксель: инструкция для новичков

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

Использование ВПР в программе Excel

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

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

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

Вторая – содержит тот же список, но в ней ещё есть цена за 1 штуку.

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

Этапы работы (инструкция):

  1. Для начала в первую Excel таблицу добавляются два столбца: «Цена за 1 шт.» и «Общая сумма».
  2. Отметить верхнее поле в новом.
  3. Выбрать раздел формулы, и нажать «Вставить функцию».
  4. Из предложенных категорий Excel отметить «Ссылки и массивы».
  5. Найти ВПР, и нажать «ОК».
  6. Заполнить открывшееся окно «Аргументы».

– это товары из первой таблицы, которые необходимо будет определить во второй. Их значение выставляется таким образом: X: Y, где Х – это адрес первой ячейки столбика с товарами, а Y – последней. В рассматриваемой это А2 и А5.

– в этом поле будет стоимость из второго листа с данными. Чтобы её проставить следует кликнуть по строке, затем перейти на страницу с суммой, и выделить нужное (А2 – В5).

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

Фиксирование информации производится путём нажатия горячей клавиши F4, на выделенной строке. Если всё сделано правильно там же появится значок $.

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

Интервальный просмотр – логическое значение Excel, где точно это ЛОЖЬ, а приближённо – ИСТИНА. Если пользователю нужны точные, он должен написать «ЛОЖЬ».

В конечном итоге, окно «Аргументы» выглядит так:

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

Теперь, чтобы сосчитать общую стоимость предмета, достаточно вставить соответствующую формулу в ячейку Е2, и также растянуть её на все продукты. Конец инструкции.

Как сравнить две таблицы: пошаговая инструкция для «чайников»

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

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

Делается это в несколько шагов:

  1. Открыть первую со старой информацией.
  2. Добавить дополнительный столбик для новых данных «Новая стоимость».
  3. Выделить первое пустое поле в созданном столбце (С2).
  4. Выбрать раздел «ВПР Формулы» и «Вставить функцию».
  5. Найти категорию Excel «Ссылки и массивы».
  6. Выбрать ВПР.
  7. Задать «Аргументы».

– то, что важно будет найти во второй таблице. Чтобы значение появилось в строке, нужно выделить первый столбик с наименованиями товаров (А2 – А5).

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

Номер столбца – второй, так как именно стоимость переносится в новую.

Интервальный просмотр – ЛОЖЬ.

Заполненное окно выглядит так:

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

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

Поиск с помощью ВПР по нескольким условиям

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

Итак, имеется документ, в котором обозначены: компании, товары и цены.

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

Чтобы осуществить поиск следует:

  1. Создать слева новый столбец с объединёнными данными (название компании и товара).

Делается это просто:

  • выделить крайнюю левую ячейку (А1);
  • щёлкнуть ПКМ и выбрать «Вставить»;
  • отметить добавление столбца и нажать «ОК».

  1. Внести данные в новый столбец. Для этого нужно нажать на пустое поле А2, ввести формулу объединения (=B2&C2) и нажать кнопку Enter. Чтобы продлить список достаточно растянуть ячейку.

  2. Нажать на любое свободное место и самостоятельно ввести, что нужно найти (ЛасточкаГелевая ручка).
  3. Выбрать ячейку где будет отображен результат и заполнить Аргументы функции.

– что нужно найти (щёлкнуть по введенной — ЛасточкаГелевая ручка – А8).

– где искать нужное значение (выделить ячейки от первой до последней — А2 – D5).

Номер столбца – из какого столбца вывести результат (4).

Интервальный просмотр – ЛОЖЬ.

После нажатия команды «ОК», программа отобразит результат.

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

Чтобы сделать выпадающий список из существующего нужно следовать инструкции:

  1. Выбрать поле, в котором будет сформированы показатели. Например, Е2.
  2. Зайти в раздел «Данные», и выбрать «Проверка данных».
  3. Установить тип данных, как список.

  1. В появившуюся строку «Источник» ввести информацию (выделить с первой до последней ячейки – А2:А5).

Выпадающий список готов.

Теперь с помощью функции ВПР нужно добавить возможность просмотра цены, при выборе товара. Как это работает в Эксель? (Инструкция).

  1. Создать новое поле с названием «Цена».
  2. Вставить аргументы.

– ячейка Excel, в которой находится выпадающий список (Е2).

– выделенный фрагмент с предметами и ценами (А2-В5).

Номер столбца – 2 (в нём находятся цены).

Интервальный просмотр – ЛОЖЬ.

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

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

Как работает функция ВПР

Здравствуй уважаемый читатель!

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

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

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

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

=ВПР(_искомое значение_;_таблица_; _номер столбика_; _[интервальный просмотр]_), где:

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

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

Читать еще:  Печать в excel

Формула ищет текст «GM», в столбике А на текущем листе. Совет! При использовании аргумента «таблица», желательно использовать, такой вариант, как абсолютные ссылки (это адрес ячейки со знаком $). В этом случае диапазон поиска станет закреплённым и не изменится при копировании формулы.

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

=ВПР(”GM”;[Путь к файлу]База!А2:В10;2)

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

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

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

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

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

Совет! Для того что бы функция ВПР, корректно работала нужно в качестве четвертого аргумента использовать параметр «ЛОЖЬ». Ну, если мы уже затронули тему точного или приближенного совпадения в синтаксисе функции ВПР, то давайте ее рассмотрим поподробнее:

  • если аргумент «интервальный просмотр» равен «ЛОЖЬ», в таком случае формула ищет точное совпадение с аргументом «искомое значение». Если формула встретит два и более значения, отвечающих аргументу «искомое значение», то будет выбрано первое из списка, в случае, когда совпадение не найдены, формула вернет ошибку #Н/Д;
  • если же аргумент «интервальный просмотр» имеет значение «ИСТИНА» формула будет искать приблизительное совпадение, точнее будет, что функция сначала поищет точное совпадение, а уже потом, не найдя его, выберет приблизительное.

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

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

=ВПР(200;$A$2:$B$15;2;ЛОЖЬ)

Несмотря на то, что значений 200 у нас несколько, получили только одно, так как при точном совпадении функции ВПР, система использует только первое значение которое было найдено в указанном диапазоне. Теперь испытаем, работу функции ВПР для поиска приблизительного совпадения значений. Поищем, какой автомобиль ездит со скоростью 260 км/час. Первое что вы делаете в случае, когда «интервальный просмотр» равняется «ИСТИНА» — вы выполняете сортировку вашего диапазона значений по первому столбику по порядку его возрастания. Это необходимо и важно, поскольку функция ВПР находит следующее наибольшее значение от заданного условия, а после поиск прекращается. Если же вы не последуете совету по сортировке, итогом будет сообщение об ошибке #Н/Д или другие странные результаты.

Для поиска используем ВПР следующего вида:

=ВПР(260;$A$2:$B$15;2;ИСТИНА)

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

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

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

Не забудьте поблагодарить автора!

Без сомнения, бережливость лучше расточительности, но также неоспоримо, что она хуже полезной затраты.
Генри Форд

Все секреты Excel-функции ВПР (VLOOKUP) для поиска данных в таблице и извлечения их в другую

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

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

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

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

ВПР — это аббревиатура от вертикального просмотра. Аналогично и VLOOKUP — Vertical LOOKUP. Уже само название функции намекает нам, что она производит поиск в строках таблицы (по вертикали — перебирая строки и фиксируя столбец), а не в столбцах (по горизонтали — перебирая столбцы и фиксируя строку). Надо заметить, что у ВПР есть сестра — гадкий утёнок, которая никогда не станет лебедем, — это функция ГПР (HLOOKUP). ГПР, в противоположность ВПР, производит горизонтальный поиск, однако концепция Excel (да и вообще концепция организации данных) подразумевает, что ваши таблицы имеют небольшое количество столбцов и гораздо большее количество строк. Именно поэтому поиск по строкам нам требуется во много раз чаще, чем по столбцам. Если вы в Excel слишком часто пользуетесь функцией ГПР, то, вполне вероятно, что вы чего-то не поняли в этой жизни.

Синтаксис

Функция ВПР имеет четыре параметра:

=ВПР( ; ; [; ] ), тут:

— искомое значение (редко) или ссылка на ячейку, содержащую искомое значение (подавляющее большинство случаев);

— ссылка на диапазон ячеек (двумерный массив), в ПЕРВОМ (!) столбце которого будет осуществляться поиск значения параметра ;

— номер столбца в диапазоне, из которого будет возвращено значение;

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

Держу пари, что многие из тех, кто знает функцию ВПР как облупленную, прочитав описание четвёртого параметра, могут почувствовать себя неуютно, так как они привыкли видеть его в несколько ином виде: обычно там идёт речь о точном соответствии при поиске (ЛОЖЬ или 0) либо же о диапазонном просмотре (ИСТИНА или 1).

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

Как же конкретно работает формула ВПР

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

Схемы работы формул

ВПР тип I

ВПР тип II

Следствия для формул вида I

  1. Формулы можно использовать для распределения значений по диапазонам.
  2. Если первый столбец содержит повторяющиеся значения и правильно отсортирован, то будет возвращена последняя из строк с повторяющимися значениями.
  3. Если искать значение заведомо большее, чем может содержать первый столбец, то можно легко находить последнюю строку таблицы, что бывает довольно ценно.
  4. Данный вид вернёт ошибку #Н/Д, только если не найдёт значения меньше или равное искомому.
  5. Понять, что формула возвращает неправильные значения, в случае если ваш массив не отсортирован, довольно затруднительно.
Читать еще:  Как в excel округлить до 10

Следствия для формул вида II

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

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

Вы добрались до кульминационного места статьи. Казалось бы, ну какая разница, укажу ли я в качестве последнего параметра ноль или единицу? В основном все указывают, конечно же, ноль, так как это довольно практично: не надо заботиться о сортировке первого столбца массива, сразу видно, найдено значение или нет. Но если у вас на листе несколько тысяч формул ВПР (VLOOKUP), то вы заметите, что ВПР вида II работает медленно. При этом обычно все начинают думать:

  • мне нужен более мощный компьютер;
  • мне нужна более быстрая формула, например, многие знают про ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH), которая якобы быстрее на жалкие 5–10%.

И мало кто думает, что стоит только начать использовать ВПР вида I и обеспечить любыми способами сортировку первого столбца, как скорость работы ВПР возрастёт в 57 раз. Пишу прописью — В ПЯТЬДЕСЯТ СЕМЬ РАЗ! Не на 57%, а на 5 700%. Данный факт я проверил вполне надёжно.

Секрет такой быстрой работы кроется в том, что на отсортированном массиве можно применять чрезвычайно эффективный алгоритм поиска, который носит название бинарного поиска (метод деления пополам, метод дихотомии). Так вот ВПР вида I его применяет, а ВПР вида II ищет без какой-либо оптимизации вообще. То же самое относится и к функции ПОИСКПОЗ (MATCH), которая включает в себя аналогичный параметр, а также и к функции ПРОСМОТР (LOOKUP), которая работает только на отсортированных массивах и включена в Excel ради совместимости с Lotus 1-2-3.

Недостатки формулы

Недостатки ВПР очевидны: во-первых, она ищет только в первом столбце указанного массива, а во-вторых, только справа от данного столбца. А как вы понимаете, вполне может случиться так, что столбец, содержащий необходимую информацию, окажется слева от столбца, в котором мы будем искать. Этого недостатка лишена уже упомянутая связка формул ИНДЕКС+ПОИСКПОЗ (INDEX+MATCH), что делает её наиболее гибким решением по извлечению данных из таблиц в сравнении с ВПР (VLOOKUP).

Некоторые аспекты применения формулы в реальной жизни

Диапазонный поиск

Классическая иллюстрация к диапазонному поиску — задача определения скидки по размеру заказа.

Поиск текстовых строк

Безусловно, ВПР ищет не только числа, но и текст. При этом надо принимать во внимание, что регистр символов формула не различает. Если использовать символы подстановки, то можно организовать нечёткий поиск. Есть два символа подстановки: «?» — заменяет один любой символ в текстовой строке, «*» — заменяет любое количество любых символов.

Борьба с пробелами

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

Разный формат данных

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

=ВПР(−−D7; Продукты!$A$2:$C$5; 3; 0) — если D7 содержит текст, а таблица — числа;

=ВПР(D7 & «»); Продукты!$A$2:$C$5; 3; 0) — и наоборот.

Кстати, перевести текст в число можно сразу несколькими способами, выбирайте:

  • Двойное отрицание —D7.
  • Умножение на единицу D7*1.
  • Сложение с нулём D7+0.
  • Возведение в первую степень D7^1.

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

Как подавить выдачу #Н/Д

Это очень удобно делать при помощи функции ЕСЛИОШИБКА (IFERROR).

Например: =ЕСЛИОШИБКА( ВПР(D7; Продукты!$A$2:$C$5; 3; 0); «»).

Если ВПР вернёт код ошибки #Н/Д, то ЕСЛИОШИБКА его перехватит и подставит параметр 2 (в данном случае пустая строка), а если ошибки не произошло, то эта функция сделает вид, что её вообще нет, а есть только ВПР, вернувший нормальный результат.

Массив

Часто забывают ссылку массива сделать абсолютной, и при протягивании массив «плывёт». Помните, что вместо A2:C5 следует использовать $A$2:$C$5.

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

Ещё более хорошей идеей будет объявление этого массива в виде именованного диапазона.

Многие пользователи при указании массива используют конструкцию вида A:C, указывая столбцы целиком. Этот подход имеет право на существование, так как вы избавлены от необходимости отслеживать тот факт, что ваш массив включает все необходимые строки. Если вы добавите строки на лист с первоначальным массивом, то диапазон, указанный как A:C, не придётся корректировать. Безусловно, эта синтаксическая конструкция заставляет Excel проводить несколько большую работу, чем при точном указании диапазона, но данными накладными расходами можно пренебречь. Речь идёт о сотых долях секунды.

Ну и на грани гениальности — оформить массив в виде умной таблицы.

Использование функции СТОЛБЕЦ для указания колонки извлечения

Если таблица, в которую вы извлекаете данные при помощи ВПР, имеет ту же самую структуру, что и справочная таблица, но просто содержит меньшее количество строк, то в ВПР можно использовать функцию СТОЛБЕЦ() для автоматического расчёта номеров извлекаемых столбцов. При этом все ВПР-формулы будут одинаковыми (с поправкой на первый параметр, который меняется автоматически)! Обратите внимание, что у первого параметра координата столбца абсолютная.

Создание составного ключа через &»|»&

Если возникает необходимость искать по нескольким столбцам одновременно, то необходимо делать составной ключ для поиска. Если бы возвращаемое значение было не текстовым (как тут в случае с полем «Код»), а числовым, то для этого подошла бы более удобная формула СУММЕСЛИМН (SUMIFS) и составной ключ столбца не потребовался бы вовсе.

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

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

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

ФУНКЦИЯ ВПР используется, если нужно найти элементы в таблице или диапазоне по строкам. Например, можно найти цену на автомобильную часть по номеру детали или получить имя сотрудника на основе его кода сотрудника.

Совет: Чтобы получить дополнительную справку о функции ВПР, просмотрите эти видео с YouTube от экспертов сообщества Excel!

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

= ВПР (необходимые условия для поиска, номер столбца в диапазоне, содержащий возвращаемое значение, возвращаемое приближенное или точное соответствие — обозначено как 1/истина или 0/ложь).

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

Используйте функцию ВПР для поиска значения в таблице.

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

= ВПР (A2; A10: C20; 2; ИСТИНА)

= ВПР (a2; сведения о клиенте)! А:Ф, 3, ЛОЖЬ)

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

Например, если Таблица-массив охватывает ячейки B2: D7, то lookup_value должен находиться в столбце B.

Искомое_значение может являться значением или ссылкой на ячейку.

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

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

Номер столбца (начиная с 1 для самого левого столбца table_array), содержащего возвращаемое значение.

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

Приближенное соответствие: 1/истина предполагает, что первый столбец в таблице отсортирован либо в числовом формате, либо в алфавитном порядке, а затем будет искать ближайшее значение. Это способ по умолчанию, если не указан другой. Например, = ВПР (90; a1: B100; 2; истина).

Читать еще:  Замена в excel 2020

Точное совпадение: 0/ложь осуществляет поиск точного значения в первом столбце. Например, = ВПР («Смит»; a1: B100; 2; ложь).

Начало работы

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

Значение, которое вам нужно найти, то есть искомое значение.

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

Номер столбца в диапазоне, содержащий возвращаемое значение. Например, если в качестве диапазона задано значение B2: D11, число B должно быть первым столбцом, а в качестве второго — «C» и т. д.

При желании вы можете указать слово ИСТИНА, если вам достаточно приблизительного совпадения, или слово ЛОЖЬ, если вам требуется точное совпадение возвращаемого значения. Если вы ничего не указываете, по умолчанию всегда подразумевается вариант ИСТИНА, то есть приблизительное совпадение.

Теперь объедините все перечисленное выше аргументы следующим образом:

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

Примеры

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

Пример 1

Пример 2

Пример 3

Пример 4

Пример 5

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

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

Скопируйте таблицу с общими полями на новый лист и присвойте ей имя.

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

Для каждой из перечисленных связей обратите внимание на следующее:

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

Имя связанной таблицы подстановки. Это table_array формулы функции ВПР.

Поле (столбец) в связанной таблице подстановки с данными, которые вы хотите добавить в новый столбец. Эти сведения не отображаются в диалоговом окне Управление связями. чтобы узнать, какое поле вы хотите извлечь, вам потребуется ознакомиться со связанной таблицей подстановки. Вы хотите отметить номер столбца (A = 1) – это col_index_num в формуле.

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

В нашем примере столбец G использует юрист ( lookup_value) для получения данных о тарифных курсах из четвертого столбца (col_index_num = 4) из таблицы судебные тблатторнэйс ( table_array) с формулой = ВПР ([@Attorney], tbl_Attorneys, 4, ложь).

В формуле также могут использоваться ссылки на ячейки и ссылки на диапазоны. В нашем примере это было бы = ВПР (a2; «Юристы»! А:Д, 4, ложь).

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

Использование функции ВПР (VLOOKUP) для подстановки значений

Кому лень или нет времени читать — смотрим видео. Подробности и нюансы — в тексте ниже.

Постановка задачи

Итак, имеем две таблицы — таблицу заказов и прайс-лист:

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

Решение

В наборе функций Excel, в категории Ссылки и массивы (Lookup and reference) имеется функция ВПР (VLOOKUP) . Эта функция ищет заданное значение (в нашем примере это слово «Яблоки») в крайнем левом столбце указанной таблицы (прайс-листа) двигаясь сверху-вниз и, найдя его, выдает содержимое соседней ячейки (23 руб.) Схематически работу этой функции можно представить так:

Для простоты дальнейшего использования функции сразу сделайте одну вещь — дайте диапазону ячеек прайс-листа собственное имя. Для этого выделите все ячейки прайс-листа кроме «шапки» (G3:H19), выберите в меню Вставка — Имя — Присвоить (Insert — Name — Define) или нажмите CTRL+F3 и введите любое имя (без пробелов), например Прайс. Теперь в дальнейшем можно будет использовать это имя для ссылки на прайс-лист.

Теперь используем функцию ВПР. Выделите ячейку, куда она будет введена (D3) и откройте вкладку Формулы — Вставка функции (Formulas — Insert Function) . В категории Ссылки и массивы (Lookup and Reference) найдите функцию ВПР (VLOOKUP) и нажмите ОК. Появится окно ввода аргументов для функции:

Заполняем их по очереди:

  • Искомое значение (Lookup Value) — то наименование товара, которое функция должна найти в крайнем левом столбце прайс-листа. В нашем случае — слово «Яблоки» из ячейки B3.
  • Таблица (Table Array) — таблица из которой берутся искомые значения, то есть наш прайс-лист. Для ссылки используем собственное имя «Прайс» данное ранее. Если вы не давали имя, то можно просто выделить таблицу, но не забудьте нажать потом клавишу F4 , чтобы закрепить ссылку знаками доллара , т.к. в противном случае она будет соскальзывать при копировании нашей формулы вниз, на остальные ячейки столбца D3:D30.
  • Номер_столбца (Column index number) — порядковый номер (не буква!) столбца в прайс-листе из которого будем брать значения цены. Первый столбец прайс-листа с названиями имеет номер 1, следовательно нам нужна цена из столбца с номером 2.
  • Интервальный_просмотр (Range Lookup) — в это поле можно вводить только два значения: ЛОЖЬ или ИСТИНА:
      • Если введено значение или ЛОЖЬ (FALSE) , то фактически это означает, что разрешен поиск только точного соответствия, т.е. если функция не найдет в прайс-листе укзанного в таблице заказов нестандартного товара (если будет введено, например, «Кокос»), то она выдаст ошибку #Н/Д (нет данных).
      • Если введено значение 1 или ИСТИНА (TRUE) , то это значит, что Вы разрешаете поиск не точного, а приблизительного соответствия, т.е. в случае с «кокосом» функция попытается найти товар с наименованием, которое максимально похоже на «кокос» и выдаст цену для этого наименования. В большинстве случаев такая приблизительная подстановка может сыграть с пользователем злую шутку, подставив значение не того товара, который был на самом деле! Так что для большинства реальных бизнес-задач приблизительный поиск лучше не разрешать. Исключением является случай, когда мы ищем числа, а не текст — например, при расчете Ступенчатых скидок.

Все! Осталось нажать ОК и скопировать введенную функцию на весь столбец.

Ошибки #Н/Д и их подавление

Функция ВПР (VLOOKUP) возвращает ошибку #Н/Д (#N/A) если:

  • Включен точный поиск (аргумент Интервальный просмотр=0) и искомого наименования нет в Таблице.
  • Включен приблизительный поиск (Интервальный просмотр=1), но Таблица, в которой происходит поиск не отсортирована по возрастанию наименований.
  • Формат ячейки, откуда берется искомое значение наименования (например B3 в нашем случае) и формат ячеек первого столбца (F3:F19) таблицы отличаются (например, числовой и текстовый). Этот случай особенно характерен при использовании вместо текстовых наименований числовых кодов (номера счетов, идентификаторы, даты и т.п.) В этом случае можно использовать функции Ч и ТЕКСТ для преобразования форматов данных. Выглядеть это будет примерно так:
    =ВПР(ТЕКСТ(B3);прайс;0)
  • Функция не может найти нужного значения, потому что в коде присутствуют пробелы или невидимые непечатаемые знаки (перенос строки и т.п.). В этом случае можно использовать текстовые функции СЖПРОБЕЛЫ (TRIM) и ПЕЧСИМВ (CLEAN) для их удаления:
    =ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(B3));прайс;0)
    =VLOOKUP(TRIM(CLEAN(B3));прайс;0)

Для подавления сообщения об ошибке #Н/Д (#N/A) в тех случаях, когда функция не может найти точно соответствия, можно воспользоваться функцией ЕСЛИОШИБКА (IFERROR) . Так, например, вот такая конструкция перехватывает любые ошибки создаваемые ВПР и заменяет их нулями:

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

Ссылка на основную публикацию
Adblock
detector