A 10 legjobb Excel-trükk, pénzügyi szekértőktől/1

Nagyobb sebesség, kevesebb adminisztráció!

Az Excel folyamatosan fejlődik: 35 év alatt mintegy 500 különféle képlet gyűlt össze a tudástárában, így nem meglepő, hogy a program a pénzügyi szakemberek egyik kedvenc eszköze. A Power Query beállítások és Power Pivot miatt pedig az Excel akár „végtelenül fejlettnek” is nevezhető.

Megnéztünk 10 egyszerű Excel-trükköt, amelyek felgyorsítják és leegyszerűsítik a pénzügyesek és más szakemberek munkáját. Ezek a funkciók segítenek:

– észrevenni és feltárni a lehetséges számítási hibákat,

– olvasható megjelenést és elemzési vektort adni a hatalmas méretű táblázatoknak,

– kiemelni a szükséges információkat,

– elkerülni, hogy folyamatosan a naptárat kelljen figyelni, vagy épp hétvégén dolgozni, és

– megvédeni az adatokat is.

#1/1. Az Excel „Error Trapping” funkciója, avagy a hibák beazonosítása

Az iskolából mindenki emlékszik a szabályra: „nullával nem lehet osztani”. Az Excel is így gondolja, de a gyakorlatban előfordulhat, hogy mégis így kell tennünk. Egy jellemző példa: egy bizonyos forrásból nem volt betervezve bevétel, mégis lett, és ki kellene számolni a terv teljesülését százalékban. Osztásnál minden egyes sorban az eredmény mezőben a #ZÉRÓOSZTÓ!-t (#DIV/0!) kapjuk (lásd 1. ábra).

1. ábra: amikor előjön a ZÉRÓOSZTÓ! hibája 

Emiatt lehet, hogy az Excel a végeredményt sem fogja kiszámolni. Sőt, előfordulhat, hogy #ZÉRÓOSZTÓ! továbbmegy, és „megszakítja” az összes számítást a láncban. Ezért a táblázatot folyamatosan kézileg kell törölgetni. Pontosan ilyen esetekre van kitalálva a HAHIBA (IFERROR) függvény. 

A képlet: 

=HAHIBA (érték ; érték_hiba_esetén), ahol 

  • az érték az a megnevezés (szöveg, szám vagy cella hivatkozás), amelyben a hibákat észlelni kell 
  • érték_hiba_esetén az érték, kifejezés vagy hivatkozás, amelyet hiba esetén meg szeretnénk jeleníteni 

Az elődeivel (EOSH, END) ellentétben ez a funkció rövid, és minden csúnya kódot (#NULLA!, #ÉRTÉK! #HIV! #ZÉRÓOSZTÓ! #SZÁM! #NÉV! #HIÁNYZIK!) elkap és lecserél. Példánkban „0”-ra cseréli őket. 

A HAHIBA használata előtt gondosan ellenőrizni kell azokat a számítási képleteket, amelyek hibakódot generálhatnak. Főleg, ha a képlet összetett. Ha a táblázatban számítások vannak, akkor jobb, ha nem helyettesítjük a hibakódokat szöveges értékkel. Talán jobban néz ki, de megnehezítheti a későbbi elemzést. 

Fontos! Ne használd mindenhol a HAHIBA függvényt csak „a biztonság kedvéért”. Például a profit számításánál nem osztunk semmit, és ez a fajta hiba nem is jöhet ki. Biztonságosabb, ha már akkor alkalmazzuk, miután megjelentek a hibakódok, és kielemeztük, hogy mi vezetett el ehhez. Nemhiába nevezik ezt a „hibák elrejtése” funkciójának is. A HAHIBÁNÁL például nem biztos, hogy észreveszed, hogy véletlenül kitöröltél egy sort, amely egy összetett képletben szerepelt. 

2. ábra: példa a HAHIBA (IfError) függvény használatára 

#1/2. Kezdd az egészet elölről, avagy hogyan távolítsuk el a nullát a szemünk elől?

Néha a táblázatban szereplő sok nulla érték (és különösen a tizedesvessző utáni nullák) vizuálisan zavaróak. Előfordul, hogy az ilyen sorokat nem lehet törölni, de néhány másodperc alatt láthatatlanná lehet tenni őket, hogy többé ne vonják el a figyelmünket (lásd a 2. ábrát).

3. ábra. Példa a nullák eltüntetésére a táblázatban 

A képlet: 

  • válaszd ki a területet (használd hozzá a Shift billentyűt, ha szükséges) 
  • kattints jobb egérgombbal Cellaformázás / Szám fül / Egyénire 
  • a Formátumkód mezőbe írd be a következőt: 0;-0;;@ 

Fontos. Légy óvatos egy másik opcióval, ami a „Keresés és csere”. Gyorsabbnak és egyszerűbbnek tűnhet (néhány kattintás), de nem fog megfelelően megbirkózni ezzel a feladattal: el fogja távolítani az összes nullát, így az adatok súlyosan torzulni fognak! 

#1/3. Állítsd össze a saját szabályaidat és feltételeidet

Egy hatalmas táblázatban sokszor nem tudsz kiszúrni dolgokat, nincs, amin megakadhat a szemed. Az Excel feltételes formázása segít vizuálisan jól fogyaszthatóan megjeleníteni az adatokat. Számos lehetőség közül választhatsz: intuitív színkitöltés, hisztogramok, ikonok. 

A műveletek algoritmusa egyszerű: 

  • Válaszd ki a formázási területet (a táblafejléc és általában az Részösszeg/Végösszeg sor nélkül), 
  • majd kattints a Feltételes formázásra („Kezdőlap”), és válaszd ki a megfelelő lehetőséget. 

Használhatsz egy kész opciót, vagy létrehozhatsz saját szabályt. Fontos, hogy ne vigyük túlzásba, és ne változtassuk a táblázatunkat kifestőkönyvvé, mert azzal ellenkező hatást érünk el. Egy-két formázási paraméter elegendő lesz. Ne felejtsd el a szabályoknál „$” jellel rögzíteni a szükséges sort vagy oszlopot. 

A 4. ábra három lehetőséget mutat a tervtől való eltérések vizuális kiemelésére. 

4. ábra. A tervtől való eltérések vizuális kiemelésének különböző verziói. 

Fontos. Légy következetes a formázás többi feltételében (szabályánál): azonnal ellenőrizd, hogy a felső (első) szabály nem fedi-e át a többit!