Vzorce pro zaokrouhlování čísel v Excelu

Vaši zákazníci se často chtějí dívat na čistá, kulatá čísla. Zahlcení uživatele desetinnými hodnotami a zbytečnými číslicemi kvůli přesnosti může ve skutečnosti ztížit čtení vašich sestav. Z tohoto důvodu můžete zvážit použití funkcí pro zaokrouhlování aplikace Excel.

Tento obrázek ukazuje, jak je číslo 9,45 ovlivněno použitím funkcí ROUND, ROUNDUP a ROUNDDOWN.

Funkce ROUND v Excelu se používá k zaokrouhlení daného čísla na zadaný počet číslic. Funkce ROUND přebírá dva argumenty: původní hodnotu a počet číslic, na které se má zaokrouhlit.

Zadáním 0 jako druhého argumentu Excel sdělíte, že má odstranit všechna desetinná místa a zaokrouhlit celočíselnou část čísla podle prvního desetinného místa. Například tento vzorec se zaokrouhlí na 94:

=ROUND(94,45,0)

Zadáním 1 jako druhého argumentu Excel sdělíte zaokrouhlení na jedno desetinné místo na základě hodnoty druhého desetinného místa. Například tento vzorec se zaokrouhlí na 94,5:

=ROUND(94,45;1)

Jako druhý argument můžete také zadat záporné číslo, které Excelu řekne, aby zaokrouhloval na základě hodnot nalevo od desetinné čárky. Například následující vzorec vrátí 90:

=ROUND(94,45,-1)

Pomocí funkcí ROUNDUP nebo ROUNDDOWN můžete vynutit zaokrouhlení v určitém směru.

Tento vzorec ROUNDDOWN zaokrouhluje 94,45 dolů na 94:

=ROUNDDOWN(94,45,0)

Tento vzorec ROUNDUP zaokrouhluje 94,45 až 95:

=ROUNDUP(94,45,0)

Zaokrouhlování na nejbližší penny v Excelu

V některých odvětvích je běžnou praxí zaokrouhlit dolarovou částku na nejbližší cent. Obrázek ukazuje, jak může zaokrouhlení dolarové částky nahoru nebo dolů na nejbližší cent ovlivnit výsledné číslo.

Pomocí funkcí STROP nebo PODLAHA můžete zaokrouhlit na nejbližší haléře.

Funkce STROP zaokrouhlí číslo nahoru na nejbližší násobek významnosti, kterou mu předáte. Tento nástroj se hodí, když potřebujete přepsat standardní protokol zaokrouhlování svými vlastními obchodními pravidly. Můžete například přinutit Excel zaokrouhlit 123,222 na 124 pomocí funkce STROP s významem 1.

=CEILING(123,222;1)

Takže zadání 0,01 jako význam říká funkci STROP, aby se zaokrouhlila na nejbližší penny.

Pokud byste chtěli zaokrouhlit na nejbližší nikl, můžete jako význam použít 0,05. Například následující vzorec vrátí 123,15:

=CEILING(123,11;,05)

Funkce FLOOR funguje stejným způsobem až na to, že vynucuje zaokrouhlení dolů na nejbližší význam. Následující ukázková funkce zaokrouhlí 123,19 dolů na nejbližší nikl a výsledkem je 123,15:

=FLOOR(123,19;,05)

Zaokrouhlování na platné číslice v Excelu

V některých finančních zprávách jsou čísla uvedena významnými číslicemi. Myšlenka je taková, že když máte co do činění s čísly v milionech, nemusíte zprávu zahlcovat nadbytečnými čísly, aby bylo možné zobrazit přesnost až na desítky, stovky a tisíce míst.

Například místo čísla 883 788 můžete číslo zaokrouhlit na jednu platnou číslici. To by znamenalo zobrazit stejné číslo jako 900 000. Zaokrouhlení 883 788 na dvě platné číslice ukáže číslo jako 880 000.

V podstatě se domníváte, že místo konkrétního čísla je dostatečně významné, aby se zobrazilo. Zbytek čísla lze nahradit nulami. Můžete mít pocit, že by to mohlo způsobit problémy, ale když se zabýváte dostatečně velkými čísly, jakékoli číslo pod určitou významností je bezvýznamné.

Následující obrázek ukazuje, jak můžete implementovat vzorec, který zaokrouhluje čísla na daný počet platných číslic.

Pomocí funkce ROUND v Excelu zaokrouhlíte dané číslo na zadaný počet číslic. Funkce ROUND přebírá dva argumenty: původní hodnotu a počet číslic, na které se má zaokrouhlit.

Zadáním záporného čísla jako druhého argumentu sdělíte Excelu, aby zaokrouhloval na základě platných číslic nalevo od desetinné čárky. Například následující vzorec vrátí 9500:

=ROUND(9489,-2)

Změna argumentu platných číslic na -3 vrátí hodnotu 9000.

=ROUND(B14,-3)

Funguje to skvěle, ale co když máte čísla na různých měřítcích? To znamená, co když některá z vašich čísel jsou miliony, zatímco jiná jsou statisíce? Pokud byste je chtěli všechny zobrazit s 1 platnou číslicí, museli byste pro každé číslo vytvořit jinou funkci ROUND, aby se zohlednil argument různých platných číslic, který byste potřebovali pro každý typ čísla.

Chcete-li tento problém vyřešit, můžete nahradit pevně zakódovaný argument významných číslic vzorcem, který vypočítá, jaké by toto číslo mělo být.

Představte si, že vaše číslo je -2330,45. Tento vzorec můžete použít jako argument platných číslic ve funkci ROUND:

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

Tento vzorec nejprve zabalí vaše číslo do funkce ABS, čímž účinně odstraní jakýkoli záporný symbol, který může existovat. Potom zalomí, což má za následek funkci INT, přičemž odstraní všechna desetinná místa, která mohou existovat. Potom se zalomí, což vede k funkci LEN, aby se získalo měření počtu znaků v čísle bez desetinných míst nebo symbolů negace.

V tomto příkladu je výsledkem této části vzorce číslo 4. Pokud vezmete číslo –2330,45 a odstraníte desetinná místa a záporný symbol, zbývají čtyři znaky.

Toto číslo je poté vynásobeno -1, aby bylo záporné, a poté přidáno k počtu platných číslic, které hledáte. V tomto příkladu tento výpočet vypadá takto: 4 * –1 + 2 = –2.

Tento vzorec bude opět použit jako druhý argument pro vaši funkci ROUND. Zadejte tento vzorec do Excelu a zaokrouhlete číslo na 2300 (2 platné číslice):

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

Tento vzorec pak můžete nahradit odkazy na buňky, které ukazují na zdrojové číslo a buňku, která obsahuje požadovaný počet platných číslic.

=ROUND(B5,DÉLKA(INT(ABS(B5)))*-1+$E$3)