Функцията VLOOKUP на Excel се използва за намиране и връщане на информация от таблица с данни въз основа на избраната от вас стойност за търсене.
Обикновено VLOOKUP изисква стойността на търсенето да бъде в най-лявата колона на таблицата с данни и функцията връща друго поле от данни, разположено в същия ред вдясно от тази стойност.
Въпреки това, като комбинираме VLOOKUP с функцията CHOOSE, може да се създаде формула за търсене вляво, която:
- Разрешава търсената стойност да бъде от всяка колона в таблицата с данни
- Връща информация, разположена във всяка колона вляво от стойността за търсене
Забележка: Тези инструкции се отнасят за версии Excel 2019, 2016, 2013, 2010 и Excel за Office 365.
01 от 05Инструкция: Използване на функциите VLOOKUP и CHOOSE в лявата формула за търсене
За да създадете формулата за търсене вляво в примерното изображение, използвайте формулата:
= VLOOKUP ($ D $ 2, ИЗБЕРИ ({1,2}, $ F: $ F, $ D: $ D), 2, окачени)
В този пример формулата дава възможност да се намери частта, предоставена от различните компании, изброени в колона 3 на таблицата с данни.
Задачата на функцията CHOOSE във формулата е да подтикне VLOOKUP да вярва, че колона 3 е колона 1. В резултат на това името на компанията може да се използва като стойност за търсене, за да се намери името на частта, предоставена от всяка компания.
Въведете данните за урока
- Въведете заглавието доставчик в клетката D1.
- Въведете заглавието част в клетката E1.
- Въведете таблицата с данните, показани в изображението по-горе, в клетките D4 до F9
- Редове 2 и 3 са оставени празни, за да отговарят на критериите за търсене и левицата за търсене, създадена по време на този урок
Отворете диалоговия прозорец VLOOKUP
Въпреки че е възможно да въведете формулата директно в клетка F1 в работния лист, много хора изпитват затруднения със синтаксиса на формулата.
В този случай е по-безопасно да използвате диалоговия прозорец VLOOKUP. Почти всички функции на Excel имат диалогов прозорец, който ви позволява да въвеждате всеки от аргументите на функцията на отделен ред.
- Кликнете върху клетката E2 на работния лист. E2 е мястото, където ще бъдат показани резултатите от лявата формула за търсене.
- Кликнете върху Формули раздела на лентата.
- Кликнете върху Търсене и справка опция в лентата, за да отворите падащия списък на функциите.
- Кликнете върху VLOOKUP в списъка, за да изведете диалоговия прозорец на функцията.
Въвеждане на аргументи в диалоговия прозорец VLOOKUP
Аргументите на функцията са стойностите, използвани от функцията за изчисляване на резултата.
В диалоговия прозорец на функцията името на всеки аргумент е на отделен ред, последван от поле, в което да въведете стойност.
Въведете следните стойности за всеки от аргументите на VLOOKUP на правилния ред на диалоговия прозорец, както е показано в придружаващото изображение.
Стойността на търсенето
Стойността на търсенето е полето на информацията, използвана за търсене в табличния масив. VLOOKUP връща друго поле от данни от същия ред като стойността за търсене.
Този пример използва референция за клетката до мястото, където името на фирмата ще бъде въведено в работния лист. Предимството на това е, че го прави лесно да се промени името на фирмата без да се редактира формулата.
- Кликнете върху търсена_стойност линия в диалоговия прозорец.
- Кликнете върху клетката D2 за да добавите тази клетка към търсена_стойност линия.
- Натисни F4 клавиша на клавиатурата, за да се направи абсолютната клетка - $ D $ 2.
Абсолютни клетъчни референции
Абсолютните референтни клетки се използват за аргументите за стойността на търсенето и аргументите в таблицата, за да се предотвратят грешки, ако формулата за търсене се копира в други клетки в работния лист.
Въвеждане на функцията CHOOSE
Аргументът за таблицата с масива е блокът от съседни данни, от който се извлича конкретна информация.
Обикновено VLOOKUP изглежда само вдясно от аргумента за стойността на търсенето, за да открие данни в табличния масив. За да го разгледате наляво, VLOOKUP трябва да бъде измамен, като пренареждате колоните в таблицата с помощта на функцията CHOOSE.
В тази формула функцията CHOOSE изпълнява две задачи:
- Той създава табличен масив, който е само с две колони широк (колони D и F).
- Той променя реда отляво на колоните в табличния масив, така че колоната F идва и колоната D е втора.
Въвеждане на функции
При ръчно въвеждане на функции, всеки от аргументите на функцията трябва да бъде разделен със запетая.
- В диалоговия прозорец VLOOKUP функцията кликнете върху Table_array линия.
- Въведете следния ИЗБИРАМ функция:ИЗБЕРИ ({1,2}, $ F: $ F, $ D: $ D)
Колонен индекс номер
Обикновено индексният номер на колоната показва коя колона в табличния масив съдържа данните, които следвате. В тази формула обаче се отнася до реда на колоните, зададени от функцията CHOOSE.
Функцията CHOOSE създава табличен масив с широчина от две колони с колона F, последвана от колона D. Тъй като търсената информация - името на частта - е в колона D, стойността на аргумента за индекса на колоната трябва да бъде зададена на 2.
- Кликнете върху Col_index_num линия в диалоговия прозорец.
- Въведете a 2 в този ред.
Обхват на търсенето
Argument Range_lookup на VLOOKUP е логическа стойност (само за TRUE или FALSE), която показва дали искате VLOOKUP да намери точна или приблизителна съвпадение с търсената стойност.
- Ако TRUE или ако този аргумент е пропуснат, VLOOKUP връща или точното съвпадение с Lookup_value, или ако точното съвпадение не е намерено, VLOOKUP връща следващата най-голяма стойност. За формулата да направите това, данните в първата колона на Table_array трябва да бъдат сортирани във възходящ ред.
- Ако FALSE, VLOOKUP използва само точно съвпадение с Lookup_value. Ако в първата колона на Table_array има две или повече стойности, които съответстват на стойността за търсене, се използва първата намерена стойност. Ако точното съвпадение не е намерено, се връща грешка # N / A.
В този урок, тъй като търсим определено име, Range_lookup е настроен на фалшив така че само формулировките да се връщат с точни съвпадения.
- Кликнете върху Range_lookup линия в диалоговия прозорец.
- Въведете думата фалшив в този ред, за да покажем, че искаме VLOOKUP да върне точно съвпадение за данните, които търсим.
- Кликнете Добре за да завършите лявата формула за търсене и затваряне на диалоговия прозорец.
- Тъй като все още не сме въвели името на фирмата в клетка D2, в клетката E2 се появява грешка # N / A.
Данните се връщат с формулата за леви търсене
За да намерите кои компании доставят кои части, въведете името на фирмата в клетка D2 и натиснете бутона ENTER на клавиатурата.
Името на частта се показва в клетка E2.
- Кликнете върху клетката D2 във вашия работен лист.
- Тип Gadgets Плюс в клетката D2 и натиснете бутона ENTER на клавиатурата.
- Текстът "Gadgets" - частта, предоставена от компанията Gadgets Plus - трябва да се показва в клетка E2.
Изпробвайте допълнително формулата за търсене, като въведете други имена на фирми в клетка D2 и съответното име на частта трябва да се появи в клетка E2.
Ако в клетка E2 се появи съобщение за грешка като # N / A, проверете за правописни грешки в клетка D2.
04 от 05Създаване на масив с две колони
Синтаксисът за функцията CHOOSE е:
= CHOOSE (номер на индекса, стойност1, стойност2, … стойност254)
Функцията CHOOSE обикновено връща една стойност от списъка с стойности (Value1 до Value254) въз основа на въведения индекс номер.
Ако индексният номер е 1, функцията връща Value1 от списъка; ако индексният номер е 2, функцията връща Value2 от списъка и т.н.
Когато се въвеждат множество индексни номера, функцията връща множество стойности в желаната от Вас последователност. Получаването на CHOOSE за връщане на няколко стойности се извършва чрез създаване на масив.
За да въведете масив, заобикаляйте въведените числа с къдрави скоби или скоби. За номера на индекса се въвеждат две цифри:{ 1,2 }.
Трябва да се отбележи, че CHOOSE не се ограничава до създаването на таблица с две колони. Чрез включването на допълнителен номер в масива - например {1,2,3} - и допълнителен обхват в аргумента за стойността, CHOOSE създава таблица с три колони.
Допълнителните графи ви позволяват да върнете различна информация с формулата за ляво търсене, като промените аргумента на кода на индекса на колоната на VLOOKUP към номера на колоната, съдържаща желаната информация.
05 от 05Промяна на реда на колоните с функцията CHOOSE
Във функцията CHOOSE, използвана в тази формула:
ИЗБЕРИ ({1,2}, $ F: $ F, $ D: $ D)
обхватът за колона F е посочен преди колона D.
Тъй като функцията CHOOSE задава масива на таблицата на VLOOKUP (източникът на данни за тази функция), превключването на реда на колоните във функцията CHOOSE се предава по VLOOKUP.
Що се отнася до VLOOKUP, масивът на таблицата е само с две колони с колона F вляво и колона D отдясно. Тъй като колона F съдържа името на фирмата, която искаме да търсим, и тъй като колона D съдържа имената на части, VLOOKUP може да изпълнява редовните си задължения за търсене при намиране на данни, намиращи се вляво от търсената стойност.
В резултат VLOOKUP може да използва името на компанията, за да намери частта, която доставя.
