Kaip pridėti pagrindinius nulius „Excel“ - viskas, ką reikia žinoti

Yra situacijų, kai prie „Excel“ duomenų rinkinio reikia pridėti priekinius nulius. Tai gali atsitikti, jei „Excel“ tvarkote įrašus, pvz., Darbuotojų ID arba Operacijų ID.

Pavyzdžiui, galbūt norėsite nuosekliai peržiūrėti savo duomenų rinkinį, kaip parodyta žemiau:

Šioje pamokoje sužinosite įvairius būdus, kaip pridėti „Excel“ priekinius nulius:

• Formato konvertavimas į tekstą
• Pasirinktinio skaičių formatavimo naudojimas
• Teksto funkcijos naudojimas
• Naudojant REPT/LEN funkcijas
• Naudojant VBA

Kiekvienas iš šių metodų turi tam tikrų privalumų ir trūkumų (aprašytas kiekviename skyriuje).

Pažiūrėkime, kaip veikia kiekvienas iš jų.

Pridėkite pagrindinius nulius konvertuodami formatą į tekstą

Kada naudoti: Kai turite nedidelį skaičių duomenų rinkinį ir planuojate tai redaguoti rankiniu būdu.

Tarkime, kad turite rinkodaros skyriaus darbuotojų ID, kaip parodyta žemiau, ir norite, kad šie ID atrodytų nuosekliai, pridedant nulius.

Taigi pabandykite pakeisti ID įvesdami priekinius nulius (00001 vietoj 1).

Bet jūsų nuostabai, „Excel“ ją grąžina į 1.

Tai atsitinka, kai „Excel“ supranta, kad 00001 ir 1 yra tie patys skaičiai ir turėtų būti laikomasi tų pačių rodymo taisyklių.

Kad ir kaip tai jus vargintų, „Excel“ turi savo priežasčių.

Taigi, norėdami atlikti darbą nesulenkdami „Excel“ taisyklių, turėsite pasinaudoti tuo, kad ši taisyklė netaikoma teksto formatavimui.

Taigi štai ką jums reikia padaryti:

1. Pasirinkite langelius, kuriuose norite rankiniu būdu pridėti priekinius nulius.
2. Eikite į Pradžia → Skaičių grupė ir išskleidžiamajame meniu pasirinkite Tekstas.

Viskas!

Dabar, kai rankiniu būdu įvedate priekinius nulius, „Excel“ lengvai tai atliks.

Įspėjimas: kai formatą konvertuojate į tekstą, kai kurios „Excel“ funkcijos neveiks tinkamai. Pavyzdžiui, funkcija SUM/COUNT nekreipia dėmesio į langelį, nes ji yra teksto formatu.

Naudodami pasirinktinį skaičių formatavimą pridėkite pagrindinius nulius

Kada naudoti: Kai turite skaitinį duomenų rinkinį ir norite, kad rezultatas būtų skaitinis (ne tekstinis).

Kai rodote skaičių tam tikru formatu, tai nekeičia pagrindinės skaičiaus vertės. Pavyzdžiui, skaičių 1000 galiu rodyti kaip 1000 arba 1000 arba 1000,00 arba 001000 arba 26-09-1902 (net datos yra skaičiai „Excel“ užpakalinėje dalyje).

Visais skirtingais skaičiaus rodymo būdais skaičiaus vertė niekada nesikeičia. Keičiamas tik tai, kaip jis rodomas.

Norėdami pridėti priekinius nulius, galime suformatuoti, kad taip būtų parodyta, o pagrindinė vertė išliktų nepakitusi.

Toliau pateikiami žingsniai, kaip naudoti šią techniką, norint pridėti „Excel“ priekinius nulius:

1. Pasirinkite langelius, kuriuose norite pridėti priekinius nulius.
2. Eikite į Pradžia → Skaičių grupė ir spustelėkite dialogo lango paleidimo priemonę (maža pakreipta rodyklė apačioje dešinėje). Bus atidarytas dialogo langas Langelių formatavimas. Taip pat galite naudoti sparčiuosius klavišus: „Control“ + 1.
3. Dialogo lango Formatuoti langelius skirtuke Skaičius sąraše pasirinkite Pasirinktinis.
4. Lauke Tipas įveskite 00000
5. Spustelėkite Gerai.

Tai padarius, visi skaičiai visada bus rodomi penkių skaitmenų, o priekiniai 0 pridedami automatiškai, jei skaičius yra mažesnis nei 5 skaitmenys. Taigi 10 būtų 00010, o 100 - 00100.

Šiuo atveju mes naudojome šešis nulius, tačiau jei jūsų duomenys turi daugiau skaitmenų turinčius skaičius, turite atitinkamai naudoti formatą.

Pastaba: Šis metodas tinka tik skaitmeniniam duomenų rinkiniui. Jei turite darbuotojų ID, tokius kaip A1, A2, A3 ir pan., Tai yra tekstas ir nesikeis, kai pritaikysite pasirinktinį formatą, kaip parodyta aukščiau.

Naudodami TEXT funkciją pridėkite pagrindinius nulius

Kada naudoti: Kai norite, kad rezultatas būtų tekstas.

Funkcija TEKSTAS leidžia pakeisti vertę į norimą formatą.

Pavyzdžiui, jei norite, kad 1 būtų rodomas kaip 001, tam galite naudoti funkciją TEXT.

Tačiau atminkite, kad funkcija TEKSTAS pakeistų formatą ir paverstų jį tekstu. Tai reiškia, kad kai padarysite 1 kaip 001, „Excel“ naują rezultatą traktuos kaip tekstą su trimis simboliais (kaip abc arba xyz).

Štai kaip pridėti priekinius nulius naudojant funkciją TEXT:

1. Jei turite A stulpelyje esančius skaičius (tarkime nuo A2: A100), tada pasirinkite B2: B100 ir įveskite šią formulę:
   = TEKSTAS (A2, „00000 ″)
2. Paspauskite „Control“ + „Enter“, kad pritaikytumėte formulę visoms pasirinktoms ląstelėms.

Bus rodomi visi skaičiai kaip penki skaitmenys, o priekiniai 0 pridedami automatiškai, jei skaičius yra mažesnis nei 5 skaitmenys.

Vienas iš duomenų konvertavimo į tekstą privalumų yra tas, kad dabar galite juos naudoti paieškos formulėse, tokiose kaip VLOOKUP arba INDEX/MATCH, kad gautumėte išsamią informaciją apie darbuotoją, naudojant jo darbuotojo ID.

Pastaba: Šis metodas tinka tik skaitmeniniam duomenų rinkiniui. Jei turite darbuotojų ID, tokius kaip A1, A2, A3 ir pan., Tai yra tekstas ir nesikeis, kai pritaikysite pasirinktinį formatą, kaip parodyta aukščiau.

Naudodami REPT ir LEN funkcijas pridėkite pagrindinius nulius

Kada naudoti: Kai turite duomenų/raidžių ir skaičių duomenų rinkinį ir norite, kad rezultatas būtų tekstinis.

Funkcijos TEXT naudojimo trūkumas buvo tas, kad ji veiktų tik su skaitmeniniais duomenimis. Bet jei turite raidžių ir skaičių duomenų rinkinį (tarkime, A1, A2, A3 ir pan.), Funkcija TEXT nepavyks.

Tokiais atvejais REPT ir LEN funkcijos derinys padeda.

Štai kaip tai padaryti:

1. Jei turite A stulpelyje esančius skaičius (tarkime nuo A2: A100), tada pasirinkite B2: B100 ir įveskite šią formulę:
   = REPT (0,5-LEN (A2)) ir A2
2. Paspauskite „Control“ + „Enter“, kad pritaikytumėte formulę visoms pasirinktoms ląstelėms.

Taip visi skaičiai/eilutės būtų 5 simbolių ilgio, o priekiniai nuliai būtų visur, kur reikia.

Štai kaip veikia ši formulė:

• LEN (A2) nurodo eilutės/skaičių ilgį ląstelėje.
• = REPT (0,5-LEN (A2)) duotų skaičių 0, kuris turėtų būti pridėtas. Čia aš naudoju 5 formulėje, nes tai buvo didžiausias eilutės/skaičių ilgis mano duomenų rinkinyje. Tai galite pakeisti pagal savo duomenis.
• = REPT (0,5-LEN (A2)) ir A2 tiesiog pridėtų nulių skaičių prie langelio vertės. Pvz., Jei langelio vertė yra 123, tai grąžins 00123.

Pridėkite pagrindinius nulius naudodami pasirinktinę funkciją (VBA)

Jei „Excel“ pridedami nuliniai nuliai yra tai, ko jums reikia padaryti gana dažnai, tinkinta funkcija yra gera idėja.

Čia yra VBA kodas, kuris sukurs paprastą funkciją pridedant nulius:

„Sumit Bansal kodas iš http://trumpexcel.com Funkcija AddLeadingZeroes (nuoroda kaip diapazonas, ilgis kaip sveikasis skaičius) Dim i kaip sveikasis skaičius Rezultatas kaip eilutė Dim StrLen kaip sveikasis skaičius <= StrLen Tada rezultatas = Rezultatas ir vidurys (nuoroda, i, 1) Kitas rezultatas = "0" ir rezultato pabaiga, jei kitas i AddLeadingZeroes = Rezultato pabaigos funkcija

Tiesiog pridėkite šį kodą prie modulio kodo lango ir galėsite jį naudoti kaip ir bet kurią kitą darbalapio funkciją.

Arba sukurkite jo priedą ir pasidalykite juo su kolegomis.