V programe Excel sa objavuje škaredá chyba výpočtu. Zdá sa, že problém siaha hlboko do výpočtového modulu programu Excel a nebude ľahké ho vyriešiť.
Jadrom problému je jednoduchá skutočnosť: Excel ukladá do jednej bunky presnosť 15 číslic. Môžete mať čísla, ktoré majú 20 číslic, ale akékoľvek číslice medzi poslednou platnou číslicou a desatinnou čiarkou musia byť nulové.
Nedávno som videl dva prípady, keď výpočtový modul programu Excel vracal nesprávne výsledky. Keď som sa pripojil k problému a pozrel sa na podkladové XML, prekvapilo ma, že program Excel tajne ukladal do XML 17 číslic.
Problém je v tom, že Excel zobrazí iba 15 číslic. Takže si myslíte, že máte číslo uložené ako 0.123456789012345, ale skutočne je uložené ako 0.12345678901234567.
Tie posledné dve číslice nevidíte. Väčšina funkcií programu Excel tieto posledné dve číslice ignoruje. Keby * všetky * funkcie ignorovali posledné dve číslice, nemali by sme problém. Zatiaľ však triedenie, RANK a FREKVENCIA používa všetkých 17 číslic.
Ďalej je známy trik na hodnotenie buniek. Ak potrebujete, aby sa každá pozícia objavila presne raz, môžete kombinovať RANK a COUNTIF. Na obrázku nižšie sú Claire, Flo, Ivana a Lucy viazané na 115%. Pomocou vzorca RANK + COUNTIF by mali byť zoradené medzi 5, 6, 7 a 8.
Ale vzorec zlyhá. Dva riadky sú označené ako 7. To sa nikdy nestane. Štyri vzorce v stĺpci D zabezpečujú, aby 115% v B6, B9, B12 a B15 bolo rovnakých. =B6=B15Vzorec uvádza, že obe bunky obsahujú rovnaké údaje.
Keď som sa pokúsil problém izolovať, pozrite sa iba na funkciu RANK. Malo by hlásiť 4-cestné remízy na 4. mieste pre ľudí so 115%. Ale nejako je Lucy v rade 15 zaradená pred ostatnými tromi.
Aby som to zistil, poslal som žiadosť o pomoc ostatným MVP programu Excel. Jan Karel Pieterse otvoril súbor programu Excel a pozrel sa do XML. V XML vidíte, že ukladajú 17 číslic presnosti. Štyri bunky, ktoré vyzerajú ako vyrovnané v programe Excel, nie sú viazané v XML. Jedna zo 115% je uložená ako 1,1500000000000001 a ostatné sú 1,1499999999999999.
Zatiaľ triedenie, hodnotenie a funkcia FREKVENCIA používajú extra číslice. Prečo je to problém? Pretože rátame s tým, že RANK aj COUNTIF používajú rovnaký počet číslic. Keď má jedna funkcia 15 číslic a druhá 17 číslic, máte problém.
Zatiaľ sa zdá, že riešením je konverzia všetkých vašich odpovedí pomocou =ROUND(A4,15).
Každý piatok skúmam chybu alebo iné podozrivé správanie v programe Excel. Túto chybu výpočtu je ťažké odhaliť a dá sa považovať za veľkú rybu.
Excel myslel dňa
Požiadal som svojich priateľov programu Excel Master o radu o programe Excel. Dnešná myšlienka na zamyslenie:
„Zakaždým, keď zlúčiš bunky, zabiješ mačiatko“
Szilvia Juhasz
