Den här artikeln avslöjar dolda hemligheter om Excel-nummerformatering. Hur du kan lagra nummer i Excel men lägga till ord bredvid siffrorna. Läs vidare för fler coola tips om nummerformatering.
Excel är fantastiskt att lagra ett nummer och presentera ett annat nummer. Välj valfri cell och välj Valutaformat. Excel lägger till ett dollartecken och ett komma och presenterar antalet avrundat till två decimaler. I figuren nedan innehåller cell D2 faktiskt 6.42452514. Tack och lov presenterar det inbyggda anpassade nummerformatet resultaten i ett lättläst format.
Den anpassade nummerformatkoden i D2 är $ #, ## 0.00. I den här koden krävs 0s siffror. Alla #s är valfria siffror.
Formateringskoder kan dock vara mycket mer komplexa. Koden ovan har ett format. Det formatet tillämpas på varje värde i cellen. Om du anger en kod med två format är det första formatet för icke-negativa nummer och det andra formatet för negativa nummer. Du separerar format med halvkolon. Om du anger en kod med tre format är den första för positiv, sedan negativ, sedan noll. Om du anger en kod med fyra format används de för positivt, negativt, noll och text.
Även om du använder ett inbyggt format kan du gå till Format Cells, Number, Custom och se koden som används för att generera det formatet. Här är koden för bokföringsformatet:
För att bygga ditt eget anpassade format, gå till Formatera celler, nummer, anpassad och ange koden i rutan Typ. Kolla in exemplet i rutan Prov för att se till att allt ser korrekt ut.
I följande exempel används tre zoner. Text i citat läggs till i nummerformatet för att anpassa meddelandet.
Om du skapar en zon för noll men inte lägger något där döljer du alla nollvärden. Följande kod använder färgkoder för positiva och negativa. Koden slutar i ett semikolon och skapar en zon för nollvärden. Men eftersom zonen är tom visas inga värden.
Du kan utöka detta genom att göra alla zoner tomma. En anpassad formatkod ;;; döljer värden i displayen och utskriften. Du kan dock fortfarande se värdena i formelfältet. Om du döljer värden genom att göra teckensnittet vitt, kommer ;;; kommer att förbli dold även om människor ändrar fyllningsfärgen. Följande bild innehåller några intressanta formateringstrick.
I B2 och B3, om du placerar ** före sifferkoden, kommer den att fyllas till vänster om numret med asterisker som de gamla checkwriter-maskinerna skulle göra. Men det finns inget som säger att du måste använda asterisker. Oavsett vad du lägger efter den första asterisken upprepas för att fylla utrymmet. Rad 3 använder *! för att upprepa utropstecken.
I B4 och B5 delar varje komma som du sätter efter den sista nollan antalet med 1000. Koden 0, K visar siffror i tusentals, med ett K efteråt. Om du vill visa miljoner använder du två komma. "M" -koden måste placeras i offert, eftersom M redan betyder månader.
I B6 placera ett strängt meddelande i den fjärde zonen för att varna alla som matar in data om att du vill ha ett nummer i cellen. Om de av misstag skriver in text visas meddelandet.
I B7 till B9 skrivs de normala zonerna Positiva, Negativa och Noll över av villkor som du sätter inom hakparenteser. Siffror under 70 år är röda. Siffror över 90 är blå. Allt annat är svart.
I B10 talar de udda _ (symbolerna i bokföringsformatet för Excel att lämna så mycket utrymme som en vänster parentes skulle ta. Det visar sig att en understrykning följt av vilken karaktär som helst kommer att lämna lika mycket utrymme som den karaktären. I B10, koden innehåller 4 nollor. Men det finns olika mängder utrymme mellan var och en. Utrymmet mellan 1 och 2 är bredden på 2 WW-tecken. Utrymmet mellan 2 och 3 är bredden på ett N. Mellanrummet mellan 3 och 4 är bredden på små bokstäver i.
Följande bild visar olika datumformateringskoder.
Observera att mmmmm-formatet i rad 8 är användbart för att producera JFMAMJJASOND-diagrametiketter.
Tack till Dave Baylis, Brad Edgar, Mike Girvin och @best_excel för att de föreslog den här funktionen.
Kolla på video
- Nummerformatering är en fasad: Excel lagrar en sak, visar oss en annan.
- Till exempel: använd decimaler för att visa en rundad version av numret.
- Excel lagrar fortfarande alla decimaler, men förenklar visningen.
- Det finns 11 nummerformat i rullgardinsmenyn på hemfliken.
- Klicka på dialogrutan för att komma till Formatera dialogrutan.
- Idag pratar vi om formateringskoderna i gruppen Anpassad.
- Jag har inte all kunskap här idag, så om du har bättre tips, lägg dem i YouTube-kommentarerna.
- En noll i formatkoden säger att Excel måste visa siffran.
- Ett # -tecken säger att de kan visa siffran om det finns tillräckligt med precision, men de behöver inte när det inte finns.
- A? lämnar utrymme för siffran för att hålla decimaltalet uppradat.
- Det kan finnas 1, 2, 3 eller 4 format, åtskilda av en semikolon.
- När det finns två format är det första för noll och uppåt. Den andra är för negativ.
- När det finns 3 format, det tredje om för noll.
- 4: e formatet är för text.
- Använda nummerformatet för att visa text med nummer eller för att visa text istället för nummer.
- Ange en färg för en zon.
- För att ändra (Färg 5) besök Excel-alternativ.
- * x för att upprepa x upp till siffrorna
- , i slutet för att dela med 1000
- ,, delar med miljoner
- Använda förhållanden i zonen
- _x lämnar utrymme i storleken "x"
- m mm mmm mmmm mmmmm
- d dd ddd dddd
- (h): mm
- Tack till Dave Baylis, Brad Edgar, Mike Girvin och @best_excel för att de föreslog den här funktionen.
Videoutskrift
Lär dig Excel från podcast, avsnitt 2044 - Formatera som fasad!
Okej, jag podcastar alla mina tips i den här boken, klicka på "i" i det övre högra hörnet för att komma till spellistan!
Vi pratar om anpassad nummerformatering idag, så jag har antal, intäkter, och vi visar här en hel massa decimaler. Och du vet, om vi inte vill spela alla dessa decimaler och vi inte vill använda funktionen RUND, kommer jag bara upp hit och använder Minska decimal. det ändrar inte numret som faktiskt lagras bakom kulisserna, det visar bara hur Excel visar numret. Okej, och så stjäl jag Mike Girvins term, att vi använder formatering som en fasad, eller hur, det finns en hel massa decimaler lagrade i den cellen, men vi använder anpassade nummerformat för att visa det. Och jag vill gå längre än de 11 formaten här och till och med klicka på den dialogrampan, bortom de flesta saker som finns här, och jag vill prata om anpassad.Anpassade nummerformat ger oss världen, okej?
Och så här är en hel massa anpassade nummerformat som du kan skriva i den här rutan, och resultaten visas till vänster. Så bara Allmänt, du får alla decimaler, men här tvingar nollor före decimaler nollor att visas. Så om siffran <100 kommer jag att få en ledande 0, ibland är det användbart för artikelnummer, nollor efter decimalerna tvingar siffrorna att visas. Hashtaggen eller pundtecknet (#) före decimalen är valfritt, okej, så det kan vara där, det behöver inte vara där. Okej, så i det här fallet har vi siffran 6, men eftersom vi har fem nollor ger det oss ledande nollor. Nu, om du behöver SÖKA i en textavsnitt som har 00006, kommer detta inte att matcha, men om du bara behöver se rätt ut, fungerar det. OK, så några intressanta saker här,vi använder pundskyltar eller hashtags mycket, “hashsigns” mycket. I USA kallar vi detta ett pundtecken, och jag förstår i England att det inte är ett pundtecken, £ är din valuta som du kallar det en hash. Okej så, förlåt, jag har kallat det ett pundskylt hela mitt liv, och det är svårt för mig att börja ringa en hash, men jag kommer att arbeta med det.
Okej, så här nere säger .000 att du kommer att visa nollorna oavsett om det finns precision eller inte, och jag har ändrat dessa siffror till 6.42 och 6.4. Nu är det intressant, det här är en som jag aldrig riktigt visste, om du lägger en? istället för 0, står det att den kommer att visa precisionen om den är där, men om inte, visa den inte, men låt inte saker flöda till höger. Okej så här, låt oss ändra dessa siffror, 6.42 och 6.4, okej, se här, decimalerna är inte uppradade. Om du vill att decimalerna ska vara i linje, använd frågetecknen. Om du vill tvinga nollorna att vara där, använd nollorna.
Nu hej, när vi går igenom här finns det MYCKET detaljer i minutia, men jag har inte hörnet på kunskapen här. Om du ser mig sakna ett coolt nummerformateringstrick, vänligen lämna det i YouTube-kommentarerna. Okej nu, här är nästa koncept, vi kan visa 1, 2, 3 eller 4 nummerformat i rutan Typ. Om du bara visar 1 är det bara numret som används för allt, okej. Men om du visar 2 och de är åtskilda av ett semikolon, är den första för siffror> = 0 och den andra för siffrorna <0. Så här har jag använt två koder, +0; -0, och det betyder att de negativa siffrorna dyker upp med ett minus, men de positiva siffrorna dyker upp med plus, men för att jag inte specificerade vad som händer för 0 , det dyker upp med ett plus också.
Så vi kan lägga till en tredje zon: positiv, negativ, noll, okej, så +0; -0; 0, okej, så att hanterar nollor. Det kan till och med finnas en fjärde kod för vad man ska göra om någon skriver in text. Okej nu, nästa koncept, i det anpassade nummerformatet, förutom nollor, kan du sätta text i citattecken! Okej, så låt oss ta en titt på det anpassade nummerformatet här, för positiva siffror, orden "Please Remit" och sedan siffran, för negativ "Credit Balance of" the number and "(Do not Pay)". Och för 0 visar jag inte ens numret, jag visar bara "No Balance", okej. Så du kan faktiskt använda detta för att ersätta numret, det här är -1, 0 och 1, och det här är formler, för att få formeln är bara att kopiera ner det. Alla negativa siffror säger "Förlorare!" eller "Down" eller ordet "Minus" och siffran, så vi lägger in -5, säger fortfarande "Loser!" eller “Ner” eller “Minus 5”.För 0 kan vi säga "noll" eller säga "platt" eller säga "0", och sedan för ett positivt tal, låt oss sätta det i 55, "vinnare!", "Upp" eller "positiv 55", okej, så att du faktiskt kan lägga till ord. Och vad är häftigt, så det här är 55, 55, 55, om jag skulle SUMMA det, de är verkligen siffror, och det kommer verkligen att fungera, som normalt skulle SUM inte fungera med text, men det ger mig faktiskt ett svar . Och så, om du vill behålla siffror där, men visa ord i cellerna, är det här ett bra trick.t arbeta med text, men det ger mig faktiskt ett svar. Och så, om du vill behålla siffror där, men visa ord i cellerna, är det här ett bra trick.t arbeta med text, men det ger mig faktiskt ett svar. Och så, om du vill behålla siffror där, men visa ord i cellerna, är det här ett bra trick.
Okej nu, det här är tecknen som kan användas i det anpassade nummerformatet utan citat, så om du råkar behöva ett + eller ett - behöver du inte sätta det i citat. Och det finns några tillägg till detta, som när jag såg den här listan, är jag som "OK, låt oss se vad vi kan skruva med." Alla dessa är siffrorna 1 2 3 4, så om jag använde ett anpassat nummerformat på ((nummer> & kommer allt detta att visas. Om jag vill separera siffrorna med bindestreck är detta ett anpassat nummerformat för att få det fungerar. Och dessa är alla riktiga, så 7854, se allt förändras, det här är alla formler här nere. Så jag kan snälla skruva med det anpassade nummerformatet och sätta tecken före det, sätta tecken efter det. inte i listan som jag vet att det fungerar är K, du får sätta ett K i slutet, och det 's inte i citat. Men M, eftersom M betyder månader eller minuter, måste du sätta det i citat eller sätta detta M för att få ett M att visas i slutet.
Okej, nästa koncept, i varje zon får du ange färg, höger, så här är de positiva siffrorna gröna och de negativa siffrorna är blå. Ctrl + 1 för att komma till Formatera celler, före det anpassade nummerformatet, i den första zonen, den positiva zonen, sa jag grönt och för negativa tal sa jag blått. Vilken färg får vi använda där? Återgår till dagarna med åtta färger, så svart, grön, vit, blå, magenta, gul, cyan och röd. Men tack vare Mike Alexander får du använda 56 andra färger, Color 1, Color 2, måste vara i (), så är det 56 färger. Om du behöver ändra dessa blir vi riktigt djupa nu, nu går vi in i Charlie kid territorium! Excel-alternativ, Spara och klicka sedan på Färger, okej, och liknande, om jag ville ändra den orange till något annat, kanske den lila, klicka på OK, klicka på OK,klicka OK, och en av dessa, jag kan inte säga vilken det är skulle ha ändrats till den färgen. Ett annat coolt trick, vad händer om du vill dölja alla siffror? Så du har några hemligheter här och du vill inte att någon ska se dem. Ctrl + 1, sätt ett anpassat nummerformat, jag säger "Visa inget för positivt, inget för negativt, inget för 0" klicka på OK och allt är dolt. Naturligtvis kan de fortfarande se det här uppe i formelfältet, så det är inte perfekt, okej, här är några andra kundformateringstecken.inget för negativt, inget för 0 ”klicka OK, och allt är dolt. Naturligtvis kan de fortfarande se det här uppe i formelfältet, så det är inte perfekt, okej, här är några andra kundformateringstecken.inget för negativt, inget för 0 ”klicka OK, och allt är dolt. Naturligtvis kan de fortfarande se det här uppe i formelfältet, så det är inte perfekt, okej, här är några andra kundformateringstecken.
En asterisk säger "Visa nästa karaktär som en check-writer". Så de ** säger "Du vet, fyll området upp till siffran med asterisker", men du kan ändra det, du kan göra den karaktären vad du vill! Så *! kommer att placera alla utropstecken där, låt oss ändra detta, Ctrl + 1, kommer att ändra det till * X, och jag kommer att få alla X fram till den punkten. Så du kan skapa check-writer-effekten, men det behöver inte vara med en asterisk, du kan placera vad du vill där. Sätt ett komma i slutet, så det här är 123456, anpassat nummerformat med ett komma efter nollan säger "Visa detta i tusentals, dividerat med 1000", jag lägger, K där i tusentals. När du vill göra det i miljoner, så finns det vår 0,0 ,, varje komma säger "Display, men dela det med ytterligare 1000". Så det dividerar med en miljon,sätt "M" för att visa miljoner. Jag vet att vissa använder MM där, min syster Alice (?) Som arbetar för Procter & Gamble, "MM" måste vara i offert.
Nu här, det här är en där vi tvingar ett nummer, om någon skriver in något inte är ett nummer kommer meddelandet i den 4: e zonen att skriva över det. Så om vi lägger in siffran 1 är det bra, sätter in -1, det är jättebra, om vi lägger in "Hej", ropar han till oss, "Ange ett nummer!". Okej, igen, det här är formler som drar vad jag skriver in i A till B. Du kan använda villkor, okej, så det positiva, negativa, noll, du kan åsidosätta det. Så jag ska säga rött om det är 90, och du kan bara ha två villkor, och sedan en tredje zon för saker som inte är 90, gammal, gammaldags villkorlig formatering. En annan, här är en annan, det här är bara galet, sätter en understrykning och sedan lämnar varje karaktär tillräckligt med utrymme för att passa den karaktären.Du ser det här mycket med _ (för att hålla de positiva siffrorna i linje med de negativa siffrorna, men du kan verkligen skruva med Excel. Så W är det bredaste tecknet, så det här sätter den första siffran, och sedan tillräckligt med utrymme för 2 W och sedan den andra siffran och tillräckligt med utrymme för en N, och sedan den tredje siffran och tillräckligt med utrymme för ett jag, och slutligen, den fjärde siffran. Är det användbart för någonting? att du kan tvinga olika avstånd mellan siffrorna med hjälp av understrykningen.men det är ganska vildt att du kan tvinga olika avstånd mellan siffrorna med hjälp av understrykningen.men det är ganska vildt att du kan tvinga olika avstånd mellan siffrorna med hjälp av understrykningen.
Okej, datum, m / d / åå, du får ett tvåsiffrigt år, expandera till åååå, du får ett fyrsiffrigt år, sätt mm, det tvingar en ledande 0 före 7, dd tvingar en ledande 0 före 3 Om du vill använda något gammaldags koboltdatformat, lägg inte några / bara alla tecken, ÅÅÅÅMMDD. mmm stavar månadens förkortning, mmmm stavar hela månaden, mmmmm ger dig JASON-formatet. Varför kallar jag det JASON-formatet? Om du tittar på Wall Street Journal har deras finansiella shorts alltid JFMAMJJASOND. Tillbaka 1984 vill jag ha en vinylkopia av "Frankie & the Knockouts - Below the Belt" för att svara på triviafrågan "Vilket mans namn kan stavas genom att använda månadens förkortningar?" Och eftersom jag läste The Wall Street Journal i skolan visste jag att Jason var namnet och det vann jag.Jag gav förmodligen bort det i en garageförsäljning för $ 50c, min dåliga. Okej, dd ger dig den ledande siffran före datumet, ddd stavar förkortningen, dddd ger dig hela dagens namn, veckodagsnamn, ddddd gör ingenting. Du kan kombinera dessa hur du vill. dddd stavar dagen, tisdag, sätt ordet "the" i citat, själva dagen för mmm, ger dig förkortningen, okej, så du kan kombinera dessa hur du vill.
Tid, en konstig sak med tiden så, h: mm eller AM / PM, så om du inte sätter AM / PM tvingas den till 24-timmars tid, om du gör det, får du 14:15. Om du har hh, tvingar den den ledande nollan före 2, du kan till och med lägga till .0 efter sekunder för att få millisekunder. Nu är det bisarra, faktiskt innan jag kommer till det bisarra, jag vet inte, låt oss göra det bisarra först. Låt oss göra = SUMMA av alla dessa, så alla dessa är 14 timmar, 6 * 14 = 84, det borde vara 84 timmar, men om jag formaterar det som en tid, säger det 1:30 på eftermiddagen, okej, och så här är det. Så vi har ett nummer här, den här morgonen 2016-10-09 klockan 6, och jag har bara formler som kopierar ner det, och jag kan åsidosätta, kom ihåg, det formateras som en fasad. Jag kan säga ”Hej, visa mig bara åren, visa mig bara månaden, visa mig bara dagarna,bara visa mig tidsdelen av det. ” Och här i det här SUMMET, när vi ber om en tid, säger vi "Hej, ignorera åren, månaderna, dagarna, visa mig bara tiden." och vad detta egentligen är, det här är tre dagar + 1:30 på eftermiddagen.
Så för att detta ska visas korrekt kommer du antingen in i Time och letar efter den som säger 37 timmar, eller går till Custom, så ser du att sätta (h) säger "Visa oss hela antalet timmar . ” Så om jag behöver se timmar och minuter, (h): mm kommer att visa mig att det är faktiskt 85,5 timmar, jag förstår, jag skrev av de 15 minuter och försökte få 84 i mitt huvud, jag var nära. Var annars kan du använda hakparenteser? Så 06:00 kan du visa timmarna, du kan bara visa minuter, 06:00 är 360 minuter, eller bara sekunder med fyrkantiga parenteser. Okej så, låt oss gå tillbaka till ett av de nummerformat vi har under redovisningsformatet och se om vi nu, efter det här riktigt långa avsnittet, förstår vad som händer.
Så vad detta säger, _ (säger "Lämna så mycket utrymme som a (i det positiva värdet." "*" Säger "Upprepa mellanslag tills du kommer till siffrorna." #, ## 0 säger "Vi ska att använda kommaseparatorn, sätt inte ledande tecken om vi inte har precision, sätt alltid två decimaler. ”Och sedan säger _)” Lämna så mycket utrymme som), och det är för de positiva siffrorna. ” Okej nu, för de negativa siffrorna är det verkligen bisarrt här ute, innan de lämnar så mycket utrymme som a (så med andra ord kommer de aldrig att visa det. Återigen "*", men här ' ska nu lägga in (och). Och sedan för 0, lämna lika mycket utrymme som (och lika mycket utrymme som). Lägg inte något, vi ersätter noll med "-", men lämna sedan så mycket utrymme som två decimaler, så att det 'kommer att stämma överens med detta. Och slutligen i fjärde zonen, om de skriver in text, lämna tillräckligt med utrymme för (och a) och lägg sedan in texten.
Okej, det här var alltid bara en hel del nonsens än jag, men det finns specifika betydelser för alla dessa karaktärer. Det är galet att all denna information, 18 minuters podcast, kommer från tre sidor i den här boken, tänk bara hur mycket det finns på alla 200 sidor i boken. Köp boken, klicka på "i" i det övre högra hörnet, du kommer att uppskatta den och jag kommer att uppskatta den. Okej, lång, lång episod: Nummerformatering är en fasad, Excel lagrar en sak, den visar oss en annan. Minska decimal och öka decimal är ett bra exempel på det, de lagrar fortfarande alla decimaler, men det förenklar visningen. Det finns 11 format här, men verkligen för att komma till dem går du till dialogrullen, och sedan kan du välja något och sedan ändra det i Anpassad om du vill. Pratade om alla dessa anpassade nummerformateringskoder,om du har bättre tips, lägg dem i YouTube-kommentarerna.
Noll i formatkoden säger "Excel MÅSTE visa siffran, oavsett om det finns precision eller inte." Hash-tecken, pundskylt säger att de kan visa siffran om det finns en precision, men de behöver inte. Frågetecknet säger "Lämna utrymmet för siffran om det inte finns tillräckligt med precision." Det kan finnas upp till fyra format åtskilda av ett semikolon, på det sättet har vi ett annat tal för positivt, negativt, noll och text. 2 format, det första är för icke-negativt, det andra är för negativt, om du hade ett tredje format är det tredje formatet för 0, det fjärde formatet är för text, om det finns. Okej, vi kan visa text med ett nummer eller visa text istället för siffran, "Winner", "Loser" eller "Up", "Flat", "Down". Du kan ange en färg för varje zon, för att ändra färgkoderna besök Excel-alternativ, Spara, Färger.Stjärna vilken karaktär som helst för att upprepa den karaktären före siffrorna, som en check-writer skulle göra. Sätt komma i slutet för att dela med tusen, två komma för att dela med miljoner, 3 komma dividera med miljarder och så vidare. Vi kan använda förhållanden i zonen, och då kommer X att lämna ett utrymme på storleken X. Okej, de olika variationerna av månader, oavsett om du lägger en siffra, två siffror, förkortningen, stavar den eller bara den första initial . Dagar, en siffra, två siffror, förkorta eller stava det, det finns ingen femte. Och slutligen, för att få tider som överstiger 24 timmar, måste du sätta det h i hakparenteser och andra variationer av det.Vi kan använda förhållanden i zonen, och då kommer X att lämna ett utrymme på storleken X. Okej, de olika variationerna av månader, oavsett om du lägger en siffra, två siffror, förkortningen, stavar den eller bara den första initial . Dagar, en siffra, två siffror, förkorta eller stava det, det finns ingen femte. Och slutligen, för att få tider som överstiger 24 timmar, måste du sätta det h i hakparenteser och andra variationer av det.Vi kan använda förhållanden i zonen, och då kommer X att lämna ett utrymme på storleken X. Okej, de olika variationerna av månader, oavsett om du lägger en siffra, två siffror, förkortningen, stavar den eller bara den första initial . Dagar, en siffra, två siffror, förkorta eller stava det, det finns ingen femte. Och slutligen, för att få tider som överstiger 24 timmar, måste du sätta det h i hakparenteser och andra variationer av det.
Tack för att du hänger till slutet! Vi ses nästa gång för en ny netcast från!
Nedladdning fil
Ladda ner exempelfilen här: Podcast2044.xlsm
