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

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. 

#2/1. Dinamikus sorkiemelés bekapcsolása a táblázatban 

Gyakorlatilag nincs olyan táblázat, amelyben egy pénzügyi szakértők adatai kiférnek egy képernyőre. A megfelelő sort pedig nehéz folyamatosan szem előtt tartani. Szűrőkkel történő kijelölésük vagy színnel való kiemelésük pedig kényelmetlen. 

Létezik azonban egy speciális trükk erre a problémára – ez a sorkiemelés. A kiemelés a kurzorral együtt fog mozogni, és az információkat kényelmesebb lesz elemezni és értelmezni. Ezt egyetlen képlettel létre lehet hozni a Feltételes formázásban és egy sablon VBA-makró segítségével. 

A művelet algoritmusa: 

• Válaszd ki a teljes táblázatterületet a fejléc kivételével, 

• a Feltételes formázásban hozz létre egy szabályt a következő képlet szerint: 

• = SOR(A6)=CELLA(„sor”), ahol az A6 az első sor kezdete. 

• Válaszd ki a kitöltés színét. 

Ezután meg kell nyitni a Makrószerkesztőt (Nézet > Makrók), és be kell illeszteni a makrót a mintának megfelelően: 

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 

If Target.Cells.Count > 1 Then Exit Sub 

ActiveCell.Calculate 

End Sub 

Ezzel a színes kiemelés sorról sorra fog mozogni. 

Fontos. Mentsd el a fájlt az .xlsm néven, és vedd figyelembe, hogy a számítógép biztonsági beállításai letilthatják a makrót. 

#2/2. Találj meg képleteket és állandókat

Sokan egy ismeretlen táblázatban azonnal meg akarják érteni azt, hogyan épül fel a számítási logika, meg akarják nézni, hogy hol vannak a képletek a számértékek. Nem ritka, hogy a fájlt nagyon gyorsan, sietve töltik ki és folyamatosan szerkesztik, mi viszont biztosak szeretnénk lenni abban, hogy a következő számításnál véletlenül nem megy félre semmi. Amikor pedig információkat gyűjtünk más alkalmazottaktól, akkor pedig meg szeretnénk győződni arról, hogy senki nem javította ki a képletet és nem módosította a számítást. 

Ezt az ellenőrzést az Excelben is meg lehet tenni. Vegyünk két helyzetet: 

1. szituáció. A képlet helyett érték van 

• Válaszd ki a vizsgált területet (használd hozzá a Shift billentyűt, ha szükséges), 

• majd haladj a következő lépésekkel: F5 (vagy Ctrl + G), majd „Irányított” opció az ablak bal alsó sarkában. Jelöld ki az „Állandókat”. 

• A talált értékeket emeld ki valamilyen színnel. 

2. szituáció. A számérték helyett egy képlet van 

• Válaszd ki a vizsgált területet (használd hozzá a Shift billentyűt, ha szükséges). 

• majd haladj a következő lépésekkel: F5 (vagy Ctrl + G), majd „Irányított” opció az ablak bal alsó sarkában. Jelöld ki a „Képleteket”. 

• A talált értékeket emeld ki valamilyen színnel. 

A fő előnye ennek a módszernek, hogy nagyon gyors és mutatós. Ezután már csak elemezni kell az eltérést mutató cellákat (vagy ki kell találni az új report logikáját). 

Fontos. Ez a technika nem teszi lehetővé, hogy megtaláljuk azokat a képleteket, ahol kézileg módosították azok tartalmát az eredmény korrekciójához. Például, amikor a végleges képlethez hozzáadtak egy számot, vagy a cellában összeadtak két, kézzel bevitt számot. Az Excel ezt is ugyanúgy képletként kezeli, és nem fogja külön kijelölni. Egy pénzügyes számára ennek nagy jelentősége lehet. A megoldás az lehet, hogy jelszóval védjük a cellákat a változtatásoktól (a cikk 10. pontja). 

#2/3. Írj egy hasznos szöveges képletet

Az Excel természetesen eredetileg számításokhoz készült, és nem szövegírásra. Ennek ellenére tud néhány hasznos mondatot készíteni. Erre használhatjuk az ÖSSZEFŰZ funkciót. 

A képlet: 

=ÖSSZEFŰZ(szöveg1, szöveg2,… szöveg n),  (angolul a CONCAT függvény) 

ahol a szöveg 1, szöveg 2 … szöveg n – szavak és kifejezések, számok, hivatkozások a szükséges információkat tartalmazó cellákra. 

A szöveg sorrendjét te magad határozod meg. Több mint 200 szöveges elemet is össze lehet fűzni egymással. Azonban fontos, hogy ne vigyük túlzásba, és ne kísérletezzünk azon, hogy egyetlen képletben leírjuk egy pénzügyi jelentés vagy szakvélemény eredményét. A képletek feltételeit célszerű külön cellában kialakítani, hogy ne terheld őket a sok szöveggel. 

A pénzügyi szakembert ez a funkció akkor menti meg, amikor például be kell írni a fizetési cél szövegét, amelyből rengeteg van. Ezt a jóváhagyott nyilvántartás szerint automatikusan meg lehet tenni – majd egyszerűen bemásolhatjuk a fizetési megbízásba. 

Egy dologra figyelni kell. Ha a dokumentum dátuma külön cellában van, akkor az ÖSSZEFŰZ képletbe be kell ágyazni a SZÖVEG függvényt, ellenkező esetben a dátum csak numerikus formátumban kerül átvitelre. 

Példa a szöveg összefűzésére az Excelben: 

Az Alpha Companyhoz történő fizetés hozzárendelésének képlete (lásd: 5. ábra) így nézhet ki: 

=ÖSSZEFŰZ(“Az “;A3; D2; D3;”-ot tett ki”;SZÖVEG(E3;” éééé.hh.nn”);” dátum szerint”) 

5. ábra. A bevételi tervek teljesítésének a táblázata. 

Fontos. A szöveges és speciális karakteres értékeket idézőjelek közé kell tenni, a számokat és a cellahivatkozásokat nem. Amikor a függvényt a Képletvarázslón keresztül készíted, ne felejtsd el pontosvesszővel elválasztani az értékeket, és figyelj a szóközökre is!