Функция ВПР
Совет: Попробуйте использовать новую функцию ПРОСМОТРX , улучшенную версию функции ВЛОП, которая работает в любом направлении и по умолчанию возвращает точные совпадения, что упрощает и удобнее в использовании, чем предшественницу.
Если вам нужно найти что-то в таблице или диапазоне по строкам, используйте В ПРОСМОТР. Например, можно найти цену автомобильной части по номеру части или имя сотрудника на основе его ИД.
Совет: Ознакомьтесь с этими видеороликами с YouTube от Microsoft Creators, чтобы узнать больше о ВЛИО!
Самая простая функция ВПР означает следующее:
=ВРОТ.В.(Что вы хотите найти, где ее нужно найти, номер столбца в диапазоне, содержащего возвращаемую величину, возвращает приблизительное или точное совпадение, обозначенные как 1/ИСТИНА или 0/ЛОЖЬ).
Совет: Секрет функции ВПР состоит в организации данных таким образом, чтобы искомое значение (Фрукт) отображалось слева от возвращаемого значения, которое нужно найти (Количество).
Используйте функцию ВПР для поиска значения в таблице.
ВПР(искомое_значение, таблица, номер_столбца, [интервальный_просмотр])
=ВLOOKUP(A2;’Сведения о клиенте’! A:F;3;ЛОЖЬ)
Значение для поиска. Иного значения должно быть в первом столбце диапазона ячеек, который указан в table_array.
Например, если массив таблицы охватывает ячейки B2:D7, lookup_value должны быть в столбце B.
Искомое_значение может являться значением или ссылкой на ячейку.
Диапазон ячеек, в котором будет выполнен поиск искомого_значения и возвращаемого значения с помощью функции ВПР. Можно использовать именуемый диапазон или таблицу, а не ссылки на ячейки, а имена в аргументе.
Первый столбец в диапазоне ячеев должен содержать lookup_value . Диапазон ячеев также должен включать возвращаемую величину, которая требуется найти.
Номер столбца (начиная с 1 в левом большинстве столбцов table_array),содержащий возвращаемую величину.
Логическое значение, определяющее, какое совпадение должна найти функция ВПР, — приблизительное или точное.
Приблизительное совпадение: 1/ИСТИНА предполагает, что первый столбец в таблице отсортировали по алфавиту или по числу, а затем будут выполнять поиск ближайшего значения. Это способ по умолчанию, если не указан другой. Например, =ВКП(90;A1:B100;2;ИСТИНА).
Точное совпадение: 0/ЛОЖЬ ищет точное значение в первом столбце. Например, =ВКП("Кузнецов";A1:B100;2;ЛОЖЬ).
Начало работы
Для построения синтаксиса функции ВПР вам потребуется следующая информация:
Значение, которое вам нужно найти, то есть искомое значение.
Диапазон, в котором находится искомое значение. Помните, что для правильной работы функции ВПР искомое значение всегда должно находиться в первом столбце диапазона. Например, если искомое значение находится в ячейке C2, диапазон должен начинаться с C.
Номер столбца в диапазоне, содержащий возвращаемое значение. Например, если в качестве диапазона указать диапазон B2:D11, следует посчитать B первым столбцом, C — вторым и так далее.
При желании вы можете указать слово ИСТИНА, если вам достаточно приблизительного совпадения, или слово ЛОЖЬ, если вам требуется точное совпадение возвращаемого значения. Если вы ничего не указываете, по умолчанию всегда подразумевается вариант ИСТИНА, то есть приблизительное совпадение.
Теперь объедините все перечисленное выше аргументы следующим образом:
=ВЛОП(искомого значения; диапазон, содержащий искомые значения, номер столбца в диапазоне, содержащий возвращаемую величину, приблизительное совпадение (ИСТИНА) или Точное совпадение (ЛОЖЬ)).
Примеры
Вот несколько примеров использования функции ВПР.
Пример 1
Пример 2
Пример 3
Пример 4
Пример 5
Функцию ВЛОП можно использовать для объединения нескольких таблиц в одну, если одна из таблиц имеет поля, общие для всех остальных. Это особенно полезно, если вам нужно поделиться книгой с людьми, у которых есть более старые версии Excel, которые не поддерживают функции данных с несколькими таблицами в качестве источников данных, путем объединения источников в одну таблицу и изменения источника данных функции данных в новую таблицу, функцию данных можно использовать в более старых версиях Excel (при условии, что функция данных поддерживается более старой версией).
В этом столбце столбцы A–F и H имеют значения или формулы, которые используют только значения на этом сайте, а в остальных столбцах используется В., а для получения данных из других таблиц используются значения из столбцов A (код клиента) и B (Доверенность).
Скопируйте таблицу с общими полями на новый и придать ей имя.
Чтобы открыть диалоговое окно Управление отношениями, > в > управления отношениями нажмите кнопку Data > Data Tools (Управление отношениями).
Для каждой из указанных связей обратите внимание на следующее:
Поле, которое связывает таблицы (в скобки в диалоговом окне). Это первый lookup_value для формулы ВЛВП.
Имя связанной таблицы подытов. Это первый table_array в формуле ВЛИО.
Поле (столбец) в связанной таблице подытовки с данными, которые должны быть в новом столбце. Эта информация не отображается в диалоговом оке Управление связями. Чтобы узнать, какое поле нужно извлечь, необходимо посмотреть в связанной таблице подыска. Обратите внимание на номер столбца (A=1) — это col_index_num формуле.
Чтобы добавить поле в новую таблицу, введите формулу в первом пустом столбце с использованием сведений, собранных на шаге 3.
В нашем примере в столбце G для получения данных "Ставка счета" из четвертого столбца (col_index_num = 4) из таблицы "Доверенности" используется столбец "Доверенность" lookup_value(table_array) с формулой =В.В.ПРОСМОТР([@Attorney];tbl_Attorneys;4;ЛОЖЬ).
В формуле также можно использовать ссылку на ячейку и ссылку на диапазон. В нашем примере это будет =ВЛП(A2;’Защитники’! A:D,4;ЛОЖЬ).
Продолжайте добавлять поля, пока не будут добавлены все необходимые поля. При подготовке книги, содержащей функции данных, в которой используются несколько таблиц, измените источник данных функции на новую таблицу.
Exceltip
Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки
Многоразовое копирование формулы ВПР
Ничто так не раздражает, как ручная правка формул. При этом меня не покидает ощущение, что все это можно сделать более легким путем. Такое ощущение появляется, к примеру, когда вы редактируете формулу ВПР.
Сегодняшний пост посвящен формуле ВПР, описывающий многоразовое копирование без необходимости ручной правки.
В нашем примере, я пытаюсь вернуть определенную информацию по номеру продукта. У меня есть сводная таблица, где находится описание продукта, сегмент бизнеса и цена. Используем функцию ВПР.
На рисунке видно, что я использовал общепринятый подход в использовании формулы ВПР.
Но если я скопирую формулу в следующую ячейку, excel не изменил номер столбца, как если бы это была относительная ссылка.
Чтобы сослаться на разные части сводной таблицы, необходимо каждый раз менять номер столбца в формуле ВПР. К примеру, в поле Описание номер столбца должен быть 3-й, а в поле Бизнес сегмент — 4-й.
Многие из нас делают такую правку вручную. Может показаться, что ничего зазорного в этом нет, но когда таких столбцов больше 10, это становится утомительным, часто вызывая мысли о самоубийстве.
Решение 1: Использование дополнительных ячеек
Простым решением данного вопроса будет использование дополнительных ячеек. Как вы в видите, над каждой формулой ВПР я поместил значение номера столбца. Теперь, вместо ручного прописывания этого значения в каждой формуле =ВПР($A3;$H$3:$L$13;3;ЛОЖЬ), мы ссылаемся на дополнительную ячейку. Т.е. наша формула примет вид =ВПР($A3;$H$3:$L$13;C3;ЛОЖЬ).
Таким образом, номер столбца в формуле ВПР будет каждый раз исправляться, когда я буду копировать ее в соседнюю колонку.
Решение 2: Использование функции СТОЛБЕЦ()
Если вам не по вкусу первое решение, и вам требуется более элегантный метод, вы можете воспользоваться функцией СТОЛБЕЦ. Этот метод не требует использования дополнительных ячеек.
Для тех, кто не знает, функция СТОЛБЕЦ принимает в качестве аргумента адрес ячейки и возвращает номер столбца этой ячейки. К примеру, СТОЛБЕЦ(D1) вернет значение 4, так как колонка D имеет четвертый порядковый номер.
В нашем случае, мне необходимо указать 3-й номер столбца в сводной таблице. Поэтому вместо ручного коддинга, я использую СТОЛБЕЦ(C1).
При копировании формулы ВПР поперек столбцов, функция СТОЛБЕЦ автоматически сдвигается вместе с другими ссылками. Это позволяет копировать ВПР без того, чтобы корректировать наши ссылки вручную.
На этом все. Я уверен, что существуют другие, более продвинутые способы решения данной проблемы, но эти два метода, которые я использую в своей работе.
Вам также могут быть интересны следующие статьи
- Четыре способа использования ВПР с несколькими условиями
- Функция СЖПРОБЕЛЫ в Excel с примерами использования
- Несколько условий ЕСЛИ в Excel
- Что такое стандартное отклонение — использование функции СТАНДОТКЛОН для расчета стандартного отклонения в Excel
- Как расчитать дисперсию в Excel с помощью функции ДИСП.В
- Функция СУММПРОИЗВ — как использовать формулу СУММПРОИЗВ в Excel
- Функции НАИБОЛЬШИЙ и НАИМЕНЬШИЙ Excel
- Функция СУММЕСЛИМН в Excel
- Изучаем функцию СМЕЩ
- Изучаем формулу ЕСЛИ — 5 приемов Excel, о которых вы не знали
9 комментариев
хотел бы спросить. можно ли устроить «суммарный» ВПР. для примера: в таблице несколько раз встречается один и тот же элемент, в итоговую таблицу я должен занести сумму всех одинаковых элементов, получается сделать через сводную таблицу, но мне бы хотелось обойтись без неё.
В таких случаях пользуюсь формулой СУММЕСЛИМН()