Урок за Excel VLOOKUP за начинаещи
Шарлот Милър
⚡ Умно обобщение
Урокът за Excel VLOOKUP обяснява как функцията за вертикално търсене търси в първата колона на таблица и връща съответстваща стойност от друга колона. Това ръководство обхваща синтаксиса, точните и приблизителните съвпадения, кръстосаните търсения, често срещаните грешки и съвременната алтернатива на XLOOKUP.
Какво е VLOOKUP?
VLOOKUP (V означава Vertical - Вертикално) е вградена функция на Excel, която установява връзка между колони в електронна таблица. Тя ви позволява да търсите стойност в една колона и да връщате съответната стойност от друга колона в същия ред.
Синтаксис и аргументи на VLOOKUP
Преди да приложите VLOOKUP, е полезно да разберете структурата на формулата. Функцията приема четири аргумента и следва постоянен модел във всяка версия на Excel.
- търсена_стойност — стойността, която искате да намерите (препратка към клетка или литерал).
- table_array — диапазонът от клетки, съдържащ колоната за търсене и колоната за връщане.
- col_index_num — номерът на колоната в table_array, от която да се върне стойността (1 е най-лявата).
- търсене_обхват — FALSE за точно съвпадение, TRUE (или пропуснато) за приблизително съвпадение на сортирани данни.
Важно: Търсената стойност трябва да се намира в най-лявата колона на table_array, а VLOOKUP търси само отляво надясно.
Използване на VLOOKUP
Когато трябва да намерите конкретна информация в голяма електронна таблица или да извлечете един и същ вид стойност многократно, VLOOKUP спестява значително време в сравнение с ръчното филтриране.
Помислете за Таблица на фирмените заплати поддържа се от финансовия екип. Започвате с известна информация – индекс – и използвате VLOOKUP, за да извлечете неизвестната стойност.
Например, вече знаете името на служителя:
И искате да проверите заплатата на служителя:
Електронна таблица в Excel за горния пример:
За да намерим неизвестната заплата на служителя, въвеждаме служителя Code което вече е налично.
Чрез прилагане на VLOOKUP, стойността на заплатата, съответстваща на този служител Code се появява автоматично.
Как да използвате функцията VLOOKUP в Excel
Следвайте това ръководство стъпка по стъпка, за да приложите функцията VLOOKUP в Excel:
Стъпка 1) Придвижете се до целевата клетка
Щракнете върху клетката, където искате да се покаже заплатата на избрания служител – в този пример, клетка H3.
Стъпка 2) Въведете функцията VLOOKUP =VLOOKUP()
Въведете функцията в клетката. Започнете със знак за равенство (който казва на Excel, че следва формула) и след това ключовата дума VLOOKUP: =VLOOKUP().
Скобите съдържат набора от аргументи (данните, от които функцията се нуждае).
VLOOKUP изисква четири аргумента:
Стъпка 3) Първи аргумент — търсената стойност
Първият аргумент е препратката към клетката за стойността, която искате да търсите. В този случай, служителят Code е търсената стойност, така че първият аргумент е H2 - клетката, чието съдържание Excel трябва да съвпадне.
Стъпка 4) Втори аргумент — табличният масив
Това се отнася до блока от стойности, които ще се търсят, известен в Excel като табличен масив или таблица за търсене. В нашия пример таблицата за търсене работи от B2 до E25.
ЗАБЕЛЕЖКА: Колоната за търсене трябва да е най-лявата колона на вашия табличен масив.
Стъпка 5) Трети аргумент — col_index_num
Това указва на VLOOKUP коя колона в масива от таблицата съдържа върнатата стойност. „Заплатата на служителя“ се намира в четвъртата колона, така че индексът на колоната е 4.
Стъпка 6) Четвърти аргумент — точно или приблизително съвпадение
Последният аргумент е флагът за търсене в диапазон. Той контролира дали VLOOKUP връща точно или приблизително съвпадение. Тук искаме точно съвпадение (FALSE).
- FALSE — точно съвпадение.
- TRUE — приблизително съвпадение.
Стъпка 7) Натиснете Enter
Натиснете Enter, за да завършите формулата. Първоначално ще видите грешка, защото няма служител Code все още не е въведено в H2.
След като въведете валиден служител Code В H2 клетката връща съответната заплата на служителя.
Накратко, формулата казва на Excel, че известните стойности се намират в най-лявата колона на данните (Служител Code). VLOOKUP след това сканира таблицата и връща стойността от четвъртата колона на съответстващия ред — „Заплата на служителя“.
Този пример обхваща точни съвпадения (ключовата дума FALSE). Следващият раздел обяснява приблизителните съвпадения.
VLOOKUP за приблизителни съвпадения (TRUE ключова дума като последен параметър)
Да разгледаме сценарий, в който таблица изчислява отстъпки за клиенти, които не купуват точно десетки или стотици артикули.
Както е показано по-долу, една компания прилага отстъпки за количества от 1 до 10 000:
Клиентът рядко купува точно 100 или 1,000 единици. Режимът на приблизително съвпадение позволява на VLOOKUP да намери най-близката по-ниска стойност, вместо да настоява за точна цифра. Стъпки:
Стъпка 1) Щракнете върху клетката, където ще се намира функцията VLOOKUP – препратка към клетка I2.
Стъпка 2) Въведете =VLOOKUP() в клетката и добавете аргументите в скобите.
Стъпка 3) Аргумент 1: Въведете препратката към клетка, чиято стойност трябва да се съпостави с таблицата за търсене.
Стъпка 4) Аргумент 2: Изберете таблицата за търсене — тук колоните „Количество“ и „Отстъпка“.
Стъпка 5) Аргумент 3: Въведете индекса на колоната в таблицата за търсене, от която да се върне съответстващата стойност.
Стъпка 6) Аргумент 4: Задайте последния аргумент на TRUE за приблизителни съвпадения.
Стъпка 7) Натиснете Enter. Формулата вече се прилага към клетката. Когато въведете произволно количество, Excel връща диапазона на отстъпката въз основа на приблизителното съвпадение.
ЗАБЕЛЕЖКА: Ако оставите четвъртия аргумент празен, Excel по подразбиране приема стойността TRUE (приблизително съвпадение). За приблизителни съвпадения колоната за търсене трябва да бъде сортирана във възходящ ред.
Функция Vlookup, приложена между 2 различни листа, поставени в една и съща работна книга
Сега разгледайте работна книга с два листа. Лист 1 изброява служителите Code, Име и длъжност; Лист 2 изброява служителя Code и Заплата на служителя.
ЛИСТ 1:
ЛИСТ 2:
Целта е да се консолидират всички данни в Лист 1, както е показано по-долу:
VLOOKUP може да агрегира данни, така че служителите Code, Име и Заплата се показват заедно в един лист.
Започваме от Лист 2, защото той предоставя два аргумента - колоната „Заплата на служителите“ е тук и индексът на колоната е 2.
Искаме да намерим заплатата, която е подходяща за всеки служител Code.
Данните обхващат от A2 до B25 - това е нашият табличен масив.
Стъпка 1) Преминете към Лист 1 и въведете показаните заглавия.
Стъпка 2) Щракнете върху клетката до „Заплата на служителя“ – клетка F3 – където ще се постави формулата VLOOKUP.
Въведете функцията VLOOKUP: =VLOOKUP().
Стъпка 3) Аргумент 1: Въведете F2 — клетката, съдържаща служителя Code да съвпада в таблицата за търсене.
Стъпка 4) Аргумент 2: Таблицата за търсене се намира на другия лист, така че я посочете с името на листа: Лист2!A2:B25.
Стъпка 5) Аргумент 3: Въведете индекса на колоната в таблицата за търсене, която съдържа върнатата стойност.
Стъпка 6) Аргумент 4: Използвайте FALSE за точно съвпадение, защото искаме точната заплата, която съответства на всеки служител. Code.
Стъпка 7) Натиснете Enter. Когато въведете служител Code, клетката връща съответната заплата, извлечена от Лист 2.
Често срещани грешки и корекции на VLOOKUP
Дори опитни потребители се сблъскват с грешки във VLOOKUP. Най-често срещаните и бързи решения:
- # N / A — VLOOKUP не може да намери търсената стойност. Проверете за допълнителни интервали, несъответстващи типове данни (числа, съхранени като текст) или дали стойността наистина съществува в първата колона на table_array.
- #REF! — col_index_num е по-голям от броя на колоните в table_array. Намалете индекса на колоната или разширете диапазона.
- #VALUE! — col_index_num е по-малко от 1 или аргументът е невалиден. Проверете синтаксиса на формулата.
- Върнат е грешен резултат — четвъртият аргумент е TRUE или е пропуснат, но колоната за търсене е несортирана. Превключете на FALSE или сортирайте колоната във възходящ ред.
- Заключени препратки — когато копирате формула надолу, използвайте абсолютни препратки (например $B$2:$E$25), за да не се отклонява table_array.
VLOOKUP срещу XLOOKUP: Кой да използвате?
Microsoft въведе XLOOKUP в Microsoft 365 и Excel 2021 като модерен заместител на VLOOKUP. Премахва няколко ограничения на VLOOKUP и вече е препоръчителният избор в поддържаните версии.
| Особеност | ВОЛОКУП | XLOOKUP |
|---|---|---|
| Посока на търсене | Само отляво надясно | Всяка посока (наляво, надясно, нагоре, надолу) |
| Тип на съвпадението по подразбиране | Приблизително (ВЯРНО) | Точен |
| Обработка при липса на намерен резултат | Връщания #N/A | Вграден аргумент if_not_found |
| Индекс на колоната | Твърдо кодиран номер | Препратка към диапазон от върнати колони |
| Наличност | Всички версии на Excel | Microsoft 365, Excel 2021, Excel за уеб |
Кога да изберете VLOOKUP: Работната книга трябва да се изпълнява в Excel 2019 или по-стара версия, в противен случай поддържате стари формули. Кога да изберете XLOOKUP: Създавате нови работни книги в модерен Excel и искате търсене отляво, по-чиста обработка на грешки и точно съвпадение по подразбиране. Научете повече за функциите за търсене в Уроци за Excel серия.
Заключение
Трите сценария по-горе обясняват как VLOOKUP работи за точни съвпадения, приблизителни съвпадения и кръстосани препратки. Упражнявайте се със собствените си набори от данни, за да развиете плавност. VLOOKUP остава важна функция в MS Excel за ефективно управление на данни, а XLOOKUP разширява този набор от инструменти в съвременния Excel.
