Auditportal.ru

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

Как использовать подстановочный знак vlookup (~, *,?) Буквально в Excel?

Функция ВПР в экселе

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

VLOOKUP означает вертикальный просмотр. Эта функция является одной из наиболее часто используемых в Excel. Например, он позволяет легко находить и сопоставлять телефонные номера людей или организаций в электронной таблице по имени. Вы также можете сравнить цены на товары по названию. Эти и многие другие функции доступны с помощью функции БПФ. Он довольно прост в использовании.

Использование функции

Давайте рассмотрим структуру ДРП и аргументы, связанные с ней. Как и любая другая функция в Excel, она начинается со знака равенства (=). За ним следует имя функции и аргументы в скобках.

Аргументы функции

PVR состоит из четырех аргументов.

Искомое значение

Функция ищет значение в крайнем левом столбце и возвращает значение в той же строке указанного столбца.

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

Номер столбца

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

Интервальный просмотр

Последний аргумент – интервальный просмотр, здесь может быть 2 значения: 0 – ЛОЖЬ, 1 — ИСТИНА. отвечает за точный поиск (совпадения при просмотре сверху вниз). Если ничего не находит, то возвращается ошибка Н/Д (нет данных), 1приблизительный.

Рассмотрим пример работы этой функции.

Примеры использования

Использование фильтра

Первый простой пример: есть 2 таблицы. Один содержит товары и их идентификаторы. Во втором, используя фильтр ID, мы получим название продукта.

Вводим функцию

После знака равенства введите RVP, затем Enter и Fx для ввода аргументов.

Мастер настроек

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

Вводим искомые значения

Мы будем искать совпадения на основе искомых значений. В данном случае это ячейка E1.

Задаем диапазон

Второй аргумент — диапазон таблицы.

Делаем абсолютные ссылки

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

Вводим номер столбца

Возвращается номер столбца. В данном примере возвращается столбец товаров (столбец 2). Для точного поиска 4-м аргументом является .

Читайте так же:
Как защитить или заблокировать значения ячеек на основе цвета фона?

Нажмите OK после ввода всех значений.

Изменение наименования

Теперь название продукта будет меняться при изменении ID-номера в фильтре.

Теперь рассмотрим еще один пример.

Пример таблица

Теперь необходимо получить партию для каждого товара по критерию Количество.

Средняя партия должна составлять 200-300, для маленькой партии 100-200 и т.д.

Мастер функций

Желаемым значением в данном случае будет количество, выберите диапазон Критерии — Часть (блок F4). Колонка номер 2, вид диапазона в данном случае должен быть равен 1 (это даст ближайшее нижнее значение к искомому значению).

Готовый результат

Как видно из результирующей таблицы для количества, например, 110, партия произведена Маленькая (ближайшая меньше 100) и т.д. Сортируйте критерии от большего к меньшему, иначе ВПР не будет работать.

Функция (формула) «ВПР» в «Эксель» или как сравнить значения в столбцах.

ВПР в Эксель

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

Рассмотрим функцию «ВПР» в Excel.

Эта функция используется для последовательного сравнения информации в двух колонках (столбцах) сверху вниз. Аббревиатура «VPR» может быть интерпретирована как «Вертикальный вид».

Принцип действия функции «ВПР» следующий:

Вы задаете значение, которое должно быть найдено в ячейках указанного столбца. Функция ищет это значение сверху вниз, переходя от ячейки к ячейке. Когда функция находит значение, она присваивает его ячейке, в которой оно содержится, или ячейкам, непосредственно примыкающим к найденной ячейке, в зависимости от того, какая ячейка указана из найденных. Укажите «1», чтобы присвоить найденное значение.

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

Аргументы функции ВПР.

  • Установите курсор в ячейку, которой вы хотите присвоить значение с формулой с помощью функции «БПФ»;
  • Выберите функцию «БПФ» в Мастере функций;
  • В появившемся окне введите искомое значение или адрес ячейки, содержащей нужную информацию, в поле «Искомое значение».
  • В поле «Таблица» введите диапазон, из которого вы хотите выбрать значения; столбец, в котором вы хотите искать значения, должен быть самым левым;
  • В поле «Номер_столбца» введите порядковый номер столбца (начиная со столбца, содержащего значение), из которого вы хотите присвоить значение. Если указано число ‘1’, присваивается значение из первого столбца, т.е. искомое значение
  • Поле ‘Вид интервала’ задает количество символов, на которое может сбиться поиск. Это поле задает критерий неточности для поиска значений. Если вы установите вид интервала значений на ‘0’, то будет выполняться поиск только полных совпадений значений, т.е. без отклонений от искомого значения.
Читайте так же:
Как найти и найти именованные диапазоны в Excel?

Файл с примером работы формулы с «ВПР»

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

Функция ВПР в Excel

V LOOKUP — что означает Vertical Browsing — так называется эта функция в английской версии. Существует также функция VLOOKUP, которая ориентирована на горизонтальный просмотр. Эта функция часто используется для сравнения столбцов между двумя таблицами или для извлечения данных из одной таблицы в другую.

Давайте рассмотрим несколько простых примеров, чтобы лучше понять особенности работы с этой функцией.

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

Функция ВПР в Excel

Используя таблицу «Прайс-лист» в качестве отправной точки, мы можем получить цену за кг товара из таблицы «Заказы». Цены из нижней таблицы должны быть подставлены в верхнюю таблицу в отношении каждого товара.

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

Функция ВПР в Excel

Появится подсказка, в соответствии с которой будут заданы аргументы. Если расшифровать VPR с технического языка на нормальный, то функция VLOOKUP будет искать выбранный товар из таблицы «Заказы» в крайнем левом столбце таблицы «Прайс-лист» и, если найдет этот товар, отобразит его ценовое значение, которое находится в той же строке, что и найденный товар. Вкратце это выглядит так, как показано на скриншоте ниже.

Функция ВПР в Excel

Функция VPR имеет четыре аргумента. Первый аргумент, который мы вводим в формулу, — это искомое значение, то есть цена, которую мы хотим найти во второй таблице прайс-листа. После того как вы набрали имя функции и добавили начальную скобку, нажмите на нужную ячейку и добавьте ее в качестве аргумента. В моем случае это ячейка «E4» со значением «Яблоки». Затем добавьте точку с запятой в качестве разделителя.

Читайте так же:
Как изменить значения в диапазоне ячеек в Excel?

Функция ВПР в Excel

Второй аргумент — это таблица, в которой мы ищем значение и находим цену. В данном примере нужно выбрать только таблицу «Прайс-лист» и будет лучше, если вы не будете выделять лишние ячейки (заголовок, пустые ячейки и просто другую лишнюю информацию). В конце не забудьте поставить точку с запятой.

Функция ВПР в Excel

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

Функция ВПР в Excel

В третьем аргументе мы указываем номер столбца в таблице «Прайс-лист», из которого хотим извлечь значение. В нашем примере все просто. Значение находится в первом столбце; цена извлекается из второго столбца. Мы ставим цифру 2, за которой следует точка с запятой.

Функция ВПР в Excel

Последний параметр называется «Вид интервала». — это логический аргумент, который может принимать только 2 значения: 0 или 1 (вкл./выкл.). С помощью этого значения мы можем определить, точно ли, номер 0, или приблизительно, номер 1, мы ищем элемент в прейскуранте. Для текстовых имен лучше использовать точный поиск (число 0), поскольку приблизительный поиск работает более или менее точно для ячеек с числами. Введите значение интервала как 0 и закройте скобки. Затем нажмите Enter, чтобы формула заработала. Мы получаем ячейку с ценой, взятой из обзора прайс-листа.

Функция ВПР в Excel

Функция ВПР в Excel

В графе «Общая стоимость» пишем простую формулу для умножения веса партии на цену за кг.

Функция ВПР в Excel

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

Читайте так же:
Как изменить цвета по точкам для диаграммы в Excel?

Функция ВПР в Excel

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

Функция ВПР в Excel

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

Функция ВПР в Excel

Еще одним полезным применением BPP является его использование для сравнения двух таблиц с целью выявления сходств и различий. В качестве примера, если удалить некоторые строки из одной из таблиц, вы больше не увидите полного совпадения данных, а в некоторых ячейках появится #N/D. Согласно надписи, цены из таблицы с заказами не были найдены в таблице с прайс-листами, что вызвало ошибку.

Функция ВПР в Excel

Теперь рассмотрим ситуацию, в которой интервальное представление будет использоваться не точно, с числом 0, а приближенно, с числом 1. Хотя этот метод используется редко, все же есть сценарии, в которых он может понадобиться.

Давайте вернемся к нашей таблице заказов и попробуем определить размер партии по весу. У нас будет таблица с заказами и таблица с параметрами партии.

Функция ВПР в Excel

К этому добавляется критерий «от и до», который говорит нам о размере партии. Учитывая ее вес, мы можем ориентироваться в примере. Excel поймет, что партия в 15 кг означает небольшую партию, например.

Давайте посмотрим, как это работает. Начиная с VRP, в конце формулы ставим 1 вместо 0. В аргументе, где требуется указать таблицу с ячейками, мы не будем указывать столбец с параметром «От и до», так как Excel не поймет такую запись. Вместо этого вводится поле критерия, определяющее диапазон весов. На последнем этапе нажимаем Enter и растягиваем на все ячейки.

Читайте так же:
Как использовать флажок, чтобы скрыть / показать строки или столбцы в Excel?

Функция ВПР в Excel

Функция ВПР в Excel

Чтобы понять, что произошло, нужно объяснить некоторые особенности работы. Мы видим, что при определенном весе функция вытягивала определенную партию со второго стола. Чтобы точнее понять логику, рассмотрим вес, например, 15 кг. В таблице размера партии такого критерия не существует. Однако, когда представление интервала установлено в 1, функция вернет ближайшее меньшее значение. VRP извлечет из таблицы значение, соответствующее критерию 10, для партии весом 15 кг. Исходя из веса партии, критерий 30 вычислит значение, наиболее близкое к 47 кг.

Функция ВПР в Excel

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

Функция ВПР в Excel

На этом функция завершена. На этих примерах, я надеюсь, вы узнали, как использовать функцию БПФ. Пожалуйста, сообщите нам, если у вас возникнут какие-либо трудности, в комментариях ниже.

Не забудьте поделиться ссылкой на эту статью ⇒.

Столбцы в Excel цифрами

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

  • 01.12.2015
  • Просмотров: 6299
  • Excel

Как объединить текст в ячейках в Excel (Функция Сцепить)

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

  • 18.11.2015
  • Просмотров: 66875
  • Excel
  • Видеоурок

Сортировка по нескольким столбцам в Excel

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

  • 07.03.2019
  • Просмотров: 11498
  • Excel

Специальная вставка в Excel

В этом уроке я расскажу, что такое специальная вставка в Excel и как ее использовать.

  • 20.11.2015
  • Просмотров: 64284
  • Excel

Как свернуть Outlook в трей

Вот как свернуть Outlook в трей. Многие начинающие пользователи Microsoft Outlook сталкиваются с проблемой сворачивания программ, которые остаются на панели задач после их сворачивания.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector