что значит гпр в excel

Примеры формулы ГПР для выборки по строкам с условием в Excel

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

Формула ГПР и выборка по строкам в Excel для чайников

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

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

Формула ИНДЕКС и ПОИСКПОЗ для горизонтальной выборки по строкам

Функции ГПР и ВПР очень похожи между собой. Их обе можно заменить формулами из функций ИНДЕКС и ПОИСКПОЗ:

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

Источник

ГПР (функция ГПР)

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

Описание

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

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

Синтаксис

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

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

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

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

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

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

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

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

Примечание

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

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

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

Пример

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

Источник

Примеры функции ГПР в Excel пошаговая инструкция для чайников

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

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

Например, в таблице с полями «Имя» и «Дата рождения» необходимо получить значение даты рождения для сотрудника, запись о котором является третьей сверху. В этом случае удобно использовать следующую функцию: =ГПР(«Дата рождения»;A1:B10;4), где «Дата рождения» – наименование столбца таблицы, в котором будет выполнен поиск, A1:B10 – диапазон ячеек, в котором расположена таблица, 4 – номер строки, в которой содержится возвращаемое значение (поскольку таблица содержит шапку, номер строки равен номеру искомой записи +1.

Пошаговые примеры работы функции ГПР в Excel

Пример 1. В таблице содержатся данные о клиента и их контактных номерах телефонов. Определить номер телефона клиента, id записи которого имеет значение 5.

Вид таблицы данных:

Для расчета используем формулу:

В ячейке F2 автоматически выводится значение соответствующие номеру id в исходной таблице.

ГПР для выборки по нескольких условиях в Excel

Пример 2. На основе таблицы из первого примера создать компактное представление таблицы, в которой по номеру id можно получить записи, хранящиеся в столбцах «Клиент» и «Телефон».

Создадим заготовку таблицы:

Для удобного использования в ячейке E2 создадим выпадающий список. Для этого выберите инструмент: «ДАННЫЕ»-«Работа с данными»-«Проверка данных».

В появившемся окне «Проверка вводимых значений» выберите из секции «Тип данных:» в выпадающем меню опцию «Список». А в поле «Источник:» укажите адрес ссылки на диапазон исходных ячеек первого столбца таблицы =$A$2:$A$11, как показано выше на рисунке.

Для выбора клиента используем следующую формулу в ячейке F2:

Для выбора номера телефона используем следующую формулу (с учетом возможного отсутствия записи) в ячейке G2:

Функция ЕСЛИ выполняет проверку возвращаемого значения. Если искомая ячейка не содержит данных, будет возвращена строка «Не указан».

Интерактивный отчет для анализа прибыли и убытков в Excel

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

Вид таблиц данных с выпадающим списком в ячейке E2 (как сделать выпадающий список смотрите в примере выше):

В ячейку F2 запишем следующую формулу:

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

В ячейке G2 запишем формулу:

Функция ЕСЛИ сравнивает возвращаемые функциями ГПР значения и возвращает один из вариантов текстовых строк.

Особенности использования функции ГПР в Excel

Функция имеет следующую синтаксическую запись:

=ГПР( искомое_значение;таблица;номер_строки; [интервальный_просмотр])

Источник

Функции ВПР и ГПР в Excel с примерами их использования

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

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

Синтаксис функций ВПР и ГПР

Функции имеют 4 аргумента:

! Если значения в диапазоне отсортированы в возрастающем порядке (либо по алфавиту), мы указываем ИСТИНА/1. В противном случае – ЛОЖЬ/0.

Как пользоваться функцией ВПР в Excel: примеры

Для учебных целей возьмем таблицу с данными:

Формула	Описание	Результат
Функция ищет значение ячейки F5 в диапазоне А2:С10 и возвращает значение ячейки F5, найденное в 3 столбце, точное совпадение.
Нам нужно найти, продавались ли 04.08.15 бананы. Если продавались, в соответствующей ячейке появится слово «Найдено». Нет – «Не найдено».
Если «бананы» сменить на «груши», результат будет «Найдено»
Когда функция ВПР не может найти значение, она выдает сообщение об ошибке #Н/Д. Чтобы этого избежать, используем функцию ЕСЛИОШИБКА.	Мы узнаем, были ли продажи 05.08.15
Если необходимо осуществить поиск значения в другой книге Excel, то при заполнении аргумента «таблица» переходим в другую книгу и выделяем нужный диапазон с данными.	Мы захотели узнать, кто работал 8.06.15.
Поиск приблизительного значения.

Как пользоваться функцией ГПР в Excel: примеры

Для учебных целей возьмем такую табличку:

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

Символы подстановки в функциях ВПР и ГПР

Случается, пользователь не помнит точного названия. Задавая искомое значение, он может применить символы подстановки:

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

Как сравнить листы с помощью ВПР и ГПР

У нас есть данные о продажах за январь и февраль. Эти таблицы необходимо сравнить с помощью формул ВПР и ГПР. Для наглядности мы пока поместим их на один лист. Но будем работать в условиях, когда диапазоны находятся на разных листах.

Как сравнить листы с помощью ВПР в Excel?

Решим проблему 1 : сравним наименования товаров в январе и феврале. Так как в феврале их больше, вводить формулу будем на листе «Февраль».

Решим проблему 2 : сравним продажи по позициям в январе и феврале. Используем следующую формулу:

Как сравнить листы с помощью ГПР в Excel?

Для демонстрации действия функции ГПР возьмем две «горизонтальные» таблицы, расположенные на разных листах.

Задача – сравнить продажи по позициям за январь и февраль.

Создаем новый лист «Сравнение». Это не обязательное условие. Сопоставлять данные и отображать разницу можно на любом листе («Январь» или «Февраль»).

Проанализируем части формулы:

. Искомое значение – первая ячейка в таблице для сравнения. Анализируемый диапазон – таблица с продажами за февраль. Функция ГПР «берет» данные из 2 строки в «точном» воспроизведении.

. Все то же самое. Кроме диапазона. Здесь берется таблица с продажами за январь.

Когда мы вводим формулу, Excel подсказывает, какой сейчас аргумент нужно ввести.

Источник

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

Добрый день читатель!

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

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

Теперь рассмотрим, как работает функция ГПР в Excel более пристально, и начнем эту процедуру с изучения синтаксиса функции:

=ГПР ( искомое нами значение; таблица поиска; номер строки; [интервальный просмотр]), где

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

А теперь рассмотрим на примере, как используется функция ГПР в Excel, что бы рассмотреть принцип работы этой функции. Итак, у нас есть таблица с видами мебели и их вариациями, вот на основе таких данных и будет экспериментировать: =ГПР(«Диван»;A1:C4;2;ИСТИНА), производим поиск слова «Диван» в строчке 1 и возвращает значение из строки 2, которая находится в том же столбике, результат получаем «Амур». =ГПР(«Кровать»;A1:C4;3;ЛОЖЬ), производим поиск в первой строке и возвращаем значение из 3 строчки, которая находится в том же столбце, в результате получаем «Марроко».

=ГПР(«К»;A1:C4;3;ИСТИНА), производим поиск буквы «К» в первой строке и возвращаем значение из 3 строчки того же столбца. Поскольку эту букву найти не удалось, вернулось ближайшее из меньших значений «Диван», результат получаем «Кром». =ГПР(«Спальня»;A1:C4;4), производим поиск слова «Спальня» в первой строке и возвращаем значение из 4 строчки, которая находится в том же столбике, наш результат будет «Касар». А на этом у меня всё! Я очень надеюсь, что всё о работе с функцией ГПР в Excel вам понятно. С другими функциями вы можете познакомится в «Справочнике функций». Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!

Не забудьте поблагодарить автора!

«Деньги — это средство иметь буквально все, за исключением искреннего друга, преданной жены и здоровья.
«П. Вебер

Источник

• ← что значит хиты в авакин лайф
• что делать если человек игнорит сообщения →

Формула	Описание	Результат
Поиск значения ячейки I16 и возврат значения из третьей строки того же столбца.
Еще один пример поиска точного совпадения в другой табличке.