Cum se adaugă zero în fața numărului în Excel Sfaturi Excel

Cuprins:

Anonim


În acest articol, vom învăța diferite moduri de a adăuga zero (0s) în față în Excel.

Scenariu:

Adăugarea zero în fața numărului din Excel. Excel implicit nu ia zerouri în fața numărului din celulă. Ați încercat vreodată să introduceți unele date precum 000123 în Excel? Probabil că veți observa rapid că Excel va elimina automat zero-urile din numărul de intrare. Acest lucru poate fi foarte enervant dacă doriți acele zerouri din primele date și nu știți cum să faceți ca Excel să le păstreze. Din fericire, există destul de multe modalități de a vă bloca numerele cu zerouri la început. În acest articol, vă voi explica diferite moduri de a adăuga sau de a păstra acele zerouri de la numerele dvs.

Funcții diferite pentru a adăuga zero (0) în față

După cum știm, funcția TEXT este cea mai utilizată funcție pentru a adăuga 0 în față, dar aici vă voi explica mai multe moduri de a adăuga zerouri (0) numerelor.

  1. Funcția TEXT
  2. Funcția DREAPTA
  3. Funcția BASE
  4. Adăugați 0 în valorile tabelului pivot

Formatează ca text

Una dintre soluții este convertirea formatului numărului în text, deoarece dacă introducem 00xyz, excel consideră această valoare ca text și își păstrează 0 în față. Există diferite moduri de a converti o celulă numerică în format text.

  • Folosind formatul personalizat
  • Folosind funcția TEXT
  • Folosind Apostrophe (') la începutul valorii în timp ce se introduce

Format personalizat

Să înțelegem aceste metode una câte una. Folosirea unui format personalizat pentru a schimba formatul este cel mai simplu mod de a schimba numerele deja existente în interval. Doar selectați intervalul și Utilizați comanda rapidă Ctrl + 1 sau selectați Mai multe formate numerice din lista derulantă din fila Acasă care afișează General.

Schimbați formatul din General în Text.

  1. Selectați intervalul de celule în care doriți să introduceți zero-uri.
  2. Accesați fila Acasă.
  3. În secțiunea Numere, faceți clic pe selecția Format dropdown.
  4. Alegeți Text din opțiunile de format.

Acum, dacă încercați să introduceți numere cu zerouri din partea de sus, acestea nu vor dispărea deoarece sunt introduse ca valori de text în loc de numere. Utilizatorii Excel pot adăuga o formatare personalizată pentru formatarea numerelor cu zerouri din partea de sus.

Totuși, vor părea că au zero-uri principale. Datele care stau la baza lor nu vor fi transformate în text cu zerourile adăugate.

Adăugați un format personalizat pentru a afișa zerourile din partea de sus.

  1. Selectați intervalul de celule la care doriți să adăugați zerouri inițiale și deschideți caseta de dialog Formatare celule.
    • Faceți clic dreapta și alegeți Formatarea celulelor.
    • Utilizați comanda rapidă de la tastatură Ctrl + 1.
  2. Accesați fila Număr.
  3. Selectați Personalizat din opțiunile categoriei.
  4. Adăugați un nou format personalizat în intrarea Type. Dacă doriți ca numărul total de cifre, inclusiv orice zerouri din partea de sus să fie 6, adăugați 000000 ca format personalizat.
  5. Apăsați butonul OK.

Funcția TEXT

Funcția TEXT vă va permite să aplicați o formatare personalizată oricărui număr de date aflat deja în foaia dvs. de calcul.

= TEXT (valoare, format)

  • Valoare - Aceasta este valoarea pe care doriți să o convertiți în text și să aplicați formatarea.
  • Format - Aceasta este formatarea de aplicat.

= TEXT (B3, "000000")

Dacă doriți să adăugați zerouri la un număr din celula B3 astfel încât numărul total de cifre să fie 6, atunci puteți utiliza formula de mai sus.

Folosind Apostrophe (') la început

Puteți forța Excel să introducă un număr ca text utilizând un apostrof principal.

Aceasta înseamnă că veți putea păstra aceste zerouri în față pe măsură ce introduceți datele.

Această metodă este rapidă și ușoară în timp ce introduceți date. Tastați doar un caracter înainte de orice numere. Aceasta va spune Excel că datele sunt menite să fie text și nu un număr.

Când apăsați Enter, zerourile din partea de sus vor rămâne vizibile în foaia de lucru. 'Nu va fi vizibil în foaia de lucru, dar este încă acolo și poate fi văzut în bara de formule atunci când cursorul celulei active este pe celulă.

Funcția DREAPTA

O altă modalitate de a obține zero-urile în față cu o formulă este utilizarea funcției DREAPTA. Puteți concatena un șir de zerouri la număr și apoi tăiați extrasele folosind funcția DREAPTA.

Funcția DREAPTA va extrage cele mai din dreapta N caractere dintr-o valoare text.

= DREAPTA (Text, [Număr])

  • Text - Acesta este textul din care doriți să extrageți caractere.
  • Număr (Opțional) - Acesta este numărul de caractere de extras din text. Dacă acest argument nu este introdus, atunci numai primul caracter va fi extras.

= DREAPTA ("000000" & B3, 6)

Formula de mai sus va concatena mai multe zerouri la începutul unui număr din celula B3, apoi va întoarce cele 6 caractere din dreapta, rezultând unele zerouri de la început.

Funcția BASE

Veți observa că acest articol descrie 9 moduri de a adăuga zerouri inițiale, dar videoclipul meu de pe YouTube arată doar 8 moduri.

Acest lucru se datorează faptului că nu știam că puteți folosi funcția BASE pentru a adăuga zerouri inițiale până când cineva a menționat-o în comentariile videoclipului.

Funcția BASE vă permite să convertiți un număr într-o reprezentare text cu o bază dată.

Numerele pe care le utilizați de obicei sunt baza 10, dar puteți utiliza această funcție pentru a converti un număr de bază 10 într-o reprezentare de bază 2 (binară).

= BAZĂ (număr, bază, [lungime minimă])

  • Număr - Acesta este numărul pe care doriți să îl convertiți într-o valoare text într-o altă bază.
  • Baza - Aceasta este baza în care doriți să convertiți valoarea.
  • Lungime min. (Opțional) - Aceasta este lungimea minimă a caracterelor valorii textului convertit.

Folosiți formula

= BAZĂ (B3,10,6)

Formula de mai sus va converti un număr din celula B3 în baza 10 (este deja un număr de bază 10, dar acesta îl va păstra ca bază 10) și îl va converti într-o valoare text cu cel puțin 6 caractere. Dacă valoarea convertită este mai mică de 6 caractere, va bloca valoarea cu zerouri pentru a obține un minim de 6 caractere.

Cu siguranță o nouă utilizare interesantă pentru o funcție pe care altfel nu o folosesc niciodată.

Adăugați zero (0) numai în fața numerelor, nu în valorile textului

Aici vom lua în considerare scenariul care acoperă atât textul, cât și valorile numerelor din aceeași listă. Acum Excel consideră fiecare celulă diferită din punct de vedere al formatului. Acum, cum să anunțe excel că, dacă celula are o valoare numerică, atunci adaugă 0 în față sau dacă celula are text sau orice altă valoare, atunci lasă-o așa cum este.

Pentru aceasta vom folosi o combinație de două funcții IF și funcția ISNUMBER. Aici avem câteva valori de încercat.

Formula generică continuă ca.

= IF (ISNUMBER (cell_ref), "0" & ​​cell_ref, cell_ref)

Aici

cell_ref: referință de celulă a celulei corespunzătoare

Folosiți formula în celulă

= IF (ISNUMBER (F3), "0" & ​​F3, F3)

Explicație: Funcția ISNUMBER verifică numărul celulei. Funcția IF verifică dacă celula are un număr, apoi adaugă zero în fața altui număr, doar returnează valoarea celulei.

După cum puteți vedea în imaginea de mai sus, zero (0) este adăugat în fața numărului. Să verificăm dacă această funcție funcționează pe alte valori. Copiați formula în alte celule, selectați celulele care iau prima celulă în care formula este deja aplicată, utilizați tasta de comandă rapidă Ctrl + D

va bloca valoarea cu zerouri pentru a obține cel puțin un zero în fața caracterelor. Cu siguranță o nouă utilizare interesantă pentru o funcție pe care altfel nu o folosesc niciodată.

Coloana calculată Power Pivot

Există o altă opțiune pentru a adăuga zerouri în tabelul pivot. Le puteți adăuga într-o coloană calculată în Power Pivot. În acest fel puteți utiliza noua coloană din zona Filtru, Rânduri sau Coloană a unui tabel pivot.

= FORMAT (Numere [Număr], "000000")

În programul de completare Power Pivot, puteți adăuga o nouă coloană și puteți utiliza funcția FORMAT pentru a crea zerouri din coloană cu o formulă ca mai sus. O coloană calculată calculează o valoare pentru fiecare rând, deci nu este nevoie să înfășurați funcția în interiorul unei celule. Acum puteți utiliza această nouă coloană în zona Rânduri a unui tabel pivot la fel ca orice altă coloană de date.

rezumat

Poate fi frustrant să văd toate zerourile dispărând atunci când nu știm de ce se întâmplă sau cum să o prevenim. După cum puteți vedea, există o mulțime de moduri de a vă asigura că toate acele zerouri rămân în datele dvs. Indiferent de cerințele dvs., există cu siguranță o soluție bună pentru dvs.

Iată toate notele de observație folosind formula din Excel
Note:

  1. Formula poate fi utilizată atât pentru texte, cât și pentru numere.
  2. Textul de găsire și înlocuire ar trebui să fie sortat într-un tabel. Pe măsură ce folosim indexuri pentru a găsi și înlocui, asigurați-vă că textul „găsiți” are același index ca textul „înlocuiți”. Este mai bine să aveți ambele liste una lângă alta, așa cum am făcut în al doilea exemplu.
  3. Formula generică are două funcții SUBSTITUTE. Veți avea nevoie de atâtea funcții de înlocuire, cât de multe aveți de înlocuit.
  4. metacaracterele ajută la extragerea valorii din șiruri de caractere.
  5. Funcția acceptă operatori logici cum ar fi,, = dar aceștia sunt utilizați folosind semnul ghilimelei duble ( " ) cu argumente. Dar dacă utilizați referința celulei pentru criterii, citatele pot fi ignorate.
  6. Argumentul criteriilor ar trebui să utilizeze semnul ghilimelei (") pentru texte (cum ar fi" Gillen ") sau cifre (cum ar fi"> 50 ").

Sper că acest articol despre Diferite moduri de a adăuga zero (0s) în față în Excel este explicativ. Găsiți mai multe articole despre calcularea valorilor și formulele Excel conexe aici. Dacă ți-au plăcut blogurile noastre, împărtășește-le prietenilor tăi de pe Facebook. Și, de asemenea, ne puteți urmări pe Twitter și Facebook. Ne-ar plăcea să aflăm de la dvs., să ne anunțați cum putem îmbunătăți, completa sau inova munca noastră și să o îmbunătățim. Scrieți-ne pe site-ul de e-mail.

Funcția Excel REPLACE vs SUBSTITUTE: Funcțiile REPLACE și SUBSTITUTE sunt cele mai neînțelese. Pentru a găsi și a înlocui un text dat, folosim funcția SUBSTITUTE. În cazul în care REPLACE este utilizat pentru a înlocui un număr de caractere în șir.

Înlocuiți textul de la capătul unui șir începând de la poziția variabilă: Pentru a înlocui textul de la sfârșitul șirului, folosim funcția REPLACE. Funcția REPLACE utilizează poziția textului din șir pentru a înlocui.

Cum să verificați dacă un șir conține unul dintre textele din Excel: Pentru a verifica dacă un șir conține mai multe texte, folosim această formulă. Folosim funcția SUM pentru a însuma toate meciurile și apoi efectuăm o logică pentru a verifica dacă șirul conține oricare dintre șirurile multiple.

Numărați celulele care conțin text specific: O funcție simplă COUNTIF va face magia. Pentru a număra numărul de celule multiple care conțin un șir dat, folosim operatorul wildcard cu funcția COUNTIF.

Articole populare:

50 de comenzi rapide Excel pentru a vă crește productivitatea : Obțineți mai rapid sarcinile dvs. în Excel. Aceste comenzi rapide vă vor ajuta să vă sporiți eficiența de lucru în Excel.

Cum se utilizează funcția VLOOKUP în Excel : Aceasta este una dintre cele mai utilizate și populare funcții ale Excel, care este utilizată pentru a căuta valoarea din diferite intervale și foi.

Cum se utilizează funcția IF în Excel : Instrucțiunea IF din Excel verifică starea și returnează o valoare specifică dacă condiția este ADEVĂRATĂ sau returnează o altă valoare specifică dacă este FALSĂ.

Cum se utilizează funcția SUMIF în Excel : Aceasta este o altă funcție esențială a tabloului de bord. Acest lucru vă ajută să rezumați valori în condiții specifice.

Cum se utilizează funcția COUNTIF în Excel : Numărați valorile cu condiții folosind această funcție uimitoare. Nu trebuie să vă filtrați datele pentru a număra anumite valori. Funcția Countif este esențială pentru a vă pregăti tabloul de bord.