Kaavat numeroiden pyöristämiseen Excelissä

Asiakkaasi haluavat usein katsoa puhtaita, pyöreitä lukuja. Käyttäjän hukuttaminen desimaaliarvoilla ja tarpeettomilla numeroilla tarkkuuden vuoksi voi itse asiassa tehdä raporteistasi vaikeampaa lukea. Tästä syystä sinun kannattaa harkita Excelin pyöristysfunktioiden käyttöä.

Tämä kuva havainnollistaa, kuinka ROUND-, ROUNDUP- ja ROUNDDOWN-toimintojen käyttö vaikuttaa numeroon 9.45.

Excelin ROUND-funktiota käytetään pyöristämään tietty numero tiettyyn määrään numeroita. ROUND-funktiossa on kaksi argumenttia: alkuperäinen arvo ja pyöristettävien numeroiden lukumäärä.

Nollan syöttäminen toiseksi argumentiksi kehottaa Exceliä poistamaan kaikki desimaalit ja pyöristämään luvun kokonaislukuosan ensimmäisen desimaalin perusteella. Esimerkiksi tämä kaava pyöristyy 94:ään:

=PYÖRISTÄ(94.45;0)

Kun syötät 1:n toiseksi argumentiksi, Excel pyöristää yhteen desimaaliin toisen desimaalin arvon perusteella. Esimerkiksi tämä kaava pyöristyy arvoon 94,5:

=KIERROS(94.45;1)

Voit myös kirjoittaa negatiivisen luvun toiseksi argumentiksi, jolloin Excel pyöristetään desimaalipilkun vasemmalla puolella olevien arvojen perusteella. Esimerkiksi seuraava kaava palauttaa 90:

=KIERROS(94.45;-1)

Voit pakottaa pyöristyksen tiettyyn suuntaan käyttämällä ROUNDUP- tai ROUNDDOWN-toimintoja.

Tämä ROUNDDOWN-kaava pyöristää 94,45 94:ään:

=PYÖRISTÄALAS(94.45;0)

Tämä ROUNDUP-kaava pyöristää 94,45:stä 95:een:

=PYÖRISTÄ(94.45;0)

Pyöristys lähimpään penniin Excelissä

Joillakin toimialoilla on yleinen käytäntö pyöristää dollarimäärä lähimpään penniin. Kuva osoittaa, kuinka dollarin pyöristäminen ylös- tai alaspäin lähimpään penniin voi vaikuttaa tulokseen.

Voit pyöristää lähimpään penniin käyttämällä CEILING- tai FLOOR-toimintoja.

KATTO-funktio pyöristää luvun ylöspäin lähimpään sille antamasi merkityksen kerrannaiseen. Tämä apuohjelma on kätevä, kun sinun on ohitettava tavallinen pyöristysprotokolla omilla liiketoimintasäännöilläsi. Voit esimerkiksi pakottaa Excelin pyöristämään 123,222:sta 124:ään käyttämällä CEILING-funktiota, jonka merkitys on 1.

=KATTO(123.222;1)

Joten 0,01:n syöttäminen merkitykseksi käskee CEILING-funktion pyöristämään lähimpään penniin.

Jos haluat pyöristää lähimpään nikkeliin, voit käyttää 0,05:tä. Esimerkiksi seuraava kaava palauttaa arvon 123.15:

=KATTO(123.11;.05)

FLOOR-funktio toimii samalla tavalla paitsi, että se pakottaa pyöristyksen alaspäin lähimpään merkitykseen. Seuraava esimerkkifunktio pyöristää 123,19:n alaspäin lähimpään nikkeliin, jolloin tuloksena on 123,15:

=KERROS(123.19;.05)

Pyöristys merkitseviin numeroihin Excelissä

Joissakin talousraporteissa luvut on esitetty merkitsevin numeroin. Ajatuksena on, että kun käsittelet miljoonien lukuja, sinun ei tarvitse täyttää raporttia ylimääräisillä numeroilla näyttääksesi tarkkuuden kymmeniin, satoihin ja tuhansiin paikkoihin asti.

Esimerkiksi numeron 883 788 näyttämisen sijaan voit pyöristää luvun yhteen merkitsevään numeroon. Tämä tarkoittaisi saman numeron näyttämistä kuin 900 000. Pyöristämällä 883 788 kahteen merkitsevään numeroon, luku olisi 880 000.

Pohjimmiltaan olet sitä mieltä, että tietyn numeron paikka on tarpeeksi merkittävä näytettäväksi. Loput luvusta voidaan korvata nollilla. Saatat tuntea, että tämän tekeminen voi aiheuttaa ongelmia, mutta kun käsittelet tarpeeksi suuria lukuja, kaikki tietyn merkityksen alapuolella olevat luvut ovat merkityksettömiä.

Seuraava kuva osoittaa, kuinka voit toteuttaa kaavan, joka pyöristää luvut tiettyyn määrään merkitseviä numeroita.

Käytät Excelin ROUND-funktiota pyöristääksesi tietyn luvun tiettyyn määrään numeroita. ROUND-funktiossa on kaksi argumenttia: alkuperäinen arvo ja pyöristettävien numeroiden lukumäärä.

Negatiivisen luvun syöttäminen toiseksi argumentiksi kehottaa Exceliä pyöristämään merkitsevien numeroiden perusteella desimaalipilkun vasemmalla puolella. Esimerkiksi seuraava kaava palauttaa 9500:

=PYÖRISTÄ(9489;-2)

Merkitsevien numeroiden argumentin muuttaminen arvoon -3 palauttaa arvon 9000.

=PYÖRISTÄ(B14,-3)

Tämä toimii hyvin, mutta entä jos numerot ovat eri mittakaavassa? Eli entä jos jotkut luvuistanne ovat miljoonia, kun taas toiset ovat satoja tuhansia? Jos haluat näyttää ne kaikki yhdellä merkitsevällä numerolla, sinun on rakennettava erilainen ROUND-funktio jokaiselle numerolle, jotta voidaan ottaa huomioon eri merkitsevien numeroiden argumentti, jota tarvitset kullekin numerotyypille.

Voit ratkaista tämän ongelman korvaamalla kovakoodatun merkitsevien numeroiden argumentin kaavalla, joka laskee, mikä numeron pitäisi olla.

Kuvittele, että numerosi on -2330.45. Voit käyttää tätä kaavaa merkitsevien numeroiden argumenttina ROUND-funktiossasi:

PITO(INT(ABS(-2330.45)))*-1+2

Tämä kaava kääri ensin numerosi ABS-funktioon ja poistaa tehokkaasti mahdolliset negatiiviset symbolit. Sitten se rivittää tuloksena INT-funktion poistaen mahdolliset desimaalit. Sitten se rivittää, mikä johtaa LEN-funktioon saadakseen mittarin siitä, kuinka monta merkkiä numerossa on ilman desimaalilukuja tai negatiivisia symboleja.

Tässä esimerkissä tämä kaavan osa johtaa numeroon 4. Jos otat luvun –2330.45 ja poistat desimaalit ja negatiivisen symbolin, jäljellä on neljä merkkiä.

Tämä luku kerrotaan sitten -1:llä, jotta siitä tulee negatiivinen luku, ja lisätään sitten etsimiesi merkitsevien numeroiden määrään. Tässä esimerkissä laskutoimitus näyttää tältä: 4 * –1 + 2 = –2.

Jälleen tätä kaavaa käytetään ROUND-funktion toisena argumenttina. Syötä tämä kaava Exceliin ja pyöristä luku 2300:aan (2 merkitsevää numeroa):

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

Voit sitten korvata tämän kaavan soluviittauksilla, jotka osoittavat lähdenumeroon ja soluun, joka sisältää haluttujen merkitsevien numeroiden määrän.

=PYÖRÄS(B5,PITKÄ(INT(ABS(B5)))*-1+$E$3)