Excel testre szabása makróval

A 2007-es Excel beállításainak változtatásához először a Microsoft Office - Az Excel beállításai (Excel Options) ablakot kell kinyitnunk, majd ott megkeresni a szükséges pontot a végtelennek tűnő lehetőségek listájából. Ha már sokadszor változtatunk meg egy beállítást, például a számítási módot, vagy a nullák megjelenítését, akkor egész gyorsan megtaláljuk, de ha még ennél is elegánsabban szeretnénk megoldani, akkor feltehetjük kedvenc beállításainkat a Gyorselérési eszköztárba (Quick Access Toolbar).

Először is azonosítsuk az átállítandó paraméter nevét és lehetséges értékeit, mondjuk úgy, hogy makrórögzítés közben megváltoztatjuk őket. Például a számítási mód azonosítója és két értéke egy rögzített makróból:

    Application.Calculation = xlManual
    Application.Calculation = xlAutomatic

Azt is tudjuk, hogy legalább egy munkafüzetnek nyitva kell lennie, hogy megváltoztassuk a számítási módot. Nézzük a makrót:


Sub ToggleCalculationMode()


'megpróbálunk aktiválni egy munkalapot, és ha nem sikerül, akkor küldünk megfelelő hibaüzenetet is
Err = 0
On Error Resume Next
ActiveSheet.Activate


If Err = 91 Then
    MsgBox "Legalább egy nyitott munkafüzet szükséges a számítási mód változtatásához."
    Exit Sub
ElseIf Err > 0 Then
    MsgBox "Váratlan hiba"
    Exit Sub
Else
    'ha viszont van nyitott munkalap, akkor át tudjuk állítani a számítási módot a mostani ellenkezőjére
    On Error GoTo 0
    If Application.Calculation = xlCalculationManual Then
        Application.Calculation = xlCalculationAutomatic
    Else
       Application.Calculation = xlCalculationManual
    End If
End If
On Error GoTo 0


End Sub

Ha a makró kész, akkor már csak hozzá kell adni a Gyorselérési eszköztárhoz; válasszuk a Makrók csoportot, keressük meg a fenti makrót a listából, és adjuk hozzá a gyorsmenühöz. 

Választhatunk többet mondó ikont is:

És végül kipróbálhatjuk az új gombot:

Hasonlóképpen bármelyik Excel beállítást kihozhatjuk a gyorselérési eszköztárba.

A Google Docs FILTER függvénye

A Google Docs egyik hasznos függvénye a FILTER, ami tulajdonképpen képlet formájában csinálja ugyanazt, amit a Filtertől a menüben már megszoktunk: megadott szempont(ok) szerint lelistázza a feltételnek megfelelő eleme(ke)t.

Az alábbi példában az országok közül keresünk két feltétel alapján: Európához tartozó területe legyen nagyobb, mint 400 000 km2, és a GDP dollárbeli értéke a vásárlóerő-paritás (PPP) alapján legyen nagyobb, mint 30 000 nemzetközi dollár.

A FILTER képlet nagyon logikus: először megadjuk a szűrendő listát, aztán pedig a két feltételt. A képletet csak egy cellába kell beírni. Az Enter lenyomása után a FILTER magától leszalad addig, amíg csak szükséges. A szerkesztősorban látszik, hogy a második cellától kezdve a CONTINUE függvény biztosítja a teljes adatsor megjelenését.

Az eredeti fájl és képletek közelről, működés közben is tanulmányozhatók egy Google Docs Spreadsheetben. Próbáld meg változtatni a területi és/vagy GDP feltételt a B15 és B16 cellákban, hogy lásd, hogyan változik a lista mérete teljesen automatikusan, az E15-ös cellától indulva.

A Google Docs fantáziadús újítása: GoogleTranslate!

A Google Docs függvénylistáját átfutva az ember hajlamos azt gondolni, hogy csak egy viszonylag egyszerű online táblázatkezelővel áll szemben. Ha viszont megakad a szemünk azokon a finomságokon, ami csak egy Google-nek juthat eszébe, akkor egy új világ tárulhat ki előttünk: az Internet már nem csak annyit jelent, hogy hozzáférünk egy táblázatkezelőhöz, és el is tudjuk menteni a fájlokat. A Google Docs határozottan afelé tart, hogy az Internet minden aspektusát integrálni tudjuk a táblázatainkba, ha éppen arra van szükség.

Ennek egy igen jó példája a GoogleTranslate függvény, ami nem csak szavakkal, de egyszerűbb mondatokkal is szépen boldogul, ráadásul a forrásnyelvet is felismeri, ha rábízzuk. Végtelen lehetőségek kínálkoznak a nyelvtanulók vagy többnyelvű környezetben dolgozók számára...

Ha mélyebben érdekel ez a függvény vagy a többi "Google" újítás:
A Google Docs "Google" függvényei (angol nyelven)

Dátumok

Általában úgy kezdődik, hogy megértjük: az Excel egyetlen szép nagy számként kezeli a dátumokat, például 1985 január elseje = 31048. Ahhoz, hogy különböző év-hónap-nap sorrendű vagy éppen szöveges formátumokban láthassuk a dátumokat, meg kell formáznunk a cellát, de a cella tartalma továbbra is az a bizonyos szép nagy szám marad (szerencsére ugyanebből a tényből következően viszonylag egyszerűen tudunk dátumokat kivonni egymásból).

Akik már túl vannak az első meglepetéseken, és viszonylagos önbizalommal tekintenek a dátumokra, valószínűleg értékelni fogják, mi mindent tartogat még számukra az Excel és a dátumkezelés (és az időről még nem is beszéltünk).

Néhány képlet, ami hasznos lehet, ha dátumokkal foglalkozunk:
DATE - ha megadjuk neki az év, hónap, nap adatokat, akkor összerakja a dátumot
TODAY() - a mai nap dátumát adja vissza
YEAR - ha kap egy dátumot, kitalálja, hogy melyik évben van

És néhány képlet, ami bármilyen helyzetben hasznos lehet:
CHOOSE - az első paramétere egy sorszám, ami meghatározza, hogy az őt követő listából hányadik érték legyen a képlet értéke (a példából talán könnyebb megérteni)
TEXT - ki gondolná, szöveget csinál a számból (a példa mutatja, hogyan)
UPPER - nagybetűsre konvertálja a szöveget

Ha valaki további tippeket szeretne dátum témakörben, melegen ajánlom az egyik kedvenc oldalamat:
Chip Pearson - Worksheet Functions For Dates And Times

Adatkeresés 1. Index-Match-Small/Large kombináció

Amikor csupán annyi a dolgunk, hogy egy listában megtaláljuk a legkisebb vagy a legnagyobb értéket, általában nem jövünk zavarba, és rászabadítjuk a listára a MIN vagy a MAX függvényeket. Ha nem csak az első legkisebb értékre van szükségünk, hanem a második vagy harmadik stb. legkisebb értékre, akkor a SMALL tehet jó szolgálatot, vagy ha az x-edik legnagyobb értéket keressük, akkor a LARGE.

Amikor viszont a legkisebb vagy legnagyobb értéke(ke)t azért keressük, hogy egy másik oszlopból hozzunk vissza címkét vagy értéket, nagy segítséget jelent, ha rutinosan kezeljük az INDEX és a MATCH függvényeket is.

A MIN, MAX, SMALL vagy LARGE függvények megkeresik egy lista egy vagy több legkisebb vagy legnagyobb elemét, és ezt vagy ezeket az értékeket hozzák vissza. A MATCH megkeresi, hogy az imént visszahozott értékek hányadik helyen szerepelnek az adott listában, majd az INDEX függvény visszahozza a MATCH által kijelölt sor/oszlop és egy másik oszlop/sor metszetében található értéket.

A példa, amihez a 10 legnagyobb területű európai ország néhány adatát használtam:

Színjáték

Találtam egy színgyűjteményt a http://kb.iu.edu/data/aetf.html oldalon, és persze megpróbáltam kihozni belőle valami izgalmasat egy táblázatkezelőben: egy mini-makró, ami bizonyos területeket az általam választott színre fest. A színeket lenyíló listából lehet kiválasztani.

Először is bemásoltam a színkódokat egy munkalapra, aminek az adatbazis nevet adtam. Aztán a színek neveit kijelöltem, és a listát elneveztem Colors-nak.

Ugyanígy nevet adok a területeknek, amiket majd színezni szeretnék, ezek egy másik munkalapon vannak (a munkalap neve Szinezes), a területek neve legyen fal (Wall), padló (Floor) és szőnyeg (Carpet):

Annak érdekében, hogy a színek kiválaszthatók legyenek egy lenyíló listából, a színlista nevét megadom a Data validationben mindhárom területhez:

A színkódokra is szükség lesz, ezért VLOOKUP függvényekkel megkeresem az R, G, B értékeket mindhárom választott színhez.

Ennyi volt az előkészület, jöhet a mini-makró: Excelben Alt+F11 lenyomásával lehet előhívni a makrószerkesztőt. A Szinezes munkalapon a következő kód fogja végezni a színezést:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Address = "$B$3" Then
        ColorMe Range("Wall"), ActiveSheet.Cells(3, 3).Value, ActiveSheet.Cells(4, 3).Value, ActiveSheet.Cells(5, 3).Value
    ElseIf Target.Address = "$B$12" Then
        ColorMe Range("Floor"), ActiveSheet.Cells(12, 3).Value, ActiveSheet.Cells(13, 3).Value, ActiveSheet.Cells(14, 3).Value
    ElseIf Target.Address = "$B$15" Then
        ColorMe Range("Carpet"), ActiveSheet.Cells(15, 3).Value, ActiveSheet.Cells(16, 3).Value, ActiveSheet.Cells(17, 3).Value
    End If
End Sub

Sub ColorMe(myRange As Range, myRed As Integer, myGreen As Integer, myBlue As Integer)
myRange.Interior.Color = RGB(myRed, myGreen, myBlue)
End Sub

Az első rész figyeli, hogy történik-e változás azokban a cellákban, ahol az egyes területek színeit ki lehet választani ($B$3, $B$12, $B$15). Amennyiben új színt választok, a makró akcióba lép és elindítja a második részt, ami maga a színezés. A második, rövidke makró az RGB függvény segítségével kiszínezi a megfelelő cellaterületet.

A fenti módszer felhasználható például a feltételes formázás lehetőségeinek kiterjesztésére: egy cella értékétől függően szabályozhatjuk több független (akár üres) terület celláinak színét.

Kerekítési lehetőségek

Elég sok táblázatkezelő függvényt használhatunk kerekítésre, attól függően, hogy milyen kerekítési szabályt szeretnénk használni. Kerekíthetünk lefelé, fölfelé, bizonyos számú tizedesjegyre, kérhetünk meghatározott számú nullát a szám végére... Néhány példa, most a Google Docs függvényeivel (a legtöbbjük ugyanilyen vagy hasonló formában elérhető a többi táblázatkezelőben is):

ROUNDUP (magyar rokona Excelben a KEREK.FEL): felfelé kerekíti a megadott értéket a következő szabályok szerint: a negatív paraméter hatására nullá(k)ra végződő egész számot fogunk kapni (annyi nulla, ahányat a negatív paraméterben megadunk), míg pozitív paraméter hatására csak a tizedesjegyeket kerekíti fölfelé (mégpedig annyi tizedesjegyre, ahányat a pozitív paraméterben megadunk).

ROUNDDOWN (magyar  rokona Excelben a KEREK.LE): ugyanúgy működik, mint az előbbi függvény, csak ez lefelé kerekít.

CEILING (magyar  rokona Excelben a PLAFON): nagyon hasonlít a negatív paraméterrel ellátott ROUNDUP-hoz, csak itt konkrétan azt adjuk meg a függvénynek, hogy a fölfelé kerekített érték mivel legyen osztható.

FLOOR (magyar  rokona Excelben a PADLÓ): ugyanaz, lefelé.

ROUND (magyar  rokona Excelben a KEREKÍTÉS): hasonlít a fölfelé vagy lefelé kerekítő testvéreihez, de ő a kerekítés szabályait veszi figyelembe, hogy eldöntse, lefelé vagy fölfelé kerekítsen. Vagy a záró nullák számát, vagy a tizedesjegyek számát adjuk meg paraméterként.

MROUND (magyar  rokona Excelben a TÖBBSZ.KEREKÍT): a CEILING és a FLOOR testvére, azt adjuk meg neki, hogy mivel legyen osztható a kerekített érték, viszont nem határozzuk meg, hogy lefelé vagy fölfelé kerekítsen, hanem hagyjuk, hogy az MROUND magától döntsön. Persze csak a kerekítési szabály keretein belül...

További Google Docs függvények