Kaip pridėti pirmaujančius nulius „Excel“.

Šiame vadove aprašomi skirtingi nulių priekyje pridėjimo arba laikymo, taip pat priekinių nulių pašalinimo iš „Excel“ metodai.

Kai įvedate arba importuojate skaičius su vienu ar daugiau nulių priekyje, pvz., 000652, „Excel“ automatiškai pašalina tuos nulius ir langeliuose rodomas tik pats skaičius (652). Taip yra todėl, kad priešakiniai nuliai nėra būtini skaičiavimams ir neįskaitomi.

Tačiau kartais reikia tų nulių priekyje, pvz., kai įvedate ID numerius, telefonų numerius, kredito kortelių numerius, produktų kodus ar pašto kodus ir t. t. Laimei, „Excel“ siūlo kelis būdus, kaip pridėti arba išlaikyti nulius priekyje. ląstelėse. Šiame straipsnyje parodysime skirtingus būdus, kaip pridėti arba išlaikyti priekinius nulius ir pašalinti priekinius nulius.

Pirmųjų nulių pridėjimas programoje „Excel“.

Iš esmės yra 2 metodai, kuriuos galite naudoti norėdami pridėti nulius priekyje: pirmas, suformatuokite numerį kaip „Tekstas“; antra, naudokite tinkintą formatavimą, kad pridėtumėte priekinius nulius. Norimas naudoti metodas gali priklausyti nuo to, ką norite daryti su numeriu.

Įvesdami unikalius ID, sąskaitų numerius, socialinio draudimo numerius, pašto kodus ir pan., galbūt norėsite pridėti nulį priekyje. Tačiau šių skaičių nenaudosite skaičiavimams ar funkcijoms, todėl geriausia juos konvertuoti. skaičiai į tekstą. Niekada nesuminėsite ir nesuminėsite telefono numerių ar sąskaitų numerių.

Yra keletas būdų, kaip galite pridėti nulius prieš skaičius, formatuodami juos kaip tekstą:

Ląstelių formato keitimas į Tekstas
Apostrofo pridėjimas (')
Naudojant funkciją TEXT
Naudojant funkciją REPT/LEN
Naudokite CONCATENATE funkciją / ampersand operatorių (&)
Naudojant RIGHT funkciją

Langelio formato keitimas į tekstą

Tai vienas iš paprasčiausių būdų prie skaičių pridėti nulius priekyje. Jei tik ketinate įvesti skaičius ir rašydami norite palikti nulius priekyje, šis metodas jums tinka. Pakeitę langelio formatą iš Bendra arba Skaičius į Tekstas, galite priversti „Excel“ traktuoti jūsų skaičius kaip teksto reikšmes ir viskas, ką įvesite langelyje, išliks lygiai taip pat. Štai kaip tai padaryti:

Pasirinkite langelį (-ius), kuriame (-iuose) norite pridėti nulius priekyje. Eikite į skirtuką „Pagrindinis“, numerių grupėje spustelėkite išskleidžiamąjį laukelį „Formatas“ ir formatavimo parinktyse pasirinkite „Tekstas“.

Dabar, kai įvedate skaičius, „Excel“ iš jo neištrins jokio nulio priekyje.

Viršutiniame kairiajame langelio kampe galite matyti mažą žalią trikampį (klaidos indikatorius), o kai pasirinksite tą langelį, bus parodytas įspėjamasis ženklas, nurodantis, kad numerį išsaugojote kaip tekstą.

Norėdami pašalinti klaidos pranešimą, pasirinkite langelį (-es), spustelėkite įspėjamąjį ženklą ir sąraše pasirinkite „Ignoruoti klaidą“.

Taip pat galite įvesti telefono numerius su tarpu arba brūkšneliu tarp skaičių, „Excel“ šiuos skaičius automatiškai laikys tekstu.

Naudojant Leading Apostrofas (')

Kitas būdas pridėti priekinius nulius „Excel“ yra pridėti apostrofą (') skaičiaus pradžioje. Tai privers Excel įvesti skaičių kaip tekstą.

Tiesiog įveskite apostrofą prieš bet kokius skaičius ir paspauskite „Enter“. „Excel“ paliks pirmuosius nulius nepažeistus, bet (') nebus matomas darbalapyje, nebent pasirinksite langelį.

Teksto funkcijos naudojimas

Aukščiau pateiktas metodas prideda nulius prie skaičių, kai juos įvedate, bet jei jau turite skaičių sąrašą ir norite prieš juos įrašyti nulius, tada funkcija TEKSTAS yra jums tinkamas metodas. Funkcija TEKSTAS leidžia konvertuoti skaičius į teksto eilutes taikant pasirinktinį formatavimą.

Funkcija TEKSTO sintaksė:

= TEKSTAS( reikšmė, format_tekstas)

kur,

vertė – Tai skaitinė reikšmė, kurią reikia konvertuoti į tekstą ir pritaikyti formatavimą.
format_text – yra formatas, kurį norite taikyti.

Naudodami funkciją TEXT galite nurodyti, kiek skaitmenų jūsų numeris turi turėti. Pavyzdžiui, jei norite, kad jūsų skaičiai būtų 8 skaitmenų ilgio, antrame funkcijos argumente įveskite 8 nulius: „00000000“. Jei langelyje yra 6 skaitmenų skaičius, funkcija rankiniu būdu pridės 2 pirmuosius nulius, o jei turite 2 skaitmenų skaičius, pvz., 56, likusieji bus nuliai (00000056).

Pavyzdžiui, norėdami pridėti priekinius nulius ir padaryti skaičius 6 skaitmenų ilgio, naudokite šią formulę:

=TEKSTAS(A2"000000")

Kadangi antrajame formulės argumente turime 6 nulius, funkcija skaičių eilutę paverčia teksto eilute ir prideda 5 priekinius nulius, kad eilutė būtų 6 skaitmenų ilgio.

Pastaba: Nepamirškite funkcijoje pateikti formatų kodus dvigubose kabutėse.

Dabar galite taikyti tą pačią formulę likusioms ląstelėms vilkdami užpildymo rankenėlę. Kaip matote, funkcija konvertuoja skaičius į tekstus ir prie skaičių prideda nulius, kad bendras skaitmenų skaičius būtų 6.

Funkcija TEXT visada pateiks reikšmę kaip teksto eilutę, o ne skaičių, todėl negalėsite jų naudoti aritmetiniuose skaičiavimuose, bet vis tiek galėsite jas naudoti paieškos formulėse, pvz., VLOOKUP arba INDEX/MATCH, norėdami gauti išsamią informaciją apie produktas naudojant produkto ID.

Funkcijos CONCATENATE naudojimas / ampersand operatorius (&)

Jei prieš visus skaičius stulpelyje norite pridėti fiksuotą skaičių pirmųjų nulių, galite naudoti funkciją CONCATENATE arba ampersando operatorių (&).

Funkcijos CONCATENATE sintaksė:

=SUJUNGTI(tekstas1, [tekstas2], ...)

kur,

1 tekstas – Nulių skaičius, kuris turi būti įterptas prieš skaičių.

2 tekstas - Pradinis numeris arba langelio nuoroda

Ampersand operatoriaus sintaksė:

=Vertė_1 ir vertė_2

Kur,

Reikšmė_1 yra pirmieji nuliai, įterpti prieš skaičių, o Reikšmė_2 yra skaičius.

Pavyzdžiui, jei prieš skaičių norite pridėti tik du nulius, naudokite vieną iš šių formulių:

=SUJUNGTI("00", A2)

Pirmasis argumentas yra du nuliai („00“), nes norime įrašyti du nulius prieš skaičių A2 (kuris yra antrasis argumentas).

arba

="00"&A2

Čia pirmasis argumentas yra 2 nuliai, po kurio seka „&“ operatorius, o antrasis argumentas yra skaičius.

Kaip matote, formulė prie visų stulpelio skaičių prideda tik du nulius, neatsižvelgiant į tai, kiek skaitmenų yra skaičiuje.

Abi šios formulės sujungia tam tikrą skaičių nulių prieš pradinius skaičius ir išsaugo juos kaip teksto eilutes.

Naudojant funkciją REPT/LEN

Jei prie skaitinių ar raidinių ir skaitmeninių duomenų norite pridėti nulius ir konvertuoti eilutę į tekstą, naudokite funkciją REPT. Funkcija REPT naudojama simboliui (-iams) pakartoti tam tikrą skaičių kartų. Šią funkciją taip pat galima naudoti norint įterpti fiksuotus nulių priekyje skaičius prieš skaičių.

=REPT(tekstas, skaičius_kartų)

Kur „tekstas“ yra simbolis, kurį norime pakartoti (mūsų atveju „0“), o argumentas „number_times“ yra skaičius, kiek kartų norime pakartoti tą simbolį.

Pavyzdžiui, norint sugeneruoti penkis nulius prieš skaičius, formulė atrodytų taip:

=REPT(0,5)&A2

Formulė pakartoja 5 nulius ir sujungia skaičių eilutę A2 ir grąžina rezultatą. Tada formulė pritaikoma langeliui B2:B6 naudojant užpildymo rankenėlę.

Aukščiau pateikta formulė prideda fiksuotą skaičių nulių prieš skaičių, tačiau bendras skaičiaus ilgis skiriasi priklausomai nuo skaičiaus.

Jei norite pridėti priekinių nulių, kur reikia sukurti konkretaus simbolio ilgio (fiksuoto ilgio) eilutes, galite naudoti REPT ir LEN funkcijas kartu.

Sintaksė:

=REPT(tekstas, skaičius_kartų-LEN(tekstas))&ląstelė

Pavyzdžiui, norėdami pridėti priešdėlinius nulius prie reikšmės A2 ir sudaryti 5 simbolių eilutę, išbandykite šią formulę:

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

Čia „LEN(A2)“ gauna bendrą eilutės / skaičių ilgį langelyje A2. „5“ yra didžiausias langelio eilutės / skaičių ilgis. O dalis „REPT(0,5-LEN(A2))“ prideda nulių skaičių, atimdama eilutės ilgį A2 iš didžiausio nulių skaičiaus (5). Tada prieš A2 reikšmę sujungiamas skaičius 0, kad būtų sudaryta fiksuoto ilgio eilutė.

Naudojant RIGHT funkciją

Kitas būdas įrašyti nulius prieš eilutę programoje „Excel“ yra naudoti TEISINGĄ funkciją.

Funkcija RIGHT gali pridėti nulių skaičių prie skaičiaus pradžios ir iš reikšmės ištraukti dešiniausius N simbolius.

Sintaksė:

= RIGHT (tekstas, simbolių_skaičius)

tekstą yra langelis arba reikšmė, iš kurios norite išskirti simbolius.
simbolių skaičius yra simbolių, kuriuos reikia ištraukti iš teksto, skaičius. Jei šis argumentas nepateikiamas, bus išgaunamas tik pirmasis simbolis.

Taikant šį metodą, didžiausią nulių skaičių sujungiame su langelio nuoroda, kurioje yra eilutė argumente „tekstas“.

Norėdami sukurti 6 skaitmenų skaičių pagal skaičių eilutę A su nuliais priekyje, išbandykite šią formulę:

= RIGHT("0000000"&A2,6)

Pirmasis formulės argumentas (tekstas) prideda 7 nulius prie vertės A2 („0000000“ ir A2), o tada grąžina 7 kraštus dešinėje esančius simbolius, dėl kurių atsiranda keletas nulių.

Pridedant nulius naudojant pasirinktinį skaičių formatavimą

Jei naudosite bet kurį iš aukščiau pateiktų metodų, kad prieš skaičius įvestumėte nulius, visada gausite teksto eilutę, o ne skaičių. Ir jie nebus labai naudingi atliekant skaičiavimus ar skaitmenines formules.

Geriausias būdas pridėti nulius priekyje „Excel“ yra pritaikyti tinkintą skaičių formatavimą. Jei priekyje pridedate nulius prie langelio pridėdami tinkintą skaičių formatą, tai nepakeičia langelio reikšmės, o tik jos rodymo būdas. Reikšmė vis tiek išliks kaip skaičius, o ne tekstas.

Norėdami pakeisti langelių skaičių formatavimą, atlikite šiuos veiksmus:

Pasirinkite langelį arba langelių diapazoną, kuriame norite rodyti priekinius nulius. Tada dešiniuoju pelės mygtuku spustelėkite bet kurią pasirinkto diapazono vietą ir kontekstiniame meniu pasirinkite parinktį „Formatuoti langelius“. Arba paspauskite sparčiuosius klavišus Ctrl + 1.

Lange Format Cells eikite į skirtuką „Skaičius“ ir kategorijos parinktyse pasirinkite „Custom“.

Įveskite nulių skaičių laukelyje „Tipas:“, kad nurodytumėte bendrą skaitmenų, kuriuos norite rodyti langelyje, skaičių. Pavyzdžiui, jei norite, kad skaičius būtų 6 skaitmenų ilgio, kaip pasirinktinio formato kodą įveskite „000000“. Tada spustelėkite „Gerai“, kad pritaikytumėte.

Prieš skaičius bus rodomi nuliai, o jei skaičius yra mažesnis nei 6 skaitmenys, prieš jį įrašomas nulis.

Atrodo, kad skaičiai turi tik nulius priekyje, o pagrindinė vertė išliks nepakitusi. Jei pasirinksite langelį su tinkintu formatavimu, formulės juostoje bus rodomas pradinis skaičius

Yra daug skaitmeninių vietos rezervavimo ženklų, kuriuos galite naudoti pasirinktame skaičių formate. Tačiau yra tik du pirminiai vietos rezervavimo ženklai, kuriuos galite naudoti pridedant priekinius skaičių nulius.

0 – Tai yra skaitmenų rezervuota vieta, kuri rodo papildomus nulius. Rodomi priverstiniai skaitmenys nuo 0 iki 9, nesvarbu, ar skaitmuo yra susijęs su verte, ar ne. Pavyzdžiui, jei įvesite 2.5 su formato kodu 000.00, bus rodomas 002.50.
# – Tai yra skaitmenų rezervuota vieta, kurioje rodomi pasirenkami skaitmenys ir neįtraukiami papildomi nuliai. Pavyzdžiui, jei įvesite 123 su formatavimo kodu 000#, bus rodomas 0123.

Be to, bet koks skyrybos ženklas ar kitas simbolis, kurį įtraukėte į formato kodą, bus rodomas toks, koks yra. Galite naudoti tokius simbolius kaip brūkšnelis (-), kablelis (,), pasvirasis brūkšnys (/) ir kt.

Pavyzdžiui, taip pat galite formatuoti numerius kaip telefono numerius naudodami pasirinktinį formatą.

Formatavimo kodo dialogo lange Format Cells:

Rezultatas:

Taikykime šį formatavimo kodą šiame pavyzdyje:

##0000

Kaip matote, „0“ pridės papildomų nulių, o „#“ nepridės nereikšmingų nulių:

Taip pat galite naudoti iš anksto nustatytų formatų kodus, esančius dialogo lango Formatuoti langelius skiltyje „Specialūs formatai“, skirtą pašto kodams, telefono numeriams ir socialinio draudimo numeriams.

Šioje lentelėje rodomi skaičiai su nuliais priekyje, kai skirtingiems stulpeliams taikomi skirtingi specialiojo formato kodai:

„Excel“ pirmaujančių nulių pašalinimas

Dabar jūs išmokote pridėti priekinius nulius programoje „Excel“, pažiūrėkime, kaip pašalinti priekinius nulius iš eilučių skaičiaus. Kartais, kai importuojate duomenis iš išorinio šaltinio, skaičiai gali turėti priešdėlį nuliai ir gali būti suformatuoti kaip tekstas. Tokiais atvejais turite pašalinti pirmuosius nulius ir konvertuoti juos atgal į skaičius, kad galėtumėte juos naudoti formulėse.

Yra įvairių būdų, kaip pašalinti pirmuosius nulius programoje „Excel“, ir mes juos matysime po vieną.

Pašalinkite pirmaujančius nulius pakeisdami langelio formatavimą

Jei nuliai priekyje buvo pridėti naudojant pasirinktinį skaičių formatavimą, galite lengvai juos pašalinti pakeisdami langelių formatą. Galite sužinoti, ar langeliai yra suformatuoti pasirinktinai, žiūrėdami į adreso juostą (nuliai bus matomi langelyje, o ne adreso juostoje).

Norėdami pašalinti nulius su priešdėliu, pažymėkite langelius su nuliais priekyje, spustelėkite laukelį „Skaičiaus formatas“ ir pasirinkite „Bendra“ arba „Skaičių“ formatavimo parinktį.

Dabar pirmųjų nulių nebėra:

Ištrinkite pirminius nulius konvertuodami tekstą į skaičius

Jei nuliai priekyje buvo pridėti pakeitus langelio formatą, prieš skaičius pridedant apostrofus arba automatiškai pridėjus importuojant duomenis, paprasčiausias būdas juos konvertuoti į skaičius yra naudoti klaidų tikrinimo parinktį. Štai kaip tai padaryti:

Šį metodą galite naudoti, jei jūsų skaičiai sulygiuoti kairėje, o langelių viršutiniame kairiajame kampe yra mažas žalias trikampis (klaidos indikatorius). Tai reiškia, kad skaičiai suformatuoti kaip tekstas.

Pasirinkite tuos langelius ir viršutinėje dešinėje pasirinkimo dalyje pamatysite geltoną įspėjimą. Tada išskleidžiamajame meniu spustelėkite parinktį „Konvertuoti į skaičių“.

Nuliai bus pašalinti, o skaičiai bus konvertuoti atgal į skaičių formatą (sulygiuoti dešinėje).

Pirmaujančių nulių pašalinimas Padauginti / padalyti iš 1

Kitas paprastas ir geriausias būdas pašalinti pirmąją vietą yra padauginti arba padalyti skaičius iš 1. Padalijus ar padauginus reikšmę reikšmė nekeičiama, ji tiesiog paverčia reikšmę atgal į skaičių ir pašalina pirmuosius nulius.

Norėdami tai padaryti, langelyje įveskite formulę toliau pateiktame pavyzdyje ir paspauskite ENTER. Pirmieji nuliai bus pašalinti, o eilutė vėl bus konvertuota į skaičių.

Tada pritaikykite šią formulę kitiems langeliams naudodami užpildymo rankenėlę.

Tų pačių rezultatų galite pasiekti naudodami komandą „Įklijuoti specialią“. Štai kaip:

Įveskite „1“ skaitinę reikšmę langelyje (tarkime, B2) ir nukopijuokite šią reikšmę.

Tada pasirinkite langelius, kuriuose norite pašalinti priešakinius nulius. Tada dešiniuoju pelės mygtuku spustelėkite pasirinkimą ir pasirinkite parinktį „Įklijuoti specialų“.

Dialogo lango Specialiojo įklijavimo skiltyje Operacija pasirinkite parinktį „Dauginti“ arba „Padalyti“ ir spustelėkite „Gerai“.

Štai viskas, jūsų pirmaujantys nuliai bus pašalinti, paliekant eilutes kaip skaičius.

Pašalinkite pirmaujančius nulius naudodami formules

Kitas paprastas būdas ištrinti nulius su priešdėliu yra naudoti funkciją VALUE. Šis metodas gali būti naudingas, nesvarbu, ar pirmieji nuliai buvo pridėti naudojant kitą formulę, apostrofą arba pasirinktinį formatavimą.

=VERTĖ(A1)

Formulės argumentas gali būti reikšmė arba langelio nuoroda, kuri turi reikšmę. Formulė pašalina priešakinius nulius ir paverčia reikšmę iš teksto į skaičių. Tada pritaikykite formulę likusioms ląstelėms.

Kartais galbūt norėsite pašalinti priešakinius nulius, bet norite palikti skaičius teksto formatu. Tokiais atvejais funkcijas TEXT() ir VALUE () turite naudoti kartu taip:

=TEKSTAS(VERTĖ(A1),"#")

Funkcija VERTĖ konvertuoja A1 reikšmę į skaičių. Tačiau antrasis argumentas „#“ konvertuoja reikšmę atgal į teksto formatą be jokių papildomų nulių. Dėl to jūs gausite skaičius be nulių priekyje, bet vis tiek teksto formatu (sulygiuotas kairėje).

Pašalinkite pirmuosius nulius naudodami „Excel“ teksto į stulpelius funkciją

Dar vienas būdas pašalinti nulius priekyje yra naudoti „Excel“ teksto į stulpelius funkciją.

Pasirinkite langelių diapazoną, kuriame yra skaičiai su nuliais priekyje.

Tada eikite į skirtuką „Duomenys“ ir duomenų įrankių grupėje spustelėkite mygtuką „Tekstas į stulpelius“.

Pasirodys vedlys „Konvertuoti tekstą į stulpelius“. 1 veiksme iš 3 pasirinkite „Apribota“ ir spustelėkite „Kitas“.

2 veiksme iš 3 panaikinkite visų skirtukų žymėjimą ir spustelėkite „Kitas“.

Paskutiniame etape palikite stulpelio duomenų formato parinktį „Bendra“ ir pasirinkite paskirties vietą (pirmąjį diapazono langelį), kur norite gauti skaičių be nulių priekyje. Tada spustelėkite „Baigti“

Ir jūs gausite skaičius su pirmaujančiais, pašalintais atskirame stulpelyje, kaip parodyta žemiau.

Tai viskas.