Տվյալների որոնում և համադրում

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

Պատկերացրեք, որ ունեք երկու առանձին Excel ֆայլ։ Մեկում ձեր բուհի առաջին կուրսի ուսանողների ցանկն է՝ իրենց անուններով և ուսանողական տոմսի համարներով (ID)։ Մյուսում՝ «Ներածություն ծրագրավորման» առարկայի քննության արդյունքներն են՝ միայն ուսանողի ID-ն և գնահատականը։ Ձեր խնդիրն է ստեղծել մեկ ընդհանուր հաշվետվություն, որտեղ յուրաքանչյուր ուսանողի անվան դիմաց կլինի նրա գնահատականը։

Իհարկե, կարելի է ձեռքով փնտրել յուրաքանչյուր ուսանողի ID-ն երկրորդ ֆայլում, պատճենել գնահատականը և տեղադրել առաջինում։ Բայց եթե ցուցակում հարյուրավոր ուսանողներ կան, այս գործընթացը կդառնա ձանձրալի և սխալների մեծ ռիսկ կպարունակի։ Excel-ն առաջարկում է հզոր ֆունկցիաներ՝ այս գործընթացը ավտոմատացնելու համար։

VLOOKUP՝ դասական գործիք որոնման համար

VLOOKUP ֆունկցիան (Vertical LOOKUP, ուղղահայաց որոնում) ամենահայտնի գործիքներից է այս խնդիրը լուծելու համար։ Այն որոնում է որևէ արժեք (օրինակ՝ ուսանողի ID) աղյուսակի առաջին սյունակում և վերադարձնում է համապատասխան տողի մեկ այլ սյունակի արժեքը։

Ֆունկցիայի կառուցվածքը հետևյալն է․ VLOOKUP(որոնվող_արժեք; աղյուսակ; սյունակի_համար; [ճշգրիտ_համընկնում])

Քննարկենք մեր օրինակը․

որոնվող_արժեք․ ուսանողի ID-ն է ձեր հիմնական ցուցակից (օրինակ՝ A2 բջիջը)։
աղյուսակ․ տվյալների տիրույթն է մյուս աշխատանքային թերթում, որտեղ պետք է կատարել որոնումը (օրինակ՝ Grades!A1:B150)։ Շատ կարևոր է, որ որոնվող արժեքները (ID-ները) գտնվեն այս տիրույթի առաջին սյունակում։
սյունակի_համար․ այն սյունակի հերթական համարն է, որտեղից պետք է վերադարձնել արդյունքը։ Մեր դեպքում դա 2-ն է, քանի որ գնահատականները երկրորդ սյունակում են։
ճշգրիտ_համընկնում․ պետք է նշել FALSE կամ 0՝ ճշգրիտ համընկնում գտնելու համար։ Սա ապահովում է, որ ֆունկցիան կգտնի միայն կոնկրետ ID-ին համապատասխանող գրառումը։

=VLOOKUP(A2; Grades!A$1:B$150; 2; FALSE)

Այս բանաձևը կվերցնի A2 բջիջի ID-ն, կփնտրի այն Grades թերթի A սյունակում և, գտնելով այն, կվերադարձնի նույն տողի B սյունակի արժեքը (գնահատականը)։

Սակայն VLOOKUP-ն ունի սահմանափակումներ։ Այն միշտ որոնում է միայն ընտրված տիրույթի առաջին սյունակում և կարող է վերադարձնել արժեքներ միայն աջ կողմում գտնվող սյունակներից։ Եթե ձեր տվյալները այլ կերպ են դասավորված, այս ֆունկցիան անօգուտ կլինի։

XLOOKUP՝ ժամանակակից և ճկուն այլընտրանք

Հաշվի առնելով VLOOKUP-ի սահմանափակումները՝ Microsoft-ը ներկայացրեց XLOOKUP ֆունկցիան, որն ավելի հզոր է, ճկուն և հեշտ օգտագործման մեջ։ Այն կարող է փոխարինել ոչ միայն VLOOKUP-ին, այլև HLOOKUP-ին (հորիզոնական որոնում)։

Հիմնական առավելություններն են․

Որոնում ցանկացած ուղղությամբ։ Ի տարբերություն VLOOKUP-ի, այն կարող է որոնել արժեքը և վերադարձնել արդյունքը թե՛ աջ, թե՛ ձախ գտնվող սյունակից։
Պարզ կառուցվածք։ Անհրաժեշտ չէ հաշվել սյունակների համարները։ Դուք պարզապես նշում եք որոնման սյունակը և արդյունքի սյունակը։
Ներդրված սխալների կառավարում։ Եթե արժեքը չի գտնվում, կարող եք նշել, թե ինչ տեքստ պետք է ցուցադրվի (օրինակ՝ «Չի հանձնել»)՝ առանց լրացուցիչ ֆունկցիաներ օգտագործելու։

=XLOOKUP(A2; Grades!A:A; Grades!B:B; "Գնահատական չկա")

Այս բանաձևում մենք նշում ենք, որ պետք է որոնել A2 բջիջի արժեքը Grades թերթի ամբողջ A սյունակում (Grades!A:A), իսկ արդյունքը վերադարձնել B սյունակից (Grades!B:B)։ Եթե ID-ն չգտնվի, բջջում կհայտնվի «Գնահատական չկա» տեքստը։ XLOOKUP-ը ժամանակակից Excel-ի տարբերակներում (2021 և Microsoft 365) նախընտրելի տարբերակն է։

INDEX և MATCH համադրություն

Նախքան XLOOKUP-ի հայտնվելը, Excel-ի փորձառու օգտագործողները բարդ որոնումների համար կիրառում էին INDEX և MATCH ֆունկցիաների համադրությունը։ Այս մեթոդը դեռևս արդիական է, հատկապես եթե աշխատում եք Excel-ի ավելի հին տարբերակներով։

Այս համադրությունն աշխատում է երկու փուլով․

MATCH ֆունկցիան գտնում է որոնվող արժեքի (օրինակ՝ ուսանողի ID-ի) դիրքը կամ հերթական համարը որևէ սյունակում։
INDEX ֆունկցիան վերցնում է այդ համարը և վերադարձնում է մեկ այլ սյունակի համապատասխան դիրքում գտնվող արժեքը։

Այս մոտեցումը թույլ է տալիս որոնում կատարել ցանկացած ուղղությամբ, քանի որ որոնման և արդյունքի սյունակները կապված չեն միմյանց հետ։

=INDEX(Grades!B:B; MATCH(A2; Grades!A:A; 0))

Այստեղ MATCH(A2; Grades!A:A; 0)-ն առաջինը կաշխատի։ Այն կգտնի A2-ի ID-ի տողի համարը Grades թերթի A սյունակում։ Ենթադրենք՝ դա 45-րդ տողն է։ Այնուհետև INDEX(Grades!B:B; 45) բանաձևը կվերադարձնի Grades թերթի B սյունակի 45-րդ բջջի արժեքը։

Թեև այս համադրությունը XLOOKUP-ից ավելի բարդ է թվում, այն անհավանական ճկունություն է տալիս և աշխատում է Excel-ի գրեթե բոլոր տարբերակներում։

Երբ օգտագործում եք VLOOKUP կամ INDEX/MATCH, և որոնվող արժեքը չի գտնվում, բջջում հայտնվում է #N/A սխալը։ Մեծ աղյուսակներում այս սխալները կարող են տգեղ տեսք ունենալ և խանգարել հետագա հաշվարկներին (օրինակ՝ միջին գնահատականը հաշվելիս)։

Այս խնդիրը լուծելու համար կա IFERROR ֆունկցիան։ Այն ստուգում է, թե արդյոք ձեր բանաձևը սխալ է վերադարձնում։ Եթե այո, այն փոխարինում է սխալը ձեր նշած արժեքով (օրինակ՝ 0-ով կամ «Բացակա» տեքստով)։

=IFERROR(VLOOKUP(A2; Grades!A:B; 2; FALSE); "Չի հանձնել")

Այս բանաձևը փորձում է կատարել VLOOKUP գործողությունը։ Եթե այն հաջող է, ցուցադրվում է գնահատականը։ Եթե VLOOKUP-ը վերադարձնում է #N/A սխալ, IFERROR-ը «բռնում» է այն և փոխարենը ցուցադրում «Չի հանձնել» տեքստը։ Սա ձեր հաշվետվությունները դարձնում է ավելի կոկիկ և ընթեռնելի։

Այժմ ամրապնդենք ստացած գիտելիքները մի քանի հարցի միջոցով։

Quiz Questions 1/5

Ո՞րն է VLOOKUP ֆունկցիայի հիմնական սահմանափակումը:

Quiz Questions 2/5

Excel-ի հին տարբերակներում, որտեղ բացակայում է XLOOKUP ֆունկցիան, ճկուն որոնում (օրինակ՝ աջից ձախ) կատարելու համար օգտվողները հաճախ համադրում են ________ և ________ ֆունկցիաները։

Տվյալների որոնման և համադրման այս գործիքները կտրուկ կբարձրացնեն ձեր աշխատանքի արդյունավետությունը՝ թույլ տալով արագ և ճշգրիտ կերպով միավորել տեղեկատվությունը տարբեր աղբյուրներից և ստեղծել ամբողջական հաշվետվություններ։

Excel համալսարանի մեթոդիստների համար