ВПР Excel — что это такое? Что такое впр в экселе

ВПР Excel - что это такое?

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

Что такое ВПР в Excel?

Данную функцию также называют VLOOKUP (в англоязычной версии программы). Это одна из наиболее распространенных функций ссылок и массивов. Специалисты, которые составляли шкалу BRP ADVICE, выставили ей уровень сложности, который можно приравнять к 3 или 7. Данная функция дает возможность быстро найти в большой таблице те значения столбцов или строк, которые требуются пользователю. Значения строк программа находит самостоятельно, а столбец задается пользователем. Имеется и другая функция – ГПР. В ней пользователем отмечается строчка.

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

В функции ВПР сперва указывается номер строки, а затем следует обозначение столбца. Должно получится что- то вроде этого — =ВПР (В1: База_данных;2;ЛОЖЬ). Разберемся, что собой представляет каждый параметр функции. В1 – приблизительная ссылка на ячейку. В данной ячейке может содержаться любое значение, в зависимости от того результата, который хочет получить пользователь. Параметр «База_данных» обозначает имя той области, которую следует искать. Данное понятие не настолько обширное, как ранее рассмотренное. Можно использовать его только к первым столбцам или первым строкам. «2» представляет собой порядковый номер столбца. Оттуда программа будет черпать информацию. Параметр «ЛОЖЬ» указывает на поиск точного совпадения. В некоторых случаях указываются другие дополнительные параметры данного слова. При этом программа будет искать все совпадения и определит ближайшие значения. Функция, к сожалению, не может работать более профессионально, поэтому пользователю придется держать некоторую информацию в голове (номер столбца или строки). Иначе он не сможет найти необходимую информацию.

ВПР: аргументы

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

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

ВПР: распространенные ошибки

Функция ВПР в программе Excel не будет работать неправильно, если сама задача поставлена корректно. Таким образом, во всех нарушениях в работе данной функции виноват пользователь. Существует три наиболее распространенные ошибки. Прежде всего, стоит отметить, что пользователи часто путаются в понятиях «ложь» и «истина». Первое понятие ориентировано на поиск точного совпадения. Если указывать понятие «истина», функция будет подбирать приблизительные значения. Также стоит отметить, что функция ВПР в программе Excel не может обозначаться таким образом, чтобы поиск начинался со второго и последующего столбца. Найти информацию можно только по первом столбцу. Если пользователь введет какую-то другую формулу, которая будет отличаться от заданных правил, программа может попросту ее не распознать. Также, нередко пользователи неправильно указывают номер столбца, в котором требуется искать информацию. В этом случае требуется перепроверить вводимую информацию.

В каких случаях используют функцию ВПР?

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

Как пользоваться ВПР в таблице?

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

computerologia.ru

ВПР и ГПР - Формулы рабочего листа - Excel - Каталог статей

Функция ВПР (VLOOKUP)

Функция ВПР (Вертикальный ПРосмотр) для многих (но, надеюсь, не для вас) является вершиной эволюции в Excel. Что ж, есть много ситуаций, когда эта формула может оказаться полезной. ВПР имеет следующий синтаксис:

= ВПР ( искомое_значение ; массив ; номер_столбца ; тип_поиска )

искомое_значение - константа или ссылка на ячейку, значение которой вы собираетесь искать
массив - диапазон ячеек, состоящий, как минимум, из двух столбцов (в противном случае данная формула теряет всякий смысл) и, как правило, большого количества строк
номер_столбца - номер столбца, указанного масива, из которого будут возвращаться данные
тип_поиска - переключатель типа поиска. Если вы укажите 0 или ЛОЖЬ (FALSE), то функция будет искать точное соответствие с искомым значением, а если укажете 1 или ИСТИНА (TRUE), то формула вернёт максимальное значение, которое МЕНЬШЕ или РАВНО искомого параметра.
ВПР осуществляет поиск в первом столбце массива и, если значение найдено, то возвращает результат, взятый на пересечении соответствующей строки и указанного в третьем параметре столбца массива.

Схема

Некоторые замечания о ВПР

Пример	Замечание
	Искомое значение ищется в ПЕРВОМ столбце массива. Это фундаментальное ограничение ВПР, которое серьёзно вредит универсальности данной функции. Многие пользователи, прикипев к ВПР, и, не зная более гибких альтернатив, вынужденно подстраивают свои таблицы под её возможности, делая нужный столбец первым.
	Поиск оснанавливается на первом совпадении результата, если тип поиска = 0 (точное совпадение) и на последнем, если тип поиска = 1 (примерное совпадение). Таким образом у вас есть возможность при помощи ВПР возвращать либо первую совпавшую строку, либо последнюю. Однако имейте в виду, что при нечётком поиске (тип поиска = 1) первый столбец массива должен быть отсортирован по возрастанию.
	ВПР поддерживает использование символов подстановки (* и ?) в параметре искомого значения. "?" - заменяет собой любой один символ, а "*" - заменяет любое количество любых символов. Если используете символы подстановки, то четвёртый параметр должен быть 0 (ЛОЖЬ).
	Нечёткий поиск часто применяют для распределения значений по каким либо диапазонам. Например, рассмотрим классический пример вычисления значения скидки для клиента в зависимости от заказанного количества товара. Как видите, эта задача идеально вписывается в возможности нечёткого поиска ВПР. Не забывайте только про сортировку.
	При поиске текстовых строк ВПР не видит разницы в регистре букв.
	Если значение не найдено, то формула возвращает код ошибки #Н/Д (#N/A). При помощи дополнительной функции ЕСЛИОШИБКА (IFERROR) эти ошибки из эстетических или иных соображений можно перехватывать и подставлять, как в этом примере, пустую строку (или что угодно другое) - в этом случае пользователи не тревожатся попусту, разглядывая загадочные #Н/Д, и, как следствие значительно реже, отвлекают квалифицированных людей от важного.

Функция ГПР (HLOOKUP)

Функция ГПР (Горизонтальный ПРосмотр) - это тот же самый ВПР, в котором строки заменили на столбцы и наоборот. ГПР имеет следующий синтаксис:

= ГПР ( искомое_значение ; массив ; номер_строки ; тип_поиска )

искомое_значение - константа или ссылка на ячейку, значение которой вы собираетесь искать
массив - диапазон ячеек, состоящий, как минимум, из двух строк (в противном случае данная формула теряет всякий смысл) и, как правило, большого количества столбцов
номер_строки - номер строки, указанного масива, из которой будут возвращаться данные
тип_поиска - переключатель типа поиска. Если вы укажите 0 или ЛОЖЬ (FALSE), то функция будет искать точное соответствие с искомым значением, а если укажете 1 или ИСТИНА (TRUE), то формула вернёт максимальное значение, которое МЕНЬШЕ или РАВНО искомого параметра.
ГПР осуществляет поиск в первой строке массива и, если значение найдено, то возвращает результат, взятый на пересечении соответствующего столбца и указанного в третьем параметре строки массива.

Схема

Некоторые замечания о ГПР

Пример

Замечание

У функции ГПР один недостаток - она никому не нужна. Дело в том, что она противоречит концепции Excel: ведь при грамотном использовании таблиц искать надо в основном в вертикальном столбце, а не в горизонтальной строке. А в тех не частых случаях, когда всё-таки надо что-то найти в строке, есть более удобоваримая и универсальная функция ПОИСКПОЗ (MATCH). За сим рассмотрение ГПР заканчиваю. Хотя, собственно, все замечания справедливые для ВПР, справедливы и для ГПР.

perfect-excel.ru

Поиск данных в таблицах Эксель: ВПР и другие функции

Дата: 6 мая 2016 Категория: Excel Поделиться, добавить в закладки или распечатать статью

Привет, друзья. Как часто вам приходится для какого-то значения искать соответствие в таблице Эксель? Например, нужно в справочнике найти адрес человека, или в прайсе – цену товара. Если такие задачи встречаются – этот пост именно для вас!

Я выполняю подобные процедуры каждый день и без описанных ниже функций мне действительно пришлось бы туго. Берите на заметку и применяйте их в работе!

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

Синтаксис функции ВПР такой: =ВПР(Искомое_значение; таблица_для_поиска; номер_выводимого_столбца; [тип_сопоставления]). Рассмотрим аргументы:

Искомое значение – значение, которое будем искать. Это обязательный аргумент;
Таблица для поиска – тот массив ячеек, в котором будет поиск. Столбец с искомыми значениями должен быть первым в этом массиве. Это тоже обязательный аргумент;
Номер выводимого столбца – порядковый номер столбца (начиная с первого в массиве), из которого функция выведет данные при совпадении искомых значений. Обязательный аргумент;
Тип сопоставления – выберите «1» (или «ИСТИНА») для нестрогого совпадения, «0» («ЛОЖЬ») – для полного совпадения. Аргумент необязателен, если его упустить – будет выполнен поиск нестрогого совпадения.

Поиск точного совпадения с помощью ВПР

Посмотрим на примере, как работает функция ВПР, когда выбран тип сопоставления «ЛОЖЬ», поиск точного совпадения. В массиве В5:Е10 указаны основные средства некой компании, их балансовая стоимость, инвентарный номер и место расположения. В ячейке В2 указано наименование, для которого нужно в таблице найти инвентарный номер и поместить его в ячейку С2.

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

Запишем формулу: =ВПР(B2;B5:E10;3;ЛОЖЬ).

Здесь первый аргумент указывает, что в таблице нужно искать значение из ячейки В2, т.е. слово «Факс». Второй аргумент говорит, что таблица для поиска — в диапазоне В5:Е10, а искать слово «Факс» нужно в первом столбце, т.е. в массиве В5:В10. Третий аргумент сообщает программе, что результат расчета содержится в третьем столбце массива, т.е. D5:D10. Четвёртый аргумент равен «ЛОЖЬ», т.е. требуется полное совпадение.

И так, функция получит строку «Факс» из ячейки В2 и будет искать его в массиве В5:В10 сверху вниз. Как только совпадение будет найдено (строка 8), функция вернёт соответствующее значение из столбца D, т.е. содержимое D8. Именно это нам и требовалось, задача решена.

Если искомое значение не будет найдено, функция вернёт ошибку #Н/Д.

Поиск неточного совпадения с помощью ВПР

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

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

Тогда запишем формулу нестрогого поиска: =ВПР(B2;B5:C12;2;ИСТИНА). Теперь из всех представленных в столбце В данных программа будет искать ближайшее меньшее. То есть, для суммы 8 000 будет отобрано значение 5000 и выведен соответствующий процент.

Нестрогий поиск ВПР в Excel

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

Функция ГПР имеет такой же синтаксис, как и ВПР, но ищет результат не в столбцах, а в строках. То есть, просматривает таблицы не сверху вниз, а слева направо и выводит заданный номер строки, а не столбца.

Функция ПРОСМОТР работает аналогично ВПР, но имеет другой синтаксис. Я использую её, когда таблица данных содержит несколько десятков столбцов и для использования ВПР нужно дополнительно просчитывать номер выводимой колонки. В таких случаях функция ПРОСМОТР облегчает задачу. И так, синтаксис: =ПРОСМОТР(Искомое_значение; Массив_для_поиска; Массив_для_отображения):

Искомое значение – данные или ссылка на данные, которые нужно искать;
Массив для поиска – одна строка или столбец, в котором ищем аналогичное значение. Данный массив обязательно сортируем по возрастанию;
Массив для отображения – диапазон, содержащий данные для выведения результатов. Естественно, он должен одного размера с массивом для поиска.

При такой записи вы даёте не относительную ссылку массива результатов. А прямо на него указываете, т.е. не нужно предварительно просчитывать номер выводимого столбца. Используем функцию ПРОСМОТР в первом примере для функции ВПР (основные средства, инвентарные номера): =ПРОСМОТР(B2;B5:B10;D5:D10). Задача успешно решена!

Функция «ПРОСМОТР» в Microsoft Excel

Еще один способ поиска данных – комбинирование функций ПОИСКПОЗ и ИНДЕКС.

Первая из них, служит для поиска значения в массиве и получения его порядкового номера: ПОИСКПОЗ(Искомое_значение; Просматриваемый_массив; [Тип сопоставления]). Аргументы функции:

Искомое значение – обязательный аргумент
Просматриваемый массив – одна строка или столбец, в котором ищем совпадение. Обязательный аргумент
Тип сопоставления – укажите «0» для поиска точного совпадения, «1» — ближайшее меньшее, «-1» — ближайшее большее. Поскольку функция проводит поиск с начала списка в конец, при поиске ближайшего меньшего – отсортируйте столбец поиска по убыванию. А при поиске большего – сортируйте его по возрастанию.

Позиция необходимого значения найдена, теперь можно вывести его на экран с помощью функции ИНДЕКС(Массив; Номер_строки; [Номер_столбца]):

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

Теперь скомбинируем эти функции, чтобы получить результат:

Функции ПОИСКПОЗ и ИНДЕКС в Эксель

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

Чувствуете, как растут и крепчают Ваши знания и умения? Тогда не останавливайтесь, продолжайте читать Officelegko.com! В следующем посте мы будем рассматривать расчеты кредита в Эксель: будет сложно и интересно!

Поделиться, добавить в закладки или распечатать статью

officelegko.com