Функция ВПР в Экселе – быстрый перенос данных
Самое простое использование функции ВПР — это быстрый перенос данных из одной таблицы в другую.
Например, у вас есть большой прайс-лист на 500 позиций и запрос от покупателя, например, на 50 позиций (в реальности и цена, и запрос могут быть намного выше, но принцип от этого не меняется).
Вам нужно быстро найти цены на эти 50 позиций. Конечно, можно искать каждый товар отдельно в большом прайс-листе и тратить на это 30-60 минут, а можно сделать это менее чем за минуту с помощью функции ВПР.
Итак, у нас есть 500 позиций в прайс-листе. Позиции обозначаются следующим образом: буквы обозначают тип позиции, а цифры обозначают изменение.
Например, «Chair_1» и «Chair_21» — это два совершенно разных стула.
Цены в прайс-листе, например, и вряд ли связаны с реальными ценами.
Определим задачу.
В ООО «ЮкеА» поступил запрос от «Петрович».
Петрович человек простой, любит все делать быстро, но не очень четко. Поэтому для его запросов характерна определенная путаница позиций.
Впрочем, нас это не пугает, во-первых, у нас CDF, а во-вторых, ничего подобного мы не видели.
Петрович требует, чтобы мы очень быстро указали цены в его запросе. Он намерен ждать максимум 5 минут. Ведь другие поставщики уже завалили его предложениями.
Мы не хотим терять такого клиента, и практически сразу открываем прайс-лист:
Получается, что у нас должно быть открыто два файла (две книги в Excel). Просьба от Петровича и Прайса.
Это именно то, что нужно, осталось только перенести цены из прайса в запрос.
Для этого перейдите в таблицу запросов и в первой ячейке столбца «Цены» (D4) введите «=vpr» и дважды щелкните значок функции:
Сразу после этого в строке формул нужно поставить курсор внутрь метки ВПР и нажать Fx, появится окно с аргументами функции ВПР:
В аргументах функции вы сообщаете Excel, что и где искать:
Желаемое значение – это значение (в данном случае название), цена, которую вы хотите найти в прайс-листе. Соответственно, нажмите на первую ячейку в столбце «Имя».
Тогда сразу переходите в «Цены»:
Теперь заполните следующие поля в аргументах функции:
Таблица — выберите столбцы, содержащие искомые товары и цены, чтобы товары были крайним левым столбцом.
Так работает функция ВПР — она ищет нужные значения в крайнем левом столбце (для ВПР это столбец №1). Когда функция ВПР находит искомое значение, она начинает искать вправо, в столбец, указанный вами в поле «Номер столбца».
Там необходимо указать столбец, содержащий данные, необходимые для перевода. В нашем случае это цены, а в нашем случае это столбец номер два, относительно указанной вами в аргументах таблицы.
Интервальный поиск — установите на 0. Ноль означает точное совпадение.
После заполнения аргументов функции нажмите «ОК» и если все сделано правильно, в графе «Цена» появится цена (файл «Запрос от Петровича.
Вам нужно растянуть цены на оставшиеся ячейки:
Коллеги, вот и все, вы освоили функцию ВПР.
Очень важное замечание!
Обратите внимание, что мы сейчас работали в двух разных файлах (книгах).
Когда работа ведется в двух разных книгах, Excel автоматически блокирует таблицу в функции ОТКРЫТЬ:
Он делает это с помощью знака $, который ставит перед столбцами и строками в таблице.
Это предотвратит перемещение формулы при перетаскивании ее вниз. Это очень важно при работе в пределах одного листа или книги (в этом случае Excel не замораживает ячейки автоматически).
Посмотрим, что будет, если расширить формулу «без фиксации»:
Обратите внимание, что для первой ячейки все нормально и диапазон B3:C502 точно соответствует той таблице, которую мы выбрали для поиска данных, но (без фиксации) это будет не всегда, так как формула ВПР «утягивается» , таблица тоже будет меняться, пока однажды мы не увидим эту странную надпись #N/A:
#Н/Д означает, что функция ВПР не смогла найти цену Chair_13 в прайс-листе, и это неудивительно, ведь диапазон таблицы в формуле ВПР ушел ниже этого значения:
Поэтому, если не хотите уходить, зафиксируйте диапазон.
Очень важное замечание №2
Как вы заметили, формулы относятся к определенным ячейкам, другими словами, существует связь между формулами и исходными данными. Стоит изменить первые данные, и значения в формулах сразу изменятся.
Особенно остро это проявляется в ВПР. Если вы вдруг забудетесь и добавите лишний столбец «не туда» в исходной табличке, формула ВПР вернет совершенно неожиданные значения.
Поэтому, если вам не нужна связь между таблицами, рекомендую превратить формулы в данные.
Для этого выделите столбец формул, нажмите Ctrl+C и выберите «Вставить» — «Вставить значения» в левом верхнем углу».
Для тех, кто не любит изучать изображения, я записал короткое видео, показывающее все, что мы сказали выше (кроме вставки значений):
Особенности использования формулы ВПР в Excel
Функция ВПР имеет свои особенности, о которых следует знать.
1. Первую функцию можно считать общей для функций, которые используются для многих ячеек, написав формулу в одной из них, а затем скопировав ее в остальные. Здесь приходится учитывать относительность и абсолютность отсылок. Особенно в ВПР критерий (первое поле) должен иметь относительную ссылку (без знака $), так как каждая ячейка имеет свой собственный критерий. А вот поле «Таблица» должно иметь абсолютную ссылку (адрес поля пишется через $). Если этого не сделать, область будет «уходить вниз» при копировании формулы, и многие значения просто не будут найдены, так как искать будет негде.
2. Номер столбца, введенный в третье поле «Номер_столбца» при использовании мастера функций, должен считаться, начиная с самого критерия.
3. Функция ВПР из диапазона искомых данных возвращает первое значение сверху. Это значит, что если во второй таблице, куда мы пытаемся «подтянуть» какие-то данные, есть несколько ячеек с одинаковыми критериями, ВПР захватит первое значение сверху в пределах выбранного диапазона. Это следует помнить. Например, если мы хотим прибавить количество из другой таблицы к цене товара, а там этот товар встречается несколько раз (в нескольких строках), то к цене будет прибавлено первое сверху количество.
4. Должен быть установлен последний параметр в формуле, равный 0 (ноль). В противном случае формула может работать неправильно.
5. После использования ВПР лучше сразу удалить саму формулу, оставив только полученные значения. Это делается очень легко. Выделяем область с полученными значениями, нажимаем «копировать» и приклеиваем значения на то же место с помощью специального клея. Если таблицы расположены в разных книгах Excel, очень удобно разорвать внешние ссылки (и оставить вместо них только значения) с помощью специальной команды, расположенной по пути Данные → Изменить ссылки.
После вызова функции разрыва внешних ссылок появится диалоговое окно, в котором нужно нажать кнопку «Разорвать ссылку», а затем «Закрыть».
Это удалит все внешние ссылки сразу.
Как перемещать данные с помощью ВПР?
Рассмотрим пример на практике. У нас есть таблица, в которой прописаны партии заказанного товара (она выделена зеленым цветом). Справа находится прайс-лист, где указаны цены каждого товара (выделены синим цветом). Нам нужно перенести ценовые данные из правой таблицы в левую для расчета стоимости каждого лота. Вручную это делать долго, поэтому воспользуемся функцией Вертикальный вид.
В ячейке D3 необходимо сложить цену гречки из правой таблицы. Пишем =ВПР и заполняем аргументы.
Искомым значением будет гречка из ячейки B3. Важно проставить именно номер ячейки, а не слово «гречка», чтобы потом можно было перетащить формулу вниз и автоматически получить остальные значения.
Таблица — выбирайте цену без потолка. Они только называют предметы и их цены. Зафиксируем эту матрицу клавишей F4, чтобы она не менялась при перетаскивании формулы.
Номер столбца — в нашем случае это номер 2, потому что нужные нам данные (цена) находятся во втором столбце выбранной таблицы (цена).
Отображение интервала — установите 0, потому что нам нужны точные значения, а не приближения.
Мы видим, что цена на гречку переместилась из правого стола вверх в левый.
Стягиваем формулу вниз и визуально проверяем часть продуктов, чтобы понять, что все сделано правильно.
Скачать пример перемещения таблицы с помощью ВПР
Итак, с помощью элементарных операций можно заменить значения из одной таблицы в другую. Важно помнить, что функция вертикального просмотра работает только в том случае, если таблица, из которой извлекаются данные, находится справа. В противном случае вы должны переместить его или использовать команды ИНДЕКС и ПОИСКПОЗ. Освоив эти две функции, вы сможете реализовывать гораздо более сложные решения, чем возможности, предлагаемые функцией ВПР или ГПР.
Синтаксис ВПР
Заказанный товар (назовем его ПРИМЕР. С помощью специальной почты. По найденной информации удобен для тех способ TheArk через файлообменник, который есть у каждого поставщика.
будет представлен в январе
- это технически возможно сделать очень легко И также нет
- с функцией суммы ваша помощь сделана и вам не сложно, 150 позиций и ответ можно пометить зеленым цветом). Правильная заметка. Создадим новые файлы Теперь попробуем выделить пункт
- в качестве необходимой информации поставьте такие формулы: Добрый день, друзья.
- этот пользователь, ИМХО, иначе придется копировать Книги2, из которого написать формулу Как перемещать данные с помощью ВПР?
Как создать функцию ВПР в Excel
Необходимая последовательность значений в функции называется синтаксисом. Обычно функция начинается со знака равенства «=», за которым следует имя функции и аргументы в скобках.
Пишем формулу в столбце цены (С2). Это можно сделать двумя способами:
- Выберите ячейку и введите функцию.
- Выберите ячейку → нажмите Fx (Shift + F3) → выберите вкладку «Ссылки и массивы» → выберите функцию ВПР → нажмите «ОК».
После этого открывается окно, где можно заполнить ячейки в аргументах формулы.
Аргументы функции ВПР
Теперь разберемся, что и куда писать.
Со знаком равенства «=» и названием «ВПР» все готово. Поговорим об аргументах. Они пишутся в круглых скобках через точку с запятой или заполняются ячейками окна функции. Формула ВПР имеет 4 аргумента: значение поиска, таблица, номер столбца и поиск диапазона.
Искомое значение — это имя ячейки, из которой мы будем «вытягивать» данные. Формула ВПР ищет полное или частичное совпадение в другой таблице, из которой она получает информацию.
В нашем случае выбираем ячейку «А2», в ней указано название товара. ВПР возьмет это имя и будет искать аналогичную ячейку в другой таблице с прайс-листом.
Таблица — это ряд ячеек, из которых мы хотим «подтянуть» данные для нужного значения. Мы используем абсолютные ссылки в этом аргументе. Это означает, что в формуле таблица будет иметь вид «$G$2:$H$11» вместо «G2:H11». Вы можете ввести символы «$» вручную или выбрать «G2:H11» внутри формулы и нажать F4. Если этого не сделать, таблица не будет зафиксирована в формуле и изменится при копировании.
В нашем случае это таблица с прайсом. Формула будет искать в нем совпадение с той ячейкой, которая была указана в первом аргументе формулы — А2 (Кофе). Нажмите F4 и сделайте ссылку абсолютной.
Номер столбца — это столбец в таблице, из которого должны быть извлечены данные. Именно из него мы и будем «вытягивать» результат.
- Формула сканирует таблицу по вертикали.
- Находит совпадение в крайнем левом столбце с искомым значением.
- Глядя на столбец напротив, порядок, который мы указываем в этом аргументе.
- Отправляет данные в ячейку с формулой.
В нашем случае это столбец с ценой продукции в прайс-листе. Формула ищет нужное значение ячейки А2 (Кофе) в первой колонке прайс-листа и «подтягивает» данные из второй колонки (потому что мы указали цифру 2) в ячейку с формулой.
Интервальный поиск — это параметр, который может принимать 2 значения: «истина» или «ложь». Истинно указывается в формуле цифрой 1 и означает примерное совпадение с искомым значением. False обозначается цифрой 0 и подразумевает точное совпадение. Приблизительный поиск и «истинные» критерии обычно используются при работе с числами, а точные и «ложные» — при работе с именами.
В нашем случае искомое значение — это текстовое имя. Поэтому используем точный поиск — ставим цифру 0 и закрываем скобки.
Читайте также: Настройка статического IP адреса на роутере для локальных устройств
Автозаполнение
В конце продлеваем формулу до конца, в результате чего происходит автодополнение.
Для правильной работы функции ВПР во время автозаполнения искомое значение должно быть относительной ссылкой, а таблица должна быть абсолютной.
- В нашем случае искомое значение равно A2. Это относительная ссылка на ячейку, поскольку в ней нет символов «$». Из-за этого ссылка на искомое значение меняется относительно каждой строки, когда автозаполнение происходит в других ячейках: A2 → A3 →… → A11. Это удобно, когда вам нужно повторить формулу на нескольких строках, потому что вам не придется вводить ее заново.
- Таблица фиксируется абсолютной ссылкой «$G$2:$H$11». Это означает, что ссылки на ячейки не изменяются во время автозаполнения. Таким образом, расчет будет правильным каждый раз и на основе таблицы.
ВПР и приблизительный интервальный просмотр
В предыдущем примере мы извлекли значения из таблицы, используя поиск с точным интервалом. Подходит для работы с титрами. Теперь давайте рассмотрим ситуацию, когда может потребоваться приблизительное отображение интервала.
Задача. Товар был доставлен в магазин. Вы должны назначить каждому продукту размер партии в зависимости от количества.
Товары те же, что и в первом примере, но задача изменилась: нужно привязать формулу не к наименованию, а к количеству
Решение. Заполняем формулу ВПР в ячейку «Пакет», как показано в предыдущем примере.
Разница в том, что теперь искомое значение является числом, а интервальный поиск — истинным, что означает приблизительный поиск. Оказывается, это результат:
Что случилось? Аргумент поиска интервала имеет значение 1, что означает, что формула ВПР ищет в таблице ближайшее меньшее значение поиска.
В нашем случае количество товара «Кофе» равно 380. ВПР принимает это число за искомое значение, после чего ищет ближайшее меньшее число в соседней таблице — число 300. В конце функция «вытягивает вверх» данные из противоположного столбца («Большой»). Если количество товара «Кофе» = 340 — это «крупная партия». Важно, чтобы крайний левый столбец в таблице, указанной в формуле, был отсортирован по возрастанию. В противном случае ВПР работать не будет.
Значения и данные во второй таблице отсортированы по убыванию — ВПР не работает