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... →A feltétel szerinti megszámlálásra az Excel kínál két függvényt:
Ezekkel a függvényekkel az a probléma, hogy a megszámlálás feltételeként nem lehet képletet alkalmazni. De mit tegyünk, ha például meg kellene számolnunk a páros számokat egy tartományban? A páros értékek megszámolásához képlet kell, amivel el tudjuk dönteni egy értékről, hogy páros-e. Erre több megoldás is van, de a legjobb a MARADÉK (MOD) függvény.
De hogy a feltételhez használt képletet hogyan építhetjük bele a megszámlálásba, az már nem olyan egyszerű. A példa alapján egyéb, hasonló probléma is megoldható, pl. az, hogy hány olyan sor van, ahol az egyik oszlopban kisebb vagy nagyobb érték van, mint egy másikban.
http://youtu.be/jZYb3KKV0Zc
Tovább... →Az Excelben a logikai kifejezések és függvények eredménye alapértelmezésként „IGAZ” vagy „HAMIS” („TRUE”/”FALSE”).
Előfordulhat, hogy ezeket a megfelelő számra, azaz 1-re vagy 0-ra kell konvertálni, például azért, hogy megszámolhassuk őket. Az átalakítással sima összeadássá egyszerűsíthetjük a dolgot.
Az átalakításra leggyakrabban a dupla mínuszjelet használják, de tulajdonképpen bármilyen matematikai művelet megteszi, ami az eredeti értéket nem változtatja meg.
Legegyszerűbben úgy próbálhatjuk ki, hogy egy cellába beírjuk a következőt:
Magyar: =- -IGAZ
Angol: =- -TRUE
Önmagában ennek persze semmi értelme, de képletekben alkalmazva már jól használható.
Néhány lehetőség az átalakításra (A1-ben van a logikai eredmény)
=–(A1)
=(A1)*1
=(A1)/1
=(A1)+0
=(A1)^1
=ABS(A1)
Az egészet egy db speciális képlettel is meg lehet csinálni, de azt csak a VIDEO-ban lehet megnézni.
Gyakran előfordulhat, főleg Multi környezetben, hogy az Excel munkafüzeteket, alkalmazásokat több nyelvi környezetre is fel kell készíteni.
Az is előfordulhat, hogy az adott Excel nyelvi verzióban az érvényes beállításokat meg kell néznünk, hogy a képleteink vagy egyéb műveleteinket az Excel helyesen értelmezze.
Összesen 45 ilyen nemzetközi beállítást tartalmaz az Excel. Készítettem egy táblázatot, amiben egy makró lefuttatásával Te is megnézheted, hogy a saját Excel-edben mik ezek a beállítások.
Töltsd le a fájlt, engedélyezd a makrókat, és nyomd meg a tetején a „Get values” gombot
Az Excel_nemzetközi_beállításai munkafüzet letöltése
