I denne artikkelen lærer vi forskjellige måter å legge til nuller (0s) foran i Excel.
Scenario:
Legger til null foran tallet i Excel. Standard Excel tar ikke nuller foran tallet i cellen. Har du noen gang prøvd å legge inn data som 000123 i Excel? Du vil sannsynligvis raskt legge merke til at Excel automatisk fjerner de ledende nullene fra inndatanummeret. Dette kan være veldig irriterende hvis du vil ha de ledende nullene i data og ikke vet hvordan du får Excel til å beholde dem. Heldigvis er det ganske mange måter å putte tallene dine på med nuller i starten. I denne artikkelen vil jeg forklare forskjellige måter å legge til eller beholde de ledende nullene på tallene dine.
Ulike funksjoner for å legge til null (0) foran
Som vi vet er TEKST -funksjonen den mest brukte funksjonen for å legge til 0 foran, men her vil jeg forklare flere måter å legge til nuller (0s) i tall.
- TEKST -funksjon
- HØYRE -funksjon
- BASE -funksjon
- Legg til 0 i pivottabellverdier
Formater som tekst
En av løsningene er å konvertere formatet til tallet til tekst, fordi hvis vi legger inn 00xyz, anser excel denne verdien som tekst og holder 0en foran. Det er forskjellige måter å konvertere en tallcelle til tekstformat.
- Bruker egendefinert format
- Bruker TEKST -funksjon
- Bruker Apostrophe (') i begynnelsen av verdien mens du skriver inn
Egendefinert format
La oss forstå disse metodene en etter en. Å bruke et tilpasset format for å endre formatet er den enkleste måten å endre de allerede eksisterende tallene i området. Bare velg området og Bruk snarvei Ctrl + 1 eller velg Mer tallformat fra rullegardinlisten på Hjem -fanen som viser Generelt.
Endre formatet fra Generelt til Tekst.
- Velg celleområdet du vil skrive inn ledende nuller i.
- Gå til fanen Hjem.
- I Numbers -delen klikker du på Format -rullegardinmenyen.
- Velg Tekst fra formatalternativene.
Hvis du prøver å skrive inn tall med ledende nuller, forsvinner de ikke fordi de er angitt som tekstverdier i stedet for tall. Excel -brukere kan legge til en tilpasset formatering for å formatere tall med ledende nuller.
De ser imidlertid bare ut til å ha ledende nuller. De underliggende dataene blir ikke endret til tekst med de nullene som er lagt til.
Legg til et tilpasset format for å vise ledende nuller.
- Velg celleområdet du vil legge til ledende nuller til, og åpne dialogboksen Formater celler.
- Høyreklikk og velg Format celler.
- Bruk hurtigtasten Ctrl + 1.
- Gå til kategorien Nummer.
- Velg Custom fra kategorialternativene.
- Legg til et nytt tilpasset format i Type -inngangen. Hvis du vil at det totale antallet siffer inkludert eventuelle ledende nuller skal være 6, legg til 000000 som det egendefinerte formatet.
- Trykk på OK -knappen.
TEKST Funksjon
TEKST -funksjonen lar deg bruke en tilpasset formatering til alle talldata som allerede er i regnearket.
= TEKST (verdi, format)
- Verdi - Dette er verdien du vil konvertere til tekst og bruke formatering på.
- Format - Dette er formateringen som skal brukes.
= TEKST (B3, "000000")
Hvis du ønsket å legge til nuller til et tall i celle B3 slik at det totale antallet siffer er 6, kan du bruke formelen ovenfor.
Bruker Apostrophe (') i starten
Du kan tvinge Excel til å skrive inn et tall som tekst ved å bruke en ledende apostrof.
Dette betyr at du kan beholde disse nullene foran når du skriver inn dataene dine.
Denne metoden er rask og enkel når du legger inn data. Bare skriv inn et 'tegn før noen tall. Dette vil fortelle Excel at dataene er ment å være tekst og ikke et tall.
Når du trykker Enter, forblir de ledende nullene synlige i regnearket. 'Vil ikke være synlig i regnearket, men er fortsatt der og kan sees i formellinjen når den aktive cellemarkøren er på cellen.
HØYRE Funksjon
En annen måte å få nullene dine foran med en formel er å bruke HØYRE -funksjonen. Du kan koble en streng med nuller til tallet og deretter kutte av tilleggene ved å bruke HØYRE -funksjonen.
HØYRE -funksjonen trekker ut de N -tegnene til høyre fra en tekstverdi.
= HØYRE (tekst, [tall])
- Tekst - Dette er teksten du vil trekke ut tegn fra.
- Nummer (Valgfri)- Dette er antall tegn som skal trekkes ut av teksten. Hvis dette argumentet ikke er angitt, trekkes bare det første tegnet ut.
= HØYRE ("000000" og B3, 6)
Formelen ovenfor vil sammenkoble flere nuller til begynnelsen av et tall i celle B3, så vil den returnere de 6 tegnene til høyre, noe som resulterer i noen ledende nuller.
BASE -funksjon
Du vil legge merke til at denne artikkelen beskriver 9 måter å legge til ledende nuller, men YouTube -videoen min viser bare 8 måter.
Det er fordi jeg ikke visste at du kunne bruke BASE -funksjonen til å legge til ledende nuller før noen nevnte det i videokommentarene.
Med BASE -funksjonen kan du konvertere et tall til en tekstrepresentasjon med en gitt base.
Tallene du vanligvis bruker er base 10, men du kan bruke denne funksjonen til å konvertere et base 10 -tall til en base 2 (binær) representasjon.
= BASE (Antall, Base, [MinLengde])
- Nummer - Dette er tallet du vil konvertere til en tekstverdi i en annen base.
- Base - Dette er basen du vil konvertere verdien til.
- Min lengde (Valgfri) - Dette er minimumslengden til tegnene i den konverterte tekstverdien.
Bruk formelen
| = BASE (B3,10,6) |
Formelen ovenfor vil konvertere et tall i celle B3 til base 10 (det er allerede et base 10 -nummer, men dette vil beholde det som base 10) og konvertere det til en tekstverdi med minst 6 tegn. Hvis den konverterte verdien er mindre enn 6 tegn, putter den verdien med nuller for å få minst 6 tegn.
Definitivt en kul ny bruk for en funksjon jeg ellers aldri bruker.
Legg til null (0) bare foran tall, ikke på tekstverdier
Her vil vi vurdere scenariet som dekker både tekst- og tallverdier i samme liste. Nå anser excel hver celle for seg når det gjelder format. Hvordan kan du la Excel vite at hvis cellen har en tallverdi, legg til 0 foran eller hvis cellen har tekst eller annen verdi, så la den være som den er.
For dette vil vi bruke en kombinasjon av to funksjoner IF og ISNUMBER -funksjonen. Her har vi noen verdier å prøve.
Den generiske formelen fortsetter som
= HVIS (ISNUMBER (cell_ref), "0" og cell_ref, cell_ref)
Her
cell_ref: cellereferanse for den tilsvarende cellen
Bruk formelen i cellen
| = HVIS (ISNUMBER (F3), "0" & F3, F3) |
Forklaring: ISNUMBER -funksjonen sjekker cellen for nummer. HVIS funksjonen sjekker om cellen har et tall, legger den til null foran tallet ellers returnerer den bare celleverdien.
Som du kan se på bildet ovenfor, blir null (0) lagt foran tallet. La oss sjekke om denne funksjonen fungerer på andre verdier. Kopier formelen i andre celler, velg cellene som tar den første cellen der formelen allerede er brukt, bruk hurtigtasten Ctrl+D
det vil putte verdien med nuller for å få minst ett null foran tegn. Definitivt en kul ny bruk for en funksjon jeg ellers aldri bruker.
Power Pivot Beregnet kolonne
Det er et annet alternativ for å legge til nuller i pivottabellen. Du kan legge dem til i en beregnet kolonne i Power Pivot. På denne måten kan du bruke den nye kolonnen i området Filter, Rader eller Kolonne i en pivottabell.
= FORMAT (tall [tall], "000000")
I Power Pivot-tillegget kan du legge til en ny kolonne og bruke FORMAT-funksjonen til å lage ledende nuller i kolonnen med en formel som ovenfor. En beregnet kolonne beregner en verdi for hver rad, så det er ikke nødvendig å pakke inn funksjonen inne i en celle. Nå kan du bruke denne nye kolonnen inne i en pivottabells radområde akkurat som alle andre datakolonner.
Sammendrag
Det kan være frustrerende å se alle nullene dine forsvinne når vi ikke vet hvorfor det skjer eller hvordan vi kan forhindre det. Som du kan se, er det mange måter å sørge for at alle disse nullene holder seg fast i dataene dine. Uansett krav, er det sikkert en god løsning for deg.
Her er alle observasjonsnotatene ved hjelp av formelen i Excel
Merknader:
- Formelen kan brukes for både tekster og tall.
- Finn og erstatt teksten skal sorteres i en tabell. Når vi bruker indekser for å finne og erstatte, må du kontrollere at "finn" -teksten har samme indeks som "erstatt" -teksten. Det er bedre å ha begge listene side om side som vi har i det andre eksemplet.
- Den generiske formelen har to SUBSTITUTE -funksjoner. Du trenger så mange erstatningsfunksjoner som mange du har erstatninger å gjøre.
- jokertegn hjelper til med å trekke ut verdi fra underlagene.
- Funksjonen støtter logiske operatører som,, = men disse brukes ved bruk av dobbelt sitattegn ( " ) med argumenter. Men hvis du bruker cellereferanse for kriteriene, kan sitater ignoreres.
- Kriteriargument bør bruke sitattegn (") for tekster (som" Gillen ") eller tall (som"> 50 ").
Håper denne artikkelen om forskjellige måter å legge til nuller (0s) foran i Excel er forklarende. Finn flere artikler om beregning av verdier og relaterte Excel -formler her. Hvis du likte bloggene våre, del den med vennene dine på Facebook. Og du kan også følge oss på Twitter og Facebook. Vi vil gjerne høre fra deg, gi oss beskjed om hvordan vi kan forbedre, utfylle eller innovere arbeidet vårt og gjøre det bedre for deg. Skriv til oss på e -post.
Excel REPLACE vs SUBSTITUTE -funksjon: Funksjonene REPLACE og SUBSTITUTE er de mest misforståtte funksjonene. For å finne og erstatte en gitt tekst bruker vi SUBSTITUTE -funksjonen. Hvor REPLACE brukes til å erstatte et antall tegn i strengen.
Erstatt tekst fra enden av en streng fra variabel posisjon: For å erstatte tekst fra enden av strengen bruker vi REPLACE -funksjonen. REPLACE -funksjonen bruker tekstposisjonen i strengen til å erstatte.
Hvordan sjekke om en streng inneholder en av mange tekster i Excel: For å sjekke om en streng inneholder noen av flere tekster, bruker vi denne formelen. Vi bruker SUM -funksjonen til å oppsummere alle treffene og deretter utføre en logikk for å sjekke om strengen inneholder noen av de flere strengene.
Tell celler som inneholder spesifikk tekst: En enkel COUNTIF -funksjon vil gjøre magien. For å telle antallet flere celler som inneholder en gitt streng bruker vi jokertegnoperatoren med COUNTIF -funksjonen.
Populære artikler:
50 Excel -snarveier for å øke produktiviteten : Bli raskere med oppgavene i Excel. Disse snarveiene hjelper deg med å øke arbeidseffektiviteten din i Excel.
Slik bruker du VLOOKUP -funksjonen i Excel : Dette er en av de mest brukte og populære funksjonene i excel som brukes til å slå opp verdi fra forskjellige områder og ark.
Slik bruker du IF -funksjonen i Excel : IF -setningen i Excel sjekker tilstanden og returnerer en bestemt verdi hvis betingelsen er SANN eller returnerer en annen spesifikk verdi hvis FALSE.
Slik bruker du SUMIF -funksjonen i Excel : Dette er en annen viktig funksjon i instrumentbordet. Dette hjelper deg med å oppsummere verdier på spesifikke forhold.
Slik bruker du COUNTIF -funksjonen i Excel : Tell verdier med betingelser ved hjelp av denne fantastiske funksjonen. Du trenger ikke å filtrere dataene dine for å telle spesifikke verdier. Countif -funksjonen er avgjørende for å forberede dashbordet.