Come creare una funzione LAMBDA con Excel

La creazione delle funzioni LAMBDA su Excel è un argomento attuale e complesso, dato che per la prima volta possiamo creare le nostre funzioni personalizzate da zero su Excel. Un tempo si poteva fare solo con le cosiddette UDF (User Defined Functions) utilizzando VBA, ma il grado di complessità e capacità richiesto era notevole.

Il concetto di LAMBDA, su Excel, è relativo a "creare una funzione personalizzata che posso riutilizzare sui fogli / file che desidero, facilmente modificabile e utilizzabile anche da terzi". Per chi lavora tanto su Excel è un'opportunità incredibile per velocizzarne l'utilizzo, dato che spesso ci capiterà di ripetere operazioni numerose volte che potrebbero essere fatte in un colpo solo.

In questa guida creeremo una funzione LAMBDA, mostrando i vari passaggi logici per comprendere meglio il concetto e riportarlo sui nostri singoli file. La funzione, una volta creata, può essere utilizzata su tutti i fogli del file e può essere facilmente esportata anche su altri file (basta copiare e incollare un foglio vuoto dal file con le LAMBDA a quello senza LAMBDA).

Utilizzeremo una tabella chiamata TBL_NBA che riassume le squadre vincitrici del titolo NBA, oltre ad altre informazioni quali gli avversari sconfitti in finale, la conference di appartenenza e il punteggio finale.

NB: L'intera guida si basa sulla versione Excel 365, dato che molte di queste operazioni sarebbero molto più complicate / quasi impossibili con versioni precedenti

L'obiettivo della creazione della funzione LAMBDA su Excel

In questo caso vogliamo creare una funzione LAMBDA che ci restituisca, in un colpo solo:

• L'elenco dei valori univoci di un intervallo
• Il conteggio dei valori univoci sullo stesso intervallo

per poi ordinarci questi valori in ordine decrescente, in base al conteggio. Quindi vogliamo vedere per primi i valori più presenti nel database, successivamente gli altri.

Nel nostro caso, la utilizzeremo nel database per contare i campioni NBA ma non farebbe alcuna differenza anche in altri database, basta che l'obiettivo (avere valori univoci da contare e poi ordinare) sia il medesimo.

Come arrivare al risultato senza funzioni LAMBDA con Excel

I passaggi mentali che ci servono per risolvere la domanda sono 3:

• Estrarre i valori univoci dal database
• Contare poi, per ognuno dei valori univoci, il numero di volte che appaiono sul database
• Ordinare il totale a seconda del conteggio

Possiamo quindi procedere con l'estrazione dei valori univoci:

=UNICI(TBL_NBA[CAMPIONI])

Utilizzando la funzione UNICI, che restituisce i valori univoci di un intervallo, troviamo tutti i nomi delle squadre a partire dalla cella G5. Possiamo poi contare quante volte il valore univoco si ritrovi nel database di partenza:

=CONTA.SE(TBL_NBA[CAMPIONI];G5#)

Abbiamo quindi ora sia i valori univoci sia il loro conteggio. Ci basterà ordinarle per ottenere il risultato che desideriamo:

=DATI.ORDINA(G5#:H5#;2;-1)

DATI.ORDINA in questo caso prende sia i valori univoci (G5#) sia il loro conteggio (H5#) per poi ordinare per la seconda colonna (motivo per la presenza del 2 nella formula) in ordine decrescente (il -1 nella formula).

Il risultato è corretto, ma per farlo abbiamo dovuto fare vari passaggi. Con l'utilizzo di una LAMBDA, potremmo farlo in automatico creando la nostra funzione che chiameremo MATR.CONTA.UNICI.SGL: questa funzione prende i valori univoci, li conta e poi li mette in ordine a seconda del conteggio, restituendo sia la colonna con i valori univoci sia il loro punteggio.

Come utilizzare la funzione STACK.ORIZ per ridurre i passaggi su Excel

Le funzioni STACK.ORIZ e STACK.VERT hanno rivoluzionato come gestire gli array su Excel, dato che ci permettono di impilare i valori o in orizzontale o in verticale in un colpo solo. Nel nostro caso vogliamo evitare di dover prima prendere i valori univoci per poi contarli, possiamo quindi ridurre i passaggi così:

=STACK.ORIZ(

UNICI(TBL_NBA[CAMPIONI]);

CONTA.SE(TBL_NBA[CAMPIONI];UNICI(TBL_NBA[CAMPIONI])))

La funzione prende prima i valori univoci dei campioni NBA per poi aggiungere, sulla destra, il conteggio per ognuna delle squadre.

A questo punto non ci resta che ordinarle nuovamente, riprendendo la funzione vista sopra:

=DATI.ORDINA(

STACK.ORIZ(

UNICI(TBL_NBA[CAMPIONI]);

CONTA.SE(TBL_NBA[CAMPIONI];UNICI(TBL_NBA[CAMPIONI])));2;-1)

In questo caso ordiniamo sempre per il secondo valore dell'array, quindi per il conteggio, ottenendo così il risultato finale corretto.

Come utilizzare la funzione LET per semplificare i calcoli su Excel

La funzione LET ci permette di ridurre i calcoli "ridondanti" sulle formule e in questo caso ci aiuta molto, dato che come notiamo sia l'intervallo considerato sia i valori univoci si ripetono in vari punti della formula.

Se ad esempio creassimo una funzione così:

=LET(intervallo;B3;intervallo+1)

Non faremmo altro che sommare 1 al valore nella cella B3.

Possiamo quindi riscrivere la formula dichiarando inizialmente l'intervallo che ci interessa, per evitare ripetizioni:

=LET(intervallo;TBL_NBA[CAMPIONI];

DATI.ORDINA(

STACK.ORIZ(

UNICI(intervallo);

CONTA.SE(intervallo;UNICI(intervallo)));2;-1))

La funzione LET ci permette di chiamare "intervallo" la nostra colonna di riferimento. Questo ci semplifica notevolmente la scrittura della formula successiva, dato che ora vedremo unicamente "intervallo" anzichè "TBL_NBA[CAMPIONI]" avendola dichiarata in apertura di formula.

Possiamo anche fare un passaggio successivo, semplificando ulteriormente la formula con un secondo parametro che racchiuda i valori univoci:

=LET(intervallo;TBL_NBA[CAMPIONI];

unici_int;UNICI(intervallo);

DATI.ORDINA(

STACK.ORIZ(

unici_int;

CONTA.SE(intervallo;unici_int));2;-1))

Come notiamo, per avere i valori univoci dichiariamo che unici_int è uguale a UNICI(intervallo), che abbiamo appena dichiarato come intervallo uguale a TBL_NBA[CAMPIONI]. Possiamo quindi richiamare le nostre definizioni subito dopo averle create, semplificando il procedimento.

A questo punto, abbiamo adattato il più possibile la formula per creare facilmente la nostra LAMBDA.

Come creare una funzione LAMBDA su Excel

Per creare una funzione LAMBDA, ci serve avere la formula scritta nella maniera più chiara possibile per Excel. Utilizzando LET, possiamo dichiarare solo una volta quello che ci serve e poi riutilizzare i singoli parametri nelle formule successive. L'obiettivo rimane sempre creare una funzione univoca che faccia qualcosa di molto specifico, come in questo caso (valori univoci conteggiati, poi ordinati per conteggio e inseriti in una matrice di due colonne).

Dobbiamo però decidere quali informazioni dovrà inserire l'utente per aiutarlo nell'utilizzo - in questo caso il parametro che abbiamo chiamato "intervallo", dal quale partono poi tutti i calcoli successivi. Per questo motivo è utile indicare come unici_int sia UNICI(intervallo), dato che in fase di inserimento ci permette di inserire unicamente un parametro ed Excel farà il resto.

Vediamo quindi come fare - riscriviamo la funzione precedente da LAMBDA, facendo un test inserendo alla fine il nostro intervallo "TBL_NBA[CAMPIONI]":

=LAMBDA(intervallo;

LET(unici_int;UNICI(intervallo);

DATI.ORDINA(

STACK.ORIZ(unici_int;

CONTA.SE(intervallo;unici_int));2;-1)))

(TBL_NBA[CAMPIONI])

La funzione LAMBDA in questo caso ci segnala che:

• Intervallo è il parametro che dovrà essere inserito da parte dell'utente
• La formula ordina l'array che comprende 2 colonne: i valori univoci (calcolati a partire dal parametro) e il loro conteggio relativo
• TBL_NBA[CAMPIONI] è, in questo caso specifico, l'intervallo che stiamo considerando per l'analisi

L'aspetto strano delle LAMBDA è che l'intervallo da considerare si mette alla fine della funzione, dopo aver chiuso tutte le parentesi. Questo perchè è un check della validità della funzione, più che la funzione LAMBDA definitiva.

Questa infatti sarà scritta così:

=LAMBDA(intervallo;

LET(unici_int;UNICI(intervallo);

DATI.ORDINA(

STACK.ORIZ(unici_int;

CONTA.SE(intervallo;unici_int));2;-1)))

dato che rimuoviamo il nostro intervallo di test "TBL_NBA[CAMPIONI]".

In sintesi - per utilizzare la nostra LAMBDA, basterà inserire il valore che desideriamo per "intervallo" e la funzione farà tutte le operazioni che dicevamo prima, cioè:

1. Prendere i valori univoci
2. Contare quante volte si trovino sull'intervallo definito
3. Metterli in ordine dal più presente al meno presente
4. Inserire due colonne con prima i valori univoci (1) e poi il conteggio (2) in maniera ordinata (3)

Come utilizzare la funzione LAMBDA su Excel

Abbiamo scritto la funzione LAMBDA ma non abbiamo ancora creato una nuova funzione. Per questo dovremo creare un nuovo nome su Excel, uguale alla funzione che abbiamo appena visto. Potremo decidere il nome della nuova funzione e inserire una breve spiegazione.

Per accedere alla creazione dei nuovi nomi dobbiamo cliccare sul menu Formule > Definisci nome:

Vediamolo nel dettaglio:

• Nome della nuova funzione: MATR.CONTA.UNICI.SGL
• Definizione: crea una matrice di due colonne (ordinate per il conteggio Z-A) comprendente i valori univoci di un intervallo e il loro conteggio relativo
• Riferito a: la formula precedente

A questo punto, abbiamo creato la nostra funzione LAMBDA!

Possiamo quindi ridurre tutto il calcolo precedente per trovare i campioni NBA così:

=MATR.CONTA.UNICI.SGL(TBL_NBA[CAMPIONI])

Abbiamo già definito nella creazione della LAMBDA tutti i nostri calcoli, quindi ci basta inserire l'intervallo e il gioco è fatto!

Il vantaggio di utilizzare funzioni LAMBDA su Excel

Utilizzare le funzioni LAMBDA su Excel semplifica incredibilmente il processo nel caso volessimo replicare gli stessi calcoli ma utilizzando degli intervalli / parametri diversi.

In questo caso abbiamo creato una funzione singola che fa diverse operazioni in un colpo solo, cui importa solo quale sia l'intervallo da utilizzare per tutti i calcoli.

Poniamo ad esempio il caso di voler calcolare:

• Gli avversari più frequenti dei vincitori durante le finals
• Quale sia la conference più vincente, confrontandola con l'altra (sono solo 2)
• Quali siano i punteggi più frequenti delle finals

A questo punto, avendo creato la nostra funzione MATR.CONTA.UNICI.SGL, non ci resta che cambiare il nostro intervallo di riferimento e la formula farà tutto il lavoro per noi.

Per esempio, nel caso degli avversari:

=MATR.CONTA.UNICI.SGL(TBL_NBA[AVVERSARI])

Se definiamo bene le intestazioni delle tabelle, non avremo alcun problema anche a far utilizzare questa nuova funzione pure ad altre persone.

Oppure nel caso dei punteggi:

=MATR.CONTA.UNICI.SGL(TBL_NBA[SERIE])

Anche qui, il calcolo è immediato, per nostra fortuna. Quindi non dovremo più rifare tutti i passaggi logici della parte iniziale della guida, una volta creata la funzione la potremo poi riutilizzare ovunque sul foglio.

Nel caso delle LAMBDA dobbiamo quindi definire bene i casi di utilizzo, dato che è adatta se dobbiamo eseguire le stesse operazioni numerose volte sullo stesso file.

Come riutilizzare una funzione LAMBDA su Excel

Per riutilizzare una funzione LAMBDA che abbiamo creato su Excel, il modo più semplice in assoluto è:

• Copiare un foglio vuoto dal file che ha la nostra funzione LAMBDA (FILE A)
• Incollare il foglio vuoto del FILE A in un file che non ha la funzione LAMBDA (FILE B)

A questo punto, il foglio vuoto si porterà dietro tutti i nomi creati sul file precedente (FILE A) e ci permetterà di utilizzare questa funzione anche sul nuovo file.

Idealmente, potremmo avere tutte le nostre LAMBDA personalizzate in un solo file che poi utilizziamo, ad esempio:

• Copiare un foglio vuoto dal file con tutte le nostre funzioni LAMBDA (FILE MASTER)
• Incollare il foglio vuoto del FILE MASTER in un file nuovo (FILE B)

In questo modo, il FILE B avrebbe tutte le nostre LAMBDA e ci permetterebbe di velocizzare numerosi calcoli in un colpo solo.

Se volessimo invece trasferire solamente una LAMBDA, potremmo semplicemente copiare una cella utilizzata dalla LAMBDA nel nuovo foglio ed eviteremmo così di trasportare tutti i nomi sul file precedente - grazie a Jan Karel Pieterse per la specifica!

Un altro metodo possibile è creare un Gist su Github e ottenere poi le nostre LAMBDA sul file - ma, in questo caso, avremo bisogno di utilizzare il plug-in chiamato Excel Labs in modo da richiamarle velocemente sul nostro file. L'altro problema, in questo caso, è utilizzare un mix di formule inglesi / italiane, bisogna stare attenti nell'essere sempre coerenti.

Conclusione sulla funzione LAMBDA in Excel

La stessa procedura vista nella guida dovrebbe aiutarti a creare da zero le tue LAMBDA su Excel, sono sicuramente molti passaggi ma aiutano a comprendere meglio perchè queste funzioni rappresentino una vera rivoluzione per Excel.

Nello specifico, il processo logico migliore per crearle (almeno per me) è:

• Definire un obiettivo chiaro che nasce da combinazioni di funzioni che esegui spesso (tipo DATI.ORDINA, UNICI, CONTA.SE, in questa guida specifica)
• Creare, senza LAMBDA, tutto il processo di creazione che ci sta dietro, con vari passaggi
• Minimizzare le complessità utilizzando delle funzioni di supporto quali STACK.ORIZ, STACK.VERT
• Ridurre gli intervalli da inserire, dichiarandoli con LET
• Riportare la formula su una LAMBDA facendo un test con un intervallo già conosciuto
• Creare la nuova LAMBDA come una funzione

A questo punto, non ti resta che provare su un tuo file e iniziare a creare le tue LAMBDA!

Se già ne hai creata qualcuna, scrivila pure nei commenti in modo che ognuno possa utilizzarla - proprio come MATR.CONTA.UNICI.SGL che abbiamo visto in questa guida e che puoi già utilizzare a tuo piacimento!

Ringrazio Owen Price per avermi dato la spinta necessaria per creare questa guida - se volete sperimentare con le LAMBDA, vi suggerisco caldamente di seguirlo su LinkedIn!