Функция гпр для чего нужна

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

Источник

Примеры функции ГПР в 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 — полная инструкция по использованию с примерами

Функция ГПР – подробное описание

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

Естественно, вам нужно поставить свое название столбцов, колонок и так далее. Единственное, что нужно, так это оставить последний аргумент, если необходимо осуществить поиск определенного по счету сотрудника.

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

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

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

Аргументы

Давайте более детально рассмотрим каждый из этих аргументов:

Замечания

При работе с функцией ГПР необходимо обратить внимание на следующие нюансы:

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

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

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

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

Предположим, у нас есть такая прекрасная табличка, и нам нужно получить данные о клиенте и телефоне, исходя из его айди.

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

2

Далее нам нужно сделать список в соответствующих местах (там, где будет указываться айди клиента). Для этого нужно в перечне с типами данных найти пункт «Список». А в поле, подписанном как «Источник» пропишите диапазон адресов ячеек первого столбца.

3

Как мы видим, в колонке, где описываются клиенты, была прописана следующая формула:

С ее помощью программа автоматически выдает клиента, основываясь на его идентификационном номере.

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

Давайте более подробно распишем эту формулу. С помощью функции ЕСЛИ мы осуществляем проверку значения, которое возвращается в первую ячейку (ту, в которую записывается идентификатор). Если оказывается, что номера нет, то возвращается сообщение, что клиент его не указал.

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

4 5

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

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

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

6

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

7

Теперь давайте запишем формулу со скриншота ниже.

8

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

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

9

Далее используем нашу формулу с аргументами, приведенными на этом скриншоте.

10

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

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

11

Таким образом, возможностей для поиска нужных значений в программе Excel действительно огромное количество. И с каждой новой версией офисного пакета этот набор только увеличивается. Что нас ждет дальше? Неизвестно. Но точно нужно быть уверенным, что все будет отлично. Ведь как говорят эксперты по финансам, Excel и Powerpoint – две самые популярные и эффективные программы для того, чтобы генерировать деньги. Если правильно научиться пользоваться электронными таблицами, то специалист будет невероятно востребованным, независимо от сферы, в которой он работает.

Источник

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

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

Описание

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

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

Синтаксис

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

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

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

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

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

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

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

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

Примечание

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

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

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

Пример

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

Источник

• ← Функция глонасс в телефоне что это
• Функция гриль в духовке для чего нужна →

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