Ранее в некоторых статьях мы уже рассматривали применение функции ВПР и ГПР (Статья ; ). Давайте сегодня более подробно остановимся на синтаксисе функции ГПР() .
По сравнению с функцией ВПР() функция ГПР() используется не так часто, но в ряде случаев без этой функции не обойтись.
Функция ГПР выполняет поиск значения в первой строке нашей таблицы (или поименованного диапазона данных) значений и возвращает значение, находящееся в том же столбце в заданной строке таблицы. Функция ГПР используется, когда сравниваемые значения расположены в первой строке таблицы данных, а возвращаемые - на некоторой позиции (строк) ниже. Иными словами функция ГПР это горизонтальный поиск.
Рассмотрим функцию ГПР детально:
ГПР(искомое_значение , таблица, номер_строки , [интервальный_просмотр] )
Аргументы функции ГПР :
Искомое_значение
- значение, которое нам нужно найти в первой строке таблицы (может быть значением, ссылкой или текстовой строкой).
Таблица - наша таблица, в которой производится поиск данных (можно имя диапазона), значения в первой строке аргумента «таблица» могут быть текстом, числами или логическими значениями.
Номер_строки - номер строки в таблице, из которой будет возвращено соответствующее значение. Если значение аргумента «номер_строки» равно 1, возвращается значение из первой строки аргумента «таблица», если оно равно 2 - из второй строки и т. д. Если значение аргумента «номер_строки» меньше 1, функция ГПР возвращает значение ошибки #ЗНАЧ!; если оно больше, чем количество строк в аргументе «таблица», возвращается значение ошибки #ССЫЛ!.
Если аргумент «интервальный_просмотр» имеет значение ИСТИНА, то значения в первой строке аргумента «таблица» должны быть расположены в возрастающем порядке: ... — 2, -1, 0, 1, 2, ..., A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ГПР может выдать неправильный результат. Если аргумент «интервальный_просмотр» имеет значение ЛОЖЬ, таблица может быть не отсортирована.
В текстовых строках регистр букв не учитывается.
Рассмотрим пример, простая таблица данных:
Допустим мы хотим выводить отдельно один из столбиков целиком по определенному условию. Пусть условие будет записано в ячейке Е1 (один из заголовков столбца):
Теперь, для того чтобы вывести в столбец F строки из столбца «Овощи» запишем формулу ГПР() :
=ГПР($E$1 ;$A$1:$C$5 ;2;0) где последний аргумент («интервальный_просмотр») равен 0 , т.к. нас интересует точное совпадение $E$1 в первой строке таблицы $A$1:$C$5.
Вот что у нас получилось:
Или по условию «Остальное»:
Очень надеемся, что наша статья помогла Вам в решении Вашей проблемы. Будем благодарны, если Вы нажмете +1 и/или Мне нравится внизу данной статьи или поделитесь с друзьями с помощью кнопок расположенных ниже.
Привет, друзья. Как часто вам приходится для какого-то значения искать соответствие в таблице Эксель? Например, нужно в справочнике найти адрес человека, или в прайсе – цену товара. Если такие задачи встречаются – этот пост именно для вас!
Я выполняю подобные процедуры каждый день и без описанных ниже функций мне действительно пришлось бы туго. Берите на заметку и применяйте их в работе!
Поиск в таблице Эксель, функции ВПР и ГПР
Роль этих функций в жизни обычного пользователя сложно переоценить. Теперь можно легко найти в таблице данных подходящую запись и вернуть соответствующее ей значение.
Синтаксис функции ВПР такой: =ВПР(Искомое_значение; таблица_для_поиска; номер_выводимого_столбца; [тип_сопоставления]) . Рассмотрим аргументы:
- Искомое значение – значение, которое будем искать. Это обязательный аргумент;
- Таблица для поиска – тот массив ячеек, в котором будет поиск. Столбец с искомыми значениями должен быть первым в этом массиве. Это тоже обязательный аргумент;
- Номер выводимого столбца – порядковый номер столбца (начиная с первого в массиве), из которого функция выведет данные при совпадении искомых значений. Обязательный аргумент;
- Тип сопоставления – выберите «1» (или «ИСТИНА») для нестрогого совпадения, «0» («ЛОЖЬ») – для полного совпадения. Аргумент необязателен, если его упустить – будет выполнен поиск нестрогого совпадения .
Поиск точного совпадения с помощью ВПР
Посмотрим на примере, как работает функция ВПР, когда выбран тип сопоставления «ЛОЖЬ», поиск точного совпадения. В массиве В5:Е10 указаны основные средства некой компании, их балансовая стоимость, инвентарный номер и место расположения. В ячейке В2 указано наименование, для которого нужно в таблице найти инвентарный номер и поместить его в ячейку С2 .
Функция ВПР в Excel
Запишем формулу: =ВПР(B2;B5:E10;3;ЛОЖЬ) .
Здесь первый аргумент указывает, что в таблице нужно искать значение из ячейки В2 , т.е. слово «Факс». Второй аргумент говорит, что таблица для поиска — в диапазоне В5:Е10 , а искать слово «Факс» нужно в первом столбце, т.е. в массиве В5:В10 . Третий аргумент сообщает программе, что результат расчета содержится в третьем столбце массива, т.е. D5:D10 . Четвёртый аргумент равен «ЛОЖЬ», т.е. требуется полное совпадение.
И так, функция получит строку «Факс» из ячейки В2 и будет искать его в массиве В5:В10 сверху вниз. Как только совпадение будет найдено (строка 8), функция вернёт соответствующее значение из столбца D , т.е. содержимое D8 . Именно это нам и требовалось, задача решена.
Если искомое значение не будет найдено, функция вернёт .
Поиск неточного совпадения с помощью ВПР
Благодаря этой опции в работе ВПР, мы можем избежать сложных формул, чтобы найти нужный результат.
В массиве В5:С12 указаны процентные ставки по кредитам в зависимости от суммы займа. В ячейке В2 Указываем сумму кредита и хотим получить в С2 ставку для такой сделки. Задача сложна тем, что сумма может быть любой и вряд ли будет совпадать с указанными в массиве, поиск по точному совпадению не подходит:
Тогда запишем формулу нестрогого поиска: =ВПР(B2;B5:C12;2;ИСТИНА) . Теперь из всех представленных в столбце В данных программа будет искать ближайшее меньшее. То есть, для суммы 8 000 будет отобрано значение 5000 и выведен соответствующий процент.
Нестрогий поиск ВПР в Excel
Для корректной работы функции нужно отсортировать первый столбец таблицы по возрастанию. Иначе, она может дать ошибочный результат.
Функция ГПР имеет такой же синтаксис, как и ВПР, но ищет результат не в столбцах, а в строках. То есть, просматривает таблицы не сверху вниз, а слева направо и выводит заданный номер строки, а не столбца.
Поиск данных с помощью функции ПРОСМОТР
Функция ПРОСМОТР работает аналогично ВПР, но имеет другой синтаксис. Я использую её, когда таблица данных содержит несколько десятков столбцов и для использования ВПР нужно дополнительно просчитывать номер выводимой колонки. В таких случаях функция ПРОСМОТР облегчает задачу. И так, синтаксис: =ПРОСМОТР(Искомое_значение; Массив_для_поиска; Массив_для_отображения ) :
- Искомое значение – данные или ссылка на данные, которые нужно искать;
- Массив для поиска – одна строка или столбец, в котором ищем аналогичное значение. Данный массив обязательно сортируем по возрастанию;
- Массив для отображения – диапазон, содержащий данные для выведения результатов. Естественно, он должен одного размера с массивом для поиска.
При такой записи вы даёте не относительную ссылку массива результатов. А прямо на него указываете, т.е. не нужно предварительно просчитывать номер выводимого столбца. Используем функцию ПРОСМОТР в первом примере для функции ВПР (основные средства, инвентарные номера): =ПРОСМОТР(B2;B5:B10;D5:D10) . Задача успешно решена!
Функция «ПРОСМОТР» в Microsoft Excel
Поиск по относительным координатам. Функции ПОИСКПОЗ и ИНДЕКС
Еще один способ поиска данных – комбинирование функций ПОИСКПОЗ и ИНДЕКС.
Первая из них, служит для поиска значения в массиве и получения его порядкового номера: ПОИСКПОЗ(Искомое_значение; Просматриваемый_массив; [ Тип сопоставления ] ). Аргументы функции:
- Искомое значение – обязательный аргумент
- Просматриваемый массив – одна строка или столбец, в котором ищем совпадение. Обязательный аргумент
- Тип сопоставления – укажите «0» для поиска точного совпадения, «1» — ближайшее меньшее, «-1» — ближайшее большее. Поскольку функция проводит поиск с начала списка в конец, при поиске ближайшего меньшего – отсортируйте столбец поиска по убыванию. А при поиске большего – сортируйте его по возрастанию.
Позиция необходимого значения найдена, теперь можно вывести его на экран с помощью функции ИНДЕКС(Массив; Номер_строки; [Номер_столбца] ) :
- Массив – аргумент указывает из какого массива ячеек нужно выбрать значение
- Номер строки – указываете порядковый номер строки (начиная с первой ячейки массива), которую нужно вывести. Здесь можно записать значение вручную, либо использовать результат вычисления другой функции. Например, ПОИСКПОЗ.
- Номер столбца – необязательный аргумент, указывается, если массив состоит из нескольких столбцов. Если аргумент упущен, формула использует первый столбец таблицы.
Теперь скомбинируем эти функции, чтобы получить результат:
Функции ПОИСКПОЗ и ИНДЕКС в Эксель
Вот такие способы поиска и выведения на экран данных существуют в Excel. Далее, Вы можете использовать их в расчетах, использовать в презентации, выполнять с ними операции, указывать в качестве аргументов других функций и др.
Чувствуете, как растут и крепчают Ваши знания и умения? Тогда не останавливайтесь, продолжайте читать ! В следующем посте мы будем рассматривать : будет сложно и интересно!
Цель данной статьи - показать, как работает функция Excel ГПР . Рассмотрим работу функций на примере расчета прогноза с логарифмическим трендом и сезонностью.
По ходу статьи мы:
- Рассчитаем значения логарифмического тренда и продлим тренд в будущее;
- Выделим сезонность;
- Прогнозный тренд скорректируем сезонностью. Для этого воспользуемся функцией Excel =ГПР и получим прогноз.
1. Рассчитаем значения логарифмического тренда и продлим тренд в будущее.
Выделим из объема продаж по месяцам значения логарифмического тренда и продлим тренд в будущее.
Для этого воспользуемся функцией Excel =Предсказ:
Разберем формулу Excel =ПРЕДСКАЗ(
- LN(D2) - X- ссылка но номер периода X, причем делаем логарифм Х;
- $D$4:$BE$4 - известные значения y - зафиксированная ссылка на ряд с объемом продаж за весь период;
- LN($D$2:$BE$2) - известные значения x - зафиксированная ссылка на диапазон с номерами периодов - X под логарифмом;
- Обратите внимание, что ряд "известные значения y" = "известные значения x"
Способы расчета логарифмического тренда в Excel в статье 5 способов расчета логарифмического тренда "
Для чего мы пронумеровываем периоды и о временных рядах читайте статью
Протягиваем формулу до конца ряда и на 12 месяцев вперед, получаем значения тренда для каждого момента времени в прошлом и прогнозный тренд на 12 месяцев в будущее:
2. Выделим сезонность
1. Определим отношение Объема продаж к тренду - объем продаж разделим на логарифмический тренд:
2. Рассчитаем среднее отношение объема продаж к тренду для каждого месяца в году.
2.1. Определим номер месяца для каждого периода в ряду:
2.2. Пронумеруем номера месяцев для сезонности:
2.3. Воспользовавшись функцией Excel=СУММЕСЛИ и =СЧЕТЕСЛИ (подробнее о работе с этими функциями читайте в статье "Формулы Excel "СУММЕСЛИ" и "СЧЕТЕСЛИ" "), рассчитаем сезонность для каждого месяца - среднее отклонение отношений объема продаж от логарифмического тренда:
2.3. Рассчитаем нормирующий коэффициент - среднее значений полученной сезонности
В среднем сезонность должна быть равна 1. А у нас получилась 0,995027
2.4. Рассчитаем сезонность.
Для того, чтобы сезонность стала равной 1, разделим полученную сезонность на нормирующий коэффициент, который у нас равен 0,995027:
Получили месячные коэффициенты сезонности приведенные в среднем к 1.
3. Прогнозный тренд скорректируем сезонностью, для этоговоспользуемся функцией Excel =ГПР - получим прогноз.
Мы рассчитали:
- Значения логарифмического тренда и продлили тренд в будущее на 12 месяцев
- Сезонность.
Теперь рассчитаем прогноз.
Прогноз = значения логарифмического тренда для будущих периодов мы умножаем на коэффициент сезонности.
Для поиска нужного коэффициента воспользуемся функцией Excel ГПР.
Разберем ГПР(искомое значение; таблица; номер строки; интервальный просмотр) по частям:
- Искомое значение - BF5 - номер прогнозного месяца, для которого нам необходимо найти коэффициент сезонности в таблице и вернуть его значение в ячейку:
- Таблица - $D$12:$O$13 - зафиксированная ссылка на таблицу с сезонностью, в которой в первой строке находятся номера месяцев, а во второй коэффициенты сезонности:
Формула ГПР ищет номер месяца в первой строке таблицы и возвращает коэффициент сезонности из второй , но для этого еще надо указать строку из которой возвращаем сезонность:
- 2 - номер строки - номер строки в таблице, из которого возвращаем коэффициент сезонности. В первой строке таблице с сезонностью номера месяцев, во второй коэффициенты сезонности
- интервальный просмотр ставим = 0
Нажимаем ввод и получаем прогноз - логарифмический тренд умноженный на коэффициент сезонности:
Итак, в функцию ГПР мы передали номер прогнозного месяца, для которого необходимо найти коэффициент сезонности. Далее функция ГПР в первой строке таблицы с сезонностью нашла искомый номер месяца и вернула нам коэффициент сезонности из второй строки этого же столбца таблицы. Коэффициент сезонности, найденный с помощью функции ГПР, мы умножили на прогнозный тренд и получили прогноз.
Добрый день читатель!
В этой статье мы изучим еще одну функцию с раздела , это Excel . Эта функция является зеркальным отражением , хотя и используется в работе не так часто, хотя в некоторых случаях обойтись без этой функции невозможно.
Если вы знаете принцип работы функции ВПР, то можете догадаться что Excel , отличается только диапазоном поиска, «Г» — означает горизонтальный, а значит и поиск будет горизонтальным. Функция ГПР производит поиск нужных значений в первой строчке вашей таблицы (или же ) и возвращает значение, которое находится в указанной строке таблицы в том же столбике. То есть функцию ГПР нужно использовать в том случае, когда значение, которые нам нужно сравнить располагаются в первой строчке вашей таблицы данных, а значение, которые нужно возвращать находятся в строках ниже. Как видите с описания, поиск идет по строкам, то есть в горизонтальной плоскости.
Теперь рассмотрим, как работает Excel более пристально, и начнем эту процедуру с изучения синтаксиса функции:
=ГПР (искомое нами значение; таблица поиска; номер строки; [интервальный просмотр]), где
Обращаю ваше внимание, что регистр букв в текстовых значениях не учитывается, а также есть возможность использовать подстановочные знаки: знак вопроса (?) , для замены любого одного знака и звёздочку (*) , для замены любой очередности знаков. Если у вас есть необходимость найти один из вышеуказанных знаков, то перед ним вам нужно указать знак тильда (~).
А теперь рассмотрим на примере, как используется Excel
, что бы рассмотреть принцип работы этой функции. Итак, у нас есть таблица с видами мебели и их вариациями, вот на основе таких данных и будет экспериментировать: =ГПР(«Диван»;A1:C4;2;ИСТИНА)
, производим поиск слова «Диван» в строчке 1 и возвращает значение из строки 2, которая находится в том же столбике, результат получаем «Амур».
=ГПР(«Кровать»;A1:C4;3;ЛОЖЬ)
, производим поиск в первой строке и возвращаем значение из 3 строчки, которая находится в том же столбце, в результате получаем «Марроко».
=ГПР(«К»;A1:C4;3;ИСТИНА)
, производим поиск буквы «К» в первой строке и возвращаем значение из 3 строчки того же столбца. Поскольку эту букву найти не удалось, вернулось ближайшее из меньших значений «Диван», результат получаем «Кром».
=ГПР(«Спальня»;A1:C4;4)
, производим поиск слова «Спальня» в первой строке и возвращаем значение из 4 строчки, которая находится в том же столбике, наш результат будет «Касар».
Я очень надеюсь, что моя статья о том, как используется Excel
помогла решить Вашу проблему. С другими функциями вы можете познакомится в . Буду очень благодарен за поставленную оценку или комментарий в социальных сетях которые вы увидите ниже.
До встречи на страницах сайта!
"Деньги — это средство иметь буквально все, за исключением искреннего друга, преданной жены и здоровья.
"
П. Вебер
Функция ВПР (В ертикальный ПР осмотр) для многих (но, надеюсь, не для вас) является вершиной эволюции в Excel. Что ж, есть много ситуаций, когда эта формула может оказаться полезной. ВПР имеет следующий синтаксис:
= ВПР (искомое_значение; массив; номер_столбца; тип_поиска)
искомое_значение
массив - диапазон ячеек, состоящий, как минимум, из двух столбцов (в противном случае данная формула теряет всякий смысл) и, как правило, большого количества строк
номер_столбца - номер столбца, указанного масива, из которого будут возвращаться данные
тип_поиска - переключатель типа поиска. Если вы укажите 0 или ЛОЖЬ (FALSE), то функция будет искать точное соответствие с искомым значением, а если укажете 1 или ИСТИНА (TRUE), то формула вернёт максимальное значение, которое МЕНЬШЕ или РАВНО искомого параметра.
ВПР осуществляет поиск в первом столбце массива и, если значение найдено, то возвращает результат, взятый на пересечении соответствующей строки и указанного в третьем параметре столбца массива.
Схема
Некоторые замечания о ВПР
Пример | Замечание |
Искомое значение ищется в ПЕРВОМ столбце массива. Это фундаментальное ограничение ВПР , которое серьёзно вредит универсальности данной функции. Многие пользователи, прикипев к ВПР , и, не зная более гибких альтернатив, вынужденно подстраивают свои таблицы под её возможности, делая нужный столбец первым. | |
Поиск оснанавливается на первом совпадении результата, если тип поиска = 0 (точное совпадение) и на последнем, если тип поиска = 1 (примерное совпадение). Таким образом у вас есть возможность при помощи ВПР возвращать либо первую совпавшую строку, либо последнюю. Однако имейте в виду, что при нечётком поиске (тип поиска = 1) первый столбец массива должен быть отсортирован по возрастанию . | |
ВПР поддерживает использование символов подстановки (* и ? ) в параметре искомого значения. "? " - заменяет собой любой один символ, а "* " - заменяет любое количество любых символов. Если используете символы подстановки, то четвёртый параметр должен быть 0 (ЛОЖЬ). | |
Нечёткий поиск часто применяют для распределения значений по каким либо диапазонам. Например, рассмотрим классический пример вычисления значения скидки для клиента в зависимости от заказанного количества товара. Как видите, эта задача идеально вписывается в возможности нечёткого поиска ВПР . Не забывайте только про сортировку. | |
При поиске текстовых строк ВПР не видит разницы в регистре букв. | |
Если значение не найдено, то формула возвращает код ошибки #Н/Д (#N/A). При помощи дополнительной функции ЕСЛИОШИБКА (IFERROR) эти ошибки из эстетических или иных соображений можно перехватывать и подставлять, как в этом примере, пустую строку (или что угодно другое) - в этом случае пользователи не тревожатся попусту, разглядывая загадочные #Н/Д , и, как следствие значительно реже, отвлекают квалифицированных людей от важного. |
Функция ГПР (HLOOKUP)
Функция ГПР (Г оризонтальный ПР осмотр) - это тот же самый ВПР , в котором строки заменили на столбцы и наоборот. ГПР имеет следующий синтаксис:
= ГПР (искомое_значение; массив; номер_строки; тип_поиска)
искомое_значение - константа или ссылка на ячейку, значение которой вы собираетесь искать
массив - диапазон ячеек, состоящий, как минимум, из двух строк (в противном случае данная формула теряет всякий смысл) и, как правило, большого количества столбцов
номер_строки - номер строки, указанного масива, из которой будут возвращаться данные
тип_поиска - переключатель типа поиска. Если вы укажите 0 или ЛОЖЬ (FALSE), то функция будет искать точное соответствие с искомым значением, а если укажете 1 или ИСТИНА (TRUE), то формула вернёт максимальное значение, которое МЕНЬШЕ или РАВНО искомого параметра.
ГПР осуществляет поиск в первой строке массива и, если значение найдено, то возвращает результат, взятый на пересечении соответствующего столбца и указанного в третьем параметре строки массива.