Come creare un corkscrew in Excel

Il corkscrew in Excel è la creazione di un prospetto con saldo iniziale, entrate, uscite e un saldo finale. Questa è una operazione tipica in Excel, spesso non dinamica, che può essere riassunta in italiano come "prospetto di movimentazione".

Risulta un caso interessante per spiegare sia come lavorano i dynamic array sia come evitare problemi di ricorsività, dato che idealmente vogliamo ottenere un risultato come questo dell'immagine.

Assumiamo di avere questa situazione iniziale, in una tabella:

• DATA: i fine mese per ciascun mese considerato
• ENTRATE: le entrate del mese
• COSTI: i costi del mese

Ciò che vogliamo realizzare, in maniera completamente dinamica, è un prospetto come quello di destra con:

• Date in cima
• Saldo iniziale per singolo mese considerato
• Entrate del mese
• Uscite del mese
• Saldo intermedio del mese
• Saldo finale del mese

Chiaramente potresti fare tutto a mano o con formule tipo =SOMMA, se non anche semplici sommatorie con simboli algebrici (+,-). Noi però vogliamo realizzarlo in maniera dinamica, sfruttando i dynamic array. Ciò significa che vogliamo avere una singola formula per ogni riga considerata. Ci sono tanti modi anche di impostare il ragionamento di partenza, ma sono sicuro che potrai sfruttare quanto spiegato nella guida anche in tuo caso specifico.

Come inserire la sequenza di date per il corkscrew in Excel

L'inserimento della sequenza di date è semplificato, dato che abbiamo già i fine mese:

=MATR.TRASPOSTA(TBL[DATA])

Supponendo che non ci siano duplicati e che siano già ordinati, possiamo utilizzare una semplice MATR.TRASPOSTA che traspone i nostri valori.

Se così non fosse, potremmo mettere un'altra formula per mettere le date in ordine:

=DATI.ORDINA(MATR.TRASPOSTA(TBL[DATA]);;;VERO)

Sfruttiamo DATI.ORDINA per ordinare le date per singola colonna, quindi dobbiamo inserire il VERO finale. Così, non avrai problemi anche se fai operazioni sulla tabella precedente.

Come generare il saldo intermedio mensile con entrate e uscite in Excel

Per generare il saldo intermedio mensile, non dovremo fare altro che recuperare i dati precedenti della nostra tabella sia per le entrate sia per le uscite / costi:

=CERCA.X(G4#;TBL[DATA];TBL[ENTRATE];0)

Per ciascuno dei valori di G4, cioè la sequenza di date, avremo le singole entrate. Se non ci sono, appare 0.

Per i costi, la formula è equivalente:

=CERCA.X(G4#;TBL[DATA];TBL[COSTI];0)

L'unico dettaglio da considerare è per la differenza. Qui si può fare in tanti modi diversi, ad esempio usando anche STACK.VERT o simili, ma la tentazione sarebbe quella di fare un semplice =SOMMA(G6#;-g7#).

In realtà questa formula non va bene perchè SOMMA restituisce un valore solo, mentre a noi serve il saldo progressivo. Più semplicemente, possiamo fare così:

=G6#-g7#

Lavorando con gli array, avremo i singoli risultati per mese. Come noti, abbiamo:

• Mese1 = 3.500 - 2.400 = 1.100 Euro
• Mese2 = 2.800 - 4.000 = -1.200 Euro

E così via.

Come inserire il saldo finale dinamico mensile in Excel

Per trovare il saldo finale dinamico dobbiamo sommare, per ciascun valore di data, i valori partendo dal nostro saldo iniziale. La formula sarà quindi:

=SCAN(C2;G8#;SOMMA)

SCAN permette di effettuare una somma progressiva e in questo caso partiamo da C2, cioè 15.000 Euro. Da qui, G8# considera ogni singolo valore dei nostri saldi intermedi per poi sommarli insieme. Come noti, la progressione sarà:

• Saldo finale mese 1 = 15.000 + 1.100 = 16.100 Euro
• Saldo finale mese 2 = 16.100 (che è il saldo finale del mese 1) - 1.200 = 14.900 Euro

E così via. SCAN è una delle funzioni chiamate LAMBDA Helper e la sua versione successiva, più complessa e articolata, sarebbe REDUCE.

Come noti, in realtà il nostro saldo finale è indipendente dal saldo iniziale, in modo da evitare problemi che richiedono l'utilizzo di riferimenti circolari. Questo è un trucco per rendere più semplice il calcolo, dato che in realtà non è necessario visualizzare il nostro saldo iniziale, almeno per arrivare a creare un prospetto mensile.

Come inserire il saldo iniziale dinamico mensile in Excel

Il saldo iniziale dinamico mensile viene ottenuto a partire dal saldo finale, dato che non è altro che il valore del mese precedente. Si potrebbe lavorare con funzioni come SCARTO o altri riferimenti, ma un modo più semplice è questo:

=STACK.ORIZ(C2;ESCLUDI(G9#;;-1))

In pratica, stiamo creando un array in orizzontale (STACK.ORIZ) che abbia prima di tutto il nostro saldo iniziale (C2) per poi unirsi a tutti i valori di G9# (il saldo finale), a parte l'ultimo (il -1 finale). In questo modo possiamo allineare i nostri risultati e vedere il singolo prospetto per mese, senza fare ulteriori passaggi.

A questo punto, potremmo voler inserire un grafico sparkline come quello che vedi nell'immagine. Puoi cliccare su Inserisci > Grafico sparkline e inserire questa linea temporale, che può essere utile per visualizzare meglio le varie progressioni.

Un consiglio è quello di unire le celle della sparkline, in modo da poter rispecchiare perfettamente quanto vedi sopra.

Come modificare il prospetto dei saldi se abbiamo un salto temporale

Nel caso precedente, abbiamo tutte le date disponibili - ma cosa succede se invece le date sono con dei salti temporali, per i nostri saldi? Ad esempio, potremmo non avere dati per Settembre e Ottobre, ma solo da Agosto a Novembre.

Per semplicità, considereremo esclusivamente i casi dove le date inserite sono dei fine mese, ma puoi chiaramente adattare il ragionamento come preferisci. Nell'ottica di un prospetto come questo, vogliamo comunque visualizzare anche i mesi a zero, anche per agevolare l'eventuale inserimento di nuovi dati.

Vediamo come fare:

=MATR.TRASPOSTA(FINE.MESE(MIN(TBL_X[DATA]);
SEQUENZA(
DATA.DIFF(MIN(TBL_X[DATA]);MAX(TBL_X[DATA]);"M")+1;
;0)))

Vediamola per gradi:

• MATR.TRASPOSTA ci serve per mettere i dati sulla stessa riga
• FINE.MESE prenderà prima di tutto il nostro valore iniziale, per poi generare una sequenza di date tutte con il relativo fine mese
• SEQUENZA genera la sequenza necessaria, grazie all'uso di DATA.DIFF
• DATA.DIFF fa la differenza fra il minimo e il massimo delle date trovate nella TBL_X, per poi restituire i mesi ("M") e aggiungere 1. In questo modo, tutti i mesi saranno coperti nel nostro ragionamento. Questsa funzione è veramente strana perchè non esiste neanche nella documentazione di Excel, ma torna comoda in casi specifici come questi
• Lo 0 finale è fondamentale proprio perchè noi vogliamo prendere anche il nostro valore iniziale

A questo punto, abbiamo una sequenza dinamica di date e tutte le altre formule si adatteranno, senza necessità di ulteriori modifiche. Questa è la magia di lavorare con i dynamic array (matrici dinamiche), evitando faticosi copia/incolla.

Ma come potremmo fare se volessimo evitare di inserire tutte queste formule? Si può fare in un colpo solo?

Come creare un prospetto con vari saldi e movimenti in Excel

Per generare un prospetto con saldo iniziale, saldo finale, entrate e uscite in un colpo solo, con Excel, non possiamo che far riferimento a funzioni come LAMBDA. Vediamola insieme:

=LAMBDA(saldo_iniziale;col_date;col_entrate;col_costi;

LET( date;MATR.TRASPOSTA(FINE.MESE(MIN(col_date);SEQUENZA(DATA.DIFF(MIN(col_date);MAX(col_date);"M")+1;;0)));

entrate;CERCA.X(date;col_date;col_entrate;0);

costi;CERCA.X(date;col_date;col_costi;0);

saldo_intermedio;entrate-costi;

saldo_finale;SCAN(saldo_iniziale;saldo_intermedio;LAMBDA(a;b;a+b));

saldo_iniziale_riga;STACK.ORIZ(saldo_iniziale;ESCLUDI(saldo_finale;;-1));

STACK.VERT(date;saldo_iniziale_riga;entrate;costi;saldo_intermedio;saldo_finale) ) )( C2; TBL[DATA]; TBL[ENTRATE]; TBL[COSTI] )

Rivediamola per gradi, anche se le logiche sono quasi uguali al caso precedente:

• LAMBDA indica che dovremo inserire il saldo iniziale, le date, le entrate e i costi
• LET ci permette di creare variabili che possiamo riutilizzare
• Le date saranno pescate in maniera dinamica a seconda dell'inserimento dell'utente
• Entrate e costi saranno anche in questo replicate, riprendendo lo schema precedente
• Il saldo finale sarà calcolato come sommatoria e l'unica differenza è l'inserimento di LAMBDA(a;b;a+b), ma il concetto è uguale a SOMMA. Se usiamo SOMMA, sfruttiamo le cosiddette ETA-Reduction, ma è uguale
• Il saldo iniziale sarà equivalente alla formula precedente, riprendendo il saldo iniziale per il primo mese e i valori del saldo finale altrimenti
• Il risultato sarà un grande STACK.VERT con tutti i nostri dati impilati, partendo dalle date fino al saldo finale

Alla fine, C2, TBL[DATA], TBL[ENTRATE] e TBL[COSTI] ci servono solo per testare che la formula funzioni e, in effetti, il risultato finale è corretto! Come vedi, la visualizzazione è uguale a quella pre-utilizzo della LAMBDA.

Lo step successivo è: ma dobbiamo veramente ricordarci tutti questi passaggi, o possiamo semplificare creando una funzione personalizzata?

Come generare un prospetto saldo iniziale con entrate uscite e saldo finale in Excel usando una LAMBDA

Abbiamo fatto il più del lavoro per generare il nostro prospetto in maniera dinamica, ma ora dobbiamo creare una funzione personalizzata LAMBDA per agevolare anche chi ha meno dimestichezza con Excel.

Avendo testato che la soluzione funziona, non ci resta che copiare la nostra formula precedente e andare in Formule > Nomi definiti > Definisci nome.

Qui inseriremo:

• Nome = CORKSCREW
• Commento = un commento che decidiamo, per aiutare l'utente in fase di utilizzo
• Riferito a = la formula precedente, togliendo tutto ciò che abbiamo dopo la formula, ad esempio (C2...)

Attenzione - visto che è una funzione personalizzata, è fondamentale eliminare i riferimenti per il test, altrimenti non sarà veramente dinamica.

Una volta creata, l'utente potrà scrivere =CORKSCREW e apparirà il nostro commento.

I singoli argomenti andranno poi inseriti come indicato nella formula, quindi saldo, poi le date, poi le entrate, poi le uscite.

Alla fine, il risultato sarà come quelli precedenti, ma con il vantaggio di dover solo inserire i quattro argomenti!

Aggiungiamo solo un ultimo caso - ma se invece avessimo varie transazioni per mese, non per forza con i fine mese?

Come creare un prospetto con saldo movimenti e saldo finale in Excel, con transazioni multiple

Se volessimo creare un prospetto Excel con un saldo influenzato da movimenti multipli in entrata e in uscita, dovremmo fare qualche modifica. Prima di tutto, le date:

La formula aggiornata è leggermente diversa dalla precedente:

=MATR.TRASPOSTA(FINE.MESE(MIN(TBL_M[DATA]);SEQUENZA(
DATA.DIFF(MIN(TBL_M[DATA]);FINE.MESE(MAX(TBL_M[DATA]);0);"M")+1;;0)))

In pratica aggiungiamo al massimo il FINE.MESE, così da avere i dati corretti di riferimento e ci assicuriamo di avere la giusta distanza fra il valore minimo e quello finale.

Per le entrate e i costi, abbiamo variazioni più evidenti perchè dobbiamo spiegare a Excel di fare un calcolo dinamico per ciascuna delle date che trova in G4#. Dovremo utilizzare una combinazione di funzioni:

=MAP(G4#;LAMBDA(x;SOMMA.PIÙ.SE(TBL_M[ENTRATE];TBL_M[DATA];"<="&x;TBL_M[DATA];">"&FINE.MESE(x;-1))))

In pratica, la formula fa questo:

• MAP considera tutti i dati dell'array in G4, singolarmente
• A questo punto sommiamo tutti i valori nelle entrate per le date che siano inferiori o uguali al valore della transazione (prima condizione) e superiori al valore del fine mese del mese precedente (seconda condizione, notare il -1)

In questo modo, avremo una formula dinamica.

La formula per i costi segue la stessa identica logica!

=MAP(G4#;LAMBDA(x;SOMMA.PIÙ.SE(TBL_M[COSTI];TBL_M[DATA];"<="&x;TBL_M[DATA];">"&FINE.MESE(x;-1))))

Conclusioni

Come hai visto, realizzare un corkscrew per generare un prospetto di movimentazione in un colpo solo in Excel non è troppo complicato, ma devi pianificare bene la struttura per poi lavorare al meglio.

Lo schema:

• Saldo iniziale
• Entrate
• Uscite
• Saldo finale

è molto comune, ma non per forza viene sfruttato al meglio e sono molti i casi che vedo di formule trascinate, poco efficienti e, purtroppo, anche errate.

Se poi è un tema che ti interessa particolarmente, ti suggerisco di dare un'occhiata a funzioni LAMBDA anche più complete e complesse della mia come quelle realizzate da Craig Hatmaker. Craig è un vero esperto di modellazione e crea funzioni approfondite e complesse che richiedono un certo studio per essere comprese 100%, l'esempio che hai visto nella guida è più immediato ma, chiaramente, anche meno adattabile.

Se lavori con gli array, avrai molta più tranquillità sul risultato e i tuoi file saranno anche più veloci, quindi non ti restare che provare!