contents
Nan moman sa a, Microsoft Excel gen prèske senksan (XNUMX) fonksyon fèy travay ki disponib atravè fenèt Fonksyon Sòsye - bouton fx nan ba fòmil la. Sa a se yon seri trè desan, men, kanmenm, prèske chak itilizatè pi bonè oswa pita rankontre yon sitiyasyon kote lis sa a pa gen fonksyon li bezwen an - tou senpleman paske li pa nan Excel.
Jiska kounye a, sèl fason pou rezoud pwoblèm sa a se makro, sa vle di ekri pwòp fonksyon itilizatè ou defini (UDF = Fonksyon Itilizatè Defined) nan Visual Basic, ki mande konpetans pwogramasyon apwopriye epi li, pafwa, pa fasil ditou. Sepandan, ak dènye mizajou Biwo 365 yo, sitiyasyon an chanje pou pi bon - yo te ajoute yon fonksyon espesyal "anbalaj" nan Excel. LAMBDA. Avèk èd li, travay la pou kreye fonksyon pwòp ou a kounye a rezoud fasil ak trè byen.
Ann gade nan prensip itilizasyon li yo nan egzanp sa a.
Kòm ou gen plis chans konnen, Excel gen plizyè fonksyon parsing dat ki pèmèt ou detèmine kantite jou, mwa, semèn, ak ane pou yon dat. Men, pou kèk rezon pa gen okenn fonksyon ki detèmine kantite a nan trimès la, ki se tou souvan bezwen, dwa? Ann ranje enpèfeksyon sa a epi kreye ak LAMBDA pwòp nouvo fonksyon pou rezoud pwoblèm sa a.
Etap 1. Ekri fòmil la
Ann kòmanse ak lefèt ke manyèlman nan fason nòmal nou pral ekri yon fòmil nan yon selil fèy ki kalkile sa nou bezwen. Nan ka nimewo trimès la, sa a ka fè, pou egzanp, tankou sa a:
Etap 2. Vlope nan LAMBDA ak tès
Kounye a li lè pou aplike nouvo fonksyon LAMBDA a epi vlope fòmil nou an ladan l. Sentaks fonksyon an se jan sa a:
=LAMBDA(Varyab 1; Varyab 2; ... VaryabN ; ekspresyon)
kote non youn oswa plis varyab yo ki nan lis premye, ak dènye agiman an se toujou yon fòmil oswa yon ekspresyon kalkile ki sèvi ak yo. Non varyab pa ta dwe sanble ak adrès selil yo epi yo pa ta dwe genyen pwen.
Nan ka nou an, pral gen yon sèl varyab - dat pou nou kalkile nimewo trimès la. Ann rele varyab la pou li, di, d. Lè sa a, vlope fòmil nou an nan yon fonksyon LAMBDA epi ranplase adrès selil orijinal A2 a ak yon non varyab fiktif, nou jwenn:
Tanpri sonje ke apre yon transfòmasyon konsa, fòmil nou an (an reyalite, kòrèk!) te kòmanse pwodui yon erè, paske kounye a dat orijinal la soti nan selil A2 pa transfere nan li. Pou tès ak konfyans, ou ka pase agiman nan li lè w ajoute yo apre fonksyon an LAMBDA nan parantèz:
Etap 3. Kreye yon non
Koulye a, pou pati ki fasil ak amizan. Nou louvri Non Manadjè tab fòmil (Fòmil — Manadjè Non) epi kreye yon nouvo non ak bouton an Kreye (Kreye). Vini ak antre yon non pou pwochen fonksyon nou an (pa egzanp, Nomkvartala), ak nan jaden an Link (Referans) ak anpil atansyon kopye soti nan ba fòmil la epi kole fonksyon nou an LAMBDA, sèlman san dènye agiman an (A2):
Tout bagay. Apre klike sou OK fonksyon kreye a ka itilize nan nenpòt selil sou nenpòt fèy nan liv travay sa a:
Itilize nan lòt liv
LAMBDA ak etalaj dinamik
Fonksyon Custom kreye ak yon fonksyon LAMBDA sipòte travay avèk siksè ak nouvo etalaj dinamik ak fonksyon yo (FILTRE, UNIK, GRADE) te ajoute nan Microsoft Excel nan 2020.
Ann di nou vle kreye yon nouvo fonksyon itilizatè-defini ki ta konpare de lis epi retounen diferans ki genyen ant yo - eleman sa yo nan premye lis la ki pa nan dezyèm lan. Travay lavi a, pa vre? Précédemment, pou sa yo te itilize swa fonksyon a la VPR (VCHACHUP), oswa PivotTables, oswa demann Power Query. Koulye a, ou ka fè ak yon fòmil:
Nan vèsyon angle a li pral:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Isit la fonksyon an COUNTIF konte kantite ensidan chak eleman nan premye lis la nan dezyèm lan, epi answit fonksyon an FILTRE chwazi sèlman moun nan yo ki pa t gen evènman sa yo. Pa vlope estrikti sa a nan LAMBDA epi kreye yon seri non baze sou li ak yon non, pou egzanp, DISTRIBYON RECHECH – nou pral jwenn yon fonksyon pratik ki retounen rezilta a nan konpare de lis nan fòm lan nan yon etalaj dinamik:
Si done sous yo pa òdinè, men tab "entelijan", fonksyon nou an ap fè fas tou san pwoblèm:
Yon lòt egzanp se dinamik divize tèks pa konvèti li nan XML epi answit analiz li selil pa selil lè l sèvi avèk fonksyon FILTER.XML nou fèk analize. Nan lòd pa repwodui fòmil konplèks sa a manyèlman chak fwa, li pral pi fasil pou vlope li nan LAMBDA epi kreye yon seri dinamik ki baze sou li, sa vle di yon nouvo fonksyon kontra enfòmèl ant ak pratik, nonmen li, pou egzanp, RAZDTEXT:
Premye agiman nan fonksyon sa a pral selil la ak tèks sous la, ak dezyèm lan - karaktè separasyon an, epi li pral retounen rezilta a nan fòm lan nan yon etalaj dinamik orizontal. Kòd fonksyon an pral jan sa a:
=LAMBDA(t;d; TRANSPOSE(FILTER.XML(“
Lis egzanp yo se intèrminabl - nan nenpòt sitiyasyon kote ou souvan oblije antre nan menm fòmil la long ak ankonbran, fonksyon LAMBDA a pral rann lavi notables pi fasil.
Enimerasyon rekursif nan karaktè
Tout egzanp anvan yo te montre sèlman yon sèl, ki pi evidan, bò nan fonksyon LAMBDA - itilizasyon li kòm yon "anbalaj" pou vlope fòmil long nan li epi senplifye opinyon yo. An reyalite, LAMBDA gen yon lòt bò, ki pi pwofon, ki fè l tounen yon langaj pwogramasyon konplè.
Reyalite a se ke yon karakteristik fondamantal enpòtan nan fonksyon LAMBDA se kapasite nan aplike yo nan rkursyon – lojik nan kalkil, lè nan pwosesis la nan kalkil fonksyon an rele tèt li. Soti nan abitid la, li ka son grenpe, men nan pwogramasyon, recursion se yon bagay komen. Menm nan makro nan Visual Basic, ou ka aplike li, epi kounye a, jan ou ka wè, li te vini nan Excel. Ann eseye konprann teknik sa a ak yon egzanp pratik.
Sipoze nou vle kreye yon fonksyon itilizatè defini ki ta retire tout karaktè yo bay nan tèks sous la. Itilite nan yon fonksyon sa yo, mwen panse, ou pa bezwen pwouve - li ta trè pratik yo klè done D 'chaje ak èd li yo, dwa?
Sepandan, konpare ak egzanp anvan yo, ki pa rekursif, de difikilte ap tann nou.
- Nou pral gen pou vini ak yon non pou fonksyon nou an anvan nou kòmanse ekri kòd li a, paske nan li, non sa a pral deja itilize yo rele fonksyon nan tèt li.
- Antre nan yon fonksyon rekursif nan yon selil epi debogaj li pa espesifye agiman nan parantèz apre LAMBDA (jan nou te fè pi bonè) pa pral travay. Ou pral gen yo kreye yon fonksyon imedyatman "nan grate" nan Non Manadjè (Non Manadjè).
Ann rele fonksyon nou an, di, CLEAN epi nou ta renmen li gen de agiman - tèks la yo dwe netwaye ak lis la nan karaktè eskli kòm yon fisèl tèks:
Ann kreye, jan nou te fè pi bonè, sou tab la fòmil в Non manadjè non ranje, non li CLEAR epi antre nan jaden an Range konstriksyon sa yo:
=LAMBDA(t;d;SI(d=””;t;CLEAR(SUBSTITUTE(t;GOCH(d);””);MID(d;2;255))))
Isit la varyab t se tèks orijinal yo dwe efase, ak d se lis karaktè yo dwe efase.
Li tout travay tankou sa a:
Iterasyon 1
Fragman SUBSTITUTE(t;LEFT(d);""), jan ou ta ka devine, ranplase premye karaktè ki soti nan karaktè gòch la nan seri d la yo dwe efase nan tèks sous t la ak yon fisèl tèks vid, sa vle di retire " A”. Kòm yon rezilta entèmedyè, nou jwenn:
Vsh zkz n 125 rubles.
Iterasyon 2
Lè sa a, fonksyon an rele tèt li epi kòm opinyon (premye agiman an) resevwa sa ki rete apre netwaye nan etap anvan an, ak agiman an dezyèm se yon seri karaktè eskli kòmanse pa nan premye a, men nan dezyèm karaktè a, sa vle di "BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYA. ," san inisyal "A" - sa a se fè pa fonksyon MID la. Kòm anvan, fonksyon an pran premye karaktè ki soti nan bò gòch la nan rès yo (B) epi ranplase li nan tèks yo ba li (Zkz n 125 rubles) ak yon fisèl vid - nou jwenn kòm yon rezilta entèmedyè:
125 ru.
Iterasyon 3
Fonksyon an rele tèt li ankò, k ap resevwa kòm premye agiman sa ki rete nan tèks la yo dwe otorize nan iterasyon anvan an (Bsh zkz n 125 ru.), Epi kòm dezyèm agiman an, seri a nan karaktè eskli tronke pa yon lòt karaktè a. goch la, sa vle di "VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYUYA.," san inisyal "B". Lè sa a, li ankò pran premye karaktè ki soti nan bò gòch la (B) nan seri sa a epi retire li nan tèks la - nou jwenn:
sh zkz n 125 ru.
Ak sou sa - Mwen espere ou jwenn lide a. Avèk chak iterasyon, lis karaktè yo dwe retire yo pral tronke sou bò gòch la, epi nou pral chèche epi ranplase pwochen karaktè nan seri a ak yon anile.
Lè tout karaktè yo fini, nou pral bezwen sòti bouk la - wòl sa a jis fèt pa fonksyon an IF (SI), nan ki konsepsyon nou an vlope. Si pa gen okenn karaktè kite pou efase (d=""), Lè sa a, fonksyon an pa ta dwe rele tèt li ankò, men li ta dwe tou senpleman retounen tèks la yo dwe efase (varyab t) nan fòm final li.
Iterasyon rekursif nan selil yo
Menm jan an tou, ou ka aplike yon enimerasyon rekursif nan selil nan yon seri bay yo. Sipoze nou vle kreye yon fonksyon lambda ki rele LIS RANplasman pou ranplasman an gwo fragman nan tèks sous la dapre yon lis referans bay yo. Rezilta a ta dwe sanble tankou sa a:
Moun sa yo. nan fonksyon nou an LIS RANplasman pral gen twa agiman:
- selil ak tèks pou trete (adrès sous)
- premye selil la nan yon kolòn ak valè pou fè rechèch nan rechèch la
- premye selil kolòn nan ak valè ranplasman nan rechèch la
Fonksyon an ta dwe ale soti anwo jouk anba nan anyè a epi ranplase sekans tout opsyon ki soti nan kolòn gòch la Jwenn nan opsyon ki koresponn yo nan kolòn dwat la Ranplase. Ou ka aplike sa a ak fonksyon lambda rekursif sa a:
Chanjman desann nan chak iterasyon aplike pa yon fonksyon Excel estanda JETE (OFFSET), ki nan ka sa a gen twa agiman - ranje orijinal la, chanjman ranje (1) ak chanjman kolòn (0).
Oke, le pli vit ke nou rive nan fen anyè a (n = ""), nou dwe mete fen nan rekursion a - nou sispann rele tèt nou epi montre sa ki akimile apre tout ranplasman yo nan varyab tèks sous t la.
Se tout. Pa gen makro difisil oswa demann Power Query - tout travay la rezoud pa yon sèl fonksyon.
- Kouman pou itilize nouvo fonksyon etalaj dinamik Excel yo: FILTER, SORT, UNIC
- Ranplase ak netwaye tèks ak fonksyon SUBSTITUTE
- Kreye makro ak fonksyon itilizatè-defini (UDF) nan VBA