Функции впр и гпр в excel

Функции впр и гпр в excel

В этой статье описаны синтаксис формулы и использование функции ГПР в Microsoft Excel.

Описание

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

Буква Г в аббревиатуре "ГПР" означает "горизонтальный".

Синтаксис

Аргументы функции ГПР описаны ниже.

Искомое_значение — обязательный аргумент. Значение, которое требуется найти в первой строке таблицы. "Искомое_значение" может быть значением, ссылкой или текстовой строкой.

Таблица — обязательный аргумент. Таблица, в которой производится поиск данных. Можно использовать ссылку на диапазон или имя диапазона.

Значения в первой строке аргумента "таблица" могут быть текстом, числами или логическими значениями.

Если аргумент "интервальный_просмотр" имеет значение ИСТИНА, то значения в первой строке аргумента "таблица" должны быть расположены в возрастающем порядке: . -2, -1, 0, 1, 2, . A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ГПР может выдать неправильный результат. Если аргумент "интервальный_просмотр" имеет значение ЛОЖЬ, таблица может быть не отсортирована.

В текстовых строках регистр букв не учитывается.

Значения сортируются слева направо по возрастанию. Дополнительные сведения см. в разделе Сортировка данных в диапазоне или таблице.

Номер_строки — обязательный аргумент. Номер строки в аргументе "таблица", из которой будет возвращено соответствующее значение. Если значение аргумента "номер_строки" равно 1, возвращается значение из первой строки аргумента "таблица", если оно равно 2 — из второй строки и т. д. Если значение аргумента "номер_строки" меньше 1, функция ГПР возвращает значение ошибки #ЗНАЧ!; если оно больше, чем количество строк в аргументе "таблица", возвращается значение ошибки #ССЫЛ!.

Интервальный_просмотр — необязательный аргумент. Логическое значение, которое определяет, какое соответствие должна искать функция ГПР — точное или приблизительное. Если этот аргумент имеет значение ИСТИНА или опущен, возвращается приблизительное соответствие; при отсутствии точного соответствия возвращается наибольшее из значений, меньших, чем "искомое_значение". Если этот аргумент имеет значение ЛОЖЬ, функция ГПР ищет точное соответствие. Если найти его не удается, возвращается значение ошибки #Н/Д.

Читайте также:  Как вставить текст без фона

Примечание

Если функция ГПР не может найти "искомое_значение" и аргумент "интервальный_просмотр" имеет значение ИСТИНА, используется наибольшее из значений, меньших, чем "искомое_значение".

Если значение аргумента "искомое_значение" меньше, чем наименьшее значение в первой строке аргумента "таблица", функция ГПР возвращает значение ошибки #Н/Д.

Если аргумент "интервальный_просмотр" имеет значение ЛОЖЬ и аргумент "искомое_значение" является текстом, в аргументе "искомое_значение" можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому одному знаку; звездочка — любой последовательности знаков. Чтобы найти какой-либо из самих этих знаков, следует указать перед ним знак тильды (

Пример

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

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

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

искомое_значение — константа или ссылка на ячейку, значение которой вы собираетесь искать

массив — диапазон ячеек, состоящий, как минимум, из двух столбцов (в противном случае данная формула теряет всякий смысл) и, как правило, большого количества строк

номер_столбца — номер столбца, указанного масива, из которого будут возвращаться данные

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

Читайте также:  Введите nck код что это

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

Схема

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

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

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

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

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

искомое_значение — константа или ссылка на ячейку, значение которой вы собираетесь искать

массив — диапазон ячеек, состоящий, как минимум, из двух строк (в противном случае данная формула теряет всякий смысл) и, как правило, большого количества столбцов

номер_строки — номер строки, указанного масива, из которой будут возвращаться данные

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

ГПР осуществляет поиск в первой строке массива и, если значение найдено, то возвращает результат, взятый на пересечении соответствующего столбца и указанного в третьем параметре строки массива.

Очередь просмотра

Очередь

  • Удалить все
  • Отключить

Хотите сохраните это видео?

  • Пожаловаться

Пожаловаться на видео?

Выполните вход, чтобы сообщить о неприемлемом контенте.

Понравилось?

Не понравилось?

Текст видео

В данном уроке мы будем создавать динамические столбцы и строки для выбранных записей из таблицы с помощью функций ВПР и ГПР. Что такое динамический столбец?

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

План урока выглядит следующим образом:

создание динамического столбца (1 пример);
создание динамической строки (1 пример).

Ссылка на основную публикацию
Фотоаппарат сони dsc h50
Название объектива : Carl Zeiss Vario-Tessar Количество групп оптических элементов : 8 Количество оптических элементов : 13 Цифровой Zoom :...
Файл cms что это
Файлы формата CMS открываются специальными программами. Существует 2 типа форматов CMS, каждый из которых открывается разными программами. Чтобы открыть нужный...
Файл менеджер для windows 10 на русском
Менеджер файлов осуществляет просмотр, копирование, управление медиафайлами и папками на персональном компьютере. Он предоставляет функцию быстрого перемещения объектов для ускорения...
Фотоаппараты до 10000 рублей рейтинг
На российском рынке представлено настолько много фотоаппаратов и камер, что найдется модель на любой вкус. В том числе есть действительно...
Adblock detector