Telefonszámunk: 1-472-0679

Excel 2010

Táblázatok az Excelben

On 2013-02-27   /     /   Leave a comment

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... →
Back to Top

Hibaüzenetek lecserélése – Hibakezelés

On 2013-02-21   /     /   Leave a comment

Ha adott egy olyan Excel tábla, amelyben a képletek hibaüzeneteket adnak vissza, felmerülhet az igény a hibaüzenetek megváltoztatására, törlésére.

Ezzel kapcsolatban már volt egy korábbi anyag, amelyben az IFERROR (HAHIBA) függvényt mutattuk be, ami pont erre való. De mit tehetünk akkor, ha már adott a hibákat tartalmazó táblázat, és a képletekhez nem nyúlhatunk?

Segéd oszlopban, külön képlettel vizsgáljuk a hibát, és ha fennáll, egyéb értéket adunk, különben megtartjuk az eredeti eredményt. A képen látható példában a hibaüzenetet nullára cseréljük.

A képlet angol verzióhoz

=IF(ISERROR(B2);0;B2)

A képet magyar verzióhoz

=HA(HIBÁS(B2);0;B2)

Ezt a megoldást akkor érdemes használni, ha a tráblázathoz új sorok is adódhatnak, és a hibakezelés képletet csak le kell húzni és kész.

Hibaüzenetek cseréje speciális kijelöléssel

  1. Válasszuk a speciális (irányított) kijelölést: F5->Special… (Irányított…)
  2. Jelöljük be azt, hogy képleteket, azon belül hibákat keresünk. Ez kijelöli az összes hibaüzenetet, ezek közül az elsőben lesz az aktív cella
  3. Gépeljük be azt az értéket, amit a hibák helyett látni szeretnénk, pl.: nulla
  4. Ctrl+ENTER-rel zárjuk le, ami az össze kijelölt cellába ugyanazt az értéket viszi be.

Mindez animációban

Tovább... →
Back to Top

Számtartomány elemeinek szorzása konstanssal

On 2013-02-21   /     /   Leave a comment

Ha Excelben egy számtartomány minden értékét szeretnénk szorozni egy adott konstans értékkel, akkor a következő lehetőségeink vannak:

  1. Egy segédoszlopban mellette elvégezzük a számítást, majd értékként visszatesszük az eredményt az eredetire
  2. Speciális vágólap beillesztéssel, szorzás műveletet választva
  3. Alkalmazzuk a következő makrót, ami a bekért számmal szorozza az előzőleg kijelölt tartomány számait.

[vb]

Sub multiply_range()
‘A kijelölt tartomány számait szorozza az Inputbox-ban megadott számmal
a = InputBox("value to multiply with:")
Selection.Value = Evaluate(Selection.Address & "*" & a)
End Sub

[/vb]

Mindez hogy működik, és hogy kell használni?

Kiderül a kapcsolódó videóból

Letölthető munkafüzet: tartomány-szorzas-konstanssal

  • Tartomány szorzása konstanssal

Tovább... →
Back to Top

Szövegként tárolt számok átalakítása igazi számmá

On 2013-02-20   /     /   2 hozzászólás

Gyakran előfordul, hogy a vállalatirányítási rendszer valamely eszközétől lehúzott Excel formátumú riport bizonyos oszlopokat nem a megfelelő formában adja. Leggyakrabban a dátumokkal és a számértékekkel szokott probléma lenni.

Ilyenkor kezdődik a harc, kisebb háború, hogy több-kevesebb munkával helyreigazítsuk a dolgokat. Bevetünk az Excel által kínált eszközöket, de sokszor dátum vagy szövegkezelő függvényekre van szükségünk.

Ebben az anyagban arra a problémára adunk több megoldást, amikor egy oszlopban számokat látunk ugyan, de az Excel azokat szövegként értelmezi. Szerencsés esetben az ilyen cellák bal felső sarkában figyelmeztetésképpen egy zöld háromszöget látunk, és ha rákattintunk akkor megjelenik egy sárga, figyelmeztető rombusz egy felkiáltójellel. Ha erre rámozgatjuk az egeret, megtudhatjuk, hogy az Excelnek mi a problémája, mire figyelmeztet bennünket. A most leírt esetben valami hasonlót látunk:

A felugró buborékban arra figyelmeztet minket, hogy szövegként tárolt szám van a cellában, vagy olyan szám, ami előtt egy aposztrof (felső vessző) van.

Lehetséges megoldások

  • Kijelöljük az érintett cellákat (legtöbbször teljes oszlop) fejléc nélkül, majd lenyitjuk a rombusz melletti nyilat, és választjuk az „Átalakítás számmá” parancsot. Ezt csak akkor tudjuk megtenni, ha vannak zöld háromszögek, ami koránt sem biztos.
  • Vágólap művelettel hozzáadunk nullát vagy megszorozzuk eggyel.
  • Alkalmazzuk a „Szövegből oszlopok (Text to Coluns) parancsot
  • Egy segédoszlopban a VALUE() (ÉRTÉK) függvényt használjuk

A felsorolt módszereket működés közben is megtekintheti a Videóban.

Ha egyik fenti megoldás sem segít, akkor valami érdekes probléma lehet, ami egyedi vizsgálatot igényel.

  • Szöveges számok átalakítása számmá - 4 féleképpen

Tovább... →
Back to Top

Feltétel szerinti szélső érték – MAXIF vagy MINIF

On 2013-02-13   /     /   Leave a comment

Az Excel újabb verzióiban megjelentek a feltétel szerinti átlagszámítást végző függvények, de feltétel szerinti maximum vagy minimum számításhoz már ügyeskedni kell, mert direkt függvény még nem áll rendelkezésre.

A neten több megoldást is lehet találni a problémára. Ezek közül a tesztelések során, az itt ismertetésre kerülő az, amelyik minden esetben helyesen működik, még akkor is, ha a kiválasztandó számok pl. negatív értékek.

Feladatok

  • Képlettel határozzuk meg az alábbi táblázatból a legnagyobb értéket az „A” kategóriában!
  • Az érték oszlopban melyik a legnagyobb vagy legkisebb páros szám?
  • stb…

Megoldás segédoszloppal, két lépésben

Elsőként nézzük meg azt a képletet, amellyel megállapíthatjuk a legnagyobb értéket az „A” típusok közül.

Ezt papíron, vagy szemmel úgy csinálnánk, hogy kikeressük az összes „A” típusú sort, és a hozzájuk tartozó értékek közül kiválogatjuk a legnagyobbat. A fenti táblázattal könnyű a helyzetünk, mert sorba van rendezve típus szerint, és ráadásul csak 8 sora van.

A „C” oszlopt használva segéd oszlopként, a következő képletet írjuk a C2 cellába, majd lehúzhatjuk a teljes oszlopra:

=IF(A2="A";B2)

Eszerint, ha az „A” oszlopban „A” érték van, akkor a „C” oszlopba írja be a „B” oszlopból az értéket. Különben ágat nem adunk meg a HA (IF) függvénynek, ekkor automatikusan  a hamis ág FALSE érték lesz.

Ezek után nincs más dolgunk, mint egy külön cellába lekérni a „C” oszlop maximumát vagy minimumát.

Megoldás tömbképlettel

Ez a megoldás azt használja ki, hogy az Excel képes tömbökkel is dolgozni. Próbáljuk meg, hogy a fenti táblázat alapján egy üres cellába beírjuk ezt:

=A2:A9="A"

majd ENTER helyett nyomjuk meg az F9 billentyűt!

Ezt fogjuk látni:

Azaz a tartomány minden elemére kiértékelte az egyenlőséget, és egy logikai tömböt adott vissza az eredménnyel.

Ctrl-Z-vel visszatérhetünk a képlethez, és egészítsük ki a következőre:

=IF(A2:A9="A";B2:B9)

A HA (IF) függvény megvizsgálja az egyenlőséget az „A” oszlop elemein, és ha egyezőséget talál, akkor visszaadja a „B” oszlop megfelelő elemét, különben pedig FALSE értéket. Próbáljuk megint az F9-es trükköt:

Nincs más hátra, mint ennek a tömbnek venni a MAX vagy a MIN elemét.

=MAX(IF(A2:A9="A";B2:B9))

A képletet tömbképletként kell bevinni, azaz ENTER helyett a Ctrl+Shift+ENTER-rel kell lezárni!

{=MAX(IF(A2:A9=”A”;B2:B9))}

Letölthető minta munkafüzet

MAX-IF.xlsx

Demo VIDEO

Ami működés közben mutatja a két megoldást, és bónuszként tartalmazza azt is, hogy hogyan tudjuk kiválasztani egy tartományból a legnagyobb vagy legkisebb páros számot.

  • Feltételes Maximum vagy Minimum kiválasztás képlet

Tovább... →
Back to Top

Cellához képesti reláció a feltételes összesítésekben

On 2013-02-08   /    

Az Excel 2003-ig még csak a SUMIF/COUNTIF (SZUMHA/DARABTELI) függvények léteztek a feltétel szerinti összesítésre. Az első feltétel szerinti összegzést, a második feltétel szerinti megszámlálást végez.

Aztán az Excel 2007-ben bevezettek jó néhány új függvényt, így a hasonló funkciójú függvények sora így néz ki:

Funkció leírása Angol név Magyar név
Egy feltétel szerinti összegzés SUMIF SZUMHA
Több feltétel szerinti összegzés SUMIFS SZUMHATÖBB
Egy feltétel szerinti megszámlálás COUNTIF DARABTELI
Több feltétel szerinti megszámlálás COUNTIFS DARABHATÖBB
Egy feltétel szerinti átlagolás AVERAGEIF ÁTLAGHA
Több feltétel szerinti átlagolás AVERAGEIFS ÁTLAGHATÖBB

Ennek a bejegyzésnek nem az  acélja, hogy ismertesse ezeket a függvényeket, hiszen meglehetősen egyszerűek, az Excel súgója is remek leírást és példákat ad.

De van egy olyan eset a feltétel megfogalmazásánál, ami nem teljesen egyértelmű.

Feladat

Számoljuk meg az összes olyan cellát az A1:A100 tartományban, ahol a cellák értéke kisebb, mint a  D1-ben lévő érték.

Ezt elsőre így próbálnánk megoldani:

=COUNTIF(A1:A100;”<D1″) – Az eredmény valószínűleg nulla lesz, akkor is, ha vannak a D1-nél kisebbek a tartományban

Másodikra megpróbálnánk így:

=COUNTIF(A1:A100;<D1)- Erre pedig kapunk egy hibaüzenetet

Helyes megoldás

Az összes fenti függvény esetén, ha a feltételben használnánk egy cella tartalmát, és ehhez relációval akarunk viszonyítani (kisebb, kisebb vagy egyenlő, stb…), akkor a relációs jelet össze kell fűznünk a cella címével.

=COUNTIF(A1:A100;”<„&D1)

Tehát a kívánt relációs műveleti jelet idézőjelbe tesszük, majd összefűzzük a kívánt cella címével.

Tovább... →
Back to Top

FKERES (VLOOKUP) helyett van jobb!?

On 2013-02-03   /     /   2 hozzászólás

Két táblázat összekapcsolását tapasztalatom szerint a legtöbb esetben a VLOOKUP (FKERES) függvénnyel oldják meg. A tipikus helyzet az, hogy egy cikk, termék, számla, személy, stb… adatait szeretnénk kikeresni egy törzs táblázatból.

Egy másik táblázatban rendelkezésre áll a keresendő valami azonosító adata, ezt keressük a törzs táblázatban, és ha megtaláljuk benne, akkor ugyanabból a sorból egy másik tulajdonságát (nevét, egységárát, stb..) kérjük vissza.

Az FKERES (VLOOKUP) működése pontos egyezőségi keresés esetén

Az FKERES működése

Az FKERES (VLOOKUP) problémái

  • Csak a keresési tábla első oszlopában tud keresni, és csak tőle jobbra lévő oszlopból tudunk kérni információt
  • Érzékeny a keresési táblázat szerkezetének (oszlopsorrendjének) változására
  • Ha több oszlopból is kérünk vissza adatot, akkor mindannyiszor kerestetni kell a kulcsot
  • Előbbi miatt nagy táblázatoknál lassulást eredményezhet

MATCH+INDEX (HOL.VAN+INDEX) a király!

Az előző megoldás helyett sokkal rugalmasabbnak tűnik egy másik megoldás, ami ugyanarra a feladatra hesználható, és a fenti hiányosságokra megoldást nyújt. Konyhanyelven  a következőről van szó:

  1. Először megkeresem az azonosítót a keresési táblázat vonatkozó oszlopában, és megtudom, hogy hányadik helyen van benne. Erre való a MATCH (HOL.VAN) függvény.
  2. Majd ez alapján a szükséges oszlopból kiveszem az annyiadik elemet, amit az előbb megkaptam. Ezt pedig az INDEX függvény teszi meg.

Tehát két függvényhívás. Lehetnek két külön oszlopban, de össze is lehet őket fűzni egy képletbe.

Letölthető példa munkafüzet

További érthetetlen ábrák és magyarázkodás helyett nézzük akció közben!

  • Fkeres helyett Hol.van+Index

Tovább... →
Back to Top

Excel – VAGY feltétel szerinti megszámlálás

On 2013-02-03   /     /   Leave a comment

A kezdetekben a feltétel szerinti megszámlálásra adták nekünk a COUNTIF (Darabteli) függvényt, ami csak egy feltétel szerinti megszámlálást tud.

Majd a több feltétel szerinti megszámlálásra adták nekünk az Excel 2007-től a COUNTIFS (Darabhatöbb) függvényt, de ez csak a több feltétel egyidejű teljesülése esetén jó (ÉS kapcsolat)

Mi van akkor, ha VAGY feltételek szerint szeretnénk megszámlálni?

Kapcsolódó anyagok

  • Több feltétel szerinti megszámlálás VAGY kapcsolattal

Tovább... →
Back to Top

Az Excel kalkulációs idejének mérése

On 2013-02-01   /     /   4 hozzászólás

A nagy méretű, sok képletet tartalmazó munkafüzetekkel bizony meggyűlhet  a bajunk. A képletek állandó újraszámolása sok időbe telhet, és ez gátolhatja a munkavégzést.

Ilyenkor ki szoktuk kapcsolni az automatikus újraszámolást, és áttérünk manuális újraszámolásra, amihez a következő billentyűkombinációkat használhatjuk:

  • F9 – minden nyitvalévő munkafüzetben újraszámolja azokat a képleteket, amelyeknek az elődcellái változtak , tehát csak az általa szükségesnek vélt képleteket számolja újra.
    (VBA: Application.Calculate)
  •  Shift+F9 – Az előzőt teszi, de csak az aktuális munkalappal.
    (VBA: ActiveSheet.Calculate)
  •  Ctrl+Alt+F9 – Minden nyitott munkafüzetben minden képletet újraszámol.
    (VBA: Application.CalculateFull)
  • Ctrl+Shift+Alt+F9 – Minden nyitott munkafüzetben minden képletet újraszámol, és ellenőrzi, illetve újraépíti a függőségeket.
    (VBA: Application.CalculateFullRebuild)

Ha számít a sebesség, és egy problémára többféle megoldási ötletünk is van, akkor érdemes lehet a gyorsabbat választani. A sebesség vagy egyértelműen látszik, vagy mérni kell. Például kevesebb mennyiségű teszt adaton végzett mérés rámutathat a majdani sok adattal történő viselkedésre.

Az Excel kalkuláció mérése

Van egy nagyon érdekes cikk az Excel 2007-es verzióhoz, ahol a működési sebességet befolyásoló tényezőket taglalják.
Itt a link: http://msdn.microsoft.com/en-us/library/aa730921.aspx

Megadnak ott egy VBA kódot, amivel a fenti újrakalkulációs időket lehet számolni. Elég, ha a kódot tartalmazó munkafüzetet megnyitjuk, és máris használhatjuk a mérést. A kódokat tartalmazó munkafüzet letölthető az excel-bazis.hu-ról is.

Munkafüzet a meres kódjaival

A MICROTIMER() függvény Windows API hívásokkal éri el az operációs rendszer óráját, így akár mikroszekundumokat is képes mérni. A mérés úgy működik, hogy kikapcsolja az automatikus újrakalkulációt, majd a választott kalkulációs móddal újraszámol, közben pedig megméri az eltelt időt. A mérés végén az eredményt másodpercben egy üzenetben megjeleníti, majd beírja azt az aktuális cellába.

Fontos: Mivel a Windows nem Real Time rendszer, a mérések pontosságának fokozása érdekében érdemes 3-4 alkalommal megismételni, és  a kapott eredményeket átlagolni.

A videóból pedig kiderül, hogyan kell használni.

  • Kalkulációs modell mérése

Tovább... →
Back to Top

Másolás-Beillesztési ügyeskedés billentyűzettel Excel 2010-től

On 2013-01-31   /     /   Leave a comment

A tartományok másolására és mozgatására szolgáló eszközt mindenki ismeri az Excelben. használhatjuk mutatóeszközzel az erre szolgáló ikonokat, és ott van a három legismertebb gyorsbillentyű:

  • Ctrl+C – Másolás
  • Ctrl+X – Kivágás
  • Ctrl+V – Beillesztés

A hatékony használat szempontjából talán az alapján érdemes dönteni, hogy mikor, melyik módszert használjuk, hogy éppen mi van a kezünk ügyében. Vagy esetleg melyik a gyorsabb módszer.

Speciális beillesztések

Itt az „old style” módszerrel a „Paste Special” („Irányított beillesztés”) funkciót választhattuk, vagy néhány speciális beillesztési lehetőség volt a beillesztő gomb legördülő menüjében.

Excel 2003  Excel 2007 Excel 2010
     

A beillesztési segéd

A beillesztési segéd az az eszköz, ami a sima, Ctrl+V beillesztés után a beillesztett tartomány jobb alsó sarkában megjelenik.

A beillesztett tartomány jobb alsó sarkában eddig is megjelent a beillesztési segéd, de csak egérrel lehetett kezelni. Az Excel 2010-től azonban a Ctrl billentyűvel le tudjuk nyitni, és további egy billentyűvel kiválaszthatjuk  a kívánt parancsot.

Jobb klikkes kombináció

Ha pedig a beillesztést úgy csináljuk, hogy a helyén előbb jobb klikkelünk, akkor szintén egy betűvel kiválaszthatjuk  a kívánt speciális beillesztést.

A Videoban röviden meg is lehet tekinteni az eszköz használatát.

  • Speciális beillesztés billentyűzettel Excel 2010-ben

Tovább... →
Back to Top
« Előző oldal
Következő oldal »