In questo articolo, impareremo diversi modi per aggiungere zeri (0s) davanti in Excel.
Scenario:
Aggiunta di zero davanti al numero in Excel. Excel predefinito non accetta zeri davanti al numero nella cella. Hai mai provato a inserire alcuni dati come 000123 in Excel? Probabilmente noterai rapidamente che Excel rimuoverà automaticamente gli zeri iniziali dal numero di input. Questo può essere davvero fastidioso se vuoi quegli zeri iniziali nei dati e non sai come fare in modo che Excel li conservi. Fortunatamente ci sono diversi modi per riempire i tuoi numeri con zeri all'inizio. In questo articolo, spiegherò diversi modi per aggiungere o mantenere gli zeri iniziali ai tuoi numeri.
Diverse funzioni per aggiungere zero (0) davanti
Come sappiamo, la funzione TESTO è la funzione più utilizzata per aggiungere 0 davanti, ma qui spiegherò altri modi per aggiungere zeri (0) ai numeri.
- Funzione TESTO
- Funzione DESTRA
- Funzione BASE
- Aggiungi 0 nei valori della tabella pivot
Formatta come testo
Una delle soluzioni è convertire il formato del numero in testo perché se inseriamo 00xyz, Excel considera questo valore come testo e mantiene il suo 0 davanti. Esistono diversi modi per convertire una cella numerica in formato testo.
- Utilizzo del formato personalizzato
- Utilizzo della funzione TESTO
- Utilizzo dell'apostrofo ( ' ) all'inizio del valore durante l'input
Formato personalizzato
Comprendiamo questi metodi uno per uno. L'utilizzo di un formato personalizzato per modificare il formato è il modo più semplice per modificare i numeri già esistenti nell'intervallo. Basta selezionare l'intervallo e utilizzare la scorciatoia Ctrl + 1 o selezionare Altro formato numeri dall'elenco a discesa nella scheda Home che mostra Generale.
Cambia il formato da Generale a Testo.
- Seleziona l'intervallo di celle in cui desideri inserire gli zeri iniziali.
- Vai alla scheda Home.
- Nella sezione Numeri, fai clic sulla selezione del menu a discesa Formato.
- Scegli Testo dalle opzioni di formato.
Ora, se provi a inserire numeri con zeri iniziali, non scompariranno perché vengono inseriti come valori di testo anziché come numeri. Gli utenti di Excel possono aggiungere una formattazione personalizzata per formattare i numeri con zeri iniziali.
Tuttavia, sembreranno solo avere zeri iniziali. I dati sottostanti non verranno modificati in testo con gli zeri aggiunti.
Aggiungi un formato personalizzato per mostrare gli zeri iniziali.
- Seleziona l'intervallo di celle a cui desideri aggiungere gli zeri iniziali e apri la finestra di dialogo Formato celle.
- Fare clic con il tasto destro e scegliere Formato celle.
- Usa la scorciatoia da tastiera Ctrl + 1.
- Vai alla scheda Numero.
- Seleziona Personalizzato dalle opzioni della categoria.
- Aggiungi un nuovo formato personalizzato nell'input Tipo. Se vuoi che il numero totale di cifre, inclusi gli zeri iniziali, sia 6, aggiungi 000000 come formato personalizzato.
- Premi il pulsante OK.
Funzione TESTO
La funzione TESTO ti consente di applicare una formattazione personalizzata a qualsiasi dato numerico già presente nel tuo foglio di calcolo.
= TESTO ( Valore, Formato)
- Valore - Questo è il valore che vuoi convertire in testo e applicare la formattazione.
- Formato - Questa è la formattazione da applicare.
= TESTO ( B3, "000000" )
Se desideri aggiungere zeri a un numero nella cella B3 in modo che il numero totale di cifre sia 6, puoi utilizzare la formula sopra.
Utilizzo dell'apostrofo ( ' ) in start
Puoi forzare Excel a inserire un numero come testo utilizzando un apostrofo iniziale.
Ciò significa che sarai in grado di tenere quegli zeri in primo piano mentre inserisci i tuoi dati.
Questo metodo è facile e veloce durante l'inserimento dei dati. Basta digitare un carattere ' prima di qualsiasi numero. Questo dirà a Excel che i dati devono essere testo e non un numero.
Quando premi Invio, gli zeri iniziali rimarranno visibili nel foglio di lavoro. Il ' non sarà visibile nel foglio di lavoro, ma è ancora lì e può essere visto nella barra della formula quando il cursore della cella attiva è sulla cella.
Funzione DESTRA
Un altro modo per mettere gli zeri davanti a una formula è usare la funzione DESTRA. Puoi concatenare una stringa di zeri al numero e poi tagliare gli extra usando la funzione DESTRA.
La funzione RIGHT estrarrà gli N caratteri più a destra da un valore di testo.
= DESTRA ( Testo, [Numero])
- Testo - Questo è il testo da cui vuoi estrarre i caratteri.
- Numero (Opzionale)- Questo è il numero di caratteri da estrarre dal testo. Se questo argomento non viene inserito, verrà estratto solo il primo carattere.
= DESTRA ("000000" & B3, 6 )
La formula sopra concatenerà diversi zeri all'inizio di un numero nella cella B3, quindi restituirà i caratteri 6 più a destra risultando in alcuni zeri iniziali.
Funzione BASE
Noterai che questo articolo descrive 9 modi per aggiungere zeri iniziali, ma il mio video di YouTube mostra solo 8 modi.
Questo perché non sapevo che avresti potuto usare la funzione BASE per aggiungere zeri iniziali fino a quando qualcuno non lo ha menzionato nei commenti del video.
La funzione BASE consente di convertire un numero in una rappresentazione testuale con una data base.
I numeri che usi di solito sono in base 10, ma potresti usare questa funzione per convertire un numero in base 10 in una rappresentazione in base 2 (binaria).
= BASE ( Numero, Base, [Lunghezza min])
- Numero - Questo è il numero che vuoi convertire in un valore di testo in un'altra base.
- Base - Questa è la base in cui vuoi convertire il valore.
- Lunghezza minima (Opzionale) - Questa è la lunghezza minima dei caratteri del valore di testo convertito.
Usa la formula
| =BASE(B3,10,6) |
La formula sopra convertirà un numero nella cella B3 in base 10 (è già un numero in base 10 ma questo lo manterrà come base 10) e lo convertirà in un valore di testo con almeno 6 caratteri. Se il valore convertito è inferiore a 6 caratteri, riempirà il valore di zeri per ottenere un minimo di 6 caratteri.
Sicuramente un nuovo fantastico utilizzo per una funzione che altrimenti non userei mai.
Aggiungi zero (0) solo davanti ai numeri non sui valori di testo
Qui considereremo lo scenario che copre sia i valori di testo che quelli numerici nello stesso elenco. Ora Excel considera ogni cella diversa in termini di formato. Ora come far sapere a Excel che se la cella ha un valore numerico, aggiungi 0 davanti oppure se la cella ha testo o qualsiasi altro valore, lascialo così com'è.
Per questo useremo una combinazione di due funzioni SE e la funzione ISNUMBER. Qui abbiamo alcuni valori da provare.
La formula generica continua come
=SE(VAL.NUMERO(rif_cella),"0"&rif_cella, rif_cella)
Qui
cell_ref: riferimento di cella della cella corrispondente
Usa la formula nella cella
| =SE(VAL.NUMERO(F3),"0"&F3, F3) |
Spiegazione: La funzione ISNUMBER controlla la cella per il numero. La funzione SE controlla se la cella ha un numero, quindi aggiunge zero davanti al numero, altrimenti restituisce solo il valore della cella.
Come puoi vedere nell'immagine sopra, lo zero (0) viene aggiunto davanti al numero. Verifichiamo se questa funzione funziona su altri valori. Copia la formula in altre celle, seleziona le celle prendendo la prima cella in cui è già applicata la formula, usa il tasto di scelta rapida Ctrl+D
riempirà il valore di zeri per ottenere un minimo di uno zero davanti ai caratteri. Sicuramente un nuovo fantastico utilizzo per una funzione che altrimenti non userei mai.
Colonna calcolata PowerPivot
C'è un'altra opzione per aggiungere zeri nella tabella pivot. Puoi aggiungerli in una colonna calcolata in PowerPivot. In questo modo puoi utilizzare la nuova colonna nell'area Filtro, Righe o Colonna di una tabella pivot.
= FORMATO ( Numeri[Numero], "000000" )
Nel componente aggiuntivo PowerPivot, puoi aggiungere una nuova colonna e usare la funzione FORMAT per creare zeri iniziali nella colonna con una formula come sopra. Una colonna calcolata calcola un valore per ogni riga, quindi non è necessario racchiudere la funzione all'interno di una cella. Ora puoi utilizzare questa nuova colonna all'interno dell'area Righe di una tabella pivot proprio come qualsiasi altra colonna di dati.
Riepilogo
Può essere frustrante vedere scomparire tutti i tuoi zeri quando non sappiamo perché sta succedendo o come prevenirlo. Come puoi vedere, ci sono molti modi per assicurarti che tutti quegli zeri rimangano nei tuoi dati. Qualunque sia la tua esigenza, c'è sicuramente una buona soluzione per te.
Ecco tutte le note di osservazione utilizzando la formula in Excel
Appunti :
- La formula può essere utilizzata sia per i testi che per i numeri.
- Il testo trova e sostituisci dovrebbe essere ordinato in una tabella. Poiché utilizziamo gli indici per trovare e sostituire, assicurati che il testo "trova" abbia lo stesso indice del testo "sostituisci". È meglio avere entrambe le liste fianco a fianco come nel secondo esempio.
- La formula generica ha due funzioni SOSTITUISCI. Avrai bisogno di tante funzioni sostitutive quante sono le sostituzioni da eseguire.
- i caratteri jolly aiutano nell'estrazione del valore dalle sottostringhe.
- La funzione supporta operatori logici come , , = ma questi vengono utilizzati utilizzando il segno delle virgolette ( " ) con argomenti. Ma se stai usando il riferimento di cella per i criteri, le virgolette possono essere ignorate.
- L'argomento dei criteri dovrebbe utilizzare il segno di virgolette (") per i testi (come "Gillen" ) oi numeri (come ">50" ).
Spero che questo articolo su Diversi modi per aggiungere zeri (0s) davanti in Excel sia esplicativo. Trova altri articoli sul calcolo dei valori e le relative formule di Excel qui. Se ti è piaciuto il nostro blog, condividilo con i tuoi amici su Facebook. E puoi anche seguirci su Twitter e Facebook. Ci piacerebbe avere tue notizie, facci sapere come possiamo migliorare, completare o innovare il nostro lavoro e renderlo migliore per te. Scrivici al sito di posta elettronica.
Excel SOSTITUISCI vs funzione SOSTITUISCI: Le funzioni SOSTITUISCI e SOSTITUISCI sono le funzioni più fraintese. Per trovare e sostituire un dato testo utilizziamo la funzione SOSTITUISCI. Dove REPLACE viene utilizzato per sostituire un numero di caratteri nella stringa.
Sostituisci il testo dalla fine di una stringa a partire dalla posizione variabile: Per sostituire il testo dalla fine della stringa, usiamo la funzione REPLACE. La funzione REPLACE usa la posizione del testo nella stringa da sostituire.
Come verificare se una stringa contiene uno dei tanti testi in Excel: Per verificare se una stringa contiene più testi, usiamo questa formula. Usiamo la funzione SOMMA per sommare tutte le corrispondenze e quindi eseguiamo una logica per verificare se la stringa contiene una delle stringhe multiple.
Conta le celle che contengono testo specifico: Una semplice funzione CONTA.SE farà la magia. Per contare il numero di celle multiple che contengono una data stringa usiamo l'operatore jolly con la funzione CONTA.SE.
Articoli popolari:
50 scorciatoie di Excel per aumentare la produttività : velocizza le tue attività in Excel. Queste scorciatoie ti aiuteranno ad aumentare l'efficienza del tuo lavoro in Excel.
Come utilizzare la funzione CERCA.VERT in Excel : questa è una delle funzioni più utilizzate e popolari di excel che viene utilizzata per cercare il valore da diversi intervalli e fogli.
Come usare la funzione SE in Excel : l'istruzione IF in Excel controlla la condizione e restituisce un valore specifico se la condizione è VERA o restituisce un altro valore specifico se FALSO.
Come utilizzare la funzione SOMMA.SE in Excel : Questa è un'altra funzione essenziale della dashboard. Questo ti aiuta a riassumere i valori su condizioni specifiche.
Come utilizzare la funzione CONTA.SE in Excel : Conta i valori con le condizioni utilizzando questa straordinaria funzione. Non è necessario filtrare i dati per contare valori specifici. La funzione Countif è essenziale per preparare la tua dashboard.