Az adatok minden vállalkozás számára aranyat érnek. Általában az adatok fájlokba vannak rendezve, amelyeket még értelmezni kell. A Microsoft Excel egyike azoknak az eszközöknek, amelyek segítenek ebben.
Herpai Levente, az OTP elemzési tanácsadója összegyűjtötte az Excel tíz egyszerű, de nagyon hasznos trükkjét, amelyek időt és pénzt takarítanak meg Neked.
#1. Oszlopokba rendezés
Tegyük fel, hogy adatokat gyűjtesz ki a Google űrlapokból. Az adatok CSV fájlformátumban vannak rendezve, és nem kerülnek át automatikusan oszlopokba. Vagyis az Excel cellája egy jó hosszú karaktersorozatnak tűnik, ahol a szöveget vesszők, perjelek vagy pontosvesszők választják el egymástól.
Ilyen formátumú adatokkal gyakorlatilag lehetetlen dolgozni. Ahhoz, hogy bármilyen számításokat végezhessünk velük, oszlopokra kell őket bontani.
Hogyan csináld?
Kattints az "Adatok" fülre, és válaszd ki a „Szövegből oszlopok” (Text to Columns) lehetőséget. Megjelenik egy ablak, amelyben három lépést kell végrehajtani:
1. Válaszd ki az adatok formátumát. Az első lépés ablakában jelöld be a "Tagolt" opciót és kattints a "Tovább" gombra.
2. Válaszd ki az elválasztó formátumát. A felkínált lehetőségek közül (tabulátor, pontosvessző, vessző, szóköz vagy egyéb) válaszd ki valamelyiket – például a vesszőt. Utána kattints a "Tovább" gombra.
3. Válaszd ki az oszlop formátumát - általános, szöveg vagy dátum. Az új oszlopnézet azonnal megjelenik az ablak megtekintőjében. Kattints a Befejezés gombra. A rendszer figyelmeztet, hogy az információk újjáépülnek - és néhány másodperc múlva már oszlopokba rendezett táblázatot kapunk.
#2. Oszlopok összefűzése
Tegyünk fel, hogy van egy három oszlopból álló táblázatom: ügyfélkód, keresztnév és vezetéknév. De sokkal kényelmesebb ezeket az információkat egyben látni.
Hogyan csináld?
Az „Összefűz” (CONCATENATE) képlet lehetővé tesz több érték kombinálását egyetlen sorban. Például egyesítenünk kell a B2 és a C2 cellákat. Ezért:
1. Írd be a képletet a cellába – például: =ÖSSZEFŰZ(B2; C2).
2. Az értékek közé írd be a ;" " karaktereket. Ezt követően a képletünk a következő lesz: =ÖSSZEFŰZ(B2; " "; C2).
Alternatív módszer: az & szimbólum segítségével is össze tudjuk fűzni az értékeket. Az A3, B3 és C3 cellák egyesítéséhez a következő egyenletet kell használnunk - például: =A3&" "&B3&" "&C3. Szóköz helyett kötőjelet, gondolatjelet vagy más írásjeleket is használhatunk.
#3. Hivatkozások cellákra és tartományokra ugyanazon Excel munkafüzeten belül
Mindig azt tanácsolom, hogy ne használjunk beillesztett értékeket a munkánk során, hiszen már 10 perc elteltével valószínűleg nem fogunk emlékezni arra, hogy honnan származik az egyik vagy a másik szám és érték. Sokkal kényelmesebb és praktikusabb egy másik Excel lapra mutató hivatkozást használni. Így mindig megtalálod az eredeti értéket, és könnyen ellenőrizheted az összes értéket.
Hogyan csináld?
1. Írd be az "=" értéket abba a cellába, amelybe be akarod szúrni a linket.
2. Lépj át egy másik lapra, kattints a szükséges cellára. Ezzel az Excel munkafüzet másik lapjának bármely értékére fogsz hivatkozni. Nyomd meg az Entert.
3. Az érték átkerül az eredeti cellába. A képleted valahogy így fog kinézni: =Munka1!C1
A Képletek – Elődök mutatása funkció pedig segít, hogy egy adott képlet forrásait könnyebben visszafejtsük.
#4. Összekapcsolás más Excel munkafüzetekkel
Az Excel nemcsak egy munkafüzeten belüli, hanem más Excel munkafüzetekre mutató hivatkozásokat is könnyedén tud feldolgozni. Például gyakran más fájlokban lévő adatokat kell használnom, miközben a saját fájlomon belüli értékeknek frissülnie kell.
Hogyan csináld?
1. Írd be az "=" értéket abba a cellába, amelybe be akarod szúrni a linket.
2. Lépj át egy másik munkafüzetbe, kattints a szükséges cellára. Ezzel egy másik Excel munkafüzet bármely értékére fogsz hivatkozni. Nyomd meg az Entert.
3. Az érték átkerül az eredeti cellába. A képleted valahogy így fog kinézni: =[Munkafüzet1.xlsx]Munka1!C1
Ebben az esetben a külső hivatkozásokat az "Adatok" > "Lekérdezések és kapcsolatok" > "Hivatkozások szerkesztése" eszköztár speciális menüjében tudod kezelni.
A megnyíló menü megjeleníti a lapban hivatkozott összes fájlt. Ebben a menüben megváltoztathatod a fájl elérési útját, frissítheted az összes hivatkozást, vagy megszakíthatod a fájlra mutató hivatkozást, ami automatikusan a fájlra mutató összes hivatkozást értékekké alakítja át.
Az Excel maga is javasolni szokta, amikor az értékek frissíthetők.
Ha a másik fájl adatai megváltoztak, az oldal tetején egy figyelmeztetés jelenik meg a következő formátumban: „Külső forrásokra mutató hivatkozások vannak, amelyek frissíthetők. Frissítés? " Kattints az „Igen” gombra, és a munkafüzetben található összes link automatikusan frissülni fog. De ez a frissítés manuálisan, hivatkozásonként is elvégezhető.
Kapcsolódó kurzusok:
#5. A Google Táblázatok lekérdezése
A start up vállalkozások és a kisvállalkozások gyakran használják a Google Táblázatokat. Ezek a fájlok valós időben gyűjtik a Google Analytics vagy más szolgáltatások adatait, és a cégen belüli csapat több tagjának van online hozzáférése hozzájuk.
Fontos azonban, hogy legyen egy törzsfájlod az Excelben – így több vizualizációs lehetőséged lesz, biztosan nem veszíted el az egyes értékeket, és megvéded az adataidat az illetéktelen hozzáféréstől is.
Hogyan csináld?
Ahhoz, hogy megszerezd az online fájlból származó adatokat, a következőkre van szükséged:
1. Állítsd be a hozzáférést a Google Táblázatok fájlhoz - "Mindenkinek, akinek van linkje".
2. Másold ki a Google Táblázatokra mutató linket, és kattints az "Adatok" fülre.
3. Kattints az Excel fájl felső paneljének bal oldalán található "Adatok beolvasása" blokkra. Egy lista fog megjelenni mindazokkal a lehetséges forrásokkal, amelyekhez az Excel kapcsolódhat. Válaszd ki a „Fájlból”, majd a legördülő menüben a „Munkafüzetből” opciót. Egy új párbeszédablak fog megjelenni.
4. Másold be a Google Táblázatokra mutató hivatkozást a párbeszédpanel alján található "Fájlnév" sorba. Fontos! A link végén lévő "edit"-et írd át az "export" szóra. Kattints a "Megnyitás" gombra. Ez eltarthat majd néhány másodpercig, majd egy újabb párbeszédpanel nyílik meg.
5. Válaszd ki ebben az ablakban a keresett lapot, majd kattints a „Betöltés” gombra.
Ezek az adatok ezek után közvetlen kapcsolatban lesznek a kiválasztott Google Táblázattal. Ha frissíteni akarod az adatokat az Excelben, jobb egérgombbal kattints a táblázatra, majd kattints a „Frissítés” opcióra. Az adatok be fognak frissülni.
Mindennek van azonban néhány korlátja. Nem lehet csatlakozni, ha:
- A Google Táblázatokhoz való hozzáférés korlátozott - a linkre kattintva nem lehet megnyitni.
- A Google Táblázatokban vannak egyesített oszlopok.
- A Google Táblázatokban vannak pivot táblák.
- Vannak benne grafikonok.
#6. Készíts több táblázatból egyet
Tegyük fel, hogy egy mappában több hónapról szóló adataim vannak, amelyek fájlokra vannak osztva (egy hónap - egy fájl). Egyesítenem kellene őket egy fájlba. Például úgy, hogy a „január” és a „február” adatok egy közös táblázatban legyenek.
Hogyan csináld?
1. Lépj át az "Adatok" > „Adatok beolvasása” > "Fájlból" > "Mappából" fülre. A megjelenő ablakban válaszd ki a kívánt mappa elérési útját.
2. Jelöld ki a fájlokat az ablakban megjelenő mappából. Nyomd meg az "Összevonás és betöltés" gombot. Megjelenik az egyesített fájl beállításait tartalmazó ablak.
3. Válassz ki egy példafájlt, amely alapján az információkat rendszerezni fogod. Megmutatjuk a példát, amelyet ugyanabban az ablakban választjuk ki a bal oldalon, majd várunk.
4. Válaszd ki a felajánlott lehetőségek közül, milyen formában töltse be az információkat. Például táblázat formájában. Kattints az "OK" gombra.
Egy táblázatot fogunk kapni, amelyben együtt lesznek a „január” és a „február” hónap adatai. Ha a közös mappához még a „március” hónapot is hozzáadjuk majd, akkor a „Frissítés” opció megnyomásával a márciusi adatok is meg fognak jelenni a táblázatunkban.
#7. Készíts „okos” táblázatot
Egy táblázatos jellegű, egyszerű tartomány önmagában még nem tábla. Az ilyen tábla celláit például nem lehet rendesen hivatkozni. Ahhoz, hogy egy tartomány valódi, „okos” táblává váljon, egy apró lépésre van szükség.
Hogyan csináld?
Menj a „Beszúrás” menü, „Táblázat” opciójára, vagy nyomd meg a "Ctrl + R" gombot a tartomány kiválasztása után. Megjelenik egy új párbeszédpanel, amelyben megerősítjük, hogy táblázatot akarunk létrehozni.
Ahhoz, hogy a táblázatunk rendezettebb és esztétikusabb legyen, válasszunk színformázást a felső panelben, de persze nélküle is maradhatunk.
Az okos táblák egyik előnye, hogy automatikusan bővülhetnek: ha új értékeket adunk hozzá az aljához, a cellák automatikusan hozzáadódnak a táblázathoz. A normál táblázatok ilyet nem tudnak.
#8. Csinálj pivot táblát
Egy okos táblából lehet pivot táblát csinálni – ezzel kényelmesebben és gyorsabban tudunk számolni és megjeleníteni az adatokat.
Hogyan csináld?
1. A "Beszúrás" fülön kattints a "Kimutatás" gombra.
2. Add meg, hogy hová kerüljön a pivot tábla kimutatása: "Új munkalapra" vagy "Meglévő munkalapra". Ha a "Meglévő" lehetőséget választod, meg kell adnod egy cellát – ez lesz a pivot tábla helye.
3. Válogasd ki a mezőket a pivot táblába. A jobb oldalon megjelenik egy panel, amelyen szerepelni fog a mezők listája, amelyek értékeit a pivot tábla megjeleníti majd. Például: "üzletek", "termék", "termékmennyiség", stb.
Ha a pivot tábla forrásának szolgáló okostáblában egy új termék jelenik meg, az automatikusan be fog kerülni a kimutatásba. Ehhez csak frissítened kell a másik táblázatot: kattints a jobb egérgombbal, és válaszd a "Frissítés" opciót.
Ha ugyanezt megtesszük egy normál táblával, akkor a pivot nem fog automatikusan frissülni.
#9. Adj hozzá „okos” szeletelőt
Az Excelben sokan használnak szűrőket, de a szeletelőket és skálákat kevesen ismerik. Arra lehet a legjobban használni őket, hogy a szükséges információkat a lehető legegyértelműbb módon mutassuk ki.
Hogyan csináld?
1. A szeletelőt a pivot táblában tudod létrehozni. Ehhez kattints az „Elemzés” menüpontra, majd ott válaszd ki a „Szeletelő beszúrása” opciót.
2. Válaszd ki a szükséges oszlopokat a szeletelő számára. A lista oszlopai megegyeznek a táblázatban szereplőkkel - be kell jelölni azokat, amelyek kellenek. Ezután megjelenik az interaktív szeletelő.
A szeletelő segít abban, hogy kéznél tartsd a szükséges adatokat. Ha rákattintasz a szeletelőben lévő értékre, akkor a fő táblád azonnal ez alapján lesz leszűrve. Ez gyorsabb és kényelmesebb, mint a hagyományos szűrők.
#10. A SZUMHA (SUMIF) és a nővére, SZUMHATÖBB (SUMIFS)
Például, ha egy termék eladási mennyisége részletesen meg van adva Dátum, Üzlet és Termék szerint - és csak egyetlen egy boltra vagy az összes üzletre vonatkozó értékesítési adatokat kell látnunk, de csak egy adott termékről. Bonyolultnak hangzik, de valójában nem az - itt segít nekünk a SUMIF.
Vannak ennél összetettebb esetek is. Például beazonosítani az eladásokat egy adott dátumra, egy adott áruházra és adott termékre. Ilyen esetben az egyszerű SUMIF képlet már nem fog működni – ehhez a „nővéréhez”, a SUMIFS-hez kell folyamodni segítségért.
Hogyan csináld?
1. Kattints egy üres cellára - például a H5-re. Itt fog majd megjelenni az eredmény.
2. Add meg a szükséges időszakot. Például ezt írjuk be a H2 cellába.
3. Add meg a kívánt termékkódot. Például ezt írjuk be a H3 cellába.
4. Írd be a képletet a H5 cellába = SUMIFS(E2:E48;A2:A48;H2;C2:C48;H3)".
A kapott eredmény egy meghatározott dátum és termék szerinti eladási összeg lesz. A képletben akár sokkal több feltétel is lehet, mint a példánkban – maximum 255.
