Přeskočit navigaci | Přeskočit na novinky

Nový videokurz na Seduo: Excel pro začátečníky

     

Jste zde: Úvodní stránka » excel » funkce » excel-zaokrouhlovani

Excel a zaokrouhlování


Jednoduchý a často používaný úkol zaokrouhlit číslo (hodnotu) v Excelu, mnohdy přináší komplikace díky množství možností, které v Excel existují. Cílem článku je pomocí s orientací a použitím správné funkce pro zaokrouhlení.

Úvodem do zaokrouhlování

Microsoft Excel logo

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:


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ě!

Přesnost výpočtu Excel

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í

Nevzodné zaokrouhlení - Excel

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

Desetinná místa - Excel

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í

Zaokrouhlování funkce přehled - Excel

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

CELÁ.ČÁST (INT)

Zaokrouhlí číslo na nejbližší menší celé číslo.

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)

Vrátí číslo zaokrouhlené na požadovaný násobek.

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)

Zaokrouhlí číslo dolů, směrem k nule.

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)

Zaokrouhlí číslo nahoru od nuly.

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)

Zkrátí číslo na celé číslo.

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)

Obdržíte číslo zaokrouhlené směrem dolů (ve směru k nule).

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)

Obdržíte číslo zaokrouhlené směrem nahoru (ve směru od nuly).

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)

Zaokrouhlí číslo na zadaný počet číslic.

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)

Zaokrouhlí číslo nahoru na nejbližší celé liché číslo.

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)

Zaokrouhlí číslo nahoru na nejbližší celé sudé číslo.

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)

Zaokrouhlí číslo na určený počet desetinných míst, zformátuje toto číslo v desetinném formátu s volbou mezery v tisících.

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.

Přesnost výpočtu Excel
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ě.

Microsoft Excel VBA - stahuj logo

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: 09.07.2015 18:09

Odměna

Ušetřil vám tento web čas, peníze? Pomohl vyřešit problém? Jste ochotni poskytnout symbolickou odměnu na další rozvoj? Vybrte si formu odměny, která vám vyhovuje.


Pavel Lasák

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, PowerPoint) se věnuji od roku 2006. Své vědomosti a zkušenosti dávám k dispozici na různých školeních a konzultacích, ale také na tomto webu. K dispozici na tomto webu je mnoho návodu, tipů a triků včetně desítek různých šablon.

   Pavel Lasák LinkedIn Profil    Pavel Lasák Google+ Profil    Pavel Lasák facebook Profil    Pavel Lasák twitter Profil

Doporučte tento článek přátelům

Pokud vám článek pomohl, případně si myslíte, že může pomoci i někomu dalšímu, budu rád když jej sdílením doporučíte přátelům - děkuji:



Komentáře


Lubek

Přidáno: 20.07.15 11:56

Článek je k velmi potřebnému tématu. Bohužel v něm chybí ta nejuniverzálnější možnost a rada a "místo" toho je výklad nuancí a pro jednoduché rutinní užití obtížných funkcí, které ovšem je třeba alespoň tušit, to ano, výklad sám je na místě a k tématu. Právě pro to, aby se mohl člověk obejít bez těchto složitostí, má Excel poměrně univerzální řešení nepřesností, tj. "Nastavit přesnost podle zobrazení" (Soubor-Možnosti-odstavec "Vzorce"). To má sice jeden veliký háček, na který sice upozorní Excel při tomto nastavení - zaokrouhlí konstanty v buňkách, ale podle mě jde vlastně o jasnou chybu, nedostatek volitelných možností. Ale jinak tato možnost nastavení řeší podle mé zkušenosti veškeré potíže s nepřesností. Uživatel následně vidí a tiskne skutečné hodnoty přesně tak, jak se uplatní v navazujících výpočtech. A sám jednoduše a přehledně - a tím i spolehlivě - určuje také přesnost výpočtu. Zůstávají ovšem k dispozici i všechny funkce pro různá zvláštní zaokrouhlování dolů, nahoru, na volitelné násobky a podobně, ale drtivá většina užívaných obvyklých zaokrouhlení a zmiňovaných nepřesností je řešena právě tímto nastavením.

Martin

Přidáno: 21.03.16 16:00

V jednom svém programu ve VBA sčítám čísla s desetinným rozvojem. Bez toho, aniž bych použil jakoukoliv funkci pro zaokrouhlování, se mi výsledek zaokrouhluje na sudá čísla. Netuším, co s tím a proč se to vůbec děje... Můžete mi poradit? Děkuji!


Přidání nového komentáře

Poznámka: Z časových důvodu není v mých sílách zodpovědět všechny dotazy. Děkuji za pochopení.




(vlož číslo padesátpět)

Z bezpečnostních důvodu dočasně nemůžete použít HTML značky, kromě <code> a </code> (pro vkládání VBA kódu). Děkuji za pochopení.
Děkuji, za Vaše komentáře.

Za obsah komentářů neodpovídám, jelikož jsou komentáře publikovány ihned po jejich napsání čtenářem. Toto nemohu nijak ovlivnit. Přesto si vyhrazuji možnost jakýkoli neslušný komentář smazat bez udání důvodu. V komentářích si tykáme. ;-)





Excel


Sdílejte

Pomohl Vám návod?
Sdílejte na Facebooku, G+
LinkedIn...

Nové články


Newsletter

Přihlaste se odběru novinek a získejte:
Ebook zdarma -
10 kroků ke zvládnutí (Word, Excel, PowerPoint)
Šipka Kniha 3D Více o ebooku ...

TOPlist Licence Creative Commons webarchiv rss XML

Stránky o MS Office (Excel) produktu společnosti Microsoft. Neslouží jako technická podpora.
| Email na autora: pavel.lasak@gmail.com | Copyright © : Pavel Lasák 2004 - 2016 | 8743

Tento web zatím neprošel jazykovou korekturou. Beta verze redakčního systému.