Tecniche di refactoring del codice nei fogli di calcolo
Riassunto
Le tecniche di refactoring del codice ti aiutano a ristrutturare formule e logica del foglio di calcolo senza cambiare cosa fanno, solo per renderle più leggibili, facili da correggere e riusare. Questa guida copre sei approcci efficaci: Extract Method, Replace Temp with Query, semplificazione Inline, incapsulamento, eliminazione duplicati e sostituzione degli IF annidati, con esempi pronti da copiare in Excel o Google Sheets.
Le tecniche di refactoring del codice servono a ripulire qualcosa che funziona già, senza romperlo. Nel mondo dei fogli di calcolo significa prendere una formula che dà il risultato giusto ma sembra scritta alle 23 il giorno prima della scadenza, e trasformarla in qualcosa che vorrai davvero riaprire tra tre mesi.
Biscuit l'ha già recuperata per voi tra i cespugli delle celle. Ecco cosa fa davvero la differenza.
La tua formula funziona, ma qualcosa proprio non torna?
Il segnale classico: apri un file scritto sei mesi fa e passi tre minuti a leggere una singola cella prima di capire cosa fa. Oppure copi una formula in una nuova colonna e quella si rompe in silenzio perché dentro c'era un riferimento di riga scritto a mano.
Il refactoring non cambia il risultato. Cambia la struttura, così il risultato resta corretto anche quando cambia il contesto intorno: nuove righe, nuove colonne, un foglio rinominato.
Puoi saltare questo passaggio se: il file è un calcolo una tantum che nessun altro toccherà. Il refactoring ha un costo (tempo, disturbo) e se il beneficio è zero, anche il ritorno lo è.
Ne vale la pena se: più persone usano il file, lo aggiorni regolarmente, oppure le formule si incatenano tra più fogli.
Alcuni segnali d'allarme da non ignorare:
Una formula più larga della colonna anche allo zoom al 100%
Più di due livelli di IF annidati
Numeri scritti a mano che ricompaiono in più di tre punti
Colleghi che ti chiedono di spiegare cosa fa una cella invece di leggerla da soli
Anche uno solo di questi segnali basta per programmare una sessione di refactoring.
Extract Method: spacca una formula in passaggi con nome
Questa è la tecnica più utile in assoluto, tanto nel codice quanto nei fogli di calcolo. L'idea: se una formula fa tre cose, la dividi in tre celle, una per passaggio, poi combini i risultati.
Prima (una cella che fa tutto):
=IF(AND(A2<>"",DATEDIF(A2,TODAY(),"Y")>30),"Senior","Junior")Dopo:
Cella
C2:=DATEDIF(A2,TODAY(),"Y"), chiamata "Età in anni"Cella
D2:=AND(A2<>"",C2>30), chiamata "È senior?"Cella
E2:=IF(D2,"Senior","Junior"), il risultato finale
Ecco cosa dà davvero in una cella vera. Ogni passaggio si legge da solo. Se la soglia passa da 30 a 25, cambi un numero in una cella. Se il DATEDIF si rompe perché manca una data, vedi esattamente dove.
Il compromesso: usi più colonne. Su un foglio già stretto può sembrare uno spreco. Usa un'area di celle di supporto su un foglio separato se lo spazio scarseggia. Chiamalo "Aiuti" o "Calcoli" così chi apre il file capisce subito l'organizzazione.
Funziona identico su Excel e su Sheets, non c'è nessuna limitazione di versione per usare celle di supporto.
Replace Temp with Query: basta salvare quello che puoi calcolare al volo
Una variabile temporanea, in un foglio di calcolo, è una cella che riempi con un valore intermedio che nessun altro richiama direttamente: ti serviva solo per arrivare al passaggio dopo.
A volte queste celle sono utili (vedi sopra). Ma quando hai una catena di cinque celle di supporto e conta solo l'ultima, mentre le intermedie non cambiano mai, puoi comprimerle in un'unica formula.
Tre celle di supporto che costruiscono verso un risultato:
=B2*C2, subtotale=D2*0.2, importo IVA=D2+E2, totale
Compressa:
=(B2*C2)+(B2*C2*0.2)Oppure con un intervallo denominato per l'aliquota:
=B2*C2*(1+AliquotaIVA)La regola: se una cella di supporto viene usata solo da un'altra cella, chiediti se quella formula è abbastanza corta da poter essere incorporata. Se sì, incorporala. Se no, tieni la cella di supporto e dalle un nome sensato.
Dove tracciare il limite: una formula che supera gli 80 caratteri è di solito troppo lunga da incorporare. A quel punto conviene tornare all'Extract Method visto sopra. Tieni le celle di supporto quando aiutano davvero la lettura, toglile quando sono solo rumore.
Encapsulate Field: blinda i valori che non devono cambiare
Nei fogli di calcolo, incapsulare significa tenere i tuoi valori fissi, aliquote, soglie, riferimenti, in un unico posto invece che sparsi come numeri scritti a mano dentro le formule.
È la formula che tutti dimenticano e che tutti cercano.
Un'aliquota IVA scritta a mano come 0.2 in 47 formule dentro una cartella di lavoro è una trappola di manutenzione. Cambi l'aliquota e devi trovarle tutte e 47. Ne perdi tre e hai un errore di calcolo.
Come sistemarlo:
Crea un foglio
Impostazionicon una cella chiamataAliquotaIVA = 0.2Richiamala in ogni formula:
=B2*AliquotaIVAQuando l'aliquota cambia, aggiorni una cella sola. Tutto il resto segue.
Vale anche per i riferimenti di data (la data di oggi come ancora fissa), i moltiplicatori di valuta e le soglie di categoria. Se un valore compare più di due volte e può cambiare, merita un intervallo denominato.
Come creare intervalli denominati:
Excel: scheda Formule > Gestione nomi > Nuovo
Google Sheets: Dati > Intervalli denominati
La convenzione dei nomi conta. AliquotaIVA è chiaro. AI non lo è. tmp_3 nemmeno. Usa nomi che chi non ha scritto la formula capisce in tre secondi.
Elimina le duplicazioni: il principio DRY applicato alle formule
Il principio DRY, Don't Repeat Yourself è tra i più citati nell'ingegneria del software, e vale anche per i fogli di calcolo. Se hai copiato la stessa VLOOKUP su 15 colonne con piccole variazioni, hai un problema di manutenzione pronto a esplodere.
Due strade a seconda della situazione:
Excel 365 / Google Sheets: usa XLOOKUP o INDEX-MATCH con espansione ad array per estrarre più colonne con una sola formula.
=XLOOKUP(A2,TabellaRicerca[ID],TabellaRicerca[[Nome]:[Stato]])Questa restituisce Nome e Stato insieme, senza copiare e incollare su più colonne.
Excel più vecchio (2016 o precedente): crea un'unica chiamata MATCH in una cella di supporto, poi usa INDEX riferendosi a quella posizione:
G2:=MATCH(A2,TabellaRicerca[ID],0), un solo MATCH per tutte le colonneH2:=INDEX(TabellaRicerca[Nome],G2)I2:=INDEX(TabellaRicerca[Stato],G2)
Continui a richiamare lo stesso risultato di MATCH ovunque. Se la colonna di ricerca cambia, aggiorni una sola formula.
Una nota di versione utile: XLOOKUP è arrivata su Excel 2019 per gli abbonati enterprise e su Excel 365. Se il tuo team usa Excel 2016, la strada INDEX-MATCH è quella giusta. Sheets supporta XLOOKUP dal 2022 su tutti i piani.
Replace Conditional: cambia gli IF annidati con qualcosa di più pulito
Gli IF annidati sono il posto dove i fogli di calcolo diventano illeggibili. Al terzo livello hai già perso la maggior parte dei lettori, incluso te stesso tra sei mesi.
Prima (catena di IF annidati):
=IF(A2>10000,"A",IF(A2>5000,"B",IF(A2>1000,"C","D")))Opzione 1: IFS (Excel 2016+ / Sheets)
=IFS(A2>10000,"A",A2>5000,"B",A2>1000,"C",TRUE,"D")Più piatta, ma restano quattro condizioni in una sola formula. Accettabile.
Opzione 2: VLOOKUP con una tabella di riferimento (l'approccio sottovalutato)
Crea una piccola tabella di riferimento:
Da 10001 in su: Categoria A
Da 5001 a 10000: Categoria B
Da 1001 a 5000: Categoria C
Da 0 a 1000: Categoria D
Poi: =VLOOKUP(A2,TabellaSoglie,2,TRUE)
Il TRUE (corrispondenza approssimata) scorre la tabella ordinata e restituisce l'ultima categoria che rientra. La logica ora vive nella tabella, non sepolta nella formula. Vuoi cambiare una soglia? Modifichi una riga nella tabella.
Vale la spesa extra: l'approccio con tabella di riferimento rende la logica verificabile e visibile a chiunque, anche a chi non legge le formule. Se hai fretta puoi saltare questo passaggio: IFS va benissimo fino a tre condizioni.
La funzione SWITCH è un'altra opzione disponibile da Excel 2019+ e su Sheets, utile quando confronti valori esatti invece che intervalli. Si legge meglio di IFS nei casi di corrispondenza esatta.
Substitute Algorithm: quando conviene riscrivere invece di rattoppare
A volte la formula non è sbagliata, è solo l'approccio sbagliato in partenza. Hai ereditato una formula di 200 caratteri che concatena testo con CONCATENATE e &, e devi aggiungere un altro campo. La mossa giusta non è allungarla ancora. È riscriverla con TEXTJOIN.
Prima:
=A2&" | "&B2&" | "&C2&" | "&D2Dopo:
=TEXTJOIN(" | ",TRUE,A2:D2)Se il tuo tavolo cambia faccia domani, questa formula ti segue. TEXTJOIN gestisce le celle vuote, gestisce gli array e non si rompe quando aggiungi la colonna E.
Stessa logica per:
Sostituire VLOOKUP con XLOOKUP quando serve corrispondenza esatta e gestione dei valori mancanti
Sostituire l'incapsulamento con IFERROR con la gestione errori nativa delle funzioni più recenti
Sostituire le formule matrice inserite con Ctrl+Maiusc+Invio con gli array dinamici nativi di Excel 365
Lo stesso principio si applica su scala più grande: se gestisci una grande pipeline di dati dentro un foglio di calcolo che è diventato un groviglio di riferimenti INDIRECT, connessioni esterne e intervalli denominati che puntano ad altri intervalli denominati, a volte la mossa giusta è portare la logica fuori, in uno strumento vero (SQL, Python, Power Query), e usare il foglio solo per la visualizzazione. È una decisione più grande, ma appartiene alla stessa categoria.
Prima di iniziare: le tre cose che ti servono (e quando fermarti)
Le tecniche di refactoring del codice funzionano solo se non rompi niente che non puoi recuperare. Nel mondo dei fogli di calcolo significa:
Una copia di backup. Prima di ogni sessione di refactoring, salva una copia datata della cartella di lavoro. Non un Salva con nome nella stessa cartella: un posto separato (backup cloud, email a te stesso, quello che preferisci). Ringrazierai te stesso la volta che qualcosa va storto.
Sapere quali dovrebbero essere i risultati attuali. Prima di cambiare qualsiasi cosa, annota i valori chiave: totali, cifre di riepilogo, valori di celle importanti. Confrontali con le stesse celle dopo ogni modifica. Un controllo di 10 secondi che intercetta subito le regressioni.
Una modifica alla volta. Non fare Extract Method, incapsulare tre intervalli denominati e appiattire un IF annidato nella stessa sessione. Se qualcosa si rompe, devi sapere quale modifica l'ha causato. Fai una cosa, verifica, poi passa alla successiva.
Questi tre passaggi richiedono circa cinque minuti da impostare. Ti hanno già fatto risparmiare molto più di cinque minuti in ogni sessione dove qualcosa è andato storto senza preavviso.
Il rischio del refactoring è il perfezionismo. Puoi passare un intero pomeriggio a riorganizzare una cartella di lavoro già funzionante, e finire con qualcosa di marginalmente più pulito al costo di mezza giornata.
Fissa un tempo limite prima di iniziare: 45 minuti per questa sessione. Dai priorità alle formule che causano davvero problemi, quelle che vengono lette male, quelle che si rompono nell'uso normale, quelle che bloccano una nuova funzionalità di cui hai bisogno.
Tutto il resto: lascialo stare. Una formula che funziona, che nessuno tocca, che non ha causato un errore in un anno, va bene così. Abbastanza buono è un traguardo legittimo.
Un modo utile di vedere la cosa: il refactoring non serve a rendere perfetta la cartella di lavoro. Serve a rendere più facile la prossima modifica. Chiediti prima di ogni cambiamento: questo rende più veloce o più sicura la prossima cosa che devo fare? Se sì, fallo. Se no, fermati. Good boy, Biscuit.