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.
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.
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.
- 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.
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.
Munkafüzet letöltése
15_karakternel_hosszabb_szamok
Vélemény, hozzászólás?
Hozzászólás küldéséhez be kell jelentkezni.