Excel

Brugerdefinerede Excel -formater

Excel Custom Number Formats

Introduktion

Talformater styrer, hvordan tal vises i Excel. Den vigtigste fordel ved talformater er, at de ændrer, hvordan et tal ser ud uden at ændre data. De er en fantastisk måde at spare tid i Excel på, fordi de udfører en enorm formatering automatisk. Som en bonus får de regneark til at se mere konsistente og professionelle ud.





Video: Hvad er et talformat

Hvad kan du gøre med tilpassede talformater?

Brugerdefinerede talformater kan styre visningen af ​​tal, datoer, tider, brøker, procenter og andre numeriske værdier. Ved hjælp af brugerdefinerede formater kan du f.eks. Formatere datoer for kun at vise månedsnavne, formatere store tal i millioner eller tusinder og vise negative tal med rødt.





Eksempel på tilpassede talformater

Hvor kan du bruge tilpassede talformater?

Mange områder i Excel understøtter talformater. Du kan bruge dem i tabeller, diagrammer, pivottabeller, formler og direkte på regnearket.



  • Dialog med regneark - format celler
  • Pivottabeller - via værdifeltindstillinger
  • Diagrammer - datatiketter og aksemuligheder
  • Formler - via TEKST -funktion

Hvad er et talformat?

Et talformat er en særlig kode til at styre, hvordan en værdi vises i Excel. For eksempel viser nedenstående tabel 7 forskellige talformater, der er anvendt på samme dato, 1. januar 2019:

Input Kode Resultat
1-jan-2019 åååå 2019
1-jan-2019 åå 19
1-jan-2019 mmm Jan
1-jan-2019 MMM januar
1-jan-2019 d 1
1-jan-2019 ddd Din
1-jan-2019 dddd tirsdag

Det vigtigste at forstå er, at talformater ændrer måden, hvorpå numeriske værdier er vises , men de ikke skift de faktiske værdier.

Hvor kan du finde talformater?

På fanen Hjem på båndet finder du en menu med indbyggede talformater. Under denne menu til højre er der en lille knap for at få adgang til alle talformater, herunder tilpassede formater:

Menuen Talformat på fanen Startside på båndet

Denne knap åbner dialogboksen Formater celler. Du finder en komplet liste over talformater, organiseret efter kategori, på fanen Nummer:

Formater celler dialogboks på fanen Nummer

Bemærk: du kan åbne dialogboksen Format celler med tastaturgenvejen Control + 1.

Generelt er standard

Som standard starter celler med det generelle format, der anvendes. Visningen af ​​tal ved hjælp af det generelle talformat er noget 'flydende'. Excel viser så mange decimaler, som rummet tillader, og afrunder decimaler og bruger videnskabeligt talformat, når pladsen er begrænset. Skærmen herunder viser de samme værdier i kolonne B og D, men D er smallere, og Excel foretager justeringer i farten.

Generelt talformat i smal kolonne

Sådan ændres nummerformater

Du kan vælge standardnummerformater (generelt, tal, valuta, regnskab, kort dato, lang dato, tid, procent, brøk, videnskabelig, tekst) på fanen Hjem på båndet ved hjælp af menuen Talformat.

Bemærk: Når du indtaster data, ændrer Excel nogle gange nummerformater automatisk. Hvis du f.eks. Indtaster en gyldig dato, ændres Excel til 'Dato' -format. Hvis du indtaster en procentdel som 5%, ændres Excel til Procentdel og så videre.

Genveje til talformater

Excel giver en række tastaturgenveje til nogle almindelige formater:

Format Genvej
Generelt format Ctrl Shift ~
Valutaformat Ctrl Skift $
Procentformat Ctrl Skift %
Videnskabeligt format Ctrl Shift ^
Datoformat Ctrl Skift #
Tidsformat Ctrl Shift @
Tilpassede formater Kontrol + 1

Se også: 222 Excel -genveje til Windows og Mac

Hvor skal man indtaste tilpassede formater

Nederst i de foruddefinerede formater ser du en kategori, der kaldes brugerdefineret. Kategorien Brugerdefineret viser en liste over koder, du kan bruge til tilpassede talformater, sammen med et inputområde til at indtaste koder manuelt i forskellige kombinationer.

Tilpasset kategori i dialogboksen Formatceller

Når du vælger en kode fra listen, ser du den vises i feltet Type -input. Her kan du ændre eksisterende brugerdefinerede kode eller indtaste dine egne koder fra bunden. Excel viser et lille eksempel på koden, der er anvendt på den første valgte værdi over inputområdet.

Bemærk: Brugerdefinerede talformater findes i en projektmappe, ikke generelt i Excel. Hvis du kopierer en værdi, der er formateret med et tilpasset format fra en projektmappe til en anden, overføres det tilpassede talformat til projektmappen sammen med værdien.

Sådan opretter du et tilpasset talformat

Følg denne enkle 4-trins proces for at oprette brugerdefineret talformat:

  1. Vælg celle (r) med værdier, du vil formatere
  2. Control + 1> Tal> Brugerdefineret
  3. Indtast koder og se forhåndsvisningsområdet for at se resultatet
  4. Tryk på OK for at gemme og anvende

Tip: Hvis du vil basere dit tilpassede format på et eksisterende format, skal du først anvende basisformatet, derefter klikke på kategorien 'Brugerdefineret' og redigere koder, som du vil.

Sådan redigeres et tilpasset talformat

Du kan ikke rigtig redigere et tilpasset talformat i sig selv. Når du ændrer et eksisterende tilpasset talformat, a ny format er oprettet og vises på listen i kategorien Brugerdefineret. Du kan bruge knappen Slet til at slette tilpassede formater, du ikke længere har brug for.

Advarsel: der er ingen 'fortryd' efter sletning af et brugerdefineret talformat!

Struktur og reference

Excel -tilpassede talformater har en specifik struktur. Hvert talformat kan have op til fire sektioner adskilt med semikolon som følger:

Brugerdefineret talformatstruktur

Denne struktur kan få brugerdefinerede talformater til at se overvældende komplekse ud. For at læse et tilpasset talformat skal du lære at få øje på semikolonerne og mentalt analysere koden i disse sektioner:

  1. Positive værdier
  2. Negative værdier
  3. Nulværdier
  4. Tekstværdier

Ikke alle sektioner kræves

Selvom et talformat kan indeholde op til fire sektioner, kræves der kun et afsnit. Som standard gælder det første afsnit for positive tal, det andet afsnit gælder for negative tal, det tredje afsnit gælder for nulværdier, og det fjerde afsnit gælder for tekst.

  • Når der kun leveres et format, bruger Excel dette format til alle værdier.
  • Hvis du angiver et talformat med kun to sektioner, bruges det første afsnit til positive tal og nuller, og det andet afsnit bruges til negative tal.
  • Hvis du vil springe et afsnit over, skal du inkludere et semikolon på den korrekte placering, men angiv ikke en formatkode.

Tegn, der vises indfødt

Nogle tegn vises normalt i et talformat, mens andre kræver særlig håndtering. Følgende tegn kan bruges uden særlig håndtering:

Karakter Kommentar
$ Dollar
+ - Plus, minus
() Parenteser
{} Krøllede seler
Mindre end, større end
= Lige
: Kolon
^ mangler
' Apostrof
/ Skråstreg
! Udråbstegn
& Ampersand
~ Accentmærke
Rumkarakter

Undslippe karakterer

Nogle tegn fungerer ikke korrekt i et brugerdefineret talformat uden at blive undgået. For eksempel kan stjernen (*), hash (#) og procent (%) tegn ikke bruges direkte i et tilpasset talformat - de vises ikke i resultatet. Escape -karakteren i tilpassede talformater er omvendt skråstreg (). Ved at placere backslash foran tegnet kan du bruge dem i tilpassede talformater:

Værdi Kode Resultat
100 # 0 # 100
100 * 0 * 100
100 \% 0 % 100

Pladsholdere

Visse tegn har særlig betydning i tilpassede talformatkoder. Følgende tegn er centrale byggesten:

hvor er målsøgning placeret i excel
Karakter Formål
0 Vis ubetydelige nuller
# Vis betydelige cifre
? Vis justerede decimaler
. Decimaltegnet
, Tusinder adskiller
* Gentag ciffer
_ Tilføj plads
@ Pladsholder til tekst

Nul (0) bruges til at tvinge til visning af ubetydelige nuller, når et tal har færre cifre end nuller i formatet. For eksempel viser det brugerdefinerede format 0.00 nul som 0.00, 1.1 som 1.10 og .5 som 0.50.

Nul eksempler på pladsholdere

Pund tegn (#) er en pladsholder til valgfrie cifre. Når et tal har færre cifre end # symboler i formatet, vises intet. Det tilpassede format#. ## viser f.eks. 1.15 som 1.15 og 1.1 som 1.1.

Pund pladsholdereksempler

Spørgsmålstegn (?) bruges til at justere cifre. Når et spørgsmålstegn indtager et sted, der ikke er nødvendigt i et tal, tilføjes et mellemrum for at opretholde visuel justering.

Spørgsmålstegn stedholdereksempler

Periode (.) er en pladsholder for decimaltegnet i et tal. Når en periode bruges i et tilpasset talformat, vil den altid blive vist, uanset om tallet indeholder decimalværdier.

Komma (,) er en pladsholder for de tusinder adskillere i det antal, der vises. Det kan bruges til at definere cifrets adfærd i forhold til tusinder eller millioner cifre.

Komma -pladsholdereksempler

Stjerne (*) bruges til at gentage tegn. Tegnet umiddelbart efter en stjerne vil blive gentaget for at fylde den resterende plads i en celle.

Stjerneteksteksempler

Underscore (_) bruges til at tilføje plads i et talformat. Tegnet umiddelbart efter et understregningstegn styrer, hvor meget plads der skal tilføjes. En almindelig brug af understregningstegnet er at tilføje plads til at justere positive og negative værdier, når et talformat kun tilføjer parenteser til negative tal. For eksempel tilføjer nummerformatet '0 _) (0)' lidt plads til højre for positive tal, så de forbliver på linje med negative tal, som er omsluttet i parentes.

Fremhæv eksempler på pladsholdere

Ved (@) - pladsholder til tekst. F.eks. Viser følgende talformat tekstværdier i blåt:

 
000[Blue]@

Se nedenfor for mere information om brug af farve.

Automatisk afrunding

Det er vigtigt at forstå, at Excel vil udføre 'visuel afrunding' med alle brugerdefinerede talformater. Når et tal har flere cifre end pladsholdere på højre side af decimalpunktet, afrundes tallet til antallet af pladsholdere. Når et tal har flere cifre end pladsholdere på venstre side af decimalpunktet, vises ekstra cifre. Dette er en visuel effekt, kun de faktiske værdier ændres ikke.

Talformater til TEKST

For at få vist både tekst og tal skal du omsætte teksten i dobbelte anførselstegn (''). Du kan bruge denne fremgangsmåde til at tilføje eller forberede tekststrenge i et tilpasset talformat, som vist i tabellen nedenfor.

Værdi Kode Resultat
10 Generelle 'enheder' 10 enheder
10 0,0 'enheder' 10,0 enheder
5.5 0,0 'fod' 5,5 fod
30000 0 'fødder' 30000 fod
95,2 'Score:' 0,0 Resultat: 95,2
1. jun 'Dato:' mmmm d Dato: 1. juni

Talformater for DATES

Datoer i Excel er kun tal, så du kan bruge tilpassede talformater til at ændre den måde, de vises på. Excel mange specifikke koder, du kan bruge til at vise komponenter i en dato på forskellige måder. Skærmen herunder viser, hvordan Excel viser datoen i D5, 3. september 2018, med en række tilpassede talformater:

Nummerformatkoder for datoer

Talformater for TIME

Tider i Excel er brøkdele af en dag. For eksempel er 12.00 PM 0.5, og 18.00 er 0.75. Du kan bruge følgende koder i tilpassede tidsformater til at vise tidens komponenter på forskellige måder. Skærmen herunder viser, hvordan Excel viser tiden i D5, 9:35:07 AM, med en række tilpassede talformater:

Nummerformatkoder til tider

Bemærk: m og mm kan ikke bruges alene i et brugerdefineret talformat, da de er i konflikt med månedenummerkoden i datoformatkoder.

Talformater for ELAPSED TIME

Forløbet tid er et specielt tilfælde og kræver særlig håndtering. Ved at bruge firkantede parenteser giver Excel en særlig måde at vise forløbne timer, minutter og sekunder. Følgende skærm viser, hvordan Excel viser forløbet tid baseret på værdien i D5, hvilket repræsenterer 1,25 dage:

Nummerformatkoder for forløbet tid

Talformater for FARVER

Excel giver grundlæggende understøttelse af farver i tilpassede talformater. Følgende 8 farver kan angives ved navn i et talformat: [sort] [hvid] [rød] [grøn] [blå] [gul] [magenta] [cyan]. Farvenavne skal vises i parentes.

Brugerdefineret talformat med farver

excel-formel til tilføjelse af en kolonne

Farver efter indeks

Ud over farvenavne er det også muligt at angive farver med et indeksnummer (Color1, Color2, Color3 osv.) Nedenstående eksempler bruger det brugerdefinerede talformat: [ColorX] 0 '▲ ▼', hvor X er et tal mellem 1-56:

 
[Color1]0'▲▼' // black [Color2]0'▲▼' // white [Color3]0'▲▼' // red [Color4]0'▲▼' // green etc.

Trekantsymbolerne er kun tilføjet for at gøre farverne lettere at se. Det første billede viser alle 56 farver på en standard hvid baggrund. Det andet billede viser de samme farver på en grå baggrund. Bemærk, at de første 8 farver, der vises, svarer til den navngivne farveliste ovenfor.

Brugerdefinerede talformatfarver på hvidt

Brugerdefinerede talformatfarver på grå baggrund

Anvend talformater i en formel

Selvom de fleste talformater anvendes direkte på celler i et regneark, kan du også anvende talformater inde i en formel med funktionen TEKST. For eksempel, med en gyldig dato i A1, viser følgende formel kun månedsnavnet:

 
= TEXT (A1,'mmmm')

Resultatet af TEKST -funktionen er altid tekst, så du er fri til at sammenkoble resultatet af TEKST til andre strenge:

 
='The contract expires in '& TEXT (A1,'mmmm')

Skærmen herunder viser talformaterne i kolonne C, der anvendes på tal i kolonne B ved hjælp af TEKST -funktionen:

Anvendelse af nummerformater med TEKST -funktionen

Betingelser

Brugerdefinerede talformater også op til to betingelser, der skrives i firkantede parenteser som [> 100] eller [<=100]. When you use conditionals in custom number formats, you override the standard [postive][negative][zero][text] structure. For example, to display values below 100 in red, you can use:

[Net][<100]00

For at vise værdier, der er større end eller lig med 100 i blåt, kan du udvide formatet således:

[Rød] [= 100] 0

Betingelser med tilpassede nummerformater

For at anvende mere end to betingelser eller for at ændre andre celleattributter, f.eks. Fyldfarve osv., Skal du skifte til Betinget formatering , som kan anvende formatering med meget mere kraft og fleksibilitet ved hjælp af formler.

Flertal tekstetiketter

Du kan bruge betingelser til at tilføje en 's' til etiketter større end nul med et tilpasset format som dette:

[= 1] 0 'dag'0' dage '

Brugerdefineret talformat til flertalstekst

Telefonnumre

Brugerdefinerede nummerformater kan også bruges til telefonnumre, som vist på skærmen herunder:

Brugerdefineret nummerformat til telefonnumre

Bemærk, at det tredje og fjerde eksempel bruger et betinget format til at søge efter numre, der indeholder et områdenummer. Hvis du har data, der indeholder telefonnumre med hårdkodet tegnsætning (parenteser, bindestreger osv.), Skal du rens telefonnumrene først så de kun indeholder tal.

Skjul alt indhold

Du kan faktisk bruge et tilpasset talformat til at skjule alt indhold i en celle. Koden er simpelthen tre semikolon og intet andet

For at afsløre indholdet igen kan du bruge tastaturgenvejen Control + Shift + ~, som anvender det generelle format.

Andre ressourcer

  • Udvikler Bryan Braun byggede en dejligt interaktivt værktøj til opbygning af brugerdefinerede talformater
Forfatter Dave Bruns


^