Postoje situacije u kojima morate dodati početne nule u skup podataka u Excelu. To bi mogao biti slučaj ako vodite evidenciju u Excelu, kao što su ID -ovi zaposlenika ili ID -ovi transakcija.
Na primjer, možda ćete htjeti dosljedno pogledati skup podataka kao što je prikazano u nastavku:
U ovom ćete vodiču naučiti različite načine dodavanja početnih nula u Excel:
- Pretvaranje formata u tekst
- Korištenje prilagođenog oblikovanja brojeva
- Korištenje funkcije teksta
- Korištenje REPT/LEN funkcija
- Korištenje VBA
Svaka od ovih metoda ima neke prednosti i nedostatke (obrađene u svakom odjeljku).
Pogledajmo kako funkcionira svaki od ovih.
Dodajte vodeće nule pretvaranjem formata u tekst
Kada koristiti: Kada imate mali brojčani skup podataka, a to namjeravate izvršiti ručno.
Pretpostavimo da imate ID -ove zaposlenika odjela marketinga kako je prikazano u nastavku i želite da ti ID -ovi izgledaju dosljedno dodavanjem početnih nula.
Pokušajte promijeniti ID unošenjem početnih nula (00001 umjesto 1).
No, na vaše čuđenje, Excel ga pretvara natrag u 1.
To se događa jer Excel razumije da su 00001 i 1 isti brojevi i da bi trebali slijediti ista pravila prikaza.
Koliko god vas ovo frustriralo, Excel ima svoje razloge.
Dakle, da biste obavili posao bez savijanja Excelovih pravila, morat ćete iskoristiti činjenicu da se ovo pravilo ne odnosi na oblikovanje teksta.
Evo što trebate učiniti:
- Odaberite ćelije u koje želite ručno dodati početne nule.
- Idite na Početna → Grupa brojeva i s padajućeg izbornika odaberite Tekst.
To je to!
Sada, kada ručno unesete početne nule, Excel bi se lako pridržavao.
Oprez: Kad pretvorite format u tekst, neke Excelove funkcije neće raditi ispravno. Na primjer, funkcija SUM/COUNT zanemarila bi ćeliju jer je u tekstualnom formatu.
Dodajte vodeće nule korištenjem prilagođenog oblikovanja brojeva
Kada koristiti: Kada imate numerički skup podataka i želite da rezultat bude numerički (ne tekstualni).
Kad prikazujete broj u određenom formatu, to ne mijenja temeljnu vrijednost broja. Na primjer, mogu prikazati broj 1000 kao 1000 ili 1000 ili 1000,00 ili 001000 ili 26-09-1902 (čak su i datumi brojevi u pozadini u Excelu).
Na sve različite načine prikaza broja, vrijednost broja se nikada ne mijenja. Mijenja se samo način prikaza.
Da bismo dodali početne nule, možemo ga oblikovati tako da to prikaže, dok bi temeljna vrijednost ostala nepromijenjena.
Evo koraka za korištenje ove tehnike za dodavanje početnih nula u Excel:
- Odaberite ćelije u koje želite dodati početne nule.
- Idite na Početna → Grupa brojeva i kliknite pokretač dijaloga (mala nagnuta strelica u donjem desnom kutu). Ovo će otvoriti dijaloški okvir Oblikovanje ćelija. Alternativno, možete koristiti i tipkovnički prečac: Control + 1.
- U dijaloškom okviru Oblikovanje ćelija na kartici Broj odaberite Prilagođeno na popisu kategorija.
- U polje Vrsta unesite 00000
- Pritisnite U redu.
Na taj način uvijek će se svi brojevi prikazati kao pet znamenki, gdje se početne 0 automatski dodaju ako je broj manji od 5 znamenki. Tako bi 10 postalo 00010, a 100 postalo 00100.
U ovom slučaju koristili smo šest nula, ali ako vaši podaci imaju brojeve s više znamenki, tada morate koristiti format u skladu s tim.
Napomena: Ova bi tehnika radila samo za numerički skup podataka. U slučaju da imate ID -ove zaposlenika poput A1, A2, A3 i tako dalje, to su tekstovi i ne bi se promijenili kada primijenite prilagođeni format kao što je prikazano gore.
Dodajte vodeće nule pomoću funkcije TEXT
Kada koristiti: Kada želite da rezultat bude tekst.
TEXT funkcija omogućuje vam promjenu vrijednosti u željeni format.
Na primjer, ako želite da se 1 prikaže kao 001, za to možete koristiti funkciju TEXT.
Međutim, zapamtite da bi funkcija TEXT promijenila format i učinila ga TEXT. To znači da kada napravite 1 kao 001, Excel novi rezultat tretira kao tekst s tri znaka (baš poput abc ili xyz).
Evo kako dodati početne nule pomoću funkcije TEXT:
- Ako imate brojeve u stupcu A (recimo iz A2: A100), odaberite B2: B100 i unesite sljedeću formulu:
= TEKST (A2, ”00000 ″) - Pritisnite Control + Enter da biste primijenili formulu na sve odabrane ćelije.
Ovo će prikazati sve brojeve kao pet znamenki, gdje se početne 0 automatski dodaju ako je broj manji od 5 znamenki.
Jedna je od prednosti pretvaranja podataka u tekst to što ih sada možete koristiti u formulama za pretraživanje, kao što su VLOOKUP ili INDEX/MATCH za dohvaćanje podataka o zaposleniku koristeći njegov ID zaposlenika.
Napomena: Ova bi tehnika radila samo za numerički skup podataka. U slučaju da imate ID -ove zaposlenika poput A1, A2, A3 i tako dalje, to su tekstovi i ne bi se promijenili kada primijenite prilagođeni format kao što je prikazano gore.
Dodajte vodeće nule pomoću funkcija REPT i LEN
Kada koristiti: Kad imate skup podataka koji je numerički/alfanumerički i želite da rezultat bude tekst.
Nedostatak korištenja funkcije TEXT bio je u tome što bi radila samo s numeričkim podacima. No, u slučaju da imate alfanumerički skup podataka (recimo A1, A2, A3 i tako dalje), funkcija TEXT neće uspjeti.
U takvim slučajevima kombinacija REPT -a i LEN -a pomaže.
Evo kako to učiniti:
- Ako imate brojeve u stupcu A (recimo iz A2: A100), odaberite B2: B100 i unesite sljedeću formulu:
= REPT (0,5-LEN (A2)) & A2 - Pritisnite Control + Enter da biste primijenili formulu na sve odabrane ćelije.
Time bi svi brojevi/nizovi bili dugački 5 znakova s početnim nulama gdje god je to potrebno.
Evo kako ova formula funkcionira:
- LEN (A2) daje duljinu niza/brojeva u ćeliji.
- = REPT (0,5-LEN (A2)) bi dao broj 0 koji treba dodati. Ovdje sam koristio 5 u formuli jer je to bila maksimalna duljina niza/brojeva u mom skupu podataka. To možete promijeniti prema svojim podacima.
- = REPT (0,5-LEN (A2)) & A2 jednostavno bi dodali broj nula vrijednosti ćelije. Na primjer, ako je vrijednost u ćeliji 123, vratit će se 00123.
Dodajte vodeće nule pomoću prilagođene funkcije (VBA)
Ako ste dodavanje početnih nula u Excel nešto što morate učiniti prilično često, upotreba prilagođene funkcije dobra je ideja.
Evo VBA koda koji će stvoriti jednostavnu funkciju za dodavanje početnih nula:
'Kod prema Sumit Bansalu s http://trumpexcel.com Funkcija 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 = Result & Mid (ref, i, 1) Else Result = "0" & Result End If Next i AddLeadingZeroes = Funkcija kraja rezultata
Jednostavno dodajte ovaj kôd u prozor koda modula i moći ćete ga koristiti kao i svaku drugu funkciju radnog lista.
Ili izradite dodatak za njega i moći ćete ga podijeliti sa svojim kolegama.
