Намерете няколко полета на данни с Excel VLOOKUP

Чрез комбинирането на функцията VLOOKUP на Excel с функцията COLUMN можем да създадем формула за търсене, която ви позволява да върнете няколко стойности от един ред на база данни или таблица с данни.

В примера, показан на изображението по-горе, формулата за търсене прави лесно връщането на всички стойности - като цена, номер на част и доставчик - свързани с различни части от хардуера.

01 от 10

Връщане на множество стойности с Excel VLOOKUP

Следвайки стъпките, изброени по-долу, създава формулата за търсене, представена в изображението по-горе, която ще върне няколко стойности от един запис на данни.

Формулата за търсене изисква функцията COLUMN да бъде вградена във VLOOKUP.

Поставянето на функция включва въвеждането на втората функция като един от аргументите за първата функция.

В този наръчник ще бъде въведена функцията COLUMN като аргумент за индекс на колона за VLOOKUP.

Последната стъпка в урока включва копиране на формулата за търсене в допълнителни колони, за да се извлекат допълнителни стойности за избраната част.

Съдържание на урока

• Въвеждане на данните за урока
• Създаване на именуван диапазон за таблицата с данни
• Стартиране на функцията VLOOKUP
• Въвеждане на аргумента за стойността на търсенето, използвайки абсолютни референции за клетки
• Въвеждане на аргумента за табличен масив
• Въвеждане на функцията за вложена колона
• Завършване на функцията VLOOKUP
• Копиране на формулата за търсене с пълнителя
• Извличане на данни с формула за търсене

02 от 10

Въведете данните за урока

Първата стъпка в урока е да въведете данните в работен лист на Excel.

За да изпълните стъпките в урока, въведете данните в изображението по-горе в следните клетки .

• Въведете най-горния диапазон от данни в клетки D1 ​​до G1
• Въведете втория обхват в клетки D4 до G10

Критериите за търсене и формулата за търсене, създадени по време на този урок, ще бъдат въведени в ред 2 на работния лист.

Урокът не включва форматирането, което се вижда в изображението, но това няма да повлияе на начина на работа на формулата за търсене.

Информация за опциите за форматиране, подобни на тези, описани по-горе, е достъпна в този Основен урок за форматиране на Excel .

Инструкции Стъпки

1. Въведете данните, посочени в изображението по-горе, в клетки D1 ​​до G10

03 от 10

Създаване на именуван диапазон за таблицата с данни

Посочените диапазони са лесен начин да се отнесе към набор от данни във формула. Вместо да въвеждате референтните клетки за данните, можете просто да въведете името на диапазона.

Второто предимство при използването на посочения диапазон е, че референтните стойности на клетките за този диапазон никога не се променят, дори когато формулата се копира в други клетки в работния лист.

Поради това имената на диапазоните са алтернатива на използването на абсолютни референтни клетки, за да се предотвратят грешки при копиране на формули.

Забележка: Името на диапазона не включва заглавията или имената на полетата за данните (ред 4), а само самите данни.

Инструкции Стъпки

1. Маркирайте клетки D5 до G10 в работния лист, за да ги изберете
2. Кликнете върху полето Име над колона А
3. Въведете "Таблица" (без цитати) в полето Име
4. Натиснете клавиша ENTER на клавиатурата
5. Клетките D5 до G10 сега имат диапазона на име "Таблица". Ще използваме името за аргумента VLOOKUP табличен масив по-късно в урока

04 от 10

Отваряне на диалоговия прозорец VLOOKUP

Въпреки че е възможно просто да въведете формулата за търсене директно в клетка в работен лист, много хора смятат, че е трудно да поддържат синтаксиса прав - особено за сложна формула като тази, която използваме в този урок.

Друга алтернатива в този случай е да използвате диалоговия прозорец VLOOKUP. Почти всички функции на Excel имат диалогов прозорец, който ви позволява да въвеждате всеки от аргументите на функцията на отделен ред.

Инструкции Стъпки

1. Кликнете върху клетката E2 на работния лист - мястото, където ще се показват резултатите от двуизмерната формула за търсене
2. Кликнете върху раздела Формули на лентата
3. Кликнете върху опцията Търсене и справка в лентата, за да отворите падащия списък на функциите
4. Кликнете върху VLOOKUP в списъка, за да отворите диалоговия прозорец на функцията

05 от 10

Въвеждане на аргумента за стойността на търсенето, използвайки абсолютни референции за клетки

Обикновено търсената стойност съответства на поле от данни в първата колона на таблицата с данни.

В нашия пример стойността на търсенето се отнася до името на хардуерната част, за която искаме да намерим информация.

Допустимите типове данни за търсената стойност са:

• текстови данни
• логическа стойност (само за TRUE или FALSE)
• номер
• клетъчна препратка към стойност в работния лист

В този пример ще въведем референтната клетка на мястото, където ще се намира името на частта - клетка D2.

Абсолютни клетъчни референции

В по-късна стъпка в урока ще копираме формулата за търсене в клетка Е2 към клетките F2 и G2.

Обикновено, когато формулите се копират в Excel, референтните клетки се променят, за да отразят новото им местоположение.

Ако това се случи, D2 - референтната клетка за стойността на търсенето - ще се промени, тъй като формулата се копира, създавайки грешки в клетките F2 и G2.

За да се предотвратят грешките, ще преобразуваме референтната клетка D2 в абсолютна клетка .

Абсолютните референтни клетки не се променят при копиране на формули.

Абсолютните референтни клетки се създават чрез натискане на клавиша F4 на клавиатурата. Така се добавят доларовите знаци около референтната клетка, като $ D $ 2

Инструкции Стъпки

1. Кликнете върху линията lookup_value в диалоговия прозорец
2. Кликнете върху клетка D2, за да добавите тази клетка към линията lookup_value . Това е клетката, където ще напишем името на частта, за която търсим информация
3. Без да премествате точката за вмъкване, натиснете клавиша F4 на клавиатурата, за да преобразувате D2 в абсолютната клетка с указания $ D $ 2
4. Оставете отворения диалогов прозорец на функцията VLOOKUP за следващата стъпка в урока

06 от 10

Въвеждане на аргумента за табличен масив

Масивът на таблицата е таблицата с данни, която търсената формула търси, за да намери желаната от нас информация.

Масивът на таблицата трябва да съдържа поне две колони с данни .

• първата колона съдържа аргумента за стойността на търсенето (предишна стъпка в урока)
• втората и всякакви допълнителни колони ще бъдат претърсени от формулата за търсене, за да намерите информацията, която посочваме.

Аргументът за масива на масива трябва да бъде въведен като обхват, съдържащ референтните клетки за таблицата с данни или като име на диапазона .

За този пример ще използваме името на диапазона, създадено в стъпка 3 на урока.

Инструкции Стъпки

1. Кликнете върху row_array в диалоговия прозорец
2. Въведете "Таблица" (без цитати), за да въведете името на диапазона за този аргумент
3. Оставете отворения диалогов прозорец на функцията VLOOKUP за следващата стъпка в урока

07 от 10

Включване на функцията COLUMN

Обикновено VLOOKUP връща данни само от една колона на таблица с данни и тази колона се определя от аргумента за кодово число на колоната .

В този пример обаче имаме три колони, от които искаме да върнем данни, така че се нуждаем от начин за лесно промяна на индекса на колоната, без да редактираме формулата за търсене.

Тук влиза функцията COLUMN. Като я въведете като аргумент на колонен индекс , тя ще се промени, тъй като формулата за търсене се копира от клетка D2 до клетки E2 и F2 по-късно в урока.

Функции за вмъкване

Функцията COLUMN, следователно, действа като аргумент на кодовия индекс на колоната на VLOOKUP.

Това се постига чрез вмъкване на функцията COLUMN във вътрешността на VLOOKUP в колоната Col_index_num на диалоговия прозорец.

Въвеждане на функцията COLUMN ръчно

Когато вмъкваме функции, Excel не ни позволява да отворим диалоговия прозорец на втората функция, за да въведем нейните аргументи.

Функцията COLUMN следователно трябва да бъде въведена ръчно в колоната Col_index_num .

Функцията COLUMN има само един аргумент - референтния аргумент, който е референтна клетка.

Избиране на аргумента за референция на функцията на колоната

Задачата на функцията COLUMN е да върне номера на колоната, дадена като аргумент за референция .

С други думи, тя преобразува буквата на колоната в число, като колона А е първата колона, колона Б втората и така нататък.

Тъй като първото поле на данни, което искаме да върнем, е цената на елемента - която е в колона 2 на таблицата с данни - можем да изберем референтната клетка за всяка клетка в колона Б като Референтен аргумент, за да получим номер 2 за аргументът Col_index_num .

Инструкции Стъпки

1. В диалоговия прозорец на функцията VLOOKUP кликнете върху колоната Col_index_num
2. Въведете колоната с името на функцията, последвана от отворена кръгла конзола " ( "
3. Кликнете върху клетка B1 в работния лист, за да въведете тази клетка като референтен аргумент
4. Въведете затваряща кръгла скоба " ) ", за да завършите функцията COLUMN
5. Оставете отворения диалогов прозорец на функцията VLOOKUP за следващата стъпка в урока

08 от 10

Въвеждане на аргумента за търсене на обхвата VLOOKUP

Argument Range_lookup на VLOOKUP е логическа стойност (само TRUE или FALSE), която показва дали искате VLOOKUP да намери точна или приблизителна съвпадение с Lookup_value.

• Ако TRUE или ако този аргумент е пропуснат, VLOOKUP връща или точното съвпадение с Lookup_value, или, ако точното съвпадение не е намерено, VLOOKUP връща следващата най-голяма стойност. За формулата да направите това, данните в първата колона на Table_array трябва да бъдат сортирани във възходящ ред .
• Ако FALSE, VLOOKUP ще използва точна съвпадение само с Lookup_value. Ако в първата колона на Table_array има две или повече стойности, които съответстват на стойността за търсене, се използва първата намерена стойност. Ако точното съвпадение не е намерено, се връща грешка # N / A.

В този урок, тъй като търсим конкретна информация за конкретен хардуерен артикул, ще определим Range_lookup, равен на False .

Инструкции Стъпки

1. Кликнете върху диапазона Range_lookup в диалоговия прозорец
2. Въведете думата " False" в този ред, за да покажете, че искаме VLOOKUP да върне точното съвпадение на данните, които търсим
3. Кликнете върху OK, за да завършите диалоговия прозорец за търсене и затваряне
4. Тъй като все още не сме въвели критериите за търсене в клетка D2, ще бъде налице грешка # N / A в клетка E2
5. Тази грешка ще бъде коригирана, когато добавим критериите за търсене в последната стъпка на урока

09 от 10

Копиране на формулата за търсене с пълнителя

Формулата за търсене е предназначена да извлича данни от няколко колони в таблицата с данни едновременно.

За да направите това, формулата за търсене трябва да се намира във всички полета, от които искаме информация.

В този урок искаме да извлече данни от колони 2, 3 и 4 на таблицата с данни - това е цената, номерът на частта и името на доставчика, когато въведем име на част като Lookup_value.

Тъй като данните са изложени в редовен модел в работния лист , можем да копираме формулата за търсене в клетка E2 към клетките F2 и G2.

Тъй като формулата се копира, Excel ще актуализира относителната референтна клетка във функцията COLUMN (B1), за да отрази новото местоположение на формулата.

Освен това, Excel не променя абсолютната клетка за референция $ D $ 2 и таблицата с диапазона на имената, тъй като формулата се копира.

Има повече от един начин за копиране на данни в Excel, но вероятно най-лесният начин е чрез използване на дръжката за запълване .

Инструкции Стъпки

1. Кликнете върху клетката E2 - където се намира формулата за търсене - за да стане активната клетка
2. Поставете курсора на мишката върху черния квадрат в долния десен ъгъл. Показалецът ще се промени на знак " + " - това е дръжката за попълване
3. Кликнете върху левия бутон на мишката и плъзнете дръжката за пълнене към клетка G2
4. Освободете бутона на мишката и клетката F3 трябва да съдържа двуизмерната формула за търсене
5. Ако се направи правилно, клетките F2 и G2 сега трябва да съдържат и грешката # N / A, която се намира в клетка E2

10 от 10

Въвеждане на критериите за търсене

След като формулата за търсене е копирана в необходимите клетки, тя може да се използва за извличане на информация от таблицата с данни.

За целта въведете името на елемента, който искате да изтеглите, в клетката Lookup_value (D2) и натиснете клавиша ENTER на клавиатурата.

След като направите това, всяка клетка, съдържаща формулата за търсене, трябва да съдържа различни данни за хардуерния елемент, който търсите.

Инструкции Стъпки

1. Кликнете върху клетката D2 в работния лист
2. Въведете Widget в клетка D2 и натиснете клавиша ENTER на клавиатурата
3. Следната информация трябва да се показва в клетките E2 до G2:
   • E2 - 14,76 долара - цената на джаджа
   • F2 - PN-98769 - номерът на частта за джаджа
   • G2 - Widgets Inc. - името на доставчика за джаджи
4. Изпробвайте по-нататък формулата на VLOOKUP масив, като напишете името на другите части в клетка D2 и наблюдавате резултатите в клетките Е2 до G2

Ако има съобщение за грешка като #REF! се появява в клетките E2, F2 или G2, този списък с съобщения за грешка VLOOKUP може да ви помогне да определите къде е проблемът.