Применение и определение этой функции Excel
ВПР - это функция Excel, с помощью которой пользователь может искать и оценивать содержимое таблицы. Эта функция доступна в версиях Excel 2007 для Windows и Mac.
Что такое ВПР?
Возможные варианты использования ВПР должны быть объяснены здесь на примере: В этом примере вы большой поклонник литературы и поэтому создали свою собственную электронную таблицу Excel, в которой вы можете тщательно отсортировать собранные вами книги. Каждая работа вводится с информацией по следующим категориям:
-
автор
-
заглавие
-
Номер страницы
-
Год публикации
Теперь вы хотите дать другу совет о книге, чтобы он взял его с собой на следующую встречу. К сожалению, вы можете думать только об авторе, а не о названии книги. Именно здесь вступает в игру функция ВПР, поскольку она может использовать это входное значение для вывода информации, которую вы ищете, одним махом.
Как используется ВПР?
Прежде чем даже думать о формулировании формул, следует определить, где позже будут расположены поле ввода и различные поля вывода. Для этого имеет смысл создать отдельную таблицу, которая изначально пуста и, таким образом, дает место для упомянутой информации. Если вы спроектируете эту новую таблицу на основе примера существующей таблицы, вы сможете сэкономить время позже.
Исходя из этого, формула ВПР может быть создана вручную или автоматически сгенерирована Excel. Новичкам стоит использовать второй подход, чтобы постепенно знакомиться со структурой и действием формулы. Для этого на вкладке «Формулы» выбирается кнопка «Вставить функцию». ВПР скрывается в открывшемся окне. После подтверждения снова открывается окно, в котором можно заполнить четыре параметра формулы. Эти:
-
Критерий поиска
-
матрица
-
Индекс столбца
-
Area_reference
Таким образом, необработанный набросок формулы выглядит так:
= ВПР (критерий поиска, матрица, индекс столбца, диапазон_ссылка)
и в одном из возможных приложений вроде этого:
= ВПР (H3; A3: E40; 5)
Критерий поиска
Чтобы функция знала, какое значение следует использовать в качестве отправной точки, строка, которая была выбрана в качестве поля ввода двумя шагами ранее, отмечается в поле «Критерий поиска». В нашем примере сюда вводится имя автора книги «Филипп Пульманн». Это делает формулу гибкой и не требует повторной корректировки при изменении введенного значения.
матрица
Поле ввода «Матрица» описывает таблицу, в которой можно найти выводимую информацию. Таким образом, эта специальная матрица также содержит столбцы для названия книги, номера страницы и года публикации.
Матрица полностью выбирается один раз без заголовков от верхнего левого края до нижнего правого поля. Таким образом, Excel знает, какое содержимое следует учитывать при оценке.
Индекс столбца
Поле ввода для «индекса столбца» предлагает пользователю определить столбец матрицы, в котором указано только искомое значение. Расположение столбцов пронумеровано в хронологическом порядке. Это означает, что первый столбец таблицы получает значение 1, второй - значение 2 и т. Д. В нашем примере это соответствует индексу столбца 1 для автора, индексу столбца 2 для заголовка, индексу столбца 3 для номера страницы. и индекс столбца 4 для года публикации.
Чтобы таблица оставалась максимально гибкой, вместо номера можно связать заголовок столбца. Это имеет то преимущество, что формулу также можно без проблем переносить в другие строки, поскольку заголовок столбца можно гибко адаптировать каждый раз.
Внимание: ВПР считывает матрицу слева направо, поэтому индекс столбца должен быть помещен справа от столбца для критерия поиска, чтобы функция учитывала его!
Area_reference
Параметр «Range_Lookup» завершает формулу ВПР, указывая точность, с которой оценивается таблица. Однако он отличается от ранее упомянутых компонентов формулы, поскольку является необязательным. Если значение 0 введено для «неверно», Excel будет искать только значение, указанное в качестве критерия поиска. Однако при значении 1 для «истина» поиск очевидных значений продолжается, если точное значение не может быть найдено.
Указание этого параметра необязательно, поскольку по умолчанию установлено значение 1. Этот параметр будет полезен позже в расширенной ВПР с несколькими критериями поиска.
Слияние
Как только все необходимые параметры будут настроены, можно будет использовать ВПР. После ввода критерия поиска и подтверждения функции искомое значение появится в строке, которая была определена как поле вывода.
В нашем примере теперь отображается название книги «Золотой компас», которое соответствует введенному автору. Чтобы быстро узнать номер страницы и год публикации, не нужно ничего делать, кроме как перетащить существующую формулу ВПР в последующие ячейки. Это так просто, потому что индекс столбца ВПР был связан с заголовком столбца первой таблицы, а вторая таблица также структурирована в том же порядке.
Если таблицы должны отличаться друг от друга или несмотря ни на что возникает ошибка, формулу ВПР также можно изменить вручную. Для этого предпоследняя цифра индекса столбца должна соответствовать столбцу нового значения, которое нужно вывести.
ВПР с несколькими критериями поиска
Достаточно часто бывает, что одного критерия поиска недостаточно для точной оценки большой таблицы Excel. Тогда имеет смысл запустить ВПР с несколькими критериями поиска. Для этого существующая формула должна быть дополнена дополнительной функцией ЕСЛИ. Таким образом, при подаче заявки можно учесть до восьми различных критериев поиска.
ВПР в нескольких таблицах Excel
Если критерий поиска можно найти не только в одной таблице, но, возможно, и в другой, формула ВПР может быть соответствующим образом скорректирована. Для этого перед существующей формулой необходимо поместить и функцию if, и функцию ISERROR. Для этого необходимы пять параметров:
-
Критерий поиска
-
Матрица 1 и Матрица 2
-
Столбец index1 и столбец index2
Результат выглядит так:
= ЕСЛИ (ЕСТЬ ОШИБКА (ВПР (критерий поиска; матрица1; индекс-столбца1; 0));
ВПР (критерий поиска; матрица2; индекс столбца2,0); ВПР (критерий поиска; матрица1; индекс1 столбца;))
и в одном из возможных приложений вроде этого:
= ЕСЛИ (ЕСТЬ ОШИБКА (ВПР (E5; A5: B9; 2; 0)); ВПР (E5; A13: B17; 2; 0); ВПР (E5; A5: B9,2; 0))
Критерий поиска используется для вставки искомого значения в две таблицы. Matrix1 и Matrix2 определяют соответствующие области ячеек двух таблиц. Столбец index1 и столбец index2 используются для более подробного определения столбцов соответствующих таблиц, в которых следует искать.
Если искомое значение встречается в обеих таблицах, Excel выведет результат из первой таблицы. Однако, если значение не найдено ни в одной из двух таблиц, появляется сообщение об ошибке. Преимущество формулы состоит в том, что два списка не обязательно должны иметь одинаковую структуру или одинаковый размер.
Присваивайте значения категориям с помощью ВПР
Дополнительная функция ВПР позволяет автоматически разделить перечисленные значения на буквы и предикаты по вашему выбору. В нашем предыдущем примере для типа книги нужно вставить дополнительный столбец таблицы. Книги объемом до 50 страниц должны относиться к жанру рассказа, книги от 51 до 150 страниц относятся к новелле, а от 151 страницы - к роману. Для этого в ВПР не требуется дополнительных формул, достаточно использовать фигурные скобки «{}». Готовая формула выглядит так:
= ВПР (B1; {1. «Рассказ»; 51. «Новелла»; 151. «Роман»}; 2)
Содержание фигурных скобок указывает на матрицу, которая определяет область соответствующего типа книги. Соответственно, отнесение длины стороны к соответствующему роду заключено в фигурные скобки. В формуле используются пары значений, разделенных точкой. Матрица {1. "Повесть"; 51. "Новелла"; 151. "Роман"} читается следующим образом:
«Из 1 покажите рассказ, из 51 покажите новеллу, из 151 покажите роман».
Эту матрицу легко адаптировать под разные задачи. Это касается, с одной стороны, размера и количества матриц, а также их обозначения. Таким образом, в результате можно выводить строки или числа вместо отдельных букв. Все, что вам нужно сделать, это изменить буквы в формуле.
ВПР на нескольких листах
Другая функция VLOOKUP позволяет пользователям связывать контент, который находится в разных таблицах. В нашем примере этот параметр может быть полезен, когда информация сначала сортируется на разных листах, а затем обновляется в сводной таблице.
Представьте, что вы, помимо своих книг, также перечисляете свои собранные фильмы в таблице Excel. Затем вы объединяете обе коллекции в одну большую таблицу.
Преимущество этой процедуры заключается не только в повышенном порядке, но и во избежании возможных ошибок. Если вы хотите создать новую запись или обновить существующую, вам не нужно искать в большой таблице, вместо этого вы можете получить доступ к меньшим. Затем значения автоматически переносятся в итоговую таблицу Excel. Это делает излишнюю перезапись в большой таблице, что в лучшем случае позволяет избежать неудачного перемещения и последующего объединения сообщений об ошибках.
Как выглядит формула?
Эта функция снова стала возможной благодаря вставке другой формулы. В то время как для поиска по нескольким критериям требовалась дополнительная формула ЕСЛИ, для работы с несколькими листами требуется КОСВЕННАЯ формула. Это позволяет указать диапазон из другой электронной таблицы для матрицы ВПР.
= ВПР (критерий поиска; КОСВЕННЫЙ (матрица); индекс столбца; диапазон_ссылка)
Внимание: Эта формула будет работать только в том случае, если отдельные таблицы на разных листах имеют те же имена, что и заголовки столбцов общей таблицы. Целые таблицы могут быть названы в «Поле имени» в верхнем левом углу над сеткой ячеек. Таблицы, которые уже были названы, можно просмотреть с помощью комбинации клавиш Ctrl + F3.
Работа с появляющимися сообщениями об ошибках
Работа со связанными таблицами Excel может привести к нежелательным проблемам. Это, в частности, включает вывод неверных значений. В случае если выводится неверное значение 0, возникает небольшая проблема в настройках Excel, которую можно быстро исправить.
С другой стороны, обычное сообщение об ошибке #NV является преднамеренной функцией ВПР, которая указывает пользователю, что требуемое значение недоступно. Эту заметку можно оформить по-разному с помощью формулы.
ВПР - обзор
ВПР - это полезная функция Excel, которую можно использовать для поиска и оценки таблиц. Его преимущества очевидны в удобном и гибком применении. Таким образом, любой, кто регулярно работает с таблицами Excel, может извлечь выгоду из этой функции. Будь то частный коллекционер, который создает свои собственные небольшие таблицы, или крупная компания, которая обрабатывает значительно более значительные наборы данных.
Если, с другой стороны, у вас все еще есть неотвеченные запросы, которые не удалось удовлетворить с помощью функции VLOOKUP, вы можете рассчитывать на дополнительную опцию Excel: Microsoft предлагает пользователям Excel 365 новый XLOOKUP с начала 2022-2023 годов. Это расширяет возможности ВПР и дополняет их дополнительными, иногда даже более простыми функциями. Таким образом, здесь также открывается новая процедура оценки данных.
