Telefonszámunk: 1-472-0679

Táblázatok, Adattáblák

Dinamikus névtartomány képlettel

Egy korábbi tananyagban bemutattam a névtartomány lényegét és használatát. Ott az alapfogalmak mellett arról volt szó, hogyan kell egy táblázattá alakított tartományban névtartományt definiálni. A táblázatokon alapuló névtartományok automatikusan követik a táblázat méretének  változásait. Ezt a módszert használhatjuk például akkor, hogy egy legördülő lista adatforrása rendszeresen bővül, […]

Tovább...

Táblázatok használatával járó korlátok

A táblázatok és a velük járó struktúrált hivatkozások használatának lehetősége nagyszerű lehetőséget hozott az Excel 2007 megjelenésekor. Van azonban a tábláztok használatában néhány korlátozás, amiket érdemes tudni. Remélhetőleg idővel, az újabb verziókban ezek többsége megoldódik. Letiltott funkciók Ha a munkafüzet tartalmaz legalább egy táblázattá alakított tartományt, a […]

Tovább...

Kétszintű legördülő lista készítése

Az Excel adatérvényesítés lehetőségét egyre többen megismerik és használják. Ezek közül a leggyakoribb a legördülő lista szabály, amikor előírjuk, hogy a kiválasztott cellákba csak egy előre meghatározott lista elemeit lehessen beírni vagy egy legördülő menüből választani. Ebben az anyagban feltételezem, hogy az olvasó ismeri ezt a lehetőséget, […]

Tovább...

Haladó szűrés sorozat – Névtartományok használata

A haladó szűrő eszközt érdemtelenül kevesen ismerik. Többet érdemelne. Emiatt egy sorozatot indítok a lehetőségeiről. Volt már szó korábban róla, de úgy döntöttem, még egyszer nekifutok. A haladó szűrőt speciális vagy irányított szűrőnek is szokták nevezni. Az autoszűrővel egy lépésben nem megoldható, összetettebb szűrésekhez, illetve a gyakran ismétlődő, akár egyszerűbb szűrésekhez használjuk.

Lényege, hogy a szűrendő táblázaton kívül egy külön tartományban fogalmazzuk meg a szűrési feltételeket, majd a haladó szűrő párbeszédében megadjuk a feltételtartomány helyét,  pár dolgot még beállítunk, és kész!

A haladó szűrő előnyei

  • Bonyolultabb szűrésekre is alkalmas egy lépésben
  • Az eredmény akár azonnal új helyre másolható, akár másik lapra
  • A szűrési feltételeket tároljuk, nem kell folyton megcsinálni, kitalálni
  • Az eredményből kiszűrhetjük a szükségtelen oszlopokat (Projekció)
  • Szűrés közben az oszlopok sorrendjét is módosíthatjuk

A cikksorozat tagjai

Névtartományok használata

A szűrésnél a Listatartományt (List range), a szűrőtartományt (Criteria range), sőt, akár a hova másolja (Copy to) tartományt is megadhatjuk névtartománnyal. Ezzel egyszerűbbé és rugalmasabbá tehetjük a szűrő használatát.

A névtartományok létrehozásával és kezelésével kapcsolatban már volt egy korábbi anyag, amit itt olvashatsz el:

http://excel-bazis.hu/tutorial/dinamikus-nevtartomany-hasznalata

A névtartományok létrehozásával akkor érdemes időt tölteni, ha ugyanazt a szűrést rendszeresen használjuk.

Táblázatok használata

Abban az esetben, amikor az adatforrást (listatartomány) táblázattá alakítottuk, tisztában kell lennünk azzal, hogy a táblázat egyes részeire hogyan hivatkozhatunk. Erről bővebben pedig itt lehet olvasni:

http://excel-bazis.hu/tutorial/strukturalt-hivatkozas-adattablak-hasznalata-eseten

Ebből számunkra az lesz a legfontosabb, hogy a haladó szűrésnél a listatartománynak tartalmaznia kell a fejléc adatokat is. Ha viszont csak a nevét használjuk, akkor az nem tartalmazza a fejlécet. A teljes táblára fejléccel együtt, szabályos, un. struktúrált hivatkozással így kell hivatkozni:

Magyar Excel: Tabla_neve[#Mind]

Angol Excel: Tabla_neve[#All]

halado-szures-nevek-hasznalata

A struktúrált hivatkozást kikerülhetjük, ha a táblázattá alakítást követően kijelöljük a teljes táblát fejléccel együtt, és elnevezzük. Ez a név dinamikusan követni fogja a listatartomány változását, új sorok hozzáadását.

Letölthető munkafüzet

iranyitott-szuro-peldak

Mindez működés közben

  • Névtartományok haladó szűrésnél

Tovább...

Struktúrált hivatkozás adattáblák használata esetén

A cikk megértéséhez tisztában kell lenni a TÁBLÁZAT fogalmával az Excel-ben. Erről részletesen egy korábbi cikkben lehet olvasni: Táblázatok az Excelben

A táblázatok használatának rengeteg előnye van. Ezek közül az egyik, hogy ha új sorokat vagy oszlopokat fűzünk hozzá, automatikusan kiterjeszti a táblázatot. Emiatt Dinamikus névtartományként is kiválóan használható.

Tartományi hivatkozás vs. Struktúrált hivatkozás

A legegyszerűbben ezt egy példán lehet bemutatni. Az alábbi tartomány táblázattá lett alakítva. A táblázat neve: ‘Forgalom

minta-adatok

Hogyan lehetne meghivatkozni az ID oszlop összes adatát?

Tartományi hivatkozással: =$A$2:$A$10

Struktúrált hivatkozással: =Forgalom[ID]

Ugye mennyivel olvashatóbb? Ráadásul nem kell foglalkozni azzal, hogy hány sora van a táblázatnak, mert a fenti hivatkozás mindig a teljes oszlopra fog vonatkozni.

Tehát a struktúrált hivatkozás azt jelenti, hogy a táblázat részeire nevekkel és nem címekkel hivatkozunk.

Minősített vs. nem minősített hivatkozás

Ez csupán annyit jelent, hogy ha a hivatkozás minősített, akkor tartalmazza a táblázat nevét is. Ekkor a képlet nem feltétlenül abban a táblázatban van, amire hivatkozunk, vagy egy táblázatból hivatkozunk egy másik táblázat valamely részére. nyilván ilyenkor meg kell mondani azt, hogy melyik táblázatról van szó.

Pl.: A fenti minta szerinti munkalap H1-es cellájába (ami ugye kívül esik a táblázaton) ki szeretném számolni, hogy hány terméknév van a D oszlopban:

=COUNTA(Forgalom[Termék])

Ha a hivatkozás az aktuális táblázatban van, pl. egy számított oszlop, ami másik, ugyanabban a táblázatban lévő oszlopok adatait használja, akkor a hivatkozáshoz elég az oszlop neve. Ez lesz a nem minősített hivatkozás.

Pl.: A ‘mennyiség’ és az ‘egységár’ oszlopokból szeretnénk számítani az ‘Érték’ oszlopot, közvetlenül a ‘G’ oszlopba:

=[mennyiseg]*[egysegar]

Ezt a G oszlop bármely cellájába beírva, az egész oszlopot kitölti a megfelelő eredménnyel.

Struktúrált hivatkozások bevitele (Bővebben a videóban)

  • Egérrel kattintgatva a megfelelő helyeken, vagy
  • Billentyűzetről gépelve, amihez azért jelentős támogatást kapunk. Vannak olyan hivatkozások, amelyeket csak billentyűzettel lehet megfelelően bevinni.

Speciális hivatkozások

A következő példákban a táblázat neve: ‘Forgalom

Hivatkozott rész Angol Excel Magyar Excel
A teljes táblázat az oszlopfejlécekkel, adatokkal és
összesítésekkel (ha van) együtt.
Forgalom[#All] Forgalom[#Mind]
Csak az adatok fejléc nélkül Forgalom[#Data] Forgalom[#Adatok]
Csak a táblázat fejlécsora Forgalom[Header] Forgalom[#Fejlécek]
Csak az ‘Összeg’ oszlop fejléccel együtt Forgalom[[#All],[Összeg]] Forgalom[[#Mind],[Összeg]]
Több, összefüggő oszlop =Forgalom[[Termék]:[mennyiseg]] =Forgalom[[Termék]:[mennyiseg]]

Ha a hivatkozásban csak a táblázat nevét használjuk, az megfelel a [#Data] használatának.

tehát az =Forgalom ugyanaz, mint az =Forgalom[#Data]

Hivatkozás az aktuális sorban

Ez Excel 2007 esetén a [#This Row] magyar verzióban [#Ez a sor] jelölővel, Excel 2010-től pedig a @ jellel történik.

Ezt a jelölőt az Én tapasztalataim szerint nem kell használni, attól még egy számított mezőnél felfogja, hogy az aktuális sorban kell dolgoznia, de vannak esetek, amikor ez lényeges lesz, például gördülő összeg számításakor, ha ilyen hivatkozást szeretnénk használni.

Aktuális sorban egy adott
oszlop cellája
Angol Excel Magyar Excel
Excel 2007 =Forgalom[[#This Row];[mennyiseg]] =Forgalom[[#Ez a sor];[mennyiseg]]
Excel 2010-től =[@mennyiseg] =[@mennyiseg]

Figyelem! Visszafelé nem kompatibilis! Az Excel 2007 nem fogja érteni a @ jelet!

Táblázat visszaalakítása tartománnyá. Mi lesz a hivatkozásokkal?

A struktúrált hivatkozások átalakulnak tartományi hivatkozássá. Ha a tartományt újra táblázattá alakítjuk, akkor ezt már nem követi vissza.

A kapcsolódó videóban mindez bővebben, néhány további érdekességgel, beállítási lehetőséggel megtekinthető.

Letölthető munkafüzet a gyakorláshoz: strukturalt_hivatkozas_nyers

  • Struktúrált hivatkozások alapjai

Tovább...

Az aktív cella táblázatban van-e, és melyikben? – VBA

A makrófejlesztés egyik fontos része a “Bolond állóság” (FoolProof) biztosítása. Emiatt egy igényesebb VBA kód hibaellenőrzéseket is tartalmaz.

Az egyik ilyen eset lehet, hogy ellenőrizzük, az aktív cella egy korábban már táblázattá (Lista) alakított tartományban van-e?

Íme egy erre szolgáló függvény

1

Úgy működik, hogy ha az aktív cella benne áll egy táblázatban (listában), akkor létezik a .name tulajdonsága. Különben hibát ad, amit kezelünk.

A függvény a táblázat nevét, vagy üres stringet ad vissza.

Tovább...

Táblázatok az Excelben

Ezt a funkciót az Excel 2003-ban mutatták be, ott Lista néven működött. Az Excel 2007-től fölfelé átnevezték Táblázat (Table) funkcióra, ami a magyar Excel esetében kissé zavaró, mert a programot magát “Táblázatkezelő”-nek nevezzük.

Emiatt ebben az anyagban Adattábla nevet fogunk használni.

Sok előnye és kevés hátránya van a használatának, ezeket nézzük most meg.

Adattábla  definiálása

Az adattábla egy Fejléccel, és adatsorokkal rendelkező, elkülöníthető tartomány. Elég gyakran egy munkalapon egy ilyen van, de nem biztos, hogy adattáblaként használjuk. Egy tartományt adattáblaként szeretnénk használni, tegyük a következőket:

  1. Jelöljük ki az átalakítandó tartományt (Ctrl+A)
  2. Alakítsuk táblázattá:  Kezdőlap->Formázás Táblázatként vagy Beszúrás->Táblázat
  3. Adjuk meg,a tartományt, és állítsuk be, hogy az első sort fejlécként kezelje-e
    Excel-tablazat-tartomany-megadasa
  4. Választhatunk a rengeteg beépített formátumból

Amikor befejeztük a folyamatot, a tartományunk átalakul, és valószínűleg valami hasonlót látunk

Excel-tablazat-eredmeny

Az adattábla extra funkciói

  1. A fejléc átalakul
    Megjelenik az autoszűrő, és egyben rögzül is a fejléc, azaz lefelé gördítéskor nem tűnik el.
  2. Egyszerűen lehet sorokat, oszlopokat és az egész adattáblát kijelölni
    Az oszlop vagy sor elején, illetve a sarkán megváltozik az egérmutató, és csak az adattábla tartományában jelöl ki annak megfelelően. Ez képletek bevitelekor is működik.
    Adattabla-sorainak-es-oszlopainak-kijeloleseTeljes-Adattabla-kijelolese
  3. Karbantartja a táblát
    Az új sorokat és oszlopokat automatikusan az Adattábla részévé teszi, kiterjeszti, megtartva a beállított formátumot akkor is, ha sorokat vagy oszlopokat szúrunk be vagy törlünk.  (Hacsak ezt ki nem kapcsoljuk az automatikus javítási beállításokban.)
  4. Képletek automatikus kitöltése
    A táblázaton belül egy új oszlopban elég egy cellába megadni a képletet, az automatikusan kitöltődik az egész oszlopra. (Hacsak ezt ki nem kapcsoljuk az automatikus javítási beállításokban.)Table-autocorrect
    Excel beállítások->Nyelvi Ellenőrzés (Proofing)->Atomatikus javítási beállítások
  5. Rugalmas adatforrás (legfontosabb)
    Ha egy Grafikont vagy akár PIVOT táblát egy adattáblára alapozunk, akkor azok követik a forrás, azaz az adattábla méretének változásait, az esetleg hozzá jött plusz sorokat frissítés után automatikusan megjelenítik.
  6. Képletek megjegyzése
    Ha kiürítjük  atáblázatot, azaz minden sorát töröljük a fejléc kivételével, a bene lévő képletekre akkor is emlékezni fog, és az eéső sor felvitelekor újra ott lesznek. Ha valaki kíváncsi, hogy miért, akkor olvassa el ezt a cikket.

Az adattábla saját szalagja és lehetőségei

Ha egy tartományt adattáblaként definiáltunk, akkor bármely cellájára kattintva megjelenik egy új menüpont a szalag felett (Táblázat Eszközök / Table Tools). Ezt kiválasztva dolgozhatunk az adattábla saját szalagján. A legtöbb beállítás magáért beszél, ezért most nem térünk ki rá részletesen.

Tablazat-szalag

Az adattábla neve – mérete

Tabla-neve-meretezes

Az adattábla nevét érdemes megadni, hogy beszédesebb neve legyen az alapértelmezettnél. A táblázat átméretezése (amennyiben automatikusan nem teszi meg) is lehetséges, ilyenkor egyszerűen csak megadjuk az új tartományt.

Visszaalakítás tartománnyá

Bizonyos funkciók nem működnek akkor, amikor egy tartományt adattáblaként használunk. Ebben az esetben a Tools csoport “Átalakítás tartománnyá (Convert to Range) parancsát használhatjuk. A tartomány megőrzi az adattábla formátumát (pl csíkok) de már nem hordozza tovább az elérhető funkciókat.

Összegsor (Total Row)

ha bekapcsoljuk, akkor a táblázat alján megjelenik egy új , összegző sor. Ebben a sorban minden oszlophoz egy legördülő menüből választhatunk összesítő függvényt (Szum, darab, átlag, stb..), ami a SUBTOTAL függvénnyel működik, emiatt a szűrt adatok esetén az éppen látható sorokra adja az összesítés eredményét.
Table-Total-Row

További funkciók a Video-ban

Az előzőek bemutatásán kívül néhány további érdekességet is lehet látni a következő videóban.

  • Fejléc szövegek igazítása, hogy a szűrő nyilak ne takarják el
  • Csak az adattábla nyomtatása
  • Adatűrlap használata
  • Dinamikus névtartomány

  • Az Excel "Táblázat" funkciója

Tovább...

Dinamikus névtartomány használata az Excelben

Az Excelben kijelölt tartományokat el tudunk nevezni, és a képletekben a címtartományok helyett a neveket használhatjuk. Ezzel kissé áttekinthetőbb, jobban karbantartható képleteket kapunk.

Sok olyan függvénye van az Excelnek, ahol az egyik, vagy akár több argumentum is egy tartomány. (Fkeres, Szumha, Darabhatöbb, Szum, Darabteli, Hol.Van, stb…). Ha a képletekbe beledrótozzuk a címtartományokat, és változik a tartomány mérete, főleg ha új adatok kerülnek  a végére, akkor a hivatkozásokat is mindig utána kellene igazítani. Ugyanez érvényes egy PIVOT táblára is, ami a PIVOT létrehozása után az adattábla végére került adatokról nem vesz tudomást, hiába frissítjük.

A következő videóban azt lehet megnézni, hogy hogyan hozhatunk létre és kezelhetünk névtartományokat, illetve hogyan oldhatjuk meg azt, hogy a tartomány növekedését a névhivatkozás automatikusan kövesse, így ne kelljen törődni a képletekkel és kimutatásokkal.

Dinamikus (frissülő) névtartományok létrehozása

Tovább...