On olukordi, kus peate Exceli andmekogumisse lisama algnullid. See võib juhtuda juhul, kui hoiate Excelis kirjeid, näiteks töötajate ID või tehingu ID.
Näiteks võite soovida saada oma andmekogust järjepideva välimuse, nagu allpool näidatud.
Selles õpetuses saate teada, kuidas Excelis eesnulle lisada:
- Vormingu teisendamine tekstiks
- Kohandatud numbrivormingu kasutamine
- Teksti funktsiooni kasutamine
- REPT/LEN -funktsioonide kasutamine
- VBA kasutamine
Igal neist meetoditest on oma eelised ja puudused (neid käsitletakse igas jaotises).
Vaatame, kuidas igaüks neist töötab.
Lisage nullid, teisendades vormingu tekstiks
Millal kasutada: Kui teil on väike arvandmete kogum ja kavatsete seda redigeerida käsitsi.
Oletame, et teil on turundusosakonna töötajate ID -d, nagu allpool näidatud, ja soovite, et need ID -d näeksid järjepidevad välja, lisades nullid.
Nii et proovite ID -d muuta, sisestades eesmised nullid (1 asemel 00001).
Kuid teie üllatuseks teisendab Excel selle tagasi 1 -ks.
See juhtub siis, kui Excel mõistab, et 00001 ja 1 on samad numbrid ja peaksid järgima samu kuvamisreegleid.
Nii masendav kui see teie jaoks ka pole, on Excelil oma põhjused.
Nii et töö tegemiseks ilma Exceli reegleid painutamata peate kasutama ära asjaolu, et see reegel ei kehti teksti vormindamise kohta.
Nii et peate tegema järgmist.
- Valige lahtrid, kuhu soovite käsitsi nullnuppe lisada.
- Avage Avaleht → Numbrirühm ja valige rippmenüüst Tekst.
See on kõik!
Nüüd, kui sisestate nullid käsitsi, järgib Excel seda hõlpsalt.
Ettevaatust: kui teisendate vormingu tekstiks, ei tööta mõned Exceli funktsioonid korralikult. Näiteks funktsioon SUM/COUNT ignoreerib lahtrit, kuna see on tekstivormingus.
Lisage juhtnullid kohandatud numbrivormingu abil
Millal kasutada: Kui teil on numbriline andmekogum ja soovite, et tulemus oleks numbriline (mitte tekst).
Kui kuvate numbri kindlas vormingus, ei muuda see numbri aluseks olevat väärtust. Näiteks võin numbri 1000 kuvada 1000 või 1000 või 1000,00 või 001000 või 26-09-1902 (isegi kuupäevad on Exceli taustaprogrammis numbrid).
Numbri kuvamise erinevatel viisidel ei muutu numbri väärtus kunagi. Muutub ainult see, kuidas seda kuvatakse.
Esialgsete nullide lisamiseks saame selle vormindada nii, et aluseks olev väärtus jääks samaks.
Siin on sammud selle tehnika kasutamiseks Excelis eesmiste nullide lisamiseks:
- Valige lahtrid, kuhu soovite nullid lisada.
- Minge avalehele → Numbrirühm ja klõpsake dialoogi käivitajal (väike kallutatud nool paremas alanurgas). See avab dialoogiboksi Lahtrite vormindamine. Teise võimalusena võite kasutada ka kiirklahvi: Control + 1.
- Dialoogiboksi Lahtrite vormindamine vahekaardil Number valige loendist Kategooria Kohandatud.
- Sisestage väljale Tüüp 00000
- Klõpsake nuppu OK.
Seda tehes kuvatakse alati kõik numbrid viiekohalise numbrina, kus esimesed 0 -d lisatakse automaatselt, kui number on väiksem kui 5 numbrit. Nii et 10 -st saaks 00010 ja 100 -st 00100.
Sel juhul oleme kasutanud kuut nulli, kuid kui teie andmetes on rohkem numbreid, peate vormingut vastavalt kasutama.
Märkus. See meetod töötab ainult numbrilise andmekogumi puhul. Kui teil on töötajate ID -d nagu A1, A2, A3 ja nii edasi, siis on need tekst ja need ei muutu, kui rakendate kohandatud vormingut, nagu ülal näidatud.
Lisage juhtnullid funktsiooni TEXT abil
Millal kasutada: Kui soovite, et tulemus oleks tekst.
Funktsioon TEXT võimaldab muuta väärtuse soovitud vormingusse.
Näiteks kui soovite, et 1 kuvataks kui 001, saate selleks kasutada funktsiooni TEXT.
Kuid pidage meeles, et funktsioon TEXT muudaks vormingut ja muudaks selle tekstiks. See tähendab, et kui teete 1 väärtuseks 001, käsitleb Excel uut tulemust kolme tähemärgiga tekstina (täpselt nagu abc või xyz).
Siin on, kuidas funktsiooni TEXT abil eesnulle lisada:
- Kui teil on veerus A olevad numbrid (näiteks A2: A100), valige B2: B100 ja sisestage järgmine valem:
= TEKST (A2, "00000") - Valemi rakendamiseks kõigile valitud lahtritele vajutage klahvikombinatsiooni Control + Enter.
See kuvab kõik numbrid viiekohalise numbrina, kus esimesed 0 -d lisatakse automaatselt, kui number on väiksem kui 5 numbrit.
Andmete tekstiks teisendamise üks eelis on see, et saate neid nüüd kasutada otsinguvalemites, näiteks VLOOKUP või INDEX/MATCH, et tuua töötaja andmed, kasutades tema töötaja ID -d.
Märkus. See meetod töötab ainult numbrilise andmekogumi puhul. Kui teil on töötajate ID -d nagu A1, A2, A3 ja nii edasi, on need tekst ja need ei muutu, kui rakendate ülaltoodud kohandatud vormingut.
Lisage juhtnullid funktsioonide REPT ja LEN abil
Millal kasutada: Kui teil on numbriline/tähtnumbriline andmekogum ja soovite, et tulemus oleks tekst.
Funktsiooni TEXT kasutamise puuduseks oli see, et see töötaks ainult numbriliste andmetega. Kuid kui teil on tähtnumbriline andmekogum (näiteks A1, A2, A3 jne), siis funktsioon TEXT ebaõnnestub.
Sellistel juhtudel aitab REPT ja LEN funktsiooni kombinatsioon.
Siin on, kuidas seda teha.
- Kui teil on veerus A olevad numbrid (näiteks A2: A100), siis valige B2: B100 ja sisestage järgmine valem:
= REPT (0,5-LEN (A2)) ja A2 - Valemi rakendamiseks kõigile valitud lahtritele vajutage klahvikombinatsiooni Control + Enter.
See muudaks kõik numbrid/stringid 5 tähemärgi pikkuseks, kus eespool on nullid, kus vaja.
See valem töötab järgmiselt.
- LEN (A2) annab lahtris oleva stringi/numbrite pikkuse.
- = REPT (0,5-LEN (A2)) annaks arvu 0, mis tuleks lisada. Siin olen valemis kasutanud 5, kuna see oli minu andmestiku stringide/numbrite maksimaalne pikkus. Saate seda vastavalt oma andmetele muuta.
- = REPT (0,5-LEN (A2)) & A2 lisab lahtri väärtusele lihtsalt nullide arvu. Näiteks kui lahtri väärtus on 123, tagastab see väärtuse 00123.
Lisage juhtnullid kohandatud funktsiooni (VBA) abil
Kui Excelis peate nullide lisamist tegema üsna sageli, on kohandatud funktsiooni kasutamine hea mõte.
Siin on VBA -kood, mis loob lihtsa funktsiooni eesmiste nullide lisamiseks:
'Sumit Bansali kood saidilt http://trumpexcel.com Funktsioon AddLeadingZeroes (viide vahemikuna, pikkus täisarvuna) Dim i täisarvuna Tulemus stringina Dim StrLen täisarvuna StrLen = Len (viide) i = 1 kuni pikkus Kui i <= StrLen Siis tulemus = Tulemus ja keskmine (viide, i, 1) Muu tulemus = "0" ja tulemus lõpeb, kui järgmine i AddLeadingZeroes = Tulemuse lõppfunktsioon
Lihtsalt lisage see kood mooduli koodi aknasse ja saate seda kasutada nagu mis tahes muud töölehe funktsiooni.
Või looge sellele lisandmoodul ja saate seda oma kolleegidega jagada.
