Aumenta le performance di Excel: i segreti dei formati numerici personalizzati

Quando si tratta di lavorare con i numeri in Microsoft Excel, c’è una vasta gamma di strumenti e funzionalità potenti disponibili. Una caratteristica spesso trascurata è la possibilità di creare formati numerici personalizzati, grazie ai quali definire modelli di formattazione non disponibili di default in Excel. In questo articolo ti mostrerò alcuni dei più evidenti vantaggi che potrai conseguire utilizzando formati numerici personalizzati, con un particolare focus sulla velocità di esecuzione e sulla capacità di sostituire formule complesse.

La sintassi del formato personalizzato

Come già illustrato in un articolo precedente, un formato personalizzato è una combinazione di testo normale racchiuso tra virgolette e codici segnaposto specifici, ognuno con un significato proprio.
Un formato può avere fino a quattro sezioni di codice, ognuna separata dal punto e virgola; queste sezioni, nell’ordine, comunicano a Excel come rappresentare i numeri positivi, i numeri negativi, gli zeri e la presenza di un testo al posto di un numero:

<NUMERI_POSITIVI>;<NUMERI_NEGATIVI>;<ZERO>;<TESTO>

Non sei costretto a definire tutte e 4 le sezioni, ad esempio se definisci solo una sezione, sarà applicata a tutti i numeri mentre se ne definisci 2, la prima sezione verrà applicata ai numeri positivi ma anche agli zeri. Puoi anche lasciare vuota una sezione intermedia: ad esempio se desideri rappresentare in modo diverso gli zeri e sei sicuro che non ci saranno numeri negativi, puoi non scrivere nulla come seconda sezione ma digitare un altro punto e virgola e passare direttamente alla terza sezione (tieni presente però che un eventuale numero negativo non verrà mostrato affatto, perché manca il formato a lui dedicato).

I principali segnaposti di formato

La guida in linea di Excel è molto esaustiva sull’argomento (fai attenzione perché in alcuni esempi contiene errori di traduzione della sintassi!) per cui ecco un riassunto dei segnaposti secondo me più utili:

, e .
Rappresentano i segnaposto per i separatori dei decimali e delle migliaia. Se hai Windows in inglese saranno probabilmente invertiti.

“testo”
Qualsiasi testo tra virgolette verrà mostrato tal quale. Le virgolette a volte non sono necessarie (ad es. per i caratteri senza significato di segnaposto) ma è sempre meglio non rischiare!

0
Segnaposto per una cifra da visualizzare sempre, nella posizione in cui appare nel formato, quand’anche fosse uno zero che tu normalmente non scriveresti perché inutile; ad es. scegliendo come formato 0,00 rappresenteresti 50 centesimi come 0,50 piuttosto che 0,5 (utile per mantenere l’allineamento con gli altri importi presenti in colonna). Tieni presente che un formato come il precedente non solo aggiunge eventuali zeri non strettamente necessari ma mostra come arrotondati a 2 cifre decimali i numeri che ne hanno di più. Puoi inserire questo segnaposto anche a sinistra della virgola decimale, ad es. usando 00000 per rappresentare un numero intero sempre con 5 cifre, anche se inutili: è il caso dei Codici di Avviamento Postale (CAP) che in alcune regioni italiane (Lazio, Umbria e Sardegna) prevedono degli zeri iniziali.

#
Segnaposto per una cifra che, se non significativa, non verrà visualizzata, ad es. #,# non mostra le unità per i numeri tra 0 e 1, esclusi, 0 verrà mostrato con la sola virgola! # è utile più che altro per poter rappresentare le posizioni ove posizionare altri segnaposti (scrivi ad es #.##0 per visualizzare il separatore delle migliaia; 0,###0 per visualizzare come decimali sempre una cifra 0 oltre quelle presenti, ma fino a un massimo di 4, dopo di che Excel arrotonderà). Un segnaposto “.” di separatore migliaia scritto in coda al formato mostra il numero diviso per 1000: usa questa possibilità per rappresentare numeri molto grandi in forma compatta, per esempio sui grafici (es: 123456789 verrà mostrato come 123,5M usando il formato 0,0..”M”)

?
Come 0 ma al suo posto visualizza uno spazio, in modo da mostrare allineati numeri aventi diversi decimali (usa ad esempio 0,0?? per incolonnare i decimali mostrandone minimo 1 e massimo 3).

@
Questo segnaposto mostra la conversione in testo di quanto presente nella cella. Tra i vari usi, potrebbe esserti utile per rendere evidenti le celle che per qualche anomalia non contengono un numero (ad esempio perché il riconoscimento ottico dei caratteri di una scansione ha interpretato come O maiuscola quello che in realtà era uno zero). Infatti, invece di richiamare Home|Modifica|Trova e seleziona|Vai a formato speciale o di sfruttare la funzione CONTA.NUMERI(), ti basterà impostare come formato 0;0;0;”Errore! Ho trovato “@

*-
Usalo per inserire tanti caratteri “-” (o altro) come filler prima/dopo altri segnaposti; non usare le virgolette attorno al carattere da ripetere. Nel formato ci può essere un unico filler: in caso di molteplici viene considerato quello più a destra.

_x
Usalo per aggiungere uno spazio largo come il carattere “x” (o altro); non usare le virgolette attorno al carattere in questione. Excel lo usa spesso per produrre un allineamento omogeneo di numeri potenzialmente formattati in maniera diversa a causa della presenza di diverse sezioni di formato (ad es. numeri positivi e negativi).

\
Rappresenta il carattere di escape da anteporre ad un carattere normalmente interpretato come segnaposto al fine di visualizzarlo invece tal quale (scrivi ad es. \” per visualizzare il simbolo dei pollici dopo un numero: senza il carattere di escape, Excel ti restituirebbe un errore perché si aspetterebbe ulteriori virgolette a chiusura di un testo).

?/?
Usa questo formato per restituire la frazione ridotta ai minimi termini (ad es. 3,2 diventa 16/5). Anteponendo il segnaposto # potrai mostrare l’intero e solo i decimali in frazione (ad es. 3,2 diventa 3 1/5).

gg, mm, aaaa, hh, ss,…
Excel ti mette a disposizione anche tutta una lunga serie di segnaposti per visualizzare giorno, mese, anno e ore, minuti, secondi dal momento che sono tutte quantità che lui memorizza come numeri. Un consiglio per capire quale segnaposto usare è questo: più volte ripeti il carattere, più lunga sarà la rappresentazione; la data 7 dicembre 2023 verrà quindi visualizzata come 7, 07, gio e giovedì se come formato scegli rispettivamente g, gg, ggg o gggg. Stesso discorso per la rappresentazione dei mesi. Presta attenzione al segnaposto mm perché esprime sia i mesi sia i minuti (questi ultimi considerati tali solo se seguono h/hh o precedono s/ss)! Utilizza [hh] o [mm] per mostrare la rappresentazione di un lasso temporale in termini di ore o minuti totali, senza quindi che i valori ripartano da zero dopo 24 ore o 60 minuti.

Utilizzare un formato per risparmiare formule

Un impiego vantaggioso dei formati personalizzati è quello che ti permette di evitare di scrivere delle formule per visualizzare i dati nel modo che desideri. Anche se le formule fossero semplici, potresti dover conservare i dati di partenza dentro a celle aggiuntive (che magari nasconderai alla vista dell’utente) e quindi consumeresti più memoria inutilmente; se le formule fossero complesse avresti in più l’aggravio dell’onere computazionale.

Ad esempio, un’esigenza frequente è quella di nascondere i valori zero all’interno delle celle, spesso per agevolare la lettura ed interpretazione dei dati o per altri motivi più commerciali (in un preventivo ad un cliente, meglio non mostrare nulla come sconto piuttosto che un bel 0% !). Cancellare proprio il valore all’interno delle celle non è generalmente una buona idea: un elenco che presenti celle veramente vuote (al posto di zeri nascosti) non permetterà navigazioni e selezioni automatiche (per esempio tramite Ctrl+Shift+freccia) perché si arresteranno alla prima cella vuota. Altro effetto collaterale è che testi troppo lunghi a sinistra di celle vuote, al posto di essere troncati, ne invaderanno lo spazio, rendendo l’elenco di difficile lettura perché alcune colonne mostreranno dati mescolati con quelli della colonna precedente.

Per nascondere gli zeri puoi scegliere come formato personalizzato uno singolo (es: #, ma verrebbe applicato a tutte le casistiche) o meglio un formato con le 3 distinte sezioni, l’ultima delle quali lasciata in bianco non scrivendo nulla dopo il punto e virgola (es: 0,00;-0,00;).
Supponendo di voler nascondere eventuali zeri dalla cella A1, evita invece di utilizzare formule, come:

=SE(A1=0;””;A1)

o peggio ancora la seguente, che richiama in più una conversione implicita:

=SOSTITUISCI(A1;”0″;””)

sia perché ad Excel occorrerebbe tempo per calcolarle e sia perché non potresti più effettuare calcoli numerici su dette celle, perché la maggior parte delle funzioni matematiche ignorano le celle vuote ma restituiscono errori in caso di stringa di testo vuota (“”).

In linea generale, più ti assicuri la possibilità di effettuare calcoli matematici con i dati che hai e meglio è. Se in una cella vuoi visualizzare, per un prodotto, il numero di pezzi residui in magazzino, evita di scrivere formule (ad es. tramite le funzioni CONCATENA / CONCAT o con l’operatore &) che accodino il testo ” pezzi” ai numeri perché poi il dato diventerebbe testuale e non più elaborabile (non potresti per esempio calcolare la media delle giacenze): meglio quindi usare come formato 0 ” pezzi”
Analogamente, se vuoi mostrare in un’unica cella un testo che includa la data di pagamento di una fattura 60gg d.f.f.m. (data fattura fine mese), evita di accodare del testo e mantieniti la possibilità di accedere alle date, formattandole con
“Pagamento 60gg d.f.f.m.: “gggg gg/mm/aaaa

Utilizzare un formato per evitare la Formattazione Condizionale

Immagina di avere un elenco con date di ordine e di spedizione di merce acquistata online e di voler aggiungere una colonna con lo Stato dell’ordine: “Puntuale” se la spedizione avviene il giorno seguente l’acquisto, “In ritardo” se avviene dopo, “In anticipo” se avviene prima, ossia nel medesimo giorno. Potresti utilizzare come formula

=SE(B2<A2+1;”In Anticipo”;SE(B2=A2+1;”Puntuale”;”In Ritardo”))

ma introdurresti dei rallentamenti dovuti alla nidificazione della funzione SE(), piuttosto onerosa. Meglio quindi valorizzare le celle con la differenza tra la data di spedizione e quella di acquisto diminuita di 1 giorno, così da ottenere una misura che esprima il discostamento dalla situazione di spedizione puntuale:

C2=B2-A2-1

Così facendo, ti basterà poi definire un formato personalizzato come il seguente:
“In Ritardo”;”In anticipo”;”Puntuale”

In questo scenario le uniche formule sarebbero banali sottrazioni (quindi molto veloci) e ti manterresti la possibilità di effettuare ulteriori analisi sulla distribuzione dei tempi di spedizione, avendo a disposizione un dato ancora numerico e non testuale. Potresti anche visualizzare delle icone al posto/assieme al testo: basta premere la combinazione di tasti Win+. per accedere all’elenco delle emoji (purtroppo si vedranno a colori solo su Excel Online).

L’immagine sottostante mostra nel dettaglio un esempio di formato. Noterai come io abbia scelto dei colori meno saturi di [Rosso], [Verde],… (ve ne sono in tutto 56: provali cambiando il numero!) e abbia visualizzato sia icone che testo, allineandoli all’estremità dei due lati usando come filler il carattere di spazio.

Sfruttare un formato personalizzato ha quindi anche il grande vantaggio di poter utilizzare colori differenti senza dover ricorrere alla Formattazione Condizionale e accusare l’estrema lentezza che questa funzionalità apporta al foglio di calcolo, essendo lo strumento super-volatile per antonomasia. In più, in caso di condizioni da verificare semplici (banali confronti numerici), è possibile definire al posto delle 4 sezioni fisse (numeri positivi/negativi/zero/testo) fino a 2 sezioni condizionali più una terza da applicare in caso di condizioni non verificate; ciò sempre con l’obiettivo di evitare formattazioni condizionali quando non strettamente necessario.

Conclusioni

Come hai visto, i formati numerici personalizzati in Excel sono molto potenti e ti permettono di personalizzare facilmente la visualizzazione delle informazioni nelle celle. Questo spesso riesce ad evitare di dover progettare soluzioni più complesse che possono in molti casi influire sulle performance.

Per un supporto più esaustivo e guidato puoi seguire il nostro corso Microsoft Excel Expert oppure richiedere un percorso formativo personalizzato: contattaci per maggiori informazioni!