I denne artikel lærer vi forskellige måder at tilføje nuller (0'er) foran i Excel.
Scenarie:
Tilføjelse af nul foran nummeret i Excel. Standard Excel tager ikke nuller foran nummeret i cellen. Har du nogensinde prøvet at indtaste nogle data som 000123 i Excel? Du vil sandsynligvis hurtigt bemærke, at Excel automatisk fjerner de førende nuller fra inputnummeret. Dette kan være virkelig irriterende, hvis du vil have de førende nuller i data og ikke ved, hvordan du får Excel til at beholde dem. Heldigvis er der en hel del måder at skrive dine tal på med nuller i starten. I denne artikel forklarer jeg forskellige måder at tilføje eller beholde de førende nuller til dine numre.
Forskellige funktioner for at tilføje nul (0) foran
Som vi ved, er TEKST -funktion den mest anvendte funktion til at tilføje 0 foran, men her forklarer jeg flere måder at tilføje nuller (0'er) til tal.
- TEKST -funktion
- HØJRE funktion
- BASE -funktion
- Tilføj 0 i pivottabelværdier
Formater som tekst
En af løsningerne er at konvertere talets format til tekst, fordi hvis vi indtaster 00xyz, betragter excel denne værdi som tekst og holder dens 0 foran. Der er forskellige måder at konvertere en talcelle til tekstformat.
- Brug af brugerdefineret format
- Brug af TEKST -funktion
- Brug af Apostrophe (') i start af værdi, mens input
Brugerdefineret format
Lad os forstå disse metoder en efter en. Brug af et tilpasset format til at ændre formatet er den nemmeste måde at ændre de allerede eksisterende tal inden for rækkevidde. Vælg bare området og brug genvej Ctrl + 1 eller vælg Mere talformat fra rullelisten på fanen Startside, der viser Generelt.
Skift format fra Generelt til Tekst.
- Vælg det celleområde, du vil indtaste ledende nuller i.
- Gå til fanen Startside.
- Klik på afsnittet Format i rullemenuen i afsnittet Numbers.
- Vælg Tekst fra formatmulighederne.
Hvis du nu prøver at indtaste tal med førende nuller, forsvinder de ikke, fordi de indtastes som tekstværdier i stedet for tal. Excel -brugere kan tilføje en tilpasset formatering til at formatere tal med førende nuller.
De ser dog kun ud til at have førende nuller. De underliggende data ændres ikke til tekst med de tilføjede nuller.
Tilføj et tilpasset format for at vise førende nuller.
- Vælg det celleområde, du vil føje ledende nuller til, og åbn dialogboksen Format celler.
- Højreklik og vælg Format celler.
- Brug tastaturgenvejen Ctrl + 1.
- Gå til fanen Nummer.
- Vælg Brugerdefineret fra kategorimulighederne.
- Tilføj et nyt brugerdefineret format i Type -input. Hvis du vil have det samlede antal cifre inklusive alle førende nuller til at være 6, skal du tilføje 000000 som det tilpassede format.
- Tryk på knappen OK.
TEKST Funktion
TEKST -funktionen lader dig anvende en tilpasset formatering til alle taldata, der allerede er i dit regneark.
= TEKST (Værdi, Format)
- Værdi - Dette er den værdi, du vil konvertere til tekst og anvende formatering på.
- Format - Dette er den formatering, der skal anvendes.
= TEKST (B3, "000000")
Hvis du ønskede at tilføje nuller til et tal i celle B3, så det samlede antal cifre er 6, kan du bruge ovenstående formel.
Brug af Apostrophe (') i starten
Du kan tvinge Excel til at indtaste et tal som tekst ved hjælp af en ledende apostrof.
Det betyder, at du vil kunne beholde disse nuller foran, når du indtaster dine data.
Denne metode er hurtig og let, når du indtaster data. Skriv bare et 'tegn før tal. Dette vil fortælle Excel, at dataene er beregnet til at være tekst og ikke et tal.
Når du trykker på Enter, forbliver de førende nuller synlige i regnearket. 'Vil ikke være synlig i regnearket, men er der stadig og kan ses på formellinjen, når den aktive cellemarkør er på cellen.
HØJRE funktion
En anden måde at få dine nuller foran med en formel er at bruge funktionen HØJRE. Du kan sammenkæde en række nuller til tallet og derefter skære ekstramateriale af ved hjælp af HØJRE -funktionen.
Funktionen HØJRE vil udtrække de N -tegn til højre fra en tekstværdi.
= HØJRE (tekst, [tal])
- Tekst - Dette er den tekst, du vil udtrække tegn fra.
- Nummer (Valgfri)- Dette er antallet af tegn, der skal udtrækkes fra teksten. Hvis dette argument ikke indtastes, udtrækkes kun det første tegn.
= HØJRE ("000000" & B3, 6)
Ovenstående formel vil sammenkoble flere nuller til starten af et tal i celle B3, så returnerer det de 6 højre tegn til højre, hvilket resulterer i nogle førende nuller.
BASE -funktion
Du vil bemærke, at denne artikel beskriver 9 måder at tilføje førende nuller på, men min YouTube -video viser kun 8 måder.
Det er fordi jeg ikke vidste, at du kunne bruge BASE -funktionen til at tilføje ledende nuller, indtil nogen nævnte det i videokommentarerne.
Med BASE -funktionen kan du konvertere et tal til en tekstrepræsentation med en given base.
De tal, du normalt bruger, er basis 10, men du kan bruge denne funktion til at konvertere et basestal 10 til en basis 2 (binær) repræsentation.
= BASE (tal, base, [minlængde])
- Nummer - Dette er det tal, du vil konvertere til en tekstværdi i en anden base.
- Base - Dette er den base, du vil konvertere værdien til.
- MinLængde (Valgfri) - Dette er minimumslængden for tegnene i den konverterede tekstværdi.
Brug formlen
| = BASE (B3,10,6) |
Ovenstående formel konverterer et tal i celle B3 til base 10 (det er allerede et base 10 -tal, men dette vil beholde det som base 10) og konvertere det til en tekstværdi med mindst 6 tegn. Hvis den konverterede værdi er mindre end 6 tegn, putter den værdien med nuller for at få mindst 6 tegn.
Absolut en sej ny anvendelse til en funktion, jeg ellers aldrig bruger.
Tilføj kun nul (0) foran tal, ikke på tekstværdier
Her vil vi overveje scenariet, der dækker både tekst- og talværdier i den samme liste. Nu betragter Excel hver celle forskellig med hensyn til format. Nu hvordan kan du lade excel vide, at hvis cellen har en talværdi, så tilføj 0 foran eller hvis cellen har tekst eller en anden værdi, lad den være som den er.
Til dette vil vi bruge en kombination af to funktioner IF og ISNUMBER funktion. Her har vi nogle værdier at prøve.
Den generiske formel fortsætter som
= HVIS (ISNUMBER (cell_ref), "0" & cell_ref, cell_ref)
Her
cell_ref: cellereference for den tilsvarende celle
Brug formlen i cellen
| = HVIS (ISNUMBER (F3), "0" & F3, F3) |
Forklaring: ISNUMBER -funktionen kontrollerer cellen for nummer. HVIS funktionen kontrollerer, om cellen har et tal, tilføjer den nul foran tallet, ellers returnerer den bare celleværdien.
Som du kan se på ovenstående billede tilføjes nul (0) foran tallet. Lad os kontrollere, om denne funktion fungerer på andre værdier. Kopier formlen i andre celler, vælg cellerne, der tager den første celle, hvor formlen allerede er anvendt, brug genvejstasten Ctrl+D
det vil putte værdien med nuller for at få mindst et nul foran tegn. Absolut en sej ny anvendelse til en funktion, jeg ellers aldrig bruger.
Power Pivot -beregnet kolonne
Der er en anden mulighed for at tilføje nuller til pivottabellen. Du kan tilføje dem til en beregnet kolonne i Power Pivot. På denne måde kan du bruge den nye kolonne i området Filter, rækker eller kolonner i en pivottabel.
= FORMAT (Numbers [Number], "000000")
I tilføjelsesprogrammet Power Pivot kan du tilføje en ny kolonne og bruge FORMAT-funktionen til at oprette ledende nuller i kolonnen med en formel som ovenfor. En beregnet kolonne beregner en værdi for hver række, så det er ikke nødvendigt at pakke funktionen ind i en celle. Nu kan du bruge denne nye kolonne inde i en pivottabels Rækker -område ligesom enhver anden kolonne med data.
Resumé
Det kan være frustrerende at se alle dine nuller forsvinde, når vi ikke ved, hvorfor det sker, eller hvordan vi kan forhindre det. Som du kan se, er der mange måder at sikre, at alle disse nuller holder fast i dine data. Uanset dit krav, er der helt sikkert en god løsning for dig.
Her er alle observationsnotaterne ved hjælp af formlen i Excel
Bemærkninger:
- Formlen kan bruges til både tekster og tal.
- Find og erstat teksten skal sorteres i en tabel. Når vi bruger indekser til at finde og erstatte, skal du sørge for, at 'find' tekst har det samme indeks som 'erstat' tekst. Det er bedre at have begge lister side om side, som vi har i det andet eksempel.
- Den generiske formel har to SUBSTITUTE -funktioner. Du får brug for lige så mange erstatningsfunktioner, som mange du har udskiftninger til at udføre.
- jokertegn hjælper med at udtrække værdi fra underlagene.
- Funktionen understøtter logiske operatorer som,, = men disse bruges ved hjælp af dobbelt citattegn ( " ) med argumenter. Men hvis du bruger cellereference til kriterierne, kan citater ignoreres.
- Kriterier argument bør bruge citattegn (") til tekster (som" Gillen ") eller tal (som"> 50 ").
Håber denne artikel om forskellige måder at tilføje nuller (0'er) foran i Excel er forklarende. Find flere artikler om beregning af værdier og relaterede Excel -formler her. Hvis du kunne lide vores blogs, kan du dele den med dine venner på Facebook. Og også du kan følge os på Twitter og Facebook. Vi vil meget gerne høre fra dig, lad os vide, hvordan vi kan forbedre, supplere eller innovere vores arbejde og gøre det bedre for dig. Skriv til os på e -mail -stedet.
Excel REPLACE vs SUBSTITUTE -funktion: Funktionerne REPLACE og SUBSTITUTE er de mest misforståede funktioner. For at finde og erstatte en given tekst bruger vi SUBSTITUTE -funktionen. Hvor REPLACE bruges til at erstatte et antal tegn i en streng.
Erstat tekst fra enden af en streng, der starter fra variabel position: For at erstatte tekst fra slutningen af strengen bruger vi funktionen REPLACE. REPLACE -funktionen bruger tekstens placering i strengen til at erstatte.
Sådan kontrolleres, om en streng indeholder en af mange tekster i Excel: For at kontrollere, om en streng indeholder en af flere tekster, bruger vi denne formel. Vi bruger SUM -funktionen til at opsummere alle matches og udfører derefter en logik for at kontrollere, om strengen indeholder en af de flere strenge.
Tæl celler, der indeholder specifik tekst: En simpel COUNTIF -funktion gør magien. For at tælle antallet af flere celler, der indeholder en given streng, bruger vi wildcard -operatoren med COUNTIF -funktionen.
Populære artikler:
50 Excel -genveje til at øge din produktivitet : Bliv hurtigere til dine opgaver i Excel. Disse genveje hjælper dig med at øge din arbejdseffektivitet i Excel.
Sådan bruges VLOOKUP -funktionen i Excel : Dette er en af de mest anvendte og populære funktioner i excel, der bruges til at slå værdi op fra forskellige områder og ark.
Sådan bruges IF -funktionen i Excel : IF -sætningen i Excel kontrollerer betingelsen og returnerer en bestemt værdi, hvis betingelsen er SAND, eller returnerer en anden specifik værdi, hvis FALSK.
Sådan bruges SUMIF -funktionen i Excel : Dette er en anden vigtig instrumentbrætfunktion. Dette hjælper dig med at opsummere værdier på bestemte betingelser.
Sådan bruges COUNTIF -funktionen i Excel : Tæl værdier med betingelser ved hjælp af denne fantastiske funktion. Du behøver ikke at filtrere dine data for at tælle bestemte værdier. Countif -funktion er afgørende for at forberede dit dashboard.