змест
10-ы дзень марафону 30 функцый Excel за 30 дзён мы прысвяцім вывучэнню функцыі ГПР (GPR). Гэтая функцыя вельмі падобная на ВПР (VLOOKUP), працуе толькі з элементамі гарызантальнага спісу.
Няшчасная функцыя ГПР (GLOW) не так папулярны, як яго брат, так як у большасці выпадкаў дадзеныя ў табліцах размешчаны вертыкальна. Памятаеце, калі ў апошні раз вы хацелі шукаць радок? Як наконт вяртання значэння з таго ж слупка, але размешчанага ў адным з радкоў ніжэй?
У любым выпадку, давайце асаблівасці ГПР (GPR) заслужаная хвіліна славы і больш уважліва азнаёмцеся з інфармацыяй аб гэтай функцыі, а таксама з прыкладамі яе выкарыстання. Памятаеце, калі ў вас ёсць цікавыя ідэі або прыклады, падзяліцеся імі ў каментарах.
Функцыя 10: HLOOKUP
функцыя ГПР (HLOOKUP) шукае значэнне ў першым радку табліцы і вяртае іншае значэнне з таго ж слупка ў табліцы.
Як я магу выкарыстоўваць функцыю HLOOKUP (HLOOKUP)?
Паколькі функцыя ГПР (HLOOKUP) можа знаходзіць дакладнае або прыблізнае значэнне ў радку, а затым можа:
- Знайдзіце агульны аб'ём продажаў для абранага рэгіёну.
- Знайдзіце паказчык, актуальны для абранай даты.
Сінтаксіс HLOOKUP
функцыя ГПР (HLOOKUP) мае наступны сінтаксіс:
HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
ГПР(искомое_значение;таблица;номер_строки;интервальный_просмотр)
- искомое_значение (lookup_value): Значэнне, якое трэба знайсці. Можа быць значэннем або спасылкай на ячэйку.
- таблица_массив (табліца): пошукавая табліца. Можа быць спасылкай на дыяпазон або найменным дыяпазонам, які змяшчае 2 радкі ці больш.
- індэкс_радка (нумар_радка): Радок, які змяшчае значэнне, якое вяртае функцыя. Усталёўваецца па нумары радка ў табліцы.
- дыяпазон_пошуку (range_lookup): Выкарыстоўвайце FALSE або 0, каб знайсці дакладнае супадзенне; для прыблізнага пошуку TRUE (ІСЦІНА) або 1. У апошнім выпадку радок, у якой функцыя шукае, павінна быць адсартаваная ў парадку ўзрастання.
Пасткі HLOOKUP (GPR)
як ВПР (VLOOKUP), функцыя ГПР (HLOOKUP) можа працаваць павольна, асабліва пры пошуку дакладнага супадзення тэкставага радка ў несартаванай табліцы. Па магчымасці выкарыстоўвайце прыблізны пошук у табліцы, адсартаванай па першым радку ў парадку ўзрастання. Вы можаце спачатку прымяніць функцыю МАТЧ (Больш выкрыта) або COUNTIF (COUNTIF), каб пераканацца, што значэнне, якое вы шукаеце, нават існуе ў першым радку.
Іншыя функцыі, як індэкс (ІНДЭКС) і МАТЧ (MATCH) таксама можна выкарыстоўваць для атрымання значэнняў з табліцы і яны больш эфектыўныя. Мы паглядзім на іх пазней падчас нашага марафону і паглядзім, наколькі магутнымі і гнуткімі яны могуць быць.
Прыклад 1: Пошук значэнняў продажаў для абранага рэгіёну
Яшчэ раз нагадаю, што функцыя ГПР (HLOOKUP) шукае толькі значэнне ў верхнім радку табліцы. У гэтым прыкладзе мы знойдзем агульны аб'ём продажаў для абранага рэгіёну. Для нас важна атрымаць правільнае значэнне, таму мы выкарыстоўваем наступныя налады:
- Назва рэгіёну ўводзіцца ў ячэйку B7.
- Рэгіянальная табліца пошуку мае два радкі і ахоплівае дыяпазон C2:F3.
- Выніковыя продажы знаходзяцца ў радку 2 нашай табліцы.
- Апошні аргумент мае значэнне FALSE, каб знайсці дакладнае супадзенне пры пошуку.
Формула ў ячэйцы C7:
=HLOOKUP(B7,C2:F3,2,FALSE)
=ГПР(B7;C2:F3;2;ЛОЖЬ)
Калі назва рэгіёна не знойдзена ў першым радку табліцы, вынік функцыі ГПР (GPR) будзе #AT (#Н/Д).
Прыклад 2: Знайдзіце меру для выбранай даты
Звычайна пры выкарыстанні функцыі ГПР (HLOOKUP) патрабуе дакладнага супадзення, але часам больш падыходзіць прыблізнае супадзенне. Напрыклад, калі паказчыкі змяняюцца ў пачатку кожнага квартала, а ў якасці загалоўкаў слупкоў выкарыстоўваюцца першыя дні гэтых кварталаў (гл. малюнак ніжэй). У гэтым выпадку з дапамогай функцыі ГПР (HLOOKUP) і прыблізнае супадзенне, вы знойдзеце індыкатар, актуальны для дадзенай даты. У гэтым прыкладзе:
- Дата запісваецца ў ячэйцы C5.
- Табліца пошуку індыкатара мае два радкі і знаходзіцца ў дыяпазоне C2:F3.
- Пошукавая табліца сартуецца па радку даты ў парадку ўзрастання.
- Паказчыкі запісваюцца ў радок 2 нашай табліцы.
- Апошні аргумент функцыі мае значэнне TRUE для пошуку прыблізнага супадзення.
Формула ў ячэйцы D5:
=HLOOKUP(C5,C2:F3,2,TRUE)
=ГПР(C5;C2:F3;2;ИСТИНА)
Калі дата не знойдзена ў першым радку табліцы, функцыя ГПР (HLOOKUP) знойдзе бліжэйшае вялікае значэнне, меншае за аргумент искомое_значение (пошукавае_значэнне). У гэтым прыкладзе патрэбнае значэнне Сакавік 15. Яго няма ў радку даты, таму формула прыме значэнне Студзень 1 і вярнуцца 0,25.
