Obstajajo primeri, ko morate v niz podatkov v Excelu dodati začetne ničle. To lahko velja, če v Excelu hranite zapise, na primer ID -je zaposlenih ali ID -je transakcij.
Na primer, morda boste želeli dosledno pogledati svoj nabor podatkov, kot je prikazano spodaj:
V tej vadnici se boste naučili različnih načinov dodajanja začetnih nič v Excelu:
- Pretvorba oblike v besedilo
- Uporaba oblikovanja številk po meri
- Uporaba besedilne funkcije
- Uporaba funkcij REPT/LEN
- Uporaba VBA
Vsaka od teh metod ima nekatere prednosti in slabosti (obravnavane v vsakem poglavju).
Poglejmo, kako deluje vsaka od teh.
Dodajte vodilne ničle s pretvorbo oblike v besedilo
Kdaj uporabiti: Ko imate majhen številski niz podatkov in nameravate to urejanje opraviti ročno.
Recimo, da imate ID -je zaposlenih v oddelku za trženje, kot je prikazano spodaj, in želite, da bodo te ID -je videti konsistentne, tako da dodate začetne ničle.
Poskusite spremeniti id tako, da vnesete začetne ničle (00001 namesto 1).
Toda na vaše presenečenje ga Excel pretvori nazaj v 1.
To se zgodi, ko Excel razume, da sta 00001 in 1 enaki številki in morata slediti istim pravilom prikaza.
Čeprav je za vas frustrirajoče, ima Excel svoje razloge.
Torej, če želite opraviti delo brez upogibanja Excelovih pravil, boste morali izkoristiti dejstvo, da to pravilo ne velja za oblikovanje besedila.
Torej, kaj morate storiti:
- Izberite celice, v katere želite ročno dodati začetne ničle.
- Pojdite na Domov → Skupina številk in v spustnem meniju izberite Besedilo.
To je to!
Zdaj, ko ročno vnesete začetne ničle, bi Excel zlahka upošteval.
Pozor: ko pretvorite obliko v besedilo, nekatere Excelove funkcije ne bodo delovale pravilno. Na primer, funkcija SUM/COUNT ne bi upoštevala celice, ker je v besedilni obliki.
Dodajte začetne ničle z oblikovanjem številk po meri
Kdaj uporabiti: Ko imate številski nabor podatkov in želite, da je rezultat številski (ne besedilni).
Ko prikažete številko v določeni obliki, to ne spremeni osnovne vrednosti številke. Na primer, lahko prikažem številko 1000 kot 1000 ali 1000 ali 1000,00 ali 001000 ali 26-09-1902 (celo datumi so številke v ozadju v Excelu).
Na vse različne načine prikaza številke se vrednost številke nikoli ne spremeni. Spremeni se le način prikaza.
Če želimo dodati začetne ničle, ga lahko oblikujemo, da bo tako prikazan, medtem ko osnovna vrednost ostane nespremenjena.
Tu so koraki za uporabo te tehnike za dodajanje začetnih nič v Excelu:
- Izberite celice, v katere želite dodati začetne ničle.
- Pojdite na Domov → Skupina številk in kliknite zaganjalnik pogovornega okna (majhna nagnjena puščica v spodnjem desnem kotu). S tem se odpre pogovorno okno Oblikovanje celic. Lahko pa uporabite tudi bližnjico na tipkovnici: Control + 1.
- V pogovornem oknu Oblikovanje celic na zavihku Številka na seznamu kategorij izberite Po meri.
- V polje Vrsta vnesite 00000
- Kliknite V redu.
S tem bodo vse številke vedno prikazane kot pet števk, kjer se samodejno dodajo začetne 0, če je število manjše od 5 mest. Tako bi 10 postalo 00010, 100 pa 00100.
V tem primeru smo uporabili šest nič, če pa imajo vaši podatki številke z več števkami, morate ustrezno uporabiti obliko.
Opomba: Ta tehnika bi delovala samo za številski nabor podatkov. Če imate ID -je zaposlenih, kot so A1, A2, A3 in tako naprej, so to besedilo in se ne bodo spremenile, če uporabite obliko po meri, kot je prikazano zgoraj.
Dodajte vodilne ničle s funkcijo TEXT
Kdaj uporabiti: Ko želite, da je rezultat besedilo.
TEXT funkcija vam omogoča, da vrednost spremenite v želeno obliko.
Če želite na primer 1 prikazati kot 001, lahko za to uporabite funkcijo TEXT.
Vendar ne pozabite, da bi funkcija TEXT spremenila obliko in postala besedilo. To pomeni, da ko označite 1 kot 001, Excel novi rezultat obravnava kot besedilo s tremi znaki (tako kot abc ali xyz).
Spodaj je opisano, kako s funkcijo TEXT dodate začetne ničle:
- Če imate številke v stolpcu A (recimo iz A2: A100), izberite B2: B100 in vnesite naslednjo formulo:
= BESEDILO (A2, "00000 ″) - Pritisnite Control + Enter, da uporabite formulo za vse izbrane celice.
To bo prikazalo vse številke kot pet števk, kjer se samodejno dodajo začetne 0, če je število manjše od 5 mest.
Ena od prednosti pretvorbe podatkov v besedilo je, da jih lahko zdaj uporabite v iskalnih formulah, kot sta VLOOKUP ali INDEX/MATCH, da pridobite podatke o zaposlenem z uporabo njegovega ID -ja zaposlenega.
Opomba: Ta tehnika bi delovala samo za številski nabor podatkov. Če imate ID -je zaposlenih, kot so A1, A2, A3 in tako naprej, so to besedilo in se ne bodo spremenile, če uporabite obliko po meri, kot je prikazano zgoraj.
Dodajte vodilne ničle s funkcijami REPT in LEN
Kdaj uporabiti: Ko imate nabor podatkov, ki je številsko/alfanumeričen, in želite, da je rezultat besedilo.
Pomanjkljivost uporabe funkcije TEXT je bila, da bi delovala samo s številskimi podatki. Če pa imate alfanumerični nabor podatkov (recimo A1, A2, A3 itd.), Funkcija TEXT ne bi uspela.
V takih primerih je kombinacija REPT in LEN trik.
Tukaj je, kako to storiti:
- Če imate številke v stolpcu A (recimo iz A2: A100), izberite B2: B100 in vnesite naslednjo formulo:
= REPT (0,5-LEN (A2)) & A2 - Pritisnite Control + Enter, da uporabite formulo za vse izbrane celice.
Tako bi bile vse številke/nizi dolgi 5 znakov z začetnimi ničlami, kjer koli je to potrebno.
Takole deluje ta formula:
- LEN (A2) poda dolžino niza/številk v celici.
- = REPT (0,5-LEN (A2)) bi dal število 0, ki ga je treba dodati. Tu sem v formuli uporabil 5, saj je to največja dolžina niza/številk v mojem naboru podatkov. To lahko spremenite glede na svoje podatke.
- = REPT (0,5-LEN (A2)) & A2 bi k vrednosti celice preprosto dodali število ničel. Na primer, če je vrednost v celici 123, se vrne 00123.
Dodajte vodilne ničle z uporabo funkcije po meri (VBA)
Če morate dodajanje začetnih nič v Excelu početi precej pogosto, je uporaba funkcije po meri dobra ideja.
Tukaj je koda VBA, ki bo ustvarila preprosto funkcijo za dodajanje začetnih nič:
'Koda po Sumit Bansalu iz 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 Potem rezultat = rezultat & sredina (ref, i, 1) Drugi rezultat = "0" & rezultat se konča, če je naslednji i AddLeadingZeroes = funkcija konca rezultata
Preprosto dodajte to kodo v okno kode modula in lahko jo uporabite tako kot katero koli drugo funkcijo delovnega lista.
Ali pa zanj ustvarite dodatek in ga delite s sodelavci.
