01 от 03
Намерете данни отляво
Обобщение на формулата за търсене на Excel в левия списък
Функцията VLOOKUP на Excel се използва за намиране и връщане на информация от таблица с данни въз основа на избраната от вас стойност за търсене .
Обикновено VLOOKUP изисква стойността на търсенето да бъде в най-лявата колона на таблицата с данни и функцията връща друго поле от данни, разположено в същия ред вдясно от тази стойност.
Чрез комбиниране на VLOOKUP с функцията CHOOSE ; може да се създаде левица за търсене, която:
- позволява стойността на търсенето да бъде избрана от всяка колона в таблицата с данни
- връщаща информация, разположена във всяка колона отляво на търсената стойност
Пример: Използване на функциите 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 на работния лист - мястото, където ще се показват резултатите от лявата формула за търсене
- Кликнете върху раздела Формули на лентата
- Кликнете върху опцията Търсене и справка в лентата, за да отворите падащия списък на функциите
- Кликнете върху VLOOKUP в списъка, за да изведете диалоговия прозорец на функцията
02 от 03
Въвеждане на аргументи в диалоговия прозорец VLOOKUP - Кликнете, за да видите по-голямо изображение
Аргументите на VLOOKUP
Аргументите на функцията са стойностите, използвани от функцията за изчисляване на резултата.
В диалоговия прозорец на функцията името на всеки аргумент се намира на отделен ред, последван от поле, в което да въведете стойност.
Въведете следните стойности за всеки от аргументите на VLOOKUP на правилния ред на диалоговия прозорец, както е показано на изображението по-горе.
Стойността на търсенето
Стойността на търсенето е полето на информацията, използвана за търсене в табличния масив. VLOOKUP връща друго поле от данни от същия ред като стойността за търсене.
Този пример използва референция за клетката до мястото, където името на фирмата ще бъде въведено в работния лист. Предимството на това е, че го прави лесно да се промени името на фирмата без да се редактира формулата.
Инструкции Стъпки
- Кликнете върху линията lookup_value в диалоговия прозорец
- Кликнете върху клетка D2, за да добавите тази клетка към линията lookup_value
- Натиснете клавиша F4 на клавиатурата, за да направите абсолютната клетка абсолютна - $ D $ 2
Забележка: Абсолютните референтни клетки се използват за аргументите за стойността на търсене и аргументите в таблицата, за да се предотвратят грешки, ако формулата за търсене се копира в други клетки в работния лист.
Масив на таблицата: Въвеждане на функцията CHOOSE
Аргументът за таблицата с масива е блокът от съседни данни, от който се извлича конкретна информация.
Обикновено VLOOKUP изглежда само надясно на аргумента за стойността на търсенето, за да намери данни в табличния масив. За да го разгледате наляво, VLOOKUP трябва да бъде измамен, като пренареждате колоните в таблицата с помощта на функцията CHOOSE.
В тази формула функцията CHOOSE изпълнява две задачи:
- тя създава табличен масив, който е само с две колони широк - колони D и F
- тя променя правото на левия ред на колоните в табличния масив, така че колоната F идва и колоната D е втора
Подробности за това, как функцията CHOOSE изпълнява тези задачи, можете да намерите на страница 3 на урока .
Инструкции Стъпки
Забележка: Когато въвеждате функциите ръчно, всеки от аргументите на функцията трябва да бъде разделен със запетая "," .
- В диалоговия прозорец на функцията 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 ще бъде зададен на False, така че само точните съвпадения се връщат от формулата.
Инструкции Стъпки
- Кликнете върху диапазона Range_lookup в диалоговия прозорец
- Въведете думата " False" в този ред, за да покажете, че искаме VLOOKUP да върне точното съвпадение на данните, които търсим
- Кликнете върху OK, за да завършите диалоговия прозорец на лявата формула за търсене и затваряне
- Тъй като все още не сме въвели името на компанията в клетка D2, в клетката E2 трябва да има грешка # N / A
03 от 03
Тестване на формулата за леви търсене
Връщане на данни с формулата за леви търсене
За да намерите кои компании доставят кои части, въведете името на фирмата в клетка D2 и натиснете клавиша ENTER на клавиатурата.
Името на частта ще се покаже в клетка E2.
Инструкции Стъпки
- Кликнете върху клетката D2 във вашия работен лист
- Въведете Gadgets Plus в клетка D2 и натиснете клавиша ENTER на клавиатурата
- Текстът Gadgets - частта, предоставена от компанията Gadgets Plus - трябва да се показва в клетка E2
- Тествайте формулата за търсене още, като напишете други имена на фирми в клетка D2 и съответното име на част трябва да се появи в клетка E2
VLOOKUP Съобщения за грешка
Ако в клетка E2 се появи съобщение за грешка като # N / A , първо проверете за правописни грешки в клетка D2.
Ако не е проблемът за правописа, този списък с съобщения за грешка VLOOKUP може да ви помогне да определите къде е проблемът.
Прекъсване на задачата на функцията CHOOSE
Както бе споменато, в тази формула функцията CHOOSE има две задания:
- тя създава табличен масив, който е само с две колони широк - колони D и F
- тя променя правото на левия ред на колоните в табличния масив, така че колоната F идва и колоната D е втора
Създаване на табличен масив с две колони
Синтаксисът за функцията CHOOSE е:
= CHOOSE (номер на индекса, стойност1, стойност2, ... стойност254)
Функцията CHOOSE обикновено връща една стойност от списъка с стойности (Value1 до Value254) въз основа на въведения индекс.
Ако индексният номер е 1, функцията връща Value1 от списъка; ако индексният номер е 2, функцията връща Value2 от списъка и т.н.
Чрез въвеждане на няколко индексни номера; функцията обаче ще върне няколко стойности в желаната от Вас по ред. Получаването на CHOOSE за връщане на няколко стойности се извършва чрез създаване на масив .
Въвеждането на масив се осъществява чрез заобикаляне на въведените числа с къдрави скоби или скоби. За индекса се въвеждат две числа: {1,2} .
Трябва да се отбележи, че CHOOSE не се ограничава до създаването на таблица с две колони. Чрез включването на допълнителен номер в масива - например {1,2,3} - и допълнителен диапазон в аргумента за стойност, може да се създаде таблица с три колони.
Допълнителните графи ще ви позволят да върнете различна информация с формулата за ляво търсене просто като промените аргумента на кодовия индекс на колоната на VLOOKUP до номера на колоната, съдържаща желаната информация.
Промяна на реда на колоните с функцията CHOOSE
При функцията CHOOSE, използвана в тази формула: CHOOSE ({1,2}, $ F: $ F, $ D: $ D) , диапазонът за колона F е посочен преди колона D.
Тъй като функцията CHOOSE задава масива на таблицата на VLOOKUP - източникът на данни за тази функция - превключването на реда на колоните във функцията CHOOSE се предава на VLOOKUP.
Сега, що се отнася до VLOOKUP, масивът на таблицата е само две колони широк с колона F в ляво и колона D отдясно. Тъй като колона F съдържа името на фирмата, която искаме да търсим, и тъй като колона D съдържа имената на части, VLOOKUP ще може да изпълнява обичайните си задължения за търсене при намиране на данни, намиращи се отляво на търсената стойност.
В резултат VLOOKUP може да използва името на компанията, за да намери частта, която доставя.