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: 178
Registrován: červen 13
Pohlaví: Nespecifikováno

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 5.5
Level 5.5
Příspěvky: 2583
Registrován: říjen 08
Pohlaví: Muž

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 4.5
Level 4.5
Příspěvky: 1972
Registrován: říjen 08
Pohlaví: Muž

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
Nemáte oprávnění prohlížet přiložené soubory.

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

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


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...
Nemáte oprávnění prohlížet přiložené soubory.
"Dokud nebyly počítače, programování nebylo problémem.
Jestliže bylo několik slabých počítačů, bylo programování malým problémem.
Když však programátoři získali počítače na svou dobu ohromné síly, stalo se také programování ohromným problémem."

E. W. Dijkstra, 1972

Uživatelský avatar
Funstorm007
Level 4.5
Level 4.5
Příspěvky: 1972
Registrován: říjen 08
Pohlaví: Muž

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


Musím to ještě vyžkoušet na originále a jestli to bude držet.
Nemáte oprávnění prohlížet přiložené soubory.

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

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í.
"Dokud nebyly počítače, programování nebylo problémem.
Jestliže bylo několik slabých počítačů, bylo programování malým problémem.
Když však programátoři získali počítače na svou dobu ohromné síly, stalo se také programování ohromným problémem."

E. W. Dijkstra, 1972

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

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 4.5
Level 4.5
Příspěvky: 1972
Registrován: říjen 08
Pohlaví: Muž

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
  • excel sčítání hodnot jiného počtu
    od berate » 14 lis 2019 15:08 » v Kancelářské balíky
    4
    913
    od atari
    15 lis 2019 09:13
  • Sčítání sloupce podle více pravidel
    od luko02420 » 20 srp 2020 05:50 » v Kancelářské balíky
    0
    310
    od luko02420
    20 srp 2020 05:50
  • Přeměna čísel na IP adresu.
    od Daminekkkk » 09 dub 2020 00:43 » v Vše ostatní (hw)
    1
    370
    od CyberCodeDroid
    09 dub 2020 04:34
  • Ako funguje generovanie náhodných čísel
    od aribosstv » 30 zář 2020 15:30 » v Vše ostatní (hw)
    3
    291
    od ITCrowd
    30 zář 2020 18:01
  • Excel - seřazení podle čísel uprostřed
    od Karrex » 20 lis 2019 15:41 » v Kancelářské balíky
    10
    910
    od Zivan
    21 lis 2019 19:49

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

Kdo je online

Uživatelé prohlížející si toto fórum: CommonCrawl [Bot] a 4 hosti