ВПР в Excel – очень полезная функция, позволяющая подтягивать данные из одной таблицы в другую по заданным критериям. Это достаточно «умная» команда, потому что принцип ее работы складывается из нескольких действий: сканирование выбранного массива, выбор нужной ячейки и перенос данных из нее.
Весь процесс просмотра и выборки данных происходит за доли секунды, поэтому результат мы получаем моментально.
Синтаксис ВПР
ВПР расшифровывается как вертикальный просмотр. То есть команда переносит данные из одного столбца в другой. Для работы со строками существует горизонтальный просмотр – ГПР.
Аргументы функции следующие:
- искомое значение – то, что предполагается искать в таблице;
- таблица – массив данных, из которых подтягиваются данные. Примечательно, таблица с данными должна находиться правее исходной таблицы, иначе функций ВПР не будет работать;
- номер столбца таблицы, из которой будут подтягиваться значения;
- интервальный просмотр – логический аргумент, определяющий точность или приблизительность значения (0 или 1).
Как перемещать данные с помощью ВПР?
Рассмотрим пример на практике. Имеем таблицу, в которой прописываются партии заказанных товаров (она подсвечена зеленым). Справа прайс, где значатся цены каждого товара (подсвечен голубым). Нам нужно перенести данные по ценам из правой таблицы в левую, чтобы подсчитать стоимость каждой партии. Вручную это делать долго, поэтому воспользуемся функцией Вертикального Просмотра.
Как подставить данные из одной таблицы в другую с помощью ВПР
В ячейку D3 нужно подтянуть цену гречки из правой таблицы. Пишем =ВПР и заполняем аргументы.
Искомым значением будет гречка из ячейки B3. Важно проставить именно номер ячейки, а не слово «гречка», чтобы потом можно было протянуть формулу вниз и автоматически получить остальные значения.
Таблица – выделяем прайс без шапки. Т.е. только сами наименования товаров и их цены. Этот массив мы зафиксируем клавишей F4, чтобы он не изменялся при протягивании формулы.
Номер столбца – в нашем случае это цифра 2, потому что необходимые нам данные (цена) стоят во втором столбце выделенной таблицы (прайса).
Интервальный просмотр – ставим 0, т.к. нам нужны точные значения, а не приблизительные.
Видим, что из правой таблицы в левую подтянулась цена гречки.
Протягиваем формулу вниз и визуально проверяем некоторые товары, чтобы понять, что все сделали правильно.
Вот так, с помощью элементарных действий можно подставлять значения из одной таблицы в другую. Важно помнить, что функция Вертикального Просмотра работает только если таблица, из которой подтягиваются данные, находится справа. В противном случае, нужно переместить ее или воспользоваться командой ИНДЕКС и ПОИСКПОЗ. Овладев этими двумя функциями вы сможете намного больше реализовать сложных решений чем те возможности, которые предоставляет функция ВПР или ГПР.
Как подставить данные из одной таблицы в другую с помощью программы Excel
- Создать таблицу
- Форматирование
- Функции Excel
- Формулы и диапазоны
- Фильтр и сортировка
- Диаграммы и графики
- Сводные таблицы
- Печать документов
- Базы данных и XML
- Возможности Excel
- Настройки параметры
- Уроки Excel
- Карта сайта
- Скачать примеры
Источник: exceltable.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
Функция ВПР в Excel
ВПР (англ. вариант – VLOOKUP) – очень удобная и полезная функция особенно для работы с большим объемом данных, поскольку позволяет автоматически сопоставить диапазоны с десятками тысяч наименований.
2020-07-26 23:49:57 368
- Синтаксис
- Как работает функция?
- С одним условием
- С несколькими условиями
- Поиск по нескольким столбцам
- Сравнение двух таблиц
- Поиск в выпадающем списке
- Перенос данных
- Ошибки
- Вместо заключения
ВПР (англ. вариант – VLOOKUP) – очень удобная и полезная функция особенно для работы с большим объемом данных, поскольку позволяет автоматически сопоставить диапазоны с десятками тысяч наименований. Является разновидностью поиска, но только вертикального (сверху вниз) – извлекает информацию из таблицы или определенного диапазона по строкам. Работает во всех версиях Excel и даже Google Sheets.
Синтаксис
Элемент – может быть числовым (адрес ячейки) или текстовым («текст»).
Адрес таблицы – диапазон ячеек, где примерно находится значение.
Номер столбца – принимает целое число из диапазона от 1 до n, из него будет извлечен результат.
Интервальный просмотр – приблизительное (ближайшее) соответствие критерию обозначается как 1 (истина), а точное соответствие – 0 (ложь). Данный логический аргумент указывать необязательно, если таблица отсортирована от минимального к максимальному значению. Если таблица не отсортирована и аргумент опущен, это равносильно истине.
Важно! Искомое значение должно находиться слева (в первом столбце) от возвращаемого элемента.
В русифицированной версии Excel аргументы вводятся через знак «;», в англоязычной – через запятую.
Как работает функция?
Программа после поиска искомого значения переходит во второй столбец, чтобы извлечь возвращаемый элемент.
С одним условием
Рассмотрим функцию на простом примере поиска сотрудника по присвоенному ему коду. Таблицу нужно отсортировать в порядке возрастания.
- Кликнуть по ячейке справа от таблицы или под ней и вписать искомый номер.
- В ячейке рядом ввести функцию ВПР через знак «=», либо же использовать опцию «Вставить функцию» в разделе «Формулы». Проще использовать именно опцию – там есть подсказки, какие именно данные нужны.
- Задать параметры поиска, заполнив соответствующие поля. Интервальный просмотр в данном случае пропустим.
- Нажать «Enter» – и появится результат поиска.
Когда таблица не отсортирована и данные введены в хаотичном порядке, результат будет неправильный – программа найдет ближайшее соответствие («ИСТИНА»). Но можно не сортировать таблицу, а указать интервальный просмотр «ЛОЖЬ».
Чтобы найти другой элемент, следует просто изменить первый аргумент, и результат автоматически преобразится.
С несколькими условиями
Часто на практике требуется сравнить данные нескольких диапазонов и выбрать значение с учетом 2-х и более критериев. Здесь задействована также функция ЕСЛИ, которая отвечает как раз за условия.
Из таблицы нужно найти показатель выручки по конкретному менеджеру в определенный день:
- В первую выделенную ячейку вписать дату, во вторую – фамилию менеджера. В третьей ячейке будет происходить поиск.
- Кликнуть по пустой ячейке и ввести формулу =ВПР(G1;ЕСЛИ(C2:C12=G2;A2:D12;»»);4;0).
- Для подтверждения действия зажать комбинацию клавиш Ctrl+Shift+Enter. Нажатие на «Enter» не сработает в этом случае, потому что формула должна быть выполнена в массиве (об этом свидетельствуют скобки «<>», в которые взята вся формула).
Поиск по нескольким столбцам
Объем данных расширен, и нужно найти конкретное значение среди нескольких столбцов, просуммировав данные с помощью функции СУММ.
- Кликнуть по ячейке и ввести формулу =СУММ(ВПР(G1;A1:D12;;ЛОЖЬ)). Для третьего аргумента перечисление столбцов происходит в скобках «<>».
- Одновременно зажать клавиши Ctrl+Shift+Enter. В результате формула будет взята в фигурные скобки «<>».
Программа сравнивает данные в таблице и, как только определяет точное совпадение, суммирует их.
Важно! Самостоятельно писать фигурные скобки «<>» при вводе формулы нельзя, поскольку это не приведет к какому-либо результату.
Таким же способом можно найти среднее значение с помощью СРЗНАЧ: =СРЗНАЧ(ВПР(G1;A1:D12;;ЛОЖЬ)).
Сравнение двух таблиц
ВПР помогает сопоставить значения в таблицах.
Необходимо сравнить зарплату сотрудников за 2 месяца, для этого:
- В таблице с зарплатой за март добавить еще один столбец.
- Клацнуть по первой ячейке в столбце и написать функцию ВПР со следующими аргументами: =ВПР($A$2:$A$12;ссылка_на_новый_лист!$A$2:$B$12;2;ЛОЖЬ). То есть нужно выделить диапазон с фамилиями менеджеров и сделать ссылки (строки и столбца) неизменными с помощью знака «$», посмотреть его в таблице с новой зарплатой, взять данные из второго столбца новой зарплаты и подставить их в ячейку С2. В результате отобразится первый результат.
- При помощи маркера заполнения протянуть полученное значение вниз.
По желанию теперь можно найти численную и процентную разницу.
Поиск в выпадающем списке
Необходимо настроить функцию так, чтобы при выборе элемента из выпадающего списка отображалось его числовое значение.
Для создания раскрывающегося списка:
- Поставить курсор в ячейку, где он будет располагаться.
- Перейти в раздел книги «Данные» – «Проверка данных».
- В «Типе данных» выбрать «Список», задать диапазон (в нашем случае – фамилии менеджеров).
- Нажать «Ок». Отобразится список.
- В следующую ячейку вписать функцию ВПР. Первый аргумент – ссылка на раскрывающийся список, второй – диапазон таблицы, третий – номер столбца, четвертый – «ЛОЖЬ». В итоге получится следующее: =ВПР(E1;A1:B12;2;ЛОЖЬ). Нажать «Enter».
Меняется фамилия в списке – меняется и зарплата.
Перенос данных
Есть таблица с менеджерами и объемом их продаж. Во второй таблице значится сумма премии за продажу для каждого менеджера. Необходимо перенести данные в левую таблицу, чтобы подсчитать общую выручку (произведение объема продаж и премии за 1 продажу: =ПРОИЗВЕД(C2*D2)).
- Выделить первую ячейку с премией в левой таблице. Написать функцию с аргументами, сделать неизменными значения из второй таблицы, указать в третьем аргументе столбец 2, вместо «ЛОЖЬ» можно вписать 0: =ВПР(B2;$G$2:$H$12;2;0)
- Протянуть формулу вниз, чтобы заполнить все ячейки.
Ошибки
Когда пользователь ошибается при вводе данных или выборе диапазона, вместо результата отображаются различные ошибки: #Н/Д, #ЗНАЧ, #ССЫЛКА.
Ошибка #Н/Д появляется, если:
- Указанный диапазон не содержит искомый элемент.
- Искомый элемент меньше, чем минимальный в массиве.
- Задан точный поиск (аргумент «ЛОЖЬ» или 0), а искомого нет в диапазоне.
- Задан приблизительный поиск (аргумент «ИСТИНА» или 1), но данные не отсортированы по возрастанию.
- Разный формат (числовой и текстовый) у ячейки, откуда берется искомое, и ячейки с данными первого столбца.
- В коде есть пробелы или невидимые непечатаемые знаки.
- Используются значения времени или большие десятичные числа.
Во избежание ошибки #Н/Д, когда ВПР не находит значение, рекомендуется использовать следующую формулу: =ЕСЛИОШИБКА(ВПР(C2;A1:B12;2;ЛОЖЬ);0) – вместо 0 можно написать «не найдено».
Ошибка #ЗНАЧ появляется, если:
- В качестве номера столбца указано число 0.
- Длина первого аргумента превышает 255 знаков.
Ошибка #ССЫЛКА появляется, если третий аргумент больше количества столбцов в таблице.
Вместо заключения
В Excel есть улучшенная версия ВПР – функция ПРОСМОТРХ. Считается более простой и удобной в использовании, работает в любом направлении и возвращает точные совпадения по умолчанию.
При необходимости задействовать горизонтальный поиск элемента стоит воспользоваться функцией ГПР.
В программах OpenOffice и LibreOffice используется VLOOKUP, ее синтаксис =VLOOKUP(lookupvalue; datatable; columnindex; mode), а работает функция точно так же, как описано выше.
Источник: sysadmin-note.ru