Օգտագործելով VLOOKUP ֆունկցիան՝ արժեքները փոխարինելու համար

Ով չափազանց ծույլ է կամ ժամանակ չունի կարդալու՝ դիտե՛ք տեսանյութը։ Մանրամասներն ու նրբությունները՝ ստորև ներկայացված տեքստում։

Խնդրի ձևակերպում

Այսպիսով, մենք ունենք երկու սեղան. պատվերի աղյուսակ и Գնացուցակ:

Խնդիրը գնացուցակից գները ավտոմատ կերպով փոխարինել պատվերների աղյուսակում՝ կենտրոնանալով ապրանքի անվան վրա, որպեսզի հետագայում կարողանաք հաշվարկել արժեքը:

լուծում

Excel ֆունկցիայի հավաքածուում՝ կատեգորիայի տակ Հղումներ և զանգվածներ (Փնտրում և տեղեկանք) ֆունկցիա կա VPR- ը (VLOOKUP).Այս ֆունկցիան փնտրում է տրված արժեք (մեր օրինակում սա «Խնձոր» բառն է) նշված աղյուսակի (գնացուցակի) ձախ սյունակում՝ վերևից ներքև շարժվող և, գտնելով այն, ցուցադրում է հարակից բջիջի բովանդակությունը։ (23 ռուբլի) .Սխեմատիկորեն այս ֆունկցիայի գործողությունը կարող է ներկայացվել Այսպիսով.

Գործառույթի հետագա օգտագործման հեշտության համար միանգամից մի բան արեք՝ գնացուցակի բջիջների տիրույթին տվեք ձեր սեփական անունը: Դա անելու համար ընտրեք գնացուցակի բոլոր բջիջները, բացառությամբ «վերնագրի» (G3: H19), ընտրեք ցանկից: Տեղադրել – Անուն – Նշանակել (Տեղադրել — Անուն — Սահմանել) կամ սեղմեք CTRL + F3 և մուտքագրեք ցանկացած անուն (առանց բացատների): Գին… Այժմ, ապագայում, դուք կարող եք օգտագործել այս անունը՝ գնացուցակին կապելու համար:

Այժմ մենք օգտագործում ենք գործառույթը VPR- ը… Ընտրեք այն բջիջը, որտեղ այն կմուտքագրվի (D3) և բացեք ներդիրը Բանաձևեր – Ֆունկցիայի ներդրում (Բանաձևեր — Տեղադրեք ֆունկցիա)… Կատեգորիայում Հղումներ և զանգվածներ (Փնտրում և հղում) գտնել գործառույթը VPR- ը (VLOOKUP) եւ սեղմեք OK… Ֆունկցիայի համար արգումենտներ մուտքագրելու պատուհան կհայտնվի.

Օգտագործելով VLOOKUP ֆունկցիան՝ արժեքները փոխարինելու համար

Մենք դրանք լրացնում ենք հերթով.

  • Ցանկալի արժեք (Փնտրման արժեքը) – ապրանքի անվանումը, որը գործառույթը պետք է գտնի գնացուցակի ամենաձախ սյունակում: Մեր դեպքում B3 բջիջից «խնձոր» բառը:
  • Սեղան (Սեղանի զանգված) – աղյուսակ, որից վերցված են uXNUMXbuXNUMX ցանկալի արժեքները, այսինքն՝ մեր գնացուցակը: Հղման համար մենք օգտագործում ենք նախկինում տրված մեր սեփական անունը՝ «Գին»: Եթե ​​անուն չեք տվել, կարող եք պարզապես ընտրել աղյուսակը, բայց մի մոռացեք սեղմել կոճակը F4դոլարի նշաններով հղումը ամրացնելու համար, քանի որ հակառակ դեպքում, այն կսահի ներքև, երբ պատճենենք մեր բանաձևը դեպի D3:D30 սյունակի մնացած բջիջները:
  • Սյունակի_համար (Սյունակի ինդեքսի համարը) – Սերիայի համարը (ոչ տառ!) Գնացուցակի այն սյունակի, որից մենք կվերցնենք գնային արժեքները: Գնացուցակի առաջին սյունակը անվանումներով համարակալված է 1, հետևաբար մեզ անհրաժեշտ է գինը 2 համարով սյունակից։
  • interval_lookup (Տեսականի որոնում) - այս դաշտում կարող են մուտքագրվել միայն երկու արժեք՝ FALSE կամ TRUE:
      • Եթե ​​մուտքագրված է արժեք 0 or ՍՈՒՏ (ՍՈՒՏ), ապա իրականում սա նշանակում է, որ թույլատրվում է միայն որոնումը ճշգրիտ համընկնում, այսինքն, եթե գործառույթը չգտնի գնացուցակի պատվերի աղյուսակում նշված ոչ ստանդարտ ապրանքը (եթե, օրինակ, «Coconut» է մուտքագրված), այն կառաջացնի #N/A (տվյալներ չկան) սխալ:
      • Եթե ​​մուտքագրված է արժեք 1 or TRUE (ՃԻՇՏ), ապա սա նշանակում է, որ դուք թույլ եք տալիս որոնումը ոչ թե ճշգրիտ, այլ մոտավոր համընկնում, այսինքն՝ «կոկոսի» դեպքում ֆունկցիան կփորձի գտնել «կոկոսին» հնարավորինս մոտ անուն ունեցող ապրանք և վերադարձնել այս անվանման գինը։ Շատ դեպքերում, նման մոտավոր փոխարինումը կարող է խաբել օգտատիրոջը՝ փոխարինելով սխալ արտադրանքի արժեքը, որն իրականում եղել է այնտեղ: Այսպիսով, իրական բիզնեսի խնդիրների մեծ մասի համար մոտավոր որոնումը ավելի լավ է թույլ չտալ: Բացառություն է, երբ մենք փնտրում ենք թվեր, այլ ոչ թե տեքստ, օրինակ՝ Քայլի զեղչերը հաշվարկելիս:

Ամեն ինչ! Մնում է սեղմել OK և մուտքագրված գործառույթը պատճենեք ամբողջ սյունակում:

# N / A սխալները և դրանց ճնշումը

ֆունկցիա VPR- ը (VLOOKUP) վերադարձնում է #N/A սխալը (# N/A) Եթե:

  • Ճշգրիտ որոնումը միացված է (փաստարկ Ինտերվալային դիտում = 0) և ցանկալի անունը չկա Սեղան.
  • Ներառված է կոպիտ որոնում (Ինտերվալային դիտում = 1), բայց Սեղան, որտեղ որոնումն իրականացվում է անունների աճման կարգով դասավորված չէ։
  • Բջջի ձևաչափը, որտեղից գալիս է անվան պահանջվող արժեքը (օրինակ՝ B3 մեր դեպքում) և աղյուսակի առաջին սյունակի (F3: F19) բջիջների ձևաչափը տարբեր են (օրինակ՝ թվային և տեքստային): ) Այս դեպքը հատկապես բնորոշ է, երբ տեքստային անունների փոխարեն օգտագործվում են թվային կոդեր (հաշվի համարներ, նույնացուցիչներ, ամսաթվեր և այլն): Այս դեպքում դուք կարող եք օգտագործել գործառույթները Ч и TEXT տվյալների ձևաչափերը փոխարկելու համար: Այն նման տեսք կունենա.

    =VLOOKUP(TEXT(B3),գին,0)

    Այս մասին ավելին կարող եք կարդալ այստեղ:

  • Ֆունկցիան չի կարող գտնել անհրաժեշտ արժեքը, քանի որ կոդը պարունակում է բացատներ կամ անտեսանելի, չտպվող նիշեր (գծերի ընդմիջումներ և այլն): Այս դեպքում կարող եք օգտագործել տեքստային գործառույթներ TRIM- ը (TRIM) и ՏՊԵԼ(ՄԱՔՈՒՐ) դրանք հեռացնելու համար.

    = VLOOKUP (ՏՐԱՄԱՏԵՂԵՐ (ՄԱՔՈՒՐ (B3)), գին, 0)

    =VLOOKUP(TRIM(CLEAN(B3));գին;0)

Սխալի հաղորդագրությունը ճնշելու համար # N / A (# N/A) այն դեպքերում, երբ ֆունկցիան չի կարող ճշգրիտ համապատասխանություն գտնել, կարող եք օգտագործել ֆունկցիան ԻՐԱER (ԻՖԵՐՈՐ)… Այսպիսով, օրինակ, այս կոնստրուկցիան ընդհատում է VLOOKUP-ի կողմից ստեղծված ցանկացած սխալ և դրանք փոխարինում զրոներով.

= IFERROR (VLOOKUP (B3, գին, 2, 0), 0)

= IFERROR (VLOOKUP (B3; գին; 2; 0); 0)

PS

Եթե ​​Ձեզ անհրաժեշտ է հանել ոչ թե մեկ արժեք, այլ ամբողջ հավաքածուն միանգամից (եթե կան մի քանի տարբեր), ապա ստիպված կլինեք շամանացնել զանգվածի բանաձևով։ կամ օգտագործեք նոր XLOOKUP գործառույթը Office 365-ից:

 

  • VLOOKUP ֆունկցիայի բարելավված տարբերակը (VLOOKUP 2):
  • Քայլերի (միջակայքի) զեղչերի արագ հաշվարկ՝ օգտագործելով VLOOKUP ֆունկցիան:
  • Ինչպես կատարել «ձախ VLOOKUP»՝ օգտագործելով INDEX և MATCH գործառույթները
  • Ինչպես օգտագործել VLOOKUP ֆունկցիան՝ ցուցակից տվյալները լրացնելու համար
  • Ինչպես սեղանից միանգամից հանել ոչ թե առաջինը, այլ բոլոր արժեքները
  • VLOOKUP2 և VLOOKUP3 գործառույթները PLEX հավելումից

 

Թողնել գրառում