Kuidas Excelis juhtnulle lisada

See õpetus hõlmab erinevaid meetodeid eesotsate nullide lisamiseks või säilitamiseks ning eesotsate nullide eemaldamiseks Excelis.

Iga kord, kui sisestate või impordite ühe või mitme eesoleva nulliga numbreid (nt 000652), eemaldab Excel need nullid automaatselt ja lahtrites kuvatakse ainult arv ise (652). Põhjus on selles, et ees olevad nullid pole arvutuste jaoks vajalikud ja neid ei loeta.

Siiski on aegu, mil need ees olevad nullid on vajalikud, näiteks ID-numbrite, telefoninumbrite, krediitkaardinumbrite, tootekoodide, sihtnumbrite jne sisestamisel. Õnneks annab Excel meile mitmeid viise eesolevate nullide lisamiseks või hoidmiseks. rakkudes. Selles artiklis näitame teile erinevaid viise eesolevate nullide lisamiseks või hoidmiseks ja esinullide eemaldamiseks.

Juhtnullide lisamine Excelis

Põhimõtteliselt on eesolevate nullide lisamiseks kaks meetodit: üks, vormindage oma number kujul "Tekst"; teiseks kasutage eesolevate nullide lisamiseks kohandatud vormingut. Meetod, mida soovite kasutada, võib sõltuda sellest, mida soovite numbriga teha.

Kui sisestate kordumatuid ID-numbreid, kontonumbreid, sotsiaalkindlustuse numbreid või sihtkoode jne, võite soovida lisada eesoleva nulli. Kuid te ei kasuta neid numbreid arvutustes ega funktsioonides, seega on parem need teisendada. numbrid tekstiks. Te ei võtaks kunagi kokku ega keskmistaks telefoninumbreid või kontonumbreid.

Nulle saab numbrite ette lisada või polsterdada mitmel viisil, vormindades need tekstina.

Lahtri vormingu muutmine tekstiks
Apostroofi (') lisamine
Funktsiooni TEXT kasutamine
Funktsiooni REPT/LEN kasutamine
Funktsiooni CONCATENATE/Ampersand-operaatori kasutamine (&)
Funktsiooni RIGHT kasutamine

Lahtri vormingu muutmine tekstiks

See on üks lihtsamaid viise, kuidas lisada numbritele eesnulle. Kui kavatsete lihtsalt numbreid sisestada ja soovite tippimisel hoida ees olevaid nulle, on see meetod teie jaoks sobiv. Kui muudate lahtri vormingu üldisest või numbrist tekstiks, saate sundida Excelit käsitlema teie numbreid tekstiväärtustena ja kõik, mida lahtrisse sisestate, jääb täpselt samaks. Siin on, kuidas seda teha.

Valige lahter(id), millesse soovite algusnulle lisada. Minge vahekaardile "Kodu", klõpsake numbrite rühmas rippmenüüd "Vorming" ja valige vormindamisvalikutest "Tekst".

Nüüd, kui sisestate oma numbreid, ei kustuta Excel sellest ühtegi eesolevat nulli.

Lahtri vasakus ülanurgas võite näha väikest rohelist kolmnurka (veaindikaator) ja kui valite selle lahtri, kuvatakse teile hoiatusmärk, mis näitab, et olete numbri salvestanud tekstina.

Veateate eemaldamiseks valige lahter(id), klõpsake hoiatusmärgil ja seejärel valige loendist „Ignoreeri viga”.

Telefoninumbreid saate sisestada ka tühiku või sidekriipsuga numbrite vahele, Excel käsitleb neid numbreid automaatselt tekstina.

Juhtimise kasutamine Apostroof (')

Teine viis Excelis esinullide lisamiseks on lisada numbri algusesse apostroof ('). See sunnib Excelit tekstina numbrit sisestama.

Sisestage lihtsalt enne mis tahes numbrit apostroof ja vajutage sisestusklahvi. Excel jätab alguses olevad nullid puutumata, kuid (') ei ole töölehel nähtaval juhul, kui te lahtrit ei vali.

Tekstifunktsiooni kasutamine

Ülaltoodud meetod lisab numbritele nende sisestamisel nullid, kuid kui teil on juba arvude loend ja soovite nende ette asetada nullid, on funktsioon TEKST teie jaoks õige meetod. Funktsioon TEXT võimaldab teil kohandatud vormingu rakendamisel teisendada numbreid tekstistringideks.

Funktsioon TEKSTI süntaks:

= TEKST( väärtus, formaat_tekst)

kus,

väärtus - See on arvväärtus, mille peate tekstiks teisendama ja vormingut rakendama.
vormingu_tekst – on vorming, mida soovite rakendada.

Funktsiooni TEXT abil saate määrata, mitu numbrit teie number peab sisaldama. Näiteks kui soovite, et teie numbrid oleksid 8-kohalised, sisestage funktsiooni teise argumendisse 8 nulli: "00000000". Kui lahtris on 6-kohaline arv, lisab funktsioon käsitsi 2 algusnulli ja kui teil on 2-kohalised numbrid, näiteks 56, on ülejäänud nullid (00000056).

Näiteks eesolevate nullide lisamiseks ja numbrite 6-kohaliseks muutmiseks kasutage järgmist valemit:

=TEKST(A2"000000")

Kuna meil on valemi teises argumendis 6 nulli, teisendab funktsioon numbristringi tekstistringiks ja lisab 5 algust, et string oleks 6-kohaline.

Märge: Ärge unustage lisada vormingu koodid funktsioonis jutumärkidesse.

Nüüd saate rakendada sama valemit ülejäänud lahtritele, lohistades täitepidet. Nagu näete, teisendab funktsioon numbrid tekstideks ja lisab numbritele ette nullid, nii et numbrite koguarv on 6.

Funktsioon TEXT tagastab väärtuse alati tekstistringina, mitte arvuna, nii et te ei saa neid kasutada aritmeetilistes arvutustes, kuid saate neid siiski kasutada otsinguvalemites, nagu VLOOKUP või INDEX/MATCH, et hankida üksikasju toode, kasutades toote ID-sid.

Funktsiooni CONCATENATE/Ampersand operaatori kasutamine (&)

Kui soovite veerus kõigi numbrite ette lisada fikseeritud arvu esinulle, võite kasutada funktsiooni CONCATENATE või ampersandi operaatorit (&).

Funktsiooni CONCATENATE süntaks:

=CONCATENATE(tekst1, [tekst2], ...)

kus,

tekst1 – Nullide arv, mis sisestatakse numbri ette.

tekst2 – Algne number või lahtriviide

Ampersandi operaatori süntaks:

=Väärtus_1 ja Väärtus_2

Kus,

Väärtus_1 on ees olevad nullid, mis sisestatakse numbri ette, ja Väärtus_2 on arv.

Näiteks ainult kahe nulli lisamiseks numbri ette kasutage ühte järgmistest valemist:

=CONCATENATE("00",A2)

Esimene argument on kaks nulli ("00"), sest tahame sisestada kaks nulli enne A2 arvu (mis on teine argument).

või

="00"&A2

Siin on esimene argument 2 nulli, millele järgneb operaator & ja teine argument on arv.

Nagu näete, lisab valem kõigile veerus olevatele numbritele ainult kaks eesolevat nulli, olenemata sellest, mitu numbrit number sisaldab.

Mõlemad valemid ühendavad teatud arvu nulle enne algseid numbreid ja salvestavad need tekstistringidena.

Funktsiooni REPT/LEN kasutamine

Kui soovite numbrilistele või tähtnumbrilistele andmetele lisada eesnulle ja teisendada stringi tekstiks, kasutage funktsiooni REPT. Funktsiooni REPT kasutatakse märgi(de) kordamiseks teatud arv kordi. Seda funktsiooni saab kasutada ka eesolevate nullide fikseeritud arvude sisestamiseks numbri ette.

=REPT(tekst, arv_kordi)

Kus "tekst" on märk, mida tahame korrata (meie puhul "0") ja argument "number_times" on kordade arv, mitu korda tahame seda märki korrata.

Näiteks viie nulli genereerimiseks enne numbreid näeb valem välja järgmine:

=REPT(0,5)&A2

Valem kordab 5 nulli ja ühendab A2-s oleva arvujada ning tagastab tulemuse. Seejärel rakendatakse valem täitepideme abil lahtrile B2:B6.

Ülaltoodud valem lisab arvu ette fikseeritud arvu nulle, kuid arvu kogupikkus varieerub sõltuvalt arvust.

Kui soovite konkreetse tähemärgi pikkuste (fikseeritud pikkusega) stringide loomiseks lisada alguses olevaid nulle, saate kasutada funktsioone REPT ja LEN koos.

Süntaks:

=REPT(tekst, arv_kordi-LEN(tekst))&lahter

Näiteks A2 väärtusele prefikseeritud nullide lisamiseks ja 5-kohalise stringi loomiseks proovige järgmist valemit:

=REPT(0,5-LEN(A2))&A2

Siin saab 'LEN(A2)' stringi/numbrite kogupikkuse lahtris A2. „5” on stringi/numbrite maksimaalne pikkus, mis lahtris peab olema. Ja osa 'REPT(0,5-LEN(A2))' liidab nullide arvu, lahutades A2 stringi pikkuse maksimaalsest nullide arvust (5). Seejärel liidetakse arv 0-sid enne A2 väärtust, et luua fikseeritud pikkusega string.

Parema funktsiooni kasutamine

Teine viis Excelis stringi ees olevate nullide sisestamiseks on kasutada funktsiooni RIGHT.

Funktsioon RIGHT saab lisada arvu algusesse nulle ja eraldada väärtusest kõige parempoolsemad N tähemärki.

Süntaks:

= PAREM (tekst, tähemärkide_arv)

tekst on lahter või väärtus, millest soovite märgid eraldada.
tähemärkide_arv on tekstist eraldatavate märkide arv. Kui seda argumenti ei anta, ekstraheeritakse ainult esimene märk.

Selle meetodi puhul ühendame maksimaalse arvu nullid lahtriviitega, mis sisaldab stringi argumendis "tekst".

6-kohalise arvu loomiseks A-s oleva numbristringi ja eesolevate nullidega proovige järgmist valemit:

=PARE("0000000"&A2,6)

Valemi esimene argument (tekst) lisab A2 väärtusele ("0000000" ja A2) 7 nulli ja tagastab seejärel 7 parempoolseimat tähemärki, mille tulemuseks on mõned eesolevad nullid.

Esinullide lisamine kohandatud numbrivormingu abil

Kui kasutate mõnda ülaltoodud meetoditest, et asetada eesolevad nullid numbrite ette, saate alati tekstistringi, mitte numbri. Ja neist pole arvutustes ega arvvalemites palju kasu.

Parim viis Excelis esinullide lisamiseks on kohandatud numbrivormingu rakendamine. Kui lisate eesolevad nullid, lisades lahtrisse kohandatud numbrivormingu, ei muuda see lahtri väärtust, vaid ainult selle kuvamisviisi. Väärtus jääb ikkagi numbriks, mitte tekstiks.

Lahtrite numbrivormingu muutmiseks toimige järgmiselt.

Valige lahter või lahtrite vahemik, kus soovite esinulle näidata. Seejärel paremklõpsake ükskõik kus valitud vahemikus ja valige kontekstimenüüst suvand "Vorminda lahtrid". Või vajutage kiirklahve Ctrl + 1.

Lahtrite vormindamise aknas minge vahekaardile "Arv" ja valige kategooria valikute alt "Kohandatud".

Lahtris kuvatavate numbrite koguarvu määramiseks sisestage väljale „Tüüp:” nullide arv. Näiteks kui soovite, et number oleks 6-kohaline, sisestage kohandatud vormingu koodiks '000000'. Seejärel klõpsake rakendamiseks nuppu OK.

See näitab enne numbreid esinulle ja kui number on alla 6 numbri, asetatakse selle ette null.

Näib, et numbrite ees on ainult nullid, samal ajal kui alusväärtus jääb muutumatuks. Kui valite kohandatud vorminguga lahtri, kuvatakse valemiribal algne number

Kohandatud numbrivormingus saate kasutada palju digitaalseid kohahoidjaid. Kuid numbrite eesolevate nullide lisamiseks saate kasutada ainult kahte peamist kohahoidjat.

0 – See on kohatäide, mis kuvab lisanulle. See kuvab sunnitud numbrid 0–9, olenemata sellest, kas number on väärtuse jaoks asjakohane või mitte. Näiteks kui sisestate 2.5 vormingukoodiga 000.00, kuvatakse 002.50.
# – See on kohatäide, mis kuvab valikulisi numbreid ja ei sisalda lisanulle. Näiteks kui sisestate 123 vormingukoodiga 000#, kuvatakse 0123.

Samuti kuvatakse kõik kirjavahemärgid või muud tähemärgid, mille vormingukoodi lisate. Võite kasutada selliseid märke nagu sidekriips (-), koma (,), kaldkriips (/) jne.

Näiteks saate kohandatud vormingu abil numbreid telefoninumbriteks vormindada.

Dialoogiboks Vorminda kood lahtrite vormindamisel:

Tulemus:

Rakendame seda vorminduskoodi järgmises näites:

##0000

Nagu näete, lisab "0" täiendavaid nulle, samas kui "#" ei lisa tähtsusetuid nulle:

Postiindeksite, telefoninumbrite ja sotsiaalkindlustusnumbrite jaoks saate dialoogiboksi Lahtrite vormindamine jaotises Erivormingud kasutada ka eelmääratletud vormingu koode.

Järgmises tabelis on näidatud eesolevate nullidega numbreid, kus erinevatele veergudele on rakendatud erivormingu koodid:

Esinullide eemaldamine Excelis

Nüüd olete õppinud, kuidas Excelis eesnulle lisada, vaatame, kuidas stringide arvust eesnulle eemaldada. Mõnikord, kui impordite andmeid välisest allikast, võivad numbrite eesliited olla nullid ja need vormindatakse tekstina. Sellistel juhtudel peate eemaldama eesmised nullid ja teisendama need tagasi numbriteks, et saaksite neid valemites kasutada.

Excelis saate esinulle eemaldada mitmel viisil ja me näeme neid ükshaaval.

Eemaldage esinullid, muutes lahtri vormingut

Kui eesolevad nullid lisati kohandatud numbrivormingu abil, saate need hõlpsalt eemaldada, muutes lahtrite vormingut. Saate aru saada, kas teie lahtrid on kohandatud vormingus, vaadates aadressiriba (nullid on nähtavad lahtris, mitte aadressiribal).

Eesliidetega nullide eemaldamiseks valige lahtrid eesolevate nullidega, klõpsake kasti "Numbrivorming" ja valige "Üldine" või "Arv" vormingusuvand.

Nüüd on eesmised nullid kadunud:

Kustutage esinullid, teisendades teksti numbriteks

Kui teie eesolevad nullid lisati lahtri vormingu muutmise või numbrite ette apostroofide lisamisega või andmete importimisel automaatselt, on lihtsaim viis nende arvudeks teisendamiseks kasutada suvandit Error Checking. Siin on, kuidas seda teha.

Seda meetodit saate kasutada, kui teie numbrid on vasakule joondatud ja teie lahtrites on lahtrite vasakus ülanurgas väike roheline kolmnurk (veaindikaator). See tähendab, et numbrid on vormindatud tekstina.

Valige need lahtrid ja näete valiku paremas ülanurgas kollast hoiatust. Seejärel klõpsake rippmenüüs valikut „Teisenda numbriks”.

Teie nullid eemaldatakse ja numbrid teisendatakse tagasi numbrivormingusse (paremale joondatud).

Juhtnullide eemaldamine Korrutamine/jagamine 1-ga

Teine lihtne ja parim viis eesmise eemaldamiseks on arvude korrutamine või jagamine 1-ga. Väärtuse jagamine või korrutamine väärtust ei muuda, see lihtsalt teisendab väärtuse tagasi arvuks ja eemaldab eesolevad nullid.

Selleks tippige allolevas näites olev valem lahtrisse ja vajutage sisestusklahvi (ENTER). Eesolevad nullid eemaldatakse ja string teisendatakse tagasi numbriks.

Seejärel rakendage see valem täitepideme abil teistele lahtritele.

Sama tulemuse saate saavutada käsu „Kleebi spetsiaalselt” abil. Tehke järgmist.

Sisestage lahtrisse arvväärtus "1" (näiteks lahtrisse B2) ja kopeerige see väärtus.

Järgmisena valige lahtrid, millest soovite eesolevad nullid eemaldada. Seejärel paremklõpsake valikul ja seejärel valige suvand "Kleebi spetsiaalselt".

Valige dialoogiboksis Paste Special (Kleebi eriline) jaotises Toiming kas suvand „Korruta” või „Jaga” ja klõpsake nuppu „OK”.

See on kõik, teie eesmised nullid eemaldatakse, jättes stringid numbriteks.

Eemaldage juhtivad nullid valemite abil

Teine lihtne viis eesliitega nullide kustutamiseks on kasutada funktsiooni VALUE. See meetod võib olla kasulik olenemata sellest, kas teie eesolevad nullid lisati mõne muu valemi või apostroofi või kohandatud vormingu abil.

=VÄÄRTUS(A1)

Valemi argumendiks võib olla väärtus või lahtriviide, millel on väärtus. Valem eemaldab eesolevad nullid ja teisendab väärtuse tekstist numbriks. Seejärel rakendage valem ülejäänud lahtritele.

Mõnikord võite soovida eemaldada eesolevad nullid, kuid soovite säilitada numbrid tekstivormingus. Sellistel juhtudel peate kasutama funktsioone TEXT() ja VALUE () koos järgmiselt:

=TEKST(VÄÄRTUS(A1),"#")

Funktsioon VÄÄRTUS teisendab A1 väärtuse arvuks. Kuid teine argument "#" teisendab väärtuse tagasi tekstivormingusse ilma täiendavate nullideta. Selle tulemusel saate numbrid ilma eesolevate nullideta, kuid siiski tekstivormingus (vasakul joondatud).

Eemaldage eesolevad nullid, kasutades Exceli teksti veergude funktsiooni

Veel üks viis eesolevate nullide eemaldamiseks on kasutada Exceli teksti veergudeks funktsiooni.

Valige lahtrite vahemik, mille numbrid on eesolevate nullidega.

Järgmisena minge vahekaardile "Andmed" ja klõpsake rühmas Andmetööriistad nuppu "Tekst veergudesse".

Ilmub viisard „Teisenda tekst veergudeks”. 1. sammus 3-st valige "Piiratud" ja klõpsake nuppu "Järgmine".

2. sammus 3-st tühjendage kõigi eraldusmärkide märge ja klõpsake nuppu "Järgmine".

Viimases etapis jätke veeru andmevormingu suvandiks „Üldine” ja valige sihtkoht (vahemiku esimene lahter), kuhu soovite numbreid ilma eesolevate nullideta. Seejärel klõpsake nuppu "Lõpeta"

Ja numbrid koos esinumbritega eemaldatakse eraldi veerus, nagu allpool näidatud.

See on kõik.