Formattazione dei numeri in Excel VBA
I numeri sono disponibili in tutti i tipi di formati nei fogli di lavoro di Excel. Potresti già avere familiarità con la finestra pop-up in Excel per utilizzare diversi formati numerici:
La formattazione dei numeri rende i numeri più facili da leggere e da capire. L'impostazione predefinita di Excel per i numeri inseriti nelle celle è il formato "Generale", il che significa che il numero viene visualizzato esattamente come lo hai digitato.
Ad esempio, se inserisci un numero tondo, ad es. 4238, verrà visualizzato come 4238 senza punto decimale o separatori delle migliaia. Verrà visualizzato un numero decimale come 9325,89 con la virgola e i decimali. Ciò significa che non si allineerà nella colonna con i numeri tondi e sembrerà estremamente disordinato.
Inoltre, senza mostrare i separatori delle migliaia, è difficile vedere quanto sia grande un numero senza contare le singole cifre. È in milioni o decine di milioni?
Dal punto di vista di un utente che guarda una colonna di numeri, questo rende piuttosto difficile la lettura e il confronto.
In VBA hai accesso esattamente alla stessa gamma di formati che hai sul front-end di Excel. Questo vale non solo per un valore inserito in una cella di un foglio di lavoro, ma anche per cose come finestre di messaggio, controlli UserForm, grafici e grafici e la barra di stato di Excel nell'angolo in basso a sinistra del foglio di lavoro.
La funzione Format è una funzione estremamente utile in VBA in termini di presentazione, ma è anche molto complessa in termini di flessibilità offerta nella modalità di visualizzazione dei numeri.
Come utilizzare la funzione di formattazione in VBA
Se stai mostrando una finestra di messaggio, la funzione Formato può essere utilizzata direttamente:
| 1 | Formato MsgBox(1234567.89, "#,##0.00") |
Verrà visualizzato un numero elevato utilizzando le virgole per separare le migliaia e per mostrare 2 posizioni decimali. Il risultato sarà 1.234.567,89. Gli zeri al posto dell'hash assicurano che i decimali vengano mostrati come 00 in numeri interi e che ci sia uno zero iniziale per un numero minore di 1
Il simbolo dell'hashtag (#) rappresenta un segnaposto di cifra che visualizza una cifra se è disponibile in quella posizione, oppure niente.
Puoi anche utilizzare la funzione formato per indirizzare una singola cella o un intervallo di celle per modificare il formato:
| 1 | Fogli("Foglio1").Intervallo("A1:A10").NumberFormat = "#,##0.00" |
Questo codice imposterà l'intervallo di celle (da A1 a A10) su un formato personalizzato che separa le migliaia con virgole e mostra 2 posizioni decimali.
Se controlli il formato delle celle sul front-end di Excel, scoprirai che è stato creato un nuovo formato personalizzato.
Puoi anche formattare i numeri sulla barra di stato di Excel nell'angolo in basso a sinistra della finestra di Excel:
| 1 | Application.StatusBar = Format(1234567.89, "#,##0.00") |
Puoi cancellarlo dalla barra di stato usando:
| 1 | Application.StatusBar = "" |
Creazione di una stringa di formato
Questo esempio aggiungerà il testo "Vendite totali" dopo ogni numero, oltre a includere un separatore delle migliaia
| 1 | Fogli("Foglio1").Range("A1:A6").NumberFormat = "#,##0.00"" Totale vendite""" |
Ecco come appariranno i tuoi numeri:
Nota che la cella A6 ha una formula "SOMMA" e questo includerà il testo "Vendite totali" senza richiedere la formattazione. Se viene applicata la formattazione, come nel codice sopra, non inserirà un'istanza aggiuntiva di "Vendite totali" nella cella A6
Sebbene le celle ora visualizzino caratteri alfanumerici, i numeri sono ancora presenti in forma numerica. La formula "SOMMA" funziona ancora perché utilizza il valore numerico in background, non come è formattato il numero.
La virgola nella stringa di formato fornisce il separatore delle migliaia. Nota che devi inserirlo nella stringa solo una volta. Se il numero raggiunge milioni o miliardi, separerà comunque le cifre in gruppi di 3
Lo zero nella stringa di formato (0) è un segnaposto di cifre. Visualizza una cifra se è presente o uno zero. Il suo posizionamento è molto importante per garantire uniformità con la formattazione
Nella stringa di formato, i caratteri hash (#) non visualizzeranno nulla se non ci sono cifre. Tuttavia, se è presente un numero come .8 (tutti i decimali), vogliamo che venga visualizzato come 0.80 in modo che si allinei con gli altri numeri.
Utilizzando un singolo zero a sinistra del punto decimale e due zeri a destra del punto decimale nella stringa di formato, questo darà il risultato richiesto (0,80).
Se ci fosse solo uno zero a destra della virgola decimale, il risultato sarebbe "0.8" e tutto verrebbe visualizzato con una cifra decimale.
Utilizzo di una stringa di formato per l'allineamento
Potremmo voler vedere tutti i numeri decimali in un intervallo allineato sui loro punti decimali, in modo che tutti i punti decimali siano direttamente uno sotto l'altro, indipendentemente dal numero di posizioni decimali presenti su ciascun numero.
Puoi utilizzare un punto interrogativo (?) all'interno della stringa di formato per farlo. Il '?' indica che viene visualizzato un numero se disponibile o uno spazio
| 1 | Fogli("Foglio1").Intervallo("A1:A6").NumberFormat = "#,##0.00??" |
Questo mostrerà i tuoi numeri come segue:
Tutti i punti decimali ora si allineano uno sotto l'altro. La cella A5 ha tre posizioni decimali e questo eliminerebbe normalmente l'allineamento, ma l'uso del carattere "?" allinea tutto perfettamente.
Utilizzo di caratteri letterali all'interno della stringa di formato
Puoi aggiungere qualsiasi carattere letterale alla stringa di formato facendolo precedere da una barra rovesciata (\).
Supponiamo che tu voglia mostrare un particolare indicatore di valuta per i tuoi numeri che non si basa sulla tua locale. Il problema è che se utilizzi un indicatore di valuta, Excel fa automaticamente riferimento al tuo locale e lo cambia in quello appropriato per il locale impostato nel Pannello di controllo di Windows. Ciò potrebbe avere implicazioni se l'applicazione Excel viene distribuita in altri paesi e si desidera garantire che, qualunque sia la locale, l'indicatore di valuta sia sempre lo stesso.
Potresti anche voler indicare che i numeri sono in milioni nel seguente esempio:
| 1 | Fogli("Foglio1").Intervallo("A1:A6").NumberFormat = "\$#,##0.00 \m" |
Ciò produrrà i seguenti risultati sul foglio di lavoro:
Quando si utilizza una barra rovesciata per visualizzare i caratteri letterali, non è necessario utilizzare una barra rovesciata per ogni singolo carattere all'interno di una stringa. Puoi usare:
| 1 | Fogli("Foglio1").Intervallo("A1:A6").NumberFormat = "\$#,##0.00 \mill" |
Questo mostrerà "mill" dopo ogni numero all'interno dell'intervallo formattato.
Puoi usare la maggior parte dei caratteri come letterali, ma non i caratteri riservati come 0, #,?
Uso delle virgole in una stringa di formato
Abbiamo già visto che le virgole possono essere utilizzate per creare separatori di migliaia per grandi numeri, ma possono essere utilizzate anche in un altro modo.
Usandoli alla fine della parte numerica della stringa di formato, agiscono come scaler di migliaia. In altre parole, divideranno ogni numero per 1.000 ogni volta che c'è una virgola.
Nei dati di esempio, lo stiamo mostrando con un indicatore che è espresso in milioni. Inserendo una virgola nella stringa di formato, possiamo mostrare quei numeri divisi per 1.000.
| 1 | Fogli("Foglio1").Intervallo("A1:A6").NumberFormat = "\$#,##0.00,\m" |
Questo mostrerà i numeri divisi per 1.000 anche se il numero originale sarà ancora sullo sfondo nella cella.
Se inserisci due virgole nella stringa di formato, i numeri verranno divisi per un milione
| 1 | Fogli("Foglio1").Intervallo("A1:A6").NumberFormat = "\$#,##0.00,,\m" |
Questo sarà il risultato usando solo una virgola (dividi per 1.000):
Creazione di formattazione condizionale all'interno della stringa di formato
Puoi impostare la formattazione condizionale sul front-end di Excel, ma puoi anche farlo all'interno del tuo codice VBA, il che significa che puoi manipolare la stringa di formato a livello di codice per apportare modifiche.
Puoi utilizzare fino a quattro sezioni all'interno della stringa di formato. Ogni sezione è delimitata da un punto e virgola (;). Le quattro sezioni corrispondono a positivo, negativo, zero e testo
| 1 | Range("A1:A7").NumberFormat = "#,##0.00;[Rosso]-#,##0.00;[Verde] #,##0.00;[Blu]” |
In questo esempio, utilizziamo gli stessi caratteri hash, virgola e zero per fornire migliaia di separatori e due punti decimali, ma ora abbiamo sezioni diverse per ogni tipo di valore.
La prima sezione è per i numeri positivi e non è diverso da quanto abbiamo già visto in precedenza in termini di formato.
La seconda sezione per i numeri negativi introduce un colore (Rosso) che è contenuto all'interno di una coppia di parentesi quadre. Il formato è lo stesso dei numeri positivi, tranne per il segno meno (-) davanti.
La terza sezione per i numeri zero utilizza un colore (verde) tra parentesi quadre con la stringa numerica uguale a quella dei numeri positivi.
La sezione finale è per i valori di testo e tutto ciò di cui ha bisogno è un colore (blu) di nuovo tra parentesi quadre
Questo è il risultato dell'applicazione di questa stringa di formato:
Puoi andare oltre con le condizioni all'interno della stringa di formato. Supponiamo che tu voglia mostrare ogni numero positivo sopra 10.000 come verde e ogni altro numero come rosso potresti usare questa stringa di formato:
| 1 | Intervallo("A1:A7").NumberFormat = "[>=10000][Verde]#,##0.00;[<10000][Rosso]#,##0.00" |
Questa stringa di formato include le condizioni per >=10000 impostate tra parentesi quadre in modo che il verde venga utilizzato solo quando il numero è maggiore o uguale a 10000
Questo è il risultato:
Utilizzo delle frazioni nella formattazione delle stringhe
Le frazioni non sono spesso utilizzate nei fogli di calcolo, poiché normalmente equivalgono a decimali che tutti conoscono.
Tuttavia, a volte servono a uno scopo. Questo esempio mostrerà dollari e centesimi:
| 1 | Range("A1:A7").NumberFormat = "#,##0 "" dollari e "" 00/100 "" centesimi """ |
Questo è il risultato che verrà prodotto:
Ricorda che nonostante i numeri vengano visualizzati come testo, sono ancora lì sullo sfondo come numeri e tutte le formule di Excel possono ancora essere utilizzate su di essi.
Formati di data e ora
Le date sono in realtà numeri e puoi utilizzare i formati su di esse allo stesso modo dei numeri. Se formatti una data come numero numerico, vedrai un numero grande a sinistra della virgola decimale e un numero di posizioni decimali. Il numero a sinistra del punto decimale mostra il numero di giorni a partire da 01-gen-1900 e le posizioni decimali mostrano l'ora basata su 24 ore
| 1 | Formato MsgBox (Ora(), "gg-mmm-aaaa") |
Questo formatterà la data corrente per mostrare "08-lug-2020". Utilizzando 'mmm' per il mese vengono visualizzati i primi tre caratteri del nome del mese. Se vuoi il nome del mese completo, usa "mmmm"
Puoi includere gli orari nella stringa di formato:
| 1 | Formato MsgBox (Ora(), "gg-mmm-aaaa hh:mm AM/PM") |
Verrà visualizzato "08-lug-2020 13:25"
'hh:mm' rappresenta ore e minuti e AM/PM utilizza un orologio di 12 ore anziché un orologio di 24 ore.
Puoi incorporare caratteri di testo nella stringa di formato:
| 1 | MsgBox Format(Now(), "dd-mmm-yyyy hh:mm AM/PM"" today""") |
Verrà visualizzato "08-lug-2020 13:25 oggi"
È inoltre possibile utilizzare caratteri letterali utilizzando una barra rovesciata davanti allo stesso modo delle stringhe di formato numerico.
Formati predefiniti
Excel ha una serie di formati incorporati sia per i numeri che per le date che puoi usare nel tuo codice. Questi riflettono principalmente ciò che è disponibile sul front-end di formattazione dei numeri, sebbene alcuni di essi vadano oltre ciò che è normalmente disponibile nella finestra pop-up. Inoltre, non hai la flessibilità sul numero di posizioni decimali o se vengono utilizzati i separatori delle migliaia.
Numero generale
Questo formato visualizzerà il numero esattamente così com'è
| 1 | Formato MsgBox (1234567.89, "Numero generale") |
Il risultato sarà 1234567.89
Valuta
| 1 | Formato MsgBox(1234567.894, "Valuta") |
Questo formato aggiungerà un simbolo di valuta davanti al numero, ad es. $, £ a seconda della tua lingua, ma formatterà anche il numero a 2 cifre decimali e separerà le migliaia con virgole.
Il risultato sarà $ 1.234.567,89
Fisso
| 1 | Formato MsgBox(1234567.894, "Fisso") |
Questo formato visualizza almeno una cifra a sinistra ma solo due cifre a destra della virgola decimale.
Il risultato sarà 1234567.89
Standard
| 1 | Formato MsgBox(1234567.894, "Standard") |
Visualizza il numero con i separatori delle migliaia, ma solo fino a due cifre decimali.
Il risultato sarà 1.234.567,89
Per cento
| 1 | Formato MsgBox(1234567.894, "Percentuale") |
Il numero viene moltiplicato per 100 e alla fine del numero viene aggiunto un simbolo di percentuale (%). Il formato viene visualizzato con 2 cifre decimali
Il risultato sarà 123456789,40%
Scientifico
| 1 | Formato MsgBox(1234567.894, "Scientifico") |
Questo converte il numero in formato esponenziale
Il risultato sarà 1.23E+06
Si No
| 1 | Formato MsgBox(1234567.894, "Sì/No") |
Questo visualizza "No" se il numero è zero, altrimenti visualizza "Sì"
Il risultato sarà "Sì"
Vero falso
| 1 | Formato MsgBox(1234567.894, "Vero/Falso") |
Questo visualizza "Falso" se il numero è zero, altrimenti visualizza "Vero"
Il risultato sarà "Vero"
Acceso spento
| 1 | Formato MsgBox(1234567.894, "On/Off") |
Questo visualizza "Off" se il numero è zero, altrimenti visualizza "On"
Il risultato sarà "On"
Data generale
| 1 | Formato MsgBox (Ora(), "Data generale") |
Verrà visualizzata la data come data e ora utilizzando la notazione AM/PM. La modalità di visualizzazione della data dipende dalle impostazioni nel Pannello di controllo di Windows (Orologio e regione | Regione). Può essere visualizzato come "mm/gg/aaaa" o "gg/mm/aaaa"
Il risultato sarà "7/7/2020 15:48:25"
Appuntamento lungo
| 1 | Formato MsgBox (Ora(), "Data estesa") |
Verrà visualizzata una data lunga come definita nel Pannello di controllo di Windows (Orologio e regione | Regione). Nota che non include il tempo.
Il risultato sarà "Martedì 7 luglio 2022"
Data media
| 1 | Formato MsgBox (Ora(), "Data media") |
Viene visualizzata una data come definita nelle impostazioni della data breve come definite dalle impostazioni internazionali nel Pannello di controllo di Windows.
Il risultato sarà '07-lug-20'
Data breve
| 1 | Formato MsgBox(Now(), "Short Date") |
Visualizza una data breve come definita nel Pannello di controllo di Windows (Orologio e regione | Regione). La modalità di visualizzazione della data dipende dalla tua lingua. Può essere visualizzato come "mm/gg/aaaa" o "gg/mm/aaaa"
Il risultato sarà "7/7/2020"
A lungo
| 1 | Formato MsgBox (Ora(), "Tempo lungo") |
Visualizza un tempo lungo come definito nel Pannello di controllo di Windows (Orologio e regione | Regione).
Il risultato sarà "16:11:39"
Tempo medio
| 1 | Formato MsgBox (Ora(), "Tempo medio") |
Visualizza un tempo medio come definito dalle impostazioni internazionali nel Pannello di controllo di Windows. Di solito è impostato come formato di 12 ore utilizzando ore, minuti e secondi e il formato AM/PM.
Il risultato sarà "16:15"
Poco tempo
| 1 | Formato MsgBox (Ora(), "Short Time") |
Visualizza un tempo medio come definito nel Pannello di controllo di Windows (Orologio e regione | Regione). Di solito è impostato come formato 24 ore con ore e minuti
Il risultato sarà '16:18'
Pericoli nell'utilizzo dei formati predefiniti di Excel in date e orari
L'uso dei formati predefiniti per le date e le ore in Excel VBA dipende molto dalle impostazioni nel Pannello di controllo di Windows e anche dall'impostazione delle impostazioni internazionali
Gli utenti possono facilmente modificare queste impostazioni e ciò avrà un effetto su come le date e gli orari vengono visualizzati in Excel
Ad esempio, se sviluppi un'applicazione Excel che utilizza formati predefiniti all'interno del tuo codice VBA, questi potrebbero cambiare completamente se un utente si trova in un paese diverso o utilizza una locale diversa dalla tua. È possibile che le larghezze delle colonne non si adattino alla definizione della data o che su un modulo utente il controllo Active X, ad esempio un controllo casella combinata (elenco a discesa), sia troppo stretto per consentire la corretta visualizzazione delle date e degli orari.
Devi considerare dove si trova geograficamente il pubblico quando sviluppi la tua applicazione Excel
Formati definiti dall'utente per i numeri
Ci sono diversi parametri che puoi usare quando definisci la tua stringa di formato:
| Carattere | Descrizione |
| Stringa nulla | Nessuna formattazione |
| 0 | Segnaposto di cifre. Visualizza una cifra o uno zero. Se c'è una cifra per quella posizione, visualizza la cifra altrimenti visualizza 0. Se ci sono meno cifre di zeri, otterrai zero iniziali o finali. Se ci sono più cifre dopo la virgola rispetto agli zeri, il numero viene arrotondato al numero di posizioni decimali mostrato dagli zeri. Se prima della virgola ci sono più cifre che zero, queste verranno visualizzate normalmente. |
| # | Segnaposto di cifre. Questo visualizza una cifra o niente. Funziona allo stesso modo del segnaposto zero sopra, tranne per il fatto che gli zeri iniziali e finali non vengono visualizzati. Ad esempio, 0.75 verrebbe visualizzato utilizzando zero segnaposto, ma questo sarebbe .75 utilizzando # segnaposto. |
| . Punto decimale. | È consentito solo uno per stringa di formato. Questo carattere dipende dalle impostazioni nel Pannello di controllo di Windows. |
| % | Segnaposto percentuale. Moltiplica il numero per 100 e inserisce il carattere % dove appare nella stringa di formato |
| , (virgola) | Separatore delle migliaia. Viene utilizzato se vengono utilizzati segnaposto 0 o # e la stringa di formato contiene una virgola. Una virgola a sinistra della virgola indica l'arrotondamento alle migliaia più vicine. Per esempio. ##0, Due virgole adiacenti a sinistra del separatore delle migliaia indicano l'arrotondamento al milione più vicino. Per esempio. ##0,, |
| MI- MI+ | Formato scientifico. Questo visualizza il numero in modo esponenziale. |
| : (due punti) | Separatore dell'ora: utilizzato durante la formattazione di un'ora per dividere ore, minuti e secondi. |
| / | Separatore data: viene utilizzato quando si specifica un formato per una data |
| - + £ $ ( ) | Visualizza un carattere letterale.Per visualizzare un carattere diverso da quelli elencati qui, precederlo con una barra rovesciata (\) |
Formati definiti dall'utente per date e orari
Questi caratteri possono essere tutti utilizzati nella stringa di formato durante la formattazione di date e ore:
| Carattere | Significato |
| C | Visualizza la data come ddddd e l'ora come ttttt |
| D | Visualizza il giorno come numero senza zero iniziale |
| dd | Visualizza il giorno come un numero con lo zero iniziale |
| ddd | Visualizza il giorno come abbreviazione (dom - sab) |
| dddd | Visualizza il nome completo del giorno (domenica - sabato) |
| ddddd | Visualizza un numero di serie della data come data completa in base a Data breve nelle impostazioni internazionali del Pannello di controllo di Windows |
| dddddd | Visualizza un numero di serie della data come data completa in base a Data estesa nelle impostazioni internazionali del Pannello di controllo di Windows. |
| w | Visualizza il giorno della settimana come numero (1 = domenica) |
| ww | Visualizza la settimana dell'anno come numero (1-53) |
| m | Visualizza il mese come numero senza zero iniziale |
| mm | Visualizza il mese come numero con zeri iniziali |
| mmm | Visualizza il mese come abbreviazione (gen-dic) |
| mmmm | Visualizza il nome completo del mese (gennaio - dicembre) |
| Q | Visualizza il trimestre dell'anno come numero (1-4) |
| sì | Visualizza il giorno dell'anno come numero (1-366) |
| yy | Visualizza l'anno come numero a due cifre |
| aaaa | Visualizza l'anno come numero a quattro cifre |
| h | Visualizza l'ora come numero senza zero iniziale |
| hh | Visualizza l'ora come un numero con lo zero iniziale |
| n | Visualizza il minuto come numero senza zero iniziale |
| nn | Visualizza i minuti come un numero con lo zero iniziale |
| S | Visualizza il secondo come numero senza zero iniziale |
| ss | Visualizza il secondo come numero con zero iniziale |
| tttt | Visualizza un numero di serie temporale come tempo completo. |
| AM PM | Utilizzare un orologio a 12 ore e visualizzare AM o PM per indicare prima o dopo mezzogiorno. |
| am PM | Usa un orologio di 12 ore e usa am o pm per indicare prima o dopo mezzogiorno |
| A/P | Utilizzare un orologio a 12 ore e utilizzare A o P per indicare prima o dopo mezzogiorno |
| a/p | Utilizzare un orologio a 12 ore e utilizzare aop per indicare prima o dopo mezzogiorno |
