Amiket eddig megtanultunk használni
1. MsgBox parancs
2. Makró rögzítő
3. Worksheets, Range, Cells, Select, Value
4. Változók, matematikai műveletek
5. „If” elágazás, logikai vizsgálatok
6. „For” ciklus
7. Tömbök
8. Több dimenziós tömbök
9. Funkciók
A következő leckék többségében ezeknek az elemeknek a segítségével fogjuk majd felépíteni az automatizált Excel riportjainkat. Aktívan fogjuk használni VBA tudásunkat, és kombinálni a fent említett elemeket.
Azonban mielőtt tovább lépünk gyakoroljunk! Oldjunk a következő gyakorló példát, és áldozzuk ezt az alkalmat az ismétlésnek!
Ismétlő feladat
A következő feladat egy összetett ismétlő feladat lesz, melyben átvesszük a legfontosabb tanultakat. A teljes feladat pontokra van bontva, az egyes pontok megoldását, a lecke végén találod.
0. Előkészület
Töltsük le az alábbi txt fájlt, ami egy előre elkészített adatbázist tartalmaz. Majd másoljuk be a tartalmát egy új Excel táblába, a munkalap neve legyen „Adatforrás” a „Munka1” helyett. Ez lesz a kiinduló pontunk.
1. Új munkalap
Hozzunk létre egy új munkalapot, amit nevezzünk el „Irányító panel”-nak. Erre fogjuk tenni a saját gombjainkat, amelyek a makróinkat fogják végrehajtani.
Első feladatunk legyen az, hogy helyezzünk el egy gombot ezen az új munkalapon, amelyet megnyomva automatikusan létrehozunk egy új munkalapot.
Segítség: Makrórögzítőt használva hozzuk létre az új munkalapot, majd az így kapott kódot tudjuk felhasználni. De ha még profibban akarod emgoldani akkor olvasd el ezt a posztot is.
2. ID alapján keresés
Nehezítünk, ha egyedül nem is sikerül megoldani, próbáld meg majd megérteni a működését!
Szeretnénk, hogy ha az „Irányító panel” A1 cellájába beírunk egy ID-t, akkor azt megkeresse nekünk a program. Ha van olyan ID az Adatforrás munkalap első oszlopában, akkor az újonnan létrehozandó munkalap A1 cellájába kiírja az adot ID-hez tatozó teljes nevet, ha nincs, akkor azt írja ki, hogy „Nincs ilyen ID”.
Segítség: Egy „For” ciklus fog kelleni, amivel az Adatforrás első oszlopában futtatjuk végig a sorokat. Minden sorban rákérdezünk egy „If” függvénnyel a keresett ID-re.
3. Adatlap készítése
Fejlesszük tovább ezt a programot. Az újonnan létrehozott munkalap neve legyen mindig az ID száma, és csak akkor szúrjunk be új munkalapot, ha létezik is az adott ID.
Az új munkalapon továbbra is A1 cella legyen a teljes név, viszont a többi részt töltsük ki ilyen módon:
Az adatlap értékeit az Adatforrásról először egy tömbbe tegyük bele, majd az új munkalapon ebből a tömbből olvassuk ki az adatokat, ezzel gyakorolva a tömbök használatát.
Segítség: Amikor az ID alapján keresünk az Adatbázis lapon, akkor az a megfelelő sor értékeit kell betennünk a tömbbe.
4. Statisztika készítése
A következő részfeladatban hozzunk létre egy új gombot az „Irányító panel” munkalapunkon.
Továbbá hozzunk létre, „Statisztika” néven egy új munkalapot is az „Adatforrás” után, és ott A1-től A4-ig írjuk be a cellákba a 4 féle forrást, ami az „Adatforrás” I oszlopban szerepel: Facebook hirdetés, Adwords hirdetés, Google organikus, Hírlevél.
Feladatunk, hogy az új gombunkra nyomva, a „Statiszika” lapon, a B oszlopban mindegyik forrás kategória mellé kiírjuk, hogy mennyi van belőle.
Így:
Segítség:Kell 4 változó, és használjunk ElseIf-t!
Megoldás
1. Új munkalap
Bekapcsoljuk a makrórögzítőt és beszúrunk egy új munkalapot. Ekkor a következő kódot kapjuk:
Sub Makró1() Sheets.Add After:=ActiveSheet End Sub
Ezt hozzá is rendelhetjük a gombunkhoz az „Irányító panel” munkalapon. Így a gombot megnyomva, automatikusan beszúr mindig egy új munkalapot.
2. ID alapján keresés
Sub Makró1() '1. Új munkalap létrehozása Sheets.Add After:=ActiveSheet '2. ID alapján keresés 'Egy változóba felvesszük az "Irányító panel "A1 cellájának értékét, mint keresett ID-t Dim keresett_id As Integer keresett_id = Worksheets("Irányító panel").Range("A1").Value 'Felveszünk egy változót, amiben azt fogjuk csak nézni, hogy létezik-e a keresett ID, vagy sem. Ennek tényleges értékét a "For" cikluson belül fogjuk felvenni Dim van_ilyen As Boolean 'Felveszünk még egy változót, amiben a keresett ID sorát fogjuk tárolni. Későbbiekben ezzel fogunk hivatkozni a szükséges cella sorára. Dim keresett_id_sora As Integer 'Most jön ténylegesen a keresés. Végig megyünk az "Adatbázis" sorain az 1. oszlopban, és ha az adott cella értéke egyenlő a keresett ID-val, akkor a van_ilyen változó "true" lesz, és elmentjük az éppen aktuális sor számát a "keresett_id_sora" változóba. For i = 2 To 32 If Worksheets("Adatforrás").Cells(i, 1).Value = keresett_id Then van_ilyen = True keresett_id_sora = i End If Next 'Ha pedig "true" a van_ilyen, akkor keresett ID sorát felhasználjuk, és az adott sorban, a megfelelő oszlop értékeit kiírjuk az új munkalapunkra. (Az új munkalapunk a 2. munkalap lesz, így hivatkozunk most rá.) If van_ilyen = True Then Worksheets(2).Range("A1").Value = Worksheets("Adatforrás").Cells(keresett_id_sora, 2).Value & " " & Worksheets("Adatforrás").Cells(keresett_id_sora, 3).Value Else Worksheets(2).Range("A1").Value = "Nincs ilyen ID" End If End Sub
3. Adatlap készítése
Íme a továbbfejlesztett kódunk.
Sub Makró1() Dim keresett_id As Integer Dim van_ilyen As Boolean Dim keresett_id_sora As Integer 'Létrehozzuk a tömböt. 'Mivel az Adatforrás lapon 9 oszlopban van érték, így első körben ezeket mind pakoljuk majd bele a tömbünkbe. Dim adatlap_tomb(1 To 9) As String keresett_id = Worksheets("Irányító panel").Range("A1").Value For i = 2 To 32 If Worksheets("Adatforrás").Cells(i, 1).Value = keresett_id Then van_ilyen = True keresett_id_sora = i 'Itt vagyunk a nekünk szükséges sorban. A sor elemeit tegyük bele a tömbbe. ' a "j" itt az tömb elemeinek sorszámát és az Adatforrás munkalap oszlopait is jelenti. Jelen esetben ez a kettő megegyezik egymással, azaz első oszlop a tömb első eleme, a 9. oszlop a tömb 9. eleme lesz. For j = 1 To 9 adatlap_tomb(j) = Worksheets("Adatforrás").Cells(i, j).Value Next End If Next If van_ilyen = True Then '3.1 Új munkalap beszúrása és átnevezése 'Lehoztuk ide az új munkalap beszúrását. Ha nem létezik a keresett ID, akkor ne is szúrjunk be új munkalapot Sheets.Add After:=ActiveSheet Worksheets(2).Name = keresett_id 'Teljes név kiírása A1 cellába Worksheets(2).Range("A1").Value = Worksheets("Adatforrás").Cells(keresett_id_sora, 2).Value & " " & Worksheets("Adatforrás").Cells(keresett_id_sora, 3).Value '3.2 Adatlap kitöltése 'Először is nevezzük el az első oszlop címkéit. Ezeket manuálisan tudjuk megtenni: Worksheets(2).Range("A2").Value = "Neme:" Worksheets(2).Range("A3").Value = "Vásárlás száma:" Worksheets(2).Range("A4").Value = "Összes költés:" Worksheets(2).Range("A5").Value = "Utolsó vásárlás értéke:" Worksheets(2).Range("A6").Value = "Utolsó vásárlás dátuma:" Worksheets(2).Range("A7").Value = "Átlag vásárlás értéke:" Worksheets(2).Range("A8").Value = "Forrás:" 'Ezután a második oszlop celláit fel tudjuk tölteni a megfelelő értékkel, amit a tömbünkből fogunk kinyerni Worksheets(2).Range("B2").Value = adatlap_tomb(4) Worksheets(2).Range("B3").Value = adatlap_tomb(5) Worksheets(2).Range("B4").Value = adatlap_tomb(6) Worksheets(2).Range("B5").Value = adatlap_tomb(7) Worksheets(2).Range("B6").Value = adatlap_tomb(8) 'Az átlag vásárlás értékénél a tömbünk két elemével végzünk műveletet Worksheets(2).Range("B7").Value = adatlap_tomb(6) / adatlap_tomb(5) Worksheets(2).Range("B8").Value = adatlap_tomb(9) Else 'Ha nem létezik az ID, akkor kiírjuk, hogy "Nincs ilyen ID" MsgBox "Nincs ilyen ID" End If End Sub
4. Statisztika készítése
Sub Statisztika_gomb() 'Létrehozok 4 változót, amiben az adott kategória darabszámát fogom számolni Dim facebook As Integer Dim adwords As Integer Dim google As Integer Dim hirlevel As Integer 'Ezeknek mind 0-t adok kezdőértéknek facebook = 0 adwords = 0 google = 0 hirlevel = 0 'Elkezdek végig menni a I oszlopban, ha amelyik kategóriával egyezik a cella értéke, annak a változóját növelem eggyel. For i = 2 To 32 If Worksheets("Adatforrás").Cells(i, 9).Value = "Facebook hirdetés" Then facebook = facebook + 1 ElseIf Worksheets("Adatforrás").Cells(i, 9).Value = "Adwords hirdetés" Then adwords = adwords + 1 ElseIf Worksheets("Adatforrás").Cells(i, 9).Value = "Google organikus" Then google = google + 1 ElseIf Worksheets("Adatforrás").Cells(i, 9).Value = "Hírlevél" Then hirlevel = hirlevel + 1 End If Next 'Ha végig futott az összes soron, akkor kiírom a változók értékét a "Statisztika" lapon a megfelelő helyre. Worksheets("Statisztika").Range("B1").Value = facebook Worksheets("Statisztika").Range("B2").Value = adwords Worksheets("Statisztika").Range("B3").Value = google Worksheets("Statisztika").Range("B4").Value = hirlevel End Sub
Zárószó
Ezzel befejeztük az első nagy fejezetet, melyben a VBA alapokkal ismerkedtünk. Persze ettől még nem lettél profi programozó, de nem is az volt a célunk. Hanem, hogy megismerd az Excel egy új oldalát, és alapdolgokat meg tudj oldani VBA-ban. Sok gyakorlás kell még, ezért javaslom, hogy ha van időd, fusd át újra az első 10 leckét, vagy a tanfolyam további részében is bátran nézd vissza azt a részt, amire szükséged van.
Egyelőre ennyi, remélem hasznos volt a 10 lecke. Bármilyen észrevételed, megjegyzésed, javaslatod van, bátran írj! A Kapcsolat oldalon fel tudod venni a kapcsolatot velem.
Excel VBA Gyakorlatok pdf – ha gyakorolni szeretnél mégIzgalamas feladatok kezdőtől haladóig – Ár: 9.990 HUFAkár tapasztalt Excel felhasználó vagy, aki bővíteni szeretné képességeit, akár csak kezdő, és ismerkedsz az automatizálással, ez a példatár útmutatást nyújt a Excel VBA programozás alapjaihoz, és segít fejleszteni a képességeidet lépésről lépésre. A belsejében számos gyakorlatot és a valós életből vett példákat találsz, amelyek akár ihletet is tudnak adni, milyen irányba tudod még jobban kihasználni az Excel VBA programozásban rejlett lehetőségeket. A gyakorlatokat úgy terveztem, hogy érdekesek, informatívak és legfontosabb, hasznosak legyenek! |