Гпр функция не работает

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

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

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

Ниже коротко рассмотрена работа функции ГПР, её синтаксис (для ручного написания, а также понимания аргументов функции) и рассмотрен простой пример применения ГПР на практике.

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

[нажмите на картинку для увеличения]
Справка: как сохранять фото с сайтов

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

Обобщённый синтаксис формулы ГПР следующий:
ГПР(искомое_значение; таблица; номер_строки; [интервальный_просмотр])

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

• искомое_значение
  Адрес ячейки в первой таблице (где наша функция ГПР), которое нужно найти в аналогичной по содержанию строке второй таблицы. Вместо адреса ячейки можно указать текст или число, но обычно это не имеет значения, так как для разных ячеек искомое значение меняется. Это обязательный аргумент. В нашем примере для ячейки B5 искомым значением является наименование должности в ячейке B4.
• таблица
  Диапазон ячеек, в которых ищется совпадение по значению, указанное в аргументе 1. Диапазон должен быть указан таким образом, чтобы строка, в которой производится поиск совпадений, была первой (считая сверху вниз). Заголовки таблицы не нужно включать в диапазон. В нашем примере это $B8:$D9 (обратите внимание на символ доллара — он нужен чтобы диапазон оставался неизменным при копировании формулы в другие ячейки первой таблицы).
• номер_строки
  Порядковый номер строки в указанном диапазоне ячеек (параметр «таблица»), значение из которого будет являться результатом работы функции ГПР. Именно это значение будет возвращено функцией (вставлено в ячейку), если совпадение искомого значения найдено. В нашем примере для ячейки B5 это 2 (строки нумеруются начиная с 1 от верхнего края диапазона).
• интервальный_просмотр
  Значение 0 или 1. Число 0 означает поиск точного совпадения искомого значения; 1 — приблизительный. В примере мы ищем точное совпадение, поскольку нам требуется найти соответствие зарплаты по должности.

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

Как работает функция ГПР в Excel

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

Если совпадение не найдено, то ГПР возвращает значение «#Н/Д».

Почему не работает функция ГПР

Если не получается извлечь нужные значения из указанного диапазона, то наиболее вероятно, что есть ошибка в аргументах формулы. Вот несколько типичных ошибок:

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

В нашем примере для ячейки B5 во второй таблице будет найдено значение зарплаты по значению должности. Алгоритм поиска работает так:

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

Итого в результате для ячейки B5 результат работы ГПР будет такой: «40000»

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

ГПР в Excel, примеры

Показанный выше пример рассмотрен на видео. Также Вы можете скачать Excel файл с этим примером.

Комментарии по практическому применению ГПР в Эксель можно добавить после статьи. Также приветствуются Ваши примеры по применению данной формулы.

Вы можете просмотреть любой прикреплённый документ в виде PDF файла. Все документы открываются во всплывающем окне, поэтому для закрытия документа пожалуйста не используйте кнопку «Назад» браузера.

Справка по функции ГПР в Excel.pdf

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

Источник

Примеры функции ГПР в Microsoft Excel

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

Транспонировка вертикальной таблицы

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

Выделите всю таблицу с зажатой левой кнопкой мыши, а затем сделайте по ней клик правой.

Выберите пустую ячейку для вставки таблицы или создайте отдельный лист для упрощения этой задачи. Снова правым кликом вызовите контекстное меню и нажмите «Специальная вставка».

Появится окно настройки, в котором среди всех параметров понадобится отметить только «транспонировать», после чего жмите «ОК».

Пример 1: Одно значение

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

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

Объявите ее в необходимом блоке, поставьте открывающую круглую скобку и переходите далее.

В качестве искомого значения указывается ячейка, по которой следует ориентироваться. В нашем случае это «Апрель».

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

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

Добавьте в конце «0», чтобы обозначить точное совпадение, поставьте закрывающую круглую скобку и подтвердите создание функции нажатием клавиши Enter.

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

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

Выделите уже готовую ячейку и растяните ее на необходимое расстояние.

Подстановка значений прошла успешно.

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

Пример 2: Несколько значений

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

Мы добавили строки в нашу таблицу и создали новые значения для месяцев в нижней.

Используем все ту же формулу, созданную в первом примере.

Растяните ее вниз, чтобы посмотреть, как произойдет автоматическое замещение для новых строк.

Видно, что растягивание прошло успешно и значения подставились корректно.

Сразу выделите все ячейки для растягивания, чтобы не делать это с каждым столбцом.

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

Помимо этой статьи, на сайте еще 12342 инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Источник

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

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

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

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

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

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

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

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

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

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

Аргументы

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

1. Искомое значение. Этот аргумент необходимо указывать в обязательном порядке. Это те данные, поиск которых нам нужно осуществлять по горизонтали. Форматы могут быть разными: текстовый, ссылка или же число.
2. Таблица. Простыми словами, массив данных, в которых будет осуществляться поиск. Есть несколько вариантов, как можно записывать этот параметр: в виде текста, числа или логического значения. Также можно указывать как имя диапазона, так и ссылки. Важно учитывать то, что если используется строка текстового формата, то не учитывается то, большая буква или маленькая там написана. Сортировка значений в искомом диапазоне осуществляется таким же образом, как мы читаем книгу. То есть, слева направо.
3. Номер строки. Здесь задается номер строки, из которого будет получено значение. Минимальное значение номера строки, которое допускается использовать в этой формуле – 1. Если указать меньшее, то будет возвращена ошибка #ЗНАЧ! Также нужно следить, чтобы количество строк в прошлом аргументе не было меньше, чем в третьем аргументе. Если допустить это, будет возвращен результат #ССЫЛ!
4. Интервальный просмотр. Этот аргумент является необязательным. Содержит логическое значение, исходя из которого Excel понимает, ему использовать точное вхождение или приблизительное. В случае истинного значения, функция ищет похожие соответствия, но не полные. Если же значение ложное, то функцией осуществляется поиск точно такого же значения, которое было указано пользователем. В случае неудачи при поиске, возвращается ошибка #Н/Д.

Замечания

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

1. Если у функции не удается успешно осуществить поиск искомого значения, а аргумент «интервальный просмотр» имеет значение «ИСТИНА», то осуществляется поиск самого большого значения, которое меньше и не равно искомому.
2. Если указать для поиска значение, которое меньше минимального в заданном диапазоне, то функцией возвращается ошибка #Н/Д.
3. В случае соответствия аргумента «интервальный просмотр» значению ЛОЖЬ одновременно с текстовым форматом аргумента «искомое значение», то возможно использование подстановочных знаков для того, чтобы указать приблизительное соответствие. Например, вопросительный знак заменяет один символ, а звездочка – несколько любых символов. А знак тильды позволяет отыскать эти знаки.

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

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

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

1. ПОИСК . Это наиболее простая функция, которая ищет заданную строку с учетом регистра. При этом она не способна найти, например, второе, третье и так далее вхождение, ограничиваясь исключительно первым. Также есть похожая функция, которая называется ПОИСКБ. Ее основное отличие в том, что ориентируется она не на количество символов, а на количество байтов. Но в кириллических языках такой необходимости нет. Также в этой функции есть возможность использовать подстановочные знаки типа ?, *. Если же нужно искать конкретный знак вопроса или звездочку, то перед этим символом нужно поставить знак тильды. Если же нужное значение найти не удается, то выдается ошибка #ЗНАЧ!
2. НАЙТИ . По сути, функция, дублирующая ПОИСК за тем лишь исключением, что она учитывает регистр и не поддерживает подстановочные символы.
3. ИНДЕКС . Синтаксис этой функции сильно отличается от того, как мы ее будем использовать. А спектр возможностей поистине огромный. Это и получение нужных данных по номеру, и извлечение нужной информации из двухмерного диапазона, и возможность осуществлять поиск исключительно в конкретной таблице, если их несколько. Кроме этого, она дает возможность давать непосредственно ссылку на нужную колонку или строку, а также ячейку.
4. ПОИСКПОЗ . Это еще одна функция, которая может использоваться для различных целей с различным синтаксисом. Она используется для того, чтобы осуществлять поиск позиции ячейки с определенным текстом. Пользователь может точно определить, какой именно текст будет искаться. Кроме этого, данная функция нередко используется для поиска первой или последней ячейки, а также осуществлять поиск по датам. Например, с целью определить этап проекта.
   Нередко функция ПОИСКПОЗ используется в сочетании с ИНДЕКС . В таком случае она действует очень похожим на функцию ВПР ( не ГПР) образом, осуществляя поиск нужных данных по вертикали. В частности, она повторяет функционал интервального просмотра как в ВПР , так и в ГПР , поэтому она часто используется для замены их функционала. При этом набор особенностей даже шире по сравнению с ГПР , поскольку она дает возможность выбирать наибольшее или наименьшее ближайшее соответствие (если речь идет о поиске чисел). Правда, по синтаксису эта функция тоже сложнее.

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

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

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

1

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

2

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

3

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

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

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

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

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

4 5

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

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

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

6

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

7

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

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

8

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

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

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

9

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

10

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

1. A1 – это ссылка на ячейку, в которой содержится название поля таблицы.
2. A1:C11 – это исходная таблица, которая используется для получения информации.
3. E2+1 – цифровое обозначение строки таблицы с учетом шапки. Если не прибавить единицу, то будут отображаться данные, которые берут за основу неправильный айди, который будет на единицу меньше. Все дело в том, что первая строка нашей таблицы зарезервирована под шапку, содержащую названия колонок.

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

11

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

Источник