LAMBDA è la nuova super funzione di Excel

Al momento, Microsoft Excel dispone di quasi cinquecento funzioni del foglio di lavoro disponibili tramite il pulsante della finestra Funzione guidata fx nella barra della formula. Questo è un set molto decente, ma, tuttavia, quasi tutti gli utenti prima o poi incontrano una situazione in cui questo elenco non contiene la funzione di cui ha bisogno, semplicemente perché non è in Excel.

Finora l'unico modo per risolvere questo problema erano le macro, ovvero la scrittura di una propria funzione definita dall'utente (UDF = User Defined Function) in Visual Basic, che richiede adeguate capacità di programmazione e, a volte, non è affatto semplice. Tuttavia, con gli ultimi aggiornamenti di Office 365, la situazione è cambiata in meglio: in Excel è stata aggiunta una speciale funzione "wrapper" LAMBDA. Con il suo aiuto, il compito di creare le proprie funzioni è ora risolto facilmente e magnificamente.

Diamo un'occhiata al principio del suo utilizzo nell'esempio seguente.

Come probabilmente saprai, Excel ha diverse funzioni di analisi della data che ti consentono di determinare il numero del giorno, del mese, della settimana e dell'anno per una determinata data. Ma per qualche ragione non esiste una funzione che determini il numero del trimestre, anch'esso spesso necessario, giusto? Risolviamo questa mancanza e creiamo con LAMBDA possedere una nuova funzione per risolvere questo problema.

Passaggio 1. Scrivi la formula

Iniziamo con il fatto che manualmente nel solito modo scriveremo una formula in una cella del foglio che calcola ciò di cui abbiamo bisogno. Nel caso del numero del quarto, questo può essere fatto, ad esempio, in questo modo:

Passaggio 2. Conclusione in LAMBDA e test

Ora è il momento di applicare la nuova funzione LAMBDA e racchiudervi la nostra formula. La sintassi della funzione è la seguente:

=LAMBDA(Variabile1; Variabile2; ... Variabile N ; Espressione)

dove vengono elencati per primi i nomi di una o più variabili e l'ultimo argomento è sempre una formula o un'espressione calcolata che li utilizza. I nomi delle variabili non devono assomigliare a indirizzi di celle e non devono contenere punti.

Nel nostro caso, ci sarà solo una variabile: la data per la quale calcoliamo il numero del trimestre. Chiamiamo la variabile per esso, diciamo, d. Quindi avvolgere la nostra formula in una funzione LAMBDA e sostituendo l'indirizzo della cella originale A2 con un nome di variabile fittizio, otteniamo:

Si noti che dopo tale trasformazione, la nostra formula (in effetti, corretta!) Ha iniziato a produrre un errore, perché ora la data originale dalla cella A2 non viene trasferita su di essa. Per test e sicurezza, puoi passargli argomenti aggiungendoli dopo la funzione LAMBDA tra parentesi:

Passaggio 3. Crea un nome

Ora per la parte facile e divertente. Noi apriamo Nome Manager linguetta formula (Formule — Gestore nomi) e creare un nuovo nome con il pulsante Creare (Creare). Trova e inserisci un nome per la nostra funzione futura (ad esempio, Nomkvartala), e sul campo Link (Riferimento) copia attentamente dalla barra della formula e incolla la nostra funzione LAMBDA, solo senza l'ultimo argomento (A2):

Tutto quanto. Dopo aver cliccato su OK la funzione creata può essere utilizzata in qualsiasi cella su qualsiasi foglio di questa cartella di lavoro:

Utilizzare in altri libri

LAMBDA e array dinamici

Funzioni personalizzate create con una funzione LAMBDA supportare con successo il lavoro con nuovi array dinamici e le loro funzioni (FILTRO, UNICO, GRADE) aggiunto a Microsoft Excel nel 2020.

Diciamo di voler creare una nuova funzione definita dall'utente che confronti due elenchi e restituisca la differenza tra di loro: quegli elementi del primo elenco che non sono nel secondo. La vita è il lavoro, vero? In precedenza, per questo utilizzavano entrambe le funzioni a la VPR (CERCA.VERT)o tabelle pivot o query Power Query. Ora puoi farlo con una formula:

Nella versione inglese sarà:

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

Ecco la funzione COUNTIF conta il numero di occorrenze di ogni elemento del primo elenco nel secondo, quindi la funzione FILTRO seleziona solo quelli di loro che non hanno avuto queste occorrenze. Avvolgendo questa struttura in LAMBDA e creando un intervallo denominato basato su di esso con un nome, ad esempio, CERCA DISTRIBUZIONE – otterremo una comoda funzione che restituisce il risultato del confronto di due liste sotto forma di array dinamico:

Se i dati di origine non sono tabelle ordinarie, ma "intelligenti", anche la nostra funzione affronterà senza problemi:

Un altro esempio è la divisione dinamica del testo convertendolo in XML e quindi analizzandolo cella per cella utilizzando la funzione FILTER.XML che abbiamo analizzato di recente. Per non riprodurre manualmente ogni volta questa complessa formula, sarà più semplice avvolgerla in LAMBDA e creare su di essa una gamma dinamica, ovvero una nuova funzione compatta e comoda, nominandola, ad esempio, RAZDTEXT:

Il primo argomento di questa funzione sarà la cella con il testo di origine e il secondo il carattere separatore e restituirà il risultato sotto forma di una matrice dinamica orizzontale. Il codice funzione sarà il seguente:

=LAMBDA(t;d; TRANSPOSE(FILTRO.XML(“"&SOSTITUTO(t;d? "«)&»“;”//Y”)))

L'elenco di esempi è infinito: in ogni situazione in cui devi inserire spesso la stessa formula lunga e ingombrante, la funzione LAMBDA ti semplificherà notevolmente la vita.

Enumerazione ricorsiva dei caratteri

Tutti gli esempi precedenti hanno mostrato solo un lato, il più ovvio, della funzione LAMBDA: il suo utilizzo come "wrapper" per racchiudere formule lunghe al suo interno e semplificarne l'immissione. In effetti, LAMBDA ha un altro lato, molto più profondo, che lo trasforma in un linguaggio di programmazione quasi a tutti gli effetti.

Il fatto è che una caratteristica fondamentale delle funzioni LAMBDA è la capacità di implementarle ricorsione – logica dei calcoli, quando nel processo di calcolo la funzione richiama se stessa. Dall'abitudine, può sembrare inquietante, ma nella programmazione la ricorsione è una cosa comune. Anche nelle macro in Visual Basic, puoi implementarlo e ora, come puoi vedere, è arrivato in Excel. Proviamo a capire questa tecnica con un esempio pratico.

Supponiamo di voler creare una funzione definita dall'utente che rimuove tutti i caratteri dati dal testo di origine. L'utilità di una tale funzione, penso, non è necessario dimostrarla: sarebbe molto conveniente cancellare i dati di input disseminati con il suo aiuto, giusto?

Tuttavia, rispetto ai precedenti esempi non ricorsivi, ci attendono due difficoltà.

1. Dovremo trovare un nome per la nostra funzione prima di iniziare a scriverne il codice, perché in esso questo nome sarà già utilizzato per chiamare la funzione stessa.
   
2. Immettere una tale funzione ricorsiva in una cella ed eseguirne il debug specificando gli argomenti tra parentesi dopo LAMBDA (come abbiamo fatto in precedenza) non funzionerà. Dovrai creare immediatamente una funzione "da zero". Nome Manager (Nome Gestore).
   

Chiamiamo la nostra funzione, diciamo, CLEAN e vorremmo che avesse due argomenti: il testo da pulire e l'elenco dei caratteri esclusi come stringa di testo:

Creiamo, come abbiamo fatto prima, nella scheda formula в Nome manager intervallo denominato, denominarlo CLEAR e immettere nel campo Escursione seguente costruzione:

=LAMBDA(t;d;IF(d=””;t;CLEAR(SOSTITUTIVO(t;SINISTRA(d);””);MID(d;2;255))))

Qui la variabile t è il testo originale da cancellare e d è l'elenco di caratteri da cancellare.

Funziona tutto così:

Iterazione 1

Il frammento SUBSTITUTE(t;LEFT(d);””), come si può intuire, sostituisce il primo carattere del carattere sinistro dell'insieme d da cancellare nel testo sorgente t con una stringa di testo vuota, ovvero rimuove il “ UN". Come risultato intermedio, otteniamo:

Vsh zkz n 125 rubli.

Iterazione 2

Quindi la funzione chiama se stessa e come input (il primo argomento) riceve ciò che è rimasto dopo la pulizia nel passaggio precedente, e il secondo argomento è la stringa di caratteri esclusi a partire non dal primo, ma dal secondo carattere, ovvero “BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYA. ,” senza l'iniziale “A” – questo viene fatto dalla funzione MID. Come prima, la funzione prende il primo carattere da sinistra dei restanti (B) e lo sostituisce nel testo assegnatole (Zkz n 125 rubli) con una stringa vuota – otteniamo come risultato intermedio:

125 ru.

Iterazione 3

La funzione richiama se stessa, ricevendo come primo argomento ciò che resta del testo da cancellare all'iterazione precedente (Bsh zkz n 125 ru.), E come secondo argomento, l'insieme di caratteri esclusi troncati di un altro carattere per a sinistra, ovvero “VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYUYA.,” senza l'iniziale “B”. Quindi prende di nuovo il primo carattere da sinistra (B) da questo set e lo rimuove dal testo – otteniamo:

sh zkz n 125 ru.

E così via, spero che tu abbia l'idea. Ad ogni iterazione, l'elenco dei caratteri da rimuovere verrà troncato a sinistra e cercheremo e sostituiremo il carattere successivo del set con un vuoto.

Quando tutti i personaggi si esauriscono, dovremo uscire dal ciclo: questo ruolo viene semplicemente eseguito dalla funzione IF (SE), in cui è avvolto il nostro design. Se non ci sono più caratteri da cancellare (d=””), la funzione non dovrebbe più chiamare se stessa, ma dovrebbe semplicemente restituire il testo da cancellare (variabile t) nella sua forma finale.

Iterazione ricorsiva di celle

Allo stesso modo, puoi implementare un'enumerazione ricorsiva di celle in un determinato intervallo. Supponiamo di voler creare una funzione lambda denominata ELENCO DI SOSTITUZIONE per la sostituzione all'ingrosso di frammenti nel testo di partenza secondo un determinato elenco di riferimento. Il risultato dovrebbe assomigliare a questo:

Quelli. alla nostra funzione ELENCO DI SOSTITUZIONE ci saranno tre argomenti:

1. cella con testo da elaborare (indirizzo di origine)
2. la prima cella di una colonna con i valori da cercare dalla ricerca
3. la prima cella della colonna con i valori di sostituzione dalla ricerca

La funzione dovrebbe andare dall'alto verso il basso nella directory e sostituire in sequenza tutte le opzioni dalla colonna di sinistra Trovare alle opzioni corrispondenti dalla colonna di destra Sostituire. Puoi implementarlo con la seguente funzione lambda ricorsiva:

Lo spostamento verso il basso ad ogni iterazione è implementato da una funzione excel standard SMALTIMENTO (COMPENSARE), che in questo caso ha tre argomenti: l'intervallo originale, lo spostamento di riga (1) e lo spostamento di colonna (0).

Ebbene, non appena raggiungiamo la fine della directory (n = “”), dobbiamo terminare la ricorsione – smettiamo di chiamarci e visualizziamo ciò che è accumulato dopo tutte le sostituzioni nella variabile di testo sorgente t.

È tutto. Nessuna macro complicata o query Power Query: l'intera attività viene risolta da una funzione.

• Come utilizzare le nuove funzioni di matrice dinamica di Excel: FILTRO, ORDINA, UNIC
• Sostituzione e pulizia del testo con la funzione SOSTITUZIONE
  
• Creazione di macro e funzioni definite dall'utente (UDF) in VBA