Chybné sčítání desetinných čísel v Excelu Vyřešeno

Programy pro práci v kanceláři (Word, Excel, Access…=>Office)

Moderátor: Mods_senior

lubo.
Level 2
Level 2
Příspěvky: 192
Registrován: červen 13
Pohlaví: Nespecifikováno
Stav:
Offline

Re: Chybné sčítání desetinných čísel v Excelu

Příspěvekod lubo. » 05 čer 2020 17:25

Nevím, co chceš poradit. Jak bylo zmíněno, není to chyba ale vlastnost a vyskytuje se všude, kde se počítá s čísly ve dvojkové soustavě s omezenou délkou slova.

Pochopitelně programátoři (až na výjimky) o tom vědí a hledají způsoby jak chybu minimalizovat. Současně ale musejí dodat dostatečně rychlý výpočet. Pokud máš čas, tak si zkus najít některé algoritmy pro sčítání velkého množství čísel.

Excel (i další tabulkové programy) se v řadě případů pokouší výsledek korigovat, při sčítání velkého počtu čísel už to prostě nemusí vždy povést.

Pokud čas nemáš, tak se smiř s tím, že při sčítání čísel se dvěma desetinnými místy vznikne chyba na 17. desetinném místě, a použij vhodný formát, aby to nevypadalo "blbě".

Reklama
Uživatelský avatar
atari
Level 6
Level 6
Příspěvky: 3195
Registrován: říjen 08
Pohlaví: Muž
Stav:
Offline

Re: Chybné sčítání desetinných čísel v Excelu

Příspěvekod atari » 05 čer 2020 17:37

Přílohy se vkládají sem do fora, a ne na leteckou poštu ani na uložto.

Uživatelský avatar
Funstorm007
Level 5
Level 5
Příspěvky: 2015
Registrován: říjen 08
Pohlaví: Muž
Stav:
Offline

Re: Chybné sčítání desetinných čísel v Excelu

Příspěvekod Funstorm007 » 05 čer 2020 19:28

Pardón, snad to už bude OK.

Sešit1.xlsx
(20.93 KiB) Staženo 74 x

Uživatelský avatar
faraon
Master Level 8.5
Master Level 8.5
Příspěvky: 7356
Registrován: prosinec 10
Pohlaví: Muž
Stav:
Offline

Re: Chybné sčítání desetinných čísel v Excelu

Příspěvekod faraon » 06 čer 2020 09:45

Tak jsem si s tím v noci hrál, a dělají to skutečně ta tři desetinná čísla (tedy tři dvojice), která se zrovna blbě zkombinovala do nevyčíslitelného výsledku. Pokud některé změním na celé nebo na vhodnější hodnotu, například 134.61 místo 134.6, vyjde správně nula. Zajímavé je, že obyčejná suma je nulová zrovna, asi to ta kontingenční tabulka sčítá jiným způsobem, kde se ta chyba víc projeví. Ale i se sumou se může stát totéž:
https://www.youtube.com/watch?v=uow1t-hWkxk

Čili staré pravidlo, že jakékoliv desetinné číslo v počítači je pouze jeho přibližný odhad, stále stoprocentně platí. Přesně se dá počítat pouze s celými čísly (pokud je tedy nepotřebuješ dělit!). Ale když to zrovna v tomhle případě nejde, budeš se muset smířit s tím že zkrácený náhled v kontingenční tabulce bude vypadat správně, i když správný není, a pokud tu hodnotu potřebuješ použít dál ještě někde jinde, musíš si jí tam sám zaokrouhlit na potřebnou přesnost:
mt.png
mt.png (2.99 KiB) Zobrazeno 1330 x


Jiné snadné řešení mě nenapadá, pak leda ještě že bys "sehnal" zdrojáky Office a celé to přeprogramoval. Já to zkoušel v LibreOffice (takže to není problém jen Excelu ale obecný), tam by se to dalo provést, protože jsou open source. Ale než se do toho pustíš, vezmi si nějakou jednoduchou kalkulačku a zkus na ní zadat tohle:

Kód: Vybrat vše

1 ÷ 7 × 7 =

Správný výsledek je zřejmý ;-)

Počítačům se prostě nedá věřit, a už vůbec se na ně nesmí spoléhat, natož jim svěřit civilizaci, jak se již stalo...
"Král Lávra má dlouhé oslí uši, král je ušatec!

(pravil K. H. Borovský o cenzuře internetu)

Uživatelský avatar
Funstorm007
Level 5
Level 5
Příspěvky: 2015
Registrován: říjen 08
Pohlaví: Muž
Stav:
Offline

Re: Chybné sčítání desetinných čísel v Excelu

Příspěvekod Funstorm007 » 06 čer 2020 11:12

Jasně takže ať to zaokrouhlím jakkoliv, tak ta kontingenční tabulka tu chybu zase vytvoří.

Zajímavé ale je, že když použiju sloupec B do části "sloupců" v kontingenční tabulce abych měl vlastně zvlášť v jednom sloupci kladná a v druhém záporná tak celkový součet těchto dvou sloupců v kontingenčce je už v pořádku přitom vlastně dělá úplně to samé jenom trošku oklikou.

V případě jednoduché sumy není problém tuto chybu na konci eliminovat ale já tam potřebuju tu kontingenčku jelikož se zdrojová data budou měnit a taky v originále je jich tam mnohem víc. Dumal jsem nad tím jak jsi říkal že mám počítat v celých číslech tak jsem to vše vynásobil stem a abych dostal v kontingenčce správný výsledek tak jsem použil počítané pole a ejhle, ono to funguje :shock:

Sešit1.xlsx
(23.66 KiB) Staženo 49 x


Musím to ještě vyžkoušet na originále a jestli to bude držet.

Uživatelský avatar
faraon
Master Level 8.5
Master Level 8.5
Příspěvky: 7356
Registrován: prosinec 10
Pohlaví: Muž
Stav:
Offline

Re: Chybné sčítání desetinných čísel v Excelu

Příspěvekod faraon » 06 čer 2020 11:44

Ano, sečtením přesných čísel dostaneš nepřesný výsledek, protože se u některých z nich kousek přesahující velikost datového typu uřízne. Podobně jako bys v desítkové soustavě sčítal do desetinných čísel převedené například 1/3 a 1/7, tak ve dvojkové je to ta 1/10 a spousta dalších. Kdyby ten program počítal s čísly ve zlomkovém tvaru, ne s plovoucí čárkou, počítal by mnohem přesněji.

Pokud ta čísla sčítáš v jiném pořadí, tak se to stát nemusí, což by mohl být případ těch oddělených součtů kladných a záporných čísel. A zřejmě i toho, že Suma to sečte správně bez odchylky.

Jestli to půjde počítat v těch celých číslech a setiny z toho udělat až úplně na konci, budeš mít po starostech, tam by mohlo k nějaké úchylce dojít teprve až v tom závěrečném převodu. Ale to je jediné kritické místo, zatímco tady jich máš právě tolik, kolik čísel sčítáš.

Ještě mě napadla jedna věc, jak tam měníš znaménko. To také může být trochu problém, protože u čísla uloženého v doplňkovém formátu se to dá provést odečtením od nuly, a už při tom ti může bit na konci pěkně uletět. Malý příklad, osmibitový integer s rozsahem -128..127, kde nejvyšší bit určuje jestli je číslo kladné (0) nebo záporné (1):

Kód: Vybrat vše

(dec)   (bin)
  18  0_0010010
 -18  1_1101110
Existuje reprezentace čísel, kde by -18 vypadalo 1_0010010, což se od kladného tvaru liší pouze tím znaménkovým bitem, ale to používaly jen některé počítače před mnoha desítkami let. Dneska už se to moc nevidí. Viz https://www.root.cz/clanky/norma-ieee-754-a-pribuzni-formaty-plovouci-radove-tecky/.

Pokud by to nebyl integer ale float, tak by se před sčítáním ještě navíc čísla posouvala vůči sobě tak, aby se desetinná tečka dostala na stejnou pozici, a to menší z nich může vpravo vyjet z registru o celou skupinu bitů, které tak nenávratně zmizí. Ani se to nezaokrouhlí, prostě se zahodí.
"Král Lávra má dlouhé oslí uši, král je ušatec!

(pravil K. H. Borovský o cenzuře internetu)

lubo.
Level 2
Level 2
Příspěvky: 192
Registrován: červen 13
Pohlaví: Nespecifikováno
Stav:
Offline

Re: Chybné sčítání desetinných čísel v Excelu

Příspěvekod lubo. » 07 čer 2020 02:11

Sčítání v celých číslech má také svá omezení (od určité velikosti už nejsou celá).
Při všech těchto počtech záleží na velikosti čísel, rozsahu absolutních hodnot, pořadí sčítání.
Zkuste do buňky A1 zadat 0, do buňky A2 vzorec =A1+10000000 a zkopírujte vzorec do zbytku sloupce.
Potom do buňky B1 zadejte 10485750000000, do buňky B2 vzorec =B1-10000000 a zkopírujte do zbytku řádku. Jaká je suma sloupce A a jaká bude suma sloupce B?

U mne to je:
=SUMA(A:A) = 5497552895963620000
=SUMA(B:B) = 5497552896059660000

Ani jeden výsledek není správný.

Příklad je poněkud umělý, ale v některých funkcích, kde se ve výpočtu pracovalo se sumou kvadrátů hodnot, docházelo k chybným výsledkům. Jestli je to všude opraveno jsem neověřoval.

Pokud vhodně vybereme dvě čísla, tak i tam můžeme v součtu nalézt velké chyby. Jak už jsem zmínil, je to vlastnost.

Uživatelský avatar
Funstorm007
Level 5
Level 5
Příspěvky: 2015
Registrován: říjen 08
Pohlaví: Muž
Stav:
Offline

Re: Chybné sčítání desetinných čísel v Excelu  Vyřešeno

Příspěvekod Funstorm007 » 07 čer 2020 14:09

Čísla budou teoreticky maximálně od -2000 do +2000 takže po vynásobení stem -200000 až +200000.

Prostě, není to dokonalé :D Ale protože nežijeme v dokonalém světě tak to beru jak to je. Svůj problém jsem nicméně jakž takž vyřešil a když vím o této problematice tak si do budoucna už možná budu schopný pomoct sám s ošetřením.

Takže díky pánové :clap: :thumbup:


  • Mohlo by vás zajímat
    Odpovědi
    Zobrazení
    Poslední příspěvek
  • Import PDF do excelu Příloha(y)
    od luko02420 » 13 kvě 2023 11:29 » v Kancelářské balíky
    18
    3255
    od luko02420 Zobrazit poslední příspěvek
    16 kvě 2023 11:27
  • Poškozený soubor excelu Příloha(y)
    od Jsimi » 06 úno 2024 22:43 » v Kancelářské balíky
    0
    614
    od Jsimi Zobrazit poslední příspěvek
    06 úno 2024 22:43
  • Promítaní excelu a videa zároveň
    od Marw7_ » 02 čer 2023 15:57 » v Vše ostatní (sw)
    2
    1285
    od X Zobrazit poslední příspěvek
    02 čer 2023 18:06
  • Tisk z excelu mění výšky buněk
    od Moonddur » 28 dub 2023 11:50 » v Kancelářské balíky
    1
    1655
    od atari Zobrazit poslední příspěvek
    28 dub 2023 12:08
  • Jak do Excelu dostat aktuální hodnotu z webové stránky? Příloha(y)
    od Peťa » 10 lis 2023 09:41 » v Kancelářské balíky
    2
    1679
    od Peťa Zobrazit poslední příspěvek
    10 lis 2023 16:24

Zpět na “Kancelářské balíky”

Kdo je online

Uživatelé prohlížející si toto fórum: Žádní registrovaní uživatelé a 3 hosti