Denne artikkelen avslører skjulte hemmeligheter om Excel-nummerformatering. Hvordan du kan lagre tall i Excel, men legge til ord ved siden av tallene. Les videre for flere kule tips om tallformatering.
Excel er fantastisk å lagre ett nummer og presentere et annet nummer. Velg hvilken som helst celle og velg Valutaformat. Excel legger til et dollartegn og komma og presenterer tallet avrundet til to desimaler. I figuren nedenfor inneholder celle D2 faktisk 6.42452514. Heldigvis presenterer det innebygde tilpassede tallformatet resultatene i et lettlest format.
Den tilpassede nummerformatkoden i D2 er $ #, ## 0.00. I denne koden kreves 0 siffer. Eventuelle #s er valgfrie sifre.
Imidlertid kan formateringskoder være langt mer komplekse. Koden ovenfor har ett format. Det formatet brukes på alle verdier i cellen. Hvis du oppgir en kode med to formater, er det første formatet for ikke-negative tall, og det andre formatet for negative tall. Du skiller formatene med semikolon. Hvis du gir en kode med tre formater, er den første for positiv, deretter negativ, deretter null. Hvis du oppgir en kode med fire formater, brukes de til positiv, negativ, null og tekst.
Selv om du bruker et innebygd format, kan du gå til Format Cells, Number, Custom og se koden som brukes til å generere det formatet. Her er koden for regnskapsformatet:
For å bygge ditt eget tilpassede format, gå til Format celler, nummer, egendefinert og skriv inn koden i boksen Type. Ta en titt på eksemplet i Eksempel-boksen for å sikre at alt ser riktig ut.
I det følgende eksemplet brukes tre soner. Tekst i anførselstegn legges til i tallformatet for å tilpasse meldingen.
Hvis du oppretter en sone for null, men ikke legger noe der, vil du skjule alle nullverdier. Følgende kode bruker fargekoder for positive og negative. Koden ender i en semikolon, og skaper en sone for nullverdier. Men siden sonen er tom, vises ikke nullverdier.
Du kan utvide dette ved å gjøre alle soner blanke. En tilpasset formatkode ;;; vil skjule verdier i displayet og utskriften. Imidlertid vil du fortsatt kunne se verdiene i formellinjen. Hvis du skjuler verdier ved å gjøre skrifttypen hvit, blir ;;; vil forbli skjult selv om folk endrer fyllfargen. Følgende figur inneholder noen interessante formateringstriks.
Hvis du setter ** foran tallkoden i B2 og B3, fylles den til venstre for nummeret med stjerner som de gamle sjekkforfattermaskinene ville gjort. Men det er ingenting som sier at du må bruke stjerner. Uansett hva du setter etter den første stjernen gjentas for å fylle plassen. Rad 3 bruker *! å gjenta utropstegn.
I B4 og B5 vil hvert komma du setter etter siste null dele tallet med 1000. Koden 0, K viser tall i tusenvis, med et K etterpå. Hvis du vil vise millioner, bruk to komma. “M” -koden må settes i anførselstegn, siden M allerede betyr måneder.
I B6 legger du en streng melding i den fjerde sonen for å varsle alle som skriver inn data om at du vil ha et nummer i cellen. Hvis de ved et uhell skriver inn tekst, vises meldingen.
I B7 til B9 blir de normale sonene Positives, Negatives og Zero overskrevet av forhold som du setter i parentes. Tall under 70 er røde. Tall over 90 er blå. Alt annet er svart.
I B10 forteller de merkelige _ (symbolene i regnskapsformatet Excel om å la så mye plass som en venstre parentes ville ta. Det viser seg at et understrek etterfulgt av et hvilket som helst tegn vil gi like mye hvitt mellomrom som det tegnet. I B10, koden inneholder 4 nuller. Men det er forskjellige mengder mellomrom mellom hver. Avstanden mellom 1 og 2 er bredden på 2 WW-tegn. Mellomromet mellom 2 og 3 er bredden på et N. Mellomromet mellom 3 og 4 er bredden på en liten bokstav i.
Følgende figur viser forskjellige datoformateringskoder.
Merk at mmmmm-formatet i rad 8 er nyttig for å produsere JFMAMJJASOND kartetiketter.
Takk til Dave Baylis, Brad Edgar, Mike Girvin og @best_excel for å foreslå denne funksjonen.
Se på video
- Tallformatering er en fasade: Excel lagrer en ting, viser oss en annen.
- For eksempel: bruk reduserte desimaler for å vise en avrundet versjon av nummeret.
- Excel lagrer fremdeles alle desimaler, men forenkler skjermen.
- Det er 11 tallformater i rullegardinmenyen på Hjem-fanen.
- Klikk dialogboksen for å komme til dialogboksen Format celler.
- I dag snakker vi om formateringskodene i Custom-gruppen.
- Jeg har ikke all kunnskapen her i dag, så hvis du har bedre tips, legg dem inn i YouTube-kommentarene.
- Et null i formatkoden sier at Excel må vise sifferet.
- Et # -tegn sier at de kan vise sifferet hvis det er nok presisjon, men det trenger de ikke når det ikke er det.
- A? vil gi plass til sifferet for å holde desimalplassen på linje.
- Det kan være 1, 2, 3 eller 4 formater, atskilt med en semikolon.
- Når det er to formater, er det første for null og oppover. Den andre er for negativ.
- Når det er tre formater, er det tredje hvis det er null.
- 4. format er for tekst.
- Bruke tallformatet til å vise tekst med nummer eller for å vise tekst i stedet for nummer.
- Spesifisere en farge for en sone.
- For å endre (Color 5) besøk Excel-alternativene.
- * x for å gjenta x opp til tallene
- , til slutt å dele med 1000
- ,, deler med millioner
- Bruke forhold i sonen
- _x vil gi plass på størrelse med "x"
- m mm mmm mmmm mmmmm
- d dd ddd dddd
- (h): mm
- Takk til Dave Baylis, Brad Edgar, Mike Girvin og @best_excel for å foreslå denne funksjonen.
Videoutskrift
Lær Excel fra podcast, episode 2044 - Formatering som fasade!
Ok, jeg podcaster alle tipsene mine i denne boken, klikk på "i" øverst til høyre for å komme til spillelisten!
Vi snakker om tilpasset nummerformatering i dag, så jeg har antall, inntekter, og vi viser en hel haug med desimaler her. Og du vet, hvis vi ikke vil spille alle desimalene, og vi ikke vil bruke RUND-funksjonen, kommer jeg bare opp hit og bruker Reduser desimal. det endrer ikke nummeret som faktisk er lagret bak kulissene, det viser bare hvordan Excel viser oss nummeret. Greit, og så stjeler jeg Mike Girvins begrep, at vi bruker formatering som en fasade, ikke sant, det er en hel haug med desimaler lagret i den cellen, men vi bruker tilpassede tallformater for å vise det. Og jeg vil gå utover de 11 formatene her, og til og med klikke på den dialogruten, utover de fleste tingene som er her, og jeg vil snakke om Custom.Egendefinerte tallformater gir oss verden, ok?
Og så her er en hel haug med tilpassede tallformater som du kan skrive i denne boksen, og resultatene vises til venstre. Så bare Generelt, du får alle desimalene, men her tvinger nuller før desimaltallet til å vises. Så hvis tallet <100, skal jeg få et ledende 0, noen ganger er det nyttig for delenumre, nuller etter desimalene tvinge tallene til å vises. Hashtaggen eller pundtegnet (#) før desimalplassen er valgfri, ok, så den kan være der, den trenger ikke være der. OK, så i dette tilfellet har vi tallet 6, men fordi vi har fem nuller, gir det oss ledende nuller. Nå, hvis du trenger å slå opp i en del av teksten som har 00006, vil dette ikke være en kamp, men hvis du bare trengte å se riktig ut, vil det fungere. OK, så noen interessante ting her,vi bruker pundtegn eller hashtags mye, “hashsigns” mye. I USA kaller vi dette et pundtegn, og jeg forstår i England at det ikke er et pundtegn, £ er din valuta at du kaller det en hash. OK, beklager, jeg har kalt det et pundtegn hele livet, og det er vanskelig for meg å begynne å kalle en hasj, men jeg skal jobbe med det.
Greit, så her nede sier .000 at du skal vise nuller om det er presisjon eller ikke, og jeg har endret disse tallene til 6.42 og 6.4. Dette er interessant, dette er en som jeg aldri kjente, hvis du setter en? i stedet for 0, står det at den vil vise presisjonen hvis den er der, men hvis ikke, ikke vis den, men ikke la ting flyte til høyre. Ok, så her, la oss endre disse tallene, 6.42 og 6.4, ok, se her, desimalene holder seg ikke på linje. Hvis du vil at desimalene skal være i kø, bruk spørsmålstegnene. Hvis du vil tvinge nullene til å være der, bruk nollene.
Nå hei, når vi går gjennom her, er det MYE detaljer i detalj, men jeg har ikke hjørnet med kunnskapen her. Hvis du ser meg savne noe kult nummerformateringstriks, kan du la det ligge nede i YouTube-kommentarene. OK nå, her er neste konsept, vi kan vise 1, 2, 3 eller 4 tallformater i Type-boksen. Hvis du bare viser 1, er det bare tallet som brukes til alt, ok. Men hvis du viser 2 og de er atskilt med semikolon, så er den første for tall> = 0, og den andre for tall <0. Så her har jeg brukt to koder, +0; -0, og det betyr at de negative tallene vises med et minus, men de positive tallene vises med pluss, men fordi jeg ikke spesifiserte hva som skjer for 0 , det dukker opp med et pluss også.
Så vi kan legge til en tredje sone: positiv, negativ, null, OK, så +0; -0; 0, OK, slik at håndterer nuller. Det kan til og med være en fjerde kode for hva du skal gjøre hvis noen skriver inn tekst. Greit nå, neste konsept, i det tilpassede tallformatet, i tillegg til nullene, kan du sette tekst i anførselstegn! OK, så la oss ta en titt på det tilpassede tallformatet her, for positive tall, ordene “Please Remit” og deretter tallet, for negative “Credit Balance of“ the number and “(Do not Pay)”. Og for 0 viser jeg ikke engang tallet, jeg viser bare “No Balance”, ok. Så du kan faktisk bruke dette til å erstatte tallet, dette er -1, 0 og 1, og dette er formler, for å få formelen er bare å kopiere den ned. Alle negative tall vil si "Mister!" eller "Ned" eller ordet "Minus" og tallet, så vi legger inn -5, sier fremdeles "Mister!" eller “Ned” eller “Minus 5”.For 0 kan vi si det "null" eller si "flatt" eller si "0", og la det for et positivt tall sette det i 55, "Vinner!", "Opp" eller "Positiv 55", ok, så du kan faktisk legge til ord. Og hva er kult, så dette er 55, 55, 55, hvis jeg ville SUM det, er de virkelig tall, og det kommer virkelig til å fungere, som normalt, fungerer SUM ikke med tekst, men det gir meg faktisk et svar . Og så, hvis du vil ha tall der, men vise ord i cellene, er dette et flott triks.ikke jobbe med tekst, men det gir meg faktisk svar. Og så, hvis du vil ha tall der, men vise ord i cellene, er dette et flott triks.ikke jobbe med tekst, men det gir meg faktisk svar. Og så, hvis du vil ha tall der, men vise ord i cellene, er dette et flott triks.
Greit nå, dette er tegnene som kan brukes i det tilpassede tallformatet uten anførselstegn, så hvis du tilfeldigvis bare trenger et + eller et -, trenger du ikke å sette det i anførselstegn. Og det er noen tillegg til dette, som en gang jeg så denne listen, er jeg som "OK, la oss se hva vi kan skru på med." Alt dette er tallene 1 2 3 4, så hvis jeg brukte et tilpasset tallformat på ((nummer> &, vil alt dette vises. Hvis jeg vil skille sifrene med bindestreker, er dette et tilpasset tallformat for å få det fungerer. Og alt dette er ekte, så 7854, se alt endres, alt dette er formler som kommer hit. Så jeg kan liksom skrue med det egendefinerte tallformatet og sette tegn foran det, sette tegn etter det. ikke i listen som jeg vet at det fungerer er K, du har lov til å sette en K på slutten, og det 's ikke i anførselstegn. Men M, fordi M betyr måneder eller minutter, må du sette det i anførselstegn eller sette dette M for å få et M til å vises på slutten.
OK, neste konsept, i hver sone har du lov til å spesifisere farge, ikke sant, så her oppe er de positive tallene grønne, og de negative tallene er blå. Ctrl + 1 for å komme til Formater celler, før det tilpassede tallformatet, i den første sonen, den positive sonen, sa jeg grønt, og for negative tall sa jeg blått. Hvilken farge har vi lov til å bruke der? Går tilbake til dagene med 8 farger, så svart, grønn, hvit, blå, magenta, gul, cyan og rød. Men takket være Mike Alexander, har du lov til å bruke 56 andre farger, Color 1, Color 2, må være i (), slik er det 56 farger. Hvis du trenger å endre disse, blir vi veldig dype nå, nå skal vi inn i Charlie kid territorium! Excel-alternativer, Lagre, og klikk deretter på Farger, greit og lignende. Hvis jeg vil endre den oransje til noe annet, kanskje den lilla, klikker du OK, klikker OK,klikk OK, og en av disse, jeg kan ikke fortelle hvilken det er som ville ha endret seg til den fargen. Et annet kult triks, hva om du vil skjule alle tallene? Så du har noen hemmeligheter her, og du vil ikke at noen skal se dem. Ctrl + 1, legg et tilpasset tallformat. Jeg sier "Vis ingenting for positivt, ingenting for negativt, ingenting for 0", klikk OK, og alt er skjult. Selvfølgelig kan de fremdeles se det her oppe i formellinjen, så det er ikke perfekt, ok, her er noen andre kundeformateringsfigurer.ingenting for negativt, ingenting for 0 ”, klikk OK, og alt er skjult. Selvfølgelig kan de fremdeles se det her oppe i formellinjen, så det er ikke perfekt, ok, her er noen andre kundeformateringsfigurer.ingenting for negativt, ingenting for 0 ”, klikk OK, og alt er skjult. Selvfølgelig kan de fremdeles se det her oppe i formellinjen, så det er ikke perfekt, ok, her er noen andre kundeformateringsfigurer.
En stjerne sier “Vis neste tegn som en sjekkforfatter”. Så de ** sier "Du vet, fyll området opp til tallet med stjerner", men du kan endre det, du kan lage den karakteren hva du vil! Så *! vil sette alle utropstegn der, la oss endre dette, Ctrl + 1, vil endre det til * X, og jeg vil få alle X opp til det punktet. Så du kan opprette sjekk-skribent-effekten, men det trenger ikke å være med en stjerne, du kan legge det du vil der inne. Sett et komma på slutten, så dette er 123456, tilpasset tallformat med komma etter null sier "Vis dette i tusenvis, delt på 1000", jeg setter, K der i tusenvis. Når du vil gjøre det i millioner, så er det vårt 0,0 ,, hvert komma sier "Display, men del det med ytterligere 1000". Så det deles med en million,sette "M" for å vise millioner. Jeg vet at noen bruker MM der, søsteren min Alice (?) Som jobber for Procter & Gamble, "MM" må være i anførselstegn.
Nå her, dette er en der vi tvinger et nummer. Hvis noen skriver inn noe ikke er et nummer, vil meldingen i 4. sone overskrive det. Så hvis vi setter inn tallet 1, er det flott, setter inn -1, det er flott, hvis vi setter inn “Hei”, roper han til oss, “Skriv inn et tall!”. OK, igjen, dette er formler som trekker det jeg skriver inn i A over til B. Du kan bruke forhold, greit, så det positive, negative, null, du kan overstyre det. Så jeg skal si rødt hvis det er 90, og du kan bare ha to forhold, og deretter en tredje sone for ting som ikke er 90, gammel, gammeldags betinget formatering. En annen, her er en annen, dette er bare sprøtt, setter en understreking, og deretter gir enhver karakter nok plass til å passe den karakteren.Du ser dette mye med _ (for å holde de positive tallene på linje med de negative tallene, men du kan virkelig skru med Excel. Så W er det bredeste tegnet, så dette setter det første sifferet, og deretter nok plass til 2 W , og deretter det andre sifferet og nok plass til et N, og deretter det tredje sifferet og nok plass for et jeg, og til slutt, det 4. sifferet. Er dette nyttig for noe? Jøss, jeg vet ikke, men det er snill av vill, at du kan tvinge forskjellig avstand mellom sifrene ved å bruke understrekingene.men det er litt vilt at du kan tvinge forskjellig avstand mellom sifrene ved å bruke understrekene.men det er litt vilt at du kan tvinge forskjellig avstand mellom sifrene ved å bruke understrekene.
Greit, datoer, m / d / åå, du får et 2-sifret år, utvides til åååå, du får et firesifret år, setter mm, det tvinger et ledende 0 før det 7, dd tvinger et ledende 0 før det 3 Hvis du vil bruke noe gammeldags koboltdatoformat, ikke sett noen /, bare alle tegnene, ÅÅÅÅMMDD. mmm staver ut månedens forkortelse, mmmm staver ut hele måneden, mmmmm gir deg JASON-formatet. Hvorfor kaller jeg det JASON-formatet? Hvis du ser på Wall Street Journal, har deres økonomiske shorts alltid JFMAMJJASOND. Tilbake i 1984 vil jeg ha en vinylkopi av "Frankie & the Knockouts - Below the Belt" for å svare på triviaspørsmålet "Hva manns navn kan staves ved å bruke månedens forkortelser?" Og fordi jeg leste The Wall Street Journal på skolen, visste jeg at Jason var navnet, og jeg vant det.Jeg ga det sannsynligvis bort på et garasjesalg for $ 50c, det er dårlig. OK, dd gir deg det ledende sifferet før datoen, ddd staver ut forkortelsen, dddd gir deg hele dagsnavnet, ukedagsnavnet, ddddd gjør ingenting. Du kan kombinere disse slik du vil. dddd staver dagen, tirsdag, legg ordet "the" i anførselstegn, selve dagen for mmm, gir deg forkortelsen, ok, så du kan kombinere disse slik du vil.
Tid, en rar ting med tiden så, h: mm, eller AM / PM, så hvis du ikke setter AM / PM, tvinger den til 24-timers tid, hvis du gjør det, så får du 14:15. Hvis du har hh, tvinger det det ledende nullpunktet før 2, du kan til og med legge til .0 etter sekunder for å få millisekunder. Nå er det den bisarre tingen, faktisk før jeg kommer til den bisarre tingen, vet jeg ikke, la oss gjøre den bisarre tingen først. La oss gjøre = SUM av alle disse, så alle disse er 14 timer, 6 * 14 = 84, det skal være 84 timer, men hvis jeg formaterer det som en tid, sier det 1:30 på ettermiddagen, ok, og her er tingen. Så vi har et nummer her, i morges 09.10.2016 klokka 06.00, og jeg har bare formler som kopierer det ned, og jeg kan overstyre, husk, det formateres som en fasade. Jeg kan si "Hei, bare vis meg årene, bare vis meg måneden, bare vis meg dagene,bare vis meg tidsdelen av det. ” Og her i dette SUMMET, når vi ber om en stund, sier vi "Hei, ignorere årene, månedene, dagene, bare vis meg tiden." og hva dette egentlig er, dette er tre dager + 1:30 på ettermiddagen.
Så for å få dette til å vises riktig, kommer du enten inn i Time og ser etter den som sier 37 timer, eller går til Custom, og du vil se å sette (h) sier "Vis oss hele antall timer . ” Så hvis jeg trenger å se timer og minutter, (h): mm vil vise meg at det er faktisk 85,5 timer, skjønner jeg, jeg avskrev de 15 minuttene og prøvde å få 84 i hodet, jeg var nær. Hvor ellers kan du bruke firkantede parenteser? Så 6 AM, kan du vise timer, du kan bare vise minutter, 06:00 er 360 minutter, eller bare sekunder ved å bruke firkantede parenteser. OK, la oss gå tilbake til et av tallformatene vi har under regnskapsformatet, og se om vi, etter denne virkelig lange episoden, forstår hva som skjer.
Så hva dette sier, _ (sier "La være så mye plass som a (i den positive verdien." "*" Sier "Gjenta mellomrom til du kommer til tallene." #, ## 0 sier "Vi skal å bruke komma-skilletegn, ikke sett ledende tegn hvis vi ikke har presisjonen, legg alltid to desimaler. ”Og så _) sier“ La være så mye plass som), og det er for de positive tallene. ” OK nå, for de negative tallene, er det veldig bisarr her ute, før $ de forlater så mye plass som en (så med andre ord, de kommer aldri til å vise det. Igjen "*", men her de ' skal nå sette inn (og). Og så for 0, la like mye plass som (og like mye plass som). Ikke legg noe, vi erstatter null med "-", men la så mye plass være som to desimaler, slik at denkommer til å stille opp med dette. Og så til slutt i 4. sone, hvis de skriver inn tekst, la det være nok plass til (og a), og legg deretter teksten inn.
OK, dette var alltid bare mye tull enn meg, men det er spesifikke betydninger for alle disse karakterene. Det er sprøtt at all denne informasjonen, podcast til 18 minutter, kommer fra 3 sider i denne boken, tenk bare hvor mye er det på alle 200 sider i boka. Kjøp boken, klikk på “i” øverst til høyre, du vil sette pris på den, og jeg vil sette pris på den. OK, lang, lang episode: Tallformatering er en fasade, Excel lagrer en ting, den viser oss en annen. Reduser desimal og øk desimal er et godt eksempel på det, de lagrer fremdeles alle desimaler, men det forenkler skjermen. Det er 11 formater her, men egentlig for å komme til dem, går du til dialogstarteren, og deretter kan du velge noe og deretter endre det i Tilpasset hvis du vil. Snakket om alle disse tilpassede nummerformateringskodene,hvis du har bedre tips, vennligst legg dem inn i YouTube-kommentarene.
Null i formatkoden sier "Excel MÅ vise sifferet, enten det er presisjon eller ikke." Hash-tegn, pundtegn sier at de kan vise sifferet hvis det er presisjon, men de trenger ikke. Spørsmålstegnet sier: "Gi plass til sifferet hvis det ikke er nok presisjon." Det kan være opptil 4 formater atskilt med semikolon, på den måten har vi et annet tall for positiv, negativ, null og tekst. 2 formater, den første er for ikke-negativ, den andre er for negativ, hvis du hadde et tredje format, er det tredje formatet for 0, det fjerde formatet for tekst, hvis det er der. Greit, vi kan vise tekst med et tall, eller vise tekst i stedet for tallet, "Winner", "Loser", eller "Up", "Flat", "Down". Du kan spesifisere en farge for hver sone, for å endre fargekodene, besøk Excel Alternativer, Lagre, Farger.Stjerner et hvilket som helst tegn for å gjenta det tegnet før tallene, som en sjekkskriver ville gjort. Sett komma på slutten for å dele med tusen, to komma for å dele med millioner, 3 komma dele med milliarder, og så videre. Vi kan bruke forhold i sonen, og da vil X understreke et mellomrom på størrelse med X. Greit, de forskjellige variasjonene av måneder, enten du setter ett siffer, to sifre, forkortelsen, staver det eller bare den første initialen . Dager, ett siffer, to sifre, forkort eller skriv det ut, det er ingen femte. Og til slutt, for å få tider i overkant av 24 timer, må du sette den h i hakeparenteser, og andre varianter av det.Vi kan bruke forhold i sonen, og da vil X understreke et mellomrom på størrelse med X. Greit, de forskjellige variasjonene av måneder, enten du setter ett siffer, to sifre, forkortelsen, staver det eller bare den første initialen . Dager, ett siffer, to sifre, forkort eller skriv det ut, det er ingen femte. Og til slutt, for å få tider i overkant av 24 timer, må du sette den h i hakeparenteser, og andre varianter av det.Vi kan bruke forhold i sonen, og da vil X understreke et mellomrom på størrelse med X. Greit, de forskjellige variasjonene av måneder, enten du setter ett siffer, to sifre, forkortelsen, staver det eller bare den første initialen . Dager, ett siffer, to sifre, forkort eller skriv det ut, det er ingen femte. Og til slutt, for å få tider i overkant av 24 timer, må du sette den h i hakeparenteser, og andre varianter av det.
Takk for at du henger til slutt! Vi sees neste gang for nok en netcast fra!
Last ned fil
Last ned eksempelfilen her: Podcast2044.xlsm
