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

Чрез комбиниране на ExcelVLOOKUP функция сКОЛОНА ние можем да създадем формула за търсене, която ви позволява да върнете няколко стойности от един ред от база данни или таблица с данни.

01 от 10

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

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

Формулата за търсене изисква КОЛОНА функцията е вградена вътре в VLOOKUP, Поставянето на функция включва въвеждането на втората функция като един от аргументите за първата функция.

В този урок, КОЛОНА функцията ще бъде въведена като колонен индекс номер аргумент за VLOOKUP, Последната стъпка в урока включва копиране на формулата за търсене в допълнителни колони, за да се извлекат допълнителни стойности за избраната част.

02 от 10

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

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

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

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

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

03 от 10

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

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

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

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

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

04 от 10

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

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

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

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

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

05 от 10

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

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

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

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

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

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

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

06 от 10

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

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

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

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

1. Кликнете върху table_array линия в диалоговия прозорец.
2. Тип маса за да въведете името на диапазона за този аргумент.
3. Остави VLOOKUP отворен за следващата стъпка в урока.

07 от 10

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

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

В този пример обаче имаме три колони, от които искаме да върнем данни, затова се нуждаем от начин да променим лесно кодов индекс номер без да редактираме нашата формула за търсене - това е мястото, където КОЛОНА функцията идва.

Като го въведете като кодов индекс номер аргумент, той ще се промени, тъй като формулата за търсене се копира от клетка D2 да се клетки Е2 и F2 по-късно в урока.

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

Най- КОЛОНА функцията, следователно, действа като VLOOKUP'с кодов индекс номер аргумент; това се постига чрез гнездяването на КОЛОНА функция вътре в VLOOKUP в Col_index_num ред на диалоговия прозорец.

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

Когато вмъкваме функции, Excel не ни позволява да отворим диалоговия прозорец на втората функция, за да въведем нейните аргументи. Най- КОЛОНА функцията трябва да бъде въведена ръчно в Col_index_num линия. Най- КОЛОНА функцията има само един аргумент - препратка аргумент, който е референция за клетката.

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

Най- КОЛОНА задачата на функцията е да върне номера на колоната, дадена като препратка аргумент. С други думи, тя преобразува буквата на колоната в число с колона А като първата колона, колона Б втората и така нататък.

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

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

08 от 10

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

VLOOKUP'с Range_lookup аргумент е логическа стойност (ВЯРНО или FALSE), което показва дали искате VLOOKUP за да намерите точна или приблизителна съвпадение с Lookup_value.

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

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

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

09 от 10

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

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

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

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

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

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

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

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

10 от 10

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

След като формулата за търсене е копирана в необходимите клетки, тя може да се използва за извличане на информация от таблицата с данни. За да направите това, напишете името на елемента искате да се възстановите в Lookup_value клетка (D2) и натиснете бутона Въведете на клавиатурата.

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

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