«Kezdő Excel»
Igaz Ferenc
üzleti elemző, BI Functional Specialist, Norsk Hydro
Kurzusok
Online kurzusok
5 kihagyhatatlan Excel-trükk, pénzügyi szakértőktől
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 VBA-makrók, Power Query beállítások, Power Pivot miatt pedig az Excel akár „végtelenül fejlettnek” is nevezhető.
Összeszedtünk 5 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,
- automatizálni bizonyos folyamatokat, és
- megvédeni az adatokat is!
#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”. Ezt az Excel is így gondolja, de a gyakorlatban előfordulhat, hogy mégis meg kell szegnünk ezt a szabályt. 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).
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ézzel 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!) megtalál és lecserél, a mostani példánkban „0”-ra.
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 – bár 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. Hogyan ne szédüljünk el a nullák tengerétő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. Szerencsére ezeket néhány másodperc alatt láthatatlanná lehet tenni, hogy többé ne vonják el a figyelmünket (lásd a 3. ábrát).
A képlet:
- válaszd ki a tartományt (használd hozzá a Shift billentyűt, ha szükséges)
- kattints jobb egérgombbal a Cellaformázás / Szám fül / Egyéni-re
- 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!
#3. Vizualizációval az átláthatóságért!
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 a 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.
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!
#4. Tölts ki mindent minta szerint!
Az Excel (a 2013-as verziótól kezdve) képes leolvasni az általad végzett műveletek logikáját (bejegyzéseket egy minta alapján), majd egyszerűen és gyorsan megismételni őket újra. A Villámkitöltés funkcióval szimbólumokat, dátumokat, számokat és szavakat nyerhetünk ki a szövegből (esetleges átrendezéssel), összefűzhetjük a szöveget különböző cellákból, vagy szétválaszthatjuk az értékeket – és mindezt összetett képletek írása nélkül. Például azonos formátumba szervezhetünk mobiltelefonszámokat, teljes nevet jeleníthetünk meg, ésatöbbi.
A művelet algoritmusa:
- Adj hozzá egy oszlopot a táblázathoz az eredeti információ mellett (fontos, hogy ezt szigorúan ténylegesen a kiinduló adatok mellé tedd – a következő oszlopban, az adatoktól jobbra),
- kézzel írj be 1-3 különböző példát (fontos, hogy ezek ténylegesen eltérőek legyenek),
- válaszd ki az információval kitöltendő tartományt,
- kattints az Adatokra, majd Villámkitöltés, vagy használd a Ctrl + E billentyűparancsot.
A KÖZÉP, KERESÉS stb. funkciókhoz hasonlóan a Villámkitöltés sem működik elírások és hibák esetén. Az adatok kitöltésében bizonyos egységességre van szükség, kell egy követhető minta. Ezért biztonságosabb, ha a függvényt csak a saját táblázataidban használod.
Amíg a képletek használatánál minden műveletet egyszerre rögzíthetsz, a Villámkitöltésnél fontos a sorrend. Ha nem vagy biztos abban, hogy a táblázat kitöltése egységes szabályok és formátumok szerint történik, ellenőrizd le az azonnali kitöltéssel kapott eredményt és a képletet. Ha többen dolgoznak egy táblázaton, érdemes összehangolni a közös munkát, hogy egységes legyen.
Fontos. Egy képlet mindig működik, és reagál a változásokra, de a Villámkitöltés nem. Új információk megadásakor meg kell ismételni a műveletek algoritmusát.
#5. Védd az adataidat!
A bizalmas információkat tartalmazó fájlokat általában jelszóval védik a pénzügyi szakemberek. Ilyenek például a prémiumokról, a személyzeti változásokról vagy a belső értékelésekről szóló információk. Előfordulhat, hogy a táblázatot véletlenül rossz címzettnek küldöd el, ilyenkor pedig a jelszó a garancia arra, hogy az adatok nem szivárognak ki, és csak az jut hozzájuk, aki jogosult rá.
A táblázat titkosításához kattints a Fájlra, majd Információ menüpont. Válaszd ki a Füzetvédelem opciót, majd a Titkosítás jelszóval lehetőséget. Ezután egy általad meghatározott jelszót kell megadni, és megerősítés után már él is a dokumentum jelszavas védelme.
A munkafájlokban is gyakran meg kell védeni a lapokat a változásoktól. Például egy költségvetés véglegesítése során fontos, hogy senki ne adjon hozzá véletlenül újabb sorokat vagy ne módosítsa a képleteket a költségvetés sablonjában.
Mielőtt megvédenéd a fájlt a nem kívánt változtatásoktól, elő kell készíteni azt.
1. Válaszd ki azokat a tartományokat, cellákat, ahol adatokat adhatsz meg, majd:
Cellaformázás > Védelem > majd töröld a Zárolt jelölőnégyzetet
2. Válaszd ki azokat a tartományokat, cellákat, ahol el szeretnéd rejteni a képleteket, majd:
Cellaformázás > Védelem > majd jelöld ki a Rejtett jelölőnégyzetet
Ezt követően: Véleményezés > Lapvédelem. Majd ezután add meg egy általad meghatározott jelszót. A megerősítés után a védelem érvényes lesz.
Fontos. Az elfelejtett jelszót nem lehet helyreállítani, ezért ennek tudatában alkalmazd a védelmet, és lehetőleg dolgozz ki egy algoritmust a jelszavaid létrehozására.