Cuprins
În acest moment, Microsoft Excel are aproape cinci sute de funcții ale foii de lucru disponibile prin intermediul fereastra Function Wizard – butonul fx în bara de formule. Acesta este un set foarte decent, dar, cu toate acestea, aproape fiecare utilizator se confruntă mai devreme sau mai târziu cu o situație în care această listă nu conține funcția de care are nevoie - pur și simplu pentru că nu este în Excel.
Până acum, singura modalitate de a rezolva această problemă era macro-urile, adică scrierea propriei funcții definite de utilizator (UDF = User Defined Function) în Visual Basic, care necesită abilități de programare adecvate și, uneori, nu este deloc ușor. Cu toate acestea, odată cu cele mai recente actualizări Office 365, situația s-a schimbat în bine - o funcție specială de „învelire” a fost adăugată în Excel LAMBDA. Cu ajutorul acestuia, sarcina de a vă crea propriile funcții este acum rezolvată ușor și frumos.
Să ne uităm la principiul utilizării sale în exemplul următor.
După cum știți cel mai probabil, Excel are mai multe funcții de analiză a datei care vă permit să determinați numărul zilei, lunii, săptămânii și anului pentru o dată dată. Dar dintr-un motiv oarecare nu există nicio funcție care să determine numărul trimestrului, care este adesea necesar, nu? Haideți să remediam acest neajuns și să creăm cu LAMBDA propria nouă funcție pentru a rezolva această problemă.
Pasul 1. Scrieți formula
Să începem cu faptul că manual, în mod obișnuit, vom scrie o formulă într-o celulă de foaie care calculează ceea ce avem nevoie. În cazul numărului de sfert, acest lucru se poate face, de exemplu, astfel:
Pasul 2. Încheierea în LAMBDA și testarea
Acum este timpul să aplicăm noua funcție LAMBDA și să împachetăm formula noastră în ea. Sintaxa funcției este următoarea:
=LAMBDA(Variabila 1; Variabila 2; ... Variabila N ; Expresie)
unde numele uneia sau mai multor variabile sunt enumerate mai întâi, iar ultimul argument este întotdeauna o formulă sau o expresie calculată care le utilizează. Numele variabilelor nu trebuie să arate ca adrese de celule și nu trebuie să conțină puncte.
În cazul nostru, va exista o singură variabilă – data pentru care calculăm numărul trimestrului. Să numim variabila pentru aceasta, să zicem, d. Apoi împachetăm formula noastră într-o funcție LAMBDA și înlocuind adresa celulei originale A2 cu un nume de variabilă fictiv, obținem:
Vă rugăm să rețineți că, după o astfel de transformare, formula noastră (de fapt, corectă!) a început să producă o eroare, deoarece acum data originală din celula A2 nu este transferată în ea. Pentru testare și încredere, îi puteți transmite argumente adăugându-le după funcție LAMBDA în paranteze:
Pasul 3. Creați un nume
Acum, pentru partea ușoară și distractivă. Deschidem Manager nume fila formulă (Formule — Manager de nume) și creați un nume nou cu butonul Crea (Crea). Vino și introduceți un nume pentru funcția noastră viitoare (de exemplu, Nomkvartala), și în teren Link (Referinţă) copiați cu atenție din bara de formule și lipiți funcția noastră LAMBDA, numai fără ultimul argument (A2):
Tot. După ce faceți clic pe OK funcția creată poate fi utilizată în orice celulă din orice foaie a acestui registru de lucru:
Utilizați în alte cărți
LAMBDA și matrice dinamice
Funcții personalizate create cu o funcție LAMBDA sprijină cu succes lucrul cu noile matrice dinamice și funcțiile acestora (FILTRU, UNIC, GRADE) adăugat la Microsoft Excel în 2020.
Să presupunem că vrem să creăm o nouă funcție definită de utilizator care să compare două liste și să returneze diferența dintre ele – acele elemente din prima listă care nu sunt în a doua. Munca vieții, nu-i așa? Anterior, pentru aceasta au folosit fie funcții a la VPR (CĂUTARE V), sau PivotTables sau interogări Power Query. Acum poți face cu o singură formulă:
În versiunea în limba engleză va fi:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Aici funcția COUNTIF numără numărul de apariții ale fiecărui element din prima listă din a doua și apoi funcția FILTRU selectează numai pe cei dintre ei care nu au avut aceste apariții. Învelind această structură în LAMBDA și crearea unui interval denumit pe baza acestuia cu un nume, de exemplu, DISTRIBUȚIE CĂUTARE – vom obține o funcție convenabilă care returnează rezultatul comparării a două liste sub forma unui tablou dinamic:
Dacă datele sursă nu sunt tabele obișnuite, ci „inteligente”, funcția noastră se va descurca, de asemenea, fără probleme:
Un alt exemplu este împărțirea dinamică a textului prin conversia acestuia în XML și apoi analizarea lui celulă cu celulă folosind funcția FILTER.XML pe care am analizat-o recent. Pentru a nu reproduce manual această formulă complexă de fiecare dată, va fi mai ușor să o înfășurați în LAMBDA și să creați o gamă dinamică bazată pe aceasta, adică o nouă funcție compactă și convenabilă, denumindu-i, de exemplu, RAZDTEXT:
Primul argument al acestei funcții va fi celula cu textul sursă, iar al doilea – caracterul separator și va returna rezultatul sub forma unui tablou dinamic orizontal. Codul funcției va fi după cum urmează:
=LAMBDA(t;d; TRANSPOSE(FILTER.XML(“
Lista de exemple este nesfârșită – în orice situație în care de multe ori trebuie să introduceți aceeași formulă lungă și greoaie, funcția LAMBDA vă va face viața considerabil mai ușoară.
Enumerarea recursiva a caracterelor
Toate exemplele anterioare au arătat doar una, cea mai evidentă, latură a funcției LAMBDA – utilizarea sa ca „înveliș” pentru a împacheta formule lungi în ea și a simplifica introducerea acestora. De fapt, LAMBDA are o altă latură, mult mai profundă, care îl transformă într-un limbaj de programare aproape cu drepturi depline.
Faptul este că o caracteristică fundamentală importantă a funcțiilor LAMBDA este capacitatea de a le implementa în recursivitate – logica calculelor, când în procesul de calcul funcția se autoapelează. Din obicei, poate suna înfiorător, dar în programare recursiunea este un lucru obișnuit. Chiar și în macrocomenzi în Visual Basic, îl puteți implementa, iar acum, după cum puteți vedea, a ajuns în Excel. Să încercăm să înțelegem această tehnică cu un exemplu practic.
Să presupunem că vrem să creăm o funcție definită de utilizator care ar elimina toate caracterele date din textul sursă. Utilitatea unei astfel de funcții, cred, nu trebuie să dovediți - ar fi foarte convenabil să ștergeți datele de intrare așezate cu ajutorul ei, nu?
Cu toate acestea, în comparație cu exemplele anterioare, nerecursive, ne așteaptă două dificultăți.
- Va trebui să venim cu un nume pentru funcția noastră înainte de a începe să scriem codul acesteia, deoarece în ea, acest nume va fi deja folosit pentru a apela funcția în sine.
- Introducerea unei astfel de funcție recursivă într-o celulă și depanarea acesteia prin specificarea argumentelor între paranteze după LAMBDA (cum am făcut mai devreme) nu va funcționa. Va trebui să creați imediat o funcție „de la zero”. Manager nume (Manager de nume).
Să numim funcția noastră, să zicem, CLEAN și ne-am dori să aibă două argumente – textul de curățat și lista de caractere excluse ca șir de text:
Să creăm, așa cum am făcut mai devreme, pe filă formulă в Manager nume interval numit, denumește-l CLEAR și introduceți în câmp Gamă urmatoarea constructie:
=LAMBDA(t;d;IF(d=””;t;CLEAR(SUBSTITUTE(t;STÂNGA(d);””);MID(d;2;255))))
Aici variabila t este textul original care trebuie șters și d este lista de caractere care trebuie șters.
Totul funcționează astfel:
iterație 1
Fragmentul SUBSTITUTE(t;LEFT(d);””), după cum ați putea ghici, înlocuiește primul caracter din caracterul din stânga din setul d pentru a fi șters din textul sursă t cu un șir de text gol, adică elimină „ A". Ca rezultat intermediar, obținem:
Vsh zkz n 125 de ruble.
iterație 2
Apoi funcția se autoapelează și ca intrare (primul argument) primește ceea ce a rămas după curățare în pasul anterior, iar al doilea argument este șirul de caractere excluse care începe nu de la primul, ci de la al doilea caracter, adică „BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYA. ,” fără inițiala „A” – acest lucru este realizat de funcția MID. Ca și înainte, funcția ia primul caracter din stânga celor rămase (B) și îl înlocuiește în textul care i-a fost dat (Zkz n 125 ruble) cu un șir gol – obținem ca rezultat intermediar:
125 ru.
iterație 3
Funcția se autoapelează din nou, primind ca prim argument ceea ce a mai rămas din text pentru a fi șters la iterația anterioară (Bsh zkz n 125 ru.), Iar ca al doilea argument, setul de caractere excluse trunchiat cu încă un caracter la stânga, adică „VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYUYA.”, fără inițiala „B”. Apoi ia din nou primul caracter din stânga (B) din acest set și îl elimină din text - obținem:
sh zkz n 125 ru.
Și așa mai departe – sper că înțelegeți ideea. Cu fiecare iterație, lista de caractere de eliminat va fi trunchiată în stânga și vom căuta și înlocui următorul caracter din set cu un gol.
Când toate personajele se epuizează, va trebui să ieșim din buclă - acest rol este doar îndeplinit de funcție IF (DACĂ), în care este înfășurat designul nostru. Dacă nu mai sunt caractere de șters (d=””), atunci funcția nu ar trebui să se mai invoce, ci ar trebui pur și simplu să returneze textul de șters (variabila t) în forma sa finală.
Iterația recursive a celulelor
În mod similar, puteți implementa o enumerare recursivă a celulelor dintr-un interval dat. Să presupunem că vrem să creăm o funcție lambda numită LISTA DE ÎNLOCUIRE pentru înlocuirea angro a fragmentelor din textul sursă conform unei liste de referințe date. Rezultatul ar trebui să arate astfel:
Acestea. la funcția noastră LISTA DE ÎNLOCUIRE vor exista trei argumente:
- celulă cu text de procesat (adresa sursă)
- prima celulă a unei coloane cu valori de căutat din căutare
- prima celulă a coloanei cu valori de înlocuire din căutare
Funcția ar trebui să meargă de sus în jos în director și să înlocuiască secvențial toate opțiunile din coloana din stânga A găsi la opțiunile corespunzătoare din coloana din dreapta Substitui. Puteți implementa acest lucru cu următoarea funcție lambda recursivă:
Schimbarea în jos la fiecare iterație este implementată de o funcție excel standard ELIMINAREA (DECALAJ), care în acest caz are trei argumente – intervalul inițial, deplasarea rândului (1) și deplasarea coloanei (0).
Ei bine, de îndată ce ajungem la sfârșitul directorului (n = „”), trebuie să punem capăt recursiunii – încetăm să ne mai apelăm și afișăm ceea ce s-a acumulat după toate înlocuirile în variabila text sursă t.
Asta e tot. Fără macrocomenzi dificile sau interogări Power Query - întreaga sarcină este rezolvată printr-o singură funcție.
- Cum să utilizați noile funcții dinamice ale matricei Excel: FILTER, SORT, UNIC
- Înlocuirea și curățarea textului cu funcția SUBSTITUTE
- Crearea de macrocomenzi și funcții definite de utilizator (UDF) în VBA