LAMBDA-ն Excel-ի նոր սուպեր ֆունկցիան է

Այս պահին Microsoft Excel-ն ունի գրեթե հինգ հարյուր աշխատաթերթի գործառույթներ, որոնք հասանելի են Function Wizard պատուհանի կոճակի միջոցով fx բանաձևի տողում: Սա շատ պարկեշտ հավաքածու է, բայց, այնուամենայնիվ, գրեթե յուրաքանչյուր օգտվող վաղ թե ուշ հանդիպում է մի իրավիճակի, երբ այս ցուցակը չի պարունակում իրեն անհրաժեշտ գործառույթը, պարզապես այն պատճառով, որ այն Excel-ում չէ:

Մինչ այժմ այս խնդիրը լուծելու միակ միջոցը մակրոներն էին, այսինքն՝ Visual Basic-ում գրել ձեր սեփական օգտատիրոջ կողմից սահմանված ֆունկցիան (UDF = User Defined Function), որը պահանջում է համապատասխան ծրագրավորման հմտություններ և երբեմն ամենևին էլ հեշտ չէ: Այնուամենայնիվ, Office 365-ի վերջին թարմացումներով իրավիճակը փոխվել է դեպի լավը. Excel-ում ավելացվել է հատուկ «փաթաթող» ֆունկցիա: ԼԱՄԲԴԱ. Նրա օգնությամբ ձեր սեփական գործառույթները ստեղծելու խնդիրն այժմ լուծվում է հեշտությամբ և գեղեցիկ:

Դիտարկենք դրա օգտագործման սկզբունքը հետևյալ օրինակում.

Ինչպես գիտեք, ամենայն հավանականությամբ, Excel-ն ունի մի քանի ամսաթվերի վերլուծման գործառույթներ, որոնք թույլ են տալիս որոշել տվյալ ամսաթվի համար օրվա, ամսվա, շաբաթվա և տարվա քանակը: Բայց ինչ-ինչ պատճառներով չկա եռամսյակի թիվը որոշող ֆունկցիա, որը նույնպես հաճախ է անհրաժեշտ, չէ՞։ Եկեք շտկենք այս թերությունը և ստեղծենք դրանով ԼԱՄԲԴԱ այս խնդիրը լուծելու սեփական նոր գործառույթը:

Քայլ 1. Գրեք բանաձևը

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

Քայլ 2. Փաթաթում LAMBDA-ում և փորձարկում

Այժմ ժամանակն է կիրառել նոր LAMBDA ֆունկցիան և փաթաթել մեր բանաձևը դրա մեջ: Ֆունկցիայի շարահյուսությունը հետևյալն է.

=LAMBDA(Փոփոխական 1; Փոփոխական 2; … Փոփոխական N ; Արտահայտություն)

որտեղ առաջինը նշված են մեկ կամ մի քանի փոփոխականների անուններ, իսկ վերջին արգումենտը միշտ բանաձև է կամ հաշվարկված արտահայտություն, որն օգտագործում է դրանք: Փոփոխականների անունները չպետք է նմանվեն բջիջների հասցեներին և չպետք է պարունակեն կետեր:

Մեր դեպքում կլինի միայն մեկ փոփոխական՝ այն ամսաթիվը, որի համար մենք հաշվարկում ենք եռամսյակի թիվը: Դրա համար անվանենք փոփոխականը, ասենք, դ. Այնուհետև մեր բանաձևը փաթաթելով ֆունկցիայի մեջ ԼԱՄԲԴԱ և սկզբնական A2 բջիջի հասցեն փոխարինելով ֆիկտիվ փոփոխականի անունով՝ մենք ստանում ենք.

Խնդրում ենք նկատի ունենալ, որ նման փոխակերպումից հետո մեր բանաձևը (իրականում, ճիշտ է) սկսեց սխալ առաջացնել, քանի որ այժմ A2 բջիջի սկզբնական ամսաթիվը չի փոխանցվում դրան: Փորձարկման և վստահության համար կարող եք փաստարկներ փոխանցել դրան՝ դրանք ֆունկցիայից հետո ավելացնելով ԼԱՄԲԴԱ փակագծերում՝

Քայլ 3. Ստեղծեք անուն

Այժմ հեշտ և զվարճալի մասի համար: Մենք բացում ենք Անունը կառավարիչ ականջակալ ֆորմուլա (Բանաձևեր — Անունների կառավարիչ) և կոճակով ստեղծել նոր անուն Ստեղծել (Ստեղծել). Գտեք և մուտքագրեք անուն մեր ապագա ֆունկցիայի համար (օրինակ՝ Նոմկվարթալա), և դաշտում կապ (Տեղեկանք) զգուշորեն պատճենեք բանաձևի տողից և տեղադրեք մեր գործառույթը ԼԱՄԲԴԱ, միայն առանց վերջին փաստարկի (A2):

Ամեն ինչ. Սեղմելուց հետո OK ստեղծված գործառույթը կարող է օգտագործվել այս աշխատանքային գրքի ցանկացած թերթիկի ցանկացած բջիջում.

Օգտագործեք այլ գրքերում

LAMBDA և դինամիկ զանգվածներ

Գործառույթով ստեղծված հատուկ գործառույթներ ԼԱՄԲԴԱ հաջողությամբ աջակցել նոր դինամիկ զանգվածների և դրանց գործառույթների հետ աշխատանքին (Ֆիլտր, ՅՈՒՆԻԿ, GRADE) ավելացվել է Microsoft Excel-ին 2020 թվականին:

Ենթադրենք, մենք ցանկանում ենք ստեղծել օգտվողի կողմից սահմանված նոր գործառույթ, որը կհամեմատի երկու ցուցակները և կվերադարձնի դրանց միջև եղած տարբերությունը՝ առաջին ցուցակի այն տարրերը, որոնք երկրորդում չեն: Կյանքի գործը, այնպես չէ՞: Նախկինում դրա համար նրանք օգտագործում էին կամ a la ֆունկցիաները VPR- ը (VLOOKUP), կամ PivotTables, կամ Power Query հարցումներ: Այժմ դուք կարող եք անել մեկ բանաձևով.

Անգլերեն տարբերակում կլինի.

=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)

Ահա ֆունկցիան COUNTIF հաշվում է առաջին ցուցակի յուրաքանչյուր տարրի առաջացման թիվը երկրորդում, այնուհետև ֆունկցիան Ֆիլտր ընտրում է նրանցից միայն նրանց, ովքեր չեն ունեցել այդ երևույթները: Այս կառույցը փաթաթելով ԼԱՄԲԴԱ և դրա հիման վրա անվանված տիրույթ ստեղծել՝ անունով, օրինակ, ՈՐՈՆՄԱՆ ՏԱՐԱԾՈՒՄ – մենք կստանանք հարմար ֆունկցիա, որը վերադարձնում է երկու ցուցակների համեմատության արդյունքը դինամիկ զանգվածի տեսքով.

Եթե ​​աղբյուրի տվյալները սովորական, այլ «խելացի» աղյուսակներ չեն, մեր գործառույթը նույնպես առանց խնդիրների կհաղթահարի.

Մեկ այլ օրինակ է տեքստի դինամիկ բաժանումը` այն վերածելով XML-ի և այնուհետև վերլուծելով այն բջիջ առ բջիջ, օգտագործելով FILTER.XML ֆունկցիան, որը մենք վերջերս վերլուծեցինք: Որպեսզի ամեն անգամ այս բարդ բանաձևը ձեռքով չվերարտադրվի, ավելի հեշտ կլինի այն փաթաթել LAMBDA-ով և դրա հիման վրա ստեղծել դինամիկ տիրույթ, այսինքն՝ նոր կոմպակտ և հարմար գործառույթ՝ անվանելով այն, օրինակ, RAZDTEXT.

Այս ֆունկցիայի առաջին արգումենտը կլինի սկզբնաղբյուր տեքստով բջիջը, իսկ երկրորդը՝ բաժանարար նիշը, և այն կվերադարձնի արդյունքը հորիզոնական դինամիկ զանգվածի տեսքով։ Գործառույթի կոդը կլինի հետևյալը.

=LAMBDA(t;d; TRANSPOSE(FILTER.XML(“«&ՓՈԽԱՐԱԿԻՑՆԵԼ (t;d? ««)&»«;»//Y»)))

Օրինակների ցանկն անվերջ է. ցանկացած իրավիճակում, երբ դուք հաճախ ստիպված եք լինում մուտքագրել նույն երկար և ծանր բանաձևը, LAMBDA ֆունկցիան զգալիորեն կհեշտացնի կյանքը:

Նիշերի ռեկուրսիվ թվարկում

Նախորդ բոլոր օրինակները ցույց են տվել LAMBDA ֆունկցիայի միայն մեկ, ամենաակնհայտ կողմը. դրա օգտագործումը որպես «փաթաթա»՝ երկար բանաձևերը փաթաթելու և դրանց մուտքագրումը պարզեցնելու համար: Իրականում LAMBDA-ն ունի ևս մեկ, շատ ավելի խորը կողմ, որն այն վերածում է գրեթե լիարժեք ծրագրավորման լեզվի:

Փաստն այն է, որ LAMBDA գործառույթների սկզբունքորեն կարևոր հատկանիշը դրանք իրականացնելու ունակությունն է հետադարձություն – հաշվարկների տրամաբանություն, երբ հաշվարկման գործընթացում ֆունկցիան իրեն կանչում է։ Սովորությունից ելնելով կարող է սողացող թվալ, բայց ծրագրավորման մեջ ռեկուրսիան սովորական բան է։ Նույնիսկ Visual Basic-ի մակրոներում դուք կարող եք այն իրականացնել, և այժմ, ինչպես տեսնում եք, այն հասել է Excel-ի: Փորձենք հասկանալ այս տեխնիկան գործնական օրինակով:

Ենթադրենք, մենք ցանկանում ենք ստեղծել օգտվողի կողմից սահմանված գործառույթ, որը կհեռացնի բոլոր տրված նիշերը սկզբնաղբյուր տեքստից: Նման ֆունկցիայի օգտակարությունը, կարծում եմ, ապացուցելու կարիք չկա. շատ հարմար կլինի դրա օգնությամբ մաքրել աղբոտված մուտքային տվյալները, այնպես չէ՞:

Սակայն, համեմատած նախորդ, ոչ ռեկուրսիվ օրինակների հետ, մեզ երկու դժվարություն է սպասում.

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

Եկեք անվանենք մեր ֆունկցիան, ասենք, CLEAN և մենք կցանկանայինք, որ այն ունենա երկու արգումենտ՝ մաքրվող տեքստը և բացառված նիշերի ցանկը որպես տեքստային տող:

Եկեք ստեղծենք, ինչպես նախկինում, ներդիրում ֆորմուլա в Անունների կառավարիչ անվանված տիրույթ, անվանեք այն CLEAR և մուտքագրեք դաշտում Շարք հետևյալ շինարարությունը.

=LAMBDA(t;d;IF(d="";t;CLEAR(ՓՈԽՆԱՐԿՈՒՄ(t;LEFT(d);"");MID(d;2;255))))

Այստեղ t փոփոխականը բնօրինակ տեքստն է, որը պետք է մաքրվի, իսկ d-ը ջնջվող նիշերի ցանկն է:

Ամեն ինչ աշխատում է այսպես.

բազմակրկնություն 1

SUBSTITUTE(t;LEFT(d);””) հատվածը, ինչպես կարող եք կռահել, փոխարինում է սկզբնական տեքստում t ջնջվելիք d հավաքածուի ձախ նիշի առաջին նիշը դատարկ տեքստային տողով, այսինքն՝ հեռացնում է “ Ա». Որպես միջանկյալ արդյունք, մենք ստանում ենք.

Vsh zkz n 125 ռուբլի:

բազմակրկնություն 2

Այնուհետև ֆունկցիան կանչում է իրեն և որպես մուտք (առաջին արգումենտ) ստանում է այն, ինչ մնացել է նախորդ քայլի մաքրումից հետո, իսկ երկրորդ արգումենտը բացառված նիշերի շարանն է, որը սկսվում է ոչ թե առաջին, այլ երկրորդ նիշից, այսինքն՝ «BVGDEEGZIKLMNOPRSTUFHTSCHSHSHSHCHYYYYYA: », առանց սկզբնական «A»-ի – դա արվում է MID ֆունկցիայի միջոցով: Ինչպես նախկինում, ֆունկցիան վերցնում է մնացածներից (B) ձախից առաջին նիշը և նրան տրված տեքստում (Zkz n 125 ռուբլի) փոխարինում է դատարկ տողով. միջանկյալ արդյունք ենք ստանում.

125 ռու.

բազմակրկնություն 3

Ֆունկցիան կրկին կանչում է իրեն՝ որպես առաջին արգումենտ ստանալով այն, ինչ մնացել է տեքստից, որը պետք է մաքրվի նախորդ կրկնության ժամանակ (Bsh zkz n 125 ru.), իսկ որպես երկրորդ արգումենտ՝ բացառված նիշերի հավաքածուն, որը կտրված է ևս մեկ նիշով։ ձախը, այսինքն՝ «VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYUYA.», առանց սկզբնական «B»-ի: Այնուհետև այն կրկին վերցնում է ձախից (B) առաջին նիշը այս հավաքածուից և հեռացնում այն ​​տեքստից. մենք ստանում ենք.

շ զկզ ն 125 ռու.

Եվ այսպես շարունակ. հուսով եմ, որ դուք կհասկանաք գաղափարը: Յուրաքանչյուր կրկնման դեպքում հեռացվող նիշերի ցանկը կկտրվի ձախ կողմում, և մենք կփնտրենք և կփոխարինենք հավաքածուի հաջորդ նիշը դատարկությամբ:

Երբ բոլոր նիշերը վերջանան, մենք պետք է դուրս գանք հանգույցից. այս դերը պարզապես կատարում է ֆունկցիան IF (ԵԹԵ), որի մեջ փաթաթված է մեր դիզայնը։ Եթե ​​ջնջելու համար նիշեր չեն մնացել (d=””), ապա ֆունկցիան այլևս չպետք է կանչի իրեն, այլ պարզապես պետք է վերադարձնի մաքրվող տեքստը (փոփոխական t) իր վերջնական տեսքով:

Բջիջների ռեկուրսիվ կրկնություն

Նմանապես, դուք կարող եք իրականացնել բջիջների ռեկուրսիվ թվարկում տվյալ տիրույթում: Ենթադրենք, որ ուզում ենք ստեղծել լամբդա ֆունկցիա անունով ՓՈԽԱՐԻՆՈՂՆԵՐԻ ՑԱՆԿ սկզբնաղբյուր տեքստում բեկորների մեծածախ փոխարինման համար՝ համաձայն տվյալ տեղեկատու ցանկի: Արդյունքը պետք է այսպիսին լինի.

Նրանք. մեր գործառույթում ՓՈԽԱՐԻՆՈՂՆԵՐԻ ՑԱՆԿ կլինեն երեք փաստարկներ.

1. բջիջ՝ մշակման ենթակա տեքստով (աղբյուրի հասցեն)
2. սյունակի առաջին բջիջը՝ որոնումից փնտրելու արժեքներով
3. սյունակի առաջին բջիջը փոխարինող արժեքներով որոնումից

Գործառույթը պետք է գնա գրացուցակի վերևից ներքև և հաջորդաբար փոխարինի ձախ սյունակի բոլոր տարբերակները Գտնել դեպի աջ սյունակից համապատասխան ընտրանքներ Փոխարինեք. Դուք կարող եք դա իրականացնել հետևյալ ռեկուրսիվ լամբդա ֆունկցիայի միջոցով.

Յուրաքանչյուր կրկնման ժամանակ Shift-ը ներքև իրականացվում է ստանդարտ excel ֆունկցիայի միջոցով ՕՏԱՐՈՒՄ (ՕՖՍԵՏ), որն այս դեպքում ունի երեք արգումենտ՝ սկզբնական տիրույթ, տողերի տեղաշարժ (1) և սյունակի հերթափոխ (0):

Դե, հենց որ հասնենք գրացուցակի ավարտին (n = «»), մենք պետք է ավարտենք ռեկուրսիան. մենք դադարում ենք ինքներս մեզ զանգահարել և ցուցադրում ենք այն, ինչ կուտակվել է բոլոր փոխարինումներից հետո սկզբնաղբյուր տեքստի t փոփոխականում:

Այսքանը: Ոչ մի բարդ մակրո կամ Power Query հարցումներ. ամբողջ խնդիրը լուծվում է մեկ գործառույթով:

• Ինչպես օգտագործել Excel-ի նոր դինամիկ զանգվածի գործառույթները՝ FILTER, SORT, UNIC
• Տեքստի փոխարինում և մաքրում SUBSTITUTE գործառույթով
  
• VBA-ում մակրոների և օգտագործողի կողմից սահմանված գործառույթների (UDF) ստեղծում