On tilanteita, joissa Excel -tietojoukkoon on lisättävä etunollia. Näin voi tapahtua, jos ylläpidät Excelissä tietueita, kuten työntekijätunnuksia tai tapahtumatunnuksia.
Haluat esimerkiksi saada johdonmukaisen ulkoasun tietojoukostasi alla olevan kuvan mukaisesti:
Tässä opetusohjelmassa opit erilaisia tapoja lisätä nolla Excelissä:
- Muodon muuntaminen tekstiksi
- Mukautetun numeron muotoilun käyttäminen
- Tekstitoiminnon käyttäminen
- REPT/LEN -toimintojen käyttäminen
- VBA: n käyttö
Jokaisella näistä menetelmistä on joitain etuja ja haittoja (käsitelty jokaisessa osassa).
Katsotaanpa, miten kukin näistä toimii.
Lisää nollia muuntamalla muoto tekstiksi
Milloin käyttää: Kun sinulla on pieni numeerinen tietojoukko ja aiot tehdä tämän muokkauksen manuaalisesti.
Oletetaan, että sinulla on markkinointiosaston työntekijöiden tunnukset alla olevan kuvan mukaisesti ja haluat saada nämä tunnukset näyttämään johdonmukaisilta lisäämällä etunollat.
Joten yritä muuttaa tunnusta kirjoittamalla alkuasteet (00001 1: n sijasta).
Mutta hämmästykseksesi Excel muuntaa sen takaisin yhdeksi.
Tämä tapahtuu, kun Excel ymmärtää, että 00001 ja 1 ovat samat numerot ja niiden tulee noudattaa samoja näyttösääntöjä.
Nyt niin turhauttavaa kuin se voi olla sinulle, Excelillä on syynsä.
Joten saadaksesi työn tehtyä taivuttamatta Excel -sääntöjä sinun on hyödynnettävä sitä tosiasiaa, että tämä sääntö ei koske tekstin muotoilua.
Joten sinun on tehtävä tämä:
- Valitse solut, joihin haluat lisätä manuaalisesti etunollat.
- Siirry etusivulle → Numeroryhmä ja valitse avattavasta valikosta Teksti.
Se siitä!
Kun syötät alun nollat manuaalisesti, Excel noudattaa sitä helposti.
Varoitus: Kun muunnat muodon tekstiksi, jotkin Excel -toiminnot eivät toimi oikein. Esimerkiksi SUMMA/LASKU -toiminto sivuuttaa solun, koska se on tekstimuodossa.
Lisää nollia käyttämällä mukautettua numeromuotoa
Milloin käyttää: Kun sinulla on numeerinen tietojoukko ja haluat tuloksen olevan numeerinen (ei tekstiä).
Kun näytät numeron tietyssä muodossa, se ei muuta numeron taustalla olevaa arvoa. Esimerkiksi voin näyttää numeron 1000 muodossa 1000 tai 1000 tai 1000,00 tai 001000 tai 26-09-1902 (jopa päivämäärät ovat Excelin taustaohjelman numeroita).
Kaikilla eri tavoilla näyttää numero, numeron arvo ei koskaan muutu. Ainoastaan tapa, jolla se esitetään, muuttuu.
Jos haluat lisätä alun nollat, voimme muotoilla sen osoittamaan sen tällä tavalla, kun taas taustalla oleva arvo pysyy muuttumattomana.
Tässä on vaiheet tämän tekniikan käyttämiseksi Excelin etunollien lisäämiseen:
- Valitse solut, joihin haluat lisätä etunollia.
- Siirry etusivulle → Numeroryhmä ja napsauta valintaikkunan käynnistysohjelmaa (pieni kallistettu nuoli oikeassa alakulmassa). Tämä avaa Solujen muotoilu -valintaikkunan. Vaihtoehtoisesti voit käyttää myös pikanäppäintä: Control + 1.
- Valitse Muotoile solut -valintaikkunan Numero -välilehdessä Mukautettu Luokka -luettelosta.
- Kirjoita Tyyppi -kenttään 00000
- Napsauta OK.
Jos teet tämän, kaikki numerot näkyvät aina viisinumeroisina, ja ensimmäiset 0 lisätään automaattisesti, jos numero on alle 5 numeroa. Joten 10: stä tulee 00010 ja 100: sta 00100.
Tässä tapauksessa olemme käyttäneet kuutta nollaa, mutta jos tietosi sisältävät enemmän numeroita, sinun on käytettävä muotoa sen mukaisesti.
Huomautus: Tämä tekniikka toimii vain numeerisessa tietojoukossa. Jos sinulla on työntekijöiden tunnuksia, kuten A1, A2, A3 ja niin edelleen, nämä ovat tekstiä eivätkä muutu käytettäessä mukautettua muotoa yllä kuvatulla tavalla.
Lisää nollia käyttämällä TEXT -toimintoa
Milloin käyttää: Kun haluat tuloksen olevan teksti.
TEXT -toiminnon avulla voit muuttaa arvon haluttuun muotoon.
Jos esimerkiksi haluat, että 1 näytetään muodossa 001, voit käyttää siihen TEXT -toimintoa.
Muista kuitenkin, että TEXT -toiminto muuttaa muotoa ja tekee siitä TEXT. Tämä tarkoittaa, että kun teet 1: ksi 001, Excel käsittelee uutta tulosta tekstinä, jossa on kolme merkkiä (aivan kuten abc tai xyz).
Näin lisäät alun nollat TEXT -toiminnolla:
- Jos sarakkeessa A on numeroita (esimerkiksi A2: A100), valitse B2: B100 ja kirjoita seuraava kaava:
= TEKSTI (A2, "00000") - Käytä kaavaa kaikkiin valittuihin soluihin painamalla Control + Enter.
Tämä näyttää kaikki numerot viisinumeroisina, ja alkavat 0 lisätään automaattisesti, jos numero on alle 5 numeroa.
Yksi tietojen muuntamisesta tekstiksi hyöty on se, että voit nyt käyttää niitä hakukaavoissa, kuten VLOOKUP tai INDEX/MATCH, noutaaksesi työntekijän tiedot käyttämällä hänen työntekijän tunnustaan.
Huomautus: Tämä tekniikka toimii vain numeerisessa tietojoukossa. Jos sinulla on työntekijöiden tunnuksia, kuten A1, A2, A3 ja niin edelleen, ne ovat tekstiä eivätkä muutu käytettäessä mukautettua muotoa yllä kuvatulla tavalla.
Lisää nollia käyttämällä REPT- ja LEN -toimintoja
Milloin käyttää: Kun tietojoukko on numeerinen/aakkosnumeerinen ja haluat tuloksen olevan teksti.
TEKSTI -toiminnon haittana oli, että se toimisi vain numeerisen datan kanssa. Mutta jos sinulla on aakkosnumeerinen tietojoukko (esimerkiksi A1, A2, A3 ja niin edelleen), TEKSTI -toiminto epäonnistuu.
Tällaisissa tapauksissa REPT- ja LEN -toimintojen yhdistelmä tekee tempun.
Voit tehdä sen seuraavasti:
- Jos sarakkeessa A on numeroita (esimerkiksi A2: A100), valitse B2: B100 ja kirjoita seuraava kaava:
= REPT (0,5-LEN (A2)) & A2 - Käytä kaavaa kaikkiin valittuihin soluihin painamalla Control + Enter.
Tällöin kaikki numerot/merkkijonot olisivat 5 merkkiä pitkiä ja edessä olisi nolla aina tarvittaessa.
Näin tämä kaava toimii:
- LEN (A2) antaa solun merkkijonon/numeroiden pituuden.
- = REPT (0,5-LEN (A2)) antaisi lisättävän luvun 0. Tässä olen käyttänyt kaavassa 5, koska se oli merkkijonon/numeroiden enimmäispituus tietojoukossani. Voit muuttaa tätä tietojen perusteella.
- = REPT (0,5-LEN (A2)) & A2 yksinkertaisesti lisäisi nollien määrän solun arvoon. Jos esimerkiksi solun arvo on 123, tämä palauttaa arvon 00123.
Lisää nollia käyttämällä mukautettua toimintoa (VBA)
Jos joudut tekemään melko usein etunollien lisäämisen Exceliin, mukautetun toiminnon käyttäminen on hyvä idea.
Tässä on VBA -koodi, joka luo yksinkertaisen toiminnon etunollien lisäämiseen:
'Koodi: Sumit Bansal osoitteesta http://trumpexcel.com <= StrLen Sitten tulos = Tulos ja keskitaso (viite, i, 1) Muu tulos = "0" & tulos loppuu, jos seuraava i AddLeadingZeroes = Tuloksen lopetusfunktio
Lisää vain tämä koodi moduulikoodi -ikkunaan, ja voit käyttää sitä aivan kuten mitä tahansa muuta laskentataulukkotoimintoa.
Tai luo siihen lisäosa ja jaa se kollegoidesi kanssa.
