Excel Custom Number -formatering är klädseln för data i Excel -celler. Du kan klä dessa som du vill. Allt du behöver är lite kunskap om hur Excel Custom Number Format fungerar.
Med anpassad nummerformatering i Excel kan du ändra hur värdena i cellerna visas, men samtidigt behålla det ursprungliga värdet intakt.
Till exempel kan jag få ett datumvärde att visas i olika datumformat, medan det underliggande värdet (numret som representerar datumet) skulle förbli detsamma.
I den här självstudien kommer jag att täcka allt om anpassat nummerformat i Excel, och även visa dig några riktigt användbara exempel som du kan använda i ditt dagliga arbete.
Så låt oss komma igång!
Excel Custom Number Format Construct
Innan jag visar dig det fantastiska med det, låt mig kort förklara hur anpassad nummerformatering fungerar i Excel.
Genom design kan Excel acceptera följande fyra datatyper i en cell:
- Ett positivt tal
- ett negativt tal
- 0
- Textsträngar (detta kan inkludera rena textsträngar såväl som alfanumeriska strängar)
För alla celler i ett kalkylblad i Excel kan du ange hur var och en av dessa datatyper ska visas i cellen.
Och nedan är syntaxen där du kan ange detta format.
;;;
Observera att alla dessa är separerade med semikolon (;).
Allt du anger i en cell i Excel skulle falla i någon av dessa fyra kategorier och därför kan ett anpassat format för det anges.
Om du bara nämner:
- Ett format: Den tillämpas på alla fyra sektioner. Till exempel, om du bara skriver Allmänt, kommer det att tillämpas på alla fyra sektioner.
- Två format: Den första tillämpas på positiva tal och nollor, och den andra appliceras på negativa tal. Textformat som standard blir Allmänt.
- Tre format: Den första tillämpas på positiva tal, den andra tillämpas på negativa tal, den tredje tillämpas på noll och texten försvinner eftersom ingenting har angetts för text.
Om du vill lära dig mer om anpassad nummerformatering rekommenderar jag starkt Office -hjälpavsnittet.
Hur detta fungerar och var du ska ange dessa format täcks senare i denna handledning. Tänk bara på att detta är syntaxen för formatet för vilken cell som helstHur anpassat talformat fungerar i Excel
Sedan öppnar du en ny Excel -arbetsbok, som standard har alla celler i alla kalkylblad i arbetsboken det allmänna formatet där:
- Positiva siffror visas som det är (justerat till vänster)
- Negativa siffror visas med ett minustecken (och justerat till vänster)
- Noll visas som 0 (och justerat till vänster)
- Text visas som det är (justerat till höger)
Och om du anger ett datum visas det baserat på din regionala inställning (i formatet DD-MM-ÅÅÅÅ eller MM-DD-ÅÅÅÅ).
Så som standard är cellens talformatering:
Allmänt; Allmänt; Allmänt; Allmänt
Där varje datatyp formateras som Allmänt (och följ reglerna jag nämnde ovan, baserat på om det är ett positivt tal, negativt tal, 0 eller text)
Nu är standardinställningen okej, men hur ändrar du formatet. Till exempel, om jag vill att mina negativa siffror ska visas i röd färg eller om jag vill att mina datum ska visas i ett specifikt format, hur gör jag då.
Med hjälp av rullgardinsmenyn Format i menyfliksområdet
Du kan hitta några av de vanliga nummerformaten på fliken Start i gruppen Nummer.
Det är en rullgardinsmeny som visar många av de vanliga format som du kan använda på en cell genom att helt enkelt markera cellen och sedan välja formatet från den här listan.
Om du till exempel vill konvertera siffror till procentsatser eller konverterade datum till kortdatumformat eller långformat datumformat, kan du enkelt göra det med hjälp av alternativen i rullgardinsmenyn.
Använda dialogrutan Formatceller
Formatera celler dialogrutor där du får maximal flexibilitet när det gäller att tillämpa format på en cell i Excel.
Den har redan många färdiga format som du kan använda, eller om du vill skapa ditt eget anpassade nummerformat kan du göra det också.
Så här öppnar du dialogrutan Formatera celler;
- Klicka på fliken Hem
- I gruppen Nummer klickar du på dialogrutans startikon (den lilla lutade pilen längst ned till höger i gruppen).
Detta öppnar dialogrutan Formatera celler. I den hittar du alla format på fliken Nummer (tillsammans med möjligheten att skapa anpassade cellformat)
Du kan också använda följande kortkommando för att öppna dialogrutan formatceller:
CONTROL + 1 (alla kontrollknappar och tryck sedan på 1).
Om du använder Mac kan du använda CMD + 1
Låt oss nu ha ett par praktiska exempel där vi kommer att skapa våra egna anpassade nummerformat.
Exempel på användning av anpassad nummerformatering i Excel
Låt oss nu ha ett par praktiska exempel där vi kommer att skapa våra egna anpassade nummerformat.
Dölj ett cellvärde (eller dölj alla värden i ett cellintervall)
Ibland kan du behöva dölja cellens innehåll (utan att dölja raden eller kolumnen som har cellen).
Ett praktiskt fall av detta är när du skapar en Excel -instrumentpanel och det finns ett par värden som du använder i instrumentpanelen men du inte vill att användarna ska se den. så du kan helt enkelt dölja dessa värden
Anta att du har en dataset som visas nedan och du vill dölja alla numeriska värden:
Så här gör du med anpassad formatering i Excel:
- Markera cellen eller det cellintervall som du vill dölja cellinnehållet för
- Öppna dialogrutan Formatera celler (tangentbordsgenvägskontroll + 1)
- Klicka på Anpassad på fliken Nummer
- Ange följande format i fältet Typ:
;;;
Hur fungerar detta?
Formatet för fyra olika typer av datatyper delas med semikolon i följande format:
;;;
I denna teknik har vi tagit bort formatet och endast specificerat semikolon, vilket indikerar att vi inte vill ha någon formatering för cellerna. Detta är en indikation för Excel för att dölja allt innehåll som finns i cellen.
Observera att även om detta döljer innehållet i cellen, finns det fortfarande kvar. så om du har siffror som du har dolt kan du fortfarande använda dessa i beräkningar (vilket gör detta till ett bra trick för Excel -instrumentpaneler och rapporter).
Visa negativa siffror i röd färg
Som standard visas negativa siffror med ett minustecken (eller inom parentes i vissa regionala inställningar).
Du kan använda anpassad nummerformatering för att ändra färgen på de negativa talen och få dem att visas i röd färg.
Anta att du har en dataset som visas nedan och du vill markera alla negativa värden i röd färg.
Nedan följer stegen för att göra detta:
- Välj cellintervallet där du vill visa negativa tal i rött
- Öppna dialogrutan Formatera celler (tangentbordsgenvägskontroll + 1)
- Klicka på Anpassad på fliken Nummer
- Ange följande format i fältet Typ:
Allmänt; [Röd] -Allmänt
Ovanstående format gör att dina celler med negativa värden visas i röd färg.
Hur fungerar detta?
När du bara anger två format som det anpassade nummerformatet, anses det första för positiva tal och 0, och det andra anses för negativa tal (nästa värde som standard blir det allmänna formatet).
I ovanstående format som vi har använt har vi angett vilken färg vi vill att det formatet ska ta upp i hakparenteserna.
Så medan de negativa talen har det allmänna formatet, visas de i röd färg med ett minustecken.
Om du vill få de negativa talen att stanna i röd färg inom parentes (istället för minustecknet) kan du använda nedanstående format:
Allmänt; [Röd] (Allmänt)
Lägg till text i siffror (t.ex. miljoner/miljarder)
En fantastisk sak med anpassad nummerformatering är att du kan lägga till ett prefix eller en suffixstext till ett nummer, samtidigt som du behåller det ursprungliga numret i cellen.
Till exempel kan jag få '100' att visas som '$ 100 miljoner', samtidigt som jag fortfarande har det ursprungliga värdet i cellen så att jag kan använda det i beräkningar.
Anta att du har en dataset som visas nedan och du vill visa dessa siffror med ett dollarstecken och ordet million framför den.
Nedan följer stegen för att göra detta:
- Välj datauppsättningen där du vill lägga till texten i siffrorna
- Öppna dialogrutan Formatera celler (tangentbordsgenvägskontroll + 1)
- Klicka på Anpassad på fliken Nummer
- Ange följande format i fältet Typ:
$ General "Million"
Observera att detta anpassade nummerformat endast påverkar siffror. Om du anger text i samma cell visas den som den är.
Hur fungerar detta?
I det här exemplet, i det allmänna formatet, har jag lagt till ordet million i dubbla citattecken. Allt som jag lägger till i dubbla citattecken i formatet visas som i cellen.
Jag lade också till dollarstecknet före det allmänna formatet så att alla mina nummer också visar dollarstecknet före numret.
Obs: Om du undrar varför dollarstecknet inte finns i dubbla citattecken medan ordet Million står i dubbla citat, beror det på att Excel känner igen dollar som en symbol som ofta används i anpassad talformatering och inte kräver dig att lägga till dubbla citattecken till den. Du kan fortsätta och lägga till dubbla citattecken i dollarstecknet, men när du stänger dialogrutan formatceller och öppnar den igen kommer du att märka att Excel har tagit bort det
Dölj nummer och text
Med anpassad nummerformatering kan du bedöma högst två villkor, och baserat på dessa villkor kan du tillämpa ett format på cellen.
Anta till exempel att du har en lista med poäng för vissa elever, du vill visa texten Godkänd eller Underkänd baserad på poängen (där alla poäng mindre än 35 ska visa "Misslyckad", annars ska det visa "Godkänt".
Nedan följer stegen för att visa poäng som godkänd eller underkänd istället för värdet med hjälp av anpassat nummerformat:
- Välj datauppsättningen där du har poängen
- Öppna dialogrutan Formatera celler (tangentbordsgenvägskontroll + 1)
- Klicka på Anpassad på fliken Nummer
- Ange följande format i fältet Typ:
[<35] "Misslyckad"; "Godkänd"
Nedan ser du hur dina data skulle se ut efter att du har tillämpat ovanstående format.
Observera att det inte ändrar värdet i cellerna. Cellen fortsätter att ha poängen i numeriska värden medan den visar "Godkänd" eller "Misslyckad" beroende på cellvärdet.
Hur fungerar detta?
I det här exemplet måste villkoret anges inom hakparenteserna. anpassad nummerformatering utvärderar sedan värdet i cellen och tillämpar formatet därefter.
Så om värdet i cellen är mindre än 35, döljs numret, och istället visas texten 'Misslyckad' och lägger till alla andra fall, 'Godkänn' visas.
På samma sätt kan du använda nedanstående format om du vill konvertera ett cellintervall som har 0: or och 1: er till TRUEs och FALSE:
[= 0] "FALSE"; [= 1] "SANT"
Kom ihåg att du bara kan använda två villkor i anpassad nummerformatering. Om du försöker använda mer än två villkor kommer det att bli så att du snabbt meddelar att det inte kan acceptera det formatet.
Dölj text men visa siffror
Ibland, när du laddar ner dina data från databaser eftersom de är tomma är de fyllda med värde som t.ex. inte tillgängligt eller NA eller -
Med anpassad nummerformatering kan du dölja alla textvärden samtidigt som siffrorna behålls. detta säkerställer också att dina originaldata är intakta (eftersom du inte tar bort textvärdet döljer du det helt enkelt)
Anta att du har en datamängd som visas nedan där du vill dölja alla textvärden:
Nedan följer stegen för att göra detta:
- Välj datauppsättningen där du har data
- Öppna dialogrutan Formatera celler (tangentbordsgenvägskontroll + 1)
- Klicka på Anpassad på fliken Nummer
- Ange följande format i fältet Typ:
Allmän; -Allmän; Allmän;
Observera att jag har använt det allmänna formatet för siffror. Du kan välja vilket format du vill (t.ex. 0, 0. #, #0.0%). Observera också att det finns ett negativt tecken (-) före det andra allmänna formatet, eftersom det representerar formatet för negativa tal.
Hur fungerar detta?
I det här exemplet, eftersom vi ville dölja alla textvärden, specificerade vi helt enkelt inte formatet för det.
Och eftersom vi specificerade formatet för de andra tre datatyperna (positivt tal, negativt tal och noll), förstod Excel att vi avsiktligt har lämnat formatet för textvärdet tomt, och det ska inte visa något om en cell har en text sträng.
Visa antal i procent (%)
Med anpassad nummerformatering kan du också ändra siffrorna och få dem att visas i procent.
Antag att du har en datauppsättning som visas nedan, och du vill konvertera dessa nummer för att visa motsvarande procentvärde:
Nedan följer stegen för att göra detta:
- Markera cellerna där du har siffrorna (som du vill konvertera till procent)
- Öppna dialogrutan Formatera celler (tangentbordsgenvägskontroll + 1)
- Klicka på Anpassad på fliken Nummer
- Ange följande format i fältet Typ:
0%
Detta kommer att ändra siffrorna till deras procentsatser. Till exempel ändras den från 0,11 till 11%.
Om allt du vill göra omvandlas ett tal till motsvarande procentvärde, skulle ett bättre sätt att göra detta vara att använda procentalternativet i dialogrutan formatceller.
Där anpassad formatering kan vara användbart är när du vill ha mer kontroll över hur du vill visa procentvärdet.
Om du till exempel vill visa ytterligare text med procenten, allt du vill visa negativa procentsatser i röd färg är det bättre att använda anpassad formatering
Visa siffror i en annan enhet (miljoner som miljarder eller gram som kilogram)
Om du arbetar med stora siffror kanske du vill visa dessa i en annan enhet. till exempel, om du har försäljningsdata från vissa företag, kanske du vill visa försäljningen i miljoner eller miljarder (samtidigt som du fortfarande behåller det ursprungliga försäljningsvärdet i cellerna)
Tack och lov kan du enkelt göra det med anpassad nummerformatering.
Anta att du har en datauppsättning som visas nedan, och du vill visa dessa försäljningsvärden i miljoner.
Nedan följer stegen för att göra detta:
- Markera cellerna där du har siffrorna
- Öppna dialogrutan Formatera celler (tangentbordsgenvägskontroll + 1)
- Klicka på Anpassad på fliken Nummer
- Ange följande format i fältet Typ:
0.0,,
Ovanstående skulle ge dig resultatet enligt nedan (värden i miljoner i en siffra efter decimalen):
Hur fungerar detta?
I ovanstående format indikerar en nolla före decimalen att Excel ska visa hela talet (exakt som det är i cellen) och ett 0 efter decimalen indikerar att det bara ska visa en signifikant siffra efter decimalpunkten.
Så 0,0 skulle alltid vara ett tal med en siffra efter decimalpunkten.
När du lägger till ett komma efter 0.0 kommer det att raka av den sista tresiffriga siffran. Till exempel skulle 123456 bli 123,4 (de tre sista siffrorna tas bort, och eftersom det måste visa 1 siffra efter decimalpunkten, visar det 4)
Om du tänker efter är det ungefär som att dela talet med 1000. Så om du vill visa ett nummer i "miljoner" -enheten måste du lägga till två kommatecken så att det skulle raka av de sista sex siffrorna.
Och om du också vill visa ordet miljon eller alfabetet M efter siffran, använd nedanstående format
0,0 ,, "M"
