Изтеглете примерната работна книга
Този урок демонстрира как да използвате Функция Excel HLOOKUP в Excel, за да търсите стойност.
Преглед на функцията HLOOKUP
Функцията HLOOKUP Hlookup означава хоризонтално търсене. Той търси стойност в горния ред на таблица. След това връща стойност определен брой редове надолу от намерената стойност. Той е същият като vlookup, освен че търси стойности хоризонтално, а не вертикално.
(Забележете как се появяват входовете на формулата)
Синтаксис и вход на функцията HLOOKUP:
| 1 | = HLOOKUP (lookup_value, table_array, row_index_num, range_lookup) |
lookup_value - Стойността, която искате да търсите.
таблица_масив -Таблицата, от която да се извличат данни.
row_index_num - Номер на ред, от който да се извличат данни.
range_lookup -[по избор] Булева стойност, която показва точно съвпадение или приблизително съвпадение. По подразбиране = TRUE = приблизително съвпадение.
Какво представлява функцията HLOOKUP?
Като една от по -старите функции в света на електронните таблици, функцията HLOOKUP се използва за извършване Зоризонтален Търсения. Той има няколко ограничения, които често се преодоляват с други функции, като INDEX/MATCH. Освен това повечето таблици са изградени вертикално, но има няколко пъти, когато е полезно да се търси хоризонтално.
Основен пример
Нека да разгледаме извадка от данни от класната книга. Ще разгледаме няколко примера за извличане на информация за конкретни студенти.
Ако искаме да намерим в кой клас е Боб, бихме написали формулата:
| 1 | = HLOOKUP ("Боб", A1: E3, 2, FALSE) |
Важни неща, които трябва да запомните, са, че елементът, който търсим (Боб), трябва да е в първия ред на нашия диапазон за търсене (A1: E3). Казахме на функцията, че искаме да върнем стойност от 2nd ред от диапазона за търсене, който в този случай е ред 2. Накрая посочихме, че искаме да направим an точно съвпадение като поставите False като последен аргумент. Тук отговорът ще бъде „Четене“.
Страничен връх: Можете също да използвате числото 0 вместо False като краен аргумент, тъй като те имат същата стойност. Някои хора предпочитат това, тъй като е по -бързо да се пише. Просто знайте, че и двете са приемливи.
Преместени данни
За да добавите някои пояснения към първия ни пример, елементът за търсене не трябва да е в ред 1 на електронната ви таблица, а само в първия ред от диапазона ви за търсене. Нека използваме същия набор от данни:
Сега нека намерим оценката за класа на науката. Нашата формула би била
| 1 | = HLOOKUP ("Наука", A2: E3, 2, FALSE) |
Това все още е валидна формула, тъй като първият ред от нашия диапазон за търсене е ред 2, където ще бъде намерен нашият термин за търсене на „Наука“. Връщаме стойност от 2nd ред от диапазона за търсене, който в този случай е ред 3. След това отговорът е „A-“.
Използване на заместващи символи
Функцията HLOOKUP поддържа използването на заместващи символи „*“ и „?“ когато правите търсения. Да приемем например, че сме забравили как се изписва името на Франк и просто искахме да търсим име, което започва с „F“. Можем да напишем формулата
| 1 | = HLOOKUP ("F*", A1: E3, 2, FALSE) |
Това би могло да намери името Франк в колона Е и след това да върне стойността от 2nd относителен ред. В този случай отговорът ще бъде „Наука“.
Неточно съвпадение
През повечето време ще искате да се уверите, че последният аргумент в HLOOKUP е False (или 0), така че да получите точно съвпадение. Има обаче няколко случая, когато може да търсите неточно съвпадение. Ако имате списък с сортирани данни, можете също да използвате HLOOKUP, за да върнете резултата за елемента, който е или същият, или следващият най -малък. Това често се използва при работа с увеличаващи се диапазони от числа, като например в данъчна таблица или комисионни.
Да приемем, че искате да намерите данъчната ставка за доход, въведен в клетка Н2. Формулата в H4 може да бъде:
| 1 | = HLOOKUP (H2, B1: F2, 2, TRUE) |
Разликата в тази формула е, че последният ни аргумент е „Вярно“. В нашия конкретен пример можем да видим, че когато нашите индивиди въведат доход от 45 000 долара, те ще имат данъчна ставка от 15%.
Забележка: Въпреки че обикновено искаме точно съвпадение с False като аргумент, забравяте да посочите 4th аргумент в HLOOKUP, по подразбиране е True. Това може да доведе до неочаквани резултати, особено когато се занимавате с текстови стойности.
Динамичен ред
HLOOKUP изисква от вас да дадете аргумент, от който ред искате да върнете стойност, но може да възникне повод, когато не знаете къде ще бъде редът или искате да позволите на потребителя да промени от кой ред да се върне. В тези случаи може да бъде полезно да използвате функцията MATCH, за да определите номера на реда.
Нека да разгледаме отново нашия пример за книга с някои входове в G2 и G4. За да получим номера на колоната, можем да напишем формула на
| 1 | = MATCH (G2, A1: A3, 0) |
Това ще се опита да намери точната позиция на „Степен“ в диапазона A1: A3. Отговорът ще бъде 3. Знаейки това, можем да го включим във функция HLOOKUP и да напишем формула в G6 така:
| 1 | = HLOOKUP (G4, A1: E3, MATCH (G2, A1: A3, 0), 0) |
Така че функцията MATCH ще оцени на 3 и това казва на HLOOKUP да върне резултат от 3rd ред в диапазона A1: E3. Като цяло получаваме желания резултат от „С“. Нашата формула сега е динамична, тъй като можем да променим или реда, който да гледаме, или името, което да търсим.
Ограничения на HLOOKUP
Както бе споменато в началото на статията, най -големият спад на HLOOKUP е, че изисква терминът за търсене да бъде намерен в най -лявата колона на диапазона за търсене. Въпреки че има някои фантастични трикове, които можете да направите, за да преодолеете това, общата алтернатива е да използвате INDEX и MATCH. Тази комбинация ви дава повече гъвкавост и понякога дори може да бъде по -бързо изчисление.
HLOOKUP в Google Таблици
Функцията HLOOKUP работи абсолютно същото в Google Sheets като в Excel:
допълнителни бележки
Използвайте функцията HLOOKUP, за да извършите хоризонтално търсене. Ако вече сте запознати с функцията VLOOKUP, HLOOKUP работи по абсолютно същия начин, освен че търсенето се извършва хоризонтално, а не вертикално. HLOOKUP търси точно съвпадение (range_lookup = FALSE) или най -близкото съвпадение, което е равно или по -малко от lookup_value (range_lookup = TRUE, само числови стойности) в първия ред на масива table_array. След това връща съответна стойност, n брой редове под съвпадението.
Когато използвате HLOOKUP за намиране на точно съвпадение, първо определяте идентифицираща стойност, която искате да търсите като lookup_value. Тази идентификационна стойност може да бъде SSN, идентификационен номер на служител, име или друг уникален идентификатор.
След това определяте диапазона (наречен таблица_масив), който съдържа идентификаторите в горния ред и всички стойности, които в крайна сметка искате да търсите в редовете под него. ВАЖНО: Уникалните идентификатори трябва да са в горния ред. Ако не са, трябва или да преместите реда отгоре, или да използвате MATCH / INDEX вместо HLOOKUP.
Трето, дефинирайте номера на ред (row_index) от таблица_масив които искате да се върнете. Имайте предвид, че първият ред, съдържащ уникалните идентификатори, е ред 1. Вторият ред е ред 2 и т.н.
И накрая, трябва да посочите дали да търсите точно съвпадение (FALSE) или най -близко съвпадение (TRUE) в range_lookup. Ако е избрана опцията за точно съвпадение и точното съвпадение не е намерено, се връща грешка (#N/A). За да се върне формулата празна или „не е намерена“, или друга стойност вместо стойността на грешката (#N/A), използвайте функцията IFERROR с HLOOKUP.
За да използвате функцията HLOOKUP за връщане на приблизително съвпадение: range_lookup = ИСТИНСКО. Тази опция е налична само за числови стойности. Стойностите трябва да бъдат сортирани във възходящ ред.
Примери за HLOOKUP във VBA
Можете също да използвате функцията HLOOKUP във VBA. Тип:application.worksheetfunction.hlookup (lookup_value, table_array, row_index_num, range_lookup)
Изпълнение на следните VBA изявления
| 123456 | Range ("G2") = Application.WorksheetFunction.HLookup (Range ("C1"), Range ("A1: E3"), 1)Диапазон ("H2") = Application.WorksheetFunction.HLookup (Range ("C1"), Range ("A1: E3"), 2)Диапазон ("I2") = Application.WorksheetFunction.HLookup (Range ("C1"), Range ("A1: E3"), 3)Диапазон ("G3") = Application.WorksheetFunction.HLookup (Range ("D1"), Range ("A1: E3"), 1)Диапазон ("H3") = Application.WorksheetFunction.HLookup (Range ("D1"), Range ("A1: E3"), 2)Диапазон ("I3") = Application.WorksheetFunction.HLookup (Range ("D1"), Range ("A1: E3"), 3) |
ще даде следните резултати
За аргументите на функцията (lookup_value и т.н.) можете или да ги въведете директно във функцията, или да определите променливи, които да използвате вместо това.
Връщане към списъка с всички функции в Excel
