Գտնել վերջին երևույթը (շրջված VLOOKUP)

Բոլոր դասական որոնման և տիպի փոխարինման գործառույթները VPR- ը (VLOOKUP), GPR (HLOOKUP), ԱՎԵԼԻ ԲԱՑՎԱԾ (ՄԱՏՉ) իսկ նրանց նմաններն ունեն մեկ կարևոր հատկություն՝ որոնում են սկզբից մինչև վերջ, այսինքն՝ սկզբնաղբյուրի տվյալների մեջ ձախից աջ կամ վերևից ներքև։ Հենց որ գտնվի առաջին համապատասխանող համընկնումը, որոնումը դադարում է և հայտնաբերվում է մեզ անհրաժեշտ տարրի միայն առաջին դեպքը:

Ի՞նչ անել, եթե մեզ անհրաժեշտ է գտնել ոչ թե առաջին, այլ վերջին դեպքը: Օրինակ, վերջին գործարքը հաճախորդի համար, վերջին վճարումը, ամենավերջին պատվերը և այլն:

Մեթոդ 1. Գտնել վերջին տողը զանգվածի բանաձևով

Եթե ​​սկզբնական աղյուսակը չունի սյունակ՝ ամսաթվով կամ տողի հերթական համարով (պատվեր, վճարում…), ապա մեր խնդիրն է, ըստ էության, գտնել տվյալ պայմանին բավարարող վերջին տողը։ Դա կարելի է անել հետևյալ զանգվածային բանաձևով.

Այստեղ `

• ֆունկցիա IF (ԵԹԵ) ստուգում է սյունակի բոլոր բջիջները մեկ առ մեկ Հաճախորդ և ցուցադրում է տողի համարը, եթե այն պարունակում է մեզ անհրաժեշտ անունը: Թերթի տողի համարը մեզ տրվում է ֆունկցիայով LINE (ROW), բայց քանի որ մեզ անհրաժեշտ է աղյուսակում տողի համարը, մենք լրացուցիչ պետք է հանենք 1, քանի որ աղյուսակում ունենք վերնագիր։
  
• Այնուհետև գործառույթը MAX (MAX) ընտրում է առավելագույն արժեքը տողերի թվերի ձևավորված շարքից, այսինքն՝ հաճախորդի ամենավերջին տողի թիվը:
• ֆունկցիա INDEX (INDEX) վերադարձնում է բջիջի բովանդակությունը գտնված վերջին թվով ցանկացած այլ պահանջվող աղյուսակի սյունակից (Պատվերի ծածկագիր).

Այս ամենը պետք է մուտքագրվի որպես զանգվածի բանաձեւ, այսինքն.

• Office 365-ում՝ տեղադրված վերջին թարմացումներով և դինամիկ զանգվածների աջակցությամբ, կարող եք պարզապես սեղմել Մտնել.
• Մնացած բոլոր տարբերակներում բանաձևը մուտքագրելուց հետո ստիպված կլինեք սեղմել ստեղնաշարի դյուրանցումը Ctrl+Հերթափոխություն+Մտնել, որն ավտոմատ կերպով դրան բանաձևի տողում կավելացնի գանգուր բրեկետներ:

Մեթոդ 2. Հակադարձ որոնում նոր LOOKUP ֆունկցիայի միջոցով

Ես արդեն գրել եմ երկար հոդված՝ նոր ֆունկցիայի մասին տեսահոլովակով VIEW (XLOOKUP), որը հայտնվել է Office-ի վերջին տարբերակներում՝ հին VLOOKUP-ին փոխարինելու համար (VLOOKUP). BROWSE-ի օգնությամբ մեր խնդիրը լուծվում է բավականին տարրական, քանի որ. այս ֆունկցիայի համար (ի տարբերություն VLOOKUP-ի), դուք կարող եք բացահայտորեն սահմանել որոնման ուղղությունը՝ վերևից ներքև կամ ներքևից վեր. դրա համար պատասխանատու է վերջին արգումենտը (-1).

Մեթոդ 3. Փնտրեք վերջին ամսաթվով տող

Եթե ​​սկզբնաղբյուրի տվյալների մեջ մենք ունենք սերիական համարով կամ ամսաթվով սյունակ, որը նման դեր է խաղում, ապա առաջադրանքը փոփոխվում է. մենք պետք է գտնենք ոչ թե վերջին (ամենացածր) տողը համընկնումով, այլ ամենավերջինով տողը ( առավելագույնը) ամսաթիվը:

Ես արդեն մանրամասն քննարկել եմ, թե ինչպես դա անել՝ օգտագործելով դասական ֆունկցիաները, իսկ հիմա փորձենք օգտագործել նոր դինամիկ զանգվածի ֆունկցիաների հզորությունը։ Ավելի մեծ գեղեցկության և հարմարավետության համար մենք նաև բնօրինակ աղյուսակը վերածում ենք «խելացի» սեղանի՝ օգտագործելով ստեղնաշարի դյուրանցումը: Ctrl+T կամ հրամաններ Գլխավոր – Ձևաչափել որպես աղյուսակ (Տուն — Ձևաչափել որպես աղյուսակ).

Նրանց օգնությամբ այս «մարդասպան զույգը» շատ նրբագեղ լուծում է մեր խնդիրը.

Այստեղ `

• Գործեք առաջինը Ֆիլտր (ԶԻՏՐ) ընտրում է միայն այն տողերը մեր աղյուսակից, որտեղ գտնվում է սյունակը Հաճախորդ - մեզ անհրաժեշտ անունը:
• Այնուհետև գործառույթը GRADE (ՏԵՍԱԿԱՎՈՐՈՒՄ) տեսակավորում է ընտրված տողերը ըստ ամսաթվի՝ նվազման կարգով, իսկ ամենավերջին գործարքը վերևում:
• ֆունկցիա INDEX (INDEX) հանում է առաջին շարքը, այսինքն՝ վերադարձնում է մեզ անհրաժեշտ վերջին առևտուրը:
• Եվ, վերջապես, արտաքին FILTER ֆունկցիան արդյունքներից հեռացնում է լրացուցիչ 1-ին և 3-րդ սյունակները (Պատվերի ծածկագիր и Հաճախորդ) և թողնում է միայն ամսաթիվը և գումարը: Դրա համար օգտագործվում է հաստատունների զանգված: {0;1;0;1}, սահմանելով, թե որ սյունակները մենք ցանկանում ենք (1) կամ չենք ցանկանում (0) ցուցադրել:
  

Մեթոդ 4. Գտնել վերջին համընկնումը Power Query-ում

Դե, հանուն ամբողջականության, եկեք նայենք մեր հակադարձ որոնման խնդրի լուծմանը՝ օգտագործելով Power Query հավելումը: Նրա օգնությամբ ամեն ինչ շատ արագ ու գեղեցիկ է լուծվում։

1. Եկեք վերափոխենք մեր սկզբնական աղյուսակը «խելացի» ստեղնաշարի դյուրանցման միջոցով Ctrl+T կամ հրամաններ Գլխավոր – Ձևաչափել որպես աղյուսակ (Տուն — Ձևաչափել որպես աղյուսակ).

2. Բեռնեք այն Power Query-ում կոճակով Սեղանից / միջակայքից ականջակալ Ամսաթիվ (Տվյալներ՝ աղյուսակից/միջակայքից).

3. Մենք դասավորում ենք (վերնագրի ֆիլտրի բացվող ցանկի միջոցով) մեր աղյուսակը ամսաթվի նվազման կարգով, որպեսզի ամենավերջին գործարքները վերևում լինեն:

4… Ներդիրում Փոխակերպում ընտրիր թիմ Խմբի կողմից (Փոխակերպում — Խմբով ըստ) և սահմանեք խմբավորումն ըստ հաճախորդների, և որպես ագրեգացիոն ֆունկցիա ընտրեք տարբերակը Բոլոր տողերը (Բոլոր տողերը). Դուք կարող եք անվանել նոր սյունակը, ինչ ցանկանում եք, օրինակ Մանրամասներ.

Խմբավորումից հետո մենք կստանանք մեր հաճախորդների եզակի անունների ցանկը և սյունակում Մանրամասներ – աղյուսակներ՝ դրանցից յուրաքանչյուրի բոլոր գործարքներով, որտեղ առաջին տողը կլինի վերջին գործարքը, ինչը մեզ անհրաժեշտ է.

5. Կոճակով ավելացրեք նոր հաշվարկված սյունակ Պատվերով սյունակ ականջակալ Ավելացնել սյունակ (Ավելացնել սյունակ — Ավելացնել հատուկ սյունակ)և մուտքագրեք հետևյալ բանաձևը.

Այստեղ Մանրամասներ – սա այն սյունակն է, որտեղից մենք վերցնում ենք աղյուսակները ըստ հաճախորդների, և 0 {} այն տողի թիվն է, որը մենք ցանկանում ենք հանել (Power Query-ում տողերի համարակալումը սկսվում է զրոյից): Մենք ստանում ենք սյունակ գրառումներով (Գրառում), որտեղ յուրաքանչյուր գրառում յուրաքանչյուր աղյուսակի առաջին տողն է.

Մնում է ընդլայնել բոլոր գրառումների բովանդակությունը սյունակի վերնագրի կրկնակի սլաքներով կոճակով Վերջին գործարքը ընտրելով ցանկալի սյունակները.

… և այնուհետև ջնջեք այն սյունակը, որն այլևս անհրաժեշտ չէ Մանրամասներ աջ սեղմելով դրա վերնագրի վրա – Հեռացնել սյունակները (Հեռացնել սյունակները).

Արդյունքները թերթիկի միջոցով վերբեռնելուց հետո Գլխավոր — Փակել և բեռնել — Փակել և բեռնել (Տուն — Փակել և բեռնել — Փակել և բեռնել…) մենք կստանանք այնպիսի գեղեցիկ աղյուսակ՝ վերջին գործարքների ցանկով, ինչպես ուզում էինք.

Երբ փոխում եք աղբյուրի տվյալները, չպետք է մոռանաք թարմացնել արդյունքները՝ աջ սեղմելով դրանց վրա՝ հրամանը Թարմացնել և պահպանել (Թարմացնել) կամ ստեղնաշարի դյուրանցում Ctrl+ալտ+F5.

• LOOKUP ֆունկցիան VLOOKUP-ի հետնորդն է
• Ինչպես օգտագործել նոր դինամիկ զանգվածի SORT, FILTER և UNIC գործառույթները
• Գտեք տողում կամ սյունակում վերջին ոչ դատարկ բջիջը LOOKUP ֆունկցիայի միջոցով