01 от 03
Намерете приблизителни съвпадения на данните с VLOOKUP на Excel
Как функционира функцията VLOOKUP
Функцията VLOOKUP на Excel, която служи за вертикално търсене , може да се използва за търсене на конкретна информация, разположена в таблица с данни или база данни.
VLOOKUP нормално връща едно изходно поле на данни. Как става това:
- Вие давате име или lookup_value, което казва на VLOOKUP в кой ред или запис на таблицата с данни да търси желаните данни
- Доставяте номера на колоната , известен като col_index_num , на данните, които търсите
- Функцията търси за lookup_value в първата колона на таблицата с данни
- След това VLOOKUP намира и връща информацията, която търсите, от друго поле на същия запис, като използва номера на предоставената колона
Сортиране на данните първо
Въпреки че не винаги се изисква, обикновено е най-добре първо да се сортира обхватът от данни, които VLOOKUP търси във възходящ ред, като използва първата колона от диапазона за клавиша за сортиране.
Ако данните не са сортирани, VLOOKUP може да върне неправилен резултат.
Синтаксисът и аргументите на функцията VLOOKUP
Синтаксисът на функцията се отнася до оформлението на функцията и включва името на функцията, скобите и аргументите .
Синтаксисът за функцията VLOOKUP е:
= VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
lookup _value - (задължително) стойността, която искате да търсите - като например количеството, продадено в изображението по-горе
table_array - (задължително) това е таблицата с данни, която VLOOKUP търси, за да намери информацията, която след това.
- Масата за таблицата трябва да съдържа най-малко две колони от данни
- Първата колона обикновено съдържа lookup_value
col_index_num - (задължително) номерът на колоната на желаната от вас стойност.
- Номерирането започва с колона search_key като колона 1
- Ако col_index_num е настроен на номер, по-голям от броя на колоните, избрани в аргумента table_array a #REF! грешката се връща от функцията
range_lookup - (по избор) показва дали диапазонът е сортиран във възходящ ред.
- Данните в първата колона се използват като ключ за сортиране
- Булева стойност - TRUE или FALSE са единствените приемливи стойности
- Ако е пропуснато, стойността е зададена по подразбиране на TRUE
- Ако е зададена стойност TRUE или е пропусната и първата колона от диапазона не е сортирана във възходящ ред, може да възникне неправилен резултат
- Ако е зададена стойност TRUE или е пропусната и точното съвпадение за търсенето _value не е намерено, най-близкият мач, който е по-малък по размер или стойност, се използва като клавиш за търсене
- Ако е зададена на FALSE, VLOOKUP приема само точното съвпадение за _value за търсене . Ако има няколко стойности за съвпадение, първата стойност за съвпадение се връща
- Ако е зададена стойност FALSE и не е намерена съвпадаща стойност за search_key , функцията се връща # грешка # N / A
Пример: Намерете процента на отстъпка за закупеното количество
Примерът в изображението по-горе използва функцията VLOOKUP, за да намери дисконтовия процент, който варира в зависимост от количеството закупени артикули.
Примерът показва, че отстъпката при закупуването на 19 артикула е 2%. Това е така, защото колоната Количество съдържа диапазони от стойности. В резултат VLOOKUP не може да намери точно съвпадение. Вместо това трябва да се намери приблизително съответствие, за да се върне правилният дисконтов процент.
За да намерите приблизителни съвпадения:
- да сортирате данните в таблицата_реклама във възходящ ред;
- задайте аргумента range_lookup на TRUE
В примера се използва следната формула, съдържаща функцията VLOOKUP, за да се намери отстъпката за количествата закупени стоки.
= VLOOKUP (С2 $ C $ 5: $ D $ 8,2, вярно)
Въпреки че тази формула може просто да бъде въведена в клетка на работен лист, друга опция, използвана със стъпките, изброени по-долу, е да се използва диалоговият прозорец на функцията, за да се въведат нейните аргументи.
- Използването на диалоговия прозорец често улеснява правилното въвеждане на аргументите на функцията.
Отваряне на диалоговия прозорец VLOOKUP
Стъпките, използвани за въвеждане на функцията VLOOKUP, показана на изображението по-горе, в клетка В2 са:
- Кликнете върху клетката В2, за да стане активната клетка - мястото, където се показват резултатите от функцията VLOOKUP
- Кликнете върху раздела Формули .
- Изберете Lookup & Reference от лентата, за да отворите падащия списък на функциите
- Кликнете върху VLOOKUP в списъка, за да изведете диалоговия прозорец на функцията
02 от 03
Въвеждане на аргументите на функцията на VLOOKUP на Excel
Посочване на референции за клетки
Аргументите за функцията VLOOKUP се въвеждат в отделни линии на диалоговия прозорец, както е показано на изображението по-горе.
Референтните клетки, които трябва да се използват като аргументи, могат да бъдат въведени в правилната линия или, както е направено в стъпките по-долу, посочването, което включва маркиране на желания диапазон от клетки с показалеца на мишката, може да се използва за въвеждане в диалоговия прозорец ,
Предимствата на използването на насочване включват:
- Това е по-бързо, отколкото да пишете;
- По-малко грешки са направени, въвеждайки правилните референтни клетки.
Използване на относителни и абсолютни клетъчни референции с аргументи
Не е необичайно да използвате няколко копия на VLOOKUP, за да върнете различна информация от същата таблица с данни. За да се улесни това, често VLOOKUP може да се копира от една клетка в друга. Когато функциите се копират в други клетки, трябва да се внимава да се гарантира, че получените референтни клетки са правилни предвид новото местоположение на функцията.
На изображението по-горе знаците за долар ( $ ) заобикалят референтните клетки за аргумента table_array , което показва, че те са абсолютни референтни клетки , което означава, че те няма да се променят, ако функцията бъде копирана в друга клетка. Това е желателно, тъй като множество копия на VLOOKUP ще направят същата таблица с данни като източник на информация.
Референтната клетка, използвана за lookup_value, от друга страна , не е заобиколена от знаци за долар, което я прави относителна клетка. Относителните референтни клетки се променят, когато се копират, за да отразят новото си местоположение по отношение на позицията на данните, за които се отнасят.
Въвеждане на аргументите за функциите
- Кликнете върху реда Lookup _value в диалоговия прозорец VLOOKUP
- Кликнете върху клетката С2 в работния лист, за да въведете тази клетка като аргумент search_key
- Кликнете върху реда Table_array на диалоговия прозорец
- Маркирайте клетки C5 до D8 в работния лист, за да влезете в този диапазон като аргумент Table_array - заглавията на таблиците не са включени
- Натиснете клавиша F4 на клавиатурата, за да промените диапазона на абсолютните референтни клетки
- Кликнете върху колоната Col_index_num на диалоговия прозорец
- Въведете a 2 на този ред като аргумент Col_index_num , тъй като дисконтовите проценти се намират в колона 2 на аргумента Table_array
- Кликнете върху диапазона Range_lookup на диалоговия прозорец
- Въведете думата " Истина" като аргумент " Range_lookup"
- Натиснете клавиша Enter на клавиатурата, за да затворите диалоговия прозорец и да се върнете в работния лист
- Отговорът 2% (дисконтов процент за закупеното количество) трябва да се появи в клетка D2 на работния лист
- Когато кликнете върху клетка D2, пълната функция = VLOOKUP (C2, $ C $ 5: $ D $ 8,2, TRUE) се появява в лентата за формули над работния лист
Защо VLOOKUP връща 2% като резултат
- В примера колоната Количество не съдържа точно съвпадение за стойността на search_key от 19.
- Тъй като is_sorted аргументът е настроен на TRUE, VLOOKUP ще намери приблизително съответствие с стойността на search_key .
- Най-близката стойност в размер, която е все още по-малка от стойността на search_key от 19, е 11.
- VLOOKUP, следователно, търси процента на отстъпка в реда, съдържащ 11 и в резултат на това връща дисконтов процент от 2%.
03 от 03
Excel VLOOKUP не работи: # N / A и #REF Грешки
VLOOKUP Съобщения за грешка
Следните съобщения за грешка са свързани с VLOOKUP.
A # N / A ("стойност не е налична") Грешка се показва, ако:
- Стойността на търсенето не се намира в първата колона на аргумента за диапазона
- Аргументът Table_array е неточен. Например аргументът може да включва празни колони от лявата страна на диапазона
- Аргументът Range_lookup е зададен на FALSE, а точно съвпадение за аргумента search_key не може да бъде намерено в първата колона от диапазона
- Аргументът Range_lookup е настроен на TRUE и всички стойности в първата колона на диапазона са по-големи от класа за търсене
A #REF! ("посочване извън обхвата") Грешка се показва, ако:
- Аргументът Col_index_num е по-голям от броя на колоните в таблицата