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
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:
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.
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.
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.
A videóból pedig kiderül, hogyan kell használni.
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ű:
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.
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 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.
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.
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.
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.
Ha van Internet kapcsolatunk, akkor EXCEL VBA kód segítségével az Interneten lévő forrásokat is használhatjuk, pl.
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... →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.
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.
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.
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.
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!
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.
Az Excel egészen a 2013-as verzióig (de elképzelhető, hogy a további verziókra is igaz lesz) nem képes arra, hogy több munkalapot kijelölve egyszerre bekapcsolhassuk a lapvédelmet ugyanazzal a jelszóval.
Tehát több, sok munkalap esetén hosszas kattintgatás előtt állunk, ha ezt meg szeretnénk oldani, mivel egyesével kell bekapcsolni a lapvédelmet.
A jó hír az, hogy van megoldás a problémára, a rossz hír pedig az, hogy csak VBA kóddal oldható meg.
Igaz, a kódot nem kell a védendő dokumentumba tenni, elég, ha nyitva van egy olyan munkafüzet, amiben benne van az alábbi makró, és azt lefuttatjuk. Akár be is tehetjük a PERSONAL makró munkafüzetbe, és akkor bármikor rendelkezésünkre áll.
Nyissuk meg a PERSONAL makró füzetet. Ha nincs ilyen vagy nem tudjuk miről van szó,
akkor nézzük meg itt. A PERSONAL (Egyéni) makró füzet (link)
[vb]
Sub Protect_Unprotect( )
Dim wSheet As Worksheet
Dim pw as string
pw = inputbox("Add meg a jelszót:")
For Each wSheet In Worksheets
With wSheet
If .ProtectContents = True Then
.Unprotect Password:=pw
Else
.Protect Password:=pw
End If
End With
Next wSheet
End Sub
[/vb]
A makró bekéri a jelszót, majd végiglép minden munkalapon, és ha nincs bekapcsolva a védelem, akkor bekapcsolja, ha be volt kapcsolva, akkor pedig feloldja a védelmet.
A kódot bátran próbáljuk módosítani, hogy pl. csak bekapcsolja vagy csak kikapcsolja. Ezeket akár külön makróba is lehet tenni.
Vigyázat! A jelszót ne felejtsük el, mert ez a kód nem tárolja le sehová!
Tovább... →