Excel- ի VLOOKUP- ի հետ տվյալների բազմաբնույթ դաշտերը

Excel- ի VLOOKUP գործառույթը COLUMN գործառույթի հետ համատեղելով, մենք կարող ենք ստեղծել բանաձեւի բանաձեւ, որը թույլ է տալիս վերադարձնել բազմաթիվ արժեքներ տվյալների բազայի կամ տվյալների աղյուսակի մեկ տողից:

Վերեւում պատկերված օրինակում, որոնման բանաձեւը հեշտացնում է բոլոր արժեքները վերադարձնելու համար, օրինակ, գինը, մասի համարը եւ մատակարարը, կապված տարբեր սարքերի հետ:

01-ից 10-ը

Վերադարձեք բազմակի արժեքներ Excel- ի հետ VLOOKUP

Ստորեւ թվարկված քայլերից հետո ստեղծում է վերեւում գտնվող պատկերում գտնված որոնման բանաձեւը, որը կվերադարձնի բազմակի արժեքներ միայն տվյալների գրառումներից:

Փնտրման բանաձեւը պահանջում է, որ COLUMN գործառույթը ներկված լինի VLOOKUP- ի ներսում:

Ֆունկցիայի ներարկումը ներառում է երկրորդ ֆունկցիան որպես առաջին գործառույթի փաստարկներից մեկը:

Այս ձեռնարկի մեջ COLUMN գործառույթը կտեղադրվի որպես VLOOKUP- ի սյունակի ինդեքսի համարի փաստարկ:

Ուսուցչի վերջին քայլը ներառում է որոնման բանաձեւը լրացուցիչ սյունակներում պատճենելը, ընտրված մասի համար լրացուցիչ արժեքներ ստանալու համար:

Tutorial պարունակությունը

• Մուտքագրեք Tutorial Data- ը
• Տվյալների աղյուսակի համար անվանել Range
• Սկսած VLOOKUP գործառույթը
• Բացարձակ բջջային հղումներ օգտագործելով Փնտրող արժեքի փաստարկը մուտքագրելը
• Մտնելով Table Array փաստարկը
• Մուտք գործելով Ներկառուցված COLUMN գործառույթը
• Ավարտելով VLOOKUP գործառույթը
• Պատճենելու բանաձեւի պատճենումը լրացնելով բռնակով
• Ստացեք տվյալներ տվյալների որոնման ձեւի հետ

02-ից 10-ը

Մուտքագրեք Tutorial Data- ը

Tutorial- ի առաջին քայլը տվյալների մուտքագրումը Excel- ի աշխատաթերթ է :

Tutorial- ում կատարված քայլերին հետեւելու համար մուտքագրեք վերեւում պատկերված տվյալները հետեւյալ բջիջներում :

• Մուտքագրեք տվյալների վերին տիրույթը D1- ից մինչեւ G1 խցերում
• Մուտքագրեք երկրորդ շրջանակը D4- ի G10 բջիջների մեջ

Որոնման չափանիշները եւ այս ձեռնարկի ընթացքում ստեղծված որոնման բանաձեւը կներառվեն աշխատանքային էջի 2-րդ շարքում :

Tutorial- ն չի ներառում պատկերում երեւացող ձեւաչափը, սակայն դա չի ազդի ինչպես lookupի բանաձեւի վրա:

Ստորեւ վերը նշվածներին նման ձեւաչափման ընտրանքների մասին տեղեկատվությունը հասանելի է այս հիմնական Excel ֆորմատավորման դասընթացում :

Tutorial քայլերը

1. Մուտքագրեք տվյալները, ինչպես տեսնում ենք վերը նշված պատկերում D1- ից մինչեւ G10 բջիջները

03-ից 10-ը

Տվյալների աղյուսակի համար անվանել Range

A անունը շարք է, որը հեշտ ձեւ է դիմել մի շարք տվյալների բանաձեւում: Փոխարենը, տվյալների մուտքագրման բջջային հղումները մուտքագրելու փոխարեն, պարզապես կարող եք մուտքագրել տիրույթի անունը:

Երկրորդ առավելությունը, որը կոչված է օգտագործելու համար, այն է, որ այս շարքի համար հիշատակված բջիջները երբեք չեն փոխվում, նույնիսկ եթե բանաձեւը պատճենվում է աշխատաթերթի մյուս բջիջներին:

Հետեւյալ տողերի անունները, հետեւաբար, այլընտրանք են բացարձակ բջջային հղումների օգտագործման համար, սխալներ կանխելու համար, երբ բանաձեւերը պատճենելիս:

Նշում. Տողի անվանումն ընդգրկում է տվյալների վերնագրերը կամ դաշտի անունները (տող 4), բայց միայն տվյալներն են:

Tutorial քայլերը

1. Բարձրացրեք D5- ի G10 բջիջները աշխատանքային թերթիկում, դրանք ընտրելու համար
2. Սեղմիր Ա սյունակի վերեւում գտնվող Անունը Box- ը
3. Անվան տուփում «Աղյուսակ» (ոչ մեջբերումներ) անվանեք
4. Սեղմեք ստեղնաշարի վրա ENTER կոճակը
5. D5- ից մինչեւ G10 բջիջները այժմ ունեն «Աղյուսակ» անունը: Մենք կօգտագործենք անունը VLOOKUP սեղանի պարունակության փաստարկի հետագայում ձեռնարկի մեջ

04-ից 10-ը

Բացելով VLOOKUP երկխոսության տուփը

Չնայած հնարավոր է ուղղակիորեն մուտքագրել մեր որոնման բանաձեւը ուղղակիորեն գործածված մի բջիջի մեջ, շատերը դժվարանում են տարանջատել սինթետիկները , հատկապես այն բարդ բանաձեւի համար, ինչպիսին մենք օգտագործում ենք այս ձեռնարկը:

Այլընտրանք, այս դեպքում, VLOOKUP- ի երկխոսության վանդակում օգտագործելը : Գրեթե բոլոր Excel գործառույթները ունեն երկխոսության տուփ, որը թույլ է տալիս մուտքագրել ֆունկցիայի փաստարկներից յուրաքանչյուրը առանձին տողում:

Tutorial քայլերը

1. Սեղմեք գործի թերթի E2 բջիջը `գտնվելու վայրը, որտեղ կցուցադրվի երկու ծավալային որոնման բանաձեւի արդյունքները
2. Սեղմիր ժապավենի ձեւակերպումների էջին
3. Սեղմեք ժապավենի վրա Փնտրող եւ Տեղեկատու տարբերակը սեղմեք ֆունկցիայի բացվող ցանկը բացելու համար
4. Հպեք VLOOKUP- ում ցանկում, ֆունկցիայի երկխոսության դաշտը բացելու համար

05-ից 10-ը

Բացարձակ բջջային հղումներ օգտագործելով Փնտրող արժեքի փաստարկը մուտքագրելը

Սովորաբար, որոնման արժեքը համապատասխանում է տվյալների աղյուսակի առաջին սյունակում տվյալների դաշտին :

Մեր օրինակում, lookupային արժեքը վերաբերում է այն սարքավորման մասին, որի մասին մենք ուզում ենք գտնել:

Սխալ արժեքի համար թույլատրելի տեսակների տվյալները հետեւյալն են.

• տեքստային տվյալներ
• տրամաբանական արժեք (միայն TRUE կամ FALSE)
• թիվ
• բջջային հղում , որը վերաբերում է աշխատանքային էջին

Այս օրինակում մենք կգնանք բջջային հղում դեպի այն վայրը, որտեղ կտեղադրվի մասի անունը `D2 բջիջ:

Բացարձակ բջջային հղումներ

Ուսուցչի հետագա քայլում մենք կցուցադրենք E2 բանաձեւի բանաձեւի բանաձեւը F2 եւ G2 բջիջներում:

Սովորաբար, երբ Excel- ում բանաձեւերը պատճենվում են, բջջային տեղեկանքները փոխվում են `արտացոլելու իրենց նոր դիրքը:

Եթե ​​դա տեղի ունենա, D2 - բջջային տեղեկանք lookup արժեքի համար, կփոխվի, քանի որ բանաձեւը պատճենվում է F2 եւ G2 խցերում սխալներ ստեղծելու համար:

Խախտումները կանխելու համար մենք կստանանք բջջային տեղեկանք D2- ի բացարձակ բջջային տեղեկանք :

Բացարձակ բջջային տեղեկանքները չեն փոխվում, երբ բանաձեւերը պատճենվում են:

Բացարձակ բջջային հղումները ստեղծվում են ստեղնաշարի վրա սեղմելով F4 ստեղնը: Դրա համար ավելացնում է դոլարի նշաններ բջջային հղումների շուրջ, ինչպիսիք են $ D $ 2

Tutorial քայլերը

1. Սեղմիր lookup_value գծի երկխոսության դաշտում
2. Սեղմեք այս բջիջի հղումը lookup_value գծին ավելացնելու համար սեղմեք D2 բջիջը: Սա բջիջ է, որտեղ մենք կգրենք այն մասի անվանումը, որի մասին մենք ձգտում ենք
3. Առանց տեղադրման կետը տեղափոխելը, սեղմեք F4 ստեղնը ստեղնաշարի վրա, D2- ի փոխարկել $ D $ 2 բացարձակ բջջային հղում
4. Թողնել VLOOKUP ֆունկցիայի երկխոսության դաշտը, բացեք ուսումնական ձեռնարկի հաջորդ քայլը

06-ից 10-ը

Մտնելով Table Array փաստարկը

Սեղանի զանգվածը տվյալների սեղանն է, որը որոնման բանաձեւը որոնում է, որ մենք ուզում ենք գտնել այն տեղեկությունները:

Աղյուսակի զանգվածը պետք է պարունակի տվյալներ առնվազն երկու սյունակ:

• առաջին սյունակը պարունակում է lookup արժեքի փաստարկ (նախորդ քայլ ձեռնարկի մեջ)
• երկրորդ, եւ ցանկացած լրացուցիչ սյունակ, կքննարկվեն որոնման բանաձեւով, մեր նշած տեղեկությունները գտնելու համար:

Սեղանի պարունակության փաստարկը պետք է մուտքագրվի որպես ընդգրկույթ, որը պարունակում է տվյալների աղյուսակի կամ որպես տիրույթի անունի բջջային հղումներ :

Այս օրինակի համար մենք կօգտագործենք դասընթացի 3-րդ քայլում ստեղծված տիրույթի անունը:

Tutorial քայլերը

1. Սեղմեք սեղանի աղյուսակի գծին երկխոսության դաշտում
2. Մուտքագրեք «Աղյուսակ» (մեջբերում չկան), այս փաստարկի համարը մուտքագրելու համար
3. Թողնել VLOOKUP ֆունկցիայի երկխոսության դաշտը, բացեք ուսումնական ձեռնարկի հաջորդ քայլը

07-ից 10-ը

Նվաճելով COLUMN գործառույթը

Սովորաբար VLOOKUP տվյալները վերադարձնում է տվյալների սեղանի մեկ սյունակում եւ սյունակը սահմանվում է սյունակի ինդեքսի համարի փաստարկով:

Այս օրինակում, սակայն, մենք ունենք երեք սյունակներ, որոնք մենք ցանկանում ենք վերադարձնել տվյալները, որպեսզի մենք պետք է հեշտությամբ փոխենք սյունակի ինդեքսի համարը, առանց մեր Փնտրել բանաձեւը խմբագրելու:

Սա այն դեպքն է, երբ COLUMN- ի գործառույթը գալիս է: Մտնելով այն որպես սյունակի ինդեքսի համարի փաստարկ, այն կփոխվի, քանի որ lookup բանաձեւը պատճենվում է D2 բջիջից մինչեւ E2 եւ F2 բջիջները դեպի ձեռնարկը:

Nesting գործառույթները

COLUMN- ի գործառույթը, հետեւաբար, գործում է որպես VLOOKUP- ի սյունակի ինդեքսի համարի փաստարկ :

Սա կատարվում է VLOOKUP- ի ներսում COLUMN գործառույթի ներդիրով , երկխոսության վանդակի Col_index_num գծում:

Մտնելով COLUMN ֆունկցիան ձեռքով

Excel- ն թույլ չի տալիս մեզ բացել երկրորդ գործառույթի երկխոսության վանդակը `մուտք գործելու իր փաստարկները:

COLUMN- ի գործառույթը, հետեւաբար, պետք է ձեռքով մուտքագրվի Col_index_num գիծում:

COLUMN- ի գործառույթը ունի միայն մեկ փաստարկ ` հղումային փաստարկ, որը բջջային տեղեկանք է:

Ընտրելով COLUMN ֆունկցիայի գործառնական փաստարկը

COLUMN- ի գործառույթի գործն է վերադարձնել սյունակի համարը, որը տրված է որպես Reference argument:

Այլ կերպ ասած, այն վերափոխում է սյունակի նամակը մի սյունով Ա-ով, առաջին սյունակը, երկրորդ բյունը եւ այլն:

Քանի որ առաջին տվյալների դաշտը մենք ցանկանում ենք վերադարձել, այն նյութի գինը, որը գտնվում է տվյալների աղյուսակի երկրորդ սյունակում, մենք կարող ենք ընտրել բջջային տեղեկանք Բ բանի ցանկացած բջիջի համար որպես տեղեկանքային փաստարկ, որպեսզի համար 2 Col_index_num փաստարկը:

Tutorial քայլերը

1. VLOOKUP ֆունկցիայի երկխոսության դաշտում սեղմեք Col_index_num տողում
2. Մուտքագրեք ֆունկցիայի անվան սյունակը, որին հաջորդում է բաց round bracket " ( "
3. Սեղմիր գործի էջում B1 բջջային բջջային տեղեկանք մուտքագրելու համար
4. Մուտքագրեք փակ փուլի փակագիծը " ) , ավարտելու COLUMN գործառույթը
5. Թողնել VLOOKUP ֆունկցիայի երկխոսության դաշտը, բացեք ուսումնական ձեռնարկի հաջորդ քայլը

08-ից 10-ը

Մտնելով VLOOKUP Range որոնման փաստարկը

VLOOKUP- ի Range_lookup փաստարկը տրամաբանական արժեք է (TRUE կամ FALSE միայն), որը ցույց է տալիս, թե արդյոք ցանկանում եք VLOOKUP- ին գտնել ճշգրիտ կամ մոտավոր հանդիպում Lookup_value- ում:

• Եթե ​​TRUE կամ այս փաստարկը բացակայում է, VLOOKUP- ը վերադարձնում է կամ ճշգրիտ համընկնում է Lookup_value- ին կամ, եթե ճշգրիտ համընկնում չի հայտնաբերվել, VLOOKUP- ը վերադարձնում է հաջորդ խոշորագույն արժեքը: Որպեսզի բանաձեւն անի, Table_array- ի առաջին սյունակում տվյալները պետք է դասավորված լինեն աճող կարգով :
• Եթե ​​FALSE, VLOOKUP- ը կօգտագործի միայն Lookup_value- ի ճշգրիտ խաղը: Եթե ​​սեղանի արժեքի համապատասխանող Table_array- ի առաջին սյունակում կան երկու կամ ավելի արժեքներ, ապա օգտագործվում է առաջին արժեքը: Եթե ​​ճշգրիտ համընկնում չի գտնվել, ապա # N / A սխալը վերադարձվում է:

Այս ձեռնարկում, քանի որ մենք փնտրում ենք կոնկրետ տեղեկատվություն որոշակի տեխնիկայի մասին, մենք կստեղծենք Range_lookup հավասար հավասարեցված:

Tutorial քայլերը

1. Սեղմեք երկխոսության դաշտում Range_lookup գիծը
2. Մուտքագրեք բառի սխալը այս տողում, նշելով, որ մենք ուզում ենք 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- ում տվյալների կրկնօրինակման ավելի քան մեկ եղանակ կա, բայց, ամենայն հավանականությամբ, ամենահեշտ ձեւն է, օգտագործելով Fill Handle :

Tutorial քայլերը

1. Սեղմեք E2- ի բջիջը, որտեղ որոնման բանաձեւը գտնվում է `դարձնել այն ակտիվ խուցը
2. Մկնիկի ցուցիչը ներքեւի աջ անկյունում գտնվող սեւ քառակուսի վրա: Ցուցիչը կփոխվի + « + » նշանի նշագծին, սա լրացնել բռնիչը
3. Սեղմեք ձախ մկնիկի կոճակը եւ սահեցրեք լրացնել բռնակի ողջ G2 բջիջը
4. Հեռացրեք մկնիկի կոճակը եւ F3 բջջը պետք է պարունակի երկու ծավալային որոնման բանաձեւը
5. Եթե ​​ճիշտ է կատարվել, F2 եւ G2 բջիջները պետք է պարունակեն նաեւ թիվ N / A սխալը, որը առկա է E2 բջիջում

10-ից 10-ը

Մտնելով որոնման չափանիշները

Հետո որոնման բանաձեւը պատճենահանված է պահանջվող բջիջներին, այն կարող է օգտագործվել տվյալների աղյուսակից տեղեկություն ստանալու համար:

Դա անելու համար մուտքագրեք այն կետի անունը, որը ցանկանում եք ստանալ Lookup_value բջջային (D2) մեջ եւ ստեղնաշարի վրա ENTER կոճակը:

Կատարելուց հետո, որոնման բանաձեւը պարունակող յուրաքանչյուր բջիջը պետք է պարունակի որոնման ապարատի վերաբերյալ այլ տվյալներ:

Tutorial քայլերը

1. Սեղմեք գործի թերթում D2 բջիջը
2. Մուտքագրեք Widget- ը D2 բջջի մեջ եւ ստեղնաշարի վրա ENTER- ի ստեղնը սեղմեք
3. Հետեւյալ տեղեկատվությունը պետք է արտացոլվի E2- ի G2 բջիջներում.
   • E2- $ 14.76-ը `վիջեթի գինը
   • F2 - PN-98769 - վիդջեթի մասի համարը
   • G2 - Widgets Inc. - ֆայլերի մատակարարի անունը
4. Փորձեք VLOOKUP ալիքի բանաձեւը, հետագայում մյուս մասերի անունը մուտքագրելով D2 բջիջ եւ հետեւել արդյունքները E2- ից մինչեւ G2 բջիջներում

Եթե ​​սխալ հաղորդագրություն, ինչպիսիք են #REF! հայտնվում է E2, F2 կամ G2 բջիջներում, այս VLOOKUP սխալի հաղորդագրությունների ցանկը կարող է օգնել ձեզ որոշել, թե որտեղ է գտնվում խնդիրը: