La formattazione dei numeri personalizzati di Excel è l'abbigliamento per i dati nelle celle di Excel. Puoi vestirli come vuoi. Tutto ciò di cui hai bisogno è un po 'di know-how su come funziona il formato numero personalizzato di Excel.
Con la formattazione dei numeri personalizzata in Excel, puoi modificare il modo in cui vengono visualizzati i valori nelle celle, ma allo stesso tempo mantenendo intatto il valore originale.
Ad esempio, posso fare in modo che un valore di data venga visualizzato in diversi formati di data, mentre il valore sottostante (il numero che rappresenta la data) rimarrebbe lo stesso.
In questo tutorial, tratterò tutto ciò che riguarda il formato numerico personalizzato in Excel e ti mostrerò anche alcuni esempi davvero utili che puoi utilizzare nel tuo lavoro quotidiano.
Quindi iniziamo!
Costrutto formato numero personalizzato Excel
Prima di mostrarti la sua bellezza, lascia che ti spieghi brevemente come funziona la formattazione dei numeri personalizzati in Excel.
In base alla progettazione, Excel può accettare i seguenti quattro tipi di dati in una cella:
- Un numero positivo
- un numero negativo
- 0
- Stringhe di testo (questo può includere stringhe di testo puro e stringhe alfanumeriche)
Per qualsiasi cella in un foglio di lavoro in Excel, puoi specificare come deve essere mostrato ciascuno di questi tipi di dati nella cella.
E sotto c'è la sintassi in cui puoi specificare questo formato.
;;;
Nota che tutti questi sono separati da punto e virgola (;).
Tutto ciò che inserisci in una cella in Excel rientrerebbe in una di queste quattro categorie e quindi è possibile specificare un formato personalizzato.
Se citi solo:
- Un formato: Viene applicato a tutte e quattro le sezioni. Ad esempio, se scrivi solo Generale, verrà applicato a tutte e quattro le sezioni.
- Due formati: Il primo viene applicato ai numeri positivi e agli zeri, mentre il secondo viene applicato ai numeri negativi. Il formato del testo per impostazione predefinita diventa Generale.
- Tre formati: il primo viene applicato ai numeri positivi, il secondo viene applicato ai numeri negativi, il terzo viene applicato a zero e il testo scompare poiché non viene specificato nulla per il testo.
Se vuoi saperne di più sulla formattazione dei numeri personalizzati, ti consiglio vivamente la sezione Guida di Office.
Come funziona e dove inserire questi formati sono trattati più avanti in questo tutorial. Per ora, tieni presente che questa è la sintassi per il formato per qualsiasi cellaCome funziona il formato numero personalizzato in Excel
Quindi apri una nuova cartella di lavoro di Excel, per impostazione predefinita tutte le celle in tutti i fogli di lavoro in quella cartella di lavoro hanno il formato Generale dove:
- Numeri positivi sono mostrati così come sono (allineati a sinistra)
- Numeri negativi sono indicati con un segno meno (e allineati a sinistra)
- Zero è mostrato come 0 (e allineato a sinistra)
- Testo è mostrato così com'è (allineato a destra)
E se inserisci una data, questa viene mostrata in base alle tue impostazioni internazionali (nel formato GG-MM-AAAA o MM-GG-AAAA).
Quindi, per impostazione predefinita, la formattazione del numero della cella è:
Generale;Generale;Generale;Generale
Dove ogni tipo di dati è formattato come Generale (e segui le regole che ho menzionato sopra, in base al fatto che sia un numero positivo, un numero negativo, 0 o testo)
Ora l'impostazione predefinita va bene, ma come si cambia il formato. Ad esempio, se voglio che i miei numeri negativi vengano visualizzati in rosso o voglio che le mie date vengano visualizzate in un formato specifico, come posso farlo.
Utilizzo del menu a discesa Formato nella barra multifunzione
Puoi trovare alcuni dei formati numerici comunemente usati nella scheda Home all'interno del gruppo Numero.
È un menu a discesa che mostra molti dei formati comunemente usati che puoi applicare a una cella semplicemente selezionando la cella e quindi selezionando il formato da questo elenco.
Ad esempio, se desideri convertire i numeri in percentuali o convertire le date in un formato di data breve o in un formato di data lungo, puoi farlo facilmente utilizzando le opzioni nel menu a discesa.
Utilizzo della finestra di dialogo Formato celle
Finestra di dialogo Formato celle in cui si ottiene la massima flessibilità quando si tratta di applicare i formati a una cella in Excel.
Ha già molti formati predefiniti che puoi usare, o se vuoi creare il tuo formato numerico personalizzato, puoi farlo anche tu.
Ecco come aprire la finestra di dialogo Formato celle;
- Fare clic sulla scheda Home
- Nel gruppo Numero, fai clic sull'icona di avvio della finestra di dialogo (la piccola freccia inclinata in basso a destra del gruppo).
Si aprirà la finestra di dialogo Formato celle. Al suo interno, troverai tutti i formati nella scheda Numero (insieme all'opzione per creare formati di cella personalizzati)
Puoi anche utilizzare la seguente scorciatoia da tastiera per aprire la finestra di dialogo Formato celle:
CONTROL + 1 (tutto il tasto Ctrl e poi premere il tasto 1).
Se stai usando il Mac, puoi usare CMD + 1
Ora facciamo un paio di esempi pratici in cui creeremo i nostri formati numerici personalizzati.
Esempi di utilizzo della formattazione dei numeri personalizzati in Excel
Ora facciamo un paio di esempi pratici in cui creeremo i nostri formati numerici personalizzati.
Nascondi un valore di cella (o nascondi tutti i valori in un intervallo di celle)
A volte, potrebbe essere necessario nascondere il contenuto della cella (senza nascondere la riga o la colonna che contiene la cella).
Un caso pratico di questo è quando si crea un dashboard di Excel e ci sono un paio di valori che si utilizzano nel dashboard ma non si desidera che gli utenti lo vedano. quindi puoi semplicemente nascondere questi valori
Supponiamo di avere un set di dati come mostrato di seguito e di voler nascondere tutti i valori numerici:
Ecco come farlo utilizzando la formattazione personalizzata in Excel:
- Seleziona la cella o l'intervallo di celle per cui vuoi nascondere il contenuto della cella
- Apri la finestra di dialogo Formato celle (scorciatoia da tastiera Ctrl + 1)
- Nella scheda Numero, fai clic su Personalizza
- Immettere il seguente formato nel campo Tipo:
;;;
Come funziona?
Il formato per quattro diversi tipi di tipi di dati è diviso da punti e virgola nel seguente formato:
;;;
In questa tecnica, abbiamo rimosso il formato e specificato solo il punto e virgola, che indica che non vogliamo alcuna formattazione per le celle. Questa è un'indicazione per Excel per nascondere qualsiasi contenuto presente nella cella.
Nota che mentre questo nasconde il contenuto della cella, è ancora lì. quindi se hai numeri che hai nascosto, puoi comunque usarli nei calcoli (il che lo rende un ottimo trucco per dashboard e report di Excel).
Visualizza i numeri negativi in colore rosso
Per impostazione predefinita, i numeri negativi vengono visualizzati con un segno meno (o tra parentesi in alcune impostazioni internazionali).
Puoi utilizzare la formattazione dei numeri personalizzata per cambiare il colore dei numeri negativi e farli apparire in rosso.
Supponiamo di avere un set di dati come mostrato di seguito e di voler evidenziare tutti i valori negativi in rosso.
Di seguito sono riportati i passaggi per eseguire questa operazione:
- Seleziona l'intervallo di celle in cui vuoi mostrare i numeri negativi in rosso
- Apri la finestra di dialogo Formato celle (scorciatoia da tastiera Ctrl + 1)
- Nella scheda Numero, fai clic su Personalizza
- Immettere il seguente formato nel campo Tipo:
Generale;[Rosso]-Generale
Il formato sopra renderà le tue celle con valori negativi visualizzate in colore rosso.
Come funziona?
Quando si specificano solo due formati come formato numerico personalizzato, il primo viene considerato per i numeri positivi e 0 e il secondo viene considerato per i numeri negativi (il valore successivo per impostazione predefinita diventa il formato generale).
Nel formato sopra che abbiamo usato, abbiamo specificato il colore che vogliamo che quel formato occupi tra parentesi quadre.
Quindi, mentre i numeri negativi assumono il formato generale, sono mostrati in colore rosso con un segno meno.
Nel caso in cui desideri che i numeri negativi si fermino in rosso tra parentesi (invece del segno meno), puoi utilizzare il formato seguente:
Generale;[Rosso](Generale)
Aggiungi testo a Numbers (ad esempio Milioni/Miliardi)
Una cosa sorprendente della formattazione dei numeri personalizzata è che puoi aggiungere un prefisso o un suffisso a un numero, mantenendo il numero originale nella cella.
Ad esempio, posso visualizzare "100" come "$ 100 milioni", pur mantenendo il valore originale nella cella in modo da poterlo utilizzare nei calcoli.
Supponiamo di avere un set di dati come mostrato di seguito e di voler mostrare questi numeri con il simbolo del dollaro e la parola milione davanti.
Di seguito sono riportati i passaggi per eseguire questa operazione:
- Seleziona il set di dati in cui desideri aggiungere il testo ai numeri
- Apri la finestra di dialogo Formato celle (scorciatoia da tastiera Ctrl + 1)
- Nella scheda Numero, fai clic su Personalizza
- Immettere il seguente formato nel campo Tipo:
$Generale "milioni"
Tieni presente che questo formato numerico personalizzato influirà solo sui numeri. Se inserisci del testo nella stessa cella, apparirà così com'è.
Come funziona?
In questo esempio, al formato generale, ho aggiunto la parola milione tra virgolette. tutto ciò che aggiungo tra virgolette al formato viene mostrato così com'è nella cella.
Ho anche aggiunto il simbolo del dollaro prima del formato generale in modo che tutti i miei numeri mostrino anche il simbolo del dollaro prima del numero.
Nota: se ti stai chiedendo perché il simbolo del dollaro non è tra virgolette mentre la parola Million è tra virgolette, è perché Excel riconosce il dollaro come un simbolo che viene spesso utilizzato nella formattazione dei numeri personalizzati e non richiede di te per aggiungere virgolette doppie. Puoi andare avanti e aggiungere virgolette doppie al simbolo del dollaro, ma quando chiudi la finestra di dialogo del formato delle celle e la riapri, noterai che Excel lo ha rimosso
Travestimento di numeri e testo
Con la formattazione dei numeri personalizzata, puoi valutare un massimo di due condizioni e, in base a queste condizioni, puoi applicare un formato alla cella.
Ad esempio, supponiamo che tu abbia un elenco di punteggi per alcuni studenti, vuoi mostrare il testo Pass o Fail in base al punteggio (dove qualsiasi punteggio inferiore a 35 dovrebbe mostrare "Fail", altrimenti dovrebbe mostrare "Pass".
Di seguito sono riportati i passaggi per mostrare i punteggi come Pass o Fail invece del valore utilizzando il formato numerico personalizzato:
- Seleziona il set di dati in cui hai i punteggi
- Apri la finestra di dialogo Formato celle (scorciatoia da tastiera Ctrl + 1)
- Nella scheda Numero, fai clic su Personalizza
- Immettere il seguente formato nel campo Tipo:
[<35]"Fallito";"Superato"
Di seguito è riportato come apparirebbero i tuoi dati dopo aver applicato il formato sopra.
Nota che non cambia il valore nelle celle. La cella continua ad avere i punteggi in valori numerici mentre viene visualizzato "Passato" o "Fallito" a seconda del valore della cella.
Come funziona?
In questo esempio, la condizione deve essere specificata tra parentesi quadre. la formattazione dei numeri personalizzata valuta quindi il valore nella cella e applica il formato di conseguenza.
Quindi, se il valore nella cella è inferiore a 35, il numero è nascosto e invece viene mostrato il testo "Fallito", aggiungendo tutti gli altri casi, viene mostrato "Passato".
Allo stesso modo, se desideri convertire un intervallo di celle con 0 e 1 in TRUE e FALSE, puoi utilizzare il formato seguente:
[=0]"FALSO";[=1]"VERO"
Ricorda che puoi utilizzare solo due condizioni nella formattazione dei numeri personalizzata. Se provi a utilizzare più di due condizioni, il tuo messaggio ti farà sapere che non può accettare quel formato.
Nascondi testo ma visualizza numeri
A volte, quando scarichi i tuoi dati dai database poiché sono vuoti, vengono riempiti con valori come Non disponibile o NA o -
Con la formattazione dei numeri personalizzata, puoi nascondere tutti i valori di testo mantenendo i numeri così come sono. questo assicura anche che i tuoi dati originali siano intatti (dato che non stai cancellando il valore del testo, lo stai semplicemente nascondendo)
Supponiamo di avere un set di dati come mostrato di seguito in cui desideri nascondere tutti i valori di testo:
Di seguito sono riportati i passaggi per eseguire questa operazione:
- Seleziona il set di dati in cui hai i dati
- Apri la finestra di dialogo Formato celle (scorciatoia da tastiera Ctrl + 1)
- Nella scheda Numero, fai clic su Personalizza
- Immettere il seguente formato nel campo Tipo:
Generale; -Generale; Generale;
Nota che ho usato il formato Generale per i numeri. Puoi qualsiasi formato desideri (come 0, 0.#, #0.0%). Si noti inoltre che è presente un segno negativo (-) prima del secondo formato Generale, poiché rappresenta il formato per i numeri negativi.
Come funziona?
In questo esempio, poiché volevamo nascondere tutti i valori di testo, semplicemente non abbiamo specificato il formato per esso.
E poiché abbiamo specificato il formato per gli altri tre tipi di dati (numero positivo, numero negativo e zero), Excel ha capito che abbiamo lasciato intenzionalmente vuoto il formato per il valore del testo e non dovrebbe mostrare nulla se una cella ha un testo corda.
Visualizza i numeri come percentuali (%)
Con la formattazione dei numeri personalizzata, puoi anche modificare i numeri e farli apparire in percentuale.
Supponiamo di avere un set di dati come mostrato di seguito e di voler convertire questi numeri per mostrare il valore percentuale equivalente:
Di seguito sono riportati i passaggi per eseguire questa operazione:
- Seleziona le celle in cui hai i numeri (che vuoi convertire in percentuale)
- Apri la finestra di dialogo Formato celle (scorciatoia da tastiera Ctrl + 1)
- Nella scheda Numero, fai clic su Personalizza
- Immettere il seguente formato nel campo Tipo:
0%
Questo cambierà i numeri nelle loro percentuali. Ad esempio, cambia da 0,11 a 11%.
Se tutto ciò che vuoi fare è convertire un numero nel suo valore percentuale equivalente, un modo migliore per farlo sarebbe utilizzare l'opzione percentuale nella finestra di dialogo Formato celle.
Laddove la formattazione personalizzata può essere utile è quando si desidera un maggiore controllo su come si desidera visualizzare il valore percentuale.
Ad esempio, se vuoi mostrare del testo aggiuntivo con la percentuale, tutto quello che vuoi mostrare le percentuali negative in colore rosso, allora è meglio usare la formattazione personalizzata
Visualizza i numeri in un'unità diversa (milioni come miliardi o grammi come chilogrammi)
Se lavori con numeri grandi, potresti voler mostrarli in un'unità diversa. ad esempio, se hai i dati di vendita di alcune aziende, potresti voler mostrare le vendite in milioni o miliardi (pur mantenendo il valore di vendita originale nelle celle)
Per fortuna, puoi farlo facilmente con la formattazione dei numeri personalizzata.
Supponiamo di avere un set di dati come mostrato di seguito e di voler mostrare questi valori di vendita in milioni.
Di seguito sono riportati i passaggi per eseguire questa operazione:
- Seleziona le celle in cui hai i numeri
- Apri la finestra di dialogo Formato celle (scorciatoia da tastiera Ctrl + 1)
- Nella scheda Numero, fai clic su Personalizza
- Immettere il seguente formato nel campo Tipo:
0.0,,
Quanto sopra ti darebbe il risultato come mostrato di seguito (valori in milioni in una cifra dopo il decimale):
Come funziona?
Nel formato sopra, uno zero prima del decimale indica che Excel dovrebbe mostrare l'intero numero (esattamente come è nella cella) e uno 0 dopo il decimale indica che dovrebbe mostrare solo una cifra significativa dopo la virgola.
Quindi 0.0 sarebbe sempre un numero con una cifra dopo la virgola.
Quando aggiungi una virgola dopo lo 0,0, verranno eliminate le ultime tre cifre del numero. Ad esempio, 123456 diventerebbe 123,4 (le ultime tre cifre vengono rimosse e poiché deve mostrare 1 cifra dopo la virgola, mostra 4)
Se ci pensi, è come dividere il numero per 1000. Quindi, se vuoi mostrare un numero nell'unità "milioni", devi aggiungere 2 virgole, in modo da eliminare le ultime 6 cifre.
E se vuoi anche mostrare la parola milione o l'alfabeto M dopo il numero, usa il formato qui sotto
0.0,, "M"
