Come recuperare funzioni da una cella Excel

Quando si utilizzano file Excel di terzi, uno dei problemi principali è capire la logica delle singole formule inserite nel file - in questo caso, recuperare le funzioni utilizzate in una certa cella può sicuramente aiutarci a rivedere la struttura ed entrare più velocemente nel file stesso.

Ti sarà sicuramente capitato di aprire un file Excel e di aver trovato una formula lunga due chilometri, magari con funzioni che si annidano una dentro l’altra. La sensazione di smarrimento è notevole e magari stai cercando di capire cosa fa, oppure vuoi semplicemente avere sotto mano un elenco chiaro delle funzioni utilizzate. In questa guida ti mostro come recuperare le funzioni da una cella Excel usando solo formule, senza macro, VBA o strumenti esterni.

L'intera guida è compatibile esclusivamente con Excel 365, dato che sfrutta alcune delle funzioni più recenti in Excel quali RAGGRUPPAPER e REGEX.ESTRAI anche se ci sono sicuramente delle alternative compatibili anche con versioni precedenti.

Alcuni dei vantaggi nel recuperare le funzioni sono:

• Capire subito il livello di complessità della formula
• Individuare più velocemente eventuali errori
• Fare debug in modo strutturato
• Documentare meglio un file condiviso

Vediamolo insieme con un caso pratico.

Come analizzare una formula complessa in Excel

Dobbiamo analizzare una formula complessa in Excel e ci tornerebbe utile recuperare le funzioni utilizzate in una certa cella. Immagina di trovarti questa formula nella cella B5:

=LET(movies; INCLUDI(
RAGGRUPPAPER(TBL_VENDITE[FILM];TBL_VENDITE[MARGINE];SOMMA;;;-2; TBL_VENDITE[REGIONE]=B2);
C2);
total; STACK.ORIZ("TOTALE"; SOMMA(movies));
final; STACK.VERT(total; movies);
final
)

Questa è una formula reale usata in un file che raggruppa i dati di vendita per film e regione, ordinando poi i dati per margine complessivo. L'intero flusso logico è tutto dentro una sola cella ed è difficile capire al volo quante e quali funzioni stai davvero utilizzando - o quale fosse l'idea iniziale della persona che ti ha inviato il file.

Cosa fa questa formula, passo per passo:

1. LET: definisce variabili locali per rendere la formula più chiara
2. INCLUDI: permette all'utente, selezionando C2, di scegliere il numero di film da restituire
3. RAGGRUPPAPER: crea una tabella che raggruppa per film e margine, filtrando per regione, ordinandoli per margine complessivo (-2) e filtrandoli per la regione inserita in B2
4. STACK.ORIZ: crea una riga di totale con l'etichetta TOTALE e la somma dell'array precedente
5. STACK.VERT: impila prima il totale, poi i film, restituendo un'idea di struttura

Il risultato è una tabella leggibile, con dati e totale, generata da una singola cella. Tuttavia, il debugging potrebbe essere complesso se non hai mai visto alcune di queste formule, soprattutto se combinate. Ecco perchè ci serve un modo per recuperarle tutte, in maniera dinamica.

Come recuperare le funzioni utilizzate in una cella Excel

Prima di creare una LAMBDA che ci permette di semplificare il calcolo, vediamo come impostare una formula in grado di recuperare le funzioni utilizzate nella cella Excel precedente:

=MATR.TRASPOSTA(
DATI.ORDINA(
UNICI(
REGEX.ESTRAI(TESTO.FORMULA(B5);"([A-Z\.]+)(?=\s*\()";1);
VERO);
;;VERO)
)

Questa formula è tutto fuorchè intuitiva, quindi vediamola insieme:

• TESTO.FORMULA: prende la formula in formato testo dalla cella B5
• REGEX.ESTRAI: usa un’espressione regolare per trovare tutte le funzioni (quelle che finiscono con una parentesi aperta)
  • ([A-Z\.]+) → prende solo nomi in MAIUSCOLO, anche con punto (es. DATI.ORDINA)
  • (?=\s*\() → guarda se dopo c’è una parentesi, ma non la include
• UNICI: rimuove eventuali ripetizioni, valutando ogni singola colonna (VERO), dato che il risultato si allarga nelle singole colonne di default
• DATI.ORDINA: ordina le funzioni in ordine alfabetico, anche in questo caso con VERO dato che parliamo di colonne
• MATR.TRASPOSTA: trasforma l’elenco in verticale, una funzione per riga

Risultato: una lista pulita, subito leggibile, che ci segnala come nella formula precedente siano utilizzate le funzioni INCLUDI, LET, RAGGRUPPAPER, SOMMA, STACK.ORIZ, STACK.VERT. Adesso abbiamo una bussola per capire meglio come è stata creata la formula precedente e possiamo analizzare i singoli passaggi con più facilità.

Chiaramente, potevamo anche rimuovere il DATI.ORDINA per avere l'ordine di apparizione nella formula anzichè l'ordine alfabetico - è una scelta personale, anche se penso sia più intuitivo vedere le formule in ordine alfabetico.

Nel caso poi volessi vedere tutte le funzioni in un colpo solo, potresti fare così:

=TESTO.UNISCI(", ";;E5#)

La funzione TESTO.UNISCI ci permette di compattare tutti i risultati in una sola cella, quindi magari può essere ideale se vogliamo ridurre lo spazio utilizzato per questa fase di revisione. Potenzialmente, potresti aggiungere questa scelta anche nella funzione precedente, ma in questo caso le teniamo staccate.

E se avessimo più celle da controllare? Se tu volessi recuperare tutte le funzioni utilizzate in più di una cella, potresti fare così, considerando da E18 a E19, per esempio:

=MATR.TRASPOSTA(
DATI.ORDINA(
UNICI(
REGEX.ESTRAI(
TESTO.UNISCI("";;SE.ERRORE(TESTO.FORMULA(E18:E19);""));"([A-Z.]+)(?=\s*()";1);
VERO);
;;VERO)
)

In sostanza andresti a creare un testo più lungo che comprende tutte le formule inserite, evitando gli errori, per poi recuperare le funzioni utilizzate. In genere, questa revisione di formule andrebbe però fatta cella per cella.

Come creare una funzione personalizzata LAMBDA per recuperare le funzioni utilizzate in una cella Excel

Abbiamo trovato il modo per ottenere le funzioni utilizzate all'interno di una formula in Excel. Tuttavia, se vuoi qualcosa di più elegante e riutilizzabile, puoi trasformare la formula precedente in una LAMBDA (le funzioni personalizzate in Excel) e recuperare più facilmente le funzioni all'interno di una singola cella:

=LAMBDA(funzione;
SE.ERRORE(
MATR.TRASPOSTA(
DATI.ORDINA(
UNICI(
REGEX.ESTRAI(TESTO.FORMULA(funzione);"([A-Z\.]+)(?=\s*\()";1);
VERO);
;;VERO)
);
"La cella non ha alcuna funzione al suo interno"
)
)

L'utente dovrà inserire solamente la cella di interesse, con la funzione complessa all'interno, per ottenere poi la lista completa di funzioni utilizzate. Se non c'è alcuna funzione nella cella di partenza, avremo la scritta "La cella non ha alcuna funzione al suo interno", realizzato tramite SE.ERRORE. La struttura è comunque quasi equivalente a quella precedente.

Chiaramente puoi modificare la funzione come preferisci, ma per installarla dovrai andare su Formule > Definisci Nome > Inserire FUNZIONI.ESTRAI come nome > Inserire la formula precedente con LAMBDA per Riferito a:, come vedi nell'immagine.

A questo punto avrai la tua nuova funzione - e potrai usarla come preferisci, ad esempio:

=FUNZIONI.ESTRAI(B5)

Otterrai qualcosa del genere:

• LET
• INCLUDI
• RAGGRUPPAPER
• SOMMA
• STACK.ORIZ
• STACK.VERT

Questo ti restituisce subito un’idea precisa dei vari pezzi usati nella formula. Molto utile anche quando vuoi vedere se stai usando funzioni dinamiche o per verificare la compatibilità con versioni più vecchie di Excel, che puoi controllare anche in altri modi. Personalmente, è utile anche per verificare se vi siano modi più "moderni" di arrivare allo stesso risultato, ad esempio CERCA.VERT vs CERCA.X o combinazioni di funzioni ormai datate, tipo SINISTRA+TROVA anzichè TESTO.PRECEDENTE.

Se selezionassi una cella che non ha alcuna funzione, come E1, ti apparirebbe il messaggio "La cella non ha alcuna funzione al suo interno".

Conclusione

Funzioni Excel vanzate come LET, STACK, INCLUDI e RAGGRUPPAPER sono straordinarie, ma quando si sommano in una singola formula, si rischia di perdersi. In questo caso, utilizzare una LAMBDA dedicata per recuperare le funzioni utilizzate in una cella, oppure usare una combinazione di formule, può essere un ottimo modo per velocizzare il nostro lavoro. In particolare, che tu debba fare debug, formare un collega o semplicemente capire cosa hai scritto tre mesi fa (ahimè, capita anche a me), questa tecnica ti fa risparmiare tempo.

Non ti resta che fare un test con un tuo file di lavoro - quando trovi una formula lunga davanti a te, prova a applicare le formule della guida e fammi sapere il risultato!