7 fantastiske Excel -tilpassede nummerformat -tricks (du skal vide)

Excel -tilpasset talformatering er tøjet til data i excel -celler. Du kan klæde disse på, som du vil. Alt du behøver er lidt viden om, hvordan Excel Custom Number Format fungerer.

Med tilpasset talformatering i Excel kan du ændre måden, værdierne i cellerne viser på, men samtidig bevare den oprindelige værdi intakt.

For eksempel kan jeg få en datoværdi til at vises i forskellige datoformater, mens den underliggende værdi (tallet, der repræsenterer datoen) forbliver den samme.

I denne vejledning dækker jeg alt om brugerdefineret talformat i Excel og viser dig også nogle virkelig nyttige eksempler, som du kan bruge i dit daglige arbejde.

Så lad os komme i gang!

Excel Custom Number Format Construct

Inden jeg viser dig det fantastiske, lad mig kort forklare, hvordan tilpasset talformatering fungerer i Excel.

Efter design kan Excel acceptere følgende fire datatyper i en celle:

• Et positivt tal
• et negativt tal
• 0
• Tekststrenge (dette kan omfatte rene tekststrenge såvel som alfanumeriske strenge)

En anden datatype, som Excel kan acceptere, er datoer, men da alle dato- og tidsværdier er gemt som tal i Excel, vil disse blive dækket som en del af det positive tal.

For enhver celle i et regneark i Excel kan du angive, hvordan hver af disse datatyper skal vises i cellen.

Og herunder er syntaksen, hvor du kan angive dette format.

;;;

Bemærk, at alle disse er adskilt af semikolon (;).

Alt, hvad du indtaster i en celle i Excel, falder ind i en af ​​disse fire kategorier, og derfor kan der angives et tilpasset format til det.

Hvis du kun nævner:

• Et format: Det anvendes på alle fire sektioner. For eksempel, hvis du bare skriver Generelt, vil det blive anvendt for alle fire sektioner.
• To formater: Den første anvendes på positive tal og nuller, og den anden anvendes på negative tal. Tekstformat bliver som standard Generelt.
• Tre formater: Den første anvendes på positive tal, den anden på negative tal, den tredje på nul, og teksten forsvinder, da der ikke er angivet noget for tekst.

Hvis du vil lære mere om tilpasset talformatering, vil jeg stærkt anbefale Office Hjælp -sektionen.

Hvordan dette fungerer, og hvor du skal indtaste disse formater, er dækket senere i denne vejledning. For nu skal du bare huske på, at dette er syntaksen for formatet for enhver celle

Sådan fungerer tilpasset talformat i Excel

Derefter åbner du en ny Excel -projektmappe, som standard har alle cellerne i alle regnearkene i denne projektmappe det generelle format, hvor:

• Positive tal vises som det er (justeret til venstre)
• Negative tal er vist med et minustegn (og justeret til venstre)
• Nul vises som 0 (og justeret til venstre)
• Tekst vises som den er (justeret til højre)

Og hvis du indtaster en dato, vises den baseret på din regionale indstilling (i DD-MM-ÅÅÅÅ eller MM-DD-ÅÅÅÅ-format).

Så som standard er cellens nummerformatering:

Generelt; Generelt; Generelt; Generelt

Hvor hver datatype er formateret som Generelt (og følg de regler, jeg nævnte ovenfor, baseret på om det er et positivt tal, negativt tal, 0 eller tekst)

Nu er standardindstillingen i orden, men hvordan ændrer du formatet. For eksempel, hvis jeg vil have mine negative tal til at blive vist i rød farve, eller jeg vil have mine datoer til at blive vist i et bestemt format, hvordan gør jeg det?

Brug af rullemenuen Format i båndet

Du kan finde nogle af de almindeligt anvendte talformater på fanen Startside i gruppen Nummer.

Det er en rullemenu, der viser mange af de almindeligt anvendte formater, som du kan anvende på en celle ved blot at markere cellen og derefter vælge formatet fra denne liste.

For eksempel, hvis du vil konvertere tal til procenter eller konverterede datoer til kort datoformat eller langtidsformat, kan du nemt gøre det ved at bruge mulighederne i rullemenuen.

Brug af dialogboksen Formatceller

Formater celler dialogbokse, hvor du får den maksimale fleksibilitet, når det kommer til at anvende formater på en celle i Excel.

Det har allerede mange forhåndsdefinerede formater, som du kan bruge, eller hvis du vil oprette dit eget tilpassede nummerformat, så kan du også gøre det.

Sådan åbnes dialogboksen Format celler;

1. Klik på fanen Startside
2. I gruppen Nummer skal du klikke på ikonet til dialogboksens startprogram (den lille vippede pil nederst til højre i gruppen).

Dette åbner dialogboksen Format celler. I den finder du alle formaterne på fanen Nummer (sammen med muligheden for at oprette tilpassede celleformater)

Du kan også bruge følgende tastaturgenvej til at åbne dialogboksen Formatceller:

CONTROL + 1 (alle kontroltasterne, og tryk derefter på tasten 1).

Hvis du bruger Mac, kan du bruge CMD + 1

Lad os nu få et par praktiske eksempler, hvor vi vil oprette vores egne brugerdefinerede talformater.

Eksempler på brug af brugerdefineret talformatering i Excel

Lad os nu få et par praktiske eksempler, hvor vi vil oprette vores egne brugerdefinerede talformater.

Skjul en celleværdi (eller Skjul alle værdier i en række celler)

Nogle gange har du måske brug for at skjule cellens indhold (uden at skjule rækken eller kolonnen, der har cellen).

Et praktisk tilfælde af dette er, når du opretter et Excel -dashboard, og der er et par værdier, du bruger i instrumentbrættet, men du ikke ønsker, at brugerne skal se det. så du kan simpelthen skjule disse værdier

Antag, at du har et datasæt som vist herunder, og du vil skjule alle de numeriske værdier:

Sådan gør du dette ved hjælp af tilpasset formatering i Excel:

1. Vælg cellen eller celleområdet, som du vil skjule celleindholdet for
2. Åbn dialogboksen Format celler (tastaturgenvej Control + 1)
3. Klik på Custom på fanen Nummer
4. Indtast følgende format i feltet Type:

   ;;;

Hvordan virker det?

Formatet for fire forskellige typer datatyper er divideret med semikolon i følgende format:

;;;

I denne teknik har vi fjernet formatet og kun specificeret semikolon, hvilket angiver, at vi ikke ønsker nogen formatering til cellerne. Dette er en indikation for Excel for at skjule alt indhold, der er der i cellen.

Bemærk, at selvom dette skjuler celleindholdet, er det stadig der. så hvis du har tal, som du har skjult, kan du stadig bruge disse i beregninger (hvilket gør dette til et godt trick til Excel -dashboards og rapporter).

Vis negative tal i rød farve

Som standard vises negative tal med et minustegn (eller inden for parentes i nogle regionale indstillinger).

Du kan bruge tilpasset talformatering til at ændre farven på de negative tal og få dem til at vises i rød farve.

Antag, at du har et datasæt som vist nedenfor, og du vil fremhæve alle de negative værdier i rød farve.

Nedenfor er trinene for at gøre dette:

1. Vælg det celleområde, hvor du vil vise negative tal med rødt
2. Åbn dialogboksen Format celler (tastaturgenvej Control + 1)
3. Klik på Custom på fanen Nummer
4. Indtast følgende format i feltet Type:

Generelt; [Rød] -Generelt

Ovenstående format får dine celler med negative værdier til at blive vist i rød farve.

Hvordan virker det?

Når du kun angiver to formater som brugerdefineret talformat, betragtes det første som positive tal og 0, og det andet betragtes som negative tal (den næste værdi bliver som standard det generelle format).

I ovenstående format, som vi har brugt, har vi angivet den farve, vi ønsker, at formatet skal optage i firkantede parenteser.

Så selvom de negative tal tager det generelle format, vises de i rød farve med et minustegn.

Hvis du vil få de negative tal til at stoppe i rød farve inden for parentes (i stedet for minustegnet), kan du bruge nedenstående format:

Generelt; [Rød] (Generelt)

Føj tekst til tal (f.eks. Millioner/milliarder)

En fantastisk ting ved tilpasset talformatering er, at du kan tilføje et præfiks eller en suffikstekst til et nummer, mens du stadig beholder det originale nummer i cellen.

For eksempel kan jeg få '100' til at vise sig som '$ 100 millioner', mens jeg stadig har den oprindelige værdi i cellen, så jeg kan bruge den i beregninger.

Antag, at du har et datasæt som vist nedenfor, og du vil vise disse tal med et dollartegn og ordet million foran det.

Nedenfor er trinene for at gøre dette:

1. Vælg det datasæt, hvor du vil tilføje teksten til tallene
2. Åbn dialogboksen Format celler (tastaturgenvej Control + 1)
3. Klik på Custom på fanen Nummer
4. Indtast følgende format i feltet Type:

$ General "Million"

Bemærk, at dette tilpassede talformat kun påvirker tal. Hvis du indtaster tekst i den samme celle, ser den ud som den er.

Hvordan virker det?

I dette eksempel, til det generelle format, har jeg tilføjet ordet million i dobbelte anførselstegn. Alt, hvad jeg tilføjer i dobbelte anførselstegn til formatet, vises som i cellen.

Jeg tilføjede også dollartegnet før det generelle format, så alle mine tal også viser dollartegnet før tallet.

Bemærk: Hvis du undrer dig over, hvorfor dollartegnet ikke er i dobbelte anførselstegn, mens ordet Million er i dobbelte anførselstegn, skyldes det, at Excel genkender dollar som et symbol, der ofte bruges i tilpasset talformatering og ikke kræver dig at tilføje dobbelt citater til det. Du kan fortsætte og tilføje dobbelte anførselstegn til dollartegnet, men når du lukker dialogboksen Formatceller og åbner den igen, vil du bemærke, at Excel har fjernet det

Skjul tal og tekst

Med tilpasset nummerformatering kan du maksimalt vurdere to betingelser, og baseret på disse betingelser kan du anvende et format på cellen.

Antag for eksempel, at du har en liste over scoringer for nogle elever, du vil vise teksten Bestået eller Ikke bestået baseret på scoren (hvor enhver score mindre end 35 skal vise 'Ikke bestået', ellers skal den vise 'Bestå'.

Nedenfor er trinene til at vise scores som bestået eller mislykket i stedet for værdien ved hjælp af tilpasset talformat:

1. Vælg det datasæt, hvor du har scorerne
2. Åbn dialogboksen Format celler (tastaturgenvej Control + 1)
3. Klik på Custom på fanen Nummer
4. Indtast følgende format i feltet Type:

   [<35] "Fejl"; "Bestået"

Nedenfor er, hvordan dine data ville se ud, efter at du har anvendt ovenstående format.

Bemærk, at det ikke ændrer værdien i cellerne. Cellen fortsætter med at have scores i numeriske værdier, mens den viser 'Godkend' eller 'Ikke bestået' afhængigt af celleværdien.

Hvordan virker det?

I dette eksempel skal betingelsen angives inden for de firkantede parenteser. brugerdefineret talformatering evaluerer derefter værdien i cellen og anvender formatet i overensstemmelse hermed.

Så hvis værdien i cellen er mindre end 35, så er tallet skjult, og i stedet vises teksten 'Fail', tilføjelse af alle andre sager, 'Pass' vises.

På samme måde, hvis du vil konvertere en række celler, der har 0’er og 1’ere til SAND og FALSK, kan du bruge nedenstående format:

[= 0] "FALSK"; [= 1] "SAND"

Husk, at du kun kan bruge to betingelser i tilpasset talformatering. Hvis du prøver at bruge mere end to betingelser, kommer det til, så din prompt giver dig besked om, at det ikke kan acceptere dette format.

Skjul tekst, men vis tal

Nogle gange, når du downloader dine data fra databaser, da der er tomme, er de fyldt med værdi som f.eks. Ikke tilgængelig eller NA eller -

Med brugerdefineret talformatering kan du skjule alle tekstværdierne, mens tallene bevares, som de er. dette sikrer også, at dine originale data er intakte (da du ikke sletter tekstværdien, gemmer du dem bare)

Antag, at du har et datasæt som vist herunder, hvor du vil skjule alle tekstværdierne:

Nedenfor er trinene for at gøre dette:

1. Vælg det datasæt, hvor du har dataene
2. Åbn dialogboksen Format celler (tastaturgenvej Control + 1)
3. Klik på Custom på fanen Nummer
4. Indtast følgende format i feltet Type:

Generel; -Generel; Generel;

Bemærk, at jeg har brugt det generelle format til tal. Du kan vælge hvilket som helst format (f.eks. 0, 0. #, #0.0%). Bemærk også, at der er et minustegn (-) før det andet generelle format, da det repræsenterer formatet for negative tal.

Hvordan virker det?

I dette eksempel, da vi ønskede at skjule alle tekstværdierne, angav vi simpelthen ikke formatet for det.

Og da vi specificerede formatet for de andre tre datatyper (positivt tal, negativt tal og nul), forstod Excel, at vi målrettet har ladet formatet for tekstværdien være tomt, og det bør ikke vise noget, hvis en celle har en tekst snor.

Vis tal som procent (%)

Med tilpasset nummerformatering kan du også ændre tallene og få dem til at vises i procent.

Antag, at du har et datasæt som vist nedenfor, og du vil konvertere disse tal til at vise den tilsvarende procentværdi:

Nedenfor er trinene for at gøre dette:

1. Vælg de celler, hvor du har tallene (som du vil konvertere til procent)
2. Åbn dialogboksen Format celler (tastaturgenvej Control + 1)
3. Klik på Custom på fanen Nummer
4. Indtast følgende format i feltet Type:

0%

Dette vil ændre tallene til deres procenter. For eksempel ændres det fra 0,11 til 11%.

Hvis alt, hvad du vil gøre, konverteres et tal til den tilsvarende procentværdi, ville en bedre måde at gøre dette være at bruge procentindstillingen i dialogboksen Formatceller.

Hvor tilpasset formatering kan være nyttig, er når du vil have mere kontrol over, hvordan du vil vise procentværdien.

For eksempel, hvis du vil vise yderligere tekst med procentdelen, alt hvad du vil vise negative procenter i rød farve, er det bedre at bruge tilpasset formatering

Vis tal i en anden enhed (millioner som milliarder eller gram som kilogram)

Hvis du arbejder med store tal, vil du måske vise disse i en anden enhed. for eksempel, hvis du har salgsdata fra nogle virksomheder, vil du måske vise salget i millioner eller milliarder (mens du stadig beholder den oprindelige salgsværdi i cellerne)

Heldigvis kan du nemt gøre det med tilpasset nummerformatering.

Antag, at du har et datasæt som vist nedenfor, og du vil vise disse salgsværdier i millioner.

Nedenfor er trinene for at gøre dette:

1. Vælg de celler, hvor du har tallene
2. Åbn dialogboksen Format celler (tastaturgenvej Control + 1)
3. Klik på Custom på fanen Nummer
4. Indtast følgende format i feltet Type:

0.0,,

Ovenstående ville give dig resultatet som vist nedenfor (værdier i millioner i et ciffer efter decimalen):

Hvordan virker det?

I ovenstående format angiver et nul før decimalen, at Excel skal vise hele tallet (præcis som det er i cellen), og et 0 efter decimalen angiver, at det kun skal vise et signifikant ciffer efter decimalpunktet.

Så 0,0 ville altid være et tal med et ciffer efter decimalpunktet.

Når du tilføjer et komma efter 0.0, barberer det det sidste trecifrede nummer af. For eksempel ville 123456 blive til 123,4 (de sidste tre cifre fjernes, og da det skal vise 1 ciffer efter decimalpunktet, viser det 4)

Hvis du tænker over det, ligner det at dividere tallet med 1000. Så hvis du vil vise et tal i enheden 'millioner', skal du tilføje 2 kommaer, så det ville barbere de sidste 6 cifre af.

Og hvis du også vil vise ordet million eller alfabetet M efter tallet, skal du bruge nedenstående format

0,0 ,, "M"