370 likes | 513 Views
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. Az előadás tartalma. Az Excel leegyszerűsített adatbáziskezelési eszközei
E N D
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
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
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
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
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)
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
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
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)
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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)
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
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
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:
Ú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
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
'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
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
'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
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
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:
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