Համատեղելով Excel-ի VLOOKUP ֆունկցիան COLUMN ֆունկցիայի հետ՝ կարող եք ստեղծել որոնման բանաձև, որը վերադարձնում է բազմաթիվ արժեքներ տվյալների բազայի կամ տվյալների աղյուսակի մեկ տողից: Իմացեք, թե ինչպես ստեղծել որոնման բանաձև, որը վերադարձնում է բազմաթիվ արժեքներ մեկ տվյալների գրառումից:
Այս հոդվածի հրահանգները վերաբերում են Excel 2019, 2016, 2013, 2010 թթ. և Excel Microsoft 365-ի համար:
Վերջին գիծ
Փնտրման բանաձևը պահանջում է, որ COLUMN ֆունկցիան տեղադրվի VLOOKUP-ի ներսում: Ֆունկցիայի տեղադրումը ներառում է երկրորդ ֆունկցիան որպես առաջին ֆունկցիայի արգումենտներից մեկը մուտքագրելը:
Մուտքագրեք ձեռնարկի տվյալները
Այս ձեռնարկում COLUMN ֆունկցիան մուտքագրվում է որպես սյունակի ինդեքսի համարի փաստարկ VLOOKUP-ի համար: Ձեռնարկի վերջին քայլը ներառում է որոնման բանաձևի պատճենումը լրացուցիչ սյունակներում՝ ընտրված մասի համար լրացուցիչ արժեքներ ստանալու համար:
Այս ձեռնարկի առաջին քայլը տվյալների մուտքագրումն է Excel-ի աշխատաթերթում: Այս ձեռնարկի քայլերին հետևելու համար ստորև նկարում ներկայացված տվյալները մուտքագրեք հետևյալ բջիջներում՝
- Մուտքագրեք տվյալների վերին տիրույթը D1-ից G1 բջիջներում:
- Մուտքագրեք երկրորդ միջակայքը D4-ից մինչև G10 բջիջներում:
Այս ձեռնարկում ստեղծված որոնման չափանիշները և որոնման բանաձևը մուտքագրված են աշխատաթերթի 2-րդ շարքում:
Այս ձեռնարկը չի ներառում պատկերում ցուցադրված Excel-ի հիմնական ձևաչափումը, սակայն դա չի ազդում որոնման բանաձևի աշխատանքի վրա:
Ստեղծեք անվանված միջակայք տվյալների աղյուսակի համար
Անվանված միջակայքը բանաձևում տվյալների տիրույթին հղում կատարելու հեշտ միջոց է: Տվյալների համար բջիջների հղումները մուտքագրելու փոխարեն, մուտքագրեք տիրույթի անունը:
Անվանված միջակայքի օգտագործման երկրորդ առավելությունն այն է, որ այս տիրույթի բջիջների հղումները երբեք չեն փոխվում, նույնիսկ երբ բանաձևը պատճենվում է աշխատաթերթի այլ բջիջներում: Շրջանակների անունները այլընտրանք են բջիջների բացարձակ հղումների օգտագործմանը՝ բանաձևերը պատճենելիս սխալները կանխելու համար:
Շրջանակի անվանումը չի ներառում տվյալների վերնագրերը կամ դաշտերի անվանումները (ինչպես ցույց է տրված 4-րդ տողում), միայն տվյալները:
-
Նշեք բջիջները D5-ից մինչև G10 աշխատանքային թերթում:
Image -
Տեղադրեք կուրսորը A սյունակի վերևում գտնվող Անունի վանդակում, մուտքագրեք Աղյուսակ, այնուհետև սեղմեք Enter: D5-ից մինչև G10 բջիջները ունեն աղյուսակի տիրույթի անվանումը:
Image - VLOOKUP աղյուսակի զանգվածի արգումենտի տիրույթի անվանումը հետագայում օգտագործվում է այս ձեռնարկում:
Բացեք VLOOKUP երկխոսության տուփը
Չնայած որ հնարավոր է մուտքագրել որոնման բանաձևը ուղղակիորեն աշխատաթերթի բջիջի մեջ, շատերի համար դժվար է շարահյուսությունը ուղիղ պահել, հատկապես բարդ բանաձևի համար, ինչպիսին է այս ձեռնարկում օգտագործվածը:
Որպես այլընտրանք օգտագործեք VLOOKUP Function Arguments երկխոսության տուփը: Excel-ի գրեթե բոլոր գործառույթներն ունեն երկխոսության տուփ, որտեղ ֆունկցիայի յուրաքանչյուր արգումենտ մուտքագրվում է առանձին տողով:
-
Ընտրեք աշխատանքային թերթի բջիջ E2: Սա այն վայրն է, որտեղ կցուցադրվեն երկչափ որոնման բանաձեւի արդյունքները:
Image -
Ժապավենի վրա անցեք Formulas ներդիր և ընտրեք Փնտրում և հղում:
Image -
Ընտրեք VLOOKUP ՝ Function Arguments երկխոսության տուփը բացելու համար:
Image - Function Arguments երկխոսության տուփը այն է, որտեղ մուտքագրվում են VLOOKUP ֆունկցիայի պարամետրերը:
Մուտքագրեք որոնման արժեքի փաստարկը
Սովորաբար, որոնման արժեքը համընկնում է տվյալների աղյուսակի առաջին սյունակի տվյալների դաշտին: Այս օրինակում որոնման արժեքը վերաբերում է այն մասի անվանմանը, որը ցանկանում եք գտնել տեղեկատվություն: Որոնման արժեքի համար տվյալների թույլատրելի տեսակներն են՝ տեքստային տվյալներ, տրամաբանական արժեքներ, թվեր և բջջային հղումներ։
Բացարձակ բջջային հղումներ
Երբ բանաձևերը պատճենվում են Excel-ում, բջջային հղումները փոխվում են՝ արտացոլելու նոր գտնվելու վայրը: Եթե դա տեղի ունենա, D2-ը՝ որոնման արժեքի բջջային հղումը, փոխվում է և սխալներ է ստեղծում F2 և G2 բջիջներում։
Բացարձակ բջջային հղումները չեն փոխվում բանաձևերը պատճենելիս:
Սխալները կանխելու համար D2 բջջային հղումը վերածեք բացարձակ բջջային հղումի: Բջջի բացարձակ հղում ստեղծելու համար սեղմեք F4 ստեղնը: Սա ավելացնում է դոլարի նշաններ բջջային հղումի շուրջ, ինչպիսին է $D$2:
-
Function Arguments երկխոսության վանդակում տեղադրեք կուրսորը lookup_value տեքստային վանդակում: Այնուհետև աշխատաթերթում ընտրեք բջիջ D2 ՝ այս բջիջի հղումը lookup_value-ին ավելացնելու համար: D2 բջիջը այն վայրն է, որտեղ մուտքագրվելու է մասի անունը:
Image -
Առանց տեղադրման կետը տեղափոխելու, սեղմեք F4 ստեղնը՝ D2-ը վերածելու $D$2 բացարձակ բջջային հղումի:
Image - Բաց թողեք VLOOKUP ֆունկցիայի երկխոսության տուփը ձեռնարկի հաջորդ քայլի համար:
Մուտքագրեք աղյուսակի զանգվածի փաստարկը
Սեղանի զանգվածը տվյալների աղյուսակն է, որը որոնում է որոնման բանաձևը՝ գտնելու ձեր ուզած տեղեկատվությունը: Աղյուսակային զանգվածը պետք է պարունակի տվյալների առնվազն երկու սյունակ։
Առաջին սյունակը պարունակում է որոնման արժեքի արգումենտը (որը ստեղծվել է նախորդ բաժնում), մինչդեռ երկրորդ սյունակը որոնվում է որոնման բանաձևով՝ գտնելու ձեր նշած տեղեկատվությունը:
Սեղանի զանգվածի արգումենտը պետք է մուտքագրվի որպես տվյալների աղյուսակի բջիջների հղումներ պարունակող միջակայք կամ որպես տիրույթի անուն:
Տվյալների աղյուսակը VLOOKUP ֆունկցիային ավելացնելու համար դրեք կուրսորը table_array տեքստային վանդակում երկխոսության վանդակում և մուտքագրեք Tableայս արգումենտի միջակայքի անունը մուտքագրելու համար:
Nest the COLUMN ֆունկցիան
Սովորաբար, VLOOKUP-ը վերադարձնում է տվյալներ տվյալների աղյուսակի միայն մեկ սյունակից:Այս սյունակը սահմանվում է սյունակի ինդեքսի համարի արգումենտով: Այս օրինակում, սակայն, կան երեք սյունակներ, և սյունակի ինդեքսի համարը պետք է փոխվի առանց որոնման բանաձևը խմբագրելու: Դա անելու համար COLUMN ֆունկցիան տեղադրեք VLOOKUP ֆունկցիայի ներսում որպես Col_index_num արգումենտ:
Ֆունկցիաները տեղադրելու ժամանակ Excel-ը չի բացում երկրորդ ֆունկցիայի երկխոսության տուփը՝ դրա արգումենտները մուտքագրելու համար: COLUMN ֆունկցիան պետք է մուտքագրվի ձեռքով: COLUMN ֆունկցիան ունի միայն մեկ արգումենտ՝ Reference արգումենտը, որը բջջային հղում է։
COLUMN ֆունկցիան վերադարձնում է որպես Reference արգումենտ տրամադրված սյունակի թիվը: Այն սյունակային տառը վերածում է թվի։
Ապրանքի գինը գտնելու համար օգտագործեք տվյալների աղյուսակի 2-րդ սյունակի տվյալները: Այս օրինակն օգտագործում է B սյունակը որպես հղում՝ 2-ը Col_index_num արգումենտում տեղադրելու համար:
-
Function Arguments երկխոսության վանդակում տեղադրեք կուրսորը Col_index_num տեքստային վանդակում և մուտքագրեք COLUMN(:(Անպայման ներառեք բաց կլոր փակագիծը:)
Image -
Աշխատանքային թերթում ընտրեք բջիջ B1՝ այդ բջիջի հղումը որպես Reference արգումենտ մուտքագրելու համար:
Image - Մուտքագրեք փակվող կլոր փակագիծ՝ COLUMN ֆունկցիան ավարտելու համար:
Մուտքագրեք VLOOKUP միջակայքի որոնման փաստարկը
VLOOKUP-ի Range_lookup արգումենտը տրամաբանական արժեք է (TRUE կամ FALSE), որը ցույց է տալիս, թե արդյոք VLOOKUP-ը պետք է ճշգրիտ կամ մոտավոր համապատասխանություն գտնի Lookup_value-ին:
- ՃԻՇՏ կամ բաց թողնված. VLOOKUP-ը սերտ համընկնում է Lookup_value-ին: Եթե ճշգրիտ համընկնում չի գտնվել, VLOOKUP-ը վերադարձնում է հաջորդ ամենամեծ արժեքը: Table_array-ի առաջին սյունակի տվյալները պետք է տեսակավորվեն աճման կարգով:
- FALSE. VLOOKUP-ը ճշգրիտ համընկնում է Lookup_value-ի հետ: Եթե Table_array-ի առաջին սյունակում կան երկու կամ ավելի արժեքներ, որոնք համապատասխանում են որոնման արժեքին, ապա օգտագործվում է առաջին հայտնաբերված արժեքը: Եթե ճշգրիտ համընկնում չի գտնվել, ապա վերադարձվում է N/A սխալ:
Այս ձեռնարկում կփնտրվի որոշակի ապարատային տարրի մասին հատուկ տեղեկատվություն, ուստի Range_lookup-ը դրված է FALSE-ի:
Function Arguments երկխոսության վանդակում տեղադրեք կուրսորը Range_lookup տեքստային դաշտում և մուտքագրեք False՝ VLOOKUP-ին ասելու, որ տվյալների ճշգրիտ համընկնում է::
Ընտրեք OK՝ որոնման բանաձևը ավարտելու և երկխոսության տուփը փակելու համար: E2 բջիջը կպարունակի N/A սխալ, քանի որ որոնման չափանիշները մուտքագրված չեն D2 բջիջում: Այս սխալը ժամանակավոր է: Այն կուղղվի, երբ որոնման չափանիշներն ավելացվեն այս ձեռնարկի վերջին քայլում:
Պատճենեք Փնտրման բանաձևը և մուտքագրեք չափանիշները
Փնտրման բանաձևը տվյալներ է առբերում տվյալների աղյուսակի մի քանի սյունակներից միաժամանակ: Դա անելու համար որոնման բանաձևը պետք է լինի բոլոր այն դաշտերում, որտեղից ցանկանում եք տեղեկատվություն:
Տվյալների աղյուսակի 2-րդ, 3-րդ և 4-րդ սյունակներից տվյալներ ստանալու համար (գինը, մասի համարը և մատակարարի անունը), մուտքագրեք մասնակի անուն որպես Փնտրման_արժեք:
Քանի որ տվյալները դրված են աշխատանքային թերթում սովորական օրինակով, պատճենեք որոնման բանաձևը բջիջ E2 բջիջներ F2 և G2 Բանաձևի պատճենման հետ մեկտեղ Excel-ը թարմացնում է բջիջի հարաբերական հղումը COLUMN ֆունկցիայի (բջիջ B1)՝ արտացոլելու բանաձևի նոր գտնվելու վայրը: Excel-ը չի փոխում բջիջների բացարձակ հղումը (օրինակ՝ $D$2) և անվանված միջակայքը (Աղյուսակ), քանի որ բանաձևը պատճենվում է:
Excel-ում տվյալները պատճենելու մեկից ավելի եղանակ կա, բայց ամենահեշտը Fill Handle-ն օգտագործելն է:
-
Ընտրեք բջիջ E2, որտեղ գտնվում է որոնման բանաձևը, այն ակտիվ բջիջ դարձնելու համար:
Image -
Քաշեք լրացման բռնակը դեպի բջիջ G2: F2 և G2 բջիջները ցուցադրում են N/A սխալը, որն առկա է E2 բջիջում:
Image -
Տվյալների աղյուսակից տեղեկատվություն ստանալու համար որոնման բանաձևերն օգտագործելու համար աշխատաթերթում ընտրեք բջիջ D2, մուտքագրեք Վիջեթ և սեղմեք Մուտքագրեք.
Image Հետևյալ տեղեկատվությունը ցուցադրվում է E2-ից մինչև G2 բջիջներում:
- E2: $14,76 - վիջեթի գինը
- F2: PN-98769 - վիջեթի մասի համարը
- G2: Widgets Inc. - վիջեթների մատակարարի անվանումը
-
VLOOKUP զանգվածի բանաձևը փորձարկելու համար մուտքագրեք այլ մասերի անվանումը D2 բջիջում և դիտեք արդյունքները E2-ից մինչև G2 բջիջներում:
Image - Փնտրման բանաձևը պարունակող յուրաքանչյուր բջիջ պարունակում է տարբեր տվյալներ ձեր որոնած ապարատային տարրի վերաբերյալ:
VLOOKUP ֆունկցիան՝ COLUMN-ի նման ներդիր ֆունկցիաներով, հզոր մեթոդ է տրամադրում աղյուսակի ներսում տվյալներ փնտրելու համար՝ օգտագործելով այլ տվյալներ որպես որոնման հղում:
