В табличном редакторе Microsoft Excel множество различных формул и функций. Они позволяют сэкономить время и избежать ошибок – достаточно правильно написать формулу и подставить нужные значения.
В этой статье мы рассмотрим функцию ВПР (или VLOOKUP, что означает «вертикальный просмотр»). Функция ВПР помогает работать с данными из двух таблиц и подтягивать значения из одной в другую. Использовать ее удобно, когда нужно посчитать выручку или прикинуть бюджет, если в одной таблице указан прайс-лист, а в другой количество проданного товара.
Допустим, есть таблица с количеством проданного товара и таблица с ценами на эти товары
Необходимо к каждому товару из таблицы слева добавить цену из прайса справа.
Как создать функцию ВПР в Excel
Необходимая последовательность значений в функции называется синтаксис. Обычно функция начинается с символа равенства «=», затем идет название функции и аргументы в скобках.
Сравнение двух таблиц с помощью функции ВПР
Записываем формулу в столбик цены (С2). Это можно сделать двумя способами:
- Выделить ячейку и вписать функцию.
- Выделить ячейку → нажать на Fx (Shift +F3) → выбрать категорию «Ссылки и массивы» → выбрать функцию ВПР → нажать «ОК».
После этого открывается окно, где можно заполнить ячейки аргументов формулы.
Синтаксис функции ВПР выглядит так:
=ВПР(искомое значение;таблица;номер столбца;интервальный просмотр)
В нашем случае получится такая формула:
=ВПР(A2;$G$2:$H$11;2;0)
Аргументы функции ВПР
Сейчас разберемся что и куда писать.
Со знаком равенства «=» и названием «ВПР» все понятно. Поговорим об аргументах. Они записываются в скобках через точку с запятой или заполняются в ячейки в окне функции. Формула ВПР имеет 4 аргумента: искомое значение, таблица, номер столбца и интервальный просмотр.
Искомое значение – это название ячейки, из которой мы будем «подтягивать» данные. Формула ВПР ищет полное или частичное совпадение в другой таблице, из которой берет информацию.
В нашем случае выбираем ячейку «A2», в ней находится наименование товара. ВПР возьмет это название и будет искать аналогичную ячейку во второй таблице с прайсом.
=ВПР(A2;
Таблица – это диапазон ячеек, из которых мы будем «подтягивать» данные для искомого значения. В этом аргументе используем абсолютные ссылки. Это значит, что в формуле таблица будет выглядеть как «$G$2:$H$11» вместо «G2:H11». Знаки «$» можно поставить вручную, а можно выделить «G2:H11» внутри формулы и нажать F4. Если этого не сделать, таблица не зафиксируется в формуле и изменится при копировании.
ФУНКЦИЯ ВПР В EXCEL, КАК ПОДСТАВИТЬ СРАЗУ ВСЕ ЗНАЧЕНИЯ?
В нашем случае – это таблица с прайсом. Формула будет искать в ней совпадение с ячейкой, которую указали в первом аргументе формулы – A2 (Кофе). Нажимаем F4 и делаем ссылку абсолютной.
=ВПР(A2;$G$2:$H$11
Номер столбца – это столбец таблицы, из которой нужно взять данные. Именно из него мы будем «подтягивать» результат.
- Формула сканирует таблицу по вертикали.
- Находит в самом левом столбце совпадение с искомым значением.
- Смотрит в столбец напротив, очередность которого мы указываем в этом аргументе.
- Передает данные в ячейку с формулой.
В нашем случае – это столбец с ценой продуктов в прайсе. Формула ищет искомое значение ячейки A2 (Кофе) в первом столбце прайса и «подтягивает» данные из второго столбца (потому что мы указали цифру 2) в ячейку с формулой.
=ВПР(A2;$G$2:$H$11;2
Интервальный просмотр – это параметр, который может принимать 2 значения: «истина» или «ложь». Истина обозначается в формуле цифрой 1 и означает приблизительное совпадение с искомым значением. Ложь обозначается цифрой 0 и подразумевает точное совпадение. Приблизительный поиск и критерий «истина» обычно используют при работе с числами, а точный и «ложь» – в работе с наименованиями.
В нашем случае искомое значение – это текстовое наименование. Поэтому используем точный поиск – ставим цифру 0 и закрываем скобку.
=ВПР(A2;$G$2:$H$11;2;0)
Комьюнити теперь в Телеграм
Подпишитесь и будьте в курсе последних IT-новостей
Автозаполнение
В конце протягиваем формулу вниз до конца, в результате чего происходит автозаполнение.
Чтобы функция ВПР правильно сработала во время автозаполнения, искомое значение должно быть относительной ссылкой, а таблица – абсолютной.
- В нашем случае искомое значение – A2. Это относительная ссылка на ячейку, потому что в ней нет знаков «$». Благодаря этому ссылка на искомое значение меняется относительно каждой строчки, когда происходит автозаполнение в другие ячейки: A2 → A3 → … → A11. Это удобно, когда необходимо повторить формулу на несколько строк, ведь ее не приходится писать заново.
- Таблица зафиксирована абсолютной ссылкой «$G$2:$H$11». Это означает, что ссылки на ячейки не изменятся во время автозаполнения. Таким образом, расчет каждый раз будет корректным и опираться на таблицу.
ВПР и приблизительный интервальный просмотр
В предыдущем примере мы «подтягивали» значения из таблицы, используя точный интервальный просмотр. Он подходит для работы с наименованиями. Теперь разберем ситуацию, когда может понадобиться приблизительный интервальный просмотр.
Задача. В магазин привезли товар. Необходимо присвоить каждому товару размер партии, опираясь на его количество.
Товары такие же, как и в первом примере, но задача изменилась: нужно привязать формулу не к наименованию, а к количеству
Решение. Заполняем формулу ВПР в ячейке «Партия», как было показано в предыдущем примере.
Разница в том, что теперь искомое значение – число, а интервальный просмотр – истина, что означает приблизительный поиск. Получается вот такой результат:
Что произошло? Аргумент «интервальный просмотр» имеет значение 1. Это значит, что формула ВПР ищет в таблице ближайшее меньшее искомое значение.
В нашем случае количество товара «Кофе» – 380. ВПР берет это число в виде искомого значения, после чего ищет ближайшее меньшее в соседней таблице – число 300. В конце функция «подтягивает» данные из столбца напротив («Крупная»). Если количество товара «Кофе» = 340 – это «Крупная партия».
Важно, чтобы крайний левый столбец таблицы, которая указана в формуле, был отсортирован по возрастанию. В противном случае ВПР не сработает.
Значения и данные во второй таблице отсортированы по убыванию – ВПР не работает
Итоги
- Функция ВПР означает вертикальный просмотр. Она просматривает крайний левый столбец таблицы сверху вниз.
- Синтаксис функции: =ВПР(искомое значение;таблица;номер столбца;интервальный просмотр).
- Функцию можно вписать вручную или в специальном окне (Shift + F3).
- Искомое значение – относительная ссылка, а таблица – абсолютная.
- Интервальный просмотр может искать точное или приблизительное совпадение с искомым значением.
- Приблизительный поиск и критерий «истина» обычно используют при работе с числами, а точный и «ложь» – в работе с наименованиями.
- Порядок работы с функцией подходит для Гугл-таблиц.
Источник: timeweb.com
Все секреты 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
- Формулы можно использовать для распределения значений по диапазонам.
- Если первый столбец содержит повторяющиеся значения и правильно отсортирован, то будет возвращена последняя из строк с повторяющимися значениями.
- Если искать значение заведомо большее, чем может содержать первый столбец, то можно легко находить последнюю строку таблицы, что бывает довольно ценно.
- Данный вид вернёт ошибку #Н/Д, только если не найдёт значения меньше или равное искомому.
- Понять, что формула возвращает неправильные значения, в случае если ваш массив не отсортирован, довольно затруднительно.
Следствия для формул вида 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) и составной ключ столбца не потребовался бы вовсе.
Это моя первая статья для Лайфхакера. Если вам понравилось, то приглашаю вас посетить мой сайт, а также с удовольствием прочту в комментариях о ваших секретах использования функции ВПР и ей подобных. Спасибо. 🙂
Источник: lifehacker.ru
Как найти данные в Google Таблицах с помощью ВПР
ВПР — одна из самых недооцененных функций в Google Таблицах. Он позволяет выполнять поиск и связывать два набора данных в электронной таблице с помощью одного значения поиска. Вот как им пользоваться.
В отличие от Microsoft Excel, в Google Таблицах нет мастера VLOOKUP, который поможет вам, поэтому вам придется вводить формулу вручную.
Как работает ВПР в Google Таблицах
ВПР может показаться запутанным, но это довольно просто, если вы поймете, как это работает. Формула, использующая функцию ВПР, имеет четыре аргумента.
Первое — это значение ключа поиска, которое вы ищете, а второе — это диапазон ячеек, который вы ищете (например, от A1 до D10). Третий аргумент — это порядковый номер столбца из вашего диапазона для поиска, где первый столбец в вашем диапазоне имеет номер 1, следующий — номер 2 и так далее.
Четвертый аргумент — был ли столбец поиска отсортирован или нет.
Последний аргумент важен только в том случае, если вы ищете наиболее близкое соответствие значению вашего ключа поиска. Если вы предпочитаете возвращать точные совпадения с ключом поиска, установите для этого аргумента значение FALSE.
Вот пример того, как вы можете использовать ВПР. Электронная таблица компании может иметь два листа: один со списком продуктов (каждый с идентификационным номером и ценой), а второй со списком заказов.
Вы можете использовать идентификационный номер в качестве поискового значения ВПР, чтобы быстро найти цену для каждого продукта.
Следует отметить, что функция VLOOKUP не может выполнять поиск в данных слева от номера индекса столбца. В большинстве случаев вам нужно либо игнорировать данные в столбцах слева от ключа поиска, либо поместить данные ключа поиска в первый столбец.
Использование ВПР на одном листе
В этом примере предположим, что у вас есть две таблицы с данными на одном листе. Первая таблица — это список имен сотрудников, идентификационных номеров и дней рождения.
Во второй таблице вы можете использовать ВПР для поиска данных, которые используют любой из критериев из первой таблицы (имя, идентификационный номер или день рождения). В этом примере мы будем использовать ВПР, чтобы указать день рождения для определенного идентификационного номера сотрудника.
Соответствующая формула ВПР для этого: = ВПР (F4, A3: D9, 4, FALSE).
Чтобы разбить это, ВПР использует значение ячейки F4 (123) в качестве ключа поиска и выполняет поиск в диапазоне ячеек от A3 до D9. Он возвращает данные из столбца номер 4 в этом диапазоне (столбец D, «День рождения»), и, поскольку мы хотим точного совпадения, последний аргумент — ЛОЖЬ.
В этом случае для идентификатора 123 функция ВПР возвращает дату рождения 19.12.1971 (в формате ДД / ММ / ГГ). Мы расширим этот пример дальше, добавив в таблицу B столбец для фамилий, чтобы он связывал даты рождения с реальными людьми.
Для этого требуется лишь простое изменение формулы. В нашем примере в ячейке H4 = VLOOKUP (F4, A3: D9, 3, FALSE) ищет фамилию, которая соответствует ID-номеру 123.
Вместо того, чтобы возвращать дату рождения, он возвращает данные из столбца номер 3 («Фамилия»), соответствующие значению идентификатора, расположенному в столбце номер 1 («ID»).
Используйте ВПР для нескольких листов
В приведенном выше примере использовался набор данных с одного листа, но вы также можете использовать ВПР для поиска данных на нескольких листах в электронной таблице. В этом примере информация из таблицы A теперь находится на листе под названием «Сотрудники», а таблица B теперь на листе под названием «Дни рождения».
Вместо использования обычного диапазона ячеек, такого как A3: D9, вы можете щелкнуть пустую ячейку и затем ввести: = ВПР (A4, Сотрудники! A3: D9, 4, FALSE).
Когда вы добавляете имя листа в начало диапазона ячеек (Сотрудники! A3: D9), формула ВПР может использовать данные из отдельного листа при поиске.
Использование подстановочных знаков с функцией ВПР
В приведенных выше примерах для поиска совпадающих данных использовались точные значения ключей поиска. Если у вас нет точного значения ключа поиска, вы также можете использовать подстановочные знаки, такие как вопросительный знак или звездочку, с VLOOKUP.
В этом примере мы будем использовать тот же набор данных из наших примеров выше, но если мы переместим столбец «Имя» в столбец A, мы сможем использовать частичное имя и подстановочный знак звездочки для поиска фамилий сотрудников.
Формула VLOOKUP для поиска фамилий с использованием частичного имени: = VLOOKUP (B12, A3: D9, 2, FALSE); значение вашего ключа поиска помещается в ячейку B12.
В приведенном ниже примере «Chr *» в ячейке B12 соответствует фамилии «Компьютерщик» в образце справочной таблицы.
Поиск ближайшего совпадения с помощью ВПР
Вы можете использовать последний аргумент формулы ВПР для поиска точного или наиболее близкого совпадения со значением вашего ключа поиска. В наших предыдущих примерах мы искали точное совпадение, поэтому мы установили это значение в FALSE.
Если вы хотите найти ближайшее совпадение со значением, измените последний аргумент ВПР на ИСТИНА. Поскольку этот аргумент указывает, отсортирован ли диапазон или нет, убедитесь, что ваш столбец поиска отсортирован от А до Я, иначе он не будет работать правильно.
В нашей таблице ниже у нас есть список товаров для покупки (от A3 до B9), а также их названия и цены. Они отсортированы по цене от наименьшей к наибольшей. Наш общий бюджет, который мы можем потратить на один предмет, составляет 17 долларов (ячейка D4). Мы использовали формулу VLOOKUP, чтобы найти самый доступный элемент в списке.
Соответствующая формула ВПР для этого примера: = ВПР (D4, A4: B9, 2, ИСТИНА). Поскольку эта формула ВПР настроена на поиск ближайшего соответствия ниже, чем само значение поиска, она может искать только элементы, которые дешевле установленного бюджета в 17 долларов США.
В этом примере самый дешевый предмет стоимостью менее 17 долларов — это сумка, которая стоит 15 долларов, и это предмет, который формула ВПР вернула в качестве результата в D5.
Источник: toadmin.ru