Telefonszámunk: 1-472-0679

15 karakternél hosszabb számok az Excelben

2019-07-01 - horvimi - Megtekintések száma: 214 - Kategória: Általános tippek, Megjelenítés, Szerkesztés
Hivatkozott Excel verzio: Excel 2010 Excel 2013 Excel 2016 Office365

Az utóbbi időben többször is szembe jött céges tanfolyamokon ez a probléma, és elhatároztam, írok róla egy cikket. Ha az Excel egy cellájába olyan hosszabb számot írunk vagy képlet eredményül kapunk, aminek az egész része meghaladja a 15 számjegyet, érdekes jelenségekkel találkozhatunk.

  • 11 számjegyig nincs probléma
  • 12-15 számjegy között a cellában tudományos alak jelenik meg, de a szerkesztőlécen minden OK.
  • 15 számjegy felett tudományos alak lesz, és nem lehet vele mit kezdeni
    Ráadásul a szerkesztőlécben azt látjuk, hogy a 15 karakter feletti számjegyek helyett nullák szerepelnek. Ez konkrétan adatvesztés.

jelenség_15_számjegy

A pirossal keretezett sorban látszik, hogy az utolsó két számjegyet elvesztettük, és sehonnan nem fogjuk tudni, hogy mi volt ott előtte.

A probléma az Excel jelenleg használt összes verzióját érinti, sőt, nem csak az Excel, hanem sok egyéb program is érintett. Az ok hardver szintű, a gépekben használatos lebegő pontos számábrázolásból ered. Ennek bővebb taglalásától most eltekintek, akit közelebbról érdekel, keressen rá: “IEEE floating point”.

Jelen cikk célja a figyelemfelkeltés és  a probléma megelőzése vagy orvoslása.

Mikor jelenthet ez problémát nekünk?

  • Ilyen hosszú számokat ritkán szoktunk begépelni, de ha mégis, akkor is ott lesz az eredeti szám, úgyhogy begépeléskor nem lehet baj
  • Ha szöveges (TXT/CSV) fájlból importálunk, akkor már baj lehet akkor, ha a megnyitáskor az Excel egyből, automatikusan oszlopokra bontotta.
    Jó esetben az eredeti szövegfájl még megvan, és a hibát orvosolhatjuk.
  • Ha vágólapról, beillesztéssel (pl egy weboldalról vagy szövegfájlból) kerültek az adatok a cellákba, és nem vette észre senki a problémát (Elég nehéz nem észrevenni)
  • A feltétel szerint összesítő és az adatbázis függvények mindegyike rosszul működik, ha ekkora számsorokkal akarunk dolgozni a feltételben.

Megoldások konstans adatok esetén

  • Ha a cellákat az adatbevitel előtt szöveges formátumra állítjuk, akkor a számokat szövegként tárolva már lehet 15 karakter fölé menni.
  • Alternatív megoldásként a szokásos, aposztróffal kezdjük begépelni a számokat, ezzel erőltetve a szöveges formátumot.
  • Szöveges fájlból importáláskor a Szövegből Oszlopok (Text to Columns) eszközt kell használni, és a harmadik lépésben beállítani, hogy az adott oszlop szöveges legyen.
    Szoveges_oszlop
  • PowerQuery import esetén szintén van lehetőség az oszlop típusát szövegesre állítani

Mi van, ha számolni szeretnénk ekkora számokkal?

Ha bemenő adataink és/vagy képleteink eredményei meghaladják a 15 egész számjegyet, akkor az Excel csak szövegként tudja őket megjeleníteni. Ha számként akarjuk őket kezelni, akkor el kell engednünk az Excel-t, és más megoldás irányába nézni. Léteznek matematikai célprogramok, mint a MathLab, de olvastam egy XNumbers nevű Excel Add-In-ről is, ami orbitális számokkal is jól működik. Nem próbáltam.

Excel függvények és a hosszú számok

Nos, ha az első részt megoldottuk, és szövegesként tároljuk és jelenítjük meg a hosszú számokat (Vonalkód, számlaszám, bármilyen azonosító), akkor még előttünk áll az, hogy ezen oszlopban keresni kell, vagy feltételes összesítés során kritériumban használni.

Gyorsan összeállítottam egy teszt munkafüzetet, ahol a két leggyakoribb keresőfüggvényt (FKERES/VLOKUP és MATCH/HOL.VAN), illetve a feltétel szerinti összesítéshez a SZUMHA/SUMIF, SZUMHATÖBB/SUMIFS és barátai, illetve az adatbázis függvényeket (AB.SZUM/ DSUM) vizsgáltam.

Azt feltételeztem, hogy ha az Excel 15 karakter felett nullákat tesz az eredeti számjegyek helyett, akkor két karaktersort, ami csak a 15. karakter után tér el egymástól, azonosnak fog tekinteni.
15-karakterig-azonos

Eredményül azt kaptam, hogy a kereső függvények nem tekintik azonosnak őket, de az összes feltételes összesítő és adatbázis függvény IGEN!!!

Megoldás függvények esetén

Ha a számjegyeken kívül bármilyen nem numerikus karaktert tartalmaz  a cella, akkor meggyógyul a dolog. Tehát az elejére vagy a végére egy betűt fűzve már rendben leszünk.
Én egy “S_” prefixet választottam.
alfanumerikus-kod

Munkafüzet letöltése

15_karakternel_hosszabb_szamok

Mindez Videón

Vélemény, hozzászólás?