Telefonszámunk: 30-905-2076

Excel 2007

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

Haladó szűrési technikák – Szűrőoszlop vagy Irányított szűrő

On 2013-01-28   /     /   2 hozzászólás

Előfordulhatnak olyan szűrési feltételek, amelyeket autoszűrővel nem, vagy csak több lépésben tudunk megoldani. Ekkor vehetjük hasznát az egyéni szűrő segédoszlopnak, ahol mi fogalmazzuk meg a szűrési feltételt, vagy segítségül hívjuk az Excel beépített „Irányított” szűrőjét (Advanded Filter)

Bónuszként még az is megtudható a videóból, hogy hogyan kérhetjük a szűrés eredményét egy másik munkalapra.

  • Haladó szűrés Excellel

Tovább... →
Back to Top

Csak a látható cellák (sorok vagy oszlopok) másolása

On 2013-01-27   /     /   Leave a comment

Ha sorokat vagy oszlopokat rejtünk el az Excellel, majd a megmaradt tartományt át szeretnénk másolni valahová, akkor meglepődve tapasztalhatjuk, hogy átviszi az elrejtett cellákat is. Ha belegondolunk, akkor ez valahol jogos, hiszen amikor kijelöljük az egész megmaradt tartományt, akkor ott vannak a nem látható cellák is, valahol a kijelölésben.

Érdekes azonban, hogy ha autoszűrővel rejtünk el sorokat, akkor az átmásolás tökéletesen működik, és csak a szűrt sorokat másolja át.

A következő videóban megmutatjuk, hogyan lehet rávenni az Excelt arra, hogy az elrejtett részeket zárja ki a kijelölésből.

  • Elrejtett sorok kizárása a másolásból

Tovább... →
Back to Top

Internetkapcsolat tesztelése VBA kódból

On 2013-01-27   /     /   Leave a comment

Ha van Internet kapcsolatunk, akkor EXCEL VBA kód segítségével az Interneten lévő forrásokat is használhatjuk, pl.

  • Megnyithatunk egy publikus weboldalt, és olvashatjuk  a tartalmát
  • Be tudunk jelentkezni egy jelszóval védett oldalra
  • GET vagy POST adatokat küldhetünk egy weboldalnak
  • Egy weboldal által visszaküldött választ fogadhatunk, majd feldolgozhatunk (JSON vagy XML)
  • stb…


Mindezekhez azonban szükséges ellenőrizni, hogy van-e egyáltalán élő internet kapcsolatunk. Ehhez nyújt segítséget a következő kód, ami a Windows egyik DLL hívását használja.

Az elején láthatjuk a Windows API függvény definíciót, majd következik az a saját függvény, ami az API hívás alapján eldönti, hogyvan-e kapcsolat vagy nincs, és logikai visszatérési értéket ad.

Az utolsó subrutin pedig bemutatja használat közben.

Csak másoljuk be egy standard modulba, és máris kipróbálhatjuk.

[vb]
‘Windows API függvény deklaráció
Private Declare Function InternetGetConnectedState _
Lib "wininet.dll" (ByRef dwflags As Long, _
ByVal dwReserved As Long) As Long

‘UDF a fenti API hívással és kiértékelésével
Function IsInternetConnected() As Boolean
Dim L As Long
Dim R As Long
R = InternetGetConnectedState(L, 0&)
If R = 0 Then
IsInternetConnected = False
Else
If R <= 4 Then
IsInternetConnected = True
Else
IsInternetConnected = False
End If
End If
End Function

‘Használati példa
Sub Test_Internet()
If IsInternetConnected() = True Then
MsgBox "Connected"
Else
MsgBox "No Internet Connection"
End If
End Sub

[/vb]

Tovább... →
Back to Top

Az Excel autoszűrő beállításainak mentése

On 2013-01-26   /     /   Leave a comment

Kevesen tudják, hogy az Excel el tudja menteni az aktuális szűrési állapotot, azaz az autoszűrő beállításait. Sőt, nem csak egy, hanem sok ilyen beállítást menthetünk, így egyik szűrésről a másikra pillanatok alatt tudunk váltani. A sok szöveges magyarázkodás helyett egyszerűbb, ha megnézed az alábbi videót a témáról.

  • A szűrőbeállítások mentése

Tovább... →
Back to Top

Remek függvény a hatékony hibakezeléshez – IFERROR -HAHIBA

On 2013-01-23   /     /   Leave a comment

Előfordulhat, hogy a képletek által visszaadott hibajelzéseket érzékelni szeretnénk, és a hibát eredményező képlet cellájába nem a hibakódot akarjuk látni, hanem valami általunk definiált számot vagy szöveget.

Leggyakrabban a Vlookup (Fkeres) vagy a Match (Hol.van) függvényeknél találkozunk azzal, hogy ha nem találja a keresendő adatot pontos egyezőségre, akkor a #N/A (#NINCS) hibakódot adja vissza. Ezen kívül lehetnek még egyéb hibakódok nullával történő osztásnál, hibás névhasználatnál, stb…

Az Excel 2007 előtti időkben ennek a kezelésére egy feltételvizsgálatot kellett csinálni, amit kétféleképpen oldhattunk meg.

ISERROR (HIBÁS) függvény használata első módszer

A vizsgálatot egy külön segédoszlopban tettünk meg, és utána a következő oszlopban azt vizsgáltuk, hogy hibakódot adott-e.

ISERROR (HIBÁS) függvény használata második módszer

Segédoszlop nélkül, magában a képletben először ellenőriztük, hogy hibát kapunk-e, és ha nem, akkor lefuttattuk a függvényt, különben beírtuk a saját üzenetet. Ez utóbbi megoldás nagy hátránya, hogy ha nincs hiba, akkor a függvényt kétszer hajtatjuk végre az Excel-lel.

IFERROR (HAHIBA) függvény

Az Excel 2007-ben bevezettek egy új függvényt, ami az előző két módszert összevonja, és kiküszöböli a hatékonysági problémát. A függvény első argumentuma a kifejezés, amit vizsgálunk, és ha nem okoz hibát, akkor a kifejezés által visszaadott eredményt írja a cellába, ha viszont hibát adna, akkor a második argumentumban beállított egyedi értéket, a mi lehet szám, vagy szöveg.

Figyelem! Ha valaki a függvény beszúrás varázslóval szeretné használni, akkor érdemes tudnia, hogy a függvény a logikai függvénykategóriába lett besorolva!

Tovább... →
Back to Top

Dinamikus névtartomány használata az Excelben

On 2013-01-10   /     /   Leave a comment

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... →
Back to Top
« Előző oldal
Következő oldal »