Excel számolási hiba?
2019-07-15T19:58:50+02:00
2019-07-18T04:35:47+02:00
2022-09-09T10:20:32+02:00
apic55
Sziasztok! Ma olyan dologgal találkoztam, amire nem igen tudnék "normális" választ adni.
Egy Excel táblában néhány szám különbségét kellett volna feltételes formázással megjelenítenem, úgy, hogy a kivonás eredményének egy adott értéktől eltérése adja a formázás "értékét". A számok minden esetben 2 tizedessel bírnak, valamennyi (függetlenül attól, hogy esetleg a második tizedes csak éppen nulla.
Beírtam egy végtelen primitív táblázatba, elvégeztettem a különbségképzést (szándékosan nem kivonást írtam, mert abszolút eltérés kellett, nem csak számszaki, az előjeleket nem kérte a főnököm, így gondoltam, elég ez az egyszerűség. Beállítottam a feltételes formázási szabályokat: ha a kapott érték megegyezik 0,20-vel, akkor legyen sárga, ha kisebb, mint 0,20 legyen piros, ha nagyobb legyen zöld a cella. És jött a meglepetés: hiába láttam, hogy az eredmény 0,20, a cella piros lett, nem sárga. Miután régi motoros vagyok, gondoltam elütöttem valamit: nem, jók az alapszámok. Miután keresgéltem az okot, rá is leltem: a tizedeseknél a tizenharmadik helyi-értéknél oda került egy icike-picike 1-es a sok 0 mögé, tehát a két db 2 tizedesjegyű szám különbsége nem egy 2 tizedessel bíró szám, hanem egy szeplős valami lett.
Segítséget kérnék, hogyan lehetne ezt eltüntetni? Mitől lehetséges ez egyáltalán?
Mellékelem a táblázatot, az A oszlopban a számokkal, a B oszlopban az abs függvénnyel, a C oszlopban a sima kivonással. A feltételes formázást itt most nem állítottam be, mert nem az a problémám, hanem az eltérés...
És még egy info: vegyész vagyok, nem matematikus/programozó (tehát láma...)
Köszönettel várom a megoldást.
Mutasd a teljes hozzászólást!
Csatolt állomány
Szia!

Magyarázat a jelenségre az Excel számábrázolási módja és annak pontossága. A lebegőpontos számábrázolás a nem egész számoknál korlátozott pontosságot eredményez (2-es számrendszer! miatt). Ezért ilyen esetekben a hasonlítást a számunkra mérvadó pontosságnak megfelelően kell elvégezni. Erre jó kerekítés függvény.

=KEREKÍTÉS(ABS(A3-A$2);2)
Üdv..
Mutasd a teljes hozzászólást!

  • TRUNC függvény segít.
    A kinézeten pedig a LEFT.
    Lásd csatolt fálj.
    Mutasd a teljes hozzászólást!
    Csatolt állomány
  • Szia!

    Magyarázat a jelenségre az Excel számábrázolási módja és annak pontossága. A lebegőpontos számábrázolás a nem egész számoknál korlátozott pontosságot eredményez (2-es számrendszer! miatt). Ezért ilyen esetekben a hasonlítást a számunkra mérvadó pontosságnak megfelelően kell elvégezni. Erre jó kerekítés függvény.

    =KEREKÍTÉS(ABS(A3-A$2);2)
    Üdv..
    Mutasd a teljes hozzászólást!
  • Szia!
    Valami ilyenre gyanakodtam, de ez nem az én terepem. Köszönöm a magyarázatot, bevallom meglepett a hiba oka. A kerekítést már a kérdésem feltevése előtt alkalmaztam, csak nem tartottam elegánsnak, hogy segédfüggvényekkel kellene manipulálnom egy viszonylag egyszerű (ezért neveztem primitívnek) számolásnál.
    Köszönöm a segítséget és a magyarázatod!
    Üdv
    András
    Mutasd a teljes hozzászólást!
  • Szia!
    Köszönöm a segítséget! A küldött táblázat "E" oszlopában tudtam csak a helyes feltételes formázást beállítani (amit a felvetésben is leírtam), az "F" oszlopban csak az egyenlőségnél volt helyes a formázás, a "H" oszlopban (LEFT+TRUNC) egyáltalán nem működött, minden cellát nagyobbnak értékelt, mint 0,2...
    A "csonk" (TRUNC) függvényt eddig nem használtam, most látom, mi is az értelme. Megint tanultam valamit (van még mit).
    Üdvözlettel:
    András
    Mutasd a teljes hozzászólást!
  • A leírásod alapján fontos a 2 tizedes jegy.
    Elfogadtad Fferi kerekítős megoldását, aminél a kerekítés hatására a végeredmény változik, míg a TRUNC függvény esetén nem.

    Az eredményed hibás lesz!
    Nem értelek.
    Mutasd a teljes hozzászólást!
    Csatolt állomány
  • Szia, Ervin!
    Feri megoldását fogadtam el, mert a konkrét kérdésem az ok volt, miért is lett hibás a táblázatban az eredmény. Tulajdonképpen a sokadik tizedesnél dobott - számomra érhetetlen - plusz értéket kifogásoltam, arra kerestem a választ, amire Feri (és privátban mások is - ugyanazt írták, mármint a bináris számkezelés "hiányosságait". A te megoldásod is megfelelt, de Feri adta meg a választ a hibajelenség okára. Ezért fogadtam el az ő megoldását.
    A "kerekítés" függvénnyel én is meg tudtam oldani (Feri válasza előtt már működött a táblázat), de az ok rejtély maradt. Ugyanakkor ezt a számkezelési paradoxont más platformokon (Linux, stb.) is tapasztalták mások, tehát ez egy valós hiba, amivel számolni kell. Mellékelem a te általad javított (módosított) táblázatot azokkal a feltételes formázásos elvárásokkal, aminek meg kellet felenie a táblázatnak. A TRUNC (csonkolásos) függvény használatának eredményét te is megállapthatod. A kerekítéses függvényt és sem trtom elegánsnak a XXI. században, de ez van, amíg a Feri által leírt problematika fennáll, a cél szentesíti az eszközt. A erekítés hatására ebben a táblázatban az eredmény nem változik, mert egy egyszerű papíron ceruzával leellenőrizheted, azok a számok láthatók az eredmény-cellákban, aminek ott kellene lenniük. Még egy adalék: azért volt fontos megoldást találnom a helyes - tehát a ceruzás ellenőrzéses - eredményeket láttatni, egy képlet eredményeként, mert munkaközi eredményjellemzéshez kell, amikor is nincs idő a hibákat keresgélni, és minden esetben jó színt kell látni, ha az ellenőrző személy rutinellenőrzésként az eltéréseket keresi. Egy egyedi piros cellát hamarabb megtalál a sok egyéb színű között (vegyészekről beszélek, akiknek színtévesztési vizsgálatokon kell évente részt venniük), mint a számokat önmagukat számolgatja, és keresi a kakukktojást. 
    Üdvözlettel
    András
    Mutasd a teljes hozzászólást!
    Csatolt állomány
  • Köszi. 
    Mutasd a teljes hozzászólást!
abcd