cuntenutu
À u mumentu, Microsoft Excel hà quasi cinque centu funzioni di foglia di travagliu dispunibuli attraversu a finestra Function Wizard - buttone fx in a barra di formula. Questu hè un settore assai decentu, ma, in ogni modu, quasi ogni utilizatore prima o dopu scontra una situazione induve sta lista ùn cuntene micca a funzione chì hà bisognu - solu perchè ùn hè micca in Excel.
Finu à avà, l'unicu modu per risolve stu prublema era macros, vale à dì scrive a vostra propria funzione definita da l'utilizatore (UDF = User Defined Function) in Visual Basic, chì richiede cumpetenze di prugrammazione appropritate è, à volte, ùn hè micca faciule. Tuttavia, cù l'ultime aghjurnamenti di Office 365, a situazione hè cambiata per u megliu - una funzione speciale "wrapper" hè stata aghjunta à Excel. LAMBDA. Cù u so aiutu, u compitu di creà e vostre funzioni hè ora risolta facilmente è bella.
Fighjemu u principiu di u so usu in u seguente esempiu.
Comu sapete, Excel hà parechje funzioni di analisi di data chì permettenu di determinà u numeru di u ghjornu, u mese, a settimana è l'annu per una data data. Ma per qualchì mutivu ùn ci hè micca una funzione chì determina u numeru di u quartu, chì hè ancu spessu necessariu, nò? Fixemu sta mancanza è creanu cun LAMBDA propria nova funzione per risolve stu prublema.
Passu 1. Scrivite a formula
Cuminciamu cù u fattu chì manualmente in u modu di solitu scriveremu una formula in una cellula di fogliu chì calcula ciò chì avemu bisognu. In u casu di u quartu numeru, questu pò esse fattu, per esempiu, cusì:
Step 2. Wrapping up in LAMBDA è teste
Avà hè u tempu di applicà a nova funzione LAMBDA è imbulighjate a nostra formula. A sintassi di a funzione hè a siguenti:
=LAMBDA(Variabile 1; Variabile 2; … Variabile N ; Expression)
induve i nomi di una o più variàbili sò listati prima, è l'ultimu argumentu hè sempre una formula o una espressione calculata chì li usa. I nomi di variàbili ùn devenu micca cum'è l'indirizzi di e cellule è ùn deve micca cuntene punti.
In u nostru casu, ci serà solu una variabile - a data per quale avemu calculatu u numeru di quartu. Chjamemu a variabile per questu, dì, d. Allora impacchendu a nostra formula in una funzione LAMBDA è sustituì l'indirizzu di a cellula originale A2 cù un nome di variabile fittizia, avemu:
Per piacè nutate chì dopu una tale trasfurmazioni, a nostra formula (in fatti, curretta!) Cuminciò à pruduce un errore, perchè avà a data originale da a cellula A2 ùn hè micca trasferita à questu. Per pruvà è cunfidenza, pudete passà argumenti à questu aghjunghjendu dopu a funzione LAMBDA tra parentesi:
Step 3. Crea un nome
Avà per a parte faciule è divertente. Avemu apertu Manager di Nome Insignia fòrmula (Formule - Manager di nomi) è creanu un novu nome cù u buttone creà (Crea). Venite è inserite un nome per a nostra funzione futura (per esempiu, Nomkvartala), è in u campu Member (Riferimentu) cupià cù cura da a barra di formula è incollà a nostra funzione LAMBDA, solu senza l'ultimu argumentu (A2):
Tuttu. Dopu à cliccà nantu OK a funzione creata pò esse usata in ogni cellula in ogni foglia di stu libru di travagliu:
Aduprà in altri libri
LAMBDA è matrici dinamiche
Funzioni persunalizati create cù una funzione LAMBDA sustene u travagliu cù successu cù novi array dinamichi è e so funzioni (FILTER, UNIK, Pendentif) aghjuntu à Microsoft Excel in 2020.
Diciamu chì vulemu creà una nova funzione definita da l'utilizatori chì paragunà dui listi è rinvià a diffarenza trà elli - quelli elementi da a prima lista chì ùn sò micca in a seconda. U travagliu di a vita, ùn hè micca ? Prima, per questu anu utilizatu sia funzioni à la VPR (CERCA V), o PivotTables, o dumande Power Query. Avà pudete fà cù una formula:
In a versione inglese sarà:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Eccu a funzione COUNTIF conta u nùmeru d'occurrence di ogni elementu di a prima lista in u sicondu, è dopu a funzione FILTER selezziunà solu quelli di elli chì ùn avianu micca sti occurrence. Avvolgendu sta struttura in LAMBDA è creanu un intervallu chjamatu basatu annantu à questu cun un nome, per esempiu, RICERCA DISTRIBUZIONE - averemu una funzione còmuda chì torna u risultatu di paragunà dui listi in forma di una matrice dinamica:
Se i dati fonte ùn sò micca ordinali, ma tavule "intelligenti", a nostra funzione hà ancu affruntà senza prublemi:
Un altru esempiu hè dinamicamente splitting text by converting it to XML and then parsing it cell by cell using the FILTER.XML function we recently parsed. Per ùn ripruduce sta formula cumplessa manualmente ogni volta, serà più faciule d'imballà in LAMBDA è di creà un intervallu dinamicu basatu annantu à questu, vale à dì una nova funzione compacta è còmuda, chjamà, per esempiu, RAZDTEXT:
U primu argumentu di sta funzione serà a cellula cù u testu fonte, è u sicondu - u caratteru di separatore, è torna u risultatu in a forma di un array dinamica horizontale. U codice di a funzione serà cusì:
=LAMBDA(t;d; TRANSPOSE(FILTER.XML(“
A lista di l'esempii hè interminabile - in ogni situazione induve spessu deve entre in a stessa formula longa è ingombrante, a funzione LAMBDA farà a vita notevolmente più faciule.
Enumerazione recursiva di caratteri
Tutti l'esempii precedenti anu dimustratu solu unu, u più ovvi, latu di a funzione LAMBDA - u so usu cum'è "wrapper" per imballà formule longu in questu è simplificà a so input. In fattu, LAMBDA hà un altru latu, assai più profondu, chì u trasforma in quasi una lingua di prugrammazione cumpleta.
U fattu hè chì una funzione fundamentale impurtante di e funzioni LAMBDA hè a capacità di implementà ricursione - logica di calculi, quandu in u prucessu di calculu a funzione si chjama. Da l'abitudine, pò sonu creepy, ma in a prugrammazione, a ricursione hè una cosa cumuna. Ancu in macros in Visual Basic, pudete implementà, è avà, cum'è pudete vede, hè ghjuntu à Excel. Pruvate à capisce sta tecnica cù un esempiu praticu.
Supponemu chì vulemu creà una funzione definita da l'utilizatori chì sguassate tutti i caratteri dati da u testu fonte. L'utilità di una tale funzione, pensu, ùn avete micca bisognu di pruvà - seria assai còmuda per sguassà i dati di input litterati cù u so aiutu, nò?
Tuttavia, paragunatu à l'esempi precedenti, micca ricursivi, ci aspettanu duie difficultà.
- Avemu da vene cun un nome per a nostra funzione prima di cumincià à scrive u so codice, perchè in questu, stu nome serà digià usatu per chjamà a funzione stessa.
- Ingressu una tale funzione recursiva in una cellula è debugging in specificazione di l'argumenti in parentesi dopu à LAMBDA (cum'è avemu fattu prima) ùn funziona micca. Avete da creà una funzione immediatamente "da zero" in Manager di Nome (Nome Manager).
Chjamemu a nostra funzione, per dì, CLEAN è vuleriamu avè dui argumenti - u testu da esse pulitu è a lista di caratteri esclusi cum'è una stringa di testu:
Creemu, cum'è avemu fattu prima, nantu à a tabulazione fòrmula в Manager di nome gamma chjamata, chjamate CLEAR è entre in u campu Range seguenti custruzzioni:
=LAMBDA(t;d;IF(d=””;t;CLEAR(SUBSTITUTE(t;LEFT(d);””);MID(d;2;255))))
Quì a variabile t hè u testu uriginale per esse sguassatu, è d hè a lista di caratteri per esse eliminati.
Tuttu travaglia cusì:
Iterazione 1
U frammentu SUBSTITUTE(t;LEFT(d);""), cum'è pudete guessà, rimpiazza u primu caratteru da u caratteru manca da u settore d per esse sguassatu in u testu fonte t cù una stringa di testu viota, vale à dì sguassate u " A". Comu u risultatu intermedi, avemu:
Vsh zkz n 125 rubles.
Iterazione 2
Allora a funzione si chjama è cum'è input (u primu argumentu) riceve ciò chì resta dopu a pulizia in u passu precedente, è u sicondu argumentu hè a stringa di caratteri esclusi chì partenu micca da u primu, ma da u sicondu caratteru, vale à dì "BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYA. ," senza l'iniziale "A" - questu hè fattu da a funzione MID. Cum'è prima, a funzione piglia u primu caratteru da a manca di i restanti (B) è u rimpiazza in u testu datu à ellu (Zkz n 125 rubles) cù una stringa viota - avemu un risultatu intermediu:
125 ru.
Iterazione 3
A funzione si chjama di novu, ricevendu cum'è u primu argumentu ciò chì resta di u testu per esse sbulicatu à l'iterazione precedente (Bsh zkz n 125 ru.), È cum'è u sicondu argumentu, u settore di caratteri esclusi truncati da un caratteru più à a manca, vale à dì "VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYUYA.", senza l'iniziale "B". Allora piglia di novu u primu caratteru da a manca (B) da questu settore è l'elimina da u testu - avemu:
sh zkz n 125 ru.
È cusì - Spergu chì avete l'idea. Cù ogni iterazione, a lista di caratteri per esse sguassati serà truncata à a manca, è avemu da circà è rimpiazzà u prossimu caratteru da u settore cun un vacu.
Quandu tutti i caratteri scorri, avemu bisognu di esce da u ciclu - stu rolu hè solu realizatu da a funzione IF (SI), in quale u nostru disignu hè impannillatu. S'ellu ùn ci hè micca caratteri per sguassà (d=""), allura a funzione ùn deve più chjamà, ma deve solu vultà u testu per esse sbulicatu (variabile t) in a so forma finali.
Iterazione recursiva di e cellule
In listessu modu, pudete implementà una enumerazione recursiva di cellule in un intervallu datu. Suppone chì vulemu creà una funzione lambda chjamata LISTA DI SOSTITUZIONE per a rimpiazzamentu grossista di frammenti in u testu fonte secondu una lista di riferimentu data. U risultatu deve esse cusì:
Quelli. à a nostra funzione LISTA DI SOSTITUZIONE Ci sarà trè argumenti:
- cellula cù testu da processà (indirizzu fonte)
- a prima cellula di una colonna cù i valori per circà da a ricerca
- a prima cellula di a colonna cù i valori di rimpiazzamentu da a ricerca
A funzione deve andà da cima à fondu in u cartulare è rimpiazzà in sequenza tutte l'opzioni da a colonna di manca Da truvà à l'opzioni currispundenti da a colonna dritta Sustituitu. Pudete implementà questu cù a seguente funzione lambda recursiva:
Shift down in ogni iterazione hè implementatu da una funzione excel standard DISPOSIZIONE (OFFSET), chì in questu casu hà trè argumenti - a gamma originale, u cambiamentu di fila (1) è u cambiamentu di a colonna (0).
Ebbè, quandu avemu ghjuntu à a fine di u repertoriu (n = ""), duvemu finisce a ricursione - cessemu di chjamà noi stessi è vede ciò chì hà accumulatu dopu à tutti i rimpiazzamenti in a variabile di u testu fonte t.
Eccu tuttu. Nisuna macros complicata o dumande Power Query - tuttu u compitu hè risoltu da una funzione.
- Cumu utilizà e novi funzioni di matrice dinamica di Excel: FILTER, SORT, UNIC
- Sustituisce è pulisce u testu cù a funzione SOSTITUTI
- Creazione di macros è funzioni definite da l'utilizatori (UDF) in VBA