Excel a zaokrouhlování

Úvodem do zaokrouhlování

V tomto článku se dozvíte, jaké jsou možností zaokrouhlování, jaké zrady (princip výpočtu v Excel) vás mohou potkat. Pro přehlednost jsem rozdělil článek na jednotlivé kapitoly:

• Teoretický úvod
• Vizuální úprava čísel
• Přehled "zaokrouhlovacích" funkcí
  • CELÁ.ČÁST (INT)
  • MROUND (MROUND)
  • ROUNDDOWN (ROUNDDOWN)
  • ROUNDUP (ROUNDUP)
  • USEKNOUT (TRUNC)
  • ZAOKR.DOLŮ (FLOOR)
  • ZAOKR.NAHORU (CEILING)
  • ZAOKROUHLIT (ROUND)
  • ZAOKROUHLIT.NA.LICHÉ (ODD)
  • ZAOKROUHLIT.NA.SUDÉ (EVEN)
  • ZAOKROUHLIT.NA.TEXT (FIXED) - jedná se o funkci z kategorie Text
• Speciálnosti - (jak lze speciálně zaokrouhlovat)
• Funkce ve VBA - (v přípravě)
• Ke stažení - (v přípravě)

Obecný teoretický úvod

K zaokrouhlení (úpravě počtu desetinných míst) lze v Excel využít:

• Úprava vizuální podoby čísla (vlastní formát)
• Funkce listu
• Speciální kombinace funkci
• Využít funkce ve VBA

Nepřesnosti

Při výpočtech dochází k nepřesnostem. Excel má 15-ti místnou přesnost a u výpočtu interpoluje/zaokrouhluje. Proto i jednoduchý výpočet 111,13 - 111,12 Excel nevypočte přesně!

Po vložení vzorce =111,13-111,12 vidíte výsledek 0,01 (což je správné, ale jde jen o zobrazené číslo). Ve skutečnosti Excel vypočte 0,00999999999999091 ! Stačí přidat další desetinná místa a je jasné, že výpočet je nepřesný!

Například v logických funkcích, nebude správně fungovat 0,01 není 0,00999999999999091. Tato chyba se hledá velice dlouho (navíc pokud o tomto problému člověk neví).

Takovýchto nepřesností je mnoho, stačí si jen trochu pohrát ;)

Špatné překlady

Na mnoha webech jsou zaokrouhlovací funkce špatně přeloženy ZAOKR.DOLŮ (správně v angličtině FLOOR) se špatně překládá z angličtiny jako ROUNDDOWN. Přitom ROUNDDOWN (ROUNDDOWN) je jiná samostatná funkce. Podobný problém má funkce ZAOKR.NAHORU (správně v angličtině CEILING). Funkce ROUNDUP (ROUNDUP) se v češtině i angličtině řekne stejně.

Tímto dochází k tomu, že se popisuje něco jiného, než ve skutečnosti funkce dělá a zbytečně se hledají chyby, proč funkce nedělá co má.

Pod čarou: Toto je další důvod, proč tvrdím, že překlady funkcí je jedna z největších chyb, kterou Microsoft v Excel zavedl.

Nevhodné zaokrouhlení

Nesprávné zaokrouhlení (vizuální úprava) může mít za příčinu chybné zobrazení výsledku, přitom Excel v tomto případě počítá naprosto správně (jen bylo odebráno jedno desetinné místo). Podrobněji v další kapitole.

1,94	1,9
1,94	1,9
3,88	3,9

Vizuální úprava čísel

Vizuálně upravovat čísla ubírat, přidávat jim desetinná místa můžete za využití karty Domu - sekce Číslo - klik na ikony:

• Přidat desetinné místo
• Odebrat desetinné místo

Správně zvoleným počtem desetinných míst, můžete odhalit chyby ve výpočtech. Viz například úkol spočítat rozdíl:

=111,13-111,12

Excel vypočte jako 0,00999999999999091. Což zjistíte až přidáním správného počtu desetinných míst.

Nesprávným ubráním desetinných míst, může vypadat, že Excel počítá špatně

1,94+1,94=3,88 1,9+1,9=3,9

Přitom jde jen o nevhodně ubrané desetinné místo.

Přehled funkcí pro zaokrouhlování

Popsány jsou tyto funkce:

• CELÁ.ČÁST (INT)
• MROUND (MROUND)
• ROUNDDOWN (ROUNDDOWN)
• ROUNDUP (ROUNDUP)
• USEKNOUT (TRUNC)
• ZAOKR.DOLŮ (FLOOR)
• ZAOKR.NAHORU (CEILING)
• ZAOKROUHLIT (ROUND)
• ZAOKROUHLIT.NA.LICHÉ (ODD)
• ZAOKROUHLIT.NA.SUDÉ (EVEN)
• ZAOKROUHLIT.NA.TEXT (FIXED)

Ukázka použití funkcí

Uložením obrázku získate větší rozlišení.

CELÁ.ČÁST (INT)

Syntaxe

CELÁ.ČÁST(číslo) INT(číslo)

Popis argumentů

• Číslo - reálné číslo, které se má zaokrouhlit

Příklady

2 =CELÁ.ČÁST(2,9) 2 =CELÁ.ČÁST(2,1) -3 =CELÁ.ČÁST(-2,9) -3 -3 =CELÁ.ČÁST(-2,1) -3

MROUND (MROUND)

Syntaxe

MROUND(číslo;Násobek) MROUND(číslo,Násobek)

Popis argumentů

• Číslo - toto číslo bude zaokrouhleno
• Násobek - na jaký násobek ma být číslo zaokrouhleno

Příklady

1300 =MROUND(1298;4) 1298,2 =MROUND(1298,1235;0,2)

ROUNDDOWN (ROUNDDOWN)

Syntaxe

ROUNDDOWN(číslo;číslice) ROUNDDOWN(číslo,číslice)

Popis argumentů

• Číslo - je libovolné reálné číslo, které má být zaokrouhleno
• Číslice - udává, na kolik desetinných míst se má zadané číslo zaokrouhlit

Příklady

3 =ROUNDDOWN(3,2; 0) 76 =ROUNDDOWN(76,9;0) 3,141 =ROUNDDOWN(3,14159; 3) -3,1 =ROUNDDOWN(-3,14159; 1) 31400 =ROUNDDOWN(31415,92654; -2)

ROUNDUP (ROUNDUP)

Syntaxe

ROUNDUP(číslo; číslice) ROUNDUP(číslo, číslice)

Popis argumentů

• Číslo - je libovolné reálné číslo, které má být zaokrouhleno
• Číslice - udává, na kolik desetinných míst se má zadané číslo zaokrouhlit

Příklady

4 =ROUNDUP(3,2; 0) 77 =ROUNDUP(76,9;0) 3,142 =ROUNDUP(3,14159; 3) -3,2 =ROUNDUP(-3,14159; 1)

USEKNOUT (TRUNC)

Syntaxe

USEKNOUT(číslo;desetiny) TRUNC(číslo,desetiny)

Popis argumentů

• Číslo - číslo, které bude zkráceno
• Desetiny - počet desetinných míst ve zkráceném čísle (výchozí nula -0)

Příklady

12936,293 =USEKNOUT(12936,293846;3) 12936,29 =USEKNOUT(12936,293846;2) 12936,2 =USEKNOUT(12936,293846;1) 12936 =USEKNOUT(12936,293846;0)

ZAOKR.DOLŮ (FLOOR)

Funkce zaokrouhlí číslo na nejbližší násobek směrem k nule. Při zaokrouhlení kladného čísla bude mít zaokrouhlené číslo nižší hodnotu. Zaokrouhlené záporné číslo bude mít naopak vyšší hodnotu. Je opakem funkce ZAOKR.NAHORU (CEILING).

Syntaxe

ZAOKR.DOLŮ(číslo;násobek) FLOOR(číslo,násobek)

Popis argumentů

• Číslo - je numerická hodnota, kterou chcete zaokrouhlit
• Násobek - je násobek, na který se má zaokrouhlovat

Příklady

2 =ZAOKR.DOLŮ(2,5; 1) -2 =ZAOKR.DOLŮ(-2,5; -2) -4 =ZAOKR.DOLŮ(-2,5; 2) 1,5 =ZAOKR.DOLŮ(1,5; 0,1) 0,23 =ZAOKR.DOLŮ(0,234; 0,01)

ZAOKR.NAHORU (CEILING)

Zaokrouhluje číslo nejbližším násobkem směrem od nuly. Zaokrouhlené kladné číslo bude vyšší. Zaokrouhlené záporné číslo bude nižší. Je opakem funkce ZAOKR.DOLŮ.

Syntaxe

ZAOKR.NAHORU(číslo;násobek) CEILING(číslo;násobek)

Popis argumentů

• Číslo - je numerická hodnota, kterou chcete zaokrouhlit
• Násobek - je násobek, na který se má zaokrouhlovat

Příklady

3 =ZAOKR.NAHORU(2,5; 1) -4 =ZAOKR.NAHORU(-2,5; -2) -2 =ZAOKR.NAHORU(-2,5; 2)

ZAOKROUHLIT (ROUND)

Syntaxe

ZAOKROUHLIT(číslo;číslice) ROUND(číslo,číslice)

Popis argumentů

• Číslo - číslo, které chcete zaokrouhlit
• Číslice - počet číslic, na které se bude zaokrouhlovat

Příklady

1,3 =ZAOKROUHLIT(1,25345678;1) 1,25 =ZAOKROUHLIT(1,25345678;2) 1,253 =ZAOKROUHLIT(1,25345678;3) 1 =ZAOKROUHLIT(1,25345678;0) 10 =ZAOKROUHLIT(12,25345678;-1)

ZAOKROUHLIT.NA.LICHÉ (ODD)

Syntaxe

ZAOKROUHLIT.NA.LICHÉ(číslo) ODD(číslo)

Popis argumentů

• Číslo - které se má zaokrouhlit

Příklady

3 =ZAOKROUHLIT.NA.LICHÉ(2,1) 3 =ZAOKROUHLIT.NA.LICHÉ(2,95) -3 =ZAOKROUHLIT.NA.LICHÉ(-2,95)

ZAOKROUHLIT.NA.SUDÉ (EVEN)

Syntaxe

ZAOKROUHLIT.NA.SUDÉ(číslo) EVEN(číslo)

Popis argumentů

• Číslo - které se má zaokrouhlit

Příklady

4 =ZAOKROUHLIT.NA.SUDÉ(2,1) 4 =ZAOKROUHLIT.NA.SUDÉ(2,95) -4 =ZAOKROUHLIT.NA.SUDÉ(-2,1)

ZAOKROUHLIT.NA.TEXT (FIXED)

Poznámka: Je součásti kategorie textové

Syntaxe

ZAOKROUHLIT.NA.TEXT(číslo;desetiny;bez_čárky) FIXED(číslo;desetiny;bez_čárky)

Popis argumentů

• Číslo - je číslo, které chcete zaokrouhlit a převést na text
• Desetiny - je počet číslic vpravo od desetinné čárky
• Bez_čárky - nepovinný - je logická hodnota, která, pokud je NEPRAVDA budou odděleny tisíce má vkládat tečku místo čárky.

Příklady

1 234,6 =ZAOKROUHLIT.NA.TEXT(1234,567; 1) 1 230 =ZAOKROUHLIT.NA.TEXT(1234,567; -1) -1 230 =ZAOKROUHLIT.NA.TEXT(-1234,567; -1)

Poznámka: V nápovědách je informace o desetinné čárce (poslední argument), přitom tento argument v české verzi Excel na českém Windows "vkládá" jen oddělení tisíců (čárku za tečku nevymění).

Speciálnosti (jak lze ještě zaokrouhlovat)

V Excelu lze využít kombinací několika funkcí a zaokrouhlovat na požadovaný počet zobrazených čísel. Viz ukázka.

0,051234567890000	0,0000051234567890
4	4
Zaokrouhleno	Zaokrouhleno
0,05123000000	0,000005123000

Řešení:

=ROUNDDOWN(B11;B12-(1+CELÁ.ČÁST(LOG(ABS(B11)))))

Poznámka: Tento vzorec mám z internetu, jen nevím, kde jsem vzorec viděl poprvé. Chlubí se jím několik webů (stejně jako s výpočtem velikonoc).

VBA funkce

Excel má i vestavěné funkce na zaokrouhlování ve VBA. Podrobněji sepsání pro tuto kapitolu je v přípravě.

Ke stažení

Soubory jsou v přípravě.

Závěrem

Jak je z článku vidět, možnosti pro zaokrouhlování jsou široké (a to jsem nepopsal vše), což je na jednu stranu dobře (pokud s tím člověk umí zacházet), na druhou stranu přináší spoustu komplikací, pokud se zaokrouhlování používá nesprávně.

Pokud využíváte zaokrouhlování, nebo jste se setkali s nějakou komplikací? Můžete to zmínit v komentářích.

Článek byl aktualizován: 19.09.2020 10:57

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

           

Komentáře