Există situații în care trebuie să adăugați zero-uri inițiale la un set de date în Excel. Acesta ar putea fi cazul dacă mențineți înregistrări în Excel, cum ar fi ID-urile angajaților sau ID-urile tranzacției.
De exemplu, vă recomandăm să obțineți un aspect constant în setul de date, așa cum se arată mai jos:
În acest tutorial, veți învăța diferite moduri de a adăuga zero-uri în Excel:
- Conversia formatului în text
- Utilizarea formatării numerelor personalizate
- Utilizarea funcției text
- Utilizarea funcțiilor REPT / LEN
- Folosind VBA
Fiecare dintre aceste metode are unele merite și dezavantaje (acoperite în fiecare secțiune).
Să vedem cum funcționează fiecare dintre acestea.
Adăugați zero zero prin convertirea formatului în text
Când utilizați: Când aveți un mic set de date numerice și intenționați să faceți această editare manual.
Să presupunem că aveți ID-urile angajaților din departamentul de marketing, așa cum se arată mai jos și doriți să faceți ca aceste ID-uri să pară coerente adăugând zero-uri.
Așadar, încercați să schimbați ID-ul introducând zero-uri inițiale (00001 în loc de 1).
Dar spre uimirea dvs., Excel îl convertește înapoi la 1.
Acest lucru se întâmplă deoarece Excel înțelege că 00001 și 1 sunt aceleași numere și ar trebui să respecte aceleași reguli de afișare.
Acum, oricât de frustrant ar fi pentru dvs., Excel are motivele sale.
Deci, pentru a face munca fără a îndoi regulile Excel, va trebui să profitați de faptul că această regulă nu se aplică formatării textului.
Iată deci ce trebuie să faceți:
- Selectați celulele în care doriți să adăugați manual zero zero.
- Accesați Acasă → Grup de numere și selectați Text din meniul derulant.
Asta e!
Acum, când introduceți zero-urile inițiale manual, Excel se va conforma cu ușurință.
Atenție: Când convertiți formatul în text, unele funcții Excel nu vor funcționa corect. De exemplu, funcția SUM / COUNT ar ignora celula deoarece este în format text.
Adăugați zero zero folosind formatarea numerelor personalizate
Când se folosește: Când aveți un set de date numerice și doriți ca rezultatul să fie numeric (nu text).
Când afișați un număr într-un anumit format, acesta nu modifică valoarea de bază a numărului. De exemplu, pot afișa numărul 1000 ca 1000 sau 1.000 sau 1000.00 sau 001000 sau 26-09-1902 (datele pare sunt numere în backend în Excel).
În toate modurile diferite de afișare a numărului, valoarea numărului nu se schimbă niciodată. Numai modul în care este afișat este modificat.
Pentru a adăuga zero zero, îl putem formata pentru a o arăta în acest fel, în timp ce valoarea de bază ar rămâne neschimbată.
Iată pașii pentru a utiliza această tehnică pentru a adăuga zero zero în Excel:
- Selectați celulele în care doriți să adăugați zero zero.
- Accesați Acasă → Grup de numere și faceți clic pe lansatorul de dialog (o mică săgeată înclinată în dreapta jos). Aceasta va deschide caseta de dialog Formatare celule. Alternativ, puteți utiliza și comanda rapidă de la tastatură: Control + 1.
- În caseta de dialog Formatare celule, din fila Număr, selectați Personalizat în lista Categorie.
- În câmpul Tip, introduceți 00000
- Faceți clic pe OK.
Acest lucru va afișa întotdeauna toate numerele sub forma a cinci cifre, unde 0-urile inițiale sunt adăugate automat dacă numărul este mai mic de 5 cifre. Deci 10 ar deveni 00010 și 100 ar deveni 00100.
În acest caz, am folosit șase zerouri, dar dacă datele dvs. au numere cu mai multe cifre, atunci trebuie să utilizați formatul în consecință.
Notă: Această tehnică ar funcționa numai pentru un set de date numerice. În cazul în care aveți coduri de angajat precum A1, A2, A3 și așa mai departe, acestea sunt text și nu s-ar schimba atunci când aplicați formatul personalizat așa cum se arată mai sus.
Adăugați zero zero folosind funcția TEXT
Când utilizați: Când doriți ca rezultatul să fie text.
Funcția TEXT vă permite să schimbați valoarea în formatul dorit.
De exemplu, dacă doriți ca 1 să fie afișat ca 001, puteți utiliza funcția TEXT pentru asta.
Cu toate acestea, amintiți-vă că funcția TEXT ar schimba formatul și o va face TEXT. Aceasta înseamnă că atunci când faceți 1 ca 001, Excel tratează noul rezultat ca text cu trei caractere (la fel ca abc sau xyz).
Iată cum puteți adăuga zero-uri folosind funcția TEXT:
- Dacă aveți numerele din coloana A (să zicem din A2: A100), selectați B2: B100 și introduceți următoarea formulă:
= TEXT (A2, „00000”) - Apăsați Control + Enter pentru a aplica formula tuturor celulelor selectate.
Aceasta va afișa toate numerele sub forma a cinci cifre, unde 0-urile inițiale sunt adăugate automat dacă numărul este mai mic de 5 cifre.
Un avantaj al conversiei datelor în text este că acum le puteți folosi în formule de căutare, cum ar fi VLOOKUP sau INDEX / MATCH, pentru a prelua detaliile unui angajat folosind ID-ul său de angajat.
Notă: Această tehnică ar funcționa numai pentru un set de date numerice. În cazul în care aveți coduri de angajat precum A1, A2, A3 și așa mai departe, acestea sunt text și nu s-ar schimba atunci când aplicați formatul personalizat așa cum se arată mai sus.
Adăugați zero zero folosind funcțiile REPT și LEN
Când utilizați: Când aveți un set de date numeric / alfanumeric și doriți ca rezultatul să fie text.
Dezavantajul utilizării funcției TEXT a fost că ar funcționa doar cu date numerice. Dar, în cazul în care aveți un set de date alfanumeric (să zicem A1, A2, A3 și așa mai departe), atunci funcția TEXT ar eșua.
În astfel de cazuri, o combinație de funcții REPT și LEN face trucul.
Iată cum se face:
- Dacă aveți numerele din coloana A (să zicem din A2: A100), selectați B2: B100 și introduceți următoarea formulă:
= REPT (0,5-LEN (A2)) și A2 - Apăsați Control + Enter pentru a aplica formula tuturor celulelor selectate.
Acest lucru ar face ca toate numerele / șirurile să aibă o lungime de 5 caractere, cu zero-uri oriunde este necesar.
Iată cum funcționează această formulă:
- LEN (A2) dă lungimea șirului / numerelor din celulă.
- = REPT (0,5-LEN (A2)) ar da numărul de 0 care ar trebui adăugat. Aici am folosit 5 în formulă, deoarece aceasta a fost lungimea maximă a șirului / numerelor din setul meu de date. Puteți schimba acest lucru în funcție de datele dvs.
- = REPT (0,5-LEN (A2)) și A2 ar adăuga pur și simplu numărul de zerouri la valoarea celulei. De exemplu, dacă valoarea din celulă este 123, aceasta va întoarce 00123.
Adăugați zero zero folosind funcția personalizată (VBA)
Dacă trebuie să faceți destul de des să adăugați zero-uri inițiale în Excel, utilizarea unei funcții personalizate este o idee bună.
Iată codul VBA care va crea o funcție simplă pentru adăugarea zero-urilor principale:
'Code by Sumit Bansal from http://trumpexcel.com Function AddLeadingZeroes (ref As Range, Length As Integer) Dim i As Integer Dim Result As String Dim StrLen As Integer StrLen = Len (ref) For i = 1 To Length If i <= StrLen Then Result = Rezultat & Mid (ref, i, 1) Alt rezultat = "0" & Rezultat Sfârșit Dacă Următor i AddLeadingZeroes = Funcție Sfârșit Rezultat
Pur și simplu adăugați acest cod în fereastra codului modulului și îl veți putea folosi la fel ca orice altă funcție de foaie de lucru.
Sau creați un supliment pentru acesta și puteți să-l distribuiți colegilor dvs.
