Как найти гпр в

Совет: Попробуйте воспользоваться новой функцией ПРОСМОТРX , улучшенной версией функции ГГ ПРОСМОТР, которая работает в любом направлении и по умолчанию возвращает точные совпадения, что упрощает и удобнее использовать предшественницу.

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

Описание

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

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

Синтаксис

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

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

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

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

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

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

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

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

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

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

Примечание

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

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

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

Пример

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

Оси	Подшипники	Болты
4	4	9
5	7	10
6	8	11
Формула	Описание	Результат
=ГПР(«Оси»;A1:C4;2;ИСТИНА)	Поиск слова «Оси» в строке 1 и возврат значения из строки 2, находящейся в том же столбце (столбец A).	4
=ГПР(«Подшипники»;A1:C4;3;ЛОЖЬ)	Поиск слова «Подшипники» в строке 1 и возврат значения из строки 3, находящейся в том же столбце (столбец B).	7
=ГПР(«П»;A1:C4;3;ИСТИНА)	Поиск буквы «П» в строке 1 и возврат значения из строки 3, находящейся в том же столбце. Так как «П» найти не удалось, возвращается ближайшее из меньших значений: «Оси» (в столбце A).	5
=ГПР(«Болты»;A1:C4;4)	Поиск слова «Болты» в строке 1 и возврат значения из строки 4, находящейся в том же столбце (столбец C).	11
=ГПР(3;{1;2;3:»a»;»b»;»c»;»d»;»e»;»f»};2;ИСТИНА)	Поиск числа 3 в трех строках константы массива и возврат значения из строки 2 того же (в данном случае — третьего) столбца. Константа массива содержит три строки значений, разделенных точкой с запятой (;). Так как «c» было найдено в строке 2 того же столбца, что и 3, возвращается «c».	c

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

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

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

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

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

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

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

Описание аргументов:

• F1 – ячейка, содержащая название поля таблицы;
• A1:C11 – диапазон ячеек, в которых содержится исходная таблица;
• E2+1 – номер строки с возвращаемым значением (для id=5 – шестая строка, поскольку первая строка используется под шапку таблицы).

Возвращаемый результат:

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



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

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

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

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

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

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

=ГПР(B1;A1:C11;E2+1)

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

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

Примеры расчетов:

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

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

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

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

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

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

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

Примеры расчетов:

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

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

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

Описание аргументов:

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

1. В качестве аргумента номер_строки передано число <1. В этом случае будет возвращен код ошибки #ЗНАЧ!.
2. Передано число, превышающее значение номера последней записи (строки) в таблице или массиве. Возвращаемое значение – код ошибки #ССЫЛ!.

• [интервальный_просмотр] – необязательный для заполнения аргумент, принимающий логические значения:

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

Примечания:

1. Если аргумент [интервальный_просмотр] явно не указан или имеет значение ИСТИНА, для получения корректных результатов работы функции ГПР первая строка таблицы или массива, переданных в качестве аргумента таблица, должна быть отсортирована по возрастанию значений слева направо. Для поиска точного совпадения (значение аргумента – ЛОЖЬ) сортировку можно не выполнять.
2. Если все данные, хранящееся в первой строке массива или таблицы, превышают по значению данные, переданные в качестве аргумента искомое_значение, функция ГПР вернет код ошибки #Н/Д.
3. При поиске точного соответствия (аргумент [интервальный_просмотр] = ЛОЖЬ) в таблицах с именованными столбцами (полями) можно использовать подстановочные знаки: «?» — замена одного символа, «*» — замена нескольких символов.

Содержание

• Транспонировка вертикальной таблицы
  • Пример 1: Одно значение
  • Пример 2: Несколько значений
• Вопросы и ответы

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

Подробнее: Функция ВПР в программе Microsoft Excel

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

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

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



2. В контекстном меню нажмите кнопку «Копировать».



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



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



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

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

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

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



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



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



4. После каждого объявленного аргумента не забывайте ставить знак «;», который закрывает его.



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



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



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



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



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



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



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

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

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

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

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



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



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



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



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



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

Еще статьи по данной теме:

Помогла ли Вам статья?

Функция ГПР

​Смотрите также​ нужно было просто​Формула:​​ забыли его, но​​ «таблица» переходим в​

Описание

​ данных, где будет​ эти функции вместе,​=ВПР​ ГПР, ИНДЕКС и​Enter​9.99​. Иногда Вам придётся​ с любой формулой​ научиться с ним​ столбце (столбец A).​ используется наибольшее из​ Если аргумент «интервальный_просмотр»​В этой статье описаны​ подтянуть Категорию, спс,​.​ помним, что начинается​ другую книгу и​ производиться поиск (для​ немного сложнее формулы​

​(2345678;A1:E7;5)​ ПОИСКПОЗ.​

Синтаксис

​, то увидите, что​

​.​ менять столбцы местами,​

• ​ в Excel, начинаем​​ работать проще, чем​4​ значений, меньших, чем​ имеет значение ЛОЖЬ,​ синтаксис формулы и​ но вопрос закрыт))​

• ​Результат:​​ с Kol. С​ выделяем нужный диапазон​ ВПР – поиск​ с функцией ВПР,​. Формула возвращает цену​Общий вид функции ВПР​

  • ​ товар​Давайте разберёмся, как работает​ чтобы нужные данные​ со знака равенства​

  • ​ Вы думаете. В​=ГПР(«Подшипники»;A1:C4;3;ЛОЖЬ)​ «искомое_значение».​ таблица может быть​ использование функции​Бахтиёр​Проанализируем части формулы:​ задачей справится следующая​ с данными.​ значения осуществляется в​ но она открывает​ на другую деталь,​ и ее аргументов:​Gift basket​ эта формула. Первым​ оказались в первом​

  • ​ (=). Далее вводим​ этом уроке основы​

  • ​Поиск слова «Подшипники» в​Если значение аргумента «искомое_значение»​ не отсортирована.​ГПР​: все выложенные мною​«Половина» до знака «-»:​

• ​ формула: .​​Мы захотели узнать,​ ПЕРВОМ столбце таблицы;​ больше возможностей. Поэтому​ потому что функция​=ВПР(;;;)​находится в категории​ делом она ищет​ столбце.​ имя функции. Аргументы​ по работе с​ строке 1 и​ меньше, чем наименьшее​В текстовых строках регистр​в Microsoft Excel.​ формулы как раз​. Искомое значение –​Нам нужно отыскать название​ кто работал 8.06.15.​ для ГПР –​ некоторые пользователи предпочитают​ ВПР нашла ближайшее​

• ​Например, =ВПР(21500;C2:E7;3;ЛОЖЬ).​​Gifts​ заданное значение в​Третий аргумент​ должны быть заключены​ функцией​ возврат значения из​ значение в первой​ букв не учитывается.​Выполняет поиск значения в​ и подтягивают​ первая ячейка в​ компании, которое заканчивается​Поиск приблизительного значения.​ в ПЕРВОЙ строке);​ применять сочетание функций​ число, меньшее или​Первый аргумент (часть, необходимая​.​ первом столбце таблицы,​– это номер​

Примечание

• ​ в круглые скобки,​ВПР​ строки 3, находящейся​ строке аргумента «таблица»,​Значения сортируются слева направо​ первой строке таблицы​vikttur​

• ​ таблице для сравнения.​ на — «uda».​Это важно:​НОМЕР столбца/строки – откуда​ ИНДЕКС и ПОИСКПОЗ,​ равное указанному (2345678).​

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

Пример

​ Эта ошибка может​ — это искомое​ сможете ли Вы​ вниз (вертикально). Когда​ пояснить на примере,​ На этом этапе​ языком, который поймут​ столбце (столбец B).​ значение ошибки #Н/Д.​ сведения см. в​ и возвращает значение,​ с ГПР, ВПР,​ таблица с продажами​

​ .​	​ данные в крайнем​	​ значение (1 –​
​ ВПР.​	​ привести к неправильному​	​ значение. Это может​
​ найти данные о​	​ находится значение, например,​	​ чем на словах.​
​ у Вас должно​	​ даже полные «чайники».​	​7​
​Если аргумент «интервальный_просмотр» имеет​	​ разделе Сортировка данных​	​ находящееся в том​
​ не всем понятно,​	​ за февраль. Функция​Найдем компанию, название которой​ левом столбце таблицы​ из первого столбца​В данном примере представлен​ выставлению счета клиенту.​	​ быть ссылка на​
​ товарах:​	​Photo frame​ Первый столбец диапазона​ получиться вот что:​ Итак, приступим!​=ГПР(«П»;A1:C4;3;ИСТИНА)​ значение ЛОЖЬ и​	​ в диапазоне или​
​ же столбце в​	​ что Вы хотите​ ГПР «берет» данные​ начинается на «Ce»​ со значениями.​ или первой строки,​ небольшой список, в​Если для аргумента «приблизительное​ ячейку, например B2,​Цену​, функция переходит во​ – это​	​=VLOOKUP(​
​Прежде чем приступить к​	​Поиск буквы «П» в​ аргумент «искомое_значение» является​ таблице.​ заданной строке таблицы​ в этой теме​ из 2 строки​	​ и заканчивается на​
​Регистр не учитывается: маленькие​	​ 2 – из​ котором искомое значение​ соответствие» указано значение​ или значение, например​coffee mug​ второй столбец, чтобы​1​=ВПР(​ изучению, Вы должны​ строке 1 и​ текстом, в аргументе​Номер_строки​ или массива. Функция​ получить.​ в «точном» воспроизведении.​ –»sef». Формула ВПР​	​ и большие буквы​

support.office.com

Функция ВПР в Excel для чайников

​ второго и т.д.);​ (Воронеж) не находится​ ЛОЖЬ или 0,​ «кузьмина» или 21500.​​Категорию​​ найти цену.​, второй – это​​Теперь добавим аргументы. Аргументы​​ понять основы работы​ возврат значения из​ «искомое_значение» можно использовать​    — обязательный аргумент. Номер​ ГПР используется, когда​Предожите название темы,​После знака «-»:​ будет выглядеть так:​​ для Excel одинаковы.​​ИНТЕРВАЛЬНЫЙ ПРОСМОТР – точное​ в крайнем левом​ а точного совпадения​Второй аргумент — это​

​landscape painting​ВПР​2​ сообщают функции​ функций. Обратите внимание​​ строки 3, находящейся​​ подстановочные знаки: вопросительный​ строки в массиве​​ сравниваемые значения расположены​​ отражающее суть задачи.​. Все то же​ .​Если искомое меньше, чем​ или приблизительное значение​ столбце. Поэтому мы​

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

​ нет, вместо неправильного​​ диапазон ячеек, который,​​Цену​– сокращение от​и так далее.​ВПР​ на раздел​ в том же​ знак (?) и​

​ «таблица», из которой​ в первой строке​​ Модераторы исправят.​​ самое. Кроме диапазона.​​Когда проблемы с памятью​​ минимальное значение в​ должна найти функция​ не можем использовать​​ значения формула возвращает​​ как вы предполагаете,​serving bowl​В​​ В нашем примере​​, что и где​Формулы и функции​ столбце. Так как​ звездочку (*). Вопросительный​ будет возвращено соответствующее​

​ таблицы данных, а​Иначе: все ответы​​ Здесь берется таблица​​ устранены, можно работать​ массиве, программа выдаст​ (ЛОЖЬ/0 – точное;​ функцию ВПР. Для​ в ячейку строку​ содержит искомое значение.​Категорию​ертикальный​ требуется найти цену​ искать.​нашего самоучителя по​ «П» найти не​ знак соответствует любому​ значение. Если значение​

​ возвращаемые — на​
​ здесь не по​

Добавляем аргументы

​ с продажами за​ с данными, используя​​ ошибку #Н/Д.​​ ИСТИНА/1/не указано –​ поиска значения «Воронеж»​

​ «#Н/Д». Это наилучшее​​Важно:​s​ПР​ товара, а цены​​Первый аргумент​​ Microsoft Excel.​ удалось, возвращается ближайшее​ одному знаку; звездочка​ аргумента «номер_строки» равно​

​ несколько строк ниже.​
​ теме. Удаляем?​

​ январь.​​ все те же​Если задать номер столбца​ приблизительное).​ в диапазоне B1:B11​ решение. В данном​​ В функции ВПР столбец,​​carf​осмотр,​ содержатся во втором​– это имя​ВПР​ из меньших значений:​ — любой последовательности​ 1, возвращается значение​ Если сравниваемые значения​

​Ігор Гончаренко​
​Скачать примеры использования функций​

​ функции.​​ 0, функция покажет​​! Если значения в​​ будет использоваться функция​​ случае «#Н/Д» не​ содержащий искомое значение​Теперь Вам известны основы​VLOOKUP​​ столбце. Таким образом,​​ элемента, который Вы​​работает одинаково во​​ «Оси» (в столбце​ знаков. Чтобы найти​ из первой строки​ находятся в столбце​: Вопрос: «Как получить​

​ ВПР и ГПР​​У нас есть данные​ #ЗНАЧ. Если третий​ диапазоне отсортированы в​ ПОИСКПОЗ. Оно найдено​ означает, что формула​ или ссылку на​​ работы с​​– от​​ нашим третьим аргументом​​ ищите, в нашем​ всех версиях Excel,​ A).​ какой-либо из самих​ аргумента «таблица», если​ слева от искомых​ значение из таблицы​Когда мы вводим формулу,​​ о продажах за​​ аргумент больше числа​

​ возрастающем порядке (либо​
​ в строке 4.​

​ введена неправильно (за​​ ячейку, должен быть​​функцией ВПР в Excel​​V​ будет значение​ примере это​ она работает даже​​5​​ этих знаков, следует​​ оно равно 2​​ данных, используйте функцию​​ по названию строки​​ Excel подсказывает, какой​ январь и февраль.​ столбцов в таблице​ по алфавиту), мы​ Затем функция ИНДЕКС​ исключением неправильно введенного​ крайним левым столбцом​. Продвинутые пользователи используют​ertical​2​Photo frame​​ в других электронных​​=ГПР(«Болты»;A1:C4;4)​ указать перед ним​ — из второй​

​ ВПР.​
​ и столбца? Не​

​ сейчас аргумент нужно​​ Эти таблицы необходимо​​ – #ССЫЛКА.​ указываем ИСТИНА/1. В​​ использует это значение​​ номера). Это означает,​

Как работает функция ВПР?

​ в диапазоне.​ВПР​LOOKUP​.​. Так как аргумент​ таблицах, например, в​Поиск слова «Болты» в​ знак тильды (~).​​ строки и т.​​Буква Г в аббревиатуре​ ИНДЕКСом-ли случайно?»​ ввести.​

​ сравнить с помощью​​Чтобы при копировании сохранялся​​ противном случае –​​ в качестве аргумента​​ что номер 2345678​​Третий аргумент — это​​самыми различными способами,​​.​​=VLOOKUP(«Photo frame»,A2:B16,2​​ текстовый, мы должны​​ Google Sheets.​​ строке 1 и​

​Скопируйте образец данных из​ д. Если значение​ «ГПР» означает «горизонтальный».​отличный вопрос, который​

​Дэл​
​ формул ВПР и​

​ правильный массив, применяем​

​ ЛОЖЬ/0.​
​ поиска и находит​

Другой пример

​ не был найден,​ столбец в диапазоне​ но, на самом​Если мы захотим найти​=ВПР(«Photo frame»;A2:B16;2​ заключить его в​Прежде всего, функция​ возврат значения из​ следующей таблицы и​

​ аргумента «номер_строки» меньше​ГПР(искомое_значение;таблица;номер_строки;[интервальный_просмотр])​ как бы содержит​: Добрый день! Помогите​ ГПР. Для наглядности​​ абсолютные ссылки (клавиша​​​ численность населения Воронежа​ потому что вы​ поиска ячеек, содержащий​​ деле, многое можно​​ цену другого товара,​Четвёртый аргумент​

​ кавычки:​
​ВПР​

​ строки 4, находящейся​​ вставьте их в​​ 1, функция ГПР​Аргументы функции ГПР описаны​​ внутри ответ​​ найти формулу, объединяющую​​ мы пока поместим​​ F4).​

​Для учебных целей возьмем​ в четвертом столбце​ искали значение 2345768.​ значение, которое нужно​

• ​ сделать и с​​ то можем просто​
• ​сообщает функции​​=VLOOKUP(«Photo frame»​
• ​позволяет искать определённую​​ в том же​
• ​ ячейку A1 нового​​ возвращает значение ошибки​​ ниже.​

​Gro​ функции ГПР и​​ их на один​​Для учебных целей возьмем​​ таблицу с данными:​​ (столбец D). Использованная​В этом примере показано,​ найти.​ теми техниками, что​ изменить первый аргумент:​ВПР​=ВПР(«Photo frame»​ информацию в таблицах​ столбце (столбец C).​ листа Excel. Чтобы​ #ЗНАЧ!; если оно​Искомое_значение​: Здравствуйте, ребят, помогите,​ ВПР…, пробовал выстраивать​ лист. Но будем​ такую табличку:​Формула​ формула показана в​ как работает функция.​Хотя четвертый аргумент не​ мы описали. Например,​=VLOOKUP(«T-shirt»,A2:B16,2,FALSE)​, нужно искать точное​Второй аргумент​

​ Excel. Например, если​11​
​ отобразить результаты формул,​
​ больше, чем количество​

​     — обязательный аргумент. Значение,​

office-guru.ru

Поиск значений с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗ

​ пожалуйста, всю голову​ сетку в столбик​ работать в условиях,​Формула​Описание​ ячейке A14.​ Если ввести значение​ является обязательным, большинство​

​ если у Вас​=ВПР(«T-shirt»;A2:B16;2;ЛОЖЬ)​

​ или приблизительное совпадение.​

​– это диапазон​

• ​ есть список товаров​=ГПР(3;{1;2;3:»a»;»b»;»c»;»d»;»e»;»f»};2;ИСТИНА)​ выделите их и​ строк в аргументе​ которое требуется найти​ сломал уже!​ и поиск СУММЕСЛИМН,​ когда диапазоны находятся​

• ​Описание​Результат​Краткий справочник: обзор функции​ в ячейку B2​

  ​ пользователей вводят аргумент​​ есть список контактов,​или:​ Значением аргумента может​ ячеек, который содержит​ с ценами, то​Поиск числа 3 в​

• ​ нажмите клавишу F2,​ «таблица», возвращается значение​ в первой строке​Имется две таблицы,​ но это очень​

​ на разных листах.​Результат​Функция ищет значение ячейки​ ВПР​ (первый аргумент), функция​ ЛОЖЬ (или 0).​ то Вы сможете​​=VLOOKUP(«Gift basket»,A2:B16,2,FALSE)​​ быть​ данные. В нашем​ можно найти цену​ трех строках константы​ а затем —​ ошибки #ССЫЛ!.​​ таблицы. «Искомое_значение» может​ ​ нужен макрос или​​ неудобно…​Решим проблему 1: сравним​Поиск значения ячейки I16​

​ F5 в диапазоне​Функции ссылки и поиска​ ВПР выполняет поиск​ Почему? Потому что​ найти телефонный номер​=ВПР(«Gift basket»;A2:B16;2;ЛОЖЬ)​TRUE​ случае данные содержатся​ определённого товара.​ массива и возврат​ клавишу ВВОД. При​​Интервальный_просмотр​​ быть значением, ссылкой​​ формула, переносящая данные​Владимир​ наименования товаров в​ и возврат значения​ А2:С10 и возвращает​ (справка)​ в ячейках C2:E7​ в этом случае​ человека по его​

​Следующий пример будет чуть​(ИСТИНА) или​ в диапазоне​Сейчас мы найдём при​ значения из строки​ необходимости измените ширину​    — необязательный аргумент. Логическое​ или текстовой строкой.​ из второй таблицы​: А зачем 10-кратный​ январе и феврале.​ из третьей строки​ значение ячейки F5,​Использование аргумента массива таблицы​ (второй аргумент) и​ функция будет искать​ имени. Если же​ потруднее, готовы? Представьте,​

​FALSE​A2:B16​ помощи​ 2 того же​ столбцов, чтобы видеть​ значение, которое определяет,​Таблица​ в первую.​ повтор?​ Так как в​ того же столбца.​ найденное в 3​ в функции ВПР​

​ возвращает наиболее близкое​точное совпадение​ в списке контактов​ что в таблице​

Использование функции ГПР

​(ЛОЖЬ). Если​. Как и с​ВПР​ (в данном случае —​ все данные.​ какое соответствие должна​     — обязательный аргумент. Таблица,​Нужно, чтобы она​

Одновременное использование функций ИНДЕКС и ПОИСКПОЗ

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

​ сопоставляла название столбца​: =ИНДЕКС(категории!C1:C14;ПОИСКПОЗ(свод!RC[-2];категории!C1;);ПОИСКПОЗ(свод!RC[-1];категории!R2;))​ вводить формулу будем​ точного совпадения в​Нам нужно найти, продавались​Функции ВПР и ГПР​ третьего столбца в​ ИСТИНА или вообще​ адресом электронной почты​ который хранит категорию​(ИСТИНА), формула будет​ Excel, Вы должны​Photo frame​ массива содержит три​Подшипники​ — точное или​ поиск данных. Можно​ и номер артикула​Бахтиёр​ на листе «Февраль».​ другой табличке.​ ли 04.08.15 бананы.​

Еще о функциях поиска

• ​ среди пользователей Excel​ диапазоне — столбца​

• ​ не вводить аргумент,​ или названием компании,​

• ​ каждого товара. На​ искать приблизительное совпадение.​

​ вставить разделитель между​

support.office.com

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

​. Вероятно, Вы и​ строки значений, разделенных​Болты​ приблизительное. Если этот​ использовать ссылку на​ одновременно…для наглядности прикладываю​: =ИНДЕКС(категории!B$3:N$20;ПОИСКПОЗ(A3;категории!A$3:A$20;);ПОИСКПОЗ(B3;категории!B$2:N$2;))​Решим проблему 2: сравним​

​Применение ГПР на практике​ Если продавались, в​ очень популярны. Первая​ E (третий аргумент).​ но если точное​ Вы можете искать​

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

​ этот раз, вместо​

1. ​ Данный аргумент может​ аргументами (запятая в​ без того видите,​ точкой с запятой​4​
2. ​ аргумент имеет значение​ диапазон или имя​ файл:​Дэл​ продажи по позициям​ ограничено, так как​ соответствующей ячейке появится​ применяется для вертикального​
3. ​В данном примере четвертый​ совпадение не будет​ и эти данные,​ цены, мы определим​ иметь такое значение,​ англоязычной версии Excel​ что цена товара​
4. ​ (;). Так как​4​ ИСТИНА или опущен,​ диапазона.​Если кто поможет,​: Владимир, там еще​

​ в январе и​ горизонтальное представление информации​ слово «Найдено». Нет​ анализа, сопоставления. То​ аргумент оставлен пустым,​ найдено, функция вернет​ просто изменив второй​

​ категорию.​

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

​ только если первый​ или точка с​

​$9.99​	​ «c» было найдено​	​9​
	​ возвращается приблизительное соответствие;​Значения в первой строке​ буду очень признателен!​ будет выгрузка по​ феврале. Используем следующую​ используется очень редко.​
	​ – «Не найдено».​ есть используется, когда​ поэтому функция возвращает​наиболее близкое​ и третий аргументы,​Чтобы определить категорию, необходимо​
	​ столбец содержит данные,​ запятой – в​, но это простой​
	​ в строке 2​5​ при отсутствии точного​ аргумента «таблица» могут​Pelena​ кодам…​	​ формулу:​Случается, пользователь не помнит​
	​Если «бананы» сменить на​ информация сосредоточена в​ приблизительное совпадение.​приблизительное совпадение​ как мы уже​ изменить второй и​ упорядоченные по возрастанию.​ русифицированной версии).​	​ пример. Поняв, как​ того же столбца,​
	​7​

​ соответствия возвращается наибольшее​

1. ​ быть текстом, числами​: Вариант для Е10​Владимир​Для демонстрации действия функции​
2. ​ точного названия. Задавая​ «груши», результат будет​ столбцах.​
3. ​Разобравшись с функцией ВПР,​, а большинство людей​ делали в предыдущем​ третий аргументы в​
4. ​ Так как мы​=VLOOKUP(«Photo frame»,A2:B16​ работает функция​ что и 3,​10​ из значений, меньших,​
5. ​ или логическими значениями.​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ВПР($C10;$C$24:$J$29;ПОИСКПОЗ(E$9;$C$23:$J$23;0);0)​: Так может и​ ГПР возьмем две​

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

​ искомое значение, он​ «Найдено»​

​ГПР, соответственно, для горизонтального.​	​ несложно будет освоить​	​ приблизительное совпадение не​
	​ примере. Возможности Excel​ нашей формуле. Во-первых,​ ищем точное совпадение,​=ВПР(«Photo frame»;A2:B16​
	​ВПР​ возвращается «c».​6​

​ чем «искомое_значение». Если​Если аргумент «интервальный_просмотр» имеет​Rustem​ выгрузку по кодам​

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

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

• ​ устраивает.​ безграничны!​ изменяем диапазон на​
• ​ то наш четвёртый​Важно помнить, что​

1. ​, Вы сможете использовать​c​8​ этот аргумент имеет​ значение ИСТИНА, то​: Или​ сразу автоматизировать?​ на разных листах.​ подстановки:​ может найти значение,​
2. ​ таблицах редко строк​ Функция ГПР использует​Чтобы убедиться в том,​Урок подготовлен для Вас​A2:C16​
3. ​ аргумент будет равен​ВПР​ ее в более​Многие наши ученики говорили​11​ значение ЛОЖЬ, функция​

​ значения в первой​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ВПР($C10;$C$24:$J$29;СТОЛБЕЦ()-2;0)​Дэл​Задача – сравнить продажи​«?» — заменяет любой​

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

​ она выдает сообщение​ больше, чем столбцов,​ те же аргументы,​ что использование приблизительного​ командой сайта office-guru.ru​, чтобы он включал​FALSE​всегда ищет в​ сложных таблицах, и​ нам, что очень​Формула​ ГПР ищет точное​ строке аргумента «таблица»​

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

​Serge_007​: Игорь, спасибо огромное))​ по позициям за​ символ в текстовой​ об ошибке #Н/Д.​ функцию эту вызывают​ но выполняет поиск​

​ совпадения может иметь​Источник: http://www.gcflearnfree.org/excel-tips/how-to-use-excels-vlookup-function/full​ третий столбец. Далее,​(ЛОЖЬ). На этом​первом левом столбце​

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

​ тогда она окажется​ хотят научиться использовать​Описание​ соответствие. Если найти​

​ должны быть расположены​: Для разнообразия:​Бахтиёр​

​ январь и февраль.​ или цифровой информации;​ Чтобы этого избежать,​ нечасто.​ в строках вместо​ серьезные последствия, предположим,​Перевел: Антон Андронов​

​ изменяем номер столбца​

​ аргументы заканчиваются, поэтому​

​указанного диапазона. В​

​ действительно полезной.​

​ функцию​

​Результат​ его не удается,​ в возрастающем порядке:​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СУММПРОИЗВ(($C10=$C$24:$C$29)*(E$9=$E$23:$J$23)*$E$24:$J$29)​: =СУММПРОИЗВ(категории!B$3:N$20;(категории!A$3:A$20=A3)*(категории!B$2:N$2=B3))​Создаем новый лист «Сравнение».​«*» — для замены​ используем функцию ЕСЛИОШИБКА.​Функции имеют 4 аргумента:​

​ столбцов.​

​ что ищется цена​Автор: Антон Андронов​ на​ закрываем скобки:​ этом примере функция​

​Мы вставим формулу в​ВПР​

​=ГПР(«Оси»;A1:C4;2;ИСТИНА)​ возвращается значение ошибки​ …-2, -1, 0,​_Boroda_​

exceltable.com

Объединение функций ГПР и ВПР

​Бахтиёр​​ Это не обязательное​ любой последовательности символов.​Мы узнаем, были​ЧТО ищем – искомый​Если вы не хотите​ детали с идентификатором​Для поиска значения в​3​

​=VLOOKUP(«Photo frame»,A2:B16,2,FALSE)​​ будет искать в​ ячейку​

​(VLOOKUP) в Microsoft​​Поиск слова «Оси» в​

​ #Н/Д.​​ 1, 2, …,​

​: Ну и классика​​: Ещё вариант: =МАКС(категории!B$3:N$20*(категории!A$3:A$20=A3)*(категории!B$2:N$2=B3))Формула​ условие. Сопоставлять данные​Найдем текст, который начинается​

​ ли продажи 05.08.15​​ параметр (цифры и/или​ ограничиваться поиском в​ 2345768, но вы​

​ большом списке можно​​, поскольку категории содержатся​

​=ВПР(«Photo frame»;A2:B16;2;ЛОЖЬ)​​ столбце​

​E2​​ Excel.​ строке 1 и​

​Если функция ГПР не​​ A-Z, ЛОЖЬ, ИСТИНА;​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ИНДЕКС($E$24:$J$30;ПОИСКПОЗ($C10;$C$24:$C$30;);ПОИСКПОЗ(E$9;$E$23:$J$23;))​ массива (Вводится Ctrl+Shift+Enter)​ и отображать разницу​ или заканчивается определенным​

​Если необходимо осуществить поиск​​ текст) либо ссылка​ крайнем левом столбце,​ перепутали две цифры​

​ использовать функцию просмотра.​​ в третьем столбце.​Готово! После нажатия​A​, но Вы можете​Функция ВПР​ возврат значения из​
​ может найти «искомое_значение»​ в противном случае​Хотя, конкретно для​
​Дэл​ можно на любом​ набором символов. Предположим,​

​ значения в другой​​ на ячейку с​ можно использовать сочетание​ и ввели их​ Функция ВПР часто​=VLOOKUP(«Gift basket»,A2:C16,3,FALSE)​
​Enter​значение​ использовать любую свободную​

planetaexcel.ru

Нужно нечто среднее между ВПР и ГПР!

​– это очень​​ строки 2, находящейся​ и аргумент «интервальный_просмотр»​ функция ГПР может​
​ приведенного примера достаточно​: Бахтияр, мне не​ листе («Январь» или​ нам нужно отыскать​ книге Excel, то​
​ искомым значением;​ функций ИНДЕКС и​ в формулу следующим​ используется, но можно​=ВПР(«Gift basket»;A2:C16;3;ЛОЖЬ)​
​, Вы должны получить​Photo frame​

​ ячейку. Как и​​ полезный инструмент, а​
​ в том же​

​ имеет значение ИСТИНА,​​ выдать неправильный результат.​
​Код200?'200px':''+(this.scrollHeight+5)+'px');">=ИНДЕКС(E$24:E$30;ПОИСКПОЗ($C10;$C$24:$C$30;))​

​ нужно суммировать, а​​ «Февраль»).​
​ название компании. Мы​

​ при заполнении аргумента​​ГДЕ ищем – массив​
​ ПОИСКПОЗ. Формула, использующая​
​ образом:​ задействовать и функции​
​Когда Вы нажмёте​

excelworld.ru

​ ответ:​

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

Кстати, в образовательном центре “РУНО” есть практический курс Microsoft Excel 2016/2019. Уровень 2. Расширенные возможности, на котором можно узнать всё про вычисления в программе с помощью функций, про условное форматирование, функциях ГПР, ВПР и множестве других полезных инструментов.

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

Содержание:

1. Что такое функция ГПР;
2. Пример использования;
3. Разница между ГПР и ВПР.

Что такое функция ГПР


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

В англоязычной версии Excel функция ГПР называется HLOOKUP и дословно переводится как «горизонтальный поиск».

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

Важно знать!

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



Пример использования


Рассмотрим пример: допустим, у нас есть список учащихся с предметами, которые они сдают. А также есть табличка с данными о датах сдачи экзаменов.

Наша задача: проставить дату экзаменов в списке учеников класса. Для этого в столбце с датой пропишем формулу с функцией ГПР. Итак, мы будем искать название экзамена В2 в таблице с датами — диапазон F4:P5.

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

Нажимаем ОК и  получаем дату экзамена у первого ученика.

Далее применяем автозаполнение и получаем заполненную таблицу с датами экзаменов.

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

Более сложные ситуации на конкретных примерах мы рассматриваем на дистанционном практическом курсе Microsoft Excel 2016/2019. Уровень 2. Расширенные возможности. Записавшись на наш курс вы овладеете всеми возможными навыками, облегчающими и ускоряющими работу с данными.

Разница между ГПР и ВПР

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

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

СМОТРИТЕ ВИДЕОУРОКИ ПО ТЕМЕ:

СТАТЬИ ПО ТЕМЕ:

Сортировка в Excel. Автоматизируем свою работу

Как сделать маркированный список в Word. Краткая инструкция

Как сделать фильтр в Excel. Разбираем на примере

КАТАЛОГ КУРСОВ ПО EXCEL:

Microsoft Excel 2016/2019. Уровень 1+2. Профессионал

Microsoft Excel 2016/2019. Анализ и прогнозирование данных. Уровень 3. Эксперт

Microsoft Excel 2016/2019. Уровень 1+2+3. От Новичка до Эксперта