Vpr excel: Краткий справочник: функция ВПР — Служба поддержки Майкрософт

Вся суть функции ВПР в Excel (для начинающих пользователей)


Автор Амина С. На чтение 11 мин Опубликовано

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

Содержание

  1. Синтаксис и особенности применения функции ВПР
  2. Экспресс-сравнение двух диапазонов
  3. Использование нескольких условий для формулы ВПР
  4. ВПРи выпадающий список
  5. Почему функция не работает
  6. Нужно точное совпадение
  7. Необходима фиксация ссылок на таблицу
  8. Вставлена колонка
  9. Увеличение размеров таблицы
  10. Функция не умеет анализировать данные слева
  11. Дублирование данных
  12. Выводы

Синтаксис и особенности применения функции ВПР

Предположим, вы заведующий складом, в котором хранятся разные материалы, такие как упаковки и контейнеры для хранения разных предметов. И вам завезли продукцию в конкретном количестве, и оно задается в Excel.

1

В другой же таблице приводится цена каждого из материалов.

2

Наша задача – определить, сколько стоит каждый из поступивших на склад товаров. Чтобы достичь этой цели, необходимо записать в первой таблице стоимость из второй. После этого умножить одно значение на другое. Так можно определить искомое.

Если говорить более точно, последовательность действий следующая:

  1. Приводим внешний вид таблицы в нужный нам вид путем вставки двух колонок, которые называются «Цена» и «Стоимость/Сумма». При этом нужно применить к ячейкам денежный формат. 
  2. Нажимаем на ячейку, являющуюся первой в нашей колонке «Цена». В случае с нами она имеет адрес D2. С использованием мастера функций пользователь всегда может найти ВПР в категории «Ссылки и массивы», независимо от версии Excel. А для вызова мастера функций есть два метода. Первый – это нажать на кнопку fx рядом со строкой ввода формулы. Второй же – комбинация клавиш SHIFT + F3. После того, как нужная нам функция будет выбрана, надо нажать на клавишу ОК, чтобы подтвердить свои действия. Есть еще один способ вызова этой функции. Нужно перейти на вкладку «Формулы» и там найти тот же пункт «Ссылки и массивы».
    3
  3. Далее нам нужно настроить функцию, введя в нее свои параметры. Для этого появится специальное окошко, в котором приведено несколько аргументов, в которые пользователь может ввести собственные значения:
    • Искомое значение. В случае с нашей таблицей им выступает перечень наименований товара, то бишь, первая колонка. Именно эта информация и должна будет искаться во второй таблице.
      4
    • Таблица. Это набор ячеек, в которых будет осуществляться поиск. В данном примере это вторая таблица с прайс-листом. Осуществляем переход на нее и выбираем необходимые значения. 
      5

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

    • Номер столбца. В этом аргументе мы пишем цифру два.
    • Интервальный просмотр. Этот параметр нужен, если ищутся только приблизительные данные. Этот аргумент может принимать два значения «Истина» и «Ложь». Мы запишем второй вариант, поскольку нам требуется точная информация.
      6
  4. После этого нажимаем кнопку «ОК».
  5. Далее функция размножается на всю колонку, воспользовавшись маркером автозаполнения, потянув за правый нижний угол ячейки по направлению вниз. 

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

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

Как это сделать? Можно применить «Специальную вставку». Последовательность действий следующая:

  1. Выделяем нужную колонку и делаем правый клик мыши.
  2. Копируем колонку.
  3. Оставляем выделение, опять делаем правый клик мыши и нажимаем «Специальная вставка», после чего появится меню, в котором нужно установить радиокнопку возле пункта «Значения».

В самом конце нужно подтвердить свои действия с помощью кнопки «ОК».

7

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

Экспресс-сравнение двух диапазонов

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

8

А вот и простая инструкция, как это сделать:

  1. Создать столбец «Новая цена» в старом прайсе.
    9
  2. Делаем клик по первой ячейке и вставляем функцию ВПР описанным выше способом (через кнопку fx), поскольку он наиболее удобный для новичка. По мере обретения профессионализма можно вводить формулу вручную. В нашем случае она будет выглядеть следующим образом. =ВПР($A$2:$A$15;’новый прайс’!$A$2:$B$15;2;ЛОЖЬ). Простыми словами, нам нужно сравнить диапазон А2:А15 в двух прайсах. После этого вставить новую информацию в старый в колонку «Новая цена».
    10

Дальше информация о ценах сопоставляется в ручном режиме или с помощью логических операторов Excel. Также можно применить условное форматирование и выделить красным цветом те строки, в которых цена изменилась. Но это уже уровень профессионала. 

Использование нескольких условий для формулы

ВПР

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

Вот небольшая таблица для наглядности.

11

Допустим, перед нами стоит задача определить, по какой стоимости был завезен гофрированный картон от производителя «ОАО «Восток». В этом случае нам требуется два условия:

  1. Материал.
  2. Производитель.

Но это еще не все, потому что каждый производитель завозит сразу несколько товаров. Как можно выкрутиться в этой ситуации? А вот, как:

  1. К таблице присоединяется крайний левый столбец, чтобы поставщики и материалы были в одной группе.
    12
  2. Критерии также нужно объединить.
    13
  3. Курсор устанавливается в требуемом месте, и в скобках указываются аргументы функции (или же через соответствующее диалоговое окно). =ВПР(I6;$A$2:$D$15;4;ЛОЖЬ). После этого Excel определит необходимую стоимость.
    14

ВПР и выпадающий список

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

Сперва нужно создать сам раскрывающийся список:

  1. Кликнуть левой кнопкой мыши по ячейке E2, тем самым выделив ее.  
  2. Переместиться на вкладку «Данные», и там найти пункт «Проверка данных».
    15
  3. Указать источник информации. В качестве него у нас используются названия товаров. Сам тип данных выставляем – список.
    16

После того, как мы нажмем кнопку «ОК», появится выпадающий список.

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

  1. Нажимаем на кнопку fx, что позволит открыть мастер функций. В этом диалоговом окне выбирается функция ВПР.
  2. В качестве первого аргумента указываем ту ячейку, в которой содержится выпадающий список. В качестве второго – диапазон с наименованиями продукции и ее стоимостью. Столбец второй. Функция, в результате, обретает такой вид. =ВПР(E8;A2:B16;2;ЛОЖЬ)
  3. После нажатия клавиши «ВВОД» получаем необходимый результат.
    17

После каждой коррекции продукции изменяется и цена.

18

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

Почему функция не работает

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

Нужно точное совпадение

В последнем аргументе «Интервальный просмотр» нет острой необходимости, но важно понимать, что значение по умолчанию – ИСТИНА. Следовательно, чтобы функция без этого аргумента работала правильно, значения должны быть отсортированы по возрастанию.

Поэтому если требуется уникальное значение, то нужно обязательно указывать последний аргумент со значением ЛОЖЬ.

Необходима фиксация ссылок на таблицу

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

Очень хорошо это видно на примере ниже. Здесь были введены неверные диапазоны, и из-за этого функция не хочет работать.

19

Чтобы решить эту проблему, достаточно просто нажать на клавишу F4, чтобы зафиксировать адрес ссылки.

Простыми словами, формула должна обрести следующий вид.

=ВПР(($H$3;$B$3:$F$11;4;ЛОЖЬ)

Вставлена колонка

Для чего нужен аргумент «номер столбца»? Для того, чтобы задать функции, какие именно данные должны быть извлечены. 

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

20

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

Но так бывает не всегда. Тогда на помощь придет второе решение. Мы знаем, что в качестве аргумента функции может использоваться другая функция. Вот это и решение. Нужно просто использовать функцию ПОИСКПОЗ, которая возвращает правильный номер столбца. 

Увеличение размеров таблицы

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

21

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

Функция не умеет анализировать данные слева

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

Решение этой проблемы находится вообще вне плоскости функции ВПР. Простыми словами, ее вообще не нужно использовать. В качестве альтернативы, не имеющей такого ограничения, можно использовать сочетание функций ИНДЕКС и ПОИСКПОЗ. 

Дублирование данных

Еще один недостаток функции в том, что она умеет извлекать лишь первое найденное значение, нужно удалить дубликаты с помощью кнопки «Удалить дубликаты», располагаемой на вкладке «Данные».

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

Выводы

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

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

Оцените качество статьи. Нам важно ваше мнение:

Усовершенствуем функцию ВПР в Excel / Хабр

Прочтение публикации Упрощаем бинарный поиск в Excel сподвигло на дополнительное усовершенствование функции ВПР по сравнению с приведенным в статье.

Что не было учтено, и что хотелось бы добавить:

1. Универсальность, т.е. возможность вызывать функцию как для отсортированного массива, так и для неотсортированного.

2. Исключить необходимость два раза вызывать функцию бинарного поиска (ВПР).

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

4. Сделать защиту от дурака — проверять передаваемые аргументы на корректность.

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

' VPR - улучшенная версия функций ВПР и ГПР (VLookup & HLookup)
' key - искомое значение (индекс)
' a - массив для поиска индекса
' b - массив такой же формы, для выдачи значения
' Ordered - указывает тип упорядочения массива: 1 - по возрастанию, 0 - не упорядочен, -1 - упорядочен по убыванию
' NotStrict - указывает, нужно точное или приблизительное значение: False - точное, True - достаточно приблизительного. 
' Если массив не упорядочен (Ordered = 0), то всегда возвращается точное значение
Function VPR(key As Variant, ByRef a As Range, ByRef b As Range, Optional Ordered As Integer = 0, Optional NotStrict As Boolean = False) As Variant
    ' проверяем корректность аргументов a и b - должны быть линейными, из одной области и 
    ' с одинаковым ненулевым количеством элементов
    If (b.Areas.Count <> 1) Or ((b.Columns.Count > 1) And (b.Rows.Count > 1)) Then
        VPR = CDbl("")
        Exit Function
    End If
    If (a.Areas.Count <> 1) Or ((a.Columns.Count > 1) And (a.Rows.Count > 1)) Then
        VPR = CDbl("")
        Exit Function
    End If
    If (a.Count <> b.Count) Or (a.Count < 1) Then
        VPR = CDbl("")
        Exit Function
    End If
    If Ordered = 0 Then
        NotStrict = False
    End If
    Dim index As Long
    index = Application.WorksheetFunction.Match(key, a, Ordered)
    If (Not NotStrict) And (a(index).value <> key) Then
        VPR = CDbl("")
    Else
        VPR = b(index). value
    End If
End Function

Дополнительные плюшки

1. Возможность поиска в массиве, отсортированном по убыванию (Ordered = -1).
2. Функция позволяет делать поиск если любой (или оба) из аргументов a и b являются горизонтальными рядами (т.е. обобщает и функцию горизонтального просмотра ГПР).

Комментарии

1. Вызов CDbl(«») нужен для генерации ошибки (выдача #ЗНАЧ#).
2. Используется не ВПР, а функция Match (русский аналог — ПОИСКПОЗ).

Спасибо за внимание!

Research Instrumentation Fund – Вице-президент по исследованиям

Совместные средства: Соответствующие средства необходимы для рассмотрения заявки на получение награды RIF. Соответствующие средства являются показателем поддержки со стороны факультета/колледжа заявителя и/или пользователей оборудования и демонстрируют важность приобретения для тех, кто поддерживает заявку. Требуется минимум 30% в соответствующих фондах, за исключением хорошо документированных и исключительных обстоятельств. В заявке должен быть указан источник соответствующих средств. Непредоставление суммы и информации об источнике для всех соответствующих фондов может негативно повлиять на заявку RIF. Пожалуйста, обратите внимание, что 30% соответствующих средств — это только минимальная сумма. Запросы на большие суммы должны по возможности учитывать средства, превышающие 30%. Заявки с общей суммой запросов VPR, превышающей 250 000 долларов США, вряд ли будут финансироваться без значимого обоснования и исключительно сильной демонстрации поддержки.

Документация по лучшей цене:  Офис VPR настоятельно рекомендует преподавателям и активистам добиваться наилучшей цены на оборудование и предоставлять документацию об этих переговорах в поддержку своей заявки. Такие скидки будут учитываться при оценке заявки RIF. Однако производственные скидки и пожертвования в натуральной форме не считаются соответствующими фондами.
Инструментарий для обучения/обучения: средства RIF возникают за счет возмещения косвенных затрат по исследовательским контрактам и грантам и, следовательно, предназначены исключительно для исследований, а не для целей обучения/обучения. Если запрошенное оборудование будет также использоваться для обучения/обучения, необходимо предоставить часть использования для обучения/обучения и определить финансирование этой части приобретения, обслуживания и использования. Долевые фонды для целей обучения/обучения не зависят от долевых фондов для исследовательских целей.

Проверка оборудования: RIF предназначен для предоставления нового исследовательского оборудования ИЛИ для замены/модернизации существующих исследовательских инструментов. Пожалуйста, убедитесь, что аналогичное оборудование в настоящее время недоступно в кампусе с помощью Поиска оборудования. Если подобное оборудование доступно, заявителю необходимо будет предоставить обоснованное обоснование необходимости дополнительного оборудования. Заявки RIF, в которых не представлено убедительное обоснование потребности в оборудовании, не будут рассматриваться для рассмотрения.

Запрещенное оборудование:  Программа RIF не может финансировать компьютер или оборудование для сбора/хранения данных. Из-за глобальной нехватки гелия UHP программа RIF на 23 финансовый год не может финансировать какое-либо новое исследовательское оборудование, для работы которого требуется гелий UHP.

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

Запросы на повторную подачу RIF:  Повторно поданные предложения должны включать пошаговый ответ на все предыдущие комментарии рецензента с явным указанием а) ​​предыдущих проблем и б) того, как в новом предложении эти комментарии и проблемы учтены или прояснены. . Заявка будет автоматически отклонена, если она не отвечает должным образом на комментарии и опасения предыдущих рецензентов. Комментарии рецензента можно найти под исходной заявкой в ​​InfoReady. Пожалуйста, свяжитесь с [email protected], если вы не можете найти свои предыдущие комментарии к обзору.

МИР VPR

МИР VPR

ДОБРО ПОЖАЛОВАТЬ В VPR WORLD

Прочная сеть и отношения с ключевым

Достигайте того, чего вы когда-либо хотели, с помощью самого широкого выбора профессиональных курсов в области ИТ, поскольку мы понимаем спрос на квалифицированных специалистов в области вычислительной техники и прививаем профессионализм студентам. с отраслевыми модулями курсов и предоставить им рабочую среду в реальном времени.
VPR World внедряет инновационные курсы в соответствии с потребностями отраслевых стандартов с модульной структурой, чтобы помочь им подготовиться и повысить свои навыки для всех потоков, и это будет сделано путем предоставления специализированного коучинга, лидерства и мотивации, чтобы преуспеть в их производительности.

2990

Студенты

309

Франчайзи

73

Курсы

запрос франчайзи

онлайн-регистрация студентов

проверка студента

НАШИ КУРСЫ