Excel VLOOKUP ֆունկցիայի բոլոր գաղտնիքները՝ աղյուսակում տվյալներ գտնելու և դրանք մեկ այլում հանելու համար

2024 Հեղինակ: Malcolm Clapton | [email protected]. Վերջին փոփոխված: 2023-12-17 03:59

Հոդվածը կարդալուց հետո դուք ոչ միայն կսովորեք, թե ինչպես գտնել տվյալներ Excel աղյուսակում և դրանք հանել մեկ այլ աղյուսակում, այլև տեխնիկա, որը կարող է օգտագործվել VLOOKUP ֆունկցիայի հետ համատեղ:

Excel-ում աշխատելիս շատ հաճախ անհրաժեշտություն է առաջանում մեկ աղյուսակում տվյալներ գտնել և դրանք հանել մյուսի մեջ: Եթե դեռ չգիտեք, թե ինչպես դա անել, ապա հոդվածը կարդալուց հետո դուք ոչ միայն կսովորեք, թե ինչպես դա անել, այլև կիմանաք, թե ինչ պայմաններում կարող եք սեղմել առավելագույն կատարումը համակարգից: Դիտարկված են շատ արդյունավետ մեթոդների մեծ մասը, որոնք պետք է օգտագործվեն VLOOKUP ֆունկցիայի հետ համատեղ:

Նույնիսկ եթե տարիներ շարունակ օգտագործում եք VLOOKUP ֆունկցիան, ապա մեծ հավանականությամբ այս հոդվածը ձեզ օգտակար կլինի և անտարբեր չի թողնի։ Օրինակ՝ լինելով ՏՏ մասնագետ, իսկ հետո՝ ՏՏ-ի ղեկավար, ես VLOOKUP-ն օգտագործում եմ արդեն 15 տարի, բայց բոլոր նրբերանգներով կարողացա գլուխ հանել միայն հիմա, երբ սկսեցի մարդկանց մասնագիտական հիմունքներով Excel սովորեցնել։

VLOOKUP -ի հապավումն է v ուղղահայաց Ն. Ս զննում. Նմանապես, VLOOKUP - Ուղղահայաց Փնտրում: Ֆունկցիայի հենց անվանումը մեզ հուշում է, որ այն որոնում է աղյուսակի տողերում (ուղղահայաց՝ կրկնում է տողերի վրայով և ամրացնում սյունակը), այլ ոչ թե սյունակներում (հորիզոնական՝ կրկնում է սյունակների վրա և ամրացնում տողը): Նշենք, որ VLOOKUP-ն ունի քույր՝ տգեղ բադի ձագ, ով երբեք կարապ չի դառնա՝ սա HLOOKUP ֆունկցիան է։ HLOOKUP-ը, ի տարբերություն VLOOKUP-ի, կատարում է հորիզոնական որոնումներ, սակայն Excel-ի հայեցակարգը (և իսկապես տվյալների կազմակերպման հայեցակարգը) ենթադրում է, որ ձեր աղյուսակներն ունեն ավելի քիչ սյունակներ և շատ ավելի շատ տողեր: Այդ իսկ պատճառով մենք պետք է որոնենք ըստ տողերի շատ անգամ ավելի հաճախ, քան սյունակներով։ Եթե Excel-ում շատ հաճախ եք օգտագործում HLO ֆունկցիան, ապա հավանական է, որ այս կյանքում ինչ-որ բան չեք հասկացել:

Շարահյուսություն

VLOOKUP ֆունկցիան ունի չորս պարամետր.

= VLOOKUP (;; [;]), այստեղ:

- ցանկալի արժեքը (հազվադեպ) կամ ցանկալի արժեքը պարունակող բջիջի հղում (դեպքերի ճնշող մեծամասնությունը);

- հղում բջիջների մի շարք (երկչափ զանգված), ԱՌԱՋԻՆ (!) սյունակում, որի պարամետրի արժեքը կփնտրվի.

- սյունակի համարը այն միջակայքում, որտեղից կվերադարձվի արժեքը.

- սա շատ կարևոր պարամետր է, որը պատասխանում է այն հարցին, թե արդյոք միջակայքի առաջին սյունակը դասավորված է աճման կարգով: Եթե զանգվածը տեսակավորված է, մենք նշում ենք TRUE կամ 1 արժեքը, հակառակ դեպքում՝ FALSE կամ 0: Եթե այս պարամետրը բաց թողնված է, այն լռելյայն սահմանում է 1:

Գրազ կգամ, որ նրանցից շատերը, ովքեր գիտեն VLOOKUP ֆունկցիան որպես շերտավոր, չորրորդ պարամետրի նկարագրությունը կարդալուց հետո նրանք կարող են անհարմար զգալ, քանի որ սովոր են տեսնել այն մի փոքր այլ ձևով. սովորաբար նրանք խոսում են ճշգրիտ համընկնման մասին, երբ որոնում (FALSE կամ 0) կամ միջակայքի սկանավորում (TRUE կամ 1):

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

Ինչպե՞ս է աշխատում VLOOKUP բանաձևը:

• Բանաձևի տեսակ I. Եթե վերջին պարամետրը բաց թողնված է կամ նշված է 1-ի հավասար, ապա VLOOKUP-ը ենթադրում է, որ առաջին սյունակը դասավորված է աճման կարգով, ուստի որոնումը դադարում է այն տողում, որը անմիջապես նախորդում է ցանկալիից մեծ արժեք պարունակող տողին … Եթե նման տող չի գտնվել, ապա տիրույթի վերջին տողը վերադարձվում է:

  

• Բանաձև II. Եթե վերջին պարամետրը նշված է որպես 0, ապա VLOOKUP-ը հաջորդաբար սկանավորում է զանգվածի առաջին սյունակը և անմիջապես դադարեցնում որոնումը, երբ հայտնաբերվում է պարամետրի հետ առաջին ճշգրիտ համապատասխանությունը, հակառակ դեպքում՝ # N / A (# N / A) սխալի կոդը: վերադարձվում է։

  

Աշխատանքային հոսքերի բանաձևեր

VPR տիպ I

VPR տեսակ II

Եզրակացություններ I ձևի բանաձևերի համար

1. Բանաձևերը կարող են օգտագործվել արժեքները միջակայքերում բաշխելու համար:
2. Եթե առաջին սյունակը պարունակում է կրկնօրինակ արժեքներ և ճիշտ է դասավորված, ապա կրկնվող արժեքներով տողերից վերջինը կվերադարձվի:
3. Եթե դուք փնտրում եք արժեք, որն ակնհայտորեն ավելի մեծ է, քան կարող է պարունակել առաջին սյունակը, ապա հեշտությամբ կարող եք գտնել աղյուսակի վերջին տողը, որը կարող է բավականին արժեքավոր լինել:
4. Այս տեսքը կվերադարձնի # N / A սխալը միայն այն դեպքում, եթե այն չգտնի ցանկալի արժեքից փոքր կամ հավասար արժեք:
5. Բավականին դժվար է հասկանալ, որ բանաձևը սխալ արժեքներ է վերադարձնում, եթե ձեր զանգվածը տեսակավորված չէ:

Եզրակացություններ II տիպի բանաձևերի համար

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

VLOOKUP կատարումը

Դուք հասել եք հոդվածի գագաթնակետին։ Թվում է, լավ, ի՞նչ տարբերություն, եթե որպես վերջին պարամետր նշեմ զրո կամ մեկ: Ըստ էության, բոլորը նշում են, իհարկե, զրո, քանի որ սա բավականին գործնական է. ձեզ հարկավոր չէ անհանգստանալ զանգվածի առաջին սյունակը տեսակավորելու մասին, դուք կարող եք անմիջապես տեսնել, արդյոք արժեքը գտնվել է, թե ոչ: Բայց եթե ձեր թերթիկում ունեք մի քանի հազար VLOOKUP բանաձև, ապա կնկատեք, որ VLOOKUP II-ը դանդաղ է աշխատում: Միևնույն ժամանակ, սովորաբար բոլորը սկսում են մտածել.

• Ինձ պետք է ավելի հզոր համակարգիչ;
• Ինձ ավելի արագ բանաձև է պետք, օրինակ, շատերը գիտեն INDEX + MATCH-ի մասին, որն իբր ավելի արագ է 5-10%-ով։

Իսկ քչերին է թվում, որ հենց որ սկսեք օգտագործել I տիպի VLOOKUP-ը և համոզվեք, որ առաջին սյունակը ամեն կերպ դասավորված է, VLOOKUP-ի արագությունը կավելանա 57 անգամ։ Բառերով եմ գրում՝ ՀԻՍՈՒՆՅՈԹ ԱՆԳԱՄ։ Ոչ թե 57%, այլ 5700%։ Ես բավականին հավաստիորեն ստուգեցի այս փաստը։

Նման արագ աշխատանքի գաղտնիքը կայանում է նրանում, որ տեսակավորված զանգվածի վրա կարող է կիրառվել չափազանց արդյունավետ որոնման ալգորիթմ, որը կոչվում է երկուական որոնում (կիսովացման մեթոդ, դիխոտոմիայի մեթոդ)։ Այսպիսով, I տիպի VLOOKUP-ը կիրառում է այն, իսկ II տիպի VLOOKUP-ը որոնում է առանց որևէ օպտիմալացման: Նույնը վերաբերում է MATCH ֆունկցիային, որը ներառում է նմանատիպ պարամետր, և LOOKUP ֆունկցիայի համար, որն աշխատում է միայն տեսակավորված զանգվածների վրա և ներառված է Excel-ում՝ Lotus 1-2-3-ի հետ համատեղելիության համար:

Բանաձևի թերությունները

VLOOKUP-ի թերություններն ակնհայտ են՝ նախ այն որոնում է միայն նշված զանգվածի առաջին սյունակում, և երկրորդ՝ միայն այս սյունակի աջ կողմում։ Եվ ինչպես հասկանում եք, կարող է պատահել, որ անհրաժեշտ տեղեկատվություն պարունակող սյունակը լինի այն սյունակի ձախ կողմում, որտեղ մենք կփնտրենք: INDEX + MATCH բանաձևերի արդեն նշված համակցությունը զուրկ է այս թերությունից, ինչը այն դարձնում է աղյուսակներից տվյալների արդյունահանման առավել ճկուն լուծումը VLOOKUP-ի (VLOOKUP) համեմատությամբ:

Բանաձևի կիրառման որոշ ասպեկտներ իրական կյանքում

Շրջանակի որոնում

Տարածքի որոնման դասական նկարազարդումը զեղչը որոշելու խնդիրն է՝ ըստ պատվերի չափի:

Տեքստի տողերի որոնում

Իհարկե, VLOOKUP-ը փնտրում է ոչ միայն թվեր, այլև տեքստ: Պետք է նկատի ունենալ, որ բանաձևը չի տարբերում կերպարների դեպքը։ Եթե օգտագործում եք նիշերի նշաններ, կարող եք կազմակերպել անորոշ որոնում: Գոյություն ունեն երկու նիշք՝ «?» - փոխարինում է ցանկացած նիշ տեքստային տողում, «*» - փոխարինում է ցանկացած նիշի ցանկացած քանակ:

Մարտական տարածքներ

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

Տարբեր տվյալների ձևաչափ

Եթե VLOOKUP ֆունկցիայի առաջին պարամետրը վերաբերում է մի բջիջի, որը պարունակում է թիվ, բայց որը պահվում է վանդակում որպես տեքստ, իսկ զանգվածի առաջին սյունակը պարունակում է ճիշտ ձևաչափով թվեր, ապա որոնումը չի հաջողվի: Հնարավոր է նաև հակառակ իրավիճակը։ Խնդիրը հեշտությամբ կարելի է լուծել՝ 1-ին պարամետրը թարգմանելով անհրաժեշտ ձևաչափով.

= VLOOKUP (−− D7; Ապրանքներ! $ A $ 2: $ C $ 5; 3; 0) - եթե D7-ը պարունակում է տեքստ, իսկ աղյուսակը պարունակում է թվեր;

= VLOOKUP (D7 & ""); Ապրանքներ $ A $ 2 $ C $ 5; 3; 0) - և հակառակը:

Ի դեպ, տեքստը կարող եք թարգմանել թվի միանգամից մի քանի եղանակով, ընտրել.

• Կրկնակի ժխտում -D7.
• Բազմապատկում մեկ D7 * 1-ով:
• Զրո գումարում D7 + 0:
• Բարձրացնելով առաջին հզորությունը D7 ^ 1.

Թիվը տեքստի վերածելը կատարվում է դատարկ տողի հետ կապակցման միջոցով, որը ստիպում է Excel-ին փոխակերպել տվյալների տեսակը։

Ինչպես ճնշել # N / A

Սա շատ հարմար է անել IFERROR ֆունկցիայի հետ:

Օրինակ՝ = IFERROR (VLOOKUP (D7; Ապրանքներ! $ A $ 2: $ C $ 5; 3; 0); ""):

Եթե VLOOKUP-ը վերադարձնի # N/A սխալի կոդը, ապա IFERROR-ը կհատի այն և կփոխարինի պարամետր 2-ին (այս դեպքում՝ դատարկ տող), իսկ եթե սխալ տեղի չունենա, ապա այս ֆունկցիան կձևացնի, որ այն ընդհանրապես գոյություն չունի, բայց կա միայն VLOOKUP, որը վերադարձրեց նորմալ արդյունք:

Զանգված

Հաճախ նրանք մոռանում են զանգվածի հղումը դարձնել բացարձակ, իսկ ձգվելիս զանգվածը «լողում է»։ Մի մոռացեք օգտագործել $ A $ 2: $ C $ 5 A2: C5-ի փոխարեն:

Լավ գաղափար է, որ հղումների զանգվածը տեղադրվի աշխատանքային գրքույկի առանձին թերթիկի վրա: Այն չի ընկնում ոտքի տակ, և դա ավելի ապահով կլինի:

Ավելի լավ գաղափար կլիներ այս զանգվածը հայտարարել որպես անվանված տիրույթ:

Շատ օգտատերեր զանգված նշելիս օգտագործում են A: C-ի նման կառուցվածք՝ նշելով ամբողջ սյունակները: Այս մոտեցումն իրավունք ունի գոյություն ունենալ, քանի որ դուք փրկված եք այն փաստից, որ ձեր զանգվածը ներառում է բոլոր անհրաժեշտ տողերը: Եթե թերթի վրա տողեր եք ավելացնում սկզբնական զանգվածով, ապա A: C-ով նշված միջակայքը ճշգրտման կարիք չունի: Իհարկե, այս շարահյուսական կոնստրուկցիան ստիպում է Excel-ին մի փոքր ավելի շատ աշխատանք կատարել, քան տիրույթը ճշգրիտ նշելը, բայց այս գերավճարը կարող է անտեսվել: Խոսքը վայրկյանի հարյուրերորդականի մասին է։

Դե, հանճարի եզրին - դասավորել զանգվածը ձևով:

Օգտագործելով «COLUMN» ֆունկցիան՝ նշելու հանման ենթակա սյունակը

Եթե աղյուսակը, որում դուք տվյալներ եք վերցնում VLOOKUP-ի միջոցով, ունի նույն կառուցվածքը, ինչ որոնվող աղյուսակը, բայց պարզապես պարունակում է ավելի քիչ տողեր, ապա կարող եք օգտագործել «COLUMN ()» ֆունկցիան VLOOKUP-ում, որպեսզի ավտոմատ հաշվարկեք առբերվող սյունակների համարները: Այս դեպքում բոլոր VLOOKUP բանաձևերը կլինեն նույնը (ճշգրտված առաջին պարամետրի համար, որն ինքնաբերաբար փոխվում է): Նշենք, որ առաջին պարամետրն ունի բացարձակ սյունակի կոորդինատ:

Կոմպոզիտային բանալի ստեղծում & "|" &-ով

Եթե անհրաժեշտ է դառնում որոնել միաժամանակ մի քանի սյունակներով, ապա անհրաժեշտ է կազմել որոնման համար կոմպոզիտային բանալի։ Եթե վերադարձվող արժեքը չլիներ տեքստային (ինչպես «Կոդ» դաշտի դեպքում), այլ թվային, ապա դրա համար հարմար կլիներ SUMIFS բանաձևը, և սյունակի կոմպոզիտային ստեղն ընդհանրապես չէր պահանջվի:

Սա իմ առաջին հոդվածն է Lifehacker-ի համար: Եթե ձեզ դուր եկավ, հրավիրում եմ ձեզ այցելել, ինչպես նաև մեկնաբանություններում սիրով կարդաք VLOOKUP ֆունկցիան օգտագործելու ձեր գաղտնիքների մասին և այլն: Շնորհակալություն։:)