Formule za zaokroževanje števil v Excelu

Vaše stranke pogosto želijo pogledati čiste, okrogle številke. Če uporabnika zasipate z decimalnimi vrednostmi in nepotrebnimi številkami zaradi natančnosti, lahko vaša poročila dejansko otežijo branje. Zaradi tega boste morda želeli razmisliti o uporabi Excelovih funkcij zaokroževanja.

Ta slika ponazarja, kako na število 9,45 vpliva uporaba funkcij ROUND, ROUNDUP in ROUNDOWN.

Excelova funkcija ROUND se uporablja za zaokrožitev določenega števila na določeno število števk. Funkcija ROUND sprejme dva argumenta: prvotno vrednost in število števk, na katere je treba zaokrožiti.

Če kot drugi argument vnesete 0, Excelu poveste, da odstrani vsa decimalna mesta in zaokroži celoštevilski del števila na podlagi prvega decimalnega mesta. Ta formula na primer zaokroži na 94:

=OKROGLO(94,45,0)

Če kot drugi argument vnesete 1, Excelu poveste, naj zaokroži na eno decimalko glede na vrednost drugega decimalnega mesta. Ta formula na primer zaokroži na 94,5:

=ROUND(94,45,1)

Kot drugi argument lahko vnesete tudi negativno število, ki Excelu pove, naj zaokroži na podlagi vrednosti levo od decimalne vejice. Naslednja formula na primer vrne 90:

=ROUND(94,45,-1)

S funkcijama ROUNDUP ali ROUNDDOWN lahko prisilite zaokroževanje v določeno smer.

Ta formula ROUNDDOWN zaokroži 94,45 navzdol na 94:

=OKROŽJE (94,45,0)

Ta formula ROUNDUP zaokroži 94,45 na 95:

=ROUNDUP(94.45,0)

Zaokroževanje na najbližji peni v Excelu

V nekaterih panogah je običajna praksa, da se znesek v dolarju zaokroži na najbližji peni. Slika prikazuje, kako lahko zaokroževanje zneska v dolarju navzgor ali navzdol na najbližji peni vpliva na dobljeno število.

Z uporabo funkcij CEILING ali FLOOR lahko zaokrožite na najbližji peni.

Funkcija CEILING zaokroži število na najbližji večkratnik pomembnosti, ki ji ga podate. Ta pripomoček je uporaben, ko morate preglasiti standardni protokol zaokroževanja z lastnimi poslovnimi pravili. Excel lahko na primer prisilite, da zaokroži 123,222 na 124 z uporabo funkcije CEILING s pomembnostjo 1.

=Strop(123.222,1)

Če torej vnesete 0,01 kot pomen, povejte funkciji CEILING, da zaokroži na najbližji peni.

Če želite zaokrožiti na najbližji nikel, lahko uporabite 0,05 kot pomen. Na primer, naslednja formula vrne 123,15:

=STROP (123.11,.05)

Funkcija FLOOR deluje na enak način, le da prisili zaokroževanje navzdol na najbližji pomen. Naslednji primer funkcije zaokroži 123,19 navzdol na najbližji nikelj, kar daje rezultat 123,15:

=TLA(123,19, 05)

Zaokroževanje na pomembne števke v Excelu

V nekaterih finančnih poročilih so številke predstavljene s pomembnimi številkami. Ideja je, da ko imate opravka s številkami v milijonih, vam ni treba poročila zasuti z odvečnimi številkami, da bi prikazali natančnost do desetin, sto in tisoč mest.

Na primer, namesto da bi prikazali številko 883,788, bi se lahko odločili zaokrožiti številko na eno pomembno mesto. To bi pomenilo prikaz enakega števila kot 900.000. Zaokrožitev 883.788 na dve pomembni števki bi pokazala številko 880.000.

V bistvu menite, da je mesto določene številke dovolj pomembno za prikaz. Preostanek števila je mogoče zamenjati z ničlami. Morda se vam zdi, da bi to lahko povzročilo težave, a ko imate opravka z dovolj velikimi številkami, je vsako število pod določeno pomembnostjo nepomembno.

Naslednja slika prikazuje, kako lahko implementirate formulo, ki zaokroži števila na dano število pomembnih števk.

Za zaokrožitev določenega števila na določeno število števk uporabite Excelovo funkcijo ROUND. Funkcija ROUND sprejme dva argumenta: prvotno vrednost in število števk, na katere je treba zaokrožiti.

Vnos negativnega števila kot drugega argumenta pove Excelu, naj zaokroži na podlagi pomembnih števk levo od decimalne vejice. Naslednja formula na primer vrne 9500:

=ROUND(9489,-2)

Če spremenite argument pomembnih števk na -3, se vrne vrednost 9000.

=ROUND(B14,-3)

To deluje odlično, a kaj, če imate številke na različnih lestvicah? Se pravi, kaj, če so nekatere vaše številke milijone, druge pa stotine tisoč? Če bi jih želeli prikazati vse z 1 pomembno števko, bi morali za vsako število zgraditi drugo funkcijo ROUND, da bi upoštevali argument različnih pomembnih števk, ki bi ga potrebovali za vsako vrsto števila.

Za pomoč pri reševanju te težave lahko zamenjate trdi kodirani argument pomembnih števk s formulo, ki izračuna, kakšna naj bi bila ta številka.

Predstavljajte si, da je vaša številka -2330,45. To formulo lahko uporabite kot argument pomembnih števk v vaši funkciji ROUND:

LEN(INT(ABS(-2330,45)))*-1+2

Ta formula najprej zavije vašo številko v funkcijo ABS in učinkovito odstrani vse negativne simbole, ki morda obstajajo. Ta rezultat nato zavije v funkcijo INT in odstrani morebitne decimale. Nato obloge , da rezultat v funkcijo LEN, da bi dobili merilo, koliko znakov je v številko brez decimalk ali negacija simbolov.

V tem primeru je rezultat tega dela formule število 4. Če vzamete število –2330,45 in odstranite decimalne in negativni simbol, vam ostanejo štirje znaki.

To število se nato pomnoži z -1, da postane negativno število, nato pa se doda številu pomembnih števk, ki jih iščete. V tem primeru je ta izračun videti takole: 4 * –1 + 2 = –2.

Ta formula bo spet uporabljena kot drugi argument za vašo funkcijo ROUND. Vnesite to formulo v Excel in zaokrožite število na 2300 (2 pomembni števki):

=ROUND(-2330,45, LEN(INT(ABS(-2330,45)))*-1+2)

Nato lahko to formulo zamenjate s sklici na celice, ki kažejo na izvorno številko in celico, ki vsebuje število želenih pomembnih števk.

=ROUND(B5,LEN(INT(ABS(B5)))*-1+$E$3)