SEGÉDLET ADATKEZELÉS MS EXCEL-BEN Tekintsük az alábbi példát: Népesedési tendenciák a közép-kelet-európai fővárosokban, Nemzetközi összehasonlítások 9. KSH, Budapest 2005. Tároljuk az adatokat Excel munkalapon. Megjegyzés: A feladatokat MS Office Excel 2003ban oldottuk meg. Az Adatok menüpontból a következő adatkezelő műveletek érhetők el: 1. Rendezés: A rendezendő adatok tartományának kijelölése után az alábbi ablakban adható meg, hogy mely mezők (max. 3) szerint kívánjuk rendezni az adatokat és milyen sorrendben (növekvő ill. csökkenő).
2. Szűrés: a. Auto szűrő: Bekapcsolásával minden oszlop fejlécében (soros tárolás esetén a sor fejlécében) megjelenik egy nyíl, melyre kattintva a legördülő menüből választható ki a szűrés módja. • Növekvő/csökkenő rendezés az adott mező (oszlop) szerint • Összes megjelenítése (mind) • Egy adatsor kiválasztása a listából • Helyezettek szűrése (Helyezés…) Első/Utolsó x tétel/százalék megjelenítése
•
Egyéni szűrőfeltétel megadása (Egyéni…)
b. Irányított szűrő: Szűrés a tartomány megadásával.
3. Űrlap: A tartomány kijelölését követően egy automatikusan generált űrlapon rekordonként megjelennek az adatok, amelyek között egyesével előre-hátra lépegethetünk. A szűrő gombra kattintva mezőérték-szűrés adható meg: ekkor csak a szűrőfeltételt kielégítő rekordok jelennek meg az űrlapon.
4. Részösszeg: A parancs segítségével csoportokra bonthatjuk adatainkat, és a csoportokon összesítő műveleteket végezhetünk. Az összesítő műveletek a szokásosak: maximum, minimum, összeg, darab, stb. Fontos: az adatokat a részösszeg képzés előtt rendezni kell aszerint az oszlop szerint, amelyik szerint csoportosítva lesznek az adatok.
5. Érvényesítés: Az adatok beírása előtt megadhatunk érvényességi korlátozásokat, melyek beíráskor ellenőrzik az adatainkat. Pl: számadatok esetén az ábrán látható korlátozások állíthatók be. A Figyelmeztető üzenet fülnél megadhatjuk azt, hogy mi jelenjen meg, ha a felhasználó kijelöli a cellát, a Hibajelzésnél fülnél pedig azt, hogy hibás adatbeíráskor mi legyen az üzenet.
6. Összesítés: Ezzel a paranccsal összesítő műveleteket végezhetünk, az adatok tetszőleges területről (más munkalapokról, más táblákból) származhatnak, összesítő függvényként a már megszokottak választhatók ki.
7. Kimutatás vagy kimutatásdiagram A parancs kiválasztása után a Kimutatás varázsló segítségével készíthető el a kijelölt adattartomány kimutatásnézete. Az első lapon meg kell adni a kimutatás forrását (Excel lista vagy adatbázis) és a kimutatás típusát (Kimutatás és kimutatádiagram). A második lapon jelölje ki a tartományt ($A$1:$F$12). Az utolsó lapon azt kell megadni, hogy a kimutatásdiagram hol jelenjen meg: új munkalapon vagy létező, üres munkalapon. Ezt követően a kimutatás munkalapon vagy a kimutatásdiagram szerkesztőben készítse el a kimutatást.
Jelenítsük meg grafikonon Budapest népességének alakulását.
Jelenítsük meg grafikonon az egyes városok népességének változását a vizsgált időszakban. Ellenőrizzük a diagramhoz tartozó kimutatást!
MAKRÓKÉSZÍTÉS MS EXCEL-BEN A Microsoft Excel programban rendszeresen végzett feladatokat makróval automatikussá tehetjük. A makró olyan parancsok és függvények sorozata, melyeket Microsoft Visual Basic modulban tárolunk, és az adott feladat végrehajtásához bármikor futtathatunk. Függvény: Előre megírt képlet, amely megadott érték(ek) segítségével végrehajt egy műveletet, és egy vagy több értéket eredményez. A függvények segítségével a munkalapon lévő képleteket egyszerűsíthetjük és rövidíthetjük, főleg azokat, amelyek hosszú és összetett számításokat hajtanak végre. Modul: Egyetlen elnevezett egységként tárolt deklarációk, utasítások és eljárások gyűjteménye. Kétféle típusú modul létezik: a normál és az osztálymodul.
A makrók futtatásánál előfordulhat, hogy a biztonsági szintet át kell állítani. Ezt az Eszközök menü Makró pontján belül a Biztonság pontjánál tehetjük meg. Ha nincs aláírt makrónk, akkor érdemes közepesre állítani a biztonsági szintet.
A szint érvénybe lépéséhez újra kell indítani az Excelt, Közepes biztonsági szintnél engedélyezni kell a makrók használatát.
Az Eszközök menü Makró pontján belül a Makrók pontjánál tudjuk az egyes megírt makrókat indítani, lépésenként futtatni, szerkeszteni, vagy törölni.
A makrók használatát hatékonyabbá tehetjük vezérlőelemek (jelölőnégyzet, beviteli mező és lista, váltókapcsoló, gombok, listapanel) segítségével. A Nézet menüpont Eszköztárak pontján belül a Vezérlők eszközkészlete menüpontnál jeleníthetjük meg a vezérlők listáját.
Ha elhelyeztünk vezérlőt a munkalapon, akkor jobb gombbal a vezérlőre kattintva a Makróhozzárendelés menüpontnál rendelhetjük hozzá a makrót a vezérlőelemhez.
Makrók rögzítése: A makró rögzítésekor az Excel lépésenként tárolja az egyes parancsok végrehajtásának adatait. A makró futtatásakor megismételjük, vagy "visszajátsszuk" a parancssort. Ha a makró rögzítése során hibát vétünk, a javítások szintén rögzítésre kerülnek. A Visual Basic a munkafüzetekhez csatolt új modulban tárol minden egyes makrót.
Makrók kezelése: A makró rögzítése után a Visual Basic Editor programmal megtekinthetjük a makrókódot, és szükség szerint kijavíthatjuk a hibákat, vagy módosíthatjuk a makró működését. A Visual Basic Editor használata Megnyitásához mutasson az Eszközök menü Makró pontjára, majd kattintson a Visual Basic Editor parancsra. (Vagy nyomja le az ALT+F11 billentyűkombinációt.)
Adott makró megnyitásához mutasson az Eszközök menü Makró pontjára, majd kattintson a Makrók parancsra (vagy nyomja le az ALT+F8 billentyűkombinációt). A Makró párbeszédpanelen jelölje ki a szerkeszteni vagy megtekinteni kívánt makrót, majd a Szerkesztés gombra kattintva nyissa meg a Visual Basic Editor alkalmazást. A Visual Basic Editor alkalmazásban objektumorientált programozás útján hozhat létre saját programokat. Az objektumorientált programozási nyelvekben a számítógépkód minden vonatkozását a használt környezet objektumai határozzák meg; minden művelet és adat ezekbe az objektumokba van beágyazva. Az Excel alkalmazás objektumai lehetnek: munkafüzet, munkalap, cellatartomány vagy külső fájl. Az objektumok négy megjelenési formája képezi egy program vázát: a tulajdonságok, a metódusok, az események és a gyűjtemények. Példák: Elsődleges kulcs ellenőrzése: az elsődleges kulcsnak fontos tulajdonsága, hogy egyedi legyen. Az aktív cella az ellenőrizendő kulcsérténél legyen, majd az Elsődleges kulcs-e? feliratú parancsgomb megnyomása után bekéri a tartományt (pl.: A2:A7). Ezután a tartomány minden celláját egyenként végignézi, hogy szerepel-e már benne a leendő kulcs. Ha nem szerepel még ilyen kulcs, akkor az érték helyes.
Sub pri_key() ' pri_key Makró ' Dim selRange As String, rng, cell As Range, val As Integer
selRange = InputBox("Írd be a tartományt") Set rng = Range(selRange) val = 0 For Each cell In rng If cell.Value = Selection.Value Then cell.Font.ColorIndex = 3 val = val + 1 Else cell.Font.ColorIndex = 0 End If Next cell If val > 0 Then MsgBox "Nem egyedi" Else MsgBox "Az érték helyes!" End If End Sub Szűrés: a szűrést makró használatával még egyedibbé tehetjük. A szűrési mezőt a J4-es cellába (értékei az emberek tábla mezőnevei: szig, nev, varos, kor), az operátort a K4-es cellába, és a szűrési értéket az L4-es cellába kell írni. A Szűrés nyomógomb hatására a 4-8. sor háttérszínét kékre változtatja, ha a szűrési feltételnek az adott rekord megfelel.
Sub szures() ’ szures Makró Dim mezo, ertek, op, betujel As String, cell, rng As Range test = 0 mezo = Range(„J4”).Value op = Range(„K4”).Value ertek = Range(„L4”).Value betujel = IIf(mezo = „szig”, „A”, Iif(mezo = „nev”, „B”, Iif(mezo = „varos”, „C”, Iif(mezo = „kor”, „D”, „”)))) If betujel = „” Then MsgBox „Nem megfelelõ szûrõfeltétel: hibás mezõnév!” Else Set rng = Range(betujel & „4:” & betujel & „8”) i = 4 For Each cell In rng Select Case op Case „>” If cell.Value > ertek Then test = 1 End If Case „<” If cell.Value < ertek Then test = 1 End If
Case „=” If cell.Value = ertek Then test = 1 End If End Select If test = 1 Then Range(„A” & i & „:D” & i).Interior.ColorIndex = 5 test = 0 Else Range(„A” & i & „:D” & i).Interior.ColorIndex = 2 End If Range(„A” & i & „:D” & i).Borders(xlEdgeBottom).ColorIndex = 0 Range(„A” & i & „:D” & i).Borders(xlEdgeTop).ColorIndex = 0 i = i + 1 Next cell End If End Sub Join művelet: join művelet segítségével táblákat tudunk összefűzni. A Join nyomógomb hatására a D14 cellától kezdődően előállítja a szig mező alapján az összekapcsolt táblát.
Sub join() ' ' join Makró ' Dim tbl1, tbl2 As Range, t1, t2, cella As String Dim i, j, k, db As Integer t1 = InputBox("A tábla:") t2 = InputBox("B tábla:") Set tbl1 = Range(t1) Set tbl2 = Range(t2) db = ActiveSheet.Range(t1).Rows.Count ’A tábla rekorjainak száma k = 0 cella = ActiveCell.Address ’aktív cella címének lekérdezése Range("D14").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Selection.ClearContents ’tartalom törlése For i = 1 To db For j = 1 To ActiveSheet.Range(t2).Rows.Count If tbl1.Cells(i, 1) = tbl2.Cells(j, 1) Then ActiveSheet.Cells(14 + k, "D") = tbl1.Cells(i, 2) ActiveSheet.Cells(14 + k, "E") = tbl1.Cells(i, 3)
ActiveSheet.Cells(14 + k, "G") = tbl1.Cells(i, 4) ActiveSheet.Cells(14 + k, "H") = tbl2.Cells(j, 2) k = k + 1 End If Next j Next i Range(cella).Select End Sub
További segédletek: • Az Excel beépített súgója • ECDL Tudástár – Táblázatkezelés: http://hu.ecdlweb.org • A Microsoft Magyarország webportálján az Excel magyar nyelvű súgója: http://office.microsoft.com/hu-hu/excel
