17 nebo 15 číslic přesnosti - tipy pro Excel

V aplikaci Excel se objevuje ošklivá chyba výpočtu. Zdá se, že problém jde hluboko do výpočetního enginu aplikace Excel a nebude snadné jej opravit.

Jádrem problému je jednoduchá skutečnost: Excel ukládá 15 číslic přesnosti do jedné buňky. Můžete mít čísla, která mají 20 číslic, ale jakékoli číslice mezi poslední platnou číslicí a desetinnou čárkou musí být nulové.

Pouze 15 číslic přesnosti Tato chyba zřejmě porušuje hlavní směrnici Excel: Recalc or Die.

Nedávno jsem viděl dva případy, kdy výpočetní modul aplikace Excel vracel nesprávné výsledky. Když jsem se vrhl k problému a podíval se na podkladové XML, byl jsem překvapen, když jsem viděl, že Excel tajně ukládá 17 číslic do XML.

Problém je v tom, že Excel zobrazí pouze 15 číslic. Takže si myslíte, že máte číslo uložené jako 0.123456789012345, ale je skutečně uloženo jako 0.12345678901234567.

Ty poslední dvě číslice nevidíš. A většina funkcí aplikace Excel tyto poslední dvě číslice ignoruje. Pokud by * všechny * funkce ignorovaly poslední dvě číslice, neměli bychom problém. Zatím ale řazení, RANK a FREQUENCY používá všech 17 číslic.

Níže je známý trik pro hodnocení buněk. Pokud potřebujete, aby se každá hodnost objevila přesně jednou, můžete kombinovat RANK a COUNTIF. Na obrázku níže jsou Claire, Flo, Ivana a Lucy vázány na 115%. Pomocí vzorce RANK + COUNTIF by měli být zařazeni na 5, 6, 7 a 8.

Čtyři lidé jsou vázáni na 115%

Ale vzorec selže. Dva řádky jsou hodnoceny jako 7. To se nikdy nestane. Čtyři vzorce ve sloupci D zajistí, že 115% v B6, B9, B12 a B15 je stejných. =B6=B15Vzorec uvádí, že obě buňky obsahují stejná data.

Důvěryhodný vzorec nefunguje

Když jsem se pokusil problém izolovat, podívejte se pouze na funkci RANK. Měl by hlásit čtyřcestný remízu na 4. místě pro lidi se 115%. Ale nějak se Lucy v řadě 15 umístila před ostatními třemi.

Funkce hodnocení nefunguje

Abych to zjistil, poslal jsem žádost o pomoc ostatním MVP aplikace Excel. Jan Karel Pieterse otevřel soubor Excel a podíval se do XML. V XML můžete vidět, že ukládají 17 číslic přesnosti. Čtyři buňky, které vypadají jako spojovací v aplikaci Excel, nejsou spojeny v XML. Jedna ze 115% je uložena jako 1,1500000000000001 a ostatní jsou 1,1499999999999999.

Soubor XML odhaluje uložení dalších 2 číslic.

Zatím třídění, hodnocení a funkce FREKVENCE používají extra číslice. Proč je to problém? Protože počítáme s RANK a COUNTIF, aby oba používaly stejný počet číslic. S tím, že jedna funkce používá 15 číslic a druhá 17 číslic, máte problém.

Prozatím se zdá, že řešení převádí všechny vaše odpovědi pomocí =ROUND(A4,15).