Најдете повеќе полиња на податоци со 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. Кликнете на линијата table_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

Работата на функцијата COLUMN е да го врати бројот на колоната дадена како референтен аргумент.

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

Бидејќи првото поле на податоци што сакаме да го вратиме е цената на ставката - која е во колона две од табелата со податоци - можеме да ја одбереме референцата на ќелијата за која било ќелија во колона Б како референтен аргумент за да го добиете бројот 2 за аргументот Col_index_num .

Чекори за чекори

1. Во дијалогот за функција VLOOKUP, кликнете на линијата Col_index_num
2. Внесете колона со име на функција проследено со отворен круг заградата " ( "
3. Кликнете на ќелијата Б1 во работниот лист за да ја внесете референцата на ќелијата како референтен аргумент
4. Напишете заграда за затворање " ) " за да ја завршите функцијата COLUMN
5. Оставете го дијалогот за функција VLOOKUP отворен за следниот чекор во упатството

08 од 10

Внесување на аргумент за пребарување на VLOOKUP опсег

Аргументот 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 еднаков на Неточно .

Чекори за чекори

1. Кликнете на линијата Range_lookup во дијалогот
2. Напишете го зборот False во оваа линија за да покажеме дека сакаме VLOOKUP да врати точно пребарување за податоците што ги бараме
3. Кликнете Во ред за да ја комплетирате формата за пребарување и затворете го дијалогот
4. Бидејќи ние сеуште не влеговме во критериумот за пребарување во ќелијата D2, грешка # N / A ќе биде присутна во ќелијата E2
5. Оваа грешка ќе биде исправена кога ќе ги додадеме критериумите за пребарување во последниот чекор од упатството

09 од 10

Копирање на Формулата за пребарување со полначот за пополнување

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

За да го направите ова, формуларот за барање мора да престојува во сите полиња од кои сакаме информации.

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

Бидејќи податоците се поставени во редовен образец на работниот лист , можеме да ја ископираме формулата за пребарување во ќелијата Е2 во клетките F2 и G2.

Бидејќи формулата е копирана, Excel ќе ја ажурира релативната референтна ќелија во функцијата COLUMN (B1) за да ја одрази новата локација на формулата.

Исто така, Excel не ја менува апсолутната референца за ќелијата $ D $ 2 и наведената табела на опсег како формула е копирана.

Има повеќе од еден начин за копирање на податоци во Excel, но веројатно најлесен начин е со користење на рачката за пополнување .

Чекори за чекори

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

10 од 10

Внесување на критериумите за пребарување

Откако формулата за пребарување ќе биде копирана во бараните ќелии може да се користи за добивање информации од табелата со податоци.

За да го направите тоа, впишете го името на предметот што сакате да го превземете во ќелијата Lookup_value (D2) и притиснете го копчето ENTER на тастатурата.

Откако ќе се направи, секоја ќелија што содржи формула за пребарување треба да содржи различно парче податоци за хардверскиот елемент што го барате.

Чекори за чекори

1. Кликнете на ќелијата D2 во работниот лист
2. Напишете виџети во ќелијата D2 и притиснете го копчето ENTER на тастатурата
3. Следните информации треба да бидат прикажани во ќелиите E2 до G2:
   • Е2 - 14,76 долари - цената на елементот
   • F2 - PN-98769 - бројот на дел за елемент
   • G2 - Widgets Inc. - името на добавувачот за додатоци
4. Проверете ја формулата за VLOOKUP низа со впишување на името на другите делови во ќелијата D2 и следење на резултатите во клетките E2 до G2

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