Функцыя ГПРА ў Excel, кароткая для гарызантальнага пошуку, можа дапамагчы вам знайсці канкрэтную інфармацыю ў вялікіх табліцах дадзеных , такія як інвентарны спіс дэталяў або вялікі спіс членаў кантактаў.
ГПР працуе так жа функцыі ВПР ў Excel. Адзінае адрозненне ў тым, што ВПР шукае дадзеныя ў слупках, а ГПР шукае дадзеныя ў радках.
Пасля крокаў у тэмах падручніка ніжэй пакрокавай з дапамогай функцыі HLOOKUP, каб знайсці канкрэтную інфармацыю ў базе дадзеных Excel.
Апошні крок падручніка ахоплівае паведамленні пра памылкі, якія звычайна ўзнікаюць з функцыяй HLOOKUP.
падручнік тэмы
- Увод дадзеных Tutorial
- Запуск функцыі HLOOKUP
- пошук Значэнне
- табліца Масіў
- Row Index Number
- дыяпазон прагляду
- Выкарыстанне ГПР для вымання дадзеных
- Агульныя паведамленні пра памылкі для Excel ГПР
01 з 09
Увод дадзеных Tutorial
Пры ўводзе дадзеных у табліцу Excel, ёсць некаторыя агульныя правілы, каб прытрымлівацца:
- Кожны раз, калі гэта магчыма, не пакідайце пустыя радкі або слупкі пры ўводзе дадзеных.
- Пакінуўшы пустыя радкі і слупкі ў табліцах дадзеных могуць зрабіць яго цяжка выкарыстоўваць шэраг функцый Excel - уключаючы ГПР.
- Калі ласка, увядзіце дадзеныя ў радках.
- Пры кладцы вашага працоўнага ліста, спіс імёнаў, якія апісваюць дадзеныя ў першай радку табліцы і, на пад гэтым, сам дадзеныя.
- Калі ёсць больш чым адзін шэраг дадзеных, спіс іх адзін за іншым у радках з назвай для кожнай серыі дадзеных у першай вочку злева.
Для гэтага ўрока
- Калі ласка, увядзіце дадзеныя, як паказана на малюнку вышэй у клеткі D4 да I5.
- Першы радок дадзеных (радок 4) змяшчае назву дэталяў. Другі радок (радок 5) кошт кожнай часткі.
02 з 09
Запуск функцыі HLOOKUP
Перад запускам функцыі HLOOKUP гэта, як правіла, добрая ідэя, каб дадаць загалоўкі ліста, каб паказаць, якія дадзеныя здабываюцца з дапамогай ГПР. Для гэтага ўрока увядзіце наступныя загалоўкі ў ячэйкі названых. Функцыя ГПР і дадзеныя, якія ён здабывае з базы дадзеных будзе знаходзіцца ў вочках справа ад гэтых загалоўкаў.
- D1 - Part Name
E1 - Цана
Хоць можна проста ўвесці HLOOKUP функцыі ў вочка ў лісце , многія людзі лічаць , што прасцей у выкарыстанні дыялогавага акна функцыі.
Для гэтага ўрока
- Націсніце на вочка E2 , каб зрабіць яго актыўную вочка . Тут мы пачнем функцыю HLOOKUP.
- Перайдзіце на ўкладку Формулы.
- Выберыце Lookup & Reference з стужкай , каб адкрыць функцыю расчыняецца спісу.
- Націсніце на ГПР ў спісе , каб адкрыць дыялогавае акно функцыі.
Дадзеныя, якія мы ўводзім у чатыры пустых радкоў у дыялогавым акне будзе фармаваць аргументы функцыі HLOOKUP. Гэтыя аргументы гавораць функцыю, якую інфармацыю мы пасля і дзе ён павінен шукаць, каб знайсці яго.
03 з 09
пошук Значэнне
Першы аргумент з'яўляецца Lookup_Value. Гэта сведчыць аб ГПРЕ, які элемент у базе дадзеных мы шукаем інфармацыю. Искомое_значение размешчана ў першай радку выбранага дыяпазону .
Інфармацыя, якая будзе вяртаць HLOOKUP заўсёды з адной і той жа калонкі базы дадзеных як Lookup_Value.
Искомое_значение можа быць тэкставай радком, лагічнае значэнне (ісціна або хлусня толькі), лік, або спасылка на вочка на значэнне.
Для гэтага ўрока
- Націсніце на Lookup_Value лініі ў дыялогавым акне
- Націсніце на вочка D2 , каб дадаць спасылку на вочка ў радку Lookup_Value. Гэта клетка, дзе мы будзем уводзіць назву дэталі, пра якія мы шукаем інфармацыю.
04 з 09
табліца Масіў
Аргумент таблица_массив з'яўляецца дыяпазон ад дадзеных , што функцыя ГПР шукае , каб знайсці інфармацыю. Звярніце ўвагу , што гэты дыяпазон не павінен ўключаць у сябе ўсе радкі , ці нават першы шэраг у базе дадзеных .
Table_array павінен утрымліваць, па меншай меры, два радкі дадзеных, хоць, з першага радка, якая змяшчае искомое_значение (гл папярэдні крок).
Калі ўвесці спасылкі на ячэйкі для гэтага аргументу , што гэта добрая ідэя выкарыстоўваць абсалютныя спасылкі на вочка. Спасылкі Абсалютныя ячэйкі абазначаюцца ў Excel з дапамогай знака даляра ($). Прыкладам можа быць $ E $ 4.
Калі вы не карыстаецеся абсалютныя спасылкі і скапіяваць функцыю HLOOKUP да іншых клетак, то ёсць шанец, вы атрымаеце паведамленне пра памылку ў вочках, у якой функцыя скапіяваць.
Для гэтага ўрока
- Націсніце на лініі таблица_массив ў дыялогавым акне.
- Вылучыце ячэйкі Е4 I5 ў табліцы , каб дадаць гэты дыяпазон у лінію таблица_массива. Гэта дыяпазон дадзеных, ГПР будзе шукаць.
- Націсніце клавішу F4 на клавіятуры , каб зрабіць дыяпазон абсалютнага ($ E $ 4: $ I $ 5).
05 з 09
Row Index Number
Нумар індэкс радкі аргументу (номер_строки) паказвае , какая радок table_array змяшчае дадзеныя , вы пасля гэтага .
Напрыклад:
- калі ўвесці 1 у індэксны нумар радка, ГПР вяртае значэнне з першага слупка ў table_array;
- калі лік індэкса радкі роўна 2, ён вяртае значэнне з другога шэрагу ў table_array.
Для гэтага ўрока
- Націсніце на лініі номер_строки ў дыялогавым акне
- Калі ласка, увядзіце 2 у гэтым радку , каб паказаць , што мы хочам , каб ГПР для вяртання інфармацыі з другога радка масіва табліцы.
06 з 09
дыяпазон прагляду
Аргумент диапазон_просмотра лагічнае значэнне (ПРАЎДА або ХЛУСНЯ толькі) , што паказвае на тое, ці вы хочаце HLOOKUP знайсці дакладнае або прыблізнае супадзенне з Lookup_Value .
- Калі TRUE або калі гэты аргумент апушчаны, ГПР будзе выкарыстоўваць набліжаны матч, калі ён не можа знайсці дакладнае супадзенне з Lookup_Value. Калі дакладнае супадзенне не знойдзена, ГПР вяртае наступнае найбольшае значэнне, якое менш, чым Lookup_Value.
- Калі FALSE, ГПР будзе выкарыстоўваць толькі дакладнае супадзенне з Lookup_Value. Пры наяўнасці двух або больш значэнняў у першым слупку табліцы table_array , якія адпавядаюць искомое_значение, выкарыстоўваецца першае знойдзенае значэнне. Калі дакладнае супадзенне не знойдзена, то # N / вяртаецца памылка.
Для гэтага ўрока
- Націсніце на лініі диапазон_просмотра ў дыялогавым акне
- Увядзiце словы ілжыва ў гэтым радку , каб паказаць , што мы хочам , каб ГПР вярнуць дакладнае супадзенне дадзеных , якія мы шукаем.
- Націсніце кнопку OK, каб закрыць дыялогавае акно.
- Калі вы выканалі ўсе крокі гэтага ўроку вы павінны зараз мець поўную функцыю HLOOKUP ў вочку E2.
07 з 09
Выкарыстанне ГПР для вымання дадзеных
Пасля таго , як HLOOKUP функцыя была завершана яна можа быць выкарыстана для здабывання інфармацыі з базы дадзеных .
Для гэтага увядзіце імя элемента , які вы хочаце атрымаць у Lookup_Value вочка і націсніце клавішу ENTER на клавіятуры.
ГПР выкарыстоўвае радок Index Number, каб вызначыць, які элемент дадзеных павінен быць паказаны ў вочку Е2.
Для гэтага ўрока
- Націсніце на вочка Е1 ў табліцы.
- Увядзіце Болт ў вочка E1 і націсніце клавішу ENTER на клавіятуры.
- Кошт ніта - $ 1,54 - павінна адлюстроўвацца ў вочку Е2.
Праверце функцыю HLOOKUP далей, увёўшы назвы іншых частак у вочку E1 і параўнанне дадзеных, якiя вяртаюцца ў вочку E2 з цэнамі, пералічаных у вочках E5 да I5.
08 з 09
Паведамленні пра памылкі Excel ГПР
Наступныя паведамленні пра памылкі, звязаныя з ГПР.
# N / A памылка:
- Гэтая памылка адлюстроўваецца, калі значэнне пошуку не знойдзена ў першым слупку масіва табліцы.
- Ён таксама будзе адлюстроўвацца , калі дыяпазон для табліцы масіва аргументаў недакладна. Калі гэты аргумент ўтрымлівае пустыя радкі вышэй масіва табліцы.
#REF!:
- Гэтая памылка адлюстроўваецца, калі радок нумар індэкса аргумент больш, чым колькасць радкоў у масіве табліц. У малюнку вышэй, #REF! памылка адбываецца таму , што лік індэкс радкі усталёўваецца роўным 3 , а ёсць толькі два радкі ў table_array.
Гэта завяршае падручнік па стварэнні і выкарыстанні функцыі HLOOKUP ў Excel 2007.
09 з 09
Прыклад Выкарыстанне HLOOKUP функцыі Excel 2007 у
Калі ласка, увядзіце наступныя дадзеныя ў вочках указаны:
вочка дадзеных
- D3 - Part
- E3 - Пеленг
- F3 - Болт
- G3 - Cog
- H3 - Снасці
- I3 - Пральная машына
- D4 - Цана
- E4 - $ 17,34
- F4 - $ 1,54
- G4 - $ 20,21
- H4 - $ 23,56
- I4 - $ 1,43
Націсніце на вочка E1 - месца, дзе будуць адлюстроўвацца вынікі.
Перайдзіце на ўкладку Формулы.
Выберыце Lookup & Reference са стужкай, каб адкрыць функцыю расчыняецца спісу.
Націсніце на ГПР ў спісе, каб адкрыць дыялогавае акно функцыі.
У дыялогавым акне, націсніце на погляд уверх _value лініі.
Націсніце на вочка D1 ў табліцы. Тут мы будзем уводзіць назву частцы мы хочам, каб цана.
У дыялогавым акне, націсніце на лініі таблица_массив.
Вылучыце ячэйкі E3 ў I4 ў табліцы, каб увесці дыяпазон у дыялогавае акно. Гэта дыяпазон дадзеных, якія мы хочам HLOOKUP шукаць.
У дыялогавым акне, націсніце на лініі номер_строки.
Калі ласка, увядзіце лік 2, каб паказаць, што дадзеныя, якія мы хочам вернуты ў радку 2 table_array.
У дыялогавым акне, націсніце на лініі диапазон_просмотра.
Увядзiце словы фальшывага каб паказаць, што мы хочам, каб дакладнае адпаведнасць нашых запытаных дадзеных.
Націсніце кнопку ОК.
У вочку D1 з табліцы, увядзіце слова ніт.
Значэнне $ 1,54 павінна з'явіцца ў вочку E1 адлюстроўваючы цану ніта, як паказана на table_array.
Калі пстрыкнуць па вочку E1, поўнай функцыя = ГПР (D1, E3: I4, 2, FALSE) з'яўляецца ў радку формул над лістом.