Kā programmā Excel pievienot vadošās nulles - viss, kas jums jāzina

Pastāv situācijas, kad programmā Excel datu kopai jāpievieno sākuma nulles. Tas varētu notikt, ja programmā Excel saglabājat ierakstus, piemēram, darbinieku ID vai darījumu ID.

Piemēram, iespējams, vēlēsities konsekventi izskatīt savu datu kopu, kā parādīts zemāk:

Šajā apmācībā jūs uzzināsit dažādus veidus, kā programmā Excel pievienot nulles.

• Formāta pārvēršana par tekstu
• Pielāgota skaitļu formatēšanas izmantošana
• Teksta funkcijas izmantošana
• REPT/LEN funkciju izmantošana
• Izmantojot VBA

Katrai no šīm metodēm ir daži plusi un mīnusi (apskatīti katrā sadaļā).

Apskatīsim, kā darbojas katrs no šiem.

Pievienojiet vadošās nulles, pārveidojot formātu par tekstu

Kad lietot: Ja jums ir neliela ciparu datu kopa un plānojat veikt šo rediģēšanu manuāli.

Pieņemsim, ka jums ir mārketinga nodaļas darbinieku ID, kā parādīts zemāk, un vēlaties, lai šie ID izskatītos konsekventi, pievienojot sākuma nulles.

Tāpēc mēģiniet mainīt ID, ievadot nulles sākumā (00001, nevis 1).

Bet jūsu pārsteigumam Excel pārvērš to par 1.

Tas notiek, programmai Excel saprotot, ka 00001 un 1 ir vienādi skaitļi un tiem ir jāievēro tie paši attēlošanas noteikumi.

Lai cik nomākta tas jums varētu būt, programmai Excel ir savi iemesli.

Tātad, lai paveiktu darbu, neizliekot Excel kārtulas, jums jāizmanto fakts, ka šis noteikums neattiecas uz teksta formatējumu.

Tātad, kas jums jādara:

1. Atlasiet šūnas, kurās vēlaties manuāli pievienot sākuma nulles.
2. Atveriet Sākums → Ciparu grupa un nolaižamajā izvēlnē atlasiet Teksts.

Tieši tā!

Tagad, manuāli ievadot nulles, Excel to viegli izpildīs.

Uzmanību: pārvēršot formātu par tekstu, dažas Excel funkcijas nedarbosies pareizi. Piemēram, funkcija SUM/COUNT ignorēs šūnu, jo tā ir teksta formātā.

Pievienojiet vadošās nulles, izmantojot pielāgotu skaitļu formatējumu

Kad lietot: Ja jums ir skaitliska datu kopa un vēlaties, lai rezultāts būtu skaitlisks (nevis teksts).

Rādot skaitli noteiktā formātā, tas nemaina numura pamatā esošo vērtību. Piemēram, es varu parādīt skaitli 1000 kā 1000 vai 1000 vai 1000,00 vai 001000 vai 26-09-1902 (pat datumi ir skaitļi programmā Excel).

Visos dažādos skaitļa parādīšanas veidos skaitļa vērtība nekad nemainās. Tiek mainīts tikai tā parādīšanas veids.

Lai pievienotu nulles sākumā, mēs varam to formatēt tā, lai tā tiktu parādīta, bet pamatvērtība nemainītos.

Tālāk ir norādītas darbības, lai izmantotu šo paņēmienu, lai programmā Excel pievienotu sākuma nulles.

1. Atlasiet šūnas, kurās vēlaties pievienot sākuma nulles.
2. Dodieties uz Sākums → Ciparu grupa un noklikšķiniet uz dialoga palaišanas programmas (maza noliekta bultiņa apakšējā labajā stūrī). Tiks atvērts dialoglodziņš Šūnu formatēšana. Varat arī izmantot īsinājumtaustiņu: Control + 1.
3. Dialoglodziņa Šūnu formatēšana cilnes Skaitlis sarakstā atlasiet Pielāgots.
4. Laukā Tips ievadiet 00000
5. Noklikšķiniet uz Labi.

To darot, visi skaitļi vienmēr tiks parādīti kā pieci cipari, un, ja skaitlis ir mazāks par 5 cipariem, automātiski tiek pievienoti 0. Tātad 10 kļūtu par 00010 un 100 - par 00100.

Šajā gadījumā mēs esam izmantojuši sešas nulles, bet, ja jūsu datos ir skaitļi ar vairāk cipariem, tad jums ir jāizmanto attiecīgi formāts.

Piezīme. Šī metode darbosies tikai skaitliskai datu kopai. Ja jums ir darbinieku ID, piemēram, A1, A2, A3 un tā tālāk, tie ir teksts un tie nemainīsies, ja lietosit pielāgoto formātu, kā parādīts iepriekš.

Pievienojiet vadošās nulles, izmantojot funkciju TEXT

Kad lietot: Kad vēlaties, lai rezultāts būtu teksts.

Funkcija TEKSTS ļauj mainīt vērtību vēlamajā formātā.

Piemēram, ja vēlaties, lai 1 tiktu parādīts kā 001, varat tam izmantot funkciju TEXT.

Tomēr atcerieties, ka funkcija TEXT mainītu formātu un padarītu to par TEXT. Tas nozīmē, ka, veidojot 1 kā 001, programma Excel jauno rezultātu uztver kā tekstu ar trim rakstzīmēm (tāpat kā abc vai xyz).

Tālāk ir norādīts, kā pievienot sākuma nulles, izmantojot funkciju TEXT:

1. Ja A slejā ir skaitļi (piemēram, no A2: A100), tad izvēlieties B2: B100 un ievadiet šādu formulu:
   = TEKSTS (A2, "00000")
2. Nospiediet Control + Enter, lai lietotu formulu visām atlasītajām šūnām.

Tādējādi visi skaitļi tiks parādīti kā pieci cipari, un, ja skaitlis ir mazāks par 5 cipariem, automātiski tiek pievienoti 0.

Viena priekšrocība, pārveidojot datus tekstā, ir tā, ka tagad varat tos izmantot uzmeklēšanas formulās, piemēram, VLOOKUP vai INDEX/MATCH, lai iegūtu informāciju par darbinieku, izmantojot viņa/viņas darbinieka ID.

Piezīme. Šī metode darbosies tikai skaitliskai datu kopai. Ja jums ir darbinieku ID, piemēram, A1, A2, A3 un tā tālāk, tie ir teksts un tie nemainīsies, ja lietosit pielāgoto formātu, kā parādīts iepriekš.

Pievienojiet vadošās nulles, izmantojot funkcijas REPT un LEN

Kad lietot: Ja jums ir ciparu/burtciparu datu kopa un vēlaties, lai rezultāts būtu teksts.

Funkcijas TEXT izmantošanas trūkums bija tas, ka tā darbosies tikai ar ciparu datiem. Bet, ja jums ir burtciparu datu kopa (piemēram, A1, A2, A3 un tā tālāk), funkcija TEXT neizdosies.

Šādos gadījumos REPT un LEN funkcijas kombinācija palīdz.

Lūk, kā to izdarīt:

1. Ja A slejā ir skaitļi (piemēram, no A2: A100), tad izvēlieties B2: B100 un ievadiet šādu formulu:
   = REPT (0,5-LEN (A2)) un A2
2. Nospiediet Control + Enter, lai lietotu formulu visām atlasītajām šūnām.

Tas padarītu visus ciparus/virknes 5 rakstzīmes garas un ar nulli priekšā, kur vien nepieciešams.

Šī formula darbojas šādi.

• LEN (A2) norāda virknes/ciparu garumu šūnā.
• = REPT (0,5-LEN (A2)) dotu skaitli 0, kas būtu jāpievieno. Šeit es formulā esmu izmantojis 5, jo tas bija maksimālais virknes/ciparu garums manā datu kopā. Jūs to varat mainīt atbilstoši saviem datiem.
• = REPT (0,5-LEN (A2)) & A2 vienkārši pievienotu šūnas vērtībai nulles skaitu. Piemēram, ja šūnas vērtība ir 123, tiks atgriezts 00123.

Pievienojiet vadošās nulles, izmantojot pielāgoto funkciju (VBA)

Ja programmā Excel ir jāpievieno sākuma nulles, jums ir jādara diezgan bieži, laba ideja ir izmantot pielāgotu funkciju.

Šeit ir VBA kods, kas izveidos vienkāršu funkciju sākuma nulles pievienošanai:

'Kods: Sumits Bansals no vietnes http://trumpexcel.com Funkcija AddLeadingZeroes (atsauce kā diapazons, garums kā vesels skaitlis) Dim i Kā vesels skaitlis Dim Rezultāts kā virkne Dim StrLen kā vesels skaitlis StrLen = Len (atsauce) I = 1 līdz garumam <= StrLen Then Result = Result & Mid (ref, i, 1) Cits Result = "0" & ​​Results End if Next i AddLeadingZeroes = Rezultāta beigu funkcija

Vienkārši pievienojiet šo kodu moduļa koda logam, un jūs varēsit to izmantot tāpat kā jebkuru citu darblapas funkciju.

Vai arī izveidojiet tam pievienojumprogrammu un kopīgojiet to ar saviem kolēģiem.