Hur man justerar det maximala antalet decimaler i Excel

Om du någonsin sett en kolumn full av ######### istället för siffror Om du någonsin har stött på belopp med fler decimaler än vad som är logiskt i Excel, är du inte ensam. Excel hanterar siffror mycket bra, men utan tydliga regler kan det visa mer precision än nödvändigt eller, omvänt, dölja en del av den verkliga informationen utan att vi inser det.

Styra hur många decimaler som visas och hur många som faktiskt används i interna Excel-beräkningar Detta är nyckeln till att undvika överraskningar i rapporter, fakturor, budgetar eller ekonomiska beräkningar. Genom den här artikeln kommer vi att se, i detalj och utan att utelämna något, hur man justerar det maximala antalet decimaler, hur man avrundar eller avkortar värden, hur man tvingar fram ett fast format och vilka begränsningar Excel har med stora tal.

Justera antalet synliga decimaler manuellt

Ofta är allt vi behöver Excel visa färre decimaler på skärmen, så att siffrorna inte stör, de visas inte symboler sällsynta eller om det finns en absurd precision för sammanhanget (till exempel priser med fem eller sex decimaler).

När du ser något liknande i en cell ######### Istället för själva talet är problemet oftast att värdet har många siffror (hela eller decimala) och kolumnen är för smal för att visa det i det aktuella formatet. Ett snabbt sätt att minska denna effekt är minska antalet synliga decimaler eller bredda ryggraden, vilket som passar dig bäst.

Excel erbjuder flera enkla metoder för att justera decimaler med ögat, utan att röra formler eller funktioner, bara genom att experimentera med mobilnummerformatDetta är idealiskt när du inte vill ändra det faktiska värdet, utan bara dess utseende.

Använd decimalknapparna för att öka/minska

Den mest direkta metoden innebär att man använder ikonerna på fliken Hem > NummerI menyfliksområdet ser du två knappar med en ikon med nollor och en pil: en för Öka decimaler och en annan för Minska decimalerDe är det snabbaste sättet att justera det du ser utan att något går sönder.

Du behöver bara markera cellerna Var du än vill ändra formatet, gå till Hem > Nummer och tryck på motsvarande knapp flera gånger. Varje klick lägger till eller tar bort en siffra efter kommatecknet (eller decimaltecknet), utan att röra det faktiska värdet som sparats av Excel.

Konfigurera antalet decimaler från "Formatera celler"

Om du vill ha något lite mer förfinat kan du ändra formatet med hjälp av den klassiska rutan CellformatHär väljer du taltyp (valuta, redovisning, procent, vetenskaplig etc.) och markerar exakt hur många decimaler du vill se.

För att göra detta, gå till Hem > Nummer, klicka på den lilla pilen i hörnet av gruppen (eller tryck Ctrl+1) och i listan KategoriVälj det format som bäst passar dina data: Valuta, Redovisning, Procent eller Vetenskapligt, bland annat. I samma fönster ser du en ruta som heter Decimaler, där du kan ange hur många decimaler som ska visas.

Med det här alternativet säkerställer du att alla nummer av samma typ följer en homogent kriteriumTill exempel alltid två decimaler för belopp i euro, eller tre decimaler för produktmängder eller tekniska mått.

Avrunda ett tal med hjälp av Excel-funktioner

Det är en helt annan sak när det inte räcker att bara ändra det visuella formatet, utan vi vill att värdet ska vara avrunda uppåt korrekt i beräkningenI så fall kommer Excels avrundningsfunktioner in i bilden, vilka ändrar talet och inte bara dess utseende.

En av de mest använda funktionerna är RUNDA AVDen här funktionen justerar ett tal till det antal siffror du definierar, både till höger och vänster om decimaltecknet. Den är mycket användbar när du behöver matematisk konsistens i summor, medelvärden eller beräknade priser.

Hur ROUND-funktionen fungerar

Funktionen RUNDA AV Den har två argument: talet du vill avrunda och önskat antal decimaler. Det talet kan vara en cellreferens (som A1) eller ett värde som skrivs direkt i formeln.

Syntaxmässigt skulle du skriva något i stil med =AVRUNDA(tal; siffror)Det första argumentet är startvärdet; det andra styr hur många siffror som behålls och om avrundning görs till höger eller vänster om decimaltecknet, beroende på om det är positivt, noll eller negativt.

Tänk dig att du i cell A1 har värdet 823,7825Därifrån kan du avrunda det till närmaste tusen, hundra, tio eller ett visst antal decimaler:

• För att avrunda till närmaste tusental skulle du använda =AVRUNDA(A1;-3)I det här fallet skulle resultatet bli 1 000, eftersom 823,7825 är närmare 1 000 än 0, och 0 och 1 000 är multiplar av tusen.
• Om du vill avrunda uppåt till hundra, skulle du skriva =AVRUNDA(A1;-2), vilket skulle ge 800. Vid det här laget är 823,7825 närmare 800 än 900.
• Med en avrundning till tiondelar, skulle du använda =AVRUNDA(A1;1), med resultatet 823,8, eftersom du bara lämnar en decimal.

Negativa argument används för att avrunda till vänster om decimaltecknet (tusental, hundratal, tiotal), medan positiva argument justerar hur många decimaler bevaras till höger om separatorn. Det är ett mycket mångsidigt verktyg.

Avrunda alltid uppåt: AVRUNDA UPPÅT

Funktionen AVRUNDA UPPÅT. MER Den fungerar väldigt likt ROUND, men med en viktig skillnad: den avrundar alltid talet uppåt, oavsett om gränsvärdet för decimalen är större eller mindre än 5.

Om du till exempel har 3,2 och vill avrunda uppåt till noll decimaler, =AVRUNDA UPPÅT(3;2;0) Jag skulle ge dig 4 tillbaka. Denna logik är användbar när du behöver vara alltför konservativ, till exempel i vissa finansiella eller kommersiella tillämpningar.

Avrunda alltid nedåt: AVRUNDA NER

Omvänt, funktionen AVRUNDA NER Det tvingar Excel att alltid avrunda nedåt, det vill säga för att komma närmare noll i positiva tal eller för att bli mer negativ i negativa tal, beroende på vilka decimaler du anger.

Om du vill avrunda talet 3,14159 till tre decimaler utan att låta det gå upp till 3,142, skulle du använda =AVRUNDA NER(3,14159,3)Resultatet skulle bli 3,141, eftersom eventuella rester efter den tredje decimalen ignoreras och avrundas nedåt.

Avrunda decimaler utan avrundning med TRUNCAR

När vi justerar cellformatet till ett fast antal decimaler, tillämpar Excel det som kallas symmetrisk avrundningOm den första decimalen som ska tas bort är fem eller högre, lägg till ett till den sista decimalen som behålls. Det är precis vad som händer när vi avrundar 9,676 till två decimaler och får 9,68.

Problemet uppstår när vi inte vill att avrundningen ska ske, utan snarare vill trimma bort överflödiga decimaler Och det är allt, utan att Excel ändrar värdet. I dessa fall räcker det inte att enbart förlita sig på cellformatering, eftersom det "verkliga" värdet fortfarande har alla decimaler, även om de inte är synliga.

Det är här funktionen kommer in i bilden STYMPADen eliminerar decimaler utan avrundning. I grund och botten avrundas allt som kommer efteråt, utan att tillämpa logiken "om det är fem eller fler ökar jag med ett".

Om du till exempel har en serie värden i intervallet A2: A10 och du beräknar medelvärdet med =MEDEL(A2:A10)Du kan begränsa medelvärdet till två decimaler utan symmetrisk avrundning med =AVKORTA(MEDEL(A2:A10);2)Här är det första argumentet starttalet (resultatet av MEDELSNITT) och det andra är antalet decimaler du vill behålla.

Den extra fördelen är att när man använder TRUNC är värdet som lagras i cellen redan detsamma. nummeravgränsninginte ett längre tal förklätt med ett tvådecimalformat. Detta undviker konstiga avvikelser när du börjar addera eller beräkna medelvärdet av avkortade resultat.

Varför formatet kan ändra de synliga summorna

När du bara formaterar decimaler behåller talen sitt fulla värde nedan och Excel fortsätter göra beräkningar med alla decimaleräven om du bara ser två. Detta kan leda till visuellt störande resultat.

Tänk på det här exemplet: om du adderar 1,503 + 1,503 blir det faktiska resultatet 3,006. Hittills går det bra. Om du sedan använder ett tvådecimalformat på alla celler ser du 1,50 + 1,50 = 3,01. Tusendelarna som togs bort från varje addera visas inte, men de finns fortfarande kvar och blir signifikanta i slutresultatet.

När man använder funktioner som STYMPA (eller, om lämpligt, AVRUNDA med lämpliga inställningar), säkerställer du att både adderingar och totalen fungerar med samma effektivt antal decimalerförhindra att små skillnader ackumuleras och i slutändan påverkar hundradels procent i resultatet.

Strikt kontroll över decimaler som användaren matar in

En mycket vanlig situation är att man vill begränsa inte bara hur många decimaler som visas, utan Hur många decimaler tas med i beräkningarna? när någon matar in data. Detta är mycket vanligt i prislistor, delade mallar eller interna formulär där användaren fyller i vissa fält.

Enhetspris På rad 3 har du styckpriset för en produkt i kolumn A, kvantiteten i kolumn B och totalpriset i kolumn C. Något i stil med: A3 = "Pris för X", B3 = "Antal X", C3 = "Totalpris för X", med en formel som =SUMMA(A3*1000) i C3.

Anta att du vill att priset på A3 ska ha exakt tre decimaleroch att C3 beräknar det totala beloppet genom att multiplicera med 1 000 enheter. Problemet uppstår när personen som fyller i arket väljer att använda fler decimaler än nödvändigt.

Till exempel:
• Om du anger 0,112 i A3, kommer C3 att visa 112 000 (alla godkända).
• Om du anger 0,1121 i A3, kommer C3 att ändras till 112 100.
• Om du anger 0,11265 i A3 kan formatet visa 0,113, men beräkningen i C3 går till 112,650, eftersom Excel fortsätter att använda hela värdet.

Det många letar efter i det här scenariot är att Excel ska ignorera eventuella extra decimaler efter den tredje, så att A3 anses alltid vara 0,112 i beräkningarna, oavsett om användaren skriver 0,11265 eller 0,1121. Det vill säga, de vill ha en "hård avrundning" av decimaltalen, inte avrundning.

Använd TRUNC för att begränsa antalet decimaler som används i formeln

Ett effektivt sätt att uppnå detta är att tvinga formeln att inte använda A3 direkt, utan AVKRYP(A3,3)På så sätt kommer Excel alltid att använda högst tre decimaler och ignorera resten utan avrundning.

I föregående exempel skulle formeln för C3 kunna bli något i stil med =AVKORTA(A3;3)*1000Således skulle de tre ingångarna ha denna effekt:
• A3 = 0,112 → TRUNCATE(A3;3) = 0,112 → C3 = 112 000.
• A3 = 0,1121 → TRUNCATE(A3;3) = 0,112 → C3 = 112 000.
• A3 = 0,11265 → TRUNCATE(A3;3) = 0,112 → C3 = 112 000.

Detta gör att Excel kan... Släng eventuella extra decimaler, håll det maximala antalet "riktiga" decimaler som används i operationen fast och tillåt inte variationer i totalen baserat på inmatningens precision.

Kombinerat med cellformatering för större tydlighet

Förutom att justera formeln är det klokt att tillämpa ett talformat på A3 med tre decimalerDetta säkerställer att skärmen tydligt återspeglar kalkylbladets precisionspolicy. På så sätt, även om användaren anger fler decimaler, kommer själva cellen bara att visa tre, vilket justerar det som visas med det som beräknas.

Det är dock värt att komma ihåg att själva formatet det minskar inte det verkliga värdetUtan en funktion som TRUNC i formeln skulle dolda decimaler fortfarande påverka resultaten. Helst bör du kombinera båda: en funktion som avkortar talet och formatering som begränsar de synliga decimalerna.

Symmetrisk avrundning och dess effekter på beräkningar

När vi reducerar decimaltal med standardformat tillämpar Excel automatiskt det ovannämnda symmetrisk avrundningDet betyder att om den första siffran som ska tas bort är 5 eller mer, lägg till ett till den sista decimalen som visas; om den är mindre än 5, lämna den som den är.

Detta beteende är vanligt och matematiskt sunt, men i vissa kalkylblad kan det generera resultat som är oönskade ur ett redovisnings- eller kontrollperspektiv. Tänk dig rapporter där du summerar många värden med tre eller fyra "dolda" decimaler och bara visar två: avvikelserna kan ackumuleras till en cent, eller mer, totalt.

Om du till exempel har många värden som 1,505, 1,505, 1,505, och formaterar dem med två decimaler, kommer du alltid att se 1,51, men vid intern summering används 1,505. Om du sedan avrundar totalen kan du se... hundradelar av en skillnad med avseende på vad ögat tror sig se när det mentalt adderar "1,51 + 1,51 + 1,51…".

Genom att använda TRUNC, ROUNDDOWN eller till och med ROUND med lämpliga regler kan du definiera ett konsekvent kriterium för hela beräkningskedjan och förhindra att decimaler som ignoreras i varje steg blir signifikanta senare.

Formatera långa tal och begränsa till 15 signifikanta siffror

Excel har en viktig begränsning: tal får inte vara större än 15 signifikanta siffrorBortom den gränsen börjar Excel avrunda och kan omvandla de sista siffrorna till nollor. Detta är särskilt märkbart med siffror som kreditkortsnummer, långa koder eller identifierare som egentligen inte är "siffror" utan strängar.

För att arbeta med data av den här typen (till exempel 1234567890123456789) utan att förlora information är det viktigt att Excel behandlar dem som textinte som numeriska värden. Annars kommer de sista siffrorna att ändras och matcha inte längre originaldata.

Metod 1: Formatera cellerna som text

En ren lösning innebär formatering Text Ange cellreferensen innan du skriver den långa datan. På så sätt kommer Excel inte att försöka tolka den som ett tal, utan spara och visa det som det är, utan att använda avrundning eller vetenskaplig notation.

För att göra detta kan du högerklicka på cellen och skriva in Cellformat, gå till fliken Antal och välj kategorin TextSedan accepterar du, och du kan ange det långa talet utan att Excel trunkerar eller transformerar det.

En liten grön varningstriangel kan visas, vilket indikerar att värdet visas som ett tal lagrat som text. Om du inte vill se den här varningen kan du öppna menyn genom att klicka på den gula ikonen bredvid och sedan klicka på den. Ignorera felså att det inte ska störa dig igen.

Metod 2: Föregå med ett enkelt citattecken

Ett annat mycket snabbt sätt att tvinga Excel att behandla data som text är att skriva en enskilt citatOm du till exempel skriver '1234567890123456789' visar Excel 1234567890123456789, men internt betraktas det som en textsträng, inte ett tal.

Citattecknet försvinner efter att du tryckt på Enter, men det signalerar till Excel att inga talregler, decimaler eller vetenskaplig notation ska tillämpas. Detta är särskilt användbart när du bara behöver markera det som text. några få isolerade celler utan att ändra formatet för en hel kolumn.

DECIMAL-funktionen och dess särdrag

I vissa versioner och konfigurationer av Excel har du funktionen DECIMAL (i vissa miljöer visas det som FIXED eller med ett motsvarande namn), vars syfte är att avrunda ett tal till ett visst antal decimaler, tillämpa ett format med tusentalsavgränsare och returnera resultatet som text.

Den vanliga syntaxen är ungefär så här: DECIMAL(tal; ; ), med dessa argument:
• Nummer: värdet du vill avrunda och konvertera till text.
• Decimaltal (valfritt): hur många siffror till höger om decimalavgränsaren som behålls.
• Separera inte tusentals (valfritt): ett logiskt värde (SANT/FALSKT) som anger om tusentalsavgränsare ska utelämnas.

Några viktiga observationer Den här funktionen hjälper till att förstå hur den beter sig och hur den skiljer sig från konventionell cellformatering eller andra avrundningsfunktioner.

Funktionsuppträdande och exempel för DECIMAL-funktionen

För det första är siffrorna i Excel begränsade till de 15 signifikanta siffrorParametern "decimaler" för den här funktionen kan dock vara upp till 127. Även om du begär fler än vad som finns, kommer den bara att visa det antal som tillåts av källnumret.

Om "decimaler" är negativt avrundas till vänster om decimaltecknet, precis som med AVRUNDA när negativa siffror används. Om du utelämnar argumentet "decimaler" antar Excel vanligtvis att standardvärde på 2, vilket motsvarar att arbeta med två decimaler.

När argumentet separera inte tusentals Om det utelämnas eller är FALSKT, inkluderar den returnerade texten tusentalsavgränsare enligt de regionala inställningarna (punkter eller kommatecken). Om du ställer in det på SANT försvinner dessa avgränsare och talet lämnas utan tusentalsavgränsare.

Till exempel med följande data:
• A2 = 44 332
• A3 = -1 234 567
• A4 = 44 332
Du kan använda:
• =DECIMAL(A2;1) → avrundar till en decimal: resultat «1 234,6» (som text).
• =DECIMAL(A2;-1) → Avrundar till en decimalplats till vänster om decimaltecknet: "1.230".
• =DECIMAL(A3;-1;SANT) → Avrundar till en position åt vänster, utan tusentalsavgränsare: «-1230».
• =DECIMAL(A4) → utan att ange decimaler, lämna 2 decimaler: «44,33».

Den grundläggande skillnaden med att formatera en cell från Hem > Nummer DECIMAL/FIXED returnerar alltid textinte ett tal. Det betyder att om du senare vill utföra matematiska operationer med det resultatet måste du konvertera det tillbaka till ett tal eller bara använda det som ett presentationsvärde (till exempel för rapporter eller exporter).

I slutändan innebär det att kombinera flera metoder för att bemästra decimalstyrning i Excel: cellformatering för presentationAvrundningsfunktioner (AVRUNDA, AVRUNDA UPPÅT, AVRUNDA NERÅT) när du vill ha konsekvent matematisk precision, AVKORTA för att avrunda utan avrundning, DECIMAL/FAST när du behöver formaterad text och textformatering eller citattecken för långa tal. Genom att använda varje verktyg i sitt sammanhang kan du undvika fel, avvikelser i cent och överraskningar med känsliga data, vilket håller dina kalkylblad tydliga, tillförlitliga och lätta att tolka.