Kā programmā Excel pievienot vadošās nulles

Šajā apmācībā ir aplūkotas dažādas metodes, kā pievienot vai saglabāt sākuma nulles, kā arī noņemt sākuma nulles programmā Excel.

Ikreiz, kad ievadāt vai importējat skaitļus ar vienu vai vairākām nullēm sākumā, piemēram, 000652, programma Excel automātiski noņem šīs nulles, un šūnās tiek rādīts tikai pats skaitlis (652). Tas ir tāpēc, ka sākuma nulles nav nepieciešamas aprēķiniem un netiek skaitītas.

Tomēr ir gadījumi, kad ir nepieciešamas šīs sākuma nulles, piemēram, ievadot ID numurus, tālruņu numurus, kredītkaršu numurus, produktu kodus vai pasta indeksus utt. Par laimi, programmā Excel ir vairāki veidi, kā pievienot vai paturēt sākuma nulles. šūnās. Šajā rakstā mēs parādīsim dažādus veidus, kā pievienot vai saglabāt sākuma nulles un noņemt sākuma nulles.

Vadošo nulles pievienošana programmā Excel

Būtībā ir 2 metodes, kuras varat izmantot, lai pievienotu sākuma nulles: viena, formatējiet savu numuru kā "Teksts"; otrkārt, izmantojiet pielāgotu formatējumu, lai pievienotu sākuma nulles. Metode, kuru vēlaties izmantot, var būt atkarīga no tā, ko vēlaties darīt ar numuru.

Ievadot unikālus ID numurus, konta numurus, sociālās apdrošināšanas numurus vai pasta indeksus utt., iespējams, vēlēsities pievienot nulli sākumā. Taču jūs neizmantosiet šos skaitļus aprēķiniem vai funkcijās, tāpēc vislabāk tos konvertēt. skaitļi tekstam. Jūs nekad nesaskaitītu vai vidējos tālruņu numurus vai kontu numurus.

Ir vairāki veidi, kā pirms skaitļiem pievienot vai papildināt nulles, formatējot tos kā tekstu.

• Šūnas formāta maiņa uz Teksts
• Apostrofa (') pievienošana
• Izmantojot funkciju TEXT
• Izmantojot funkciju REPT/LEN
• Izmantojiet CONCATENATE funkciju/operatoru (&)
• Izmantojot RIGHT funkciju

Šūnas formāta maiņa uz tekstu

Šis ir viens no vienkāršākajiem veidiem, kā skaitļiem pievienot sākuma nulles. Ja jūs tikai gatavojaties ievadīt ciparus un rakstīšanas laikā vēlaties saglabāt nulles sākumā, šī metode ir piemērota jums. Mainot šūnas formātu no Vispārīgs vai Skaitlis uz Teksts, varat piespiest programmu Excel apstrādāt jūsu skaitļus kā teksta vērtības, un viss, ko ierakstāt šūnā, paliks tieši tāds pats. Lūk, kā to izdarīt:

Atlasiet šūnu(-es), kurām vēlaties pievienot sākuma nulles. Dodieties uz cilni “Sākums”, numuru grupā noklikšķiniet uz nolaižamā lodziņa “Formāts” un formāta opcijās atlasiet “Teksts”.

Tagad, kad ierakstāt skaitļus, programma Excel no tā neizdzēsīs nevienu sākuma nulli.

Iespējams, šūnas augšējā kreisajā stūrī būs redzams neliels zaļš trīsstūris (kļūdas indikators), un, atlasot šo šūnu, tiks parādīta brīdinājuma zīme, kas norāda, ka numurs ir saglabāts kā teksts.

Lai noņemtu kļūdas ziņojumu, atlasiet šūnu(-es), noklikšķiniet uz brīdinājuma zīmes un pēc tam sarakstā atlasiet “Ignorēt kļūdu”.

Varat arī ierakstīt tālruņu numurus ar atstarpi vai defisi starp cipariem, programma Excel šos numurus automātiski apstrādās kā tekstu.

Izmantojot Leading Apostrofs (')

Vēl viens veids, kā programmā Excel pievienot sākuma nulles, ir pievienot apostrofu (') skaitļa sākumā. Tas piespiedīs Excel ievadīt skaitli kā tekstu.

Vienkārši ierakstiet apostrofu pirms jebkura skaitļa un nospiediet taustiņu "Enter". Programma Excel atstās neskartas sākuma nulles, bet (') darblapā nebūs redzama tikai tad, ja atlasīsit šūnu.

Izmantojot teksta funkciju

Iepriekš minētā metode skaitļiem pievieno nulles, kad tos ierakstāt, taču, ja jums jau ir skaitļu saraksts un pirms tiem vēlaties ievietot nulles, funkcija TEKSTS ir jums piemērotākā metode. Funkcija TEKSTS ļauj pārvērst skaitļus teksta virknēs, vienlaikus piemērojot pielāgotu formatējumu.

Funkcija TEKSTA sintakse:

= TEKSTS(vērtība, format_teksts)

kur,

• vērtība - Tā ir skaitliskā vērtība, kas jāpārvērš tekstā un jāpiemēro formatējums.
• format_text – ir formāts, kuru vēlaties lietot.

Izmantojot funkciju TEKSTS, varat norādīt, cik cipariem jābūt jūsu numuram. Piemēram, ja vēlaties, lai jūsu skaitļi būtu 8 ciparus gari, otrajā funkcijas argumentā ierakstiet 8 nulles: “00000000”. Ja šūnā ir 6 ciparu skaitlis, funkcija manuāli pievienos 2 sākuma nulles un, ja jums ir 2 ciparu skaitļi, piemēram, 56, pārējās būs nulles (00000056).

Piemēram, lai pievienotu sākuma nulles un padarītu skaitļus 6 ciparu garus, izmantojiet šo formulu:

=TEKSTS(A2"000000")

Tā kā formulas otrajā argumentā mums ir 6 nulles, funkcija pārvērš skaitļu virkni teksta virknē un pievieno 5 sākuma nulles, lai virkne būtu 6 ciparu gara.

Piezīme: Lūdzu, neaizmirstiet formāta kodus funkcijā ievietot dubultpēdiņās.

Tagad to pašu formulu varat lietot pārējām šūnām, velkot aizpildīšanas turi. Kā redzat, funkcija pārvērš skaitļus tekstos un pievieno skaitļiem sākuma nulles, lai kopējais ciparu skaits būtu 6.

Funkcija TEXT vienmēr atgriezīs vērtību kā teksta virkni, nevis skaitli, tāpēc jūs nevarēsit tos izmantot aritmētiskajos aprēķinos, taču joprojām varat tos izmantot uzmeklēšanas formulās, piemēram, VLOOKUP vai INDEX/MATCH, lai iegūtu informāciju par produkts, izmantojot produktu ID.

Funkcijas CONCATENATE/operatora & (&) izmantošana

Ja pirms visiem skaitļiem kolonnā vēlaties pievienot fiksētu skaitu sākuma nulles, varat izmantot funkciju CONCATENATE vai operatoru &.

Funkcijas CONCATENATE sintakse:

=Savienot(teksts1, [teksts2], ...)

kur,

teksts1 - Nulles skaits, kas jāievieto pirms skaitļa.

teksts2 — Sākotnējais numurs vai šūnas atsauce

Ampersand operatora sintakse:

=Vērtība_1 un Vērtība_2 

Kur,

Vērtība_1 ir sākuma nulles, kas jāievieto pirms skaitļa, un Vērtība_2 ir skaitlis.

Piemēram, lai pirms skaitļa pievienotu tikai divas nulles, izmantojiet kādu no šīm formulām:

=Savienot("00",A2)

Pirmais arguments ir divas nulles (“00”), jo mēs vēlamies aizpildīt divas nulles pirms skaitļa A2 (kas ir otrais arguments).

vai,

="00"&A2

Šeit pirmais arguments ir 2 nulles, kam seko operators “&”, bet otrais arguments ir skaitlis.

Kā redzat, formula pievieno tikai divas sākuma nulles visiem cipariem kolonnā neatkarīgi no tā, cik ciparu skaitlis satur.

Abas šīs formulas savieno noteiktu skaitu nulles pirms sākotnējiem skaitļiem un saglabā tās kā teksta virknes.

Izmantojot funkciju REPT/LEN

Ja vēlaties pievienot sākuma nulles ciparu vai burtciparu datiem un pārvērst virkni tekstā, izmantojiet funkciju REPT. Funkciju REPT izmanto, lai atkārtotu rakstzīmi(-es) noteiktu skaitu reižu. Šo funkciju var izmantot arī, lai pirms skaitļa ievietotu fiksētus skaitļus ar nullēm.

=REPT(teksts, skaits_reizes)

Kur “teksts” ir rakstzīme, kuru vēlamies atkārtot (mūsu gadījumā “0”), un arguments “number_times” ir to reižu skaits, cik mēs vēlamies šo rakstzīmi atkārtot.

Piemēram, lai ģenerētu piecas nulles pirms skaitļiem, formula izskatītos šādi:

=REPT(0,5)&A2

Formula atkārto 5 nulles un savieno skaitļu virkni laukā A2 un atgriež rezultātu. Pēc tam formula tiek lietota šūnai B2:B6, izmantojot aizpildīšanas turi.

Iepriekš minētā formula pievieno fiksētu nulles skaitu pirms skaitļa, bet skaitļa kopējais garums mainās atkarībā no skaitļa.

Ja vēlaties pievienot sākuma nulles, lai izveidotu noteiktas rakstzīmes garas (noteikta garuma) virknes, varat izmantot funkcijas REPT un LEN kopā.

Sintakse:

=REPT(teksts, skaits_reizes-LEN(teksts))&šūna

Piemēram, lai A2 vērtībai pievienotu nulles ar prefiksu un izveidotu 5 rakstzīmju garu virkni, izmēģiniet šo formulu:

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

Šeit “LEN(A2)” iegūst kopējo virknes/skaitļu garumu šūnā A2. “5” ir maksimālais virknes/skaitļu garums šūnā. Un daļa “REPT(0,5-LEN(A2))” pievieno nulles skaitu, no maksimālā nulles skaita (5) atņemot A2 virknes garumu. Pēc tam pirms A2 vērtības tiek savienots skaitlis 0, lai izveidotu fiksēta garuma virkni.

Izmantojot RIGHT funkciju

Vēl viens veids, kā programmā Excel ievietot nulles pirms virknes, ir izmantot RIGHT funkciju.

Funkcija RIGHT var pievienot nulles skaitļa sākumam un izvilkt no vērtības vistālāk esošās N rakstzīmes.

Sintakse:

= RIGHT (teksts, rakstzīmju_skaits)

• tekstu ir šūna vai vērtība, no kuras vēlaties iegūt rakstzīmes.
• rakstzīmju_skaits ir rakstzīmju skaits, kas jāizņem no teksta. Ja šis arguments nav norādīts, tiks izvilkta tikai pirmā rakstzīme.

Šai metodei mēs savienojam maksimālo nulles skaitu ar šūnas atsauci, kas satur virkni argumentā “teksts”.

Lai izveidotu 6 ciparu skaitli, pamatojoties uz skaitļu virkni A ar nullēm sākumā, izmēģiniet šo formulu:

=LABI("0000000"&A2,6)

Pirmais formulas arguments (teksts) pievieno 7 nulles A2 vērtībai (“0000000” un A2) un pēc tam atgriež 7 galējās labās rakstzīmes, kā rezultātā tiek parādītas dažas nulles.

Galvenās nulles pievienošana, izmantojot pielāgotu skaitļu formatējumu

Ja izmantojat kādu no iepriekš minētajām metodēm, lai pirms skaitļiem liktu nulles, jūs vienmēr iegūsit teksta virkni, nevis ciparu. Un tie nebūs īpaši noderīgi aprēķinos vai skaitliskās formulās.

Labākais veids, kā programmā Excel pievienot sākuma nulles, ir lietot pielāgotu skaitļu formatējumu. Ja pievienojat sākuma nulles, pievienojot šūnai pielāgotu skaitļu formātu, tas nemaina šūnas vērtību, bet tikai tās attēlošanas veidu. Vērtība joprojām paliks kā skaitlis, nevis teksts.

Lai mainītu šūnu skaitļu formatējumu, rīkojieties šādi:

Atlasiet šūnu vai šūnu diapazonu, kurā vēlaties rādīt sākuma nulles. Pēc tam ar peles labo pogu noklikšķiniet jebkurā vietā atlasītajā diapazonā un konteksta izvēlnē atlasiet opciju Formatēt šūnas. Vai arī nospiediet īsinājumtaustiņus Ctrl + 1.

Šūnu formatēšanas logā dodieties uz cilni “Numurs” un sadaļā “Kategorijas opcijas” atlasiet “Pielāgots”.

Lodziņā “Tips:” ievadiet nulles, lai norādītu kopējo ciparu skaitu, ko vēlaties rādīt šūnā. Piemēram, ja vēlaties, lai skaitlis būtu 6 ciparus garš, ievadiet “000000” kā pielāgotā formāta kodu. Pēc tam noklikšķiniet uz “Labi”, lai pieteiktos.

Tas parādīs sākuma nulles pirms cipariem, un, ja skaitlis ir mazāks par 6 cipariem, pirms tā tiks ievietota nulle.

Šķiet, ka skaitļiem ir tikai nulles, kamēr pamatā esošā vērtība nemainīsies. Ja atlasāt šūnu ar pielāgotu formatējumu, formulas joslā tiks parādīts sākotnējais numurs

Ir daudz digitālo vietturu, ko varat izmantot savā pielāgotajā skaitļu formātā. Taču ir tikai divi primārie vietturi, kurus varat izmantot, lai pievienotu skaitļos esošās nulles.

• 0 – Tas ir ciparu vietturis, kas parāda papildu nulles. Tas parāda piespiedu ciparus no 0 līdz 9 neatkarīgi no tā, vai cipars attiecas uz vērtību. Piemēram, ja ierakstāt 2.5 ar formāta kodu 000.00, tiks parādīts 002.50.
• # – Tas ir ciparu vietturis, kas parāda izvēles ciparus un neietver papildu nulles. Piemēram, ja ierakstāt 123 ar formatēšanas kodu 000#, tiks parādīts 0123.

Turklāt visas pieturzīmes vai citas rakstzīmes, kuras iekļaujat formāta kodā, tiks parādītas tādas, kādas tās ir. Varat izmantot tādas rakstzīmes kā defise (-), komats (,), slīpsvītra (/) utt.

Piemēram, varat arī formatēt numurus kā tālruņa numurus, izmantojot pielāgotu formātu.

Formatēšanas koda dialoglodziņš Šūnu formatēšana:

Rezultāts:

Izmantosim šo formatēšanas kodu šādā piemērā:

##0000

Kā redzat, “0” pievienos papildu nulles, savukārt “#” nepievieno nenozīmīgas nulles:

Varat arī izmantot iepriekš definētu formātu kodus pasta indeksiem, tālruņu numuriem un sociālās apdrošināšanas numuriem dialoglodziņa Šūnu formatēšana sadaļā Īpašie formāti.

Šajā tabulā ir parādīti skaitļi ar nullēm sākumā, kur dažādām kolonnām tiek lietoti dažādi “Īpašā” formāta kodi:

Galvenās nulles noņemšana programmā Excel

Tagad esat iemācījies pievienot sākuma nulles programmā Excel, redzēsim, kā noņemt sākuma nulles no virkņu skaita. Dažreiz, importējot datus no ārēja avota, skaitļiem var būt nulles prefikss un tie var tikt formatēti kā teksts. Šādos gadījumos jums ir jānoņem sākuma nulles un jāpārvērš tās atpakaļ skaitļos, lai tās varētu izmantot formulās.

Ir dažādi veidi, kā programmā Excel noņemt sākuma nulles, un mēs tās redzēsim pa vienam.

Noņemiet vadošās nulles, mainot šūnu formatējumu

Ja sākuma nulles tika pievienotas, izmantojot pielāgotu skaitļu formatējumu, varat tās viegli noņemt, mainot šūnu formātu. Varat noteikt, vai jūsu šūnas ir pielāgotas formatējumam, apskatot adreses joslu (nulles būs redzamas šūnā, nevis adreses joslā).

Lai noņemtu prefiksa nulles, atlasiet šūnas ar nullēm sākumā, noklikšķiniet uz lodziņa "Numura formāts" un atlasiet formatēšanas opciju "Vispārīgi" vai "Numurs".

Tagad sākuma nulles ir pazudušas:

Izdzēsiet galvenās nulles, pārvēršot tekstu skaitļos

Ja sākuma nulles tika pievienotas, mainot šūnas formātu vai pievienojot apostrofus pirms skaitļiem vai automātiski pievienotas, importējot datus, vienkāršākais veids, kā pārvērst tos skaitļos, ir izmantot kļūdu pārbaudes opciju. Lūk, kā to izdarīt:

Varat izmantot šo metodi, ja jūsu skaitļi ir līdzināti pa kreisi un jūsu šūnām ir mazs zaļš trīsstūris (kļūdas indikators) šūnu augšējā kreisajā stūrī. Tas nozīmē, ka skaitļi ir formatēti kā teksts.

Atlasiet šīs šūnas, un atlases augšējā labajā stūrī redzēsit dzeltenu brīdinājumu. Pēc tam nolaižamajā izvēlnē noklikšķiniet uz opcijas “Konvertēt uz numuru”.

Jūsu nulles tiks noņemtas, un skaitļi tiks konvertēti atpakaļ skaitļu formātā (līdzināti pa labi).

Noņemot vadošās nulles ar Reiziniet/daliet ar 1

Vēl viens vienkāršs un labākais veids, kā noņemt sākumpunktu, ir skaitļu reizināšana vai dalīšana ar 1. Vērtības dalīšana vai reizināšana nemaina vērtību, tā vienkārši pārvērš vērtību atpakaļ par skaitli un noņem sākuma nulles.

Lai to izdarītu, šūnā ierakstiet formulu zemāk esošajā piemērā un nospiediet taustiņu ENTER. Sākuma nulles tiks noņemtas, un virkne tiks pārvērsta atpakaļ par skaitli.

Pēc tam izmantojiet šo formulu citām šūnām, izmantojot aizpildīšanas turi.

Tos pašus rezultātus varat sasniegt, izmantojot komandu “Īpašā ielīmēšana”. Lūk, kā to izdarīt:

Šūnā ierakstiet skaitlisko vērtību “1” (piemēram, B2) un kopējiet šo vērtību.

Pēc tam atlasiet šūnas, kurās vēlaties noņemt sākuma nulles. Pēc tam ar peles labo pogu noklikšķiniet uz atlases un pēc tam atlasiet opciju Īpašā ielīmēšana.

Dialoglodziņa Īpašā ielīmēšana sadaļā Darbība izvēlieties opciju “Reizināt” vai “Sadalīt” un noklikšķiniet uz “OK”.

Tas ir viss, jūsu sākuma nulles tiks noņemtas, atstājot virknes kā skaitļus.

Noņemiet vadošās nulles, izmantojot formulas

Vēl viens vienkāršs veids, kā dzēst prefiksu nulles, ir izmantot funkciju VALUE. Šī metode var būt noderīga, ja sākuma nulles tika pievienotas, izmantojot citu formulu vai apostrofu vai pielāgotu formatējumu.

=VĒRTĪBA(A1)

Formulas arguments var būt vērtība vai šūnas atsauce, kurai ir vērtība. Formula noņem sākuma nulles un pārvērš vērtību no teksta par skaitli. Pēc tam uzklājiet formulu pārējām šūnām.

Dažreiz, iespējams, vēlēsities noņemt sākuma nulles, bet vēlaties saglabāt skaitļus teksta formātā. Šādos gadījumos funkcijas TEXT() un VALUE () ir jāizmanto kopā šādi:

=TEKSTS(VĒRTĪBA(A1),"#")

Funkcija VĒRTĪBA pārvērš vērtību A1 par skaitli. Bet otrais arguments “#” pārvērš vērtību atpakaļ teksta formātā bez papildu nullēm. Rezultātā jūs iegūsit skaitļus bez nullēm sākumā, bet joprojām teksta formātā (līdzināti pa kreisi).

Noņemiet vadošās nulles, izmantojot Excel funkciju Teksts uz kolonnām

Vēl viens veids, kā noņemt sākuma nulles, ir izmantot Excel funkciju Teksts uz kolonnām.

Atlasiet šūnu diapazonu, kurā ir skaitļi ar nullēm sākumā.

Pēc tam dodieties uz cilni “Dati” un grupā Datu rīki noklikšķiniet uz pogas “Teksts uz kolonnām”.

Tiks parādīts vednis “Teksta konvertēšana uz kolonnām”. 1. darbībā no 3 atlasiet “Ierobežots” un noklikšķiniet uz “Tālāk”.

Veicot 2. darbību no 3, noņemiet atzīmi no visiem norobežotājiem un noklikšķiniet uz “Tālāk”.

Pēdējā darbībā atstājiet opciju Kolonnas datu formāts kā “Vispārīgi” un izvēlieties galamērķi (diapazona pirmo šūnu), kurā vēlaties skaitļus bez nullēm. Pēc tam noklikšķiniet uz "Pabeigt"

Un jūs saņemsit skaitļus ar sākumu, kas tiks noņemts atsevišķā kolonnā, kā parādīts zemāk.

Tas ir viss.