Hai mai provato a inserire alcuni dati come 000123 in Excel?
Probabilmente noterai rapidamente che Excel rimuoverà automaticamente gli zeri iniziali da qualsiasi numero.
Questo può essere davvero fastidioso se vuoi quegli zeri iniziali nei tuoi dati e non sai come fare in modo che Excel li mantenga.
Fortunatamente ci sono alcuni modi per tamponare i tuoi numeri con zeri all’inizio.
In questo post, ti mostrerò 9 modi per aggiungere o mantenere quegli zeri iniziali ai tuoi numeri.,
Sommario
Formato come testo
C’è un’opzione per cambiare il formato di un intervallo in testo.
In questo modo verranno trattati tutti i dati immessi come valori di testo, anche se sono numeri. Ciò farà sì che Excel mantenga gli zeri iniziali nei tuoi numeri.
Cambia il formato da Generale a Testo.
- Seleziona l’intervallo di celle in cui vuoi inserire gli zeri iniziali.
- Vai alla scheda Home.
- Nella sezione Numeri fare clic sulla selezione a discesa Formato.
- Scegli il testo dalle opzioni di formato.,
Ora se si tenta di inserire numeri con zeri iniziali, non scompariranno perché vengono inseriti come valori di testo anziché numeri.
Formato personalizzato
È possibile aggiungere una formattazione personalizzata per formattare i numeri con zeri iniziali.
Sembreranno avere solo zeri iniziali. I dati sottostanti non verranno modificati in testo con gli zeri aggiunti.
Aggiungi un formato personalizzato per mostrare gli zeri iniziali.
- Selezionare l’intervallo di celle a cui si desidera aggiungere zeri iniziali e aprire la finestra di dialogo Formatta celle.,
- Fare clic destro e scegliere Formato celle.
- Usa la scorciatoia da tastiera Ctrl + 1.
- Vai alla scheda Numero.
- Selezionare Personalizzato dalle opzioni di categoria.
- Aggiungi un nuovo formato personalizzato nell’input del tipo. Se si desidera che il numero totale di cifre inclusi eventuali zeri iniziali sia 6, aggiungere 000000 come formato personalizzato.
- Premere il pulsante OK.
Dopo aver applicato il formato personalizzato si noterà che i dati non sono stati effettivamente modificati.,
Selezionando una cella con formattazione verrà comunque visualizzato il numero originale nella barra della formula. Appaiono solo nel foglio di lavoro con il formato zero iniziale.
Questo significa anche che se copi e incolla i dati come valori, perderai gli zeri iniziali.
Apostrofo iniziale
È possibile forzare Excel a inserire un numero come testo utilizzando un apostrofo iniziale.
Ciò significa che sarai in grado di mantenere quegli zeri davanti mentre stai inserendo i tuoi dati.
Questo metodo è semplice e veloce durante l’immissione 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 si preme Invio, gli zeri iniziali rimarranno visibili nel foglio di lavoro. ' non sarà visibile nel foglio di lavoro, ma è ancora lì e può essere visto nella barra della formula quando il cursore della cella attiva si trova sulla cella.
Funzione TESTO
La funzione TESTO consente di applicare una formattazione personalizzata a qualsiasi numero di dati già nel foglio di calcolo.,
= TEXT ( Value, Format)- Valore-Questo è il valore che si desidera convertire in testo e applicare la formattazione.
- Format-Questa è la formattazione da applicare.
= TEXT ( B3, "000000" )Se si desidera aggiungere zeri a un numero nella cella B3 in modo che il numero totale di cifre sia 6, è possibile utilizzare la formula precedente.
Funzione DESTRA
Un altro modo per ottenere i tuoi zeri davanti con una formula sta usando la funzione GIUSTA. È possibile concatenare una stringa di zeri al numero e quindi tagliare gli extra usando la funzione GIUSTA.,
La funzione GIUSTA estrarrà i N caratteri più giusti da un valore di testo.
= RIGHT ( Text, )- Text – Questo è il testo da cui si desidera estrarre i caratteri.
- Number (Opzionale)- Questo è il numero di caratteri da estrarre dal testo. Se questo argomento non viene inserito, verrà estratto solo il primo carattere.,
= RIGHT ( "000000" & B3, 6 )La formula precedente concatenerà diversi zeri all’inizio di un numero nella cella B3, quindi restituirà i 6 caratteri giusti con conseguente 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 potessi usare la funzione di BASE per aggiungere zeri iniziali finché qualcuno non lo ha menzionato nei commenti del video.,
Le funzioni di BASE consentono di convertire un numero in una rappresentazione di testo con una data base.
I numeri che usi di solito sono base 10, ma puoi usare questa funzione per convertire un numero di base 10 in una rappresentazione base 2 (binaria).
= BASE ( Number, Base, )- Number – Questo è il numero che si desidera convertire in un valore di testo in un’altra base.
- Base-Questa è la base in cui si desidera convertire il valore.
- MinLength (Opzionale) – Questa è la lunghezza minima dei caratteri del valore di testo convertito.,
= BASE ( B3, 10, 6)La formula precedente convertirà un numero nella cella B3 in base 10 (è già un numero di 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 con zeri per ottenere un minimo di 6 caratteri.
Sicuramente un nuovo uso fresco per una funzione che altrimenti non uso mai.
Query di potenza e testo.,La funzione PadStart
Power query riguarda la trasformazione dei dati, quindi non sorprende che tu possa trasformare i tuoi numeri per aggiungere zeri iniziali in power query.
Sfortunatamente, non è un comando che si trova nella barra multifunzione e richiederà l’utilizzo di una funzione di query di potenza in una colonna personalizzata.
= Text.PadStart(,6,"0")Dopo aver importato i dati in power query, è possibile aggiungere una nuova colonna con la formula di cui sopra per creare zeri iniziali.
- Vai alla scheda Aggiungi colonna.
- Selezionare Colonna personalizzata dalla barra multifunzione.,
- Crea un nuovo nome per la colonna personalizzata e aggiungi la formula precedente nella casella Formula colonna personalizzata.
- Premere il pulsante OK.
Questo creerà una nuova colonna in cui ogni riga ha almeno 6 caratteri riempiti da zeri, se necessario.
Tabella Pivot e Misura DAX
Se si desidera aggiungere zeri iniziali ai numeri nella tabella pivot, è possibile farlo utilizzando il modello di dati e alcune misure DAX.
= CONCATENATEX ( Numbers, FORMAT ( Numbers, "000000" ), ", " )È possibile aggiungere la formula di misura di cui sopra per visualizzare i numeri in una tabella pivot con zeri iniziali.,
Si noti che la funzione FORMAT DAX è esattamente come la funzione TEXT di Excel, ma le misure DAX devono aggregarsi a un singolo valore, quindi è necessario avvolgerlo all’interno di una funzione CONCATENATEX.
Quando si aggiunge la nuova misura nell’area Valori di una tabella pivot, si otterrà un elenco separato da virgole di tutti i numeri con gli zeri iniziali aggiunti.
Power Pivot Colonna calcolata
C’è un’altra opzione per aggiungere zeri in voi tabella pivot. È possibile aggiungerli in una colonna calcolata in Power Pivot.,
In questo modo è possibile utilizzare la nuova colonna nell’area Filtro, Righe o Colonna di una tabella pivot.
= FORMAT ( Numbers, "000000" )Nel componente aggiuntivo Power Pivot, è possibile aggiungere una nuova colonna e utilizzare 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 avvolgere la funzione all’interno di una funzione CONCATENATEX come nella misura DAX.,
Ora puoi usare questa nuova colonna all’interno dell’area delle righe di una tabella pivot proprio come qualsiasi altra colonna di dati!
Conclusioni
Può essere frustrante vedere tutti i tuoi zeri scomparire quando non sai perché sta accadendo o come prevenirlo.
Come puoi vedere, ci sono molti modi per assicurarti che tutti quegli zeri rimangano nei tuoi dati.
Qualunque sia il vostro requisito, c ‘ è sicuramente una buona soluzione per voi.
Hai un modo preferito che ho perso?