5 Excel funkció, amivel álmaid munkáját megszerezheted

A táblázatkezelő szoftverek közül a legszélesebb körben használt a Microsoft Excel. Több mint 800 millió felhasználóval vezeti a listát, a Google Sheets 180 millió felhasználójával szemben.

Ha már olyan pozícióra pályázol, ahol komolyan szükséged lesz a táblázatkezelőkre, akkor nagy valószínűséggel az Excel-t kell majd használnod.

Vannak olyan funkciók az Excel-ben, amelyek nélkülözhetetlenek.

Az interjúk során előfordulhat, hogy egy teszt feladatot kell megoldanod. Ilyen esetben biztosan használnod kell majd a következő funkciók közül néhányat.

Melyek lennének ezek?

FKERES (VLOOKUP) függvény

Ezt a függvényt fogod használni, ha egy táblázatban vagy a sorban szereplő dolgokat kell megkeresni egy másik tartományban.

=FKERES(amit_keresel, ahol_keresed, oszlop_száma_amiből_vissza_akarod_kapni_az_értéket, [közelítő_vagy_pontos_érték])

Ahol a paraméterek:

  • amit_keresel: Az érték, ami alapján szeretnél keresni egy másik, kijelölt tábla első oszlopában.
  • ahol_keresed: Az adatok tartománya, amelyben megtalálható az érték, ami alapján keresel, és a visszaadási érték is megtalálható.
  • oszlop_száma_amiből_vissza_akarod_kapni_az_értéket: A keresési tartomány első oszlopának azokat az értékeket kell tartalmaznia, ahol szerepelhet az amit_keresel paraméter. Innen kell számolni az oszlopokat, és azt megadni, ahonnan visszavárod a találatot.
  • [közelítő_vagy_pontos_érték]: Igaz (1) értéket megadva közelítő egyezést szeretnél kapni, amennyiben nincs pontos találat. Hamis (0) értékkel pedig csak a pontos egyezés esetén kapunk vissza értéket. Ez a paraméter opcionális, amennyiben nem adsz meg semmit, az alapértelmezett az igaz (1), közelítő egyezés.

Az FKERES függvény angol megfelelője a VLOOKUP. A paraméterek és a függvény működése nem változik, csak az elnevezése.

Mutatok neked egy példát az FKERES (VLOOKUP) függvény működésére:

A példa szerint szeretnéd megjeleníteni az anyaglistádban felsorolt tételek árát az elkészített számlán.

A példában szereplő képlet első paramétere (amit_keresel) a ‘C’ oszlopban, a képlet helyével megegyező sorban található, jelen esetben a ‘C7’ cella “TV”.
Második paraméternek (ahol_keresed) az ‘I:J’ oszlopok tartománya lett kijelölve. Az ‘I’ oszlop ebben a tartományban az első oszlop. Mindenképp az első oszlopnak kell tartalmaznia az amit_keresel paraméter értékét.
Ezt követi harmadik paraméterként (oszlop_száma_amiből_vissza_akarod_kapni_az_értéket) a 2-es szám. Ez jelöli a tartományodnak azt az oszlopát, ahonnan vissza szeretnéd kapni a talált értéket.
Utolsó paramétert ([közelítő_vagy_pontos_érték]) mindenképp meg kellett adnod, mivel csak pontos egyezés a megfelelő. Kihagyott paraméter esetén közelítő érték az alapértelmezett.

A függvény használatának vannak korlátozásai:

  • Ha az amit_keresel értéke nem az keresési tartományod első oszlopában van, nem fog működni a függvény.
  • A függvény megadását követően már nem törölhetsz és nem is adhatsz hozzá új oszlopot a keresési tartományodhoz, különben rossz eredményt fogsz kapni.
  • Az FKERES (VLOOKUP) függvény csak az első találatig keres, a többit figyelmen kívül hagyja.
  • A [közelítő_vagy_pontos_érték] paraméter alapértelmezett értéke a közelítő érték, amennyiben nem adsz meg semmit.
  • A függvény nem érzékeny a kis- és nagybetűkre, nem különbözteti meg őket egymástól.

Sor, oszlop rögzítése

Nagy kiterjedésű tartományoknál szükséges rögzítened a sorokat és / vagy oszlopokat. Ez segíti a könnyebb áttekinthetőséget az adatok használata közben.

A következő feladatok gyakorlásához az Excel táblát le tudod tölteni innen.

Első sor rögzítése

Van lehetőséged a táblázatod első sorát rögzíteni. Ehhez a menüből válaszd a Nézet -> Panelek rögzítése -> Felső sor rögzítése (View -> Freeze Panes -> Freeze Top Row) menüpontot.

Freeze Top Row

A gomb hatására a munkafüzet legelső sorát rögzíti az Excel. Görgetés hatására a második sortól minden mozog, az első sor azonban folyamatosan látszani fog. A lenti képen is láthatod, hogy az első sort már a 14. követi.

Top row freezed

Első oszlop rögzítése

Microsoft háza táján gondoltak arra is, hogy a táblázat lehet szélességében is óriási, nem csak függőleges irányba. Ezért tudod rögzíteni az első oszlopot is. A menüben válaszd a Nézet -> Panelek rögzítése -> Első oszlop rögzítése (View -> Freeze Panes -> Freeze First Column) menüpontot.

Kiválasztás után a munkafüzeted első oszlopa oldal irányú mozgás után is látszani fog.

Ahogy a példa képen is láthatod, az ‘A’ oszlopot az ‘F’ oszlop követi az oldalirányú mozgás után.

Ablaktáblák rögzítése

A korábbi példa táblákat kiegészítettem egy új sorral: Megrendelések. Erre azért volt szükség, hogy jobban meg tudjam mutatni neked az ablaktábla rögzítése funkciót.

A módosított munkafüzeten az első sor rögzítése után a teljes táblázatod mozogna görgetés hatására, csak a “Megrendelések” szöveg látszana fixen.

Most már jól jön, hogy nem csak az első sort vagy első oszlopot tudod rögzíteni. Ezt már meg tudod tenni bármely sornál és bármely oszlopnál. Nagyon fontos, hogy pontosan arra a cellára állj rá, amelytől felfelé a sorokat és tőle balra az oszlopokat szeretnéd rögzíteni.

A példa táblázatunkban nekünk az első 3 oszlop fontos, és a fejléc (ami már a munkafüzet 2. sora).
Legyen az aktív cella a ‘D3’. Ezt követően a menüből válaszd ki a Nézet -> Panelek rögzítése -> Ablaktábla rögzítése (View -> Freeze Panes -> Freeze Panes) gombot.

Már látható is az eredmény:

A munkafüzet 2. sora folyamatosan látszik (ez a táblázatunk 1. sora). Az oszlopok közül pedig az ‘A’, ‘B’, ‘C’ oszlopok szintén folyamatosan látszanak.

Rögzítés feloldása

Ha már nincs szükséged arra, hogy a munkafüzeted bármely sorát és / vagy oszlopát rögzítsd, akkor ezt vissza is tudod állítani.
Nincs más teendőd, mint kiválasztani a Nézet -> Panelek rögzítése -> Ablaktábla feloldása (View -> Freeze Panes -> Unfreeze Panes) lehetőséget.

Ahogy már megszoktad: a gomb megnyomását követően azonnal látható az eredmény.

Most már a görgetés hatására minden sorod és oszlopod mozogni fog.

Oszlop szöveggé alakítása

Egy táblázatban lehetnek olyan adatok, amelyek több információt is tartalmaznak egyszerre. Ilyenek lehetnek:

  • raktár lokációinak listája: külön tudod szedni a raktárat, a raktáron belül a polcsort, a polcot, …
  • számlák sorszámainak listája: szeparálni lehet a számla sorszámát számlatömbre, évre, hónapra, sorszámra, …
  • jól megtervezett azonosítója az anyagok listájának: anyagcsoportra, részcsoportra, vagy még tovább lehet bontani,
  • teljes név: meg tudod határozni belőle a keresztnevet és a családi nevet.

Ezeket az oszlopokat ha megfelelően sikerül tagolni, és oszlopokba rendezni, új statisztikákat, kimutatásokat készíthetsz belőlük.

A lenti feladatok gyakorlásához a példa táblázatot letöltheted innen.

Első lépésben ki kell jelölnöd azt az oszlopot, amelyet szeretnél átalakítani. Ezt követően az Adatok -> Szövegből oszlopok (Data -> Text to Columns) gombot kell megnyomnod.

A felugró ablakban két lehetőséged van a tovább lépésre:

  • Tagolt (Delimited)
  • Fix széles (Fixed width)

Nagyon fontos: A szöveget átalakítva az Excel a már meglévő oszlopok tartalmat írja felül. Ezért érdemes új oszlopokat beszúrnod a táblázatodba, amennyiben nem az utolsó oszlop tartalmát fogod átalakítani. Természetesen az Excel rákérdez, hogy biztosan szeretnéd felülírni a már meglévő tartalmad. Itt még van lehetőséged visszalépni, és beszúrni a kimard üres oszlopokat.

Tagolt

Ebben az esetben tartalmaz valamilyen karaktert minden szöveg, amely alapján szeretnéd több oszloppá bontani.

A leggyakrabban használt ilyen határoló karakterek a szóköz, tabulátor, pontosvessző, vessző.

A példa táblázat alapján első körben a Tagolt lehetőséget válaszd ki, ugyanis a számla előtagja után fogod leválasztani a többi adattól.

Mivel a számla előtagját a “-” (kötőjel) követi, és ez nem szerepel a kiválasztható karakterek között, így az Egyéb lehetőséget választva csak írj be egy kötőjelet.

Az alapértelmezett lehetőség:

  • Tabulátor (Tab)
  • Pontosvessző (Semicolon)
  • Vessző (Comma)
  • Szóköz (Space)
  • Egyéb (Other)

A Megtekintés (Data preview) már meg is mutatja neked, milyen részekre fogja bontani az Excel a szövegeket.

A Tovább (Next) gombra kattintva ki tudod választani az új oszlopok adattípusát.

Az alsó mezőben válaszd ki az első oszlopot, ami Szöveg (Text) típusú legyen.

Itt már csak a Befejezés (Finish) gombot kell megnyomnod, és meg is kapod az új oszlopod.

A következő oszlopot szintén egy határoló karakter mentén fogod tagolni. Ehhez újfent válaszd a Tagolt (Delimited) opciót, majd a Tovább (Next) gombot.

Az évszámot a “/” perjel követi, így ez lesz most a karakterünk az elválasztáshoz. Amire most figyelned kell, hogy az új oszlopod adatkészletét helyesen kell beállítani.

A számla sorszámának “/” jelét követően 8 darab szám található. Ennek a 8 számnak 1. és 2. karaktere a hónap száma, 3. és 4. karaktere a hónap napja, az utolsó 4 karakter pedig egy sorszám. Az hónapok, napok és sorszámok előtt a vezető nullák is megtalálhatóak.

A fenti szempontok miatt a Szöveg (Text) típust kell választani, hogy a számok előtti nulla megmaradjon.

Nézd meg, milyen különbség van a két adattípus között.

Szöveg (Text)

Általános (General)

A következő fejezetnél lesz nagyon fontos, hány karakterből áll az adatod.

Fix széles (Fixed width)

A példában olyan adat következik, amiben nincs elhatároló karakter. Ilyen esetben a karakterek száma alapján tudod szétszedni az információt.

Jelöld ki az új ‘C’ oszlopod. Ez tartalmazza azokat a szövegeket, amelyeket valamely karakter után kell bontanod.

Az Adatok -> Szövegből oszlopok (Data -> Text to columns) gomb megnyomása után válaszd a Fix széles (Fixed width) lehetőséget.

A Következő (Next) gomb hatására a korábbinál egyszerűbb panelt láthatsz.

Az alsó területen láthatsz egy skálát, az segít neked, hogy a megfelelő karakter után helyezd el az oszlophatárokat, ez mutatja a karakterke számát a szövegben.

A felső területen segítséget kapsz a nyilak elhelyezéséhez:

  • Törésvonal KÉSZÍTÉSÉHEZ kattintson a kívánt helyen. (To CREATE a break line, click at the desired position.)
  • Törésvonal TÖRLÉSÉHEZ kattintson kétszer a vonalon. (To DELETE a break line, double click on the line.)
  • Törésvonal ÁTHELYEZÉSÉHEZ kattintson és húzza el a kívánt helyre. (To MOVE a break line, click and drag it.

Helyezd el a törésvonalakat a 2. és 4. karakter után. Ezek lesznek a nap és hónap.

A Tovább (Next) gomb hatására újra az a felület fogad, ahol az adatkészletet tudod beállítani. Mivel minden új oszlop szám lesz, ezért maradhat az Általános (General) típus. Már csak a Befejezés (Finish) van hátra.

Az első sor fölé beilleszthetsz egy új sort, ahogy a képen látod. Így már könnyen meg tudod különböztetni egymástól az információt. Készíthetsz új kimutatásokat, és elemzéseket.

Ismétlődések eltávolítása

Az elemzéseid eredményét nagyban tudják torzítani azok az elemek, amelyek többször is szerepelnek egy adott táblázatban.

Erre nyújt megoldást a Ismétlődések eltávolítása (Remove Duplicates).

Ehhez a témakörhöz is letöltheted a példa táblázatot innen.

A példa táblázatban már szemmel látható az ismétlődés a kijelölt helyen.

A 8-as számú dolgozó 2020.12.02-én két alkalommal is kilépett. Valószínűleg rosszul húzta le a kártyáját. Az ilyen értékeket kell kiszűrnöd, hogy megfelelő statisztikát tudj készíteni.

A menüpont, amire szükséged lesz az Adatok -> Ismétlődések eltávolítása (Data -> Remove duplicates) helyen találod.
Használatához elegendő, ha az aktív cellád a táblázatban van, nem kell kijelölni sem az oszlopokat, sem a táblázatot.

A gombra kattintás után meg kell adnod, hogy a táblázatod első sora fejléc vagy sem.

Ha be van pipálva Az adatok fejlécet tartalmaznak (My data has headers), akkor a fenti mint alapján az első sor értékei lesznek a fejléc értékek. Ezek alapján tudod majd kiválasztani, mely oszlopokban vizsgálja az ismétlődéseket az Excel.

Az Oszlopok (Columns) területen azok az oszlopok vannak kijelölve, amelyekben figyelembe veszi az Excel az ismétlődéseket. Ezen terület fölött még található két gomb. Az összes kijelölése (Select All) gomb minden oszlopot ki fog jelölni. Ezzel ellentétes feladatot lát el a Minden kijelölés megszüntetése (Unselect All) lehetőség, amely minden oszlop elől kiveszi a pipát, így egyik sem lesz kijelölve.

Az OK gomb megnyomása előtt győződj meg, hogy minden oszlop ki legyen jelölve. Ahogy elvégzi az ismétlődéseket eltávolítja a funkció, egy figyelmeztető üzenetet kapsz vissza. Ez tartalmazza hány ismétlődést távolított el, és hány egyedi érték maradt.

Ebben az esetben 67 ismétlődő elemet talált, és 132 egyedi értéket. Az eredeti táblázatod mostantól 199 helyett 132 elemű.

Most nézzük meg mi történne ha csak az irány oszlopot jelöljük ki az ismétlődések vizsgálatára.

Csak az irány oszlopra figyelni az ismétlődéseket 2 egyedi sort fogunk kapni a végén. Ez azért van, mert az irány oszlop két különböző értéket tartalmaz: ki, be.

Az ismétlődések eltávolítása előtt azt tartsd szem előtt, hogy a megfelelő oszlopokat válaszd ki. Figyelembe kell venned, hogy milyen érték alapján szeretnéd elkészíteni a kimutatásod, diagrammod.

Cellák egyesítése

Törekedj arra, hogy igényes munkát adj ki a kezeid közül. Ez fogja meghatározni megítélésed leendő főnököd számára. Igyekezz minden esetben többet és jobbat adni annál, amit elvárnak tőled.
Az általad készített táblázatban ne csak arra figyelj, hogy az adatok szerepeljenek benne, amit kérnek tőled, hanem legyen áttekinthető is.

Egy apróság a sok funkció között a Cellaegyesítés. Ezzel áttekinthetővé tudod tenni a táblázatod, fejléceket tudsz hozzáadni, ami átöleli az adataid.

Használatához először ki kell jelölnöd az érintett cellákat. Ha ezzel megvagy, már csak a Kezdőlap -> Cellaegyesítés (Home -> Merge & Center) gombot kell megnyomnod.

Nagyon fontos, hogy a kijelölt tartomány csak egy cella értékét fogja megtartani. Ez lesz a bal legfelső cella értéke, a többi törlésre kerül.

A megfelelő tagolással a fejléceket össze tudod csoportosítani, hogy áttekinthetőbb legyen.

Cellák egyesítése után már nem szeretnéd egyben tartani őket, akkor van lehetőséged az egyesített cellákat újra különálló cellákra bontani. Ehhez kattints rá arra az egyesített cellára, amit bontani szeretnél és kattints rá újra a Kezdőlap -> Cellaegyesítés (Home -> Merge & Center) gomra.

Vélemény, hozzászólás?

Hogyan tovább?

Blog feliratkozás

[xyz-ihs snippet="blog-feliratkozas"]

Regisztráció

Iratkozz fel az új Blog bejegyzésekre

Minden szombaton új bejegyzés érhető el az oldalon.

Ne maradj le, iratkozz fel!

Miért jó, ha feliratkozol?