Excel konzultáció 5. Előadás Dr. Pauler Gá bor , egyetemi docens, ev. Adószám: 63673852-3-22

1. Excel konzultáció 5. Előadás Dr. Pauler Gábor, egyetemi docens, ev. Adószám: 63673852-3-22 Számlaszám: 50400113-11065546 Telephely: 7666 Pogány, Széchenyi u. 1. Tel: 30/9015-488 E-mail: pauler@t-online.hu

2. Az előadás tartalma Az Excel leegyszerűsített adatbáziskezelési eszközei • 1 Rekord = 1 munkafüzet módszer: munkafüzet-közi csatolások kezelése: • 1 munkalap = 1 rekord/űrlap módszer, 3 dimenzós cellahivatkozások • 1 munkalapsor = 1 rekord módszer: Listák használata 1 • Autoszűrők és irányított szűrők • Összesítések • Sorok/oszlopok csoportosítása és tagolása • Korlátozások listák használatakor • Listák megjelenése VB kódban • Listák értékelése • Belső SQL lekérdezések • Részösszegek • Egy táblás belső lekérdezések • Több táblás belső lekérdezések • Lekérdezések felhasználói szerkesztése • Lekérdezőtáblák külső adatforrásának felhasználói elérése • Egyszerű belinkelt Access űrlapok • Hierarchikus tagolást tartalmazó belinkelt Access nézettáblák • A belső SQL lekérdezések értékelése Excel-alapú, értékesíthető alkalmazások készítése • Excel munkafüzet (*.xls) értékesítése, védelmekkel ellátva • Excel munkafüzet mentése interaktív HTML-oldalként (*.htm) • Excel munkafüzet mentése Excel bővítményként (Add-In) (*.xla) • Példa bővítmények írására • VB Formok szerkesztése • VB Formok programozása • Bővítmények programozása • Bővítmények használata Szakirodalom

3. Az Excel leegyszerűsített adatbáziskezelési eszközei • A Session1-ben már említettük, hogy az Excellel elkövethető legsúlyosabb hibák egyike, mikor adatbázis-kezelő vagy ERP rendszer gyanánt próbálják használni, amire kapacitás-korlátai és eltérő célja miatt nem alkalmas • Azonban az Excel felkínál leegyszerűsített eszközöket, amelyekkel közvetlenül Excelben kezelhetünk adatbázis táblához hasonló szerkezeteket, amennyiben a rekordok száma nem túl magas. Nézzük meg ennek különféle módozatait: 1 Rekord = 1 munkafüzet módszer: munkafüzet-közi csatolások kezelése: • Ez létező legprimitívebb adatkezelési módszer Excelben: • Minden rekord egy külön (általában 1 munkalapos) munkafüzetet képez, amelyek azonos szerkezetben tartalmaznak adatbeviteli mezőket adatérvényesítésekkel (lásd: FajlRekord1.xls, FajlRekord2.xls), és a legtöbbször egy egyszerű felhasználó csak a saját rekordjához tartozó munkafüzetet nyithatja meg és módosíthatja jelszavas védelem révén, a Session2–ben ismertetett módon • Ezenkívül van egy összesítő munkafüzet (lásd: FajlRekordOsszesito.xls), ahova a rekord munkafüzetek adatait képező cellatartományok Szerkesztés|Irányított beillesztés|Csatol-va (Edit|Paste special|Paste link) menüvel csatolásként vannak beillesztve, és az össze-sítő munkafüzet megnyitásakor kívülről szednek fel adatokat, a rekord-munkafüzetekre történő hivatkozások révén: ='D:\PTE-PMMFK\Excel Konzultacio\Session5\ [FajlRekord1.xls]File Record1|FajlRekord1'!H5 • Az összesítő munkafüzet ál- talában egy külön rendszer- gazdai jelszóval nyitható csak meg, a sima felhasz- nálók számára nem látható, de a rekord-munkafüzetek- ből a csatolásokkal azok jel- szavas védelme ellenére is ki tud szedni adatot • Ügyeljünk rá, hogy a hivat- kozások mindig abszolút elérési úton érik el a mun- katt

4. 1 Rekord = 1 munkafüzet módszer: munkafüzet-közi csatolások kezelése kafüzeteket, ezért a rendszer más gépre telepítve csak azonos könyvtárstruktúrában mű-ködik. Ha azt szeretnénk, hogy az összes csatolás elérési útja egy szöveges paraméter (tegyük fel, hogy az ElerUt nevű cellában van) segítségével állítható legyen, használjuk az =Indirekt(Szöveg) (=Indirect(Text)) függvényt, ami megfelelő formátumú!!! szö-veget hivatkozássá konvertál, pl.: =Indirekt(„’”&ElerUt&” [FajlRekord1.xls]FileRecord1|FajlRekord1'!H5”) • Ekkor azonban elesünk attól a lehetőségtől, amit az Excel a csatolások központi kezelésére felkínál a Szerkesztés|Csatolások (Edit|Links) menüben: • A Váltás (Modify) gombbal váltható a listából kiválasztott csatolás forrás-fájl másra • A Megszakítás (Detach) gombbal választható le a forrás, ekkor a csatolt adatokból konstans értékek lesznek • A Frissítés (Refresh)-nél állítható az Automatikus/Manuális frissítésük • Az Inditó rákérdezés (Startup query) gombbal állítható, hogy az Excel nyitáskor próbálja-e őket frisssíteni • Az Állapot ellenőrzése (Check status) gombbal ellenőrizhető, hogy melyik forrásfájl érhető el és melyiket nem találja • A csatolásos adatkezelés értékelése: • Egyszerű létrehozni • Rekordonként szabályozható a felhasználók hozzáférése • Minden rekord egy rugalmasan formázható munkafüzet-munkalap, adatérvényesítésekkel • Iszonyatos erőforrás pazarlás, minden rekordra egy munkafüzet fájlt elhasználni • Csak manuálisan karbantartható a csatolás kezelőben • MAXIMUM!!! 10-12 rekordig használható primitív megoldás! katt katt katt katt katt

5. Megyeri Jolán Kiss János Összesítő katt 1 munkalap = 1 rekord/űrlap módszer, 3 dimenzós cellahivatkozások katt katt katt • Ebben a megoldásban a rekordok 1 munkafüzet (MunkalapRekord.xls) szigorúan azonos szerkezetű lapjai (azonos mintáról másoljuk őket) inputokkal, validációval • A munkalapok neve a rekordok elsődleges kulcsa (pl. Név ha nincs ismétlődés) • A lapok a szokott eszközökkel szerkeszthetők: Szerkesztés|Lap törlés/áthelye-zés/másolás (Edit|Delete/Move/Copy Sheet) és Beszúrás|Lap (Insert|Sheet) • Az összesítő lap 3 dimenziós tartomány-hivatkozásokat (3D range references) al-kalmazó összesítő függvényekkel (Min,Max,Átlag,Szum,Darab) összesíti: ‘KezdoLap:VegLap’!Tartomany – (a munkafüzet lapjai 1 sorba rendezett kollekciót alkotnak, a sorrend e szerint értendő!) • A kezdő/véglap mindig ab- szolút, a tartomány lehet ab- szolút és relatív, de tarto- mányneveket nem használ- hatunk benne (pl.ID,Gender) • A lapok celláinak egyedi hi- vatkozásánál, ha több lapon azonos tartománynevek van- nak, a lapnévvel egyértelmű- sítünk:LapNev!TartomNev • Ha grafikai-tördelési okok mi- att egyesített cellákra hivatko- zunk névvel, ezt mindig az =Index(Tartom;[Sor];[Oszlop]) függvénnyeltesszük, mert e- zek tartományként látszanak, aminek bal felső cellája él! A módszer értékelése: • Egyszerű és szemléletes, mélyen szőkék is megértik • 1 fájlban van minden • A hozzáférés rekordonként nem szabályozható • Erőforrás-pazarló,a rekordok szűrése/rendezése csak ma- nuális lehet,max.50 rekordig! =INDEX('Megyeri Jolán'!ID;1) =ÁTLAG('Megyeri Jolán:Kiss János'!H12)

6. 5-1.Gyakorló feladat:

7. Az előadás tartalma Az Excel leegyszerűsített adatbáziskezelési eszközei • 1 Rekord = 1 munkafüzet módszer: munkafüzet-közi csatolások kezelése: • 1 munkalap = 1 rekord/űrlap módszer, 3 dimenzós cellahivatkozások • 1 munkalapsor = 1 rekord módszer: Listák használata 1 • Autoszűrők és irányított szűrők • Összesítések • Sorok/oszlopok csoportosítása és tagolása • Korlátozások listák használatakor • Listák megjelenése VB kódban • Listák értékelése • Belső SQL lekérdezések • Részösszegek • Egy táblás belső lekérdezések • Több táblás belső lekérdezések • Lekérdezések felhasználói szerkesztése • Lekérdezőtáblák külső adatforrásának felhasználói elérése • Egyszerű belinkelt Access űrlapok • Hierarchikus tagolást tartalmazó belinkelt Access nézettáblák • A belső SQL lekérdezések értékelése Excel-alapú, értékesíthető alkalmazások készítése • Excel munkafüzet (*.xls) értékesítése, védelmekkel ellátva • Excel munkafüzet mentése interaktív HTML-oldalként (*.htm) • Excel munkafüzet mentése Excel bővítményként (Add-In) (*.xla) • Példa bővítmények írására • VB Formok szerkesztése • VB Formok programozása • Bővítmények programozása • Bővítmények használata Szakirodalom

8. 1 munkalapsor = 1 rekord módszer: Listák használata 1 katt katt Egy adatbázistábla funkcióit ellátó Lista(List)ob- jektum Adatok|Lista|Létrehoz(Data|List|Crea- te) menüvel definiálható bármely adatbázis(Da- tabase) formátumú cellatartományra (lásd a ListaMinta.xls fájl List|Lista munkalapját): • Felső sorában egyedi, kitöltött mezőnevek legyenek, ezt állítsuk VáltozóID stílusra, legyen Fields a neve • Minden sora 1 rekord, az 1.sorban ne legyen hiányzó érték, mert innen állapítja meg a mezők típusát.A so-rokat célszerű input stílusúra formázni Erre a listatartományt beolvassa a listaobjektum tárterületére,bekeretezi kékkel,amit át lehet mé- retezni a bal alsó sarok( )húzásával,hozzáad 1 -al jelölt új rekord beviteli sort, valamint: • Autoszűrőt rak a tartományára,amit lista nél- kül is be lehet állítani adatbázis tartományra Adatok|Szűrő|Autoszűrő(Data|Filter|Auto) menüvel.Emiatt a mezőneveknél menük jön- nek létre a mezőnkénti szűrésre/rendezésre Nézet|Eszköztárak|Lista(View|Toolbars|List) menüvel bekapcsolható a lista eszközsor, ahol: • Összegsor gombbal ad +1 sort, amiben a mezők tartalmát összesíteni tudja(Min,Max Átlag,Darab,Összeg,stb.)kézzel nem módosítható =Részösszeg(AggregacKod;AggregTartomany) függvények segítségével (a típusa váltható menüből) • Az eszközsor Lista(List) menüjében: • Beszúrás/Törlés|Sor/Oszlop(Insert/Delete| Row/Column)-el adhat/törölhet rekordot/mezőt • Lista átméretez(Resize List)-el egyszerre több rekordot adhat/dobhat el a lista végéről • Űrlap(Form)-al egy egyszerű űrlapot mutat a lista rekordjainak egyenkénti szerkesztésére • Átalakítás tartománnyá(Convert to range)-val a listát visszaalakítja egyszerű cellatartománnyá katt katt katt katt katt katt katt katt húz =Részösszeg(101;F15:F12639) katt katt katt katt katt katt katt katt katt

9. katt 1 munkalapsor = 1 rekord módszer: Listák használata 2 katt katt katt Szándékos Excel-butítás: a Lista - a külső lekérdezésekkel ellentét-ben - nem hoz létre dinamikus nevesített cellatartományt. Ezt hozzuk létre mi (pl.List néven, lásd:Lesson2), mert megkönnyíti a későbbieket: • Az autoszűrő nem képes egyszerre több mező több értéke szerint szűr-ni. Ezen segít az Adatok|Szűrő|Irányított szűrő(Data|Filter|Special): • Adjuk meg, hogy helyben szűrjön, és a lista tartományt (List) • Adjuk meg a szűrőtartományt (Criteria range): tartomány, aminek: • Első sorában mezőnevek vannak (célszerű a lista mezőnév-tartományára (Fields) felszedni őket), VáltozóID stílusban • Alatta 1 vagy több feltételi sor, melynek celláiban konstans ér-tékek és <>= relációk lehetnek, melyek soron belül ÉS(AND) logikai operátorral,sorok közt VAGY (OR)-ral kapcsolódnak(pl. (CatCode =167 OR 111) And UnitPrice >= 1.00) • A feltételi sorok paraméter stílusúak • A szűrőtartományt automatikusan el- nevezi, de átnevezhető (pl.Criteria) • OK gombra a listaszűrés életbe lép Szándékos Excel-butítás:a szűrőtartomány változtatására a listaszűrés nem frissül automa- tikusan,újra le kell futtatni kézzel vagy makróból • Minden fajta szűrés az Adatok|Szűrő|Min- den látszik(Data|Filter| Show all)-lal iktatható ki • Adatbázis függvényekkel (Database functions)a lista adott mezőjénél a szűrőtar- tománynak megfelelő rekor- dok értékét összesíthetjük: =AB.Min/Max/Szum/Átlag( Lista;MezoNev;SzuroTart) =DB.Min/Max/Sum/Average( List;FieldName;FltRange) • Ezek frissülnek szűrőtarto- mány-változtatás esetén! katt =Fields =Fields =AB.ÁTLAG(List;F$10;Criteria)

10. 1 munkalapsor = 1 rekord módszer: Listák használata 3 Az adatbázis függvényekkel nem oldható meg 1 lépésben a lista vagy sima cellatartományok csoportosított összesítése, ez 1 új üres lap 1 cellájának kijelölése után Adatok|Összesítés (Data|Aggregate) menüvel oldható meg: • Függvény:az összesítés típusa(Min,Átlag..) • Hivatkozás: első sorában mezőnevek, bal oszlopa legyen a csoportosító mező, a töb-bi az összesítendő,Hozzáadás gomb nyom • Feliratok a bal oszlopból+felső sorból • Legyen csatolva az adatforráshoz, ne kon-stansként számolja ki!Abszolút cellahivat-kozásokból és sima Szum,Átlag,Min,Max függvényekből építi fel az összesítő táblát • Az összegző sorok közti adatforrás sorokat csoportba foglalja és elrejti, de kinyithatók • Az összesítő táblából lehet egy második listát csinálni, ennek rendezésekor az összegző sort együtt mozgatja a hozzá tartozó forrás sor csoporttal • Az összesítő tábla tartalma nem változik ha adatforrása lista és azt éppen megszűrjük Sorokat/oszlopokat az összesítéstől függetle- nül kézzel is csoportosíthatunk, kijelölésük után az Adatok|Tagolás|Csoportba foglal(Data| Sections|Group) menüvel: • A csoportba foglalt sorok/oszlopok(+/-) gombbal nyithatók/zárhatók, ami az alattuk lévő sorban foglal helyet, ahova a csopor-tot összesítő függvényt írhatunk • Sor/oszlop csoportok hierarchikusan egymásba ágyazhatók több szinten • A tagolás Csoportbontás(Ungroup) menüvel távolítható el Szándékos Excel-butítás:a tagolást is zárja a lapvédelem, a tagolt sorok katt katt katt katt katt katt katt katt =ÁTLAG(B6:B13) katt Lásd:ListaMinta.xlsAggrList| AggrLista munkalap katt katt katt

11. Sorok/oszlopok tagolása nem nyithatók/zárhatók +/- gombbal! Sajnos a tagolást kifelejtették az Eszközök|Védelem|Lapvédelem (Tools|Lock|Worksheet) menü be- állításaiból, míg pl. a kimutatásokra állítható, hogy a védelem érvényes legyen-e rá vagy nem! • A tagolás hasznosságára a BusinessPlanner.xls fájl Prod1 UnitCost|Term1EgysKölts mun -kalapján látunk példát: bonyolul-tabb üzleti tervekben az egység-költségek szerkezete nem fix szintszámú hierarchiát alkot,ami-nek áttekinthető kezelése sok fő-fájást okoz a közgazdászoknak, óriási táblázatok lesznek belőle: • Azonban tagolás segítségével alapból csak az összegsorokat mutatjuk, és a felhasználó egy kattintással ki tudja nyitni a hier-archia őt érdeklő részét. katt

12. 1 munkalapsor = 1 rekord módszer: Listák használata 4 A listák használata jelentette korlátozások: • Nem lehet listát tenni webes lekérdezés, külső adatbázis lekérdezés és kimutatás cellatar-tományára, még ha adatbázis formátumúak is. A listavarázsló ilyenkor felkínálja, hogy konstans cellaértékekké alakítja őket • A listától jobbra és balra ne tegyünk mást a munkalapra, mert szűréskor elrejt sorokat, törléskor töröl, beszúráskor létrehoz, rendezéskor áthelyezi, széttördelné a tartalmat • A lista fölötti sorokba mindig rakhatunk dolgokat, célszerű pl. a mezők leírását odarakni • A lista alá akkor rakjunk dolgokat, ha nem zavaró,hogy sorok beszúrásánál lejjebb csúszhat • Listába sorai közé nem lehet részösszegeket tenni Adatok|Részösszeg(Data|Subtotal)-el A listaobjektum megjelenése Visual Basic kódban: With Range("List") ‘Vagy Listobjects("Lista1") .AutoFilter 'Autoszűrő bekapcsolása .AdvancedFilter _ Action:=xlFilterInPlace, _ CriteriaRange:=Range("Criteria"), _ Unique:=False 'Irányított szűrő bekapcsolása ActiveSheet.ShowAllData 'Szűrők kikapcsolása .Sort _ Key1:=Range("C14"), _ Order1:=xlDescending 'Rendezés adott oszlop szerint .AutoFilter _ Field:=3, _ Criteria1:="110" '3.mező 110-re szűr ActiveSheet.ShowDataForm 'Lista űrlap bekapcsolása Range("A12371").Select ActiveCell.FormulaR1C1 = "22" 'Listába adatot ír .ListRows(12357).Delete 'Listasor törlése .Unlist ‘Lista visszalakítása cellatartománnyá End With ‘Range("List") Az Excel Lista értékelése: • Teljes szerkesztést, automatikus és irányított szűrést, egyedi aggregációs számításokat tesz lehetővé egy adatbázis táblára • Nem tud többszörös és szűréssel kombinált csoportosított (GROUP BY) számításokat • Nem lehet több listát össze joinolni • Max. 65532 rekordig működik, a maximális munkalap-sorszámon nem lóghat túl

13. 5-2.Gyakorló feladat: Oly sok szőke nős vicc után, végre 1 feladat lányoknak • Minden nő 4 éves kora óta az esküvőjét tervezi. Itt az ideje, hogy hierarchikus sortagolást és összesítéseket tartalmazó költségvetés-listát készítsen hozzá Excelben! • Nehogy kifelejtsen pár apró tételt a listából, mellékelünk egy kis segítséget: Checklist.pdf • Hímnemű hallgatóknak a feladat ugyanez, mert a nők úgy is lepasszolják a feladatot! • A megoldás: 5-2GyakorloMegoldas.xls

14. Az előadás tartalma Az Excel leegyszerűsített adatbáziskezelési eszközei • 1 Rekord = 1 munkafüzet módszer: munkafüzet-közi csatolások kezelése: • 1 munkalap = 1 rekord/űrlap módszer, 3 dimenzós cellahivatkozások • 1 munkalapsor = 1 rekord módszer: Listák használata 1 • Autoszűrők és irányított szűrők • Összesítések • Sorok/oszlopok csoportosítása és tagolása • Korlátozások listák használatakor • Listák megjelenése VB kódban • Listák értékelése • Belső SQL lekérdezések • Részösszegek • Egy táblás belső lekérdezések • Több táblás belső lekérdezések • Lekérdezések felhasználói szerkesztése • Lekérdezőtáblák külső adatforrásának felhasználói elérése • Egyszerű belinkelt Access űrlapok • Hierarchikus tagolást tartalmazó belinkelt Access nézettáblák • A belső SQL lekérdezések értékelése Excel-alapú, értékesíthető alkalmazások készítése • Excel munkafüzet (*.xls) értékesítése, védelmekkel ellátva • Excel munkafüzet mentése interaktív HTML-oldalként (*.htm) • Excel munkafüzet mentése Excel bővítményként (Add-In) (*.xla) • Példa bővítmények írására • VB Formok szerkesztése • VB Formok programozása • Bővítmények programozása • Bővítmények használata Szakirodalom

15. Belső SQL lekérdezések 1: A részösszegek használata • Az adatbázis formátumú cellatartományokra vagy a belőlük készült listákra alkalmazható adatbázis függvények, összesítések egyszerűek, de viszonylag rugalmatlanok az SQL le-kérdező nyelv lehetőségeihez képest (lásd Session4). Felvetődik a kérdés, hogyan hasz-nálhatnánk Excelen belül az SQL-t, amikor nincs kedvünk meg energiánk Ofszet(Offset) és Hol.van(Match) függvényekből összelegózni többszörös csoportosításokat, szűrése-ket, aggregációkat tartalmazó számításokat • A megoldást az Adatok|Külső adatok importálása|Új adatbázis lekérdezés(Data|Import external data|New database query) menüvel létrehozható lekérdezőtábla(QueryTable) objektum (lásd Session4) adja, amelyet most oly módon fogunk használni, hogy adatbázis formátumú munkalap adatforrásból kérdezzük le az eredményt egy másik munkalapra • Mivel – a Listával ellentétben – a lekérdezőtábla adatforrása lehet egy másik lekérdezőtábla tartalma, példánkban (ListaMinta.xlsQuery|Lekerdez munkalap) olyan adatforrást muta-tunk be, amely a Session4–ben már ismertetett 4-4Pelda.mdb nevű, áruházi termék-kate-gorizálási Access adatbázis Products tábláját kérdezi le Excelbe: • Adatbázis formátumú cellatartományban,vagy külső lekérdezés eredménytartományában a részösszegek sokkal rugalmasabban hasz- nálhatók, mint a listában, tartomány kijelölés után Adatok|Részösszeg(Data|Subtotal)-al: • Csoportosítási alap: csoportosító mező • Függvény: aggregáció típusát megadni • Összegzendő oszlopok: kiválasztani Erre a forrástartományba részösszeg sorokat szúr be, ezekbe részösszeg függvényeket ír, a hozzájuk tartozó forrás-sorokra tagolást rak • Ha voltak korábbi részösszegek, rész- összegek lecserélése üzemmódban e- zeket lecseréli, különben melléjük rakja Az egyes részösszegeket a függvények részösszegkód paramétere alapján azonosítja,így ezt kézzel ne írjuk át: =Részösszeg(RészösszKód;ÖsszesítTartomány) • Összes eltávolításra a részösszegsorokat + a tagolást leszedi Szándékos Excel-butítás:a részösszeg nem csoportosít auto- matikusan! Ha pl. a csoportosítómező azonos értékei nem egymás után vannak, több részösszeget képez a folytonos részekre! Ezért mindig rendezzük előtte a csoportosító mezőt Autoszűrővel!!! =RÉSZÖSSZEG(1;F2:F3) katt katt katt katt katt katt katt katt

16. Belső SQL lekérdezések 2: egy táblás lekérdezések katt katt katt Szándékos Excel-butítás:a lekérdezőtábla frissítésekor törli minden részösszeg sorát! Eme korlátozásokat elkerülhetjük belső SQL-el: • A varázslóban kérjünk Excel fájl adatforrást • Adjuk meg az adott fájlt (ListaMinta.xls). Ez-után már ne változtassuk az elérési utját, mert a belső lekérdezés sem fog működni! • Ezután válasszuk ki a forrás adatbázis táb-la formátumú tartományt a listából Szándékos Excel-butítás:a listában nem látszik semmi,amíg a Beállítások gombnál be nem jelöljük a Rendszertáblákat Szándékos Excel-butítás:a listában felso-rolja a nevesített cellatartományokat, de nem hajlandó adatot felolvasni belőlük! Csak olyan munkalapról olvas adatot, ami teljes egészében adatbázistábla-formátumú (pl. Query|Lekérdez). Gond, hogy így nem lehet a munkalapnak fejléce, de tudunk neki ál-fejlécet csinálni,ha más lap fejlécét vágó-lapra másoljuk,és bitképként beillesztjük a megmagasított 1. sorba, ahol a cellatartal-mat függőlegesen alulra igazítjuk Szándékos Excel-butítás:az MS Query-ben nehéz grafikus tervezőben csoportosí-tást csinálni, egyszerűbb SQL kódból! katt katt katt katt katt húz katt húz

17. húz Belső SQL 3: több táblás lekérdezések katt húz katt kat kat • A lekérdezőtábla eredménye már bármilyen for-mátú munkalapra mehet, (kivéve, ha másik lekér-dezőtábla adatforrása lesz) de figyeljünk rá, hogy ne tegyünk alá és tőle jobbra dolgokat, mert a lekérdezés változó számú rekordot/mezőt hozhat vissza az SQL kódtól függően, és felülírja őket! Szándékos Excel-butítás: ha a lekérdezőtábla adatforrása is lekérdezőtábla, a forrás lekérdezés nem frissül automatikusan a ráépülő lekérdezé-s(ek) frissítésére, ezt oldjuk meg makróból! • Továbbá figyeljünk rá, hogy a forrásba lehetnek részösszeg sorok beszúrva. Ez akkor nem zavarja ráépülő SQL lekérdezésünket, ha egy feltétellel kiszűrjük őket, pl. a részösszeg sorok valamely mezőjének üres volta alapján: WHERE CatCode Is Not Null • Egy SQL lekérdezésben ugyanazon forrás Excel fájlból több táblát is felhasználhatunk (legyenek mind külön adatbázistábla-formátumú lapokon), ha a grafikus szerkesztőben( )Tábla beszúrása gombnál megadunk egy másik táblát is. Ekkor az Accesshez hasonló módon, a táblák összecsatol-hatók, a csatolási vonalra duplán kattintva állítha-tó a csatolás fajtája (Inner/Left/Right Join). A FROM részbe írt csatolási SQL-kódhoz itt tényleg ne nyúljunk kézzel, mert a szintaxisa még bonyo-lultabb, mint Accessben: itt a mező/tábla neveket kétféle idézőjel közé kell tenni, és a táblák meg-adása igen bonyolult, mindig grafikusan csináljuk Szándékos Excel-butítás: rendkívül idegesítő, hogy ha a lekérdezéstervező varázslót újraindít-juk, totálisan elfelejti az addig beállított lekérde-zést, kezdhetjük elölről,módosítás helyett.Lássuk hogyan lehet ezt kivédeni 2 pici makróval: katt katt katt katt katt katt

18. Belső SQL lekérdezések 4: lekérdezések felhasználói szerkesztése katt katt katt • A ListaMinta.xlsAggrQuery|AggrLekerd munkalapján először létrehozunk a fentiek alapján varázslóval egy 1 tábblás aggregáló SQL-t futtató táblát • Nézet|Eszközök|Vezérlők(View|Tools|Controls) eszközsorról berakunk a munkalapra 2 ParancsGomb(CommandButton) ActiveX vezérlőt: • CommandButtonRun, CommandButtonEdit néven Run, Edit címmel • Valamint 1SzövegDoboz(Text-Box)-t az alábbi beállításokkal: • TextBox1 néven,Multiline=True-legyen többsoros,Enter/TabKeyBe-havior=True-Fogadjon el Entert/Tabot is,SelectionMargin=False-ne legyen margó,TextAlign=Left-szöveg balra,WordWrap=True-tördelés • Írjuk meg az alábbi 2 eseménykezelő makrót a gomborkhoz: • Edit-re a Textbox.Text átveszi a lekérdezőtábla .CommandText-et • Ekkor a szövegdobozban szerkeszthetjük, tabulálhatjuk az SQL kódot • Run-ra visszamá- solja a szöveget a .CommandText-be és frissíti a lekérde- zőtáblát. Az SQL Enterekkel és Tab- okkal is szépen lefut! katt katt Private Sub CommandButtonRun_Click() ActiveSheet.QueryTables(1).CommandText = TextBox1.Text ActiveSheet.QueryTables(1).Refresh BackgroundQuery:=False End Sub Private Sub CommandButtonEdit_Click() TextBox1.Text = ActiveSheet.QueryTables(1).CommandText End Sub katt katt katt

19. 5-3.Gyakorló feladat:

20. Az előadás tartalma Az Excel leegyszerűsített adatbáziskezelési eszközei • 1 Rekord = 1 munkafüzet módszer: munkafüzet-közi csatolások kezelése: • 1 munkalap = 1 rekord/űrlap módszer, 3 dimenzós cellahivatkozások • 1 munkalapsor = 1 rekord módszer: Listák használata 1 • Autoszűrők és irányított szűrők • Összesítések • Sorok/oszlopok csoportosítása és tagolása • Korlátozások listák használatakor • Listák megjelenése VB kódban • Listák értékelése • Belső SQL lekérdezések • Részösszegek • Egy táblás belső lekérdezések • Több táblás belső lekérdezések • Lekérdezések felhasználói szerkesztése • Lekérdezőtáblák külső adatforrásának felhasználói elérése • Egyszerű belinkelt Access űrlapok • Hierarchikus tagolást tartalmazó belinkelt Access nézettáblák • A belső SQL lekérdezések értékelése Excel-alapú, értékesíthető alkalmazások készítése • Excel munkafüzet (*.xls) értékesítése, védelmekkel ellátva • Excel munkafüzet mentése interaktív HTML-oldalként (*.htm) • Excel munkafüzet mentése Excel bővítményként (Add-In) (*.xla) • Példa bővítmények írására • VB Formok szerkesztése • VB Formok programozása • Bővítmények programozása • Bővítmények használata Szakirodalom

21. katt Lekérdezőtáblák külső adatforrásának felhasználói elérése katt katt katt Adatbázis formátumú cellatartományok vagy listák tartalmát a felhasználó Adatok|Űrlap(Data|Form) menüvel indítható űrlapokon keresztül hülye- biztos módon tudta elérni (nem tudta törléssel/beszúrással tönkretenni). Ha a lekérdezőtábla adatforrása külső adatbázistábla, arra ez a lehetőség nem alkalmazható. Ehelyett, az adatforrás (lásd:4-4PeldaUrlapos.mdb) adatbázist úgy állítjuk be, hogy a forrástáblát mutató űrlappal induljon: • Hozzunk létre az Access Űrlap|Létrehozás varázslóval (Forms|Cre-ate with wizard) menüjével egy egyszerű oszlopos szerkezetű űrla-pot a Lesson1-ben írtak szerint(pl.Products űrlap a Products táblára) • Az Eszközök|Indítás(Tools|Startup) menüjében megadjuk, hogy az alkalmazás a Products űrlappal induljon, és kikapcsoljuk az Access adatbázis bármely más elemének elérését a Spec.billentyűk(Hotkey) kivételével. Ekkor a felhasználó csak az adott űrlapot szerkesztheti, máshoz nem fér hozzá, kiv. ha a fájl nyitása közben Shift-et nyom.(Ha a spec.billentyűket is tiltjuk, akkor ezzel sem férhet hozzá máshoz) • Az Excel Beszúrás|Hiperhivatkozás(Insert|Hyperlink) menüjével egy munkalapcellába hiperhivatkozást szúrunk be, ami meghívja a 4-4PeldaUrlapos.mdb fájlt. Ha a felhasználó erre kattint, az Access fájl Products űrlapja futni kezd, és szerkesztheti vele a Products tábla tartalmát, majd a le- kérdezőtbla frissíté- sével az új adatokat is be tudja vonni a számításokba Szándékos Access butítás: az űrlapot ilyenkor mindig tel- jes képernyősen hozza ki, mégha Accessben be is ál- lítjuk, hogy ne le- gyen az, és teljesen eltakarja az Excel ablakot, ami zavaró katt katt katt katt katt katt katt katt katt katt katt katt katt katt katt katt katt katt

22. Items # ItemCode * Level * Aggr * Sign * ItemDesc * Quantity * Unit * Basis * Value * Date * Refreshed * ParentItem Rekord-tagolás adatbázis űrlapjában 1 Belinkelt, speciálisan formázott Access űrlapok segítségével feloldható az Excel sor/oszlop tagolásának azon hiányos-sága, hogy a lapvédelem zárja őket és nem használhatók, de védelem híján a felhasználó eltávolíthatja őket: • Logikailag a BusinessPlanner.xlsProd1UnitCost táblájá-ban lévő nem fix szintszámú hierarchia megjelenítéséhez elég egy tábla önmagára mutató 1:több kapcsolata(rekur-zív dimenzió a Lesson4-ben) a következő mezőkkel: Item-Code-költségtétel-kód, elsődleges kulcs,Level-hierarchia szintjelző,Aggr-aggregációs mód,Sign-tétel előjele(-1/+1) ItemDesc-leírás,Quantity-mennyiség,Unit-mértékegység Basis-vetítési alap,Value-költség érték,Date-dátum,Refr-eshed-frissítve flag,ParentItem-idegen kulcs ItemCode-ra • Azonban fizikailag az adatbázisban (UnitCost.mdb) ehhez még van 4 kisebb táblánk (Levels,Aggr,Units,Bases), a-melyek lookup táblaként szolgálnak a fontosabb mezőket kitöltő legördülő listákhoz (lásd:Lesson1). A Levels tábla Level elsődleges kulcsa olyan stringekből áll, amelyek az Item táblában kirajzolják egy fa szerkezetben a költségté-telek hierarchiáját,a LevelNum a szint számát adja meg • Az Item sorainak tagolása segédtáblázatokkal(Subtable) történik: a segéd/alűrlaphoz hasonló módon a főtábla:se-géd tábla 1:több kapcsolatban van, de egyszerűbb létre- hozni, mert nem kellenek hozzá űrlapok,hanem egy tábla/ lekérdezés adattábla nézetében lehet beállítani, és egy-szerre több főrekordhoz kapcsolódó alrekordokat tudja mutatni, még az alűrlapos megoldás csak egynél mutatja

23. Rekord-tagolás adatbázis űrlapjában 2 • A gondot az okozza hogy Accessben egy tábla nem lehet a saját segédtáblája is, márpedig itt az önmagára mutató 1:több kapcsolat miatt erre lenne szükség • Ezt úgy oldjuk fel, hogy a hierarchia minden szintjéhez készítünk egy lekérdezést, amely az Item összes me-ző jéhez hozzácsatolja a szintszámot a Levels-ből, és erre szűrést végez adott szint szerint (ItemLevel1..4) • A lekérdezések nézettábláit a kapcsolati diagrammon sorba left joinoljuk (ld.Session4), úgy, hogy mindig a felsőbb szinthez tartozó tábla van a bal oldalon, hogy a főrekordot akkor is megjelenítse,ha nincsenek alatta alrekordok. • Ezután minden szinten beállítjuk az alatta lé- vő szint nézettábláját segédtáblának a Be- szúrás|Segédadatlap(Insert|Subtable)-vel: • Kiválasztjuk a segédLekérdezést • A főlekérdezésből mi legyen a csatoló mező: az elsődleges kulcs: ItemCode • A segédből mi csatoljon: ParentItem • Erre a segédtáblák a főrekordokból az előt- tük lévő (+/-) gombokkal nyithatók/zárhatók katt katt SELECT Items.* FROM Levels INNER JOIN Items ON Levels.Level =Items.Level WHERE Levels. LevelNum=1; katt katt katt katt katt katt katt

24. katt Rekord-tagolás adatbázis űrlapjában 3 katt katt katt katt • Ha azt szeretnénk, hogy az Access fájl in-dulásakor rögtön az ItemLevel1 nézettábla jöjjön fel, gondot okoz, hogy ez nem egy űr-lap amit meg tudnánk adni kezdőnek az Eszközök|Indítás(Tools|Startup)menüben • Ezért a Makró|Új(Macros|New)fülnél létre-hozunk egy makrót, aminek 1 lépése lesz: • LekérdezésMegnyitása,ily beállítással: • Lekérdezés: ItemLevel1 • Nézet: Adatlap, Hozzáfér: Szerk. • Ezután a Lesson1-ben ismertett módon lét-rehozunk egy kapcsolótáblát, teszünk rá 1 kilépőgombot, meg egy olyan gombot ami a makrót futtatja, és így érjük el célukat: katt katt katt katt katt

25. A belső SQL lekérdezések értékelése • Nagy teljesítményű, viszonylag egyszerű számítások, kevés kódolással SQL-ben • A lekérdezőtábla csak akkor terheli a gép erőforrásait ha frissül (makróból ezt szépen lehet szabályozni), még a helyette használható cellaképletek tízezrei bármely bemenő cellájuk bármely előző bemenőjének módosulása esetén is frissülnek, függetlenül, kell-e vagy nem • Saját függvényekkel rugalmasabb műveletek adhatók elő ugyan, mint SQL-ben, de az SQL kódolása 100-szor gyorsabb, mint a VB-é • Ezenkívül a lekérdezőtábla Képletek automatikus kitöltése a tábla melletti oszlopban opciójával a következő oszlopba autokitölthet egy cellaképletet, ami olyan egyedi számí-tásokat ad elő, ami SQLben nehéz lenne: így kombinálhatók az SQL és az Excel előnyei: A ListaMinta.xls fájl JoinQuery|CsatLekerd munkalapján erre látunk példát: több táblát összecsatoló lekérdező tábla utáni oszlopban cellaképlettel kiszámítjuk a UnitPrice mezőjének a 0 várható értékre, 1-es szórásra normalizált értékét: Ezt SQL-ben igen nehéz előadni, mert a SELECT részbe nem írhatunk rekord szintű és ag-regált dolgokat is: SELECT (UnitPrice-Avg(UnitPrice))/Stdev(UnitPrice)… nem fog működni, beágyazott lekérdezésekkel lehetne megoldani: SELECT (UnitPrice-(SELECT Avg(UnitPrice) FROM Tabla1))/(SELECT Stdev(UnitPrice) FROM Tabla1)…, amik viszont pazarolják a gépidőt, minden rekordnál feleslegesen újra futva • Max 65534 soros táblákat kezel, mert a munkalap méretét nem lépheti túl • Az Excel fájl belső lekérdezésekkel csak egy adott elérési úton működik, hacsak nem állítjuk makróval a lekérdezőtáblák .Connection tulajdonságait • Elcseszett, lassú grafikus lekérdezéstervezés MS Query-ben, túlbonyolított szintaxis =(UnitPrice-ÁTLAG(UnitPrice))/SZÓRÁS(UnitPrice)

26. 5-4.Gyakorló feladat:

27. Az előadás tartalma Az Excel leegyszerűsített adatbáziskezelési eszközei • 1 Rekord = 1 munkafüzet módszer: munkafüzet-közi csatolások kezelése: • 1 munkalap = 1 rekord/űrlap módszer, 3 dimenzós cellahivatkozások • 1 munkalapsor = 1 rekord módszer: Listák használata 1 • Autoszűrők és irányított szűrők • Összesítések • Sorok/oszlopok csoportosítása és tagolása • Korlátozások listák használatakor • Listák megjelenése VB kódban • Listák értékelése • Belső SQL lekérdezések • Részösszegek • Egy táblás belső lekérdezések • Több táblás belső lekérdezések • Lekérdezések felhasználói szerkesztése • Lekérdezőtáblák külső adatforrásának felhasználói elérése • Egyszerű belinkelt Access űrlapok • Hierarchikus tagolást tartalmazó belinkelt Access nézettáblák • A belső SQL lekérdezések értékelése Excel-alapú, értékesíthető alkalmazások készítése • Excel munkafüzet (*.xls) értékesítése, védelmekkel ellátva • Excel munkafüzet mentése interaktív HTML-oldalként (*.htm) • Excel munkafüzet mentése Excel bővítményként (Add-In) (*.xla) • Példa bővítmények írására • VB Formok szerkesztése • VB Formok programozása • Bővítmények programozása • Bővítmények használata Szakirodalom

28. Piacilag értékesíthető alkalmazások készítése Excelben Ha készítettünk valami jó kis elemző rendszert Excelben, kérdés lehet, hogyan tudnánk azt könnyen letelepíthető formában értékesíteni. Erre a következő lehetőségek vannak: Excel munkafüzet (*.xls) értékesítése, védelmekkel ellátva (lásd: Session2): • Lapvédelemmel védjük a cellaképleteket , ActiveX kontrollokat az átírástól/lemásolástól • A modulok és munkalapok VB kódjai szintén jelszóval védhetők • Ha olyan dolgokat használunk, amelyek levédve nem működnek, rejtett munkalapra rakjuk őket, és a munkafüzetet is levédjük, hogy ne tudják a rejtett lapokat megnézni • Ekkor azonban saját lapot sem tudnak beleszúrni esetleges továbbszámoláshoz • A VB-ben csak abszolút elérési utat adhatunk meg az Excel fájl, illetve külső adatbázisok hollétére, ezért indítás után mindig detektálni kell, milyen elérési úton van a fájl • A védelmek jelszavai vagy nagyon hosszúak, vagy keygen-programokkal feltörhetők • Ha ilyen munkafüzetből használunk makrófüggvényeket cellafüggvényként más munkafüzetekben, mindig meg kell adnunk a munkafüzet nevét elérési úttal, ami nehézkes Excel munkafüzet mentése interaktív HTML-oldalként (*.htm) és ennek kirakása webszerverre:  Elméletileg ez egy jó lehetőségnek látszik, hogy Excelünket bárki az Interneten bármilyen telepítés nélkül használja egy webkereső program segítségével  Az elkészítése igen egyszerű: Fájl|Mentés weblapként(File|Save as webpage) menüben: Teljes munkafüzet, Interaktív változat, Fájlnév megadása (pl. EzIttAReklamHelye.htm) Szándékos Excel-butítás: az Excelt megjelenítő HTML-kontrollt hihetetlen módon visszabutították: nem működnek benne a kimutatások, lekérdezőtáblák, diagrammok, makrók, cellakommentek, cellaképletek tabulálása, cellák validációja, ActiveX kontrollok, a nevesített cellatartományok csak nagyon korlátozottan  A webkereső mindenféle biztonsági beállításait ki kell kapcsolni a működéséhez, és a limi-tált számolótábla funkciók is csak Explorerben működnek, más webkeresőkben nem! katt katt katt

29. Piacilag értékesíthető alkalmazások készítése Excelben: Bővítmények Excel munkafüzet mentése Excel bővítményként (Add-In) (*.xla): • Olyan munkafüzet, amelyeknek a munkalapjai nem férhetők hoz-zá a felhasználó részére, de egyebekben teljes funkcionalitásúak, tartalmazhatnak cellaképleteket, diagrammokat,vezérlőket, kimu-tatásokat, melyeket makróval át tud másolni más munkafüzetekbe  A mentése lefordított formában történik, a cellaképletek bináris kódját tartalmazza, szövegét nem, ezért nehezebben törhető. De ezért mindig csak a fejlesztés+debug végén mentsünk *.xla-ba! • A Windows C:\Documents and Settings\User Name\Application Data\Microsoft\Bővítmények könyvtárában szokás őket elhelyez-ni, és az Eszközök|Bővítménykezelő(Tools| Add-Ins) menüvel szabályozható beregisztrálásuk az Excel alkalmazásba, ezért az elérési úttal kapcsolatos korábbi problémák nem jönnek elő • Makrófüggvényeik ugyanúgy hívhatók cellafüggvényként, mintha a felhasználói munkafüzetben lennének (függvényvarázslóból is!) Bővítmények írására a 3-1HaziMegoldas.xls alapján adunk példát: • Tegyük fel, hogy számokat a szám szövegesen kiírt formájába több nyelven konvertáló =MyNumTextConv(Szam,NyelvKod) makrófüggvényünket BeepuloMinta.xla-ban akarjuk értékesíteni • Ezt BeepuloMinta.xls munkafüzetként kezdjük el,ahol az AddIn-Sample|BeepulMinta munkalap tartalmazza a WordSetHu/Eng/ Ger tartományokban a magyar:1, angol:2, német:3 szószedetet • Ezenkívül, 4-es nyelvkódra szedjen fel a felhasználói munkafüzet megadott cellatartományából hasonló formátumú szószedetet • Mivel néhány nyelvben a tízesek megelőzik az egyeseket (pl.„har-minchat”,„thirty-six”), más nyelvekben fordítva van („sechs und dreizig”), a sorrendet és a két rész közti összekötő szöveget (sem-mi, vagy ”-”, vagy ” und „) lehessen állítani. Ezeket a beállításokat bele lehetne rakni az adott nyelv szószedetébe, vagy megadni a MyNumTextConv függvény külön paramétereiként, de hogy gya-koroljuk az űrlapok programozását, oldjuk meg ezt a függvény 1. használatakor előugró, vagy később eszközsorról előhívható VB űrlap(Form) segítségével: olyan vizuális objektum, amely ActiveX vezérlőkből összeállított felhasználói felületet (GUI) jelenít meg:

30. Új üres űrlapot a VB Editor ablak Insert|U-serForm menüjével hozhatunk létre, ekkor megjelenik a Project|Forms pontjában • A Controls eszközsorról egérrel húzhatók belé ActiveX vezérlők, amelyek tulajdonsá-gai a View|Properties window menüvel kinyitható ablakban szerkeszthetők • A vezérlőkön duplán kattintva, a hozzájuk tartozó eseménykezelő VB kód írható meg VB Formok szerkesztése Opciógomb-pár 1.fele Ez jelenik meg az egeret föléhúzva Hozzáfér a felhasználó Az azonos csoportnév köti ösz- sze a 2 opciógomb .Value-ját Az űrlapot a Project Explorerben nevezzük át InitForm-nak és rakjuk ki rá ezen vezérlőket: OptionButtonTensFirst: .Caption=„Tens precede ones” .ControlTipText=„Selects tens-ones seq.” .Value=True .Visible=True .Enabled=True .Locked=False .GroupName=TensOnesSeq OptionButtonOnesFirst: Ugyanazon beállításokkal, kiv.:.Value=False LabelConnector: .Text=„Tens-ones connector text with…” TextBoxTensOnesConnector: .Value=„” .Text=„” .Visible=True .Enabled=True .Locked=False .SelectionMargin=False LabelInfo: .Text=„Usable language codes…” ImageWordSetLayout: .Picture=„WordSetLayout.GIF” .Alignment=Center .SizeMode=Stretch RefEditWordSet: .Value=„” .Visible=True .Enabled=True .Locked=False OKButton: .Caption=„OK” .Visible=True .Enabled=True .Locked=False Opciógomb-pár 2.fele katt katt 1.Opció lesz kiválasztva Statikus szöveg Szövegbeviteli doboz kat- kat Legyen üres Ne legyen margója Statikus szöveg Kép Honnan szedje (a képfájlra fu-tás közben már nincs szükség!) Középre, kinyújtva katt Cellahivatkozás-szerkesztő A felhasználó által bevitt cella(tar-tomány) hivatkozás stringként je-lenik meg, ami kezdetben üres kat- kat Parancsgomb „OK” szöveggel húz Eseménykezelő kódja kat-tintásra elrejti az űrlapot Private Sub OKButton_Click() InitForm.Hide End Sub

31. VB Formok programozása 1 3 féle módon mozgathatunk adatokat az űrlap és a VB kód közt(mind a 3 mód 2 irányban is jó): • Minden vezérlő .ControlSource tulajdonságában megadható az AddIn-Sample|Beepul-Minta munkalap egy cella(tartománya), ahonnan felvesz/lerak logikai/szám/szöveg/dátum értékeket (aszerint, mire való a vezérlő), és ezeket olvashatjuk/írhatjuk VB kódból  Ezzel a megoldással az űrlap ActiveX vezérlői a cellaképletek számításait is befolyásolhatják, csakúgy, mintha egy munkalapra raktuk volna őket (lásd: Session2)  Kell hozzá legalább 1 munkalap a bővítményben, ezért néha körülményes • Közvetlenül írjuk/olvassuk VB kódból a vezérlők tulajdonságait: FormName.ControlName.Property = Value  Ez eccerű, mint a faék  Ha más kód letörli/újraindítja a Form objektumot, nem maradnak meg az értékek • Globális változókat deklarálunk a VB modulban a beállítások tárolására, és oda vissza másolgatjuk az értékeket belőlük a vezérlők tulajdnságaiba: FormName.ControlName.Property = GlobalVar  Ez is elég egyszerű, és a beállítások akkor is megmaradnak, ha a Form megpusztul Esetünkben az utolsó megoldást választottuk: • A TensBeforeOnes, TensOnesConnector, CustomWordSetRangeText globális változókat az űrlap beállításainak tárolására deklaráljuk • Az InitOK flag jelzi, hogy a beállítás megtörtént-e • A tLanguage típus csak azért kell, hogy ne kelljen fejből megjegyezni VB kódolás közben a nyelvek számkódjait A globális változók és az űrlap közti kommunikáció intézésére az űrlap megjelenítésére a VB modulban létrehozunk egy InitMyNumTextConv nevű el- járást, amit a MyNumTextConv függvény hív majd meg, ha szükség van rá, lássuk most ennek szerkezetét részletesen: '--------------------- Global types ------------- Enum tLanguage myHungarian = 1 myEnglish = 2 myGerman = 3 myCustom = 4 End Enum '---------------------- Global variables -------- Dim TensBeforeOnes As Boolean'Tens precede Ones Dim TensOnesConnector As String'Connector word between Tens and Ones Dim CustomWordSetRangeText As String'Custom word set range text Dim InitOK As Boolean'Initialization OK. flag

32. 'Part-sub of MyNumTextConv to init system Sub InitMyNumTextConv() If InitOK Then'If re-initializing system 'Copy variables into form control properties InitForm.TextBoxTensOnesConnector.Text = _ TensOnesConnector InitForm.OptionButtonTensFirst.Value = _ TensBeforeOnes InitForm.RefEditWordSet.Value = _ CustomWordSetRangeText Else'If first initialization 'Create system toolbar Set myBar = CommandBars.Add( _ Name:="Number To Text", _ Position:=msoBarLeft, _ Temporary:=True) myBar.Visible = True 'Create Init toolbar button Set myButton = myBar.Controls.Add( _ Type:=msoControlButton, _ ID:=19) 'ID of [Copy to clipbard] button myButton.OnAction = "InitMyNumTextConv" End If 'Launch initialization form InitForm.Show 'Copy properties of form controls into variables TensOnesConnector = _ InitForm.TextBoxTensOnesConnector.Text TensBeforeOnes = _ InitForm.OptionButtonTensFirst.Value CustomWordSetRangeText = _ InitForm.RefEditWordSet.Value 'Set initialized status InitOK = True End Sub VB Formok programozása 2 • Ha a rendszer már volt inicializálva, tehát most épp újra beállítják, akkor felmásoljuk az űrlap ve-zérlőibe a jelenlegi beál-lításokat a globális vál-tozókból, ne üres beállí-tásokat mutasson,az ide-gesítené a felhasználót • Ha ez az 1. inicializáció, létrehozunk kódból egy új eszközsort Excelben • És kirakunk rá egy nyo-mógombot, amivel a je-len eljárást újra lehet in-dítani, ha a beállításokat változtatni szeretnék Szándékos Excel-butí-tás:csak létező rendszer nyomógombhoz lehet új funkciót adni, különben nem működik a dolog. Szándékos Excel-butí-tás:1995-ös Excel5-ben még grafikus felületről lehetett Add-In-hez saját menüket tervezni, ezt azóta csak kódból lehet! • Ezután megmutatjuk az űrlapot a felhasználónak aki örül és szerkeszti • Miután OK gombbal be-zárta, visszamásoljuk a vezérlők tulajdonságait a globális változókba • És végül, de nem utolsó sorban,inicializáltra állítjuk a rendszer állapotát

33. Az előadás tartalma Az Excel leegyszerűsített adatbáziskezelési eszközei • 1 Rekord = 1 munkafüzet módszer: munkafüzet-közi csatolások kezelése: • 1 munkalap = 1 rekord/űrlap módszer, 3 dimenzós cellahivatkozások • 1 munkalapsor = 1 rekord módszer: Listák használata 1 • Autoszűrők és irányított szűrők • Összesítések • Sorok/oszlopok csoportosítása és tagolása • Korlátozások listák használatakor • Listák megjelenése VB kódban • Listák értékelése • Belső SQL lekérdezések • Részösszegek • Egy táblás belső lekérdezések • Több táblás belső lekérdezések • Lekérdezések felhasználói szerkesztése • Lekérdezőtáblák külső adatforrásának felhasználói elérése • Egyszerű belinkelt Access űrlapok • Hierarchikus tagolást tartalmazó belinkelt Access nézettáblák • A belső SQL lekérdezések értékelése Excel-alapú, értékesíthető alkalmazások készítése • Excel munkafüzet (*.xls) értékesítése, védelmekkel ellátva • Excel munkafüzet mentése interaktív HTML-oldalként (*.htm) • Excel munkafüzet mentése Excel bővítményként (Add-In) (*.xla) • Példa bővítmények írására • VB Formok szerkesztése • VB Formok programozása • Bővítmények programozása • Bővítmények használata Szakirodalom

34. 'Function converting number into text description of the number in a given language Function MyNumTextConv(ByVal NumInp As Double, _ ByVal LangCode As tLanguage) As String Dim WordSet As Range 'Init part If Not InitOK Then'If system not initialized InitMyNumTextConv 'Run initialization End If With Application.Workbooks("BeepuloMinta.xla"). _ Worksheets("AddInSample|BeEpulMinta") Select Case LangCode Case myHungarian: 'Hungarian language Set WordSet = .Range("WordSetHu") Case myEnglish: 'English language Set WordSet = .Range("WordSetEng") Case myGerman: 'German language Set WordSet = .Range("WordSetGer") Case myCustom: 'Custom language, collected from target workbook! Set WordSet = ActiveSheet. _ Range(CustomWordSetRangeText) Case Else'Unidentified language Set WordSet = .Range("WordSetEng") End Select End With … Bővítmények programozása Itt a legfontosabb azt fejben tartanunk, hogy a felhasználó nem a bő vítmény valamely mun- kalapjáról hívja majd meg a MyNumText- Conv függvényt,hanem saját felhasználói mun- kafüzete 1 lapjáról! Ezért az Add-In munka- lapjai nem érhetők el ActiveWorkbook. ActiveSheet-ként, mert ezek a felhaszná- lói munkafüzet munka- lapjaira mutatnak! Ezeket csak akkor hasz -náljuk, ha oda akarunk írni, vagy onnan olvas- ni. De ha írjuk őket, ezt ne cellafügvényként használt makrófüggény -ből tegyük, mert nem fog működni (lásd: Lesson3), hanem Form gombról indított Subbal • Ha az Add-In valamely, a felhasználó számára láthatatlan munkalapjára akarunk hivatkoz-ni, ezt mindig az Add-In munkafüzet nevének megadásával tegyük! Figyeljünk rá, hogy ez fejlesztés közben még *.xls kiterjesztésű, de a végső, letesztelt verzióban, Add-Inként mentés előtt át kell írni *.xla-ra, különben nem fogja megtalálni! • A függvény idításakor teszteljük, volt-e inicializáció, és ha nem indítsuk el azt. Így a Form csak a függvény 1. használatakor jön elő (vagy gombra), utána nem zavarja a felhasználót • A függvény kódjában a szószedet-tartomány kiválasztásakor, ha a nyelv kódja 4-es (más néven myCustom), az űrlapról a CustomWordSetRangeText globális változóba szedett string értékből .Range() VB-függvény segítségével csinálunk tartomány-megadást

35. katt katt Bővítmények használata katt katt =MyNumTextConv(A1;1) • Az elkészült Add-In függvényeinek működését elő- ször saját munkalapjain teszteljük, majd ha ez sikeres • Átírjuk a VB kódban a munkafüzet hivatkozás nevét *.xla-ra, és Fájl|Mentés másként(File|Save as) menüvel le- mentjük *.xla formátumban, majd bemásoljuk a C:\Documents and Settings\User Name\Application Data\Microsoft\Bővítmények könyvtárba. Ez utóbbit nem kötelező megtenni, de innen egysze- rűbb megtalálni az Add-Int az Excelbe regisztrálásakor: • A felhasználó megkapja az Add-In fájlt a jelszavakkal és az Excel Eszközök|Bővítménykezelő(Tools|Add-Ins) menü paneljén be-jelöli a bővítményt regisztrálásra, ez ettől kezdve az Excel indítá-sakor automatikusan betöltődik és függvényei használhatók • Tegyük fel, hogy ezután felhasználónk nyit egy új üres munkafü-zetet, és egy megfelelő formátumú cellatartományba (ld. sárga cellák) elkezd írni egy olasz nyelvű szószedetet 4. nyelvnek • Majd az A1 cellába beírja a 23.58 számot, a B1-ben pedig meg-hívja a MyNumTextConv függvényt. A függvény első használata-kor Enter nyomására megjelenik a beállítópanel, ahol pl. ki tudja jelölni a 4.nyelv cellatartományát a felhasználói munkafüzet-lapon • Ezután a függvény már 4 nyelven fog működni, és a munkalap bal oldalán megjelenik egy eszközsor egy nyomógombbal, ahonnan a beállítás újraindítható,szabályozva a választott nyelv szabályainak megfelelő egyes-tízes sorrendet és a köztük lévő kötő szöveget Az Add-In saját, felhasználó számára rejtett munkalapjainak további felhasználási lehetőségei: • Előre leformázhatunk szépen egy minta diagrammot rajtuk, amit a felhasználó munkafüzetbe makróval átmásolva klónozhatunk sok példányban, más és más felhasználói adatokra • Kimutatás- vagy lekérdezőtábla vágólapon keresztüli felhasználói munkalapra beszúrása • Minta munkalap átmásolása a felhasználói munkafüzetbe, aktualizált tartalommal • A felhasználó által a saját munkafüzetének egy lapján kijelölt tartomány Add-In lapra másolása, ott cellaképletekkel számolás Enter katt katt katt katt

36. 5-5.Gyakorló feladat: Kéttermékes profitfüggvény diagrammok Add-Inből A 2-5GyakorloMegoldas.xls-ból kiindulva készítsen olyan Add-Int, amely: • A profitfüggvény számításához szükséges két termék árait a felhasználói munkafüzet egy-egy cellatartományából szedi fel hivatkozás-szerkesztő vezérlő révén 1 beállítópanelen • A két termék keresleti függvény paramétereit szövegdobozokban szedi fel ugyanott: Az R aggregált profitfüggvény: R = (pi - ci)×Bi/uii×Exp(1-Sj(pi/uij)) + (pj - cj)×Bj/ujj×Exp(1-Si(pj/uji)) Ahol: pi, pj - i és j versenyző termékek egységárai, $/unit Bi, Bj - a 2 termék fogyasztói kosarának mérete, $/piac uii, ujj - a 2 termék belső egységhasznosságai, $/unit uij, uji - 2 termék egymásszembeni kereszthasznosságai, $/unit ci, cj - 2 termék egységköltségei, $/unit, ahol: ci =gi + fi × pi2 gi , gj - a 2 termék technikailag minimális egységköltsége, $/unit fi , fj - a 2 termék beszállítójának költségleszorítási hajlandósága,% • Gombnyomásra másolja az elkészített profitfüggvény diagrammot a felhasználó aktív munkalapjára egy meghatározott bal felső cellától A megoldás:

37. Szakirodalom Általános kézikönyvek Excel-Access adatbáziskezelési eszközökhöz és Add-Inekhez: • MSDN Visual Basic for Applications Help: http://msdn.microsoft.com/hu-hu/library/aa269683(en-us,office.10).aspx • Microsoft Access SQL kézikönyv: http://office.microsoft.com/en-us/access/CH062526881033.aspx Speciális kézikönyvek Add-In íráshoz: • Microsoft: http://support.microsoft.com/kb/291392 • http://www.fontstuff.com/VBA/vbatut03.htm Hasznos ingyenes Excel Add-Inek: • Optimalizáció: http://www.me.utexas.edu/~jensen/ORMM/frontpage/jensen.lib/index.html • ExTools: mértékegység konverziós Add-In: http://www.excel-extools.com/ • SQLExcel: MSQuery helyett használható Add-In, bármely ODBC-s adatbázisra: http://www.sqlexcel.net/ Hasznos kereskedelmi Excel Add-Inek: • DigDB: 50-60 Add-In tematikusan rendezett gyűjteménye, ingyenes 15 napos próbaverzió: http://www.digdb.com/ • XLStat: statisztikák, nincs próbaverzió: http://www.xlstat.com/en/home/?gclid=COqMmcLZlpgCFQtNtAodImZGnQ • Analyse-it: statisztikák, nincs próbaverzió: http://www.analyse-it.com/ • ASAP: egy csomó funkció, amit Excelből kifelejtettek, van próbaverzió: http://www.asap-utilities.com/?gclid=CLuswePZlpgCFQNItAody3-Wmg • Office Addins: irodai-adminisztrációs célú gyűjtemény, pl. jelszó-visszanyerés: http://www.office-addins.com/-excel-addins/ • Add-In decompiler: visszafejti a bővítményből az eredeti munkalap kódját, illegális: http://www.straxx.com/excel/decompiler/xla_decompile.html