Vzorce na zaokrúhľovanie čísel v Exceli

Vaši zákazníci sa často chcú pozerať na čisté okrúhle čísla. Zahltenie používateľa desatinnými hodnotami a nepotrebnými číslicami kvôli presnosti môže v skutočnosti sťažiť čítanie vašich správ. Z tohto dôvodu možno budete chcieť zvážiť použitie funkcií Excelu na zaokrúhľovanie.

Tento obrázok znázorňuje, ako je číslo 9,45 ovplyvnené použitím funkcií ROUND, ROUNDUP a ROUNDDOWN.

Funkcia ROUND v Exceli sa používa na zaokrúhlenie daného čísla na zadaný počet číslic. Funkcia ROUND má dva argumenty: pôvodnú hodnotu a počet číslic, na ktoré sa má zaokrúhliť.

Zadanie 0 ako druhého argumentu povie Excelu, aby odstránil všetky desatinné miesta a zaokrúhlil celú časť čísla na základe prvého desatinného miesta. Napríklad tento vzorec sa zaokrúhli na 94:

=ROUND(94,45,0)

Zadanie 1 ako druhého argumentu povie Excelu, aby sa zaokrúhlil na jedno desatinné miesto na základe hodnoty druhého desatinného miesta. Napríklad tento vzorec sa zaokrúhli na 94,5:

=ROUND(94,45;1)

Ako druhý argument môžete zadať aj záporné číslo, ktoré Excelu povie, aby sa zaokrúhlil na základe hodnôt naľavo od desatinnej čiarky. Napríklad nasledujúci vzorec vráti 90:

=ROUND(94,45,-1)

Zaokrúhľovanie v určitom smere môžete vynútiť pomocou funkcií ZAKÚRAŤ NAHOR alebo ZAKÚRAŤ NADOL.

Tento vzorec ROUNDDOWN zaokrúhľuje 94,45 nadol na 94:

=ROUNDDOWN(94,45,0)

Tento vzorec ROUNDUP zaokrúhľuje 94,45 až 95:

=ROUNDUP(94,45,0)

Zaokrúhľovanie na najbližší cent v Exceli

V niektorých odvetviach je bežnou praxou zaokrúhliť sumu v dolároch na najbližší cent. Obrázok ukazuje, ako môže zaokrúhlenie sumy v dolároch nahor alebo nadol na najbližší cent ovplyvniť výsledné číslo.

Pomocou funkcií STROP alebo PODLAHA môžete zaokrúhliť na najbližší cent.

Funkcia STROP zaokrúhľuje číslo nahor na najbližší násobok významnosti, ktorú mu pridelíte. Tento nástroj sa hodí, keď potrebujete prepísať štandardný protokol zaokrúhľovania vlastnými obchodnými pravidlami. Napríklad môžete Excel prinútiť zaokrúhliť 123,222 na 124 pomocou funkcie STROP s významom 1.

=CEILING(123,222;1)

Takže zadanie 0,01 ako význam povie funkcii CEILING, aby sa zaokrúhlila na najbližší cent.

Ak by ste chceli zaokrúhliť na najbližší nikel, môžete použiť 0,05 ako význam. Napríklad nasledujúci vzorec vráti 123,15:

=CEILING(123,11;.05)

Funkcia FLOOR funguje rovnakým spôsobom s tým rozdielom, že si vynúti zaokrúhlenie nadol na najbližší význam. Nasledujúca vzorová funkcia zaokrúhli 123,19 nadol na najbližší nikel, výsledkom čoho je 123,15:

=FLOOR(123,19;.05)

Zaokrúhľovanie na platné číslice v Exceli

V niektorých finančných správach sú čísla prezentované významnými číslicami. Myšlienka je taká, že keď máte do činenia s číslami v miliónoch, nemusíte správu zahlcovať nadbytočnými číslami, aby ste ukázali presnosť až na desiatky, stovky a tisíce miest.

Napríklad namiesto zobrazenia čísla 883 788 môžete číslo zaokrúhliť na jednu platnú číslicu. To by znamenalo zobraziť rovnaké číslo ako 900 000. Zaokrúhlením 883 788 na dve platné číslice by sa číslo zobrazilo ako 880 000.

V podstate sa domnievate, že miesto konkrétneho čísla je dostatočne významné na to, aby sa zobrazilo. Zvyšok čísla možno nahradiť nulami. Môžete mať pocit, že by to mohlo spôsobiť problémy, ale keď máte čo do činenia s dostatočne veľkými číslami, akékoľvek číslo pod určitým významom je bezvýznamné.

Nasledujúci obrázok ukazuje, ako môžete implementovať vzorec, ktorý zaokrúhľuje čísla na daný počet platných číslic.

Na zaokrúhlenie daného čísla na zadaný počet číslic použite funkciu ROUND v Exceli. Funkcia ROUND má dva argumenty: pôvodnú hodnotu a počet číslic, na ktoré sa má zaokrúhliť.

Zadaním záporného čísla ako druhého argumentu Excel prikáže zaokrúhliť na základe platných číslic naľavo od desatinnej čiarky. Napríklad nasledujúci vzorec vráti 9500:

=ROUND(9489,-2)

Zmena argumentu platných číslic na -3 vráti hodnotu 9000.

=ROUND(B14;-3)

Funguje to skvele, ale čo ak máte čísla na rôznych mierkach? To znamená, čo ak niektoré z vašich čísel sú milióny, zatiaľ čo iné sú státisíce? Ak by ste ich chceli všetky zobraziť s 1 platnou číslicou, museli by ste pre každé číslo vytvoriť inú funkciu ROUND, aby ste zohľadnili argument rôznych platných číslic, ktorý by ste potrebovali pre každý typ čísla.

Na vyriešenie tohto problému môžete nahradiť napevno zakódovaný argument platných číslic vzorcom, ktorý vypočíta, aké by toto číslo malo byť.

Predstavte si, že vaše číslo je -2330,45. Tento vzorec môžete použiť ako argument platných číslic vo svojej funkcii ROUND:

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

Tento vzorec najprv zabalí vaše číslo do funkcie ABS, čím účinne odstráni akýkoľvek negatívny symbol, ktorý môže existovať. Potom zalomí, čo vedie k funkcii INT, pričom odstráni všetky desatinné miesta, ktoré môžu existovať. Potom sa zalomí, čo vedie k funkcii LEN, aby sa získala miera počtu znakov v čísle bez desatinných miest alebo symbolov negácie.

V tomto príklade je výsledkom tejto časti vzorca číslo 4. Ak zoberiete číslo –2330,45 a odstránite desatinné miesta a záporný symbol, zostávajú vám štyri znaky.

Toto číslo sa potom vynásobí -1, aby bolo záporné, a potom sa pripočíta k počtu platných číslic, ktoré hľadáte. V tomto príklade tento výpočet vyzerá takto: 4 * –1 + 2 = –2.

Tento vzorec sa opäť použije ako druhý argument pre funkciu ROUND. Zadajte tento vzorec do Excelu a zaokrúhlite číslo na 2300 (2 platné číslice):

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

Potom môžete tento vzorec nahradiť odkazmi na bunky, ktoré ukazujú na zdrojové číslo a bunku, ktorá obsahuje požadovaný počet platných číslic.

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