Excel konzultáció 2. Gyakorlat Dr. Pauler Gá bor , egyetemi docens, ev. Adószám: 63673852-3-22

Excel konzultáció 2. Gyakorlat 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

A gyakorlat tartalma 1. Házi feladat ellenőrzése: Automatikus adatbázis tervezés Cellahivatkozások fogalma • Hivatkozások szerkesztése • Hivatkozási stílusok: „A1” / „S[1]O[1]” és makro- hivatkozások • Hivatkozások pozícionálása: abszolút / relatív • Függvénytáblázatok kevert hivatkozások segítségével • Hivatkozások áttekinthetőségi problémái • Sor- és oszlopcímkék használata • Nevesített cellatartományok használata • Dinamikus cellatartományok használata • Nevesített cellatartományok sor/oszlop címkékkel • Relatív dinamikus hivatkozások • Nevesített cellatartományok értékelése Cellafüggvények • Beépített függvények • Egyszerű cellafüggvények • Tömbképletek használata • Függvénytáblák létrehozása adattábla használatával • Dinamikus méretű függvénytáblák 2-1. Házi feladat: Függvénytábla adattáblával

Excel cella(tartomány)hivatkozások: Alapfogalmak, Szerkesztésük • „Az Excelben a bal felső cella az A1” – ezt nagyjából még a takarítónő is tudja. Ezenkívül Excelben még 14 különféle módon hivatkozhatunk egy cellára, és mindegyik hatékonyabb ennél… A hivatkozás(Reference) ( ) az Excel különféle funkcionális részei: cellafüggvények (Cell Function), diagrammok (Chart), adatérvényesítés (Validation), stb. bemenő adatait mutatja meg: mindig az adott outputot előállító műveletvégző hivatkozik az inputjaira! • A hivatkozások működését a 2-1HivatkozasMinta.xls fájl segítségével szemléltetjük: • A hivatkozás történhet egy cellára (Cell) vagy több cellából álló cellatartományra (Range). A tartományhivatkozás 2 cellahivatkozásból áll: BalFelsőCella:JobbAlsóCella • A hivatkozás irányulhat azonos/eltérő munkalapra azonos/eltérő munkafüzetben. Legáltalánosabb formátuma: [Path\Fajlnev.xls]MunkalapNev!CellaTartomany • Ha a fájl vagy munkalap neve szóközöket, ékezetes betűket, speciális karaktereket tartalmaz, akkor így néz ki: ‘[Path\Fajlnév.xls]MunkalapNév’!CellaTartomány • Azonos munkafüzetre/ munkalapra történő hivatkozás esetén a jelölésük elhagyható a hivatkozásból Hivatkozások szerkesztése cellaképletben: a cellára kattintás után: • A hivatkozásokat kézzel írhatjuk be a szerkesztett cellába,vagy a képletszerkesztő sorba, illetve képletszerkesztés közben az egérrel cellákra kattintva automatikusan beírja hivatkozásukat • Az Excel képletszerkesztés közben a hivatkozott cellákat különböző színű adatforrás jelölő keretekkel ( ) keretezi be • Az adatforrás kijelölő keretet egérrel átméretezhetjük/húzhatjuk, a hivatkozás ennek megfelelően módosul katt húz katt

Excel cella(tartomány)hivatkozások: Hivatkozási stílusok A cellahivatkozásoknak kétféle stílusa lehetséges, melyek közt az Eszközök|Beállítások|Általános|S1O1-stílusú hivatkozások (Tools|Settings|General|R1C1-style references)bejelölődoboz segítségével lehet váltani: • „A1”-stílusú hivatkozások, ahol a munkalap oszlopait a latin ABC nagybetűi, illetve ezek kombinációi jelölik A-tól IV-ig, a sorait számok 1..65536-ig • „S1O1”(angolban „R1C1”)-stílusú hivatkozások, ahol a munkalap sorait „S”(„R”) előjelölő után 1..65536-ig terjedő számok, oszlopait „O”(„C”) előjelölő után 1..256 terjedő számok jelölik. • Az ilyen stílusú hivatkozá- soknak az az előnye, hogy kompatibilisek az Office Visual Basic makrónyelv cellahivatkozásaival: Worksheet.Cells(Row,Column) • Viszont ember számára kevésbé áttekinthetőek, ezért ritkán használatosak, inkább felvállaljuk, hogy makrók programozáskor az oszlop betűkódját fejben átszá-moljuk sorszámra (mivel egy felhasználói felület jellegű munkalap ritkán használ 15-20 oszlopnál többet, ez megoldható fejben) • Stílusváltáskor a hivatkozások automatikusan konver-tálódnak a cellaképletekben, a diagrammok, az ActiveX vezérlők és az adatérvényesítés beállításaiban • Nem konvertálódik viszont automatikusan a Visual Basic kódban stringként megadott adott stílusú hivatkozás, váltás után nem fog működni: Worksheet.Range(„A6”) =SZUM(C6:C13) =SZUM(S6O3:S13O3) katt

Excel cella(tartomány)hivatkozások: Hivatkozások pozícionálása 1 A cellahivatkozások pozícionálás szerint kétfélék lehetnek: • Relatív hivatkozás (Relative Reference): • Formátuma: A1, illetve S[-1]O[+2], angolul R[-1]C[+2]. A cellafüggvény Szerkesztés| Másolás (Edit|Copy), Ctrl+C, Szerkesztés| Beillesztés (Edit|Paste),Ctrl+V menük-kel/gombkombinációval vágólapon keresztüli más cellába lemásolása ( ), vagy a cellakeret jobb alsó sarkán lévő autókitöltő gomb egér húzásával történő tovább-másolása esetén a hivatkozás ugyanannyi sort/oszlopot mozdul el, mint amennyit a cellafüggvény elmozdul az eredeti helyéhez képest. • Kivételt képez a cellafüggvény Szerkesztés|Kivágás(Edit|Cut),Ctrl+X, Szerkesztés| Beillesztés (Edit|Paste), Ctrl+V menükkel/gombkombinációval vágólapon keresztüli, vagy a cellakeret egérhúzásával más cellába mozgatása ( ), ekkor a relatív hivat-kozások helyben maradnak!!! • Abszolút hivatkozás (Absolute Reference): • Formátuma: $A$1, illetve S1O1, angolul R1C1. A hivatkozás a cellafüggvény másolá-sakor/ autókitöltésekor a helyén marad! • Ha a hivatkozásban szerepel munkafüzet, munkalap,ezek MINDIG abszolút hivatkozások, a hiányuk jelenti a relatív hivatkozást (aktuális munkafüzetre/ lapra) • A diagrammokban szereplő adatforrás-hivatkozások MIN- DIG abszolútak munkafüzetre, lapra, cellára! (pl. egy dia- gramm más munkafüzetbe másolásakor hivatkozik az eredeti munkafüzetére) • Az ActiveX vezérlők beállításaiban szereplő hivatkozások (pl. ComboBox.LinkedCell) munkalapra és cellára MINDIG abszolútak (akkor is ha relatívnak írjuk őket), de munkafüzetre relatívak (pl. egy vezérlőt egyik munkafüzet- ből a másikba másolva, nem az eredeti munkafüzetre hivatkozik,hanem az újra) • Az adatérvényesítések- ben szereplő hivatkozá- sok lehetnek abszolú- tak vagy relatívak, aszerint hogy írtuk őket =SZUM(C6:C13) =SZUM(E6:E13) =SZUM(C6:C13) húz húz =SZUM($C$6:$C$13) =SZUM($C$6:$C$13)

Excel cella(tartomány)hivatkozások: Hivatkozások pozícionálása 2 Kevert hivatkozás (Mixed Reference): • Formátuma: $A1, A$1 illetve S1O[+2], S[+2]O1 angolul R1C[+2], R[+2]C1 Olyan hivatkozások, amelyek a cellaképlet továbbmásolása során csak egy sorban vagy csak egy oszlopban csúszhatnak el, aszerint, hogy az oszlopot vagy a sort rögzítettük abszolútra. Általában függvénytáblázatok (Function Table) összeállításakor használjuk: • Adott egy függvény legalább 2 bemenő változóval (pl. vegyük két szám szorzatát, és ha <25-nél, akkor vonjuk le 1-ből, egyébként adjuk hozzá) • A két bemenő változó egy táblázat sor- illetve oszlop fejléc értékeit képezi, a táblázat belsejét pedig fel kell tölteni kiszámított függvény értékekkel, így lesz függvénytábla • Nyilván nem kézzel akarjuk a több tucat cellába beírogatni a függvényeket, hanem a bal felső cellában megírunk egy függvényt úgy, hogy autókitöltővel ( ) végig lehessen húzni az egész táblázatra • Itt a sor-/oszlop fejléc értékek elérésére vegyes hivatkozásokat készítünk: • A sorfejlécnél a hivatkozás oszlopát rögzítjük (pl. $A60), így végigcsúszhat a sorokon • Az oszlopfejlécnél a hivatkozás sorát (pl. B$59), így végigcsúszhat az oszlopokon Hivatkozás pozícionálások szerkesztése: • Ha képletszerkesztés közben egérrel kattintjuk be a hivatkozást, az alapban mindig relatív • A képletben a kurzorral a hivatkozáson állva, az abszolút és relatív hivatkozás közt F4 gombbal válthatunk, vagy a hivatkozás átírásával Szándékos Excel-lebutítás: az adatforrás jelölő keret sajnos nem jelzi ki, hogy a hivatkozás sor/oszlop szerint abszolút vagy relatív-e, pedig ez nagy segítség lenne szerkesztés közben húz F4 =HA($A60*B$59<25;1-$A60*B$59;1+$A60*B$59) katt katt

2-4.Gyakorló feladat: Függvénytáblázat készítése Állítson elő egy kéttermékes aggregált profit függvénytáblát a 2 termék egységárának (0..10$) függvényében, kevert hivat-kozások segítségével! 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,% Szorgalmi feladatként diagrammolja ki a függvénytáblázatot! A megoldás: 2-4GyakorloMegoldas.xls

Excel cellatartományhivatkozások méretezése, képletek áttkinthetősége A cellatartomány-hivatkozások méret szerint 2 félék lehetnek: • Fixek (Fixed): mindig ugyanannyi cellát tartalmaznak, ez az alapértelmezett • Dinamikusak (Dynamic): automatikusan bővülnek-szűkülnek az adatok mennyiségének változásával. Ezt külön trükkökkel lehet megoldani, amiről nemsokára szó lesz A cellaképletek áttekinthetőségi problémái: • Az eddig említett dolgokat egy gyakorlott Excel-felhasználó általában tapasztalatból tudni szokta. Azonban hatékony fejlesztőmunkához kevés ennyi tudás a hivatkozásokról. Ugyanis minél bonyolultabb alkalmazásokat fejleszt valaki, elég valószínű, hogy beleszalad abba a problémába, hogy olyan bonyolult cellaképleteket írkál, amelyeket később maga sem ért meg, nem tud módosítani, és elmegy a kedve az Excelezéstől: =HA(ÉS(M$4<='Prod1Param|Term1Param'!$N$18;VAGY($B11="=";$B11="∑"));HA(OFSZET($B$5;HOL.VAN($IV11;$IV$6:$IV$1010;0)-1;0;1;1)="=";OFSZET(M$5;HOL.VAN($IV11;$IV$6:$IV$1010;0)-1;0;1;1);0)+SZORZATÖSSZEG(OFSZET(M$5;HOL.VAN($IV11;$IV$6:$IV$1010;0);0;$IV11-5-HOL.VAN($IV11;$IV$6:$IV$1010;0);1);OFSZET(EE$5;HOL.VAN($IV11;$IV$6:$IV$1010;0);0;$IV11-5-HOL.VAN($IV11;$IV$6:$IV$1010;0);1));"") • A cellaképlet automatikusan összeállított kódjából hiányzik 3 dolog, ami miatt egy programkód áttekinthetőbb: • Tabulálás:”egymásba dobozolt”részek beljebb lépnek • Kommentezhető:szöveges megjegyzések lehetősége • Változónevek használata elvont hivatkozások helyett • De hiába próbálunk Entert vagy tabulátorokat ütni a kódba képletszerkesztőben, mert ezekre kilép, és számolni kezd • Alt+Enter gombkombinációval tehetünk sortöréseket a kódba és szóköz (Space) karakterekkel lehet tabulálni • A sortörések és szóközök a cellaképlet másolása, mozgatása és autókitöltése során is megőrzödnek. • További zavaró dolog, hogy a cellaképlet kódja nem kommentezhető. Ezt úgy hidalhatjuk át, hogy a cellahoz beszúrt megjegyzésbe másoljuk a tabulált, eltérő színnel kom- mentezett képlet kódot '4.2.1.2. Down Cat table Sub DownCatTable(CatTable) Dim I As Long 'Cycle variable With CatTable .CatNum = 0 ReDim .Cat(0) 'Set status .CatTableOK = False End With 'CatTable End Sub 'DownCatTable húz Alt Enter =HA(D6<>E6; "Nem egyenlő "&D6&" és "&E6; "Egyenlő "&D6&" és "&E6 ) =HA(D6<>E6;‘Ha a 2 szám nem = "Nem egyenlő "&D6&" és "&E6; "Egyenlő "&D6&" és "&E6‘Különben )‘Ha a 2 szám nem = vége Space =HA(D6<>E6;"Nem egyenlő "&D6&" és "&E6;"Egyenlő "&D6&" és "&E6)

A sor- és oszlopcímkék használata katt katt katt A fenti trükkök nem segítenek azon, hogy a cellahivatko-zások ember számára borzasztó nehézkesen értel-mezhetők. Olyan mintha programozás közben memóriacímekkel kellene dolgozni változónevek helyett (mint pl. az assembly nyelvekben). Ezt a problémát oldja fel o sor/oszlopcímkék (row/column labels) használata a Beszúrás|Név|Címke (Insert| Name|Label) menünél: • Megadunk egy sor vagy oszlop cellatartományt (Label range), amiben a rá merőleges oszlopok/ sorok címkéi lesznek (a cellák csak fix értékeket tartalmazhatnak, és előzetesen leformázzuk őket VáltozóID stilusura). • A címkelistához történő Hozzáadásuk után a sor száma és az oszlop betűjele helyett használhatjuk őket cellahivatkozásokban. • De itt tegyünk a kettő közé szóközt, és tegyük a hivatkozást zárójelbe. Más szintaktikai szabályok változatlanok: $ jelekkel ugyanúgy abszolútra rögzíthetjük őket, mint hagyományosan. • A sor/oszlopcímkék fő hátránya, hogy a teljes sorra/ oszlopra kiterjed a hatókörük, ami nem szerencsés, ha több, eltérő oszlopszerkezetű kis táblázatot aka-runk egy munkalapon egymás alatt/mellett kezelni, és a felhasználónak egyszerre kellene látni őket. • Címkék akkor működnek, ha Eszközök|Beállítás|Számolás menüben Képletekben címkék= OK! katt katt =SZUM((Baranya Jan):(Baranya Aug)) =SZUM((Tolna Jan):(Tolna Aug)) =SZUM(($Baranya $Jan):($Baranya $Aug)) =SZUM(($Baranya $Jan):($Baranya $Aug))

katt Nevesített cellatartományok használata katt katt Erre a megoldás nevesített cellatartományok (Named Ranges) definiálása a Beszúrás|Név|Név mega-dása (Insert|Name|Define name) menüvel: • Az előzetesen egérrel bekattintott cellatartományra a megadott Névvel (Name) hivatkozhatunk majd a képletekben • A Hivatkozásnál (Reference) pontosíthatjuk a cellatartomány definíciót, ha szükséges • Majd Hozzáad (Add) gombbal adjuk a listához • A nevet tüntessük fel a munkalapon is a tartomány feletti sorban vagy tőle balra lévő oszlopban,és for-mázzuk VáltozóID stílusúra. Ezt következő miatt célszerű megtenni: Ha sok kicsi tartománynak külön nevet akarunk adni (pl. sok paraméternek, lásd a BusinessPlanner.xls fájlban), ezt nem egyenként kézzel kell megtenni, hanem a az összes tartományt és nevet egyben kijelölve a Beszúrás|Név|Létrehoz (Insert|Name| Create) menünél megadjuk, hogy pl. a Bal oszlop-ból (Left Column) hozza létre őket külön A nevesített cellatartomány lehet: • 1db cella (pl. 1 paraméter cellája - ezután a nevé-vel hivatkozhatunk rá) • Sor/oszlop részlete (pl. névvel hivatkozott vektor), • 2 dimenzios cellatömb (névvel hivatkozott mátrix). katt katt katt katt

Dinamikus cellatartományok használata A cellatömb-hivatkozás mérete dinamikus is lehet: • Az eddigi hivatkozások mind fix méretűek voltak, • De egy valós alkalmazásnak változó méretű input adatokat is kezelnie kell, átprogramozás nélkül!!! • Dinamikus cellatartomány-hivatkozást ad meg az: =Ofszet (KezdCella;SorLe;OszlJobbra;Sorok;Oszlopok) =Offset (StartCell,RowsDown,ColRight,Rows,Columns) Egy abszolút Kezdőcellától lelép SorokLe sort, jobbra lép OszlJobbra oszlopot és kijelöli a Sorok ×Oszlopok méretű cellatömböt. A SorokLe, Oszl-Jobbra lehet 0, ekkor a bal felső cella lesz a kezdő! • A Sorok, Oszlopok értékét a kitöltött sor/oszlop fej-léc cellákat leszámoló függvénnyel adhatjuk meg: =Darab2(Sor/OszlopFejlécCellatartomány) =CountA(Row/ColumnHeaderRange) • A dinamikus méretű nevesített cellatartományok definiálása első ránézésre bonyolultnak tűnhet, a használata viszont jelentősen leegyszerűsíti a fej-lesztést: egyetlen névvel (pl. „Eladások”) meghi-vatkozhatunk egy bonyolult, az aktuális adatmeny-nyiség függvényében automatikusan átméreteződő adathalmazt • A cellatömb aktuális méretei az =Sorok(Név) =Rows(Name) (de VB-ben Range.Rows.Count)! illetve az =Oszlopok(Név) =Columns(Name)(VB-ben Range.Colums.Count)! függvényekkel kaphatók vissza =DARAB2($C$5:$P$5) =DARAB2($B$6:$B$15) katt =OFSZET('RefTemplate|HivatkMinta'!$C$6;0;0; 'RefTemplate|HivatkMinta'!$A$5; 'RefTemplate|HivatkMinta'!$B$4) =SZUM(Eladások)

Nevesített cellatartományok sor/oszlopcímkékkel A nevesített cellatartományokon belül is használhatjuk a már definiált sor/oszlopcímkéket, kombináltan: • Egy sor/oszlopcímke tartománynév után írásával hivatkozhatunk a cellatartomány 1 sorára/oszlopára • Több sor/oszlopcímkéből álló felsorolás tartomány-név után írásával a tartomány több sorára/oszlopára hivatkozhatunk egyszerre (nem kell egymás mellet-tieknek lenniük) • Ezek a hivatkozások lehetnek relatívak, vagy a cím-kék elé írt $-ral abszolút rögzítettek • Ha a tartománynév után sor- és oszlopcímkéket is írunk, akkor a sorok/oszlopok keresztezésében lévő cellákra hivatkozhatunk (nem kell egymás mellet-tieknek lenniük) A szintaktikai szabályok hasonlóak címkékhez: • A kombinált címkés hivatkozást zárójelbe kell tenni • A nevek/címkék közé szóközt kell tenni • Több sor/oszlop címke felsorolását magyarban pontosvesszővel, angolban vesszővel tagoljuk =SZUM(Eladások Baranya) =SZUM(Eladások Tolna) =SZUM(Eladások $Baranya) =SZUM(Eladások $Baranya) =SZUM(Eladások Jan Baranya) =SZUM(Eladások Jan Tolna) =SZUM(Eladások $Febr $Baranya) =SZUM(Eladások $Febr $Baranya) =SZUM(Eladások Jan;Febr Baranya) =SZUM(Eladások Jan;Febr Tolna) =SZUM(Eladások $Jan;$Febr $Baranya) =SZUM(Eladások $Jan;$Febr $Baranya) =SZUM(Eladások $Jan $Baranya;Somogy) =SZUM(Eladások $Jan $Baranya;Zala)

Relatív dinamikus hivatkozások, Nevesített cellatartományok értékelése Relatív dinamikus hivatkozást, amely másolásra/ autokitöltésre mozog, és a mérete is dinamikusan változtatható, nem tudunk nevesített cellatartománnyal létrehozni. Ehelyett olyan Ofszet függvényt használunk: • Ahol a Kezdőcella relatív hivatkozás, csúszkálhat • A SorokLe, OszlopokJobbra pedig 0, vagyis a kezdőcella a tartomány bal felső cellája • A Sorok, Oszlopok értékét a kitöltött fejécértékek leszámlálásával adjuk meg =Ofszet(RelKezdCella;0;0;Darab2(SorFejLéc);Darab2(OszlopFejLéc)) =Offset(RelStartCell,0,0,CountA(RowHead),CountA(ColumnHead)) A másik megoldási lehetőség erre a problémára, hogy az Ofszet-ben, abszolút Kezdőcellát tűzünk le, majd a SorokLe, OszlopokJobbra paraméterekkel csúsztatjuk el a cellatartomány-hivatkozást oda, ahol éppen kell. Az elcsúsztatás mértékét úgy határozzuk meg, hogy megkeressük, hogy a tartományba szándékolt bal felső érték hol van éppen: =Hol.Van(KeresettÉrték;KeresésiTartomány;0) =Match(SearchValue;SearchRange;0) • A függvény a Keresett érték sorszámát adja vissza a Keresési tartományban, pontos egyezést követel, ha több hasonló érték van, mindig az elsőt választja ki. Ha nem találja a keresett értéket, #Érték (#Value) hibajelzést ad vissza. • Ebben az esetben az Ofszet függvény így néz ki: =Ofszet(AbszKezdCella;Hol.Van(BalFelsőÉrt;SorFejléc;0);0; Darab2(SorFejLéc);Darab2(OszlopFejLéc)) =Offset(AbsStartCell,Match(UpperLeftValue,RowHeader,0),0, CountA(RowHead),CountA(ColumnHead)) A címkék, nevesített cellatartományok használatának értékelése:  Létrehozásuk a munkafüzetben jelentős plusz munkát igényel • A befeketett munka fejlesztés és debug közben busásan megtérül: tabuláció, nevek és címkék használata nélkül a cellaképletek kódja olvashatatlan az ember számára!!! • Ha azt akarjuk, hogy a címke/név definiálása előtt beírt cellaképletek is használják őket, a Beszúrás|Név|Hivatkozás névvel (Insert|Name|Apply names) menüvel állíthatjuk be • A neveknek és címkéknek 1 munkalapon belül egyedieknek kell lenniük, ezért gyakran külső szoftverek (pl. LINGO9.0, Access) is így tudnak hivatkozni a munkalap részeire  Elég nehézkes egy bonyolult munkafüzetben szereplő több száz nevet egyetlen listában kezelni a Név megadása (Define name) menüben, és nem ajánlott utólag átnevezni őket =SZUM(OFSZET(C6;0;0;$A$5;1)) =SZUM(OFSZET(E6;0;0;$A$5;1)) katt katt katt

2-5.Gyakorló feladat: Függvénytáblázat készítése nevesített hivatkozásokkal A 2-4.Gyakorló feladat megoldását alakítsa át úgy, hogy a lehető legtöbb dologra nevesített cellatartomány hivatkozásokat használjon sor- és oszlopcímkékkel, és így a lehető legegyszerűbb cellaképleteket alkalmazza! A megoldás: 2-5GyakorloMegoldas.xls =(pi-ci)*Bi/uii*KITEVŐ(1-SZUM(pi/uii;pj/uij))+(pj-cj)*Bj/ujj*KITEVŐ(1-SZUM(pi/uji;pj/ujj)) =gj+pi^2*fj • Nevesített hivatkozásokkal a cellaképlet majdnem olyan lehet, mint az eredeti matematikai formula. (Leszámítva a nagyon-nagyon hülyén magyarított cellafüggvény-neveket: pl. az Exp az egész világon ex-et jelent, de valamelyik kis önjelölt Kazincynak sikerült rá megalkotni a Kitevő függvénynevet, amiről viszont intuitive mindenki az yxfüggvényre gondolna) • De még így is nagy haladás, vessük össze a képlet olvashatóságát a nevek használata nélküli cellaképletével a 2-4.Gyakorló feladatnál!

Egyszerű cellafüggvények és tömbképletek Az Excelben cellafüggvények (Cell Functions) egymásba ágyazásával (Embedding) össze-állított cellaképletek (Cell Formula) állítják elő a meghivatkozott inputokból a kívánt out-putot. A cellafüggvények eredetük szerint lehetnek: • Beépített (Built-In) függvények • Felhasználó által definiált (User-defined) függvények, amelyekhez Office Visual Basic makrónyelven írt függvény programkód tartozik Az output jellege szerint a cellafüggvények lehetnek: • Egyszeri cellafüggvények (Single Cell Function), amelyek csak a saját cellájukba tesznek outputot (ilyen a legtöbb matematikai, szövegkezelő, dátumkezelő, stb. függvény) • Mindig = jellel kezdődnek (ha olyan sima szöveget akarunk a cellába írni, ami =-vel kezdődik, pl. „ = napi táplálékszükséglet”, akkor az = elé üssünk szóközt! • Enter-rel visszük be őket • Lehetnek beépítettek és felhasználó által definiáltak • Tömbfüggvények (Range Function), amelyek egy output cellatartományba tesznek több értéket (általában speciális statisztikai-matematikai függvények: pl. Gyakoriság (Frequency), Inverz.Mátrix (MInverse), Lin.Ill (Linest) – lineáris regresszió) • A szerkesztésük előtt kijelöljük azt a cellatartományt, ahova az outputot teszi (a függvény fajtája és paraméterezése határozza meg, hogy ez milyen méretű lesz) • A képletszerkesztő soron beírjuk a tömbfüggvényt, mely mindig =-vel kezdődik • Ctrl+Shift+Enter billentyűkombinációval visszük be, mert ha Enter-t nyomunk akkor csak a tartomány bal felső cellájába teszi bele! • A tartomány cellái bevitel után már csak együttesen kijelölve, a tömbfüggvényen keresztül módosíthatóak, külön nem! Ezt a zárolást automata kapcsos zárójel {} jelöli Szándékos Excel-lebutítás: elvileg lehetőség lenne Visual Basic kódból felhasználói tömbfüggvényt is definiálni, de ezt letiltották. A korlátozás azonban megkerülhető adattáblák használatával =SZUM(Eladások Baranya) Enter =INVERZ.MÁTRIX((Baranya Jan):(Somogy Febr)) Enter Ctrl Shift {=INVERZ.MÁTRIX((Baranya Jan):(Somogy Febr))}

Adattáblák használata 1 Korábban már említettük, hogy 2 dimenziós függvénytáblázatokat kevert abszolút/relatív hivatkozásokat tartalmazó cellafüggvényekkel állíthatunk elő a legegyszerűbben • Azonban ezek óriási hátránya, hogy rendkívül pazarlóan bánnak az Excel erőforrásaival: ahány cella csak van a függvénytáblázatban, mindbe autokitöltjük – az esetenként igen bonyolult – vegyes hivatkozású képletet, amely mind a munkafüzet fájl méretét és számolási idejét jelentősen megnöveli (próbáljuk lejátszani egy teljes munkalapra, és láthatunk szép Excel fagyikat…) Átmeneti megoldás lehet a következő trükk, ha a függvénytáblázat tartalmát csak viszonylag ritkán kell újraszámolni: • Kijelöljük a függvénytáblázat celláit (kivéve a legfelső sort) • Szerkesztés|Másolás (Edit|Copy) menüvel vagy Ctrl+C forrógombbal vágólapra másoljuk őket • Szerkesztés|Irányított beillesztés|Beillesztés|Értéket (Edit|Paste special|Paste|Value) menüvel visszamásoljuk őket a helyükre statikus kiszámolt értékként, képlet nélkül, ami nem fogyaszt komoly erőforrásokat • Ha a táblázatot később frissíteni kell, a legfelső sorban meg- hagyott képletekből újra autokitöltjük az egészet katt katt

Adattáblák használata 2 Abban az esetben, ha alkalmazásunkban egy függvénytáblázat nagy és gyakran kell frissíteni, az előbbi trükk elég nehézkes. Ekkor alkalmazzuk az adattáblát (Data Table): • Egy 2 dimenziós függvénytáblázatot automatikusan kiszámoló cellatartomány • Amelynek bal felső cellájában egy legalább 2 (de lehet több) input változós minta cellaképlet van, és a függvénytábla kiszámításához az egyik input értékbe a tartomány bal oszlopának (sorfejlécének) értékeit, a másik input értékbe a tartomány felső sorának (oszlopfejlécének) értékeit kell automatikusan behelyettesíteni • A cellaképlet min. 2 db., a tartományon kívüli cellára kell, hogy hivatkozzon, mint sor- és oszlop inputra, amiket ezen nevekkel el is látunk, de hivatkozhat más külső inputokra is • A tartomány kiszámított függvényértékeket tartalmazó belseje {=Tábla(SorInput; OszlopInput)} jelzéssel zárolt lesz, külön cellánként nem módosítható • A mintaképlet és a sor/oszlop fejléc cellák viszont nem zároltak, módosításukra az adattábla újra kiszámítódik az aktuális értékekkel • A sor/oszlop input cellák sem zároltak, de értékük módosítása csak a minta képlet cella értékét változtatja, a zárolt függvénytábláét nem! • Ha a minta képletnek nemcsak sor/oszlop input cellái vannak, hanem továbbiak is, azok a függvénytábla szempontjából abszolút hivatkozásnak minősülnek, még ha relatívként is adtuk meg (vagyis, a teljes függvénytábla ezeket a cellákat használja 3., 4.,stb. inputként) • Adattábla a mintaképlet+fejléc sor/oszlop+függvénytábla együttes kijelölése után az Adatok|Adattábla (Data|Data table) menüből indítható kis varázslóval definiálható, ahol meg kell adni sor/oszlop input cellát (mintaképletnek automatikusan az aktuális kijelölés bal felső celláját tekinti) katt =HA(SorInput*OszlInput<25;1-SorInput*OszlInput;1+SorInput*OszlInput) {=TÁBLA(A73;B72)} katt katt

Adattáblák használata 3 • Az adattábla használatának óriási előnye a kevert hivatkozásos-autokitöltős függvény-tábla készítéssel szemben, hogy nagyságrendekkel kevesebb erőforrást fogyaszt!!! • Hiába áll a függvénytábla több 1000 cellából, 1db képletet alkalmaz az összesre, és nem külön egyenként tartalmaznak egy képletet, ez óriási memória- és fájlméret megtakarítás • Ilyenkor nem az Excel szokásos cellaképlet számítási algoritmusát használja, hanem mivel tudja, hogy az összes cellában ugyanaz a fajta képlet van, gyorsítótárazást (Caching) alkalmaz, ami jóval gyorsabb. (Hasonlítsuk össze egy teljes munkalap méretű kevert hivatkozásos és adattáblás függvénytábla helyigényét és sebességét! Meg fogunk lepődni, hogy mennyivel kisebb és gyorsabb. Ezen a módon az Excel meglepő számolási teljesítményekre képes.)  Az adattábla egyértelmű hátránya, hogy valamivel nehezebb definiálni, mint a sima cella-képleteket • A másik hátrány, hogy az adattábla csak fix méretű cellatartomány lehet, holott az alkalmazásban a felhasználónak eltérő számú sorra és oszlopra van szüksége a különböző esetekben. Erre a következő megoldások léteznek: A, Az adattábla marad fix méretű, és akkorára méretezzük, hogy a felhasználó maximális sor/oszlop igénye beleférjen. De hogy ne írkáljon ki hülyeségeket, vagy hibaüzeneteket azokba a sorokba/oszlopokba, amit éppen a felhasználó nem használ, a mintaképletet úgy módosítjuk, hogy tesztelje a sor/oszlop fejléc feltöltöttségét (Header Empty Value Testing), és üres inputokra üres értéket adjon vissza: B, A bonyolultabb, de kisebb erőforrás igényű megoldás, hogy az adattáblát menetközben, makró kódból definiáljuk, pont akkorára, amekkora éppen kell (feltételezzük, hogy a TeljesTabla név egy korábban definiált dinamikus cellatartomány-hivatkozás, és a sor/oszlop input cellákat is elneveztük már korábban) : De itt általában nem éri meg makróval szenvedni, mert a fix méretű adattáblában az egyszerű feltöltöttségi tesztek oly gyorsan lefutnak, hogy nincs igazán érezhető teljesitményveszteség. • A függvénytáblák legtöbbször diagrammok bemenő adatait képezik, mert egy nagyméretű függvénytáblázatot önamagában a felhasználó nehézkesen tekintene át =HA(ÉS(SorInput<>””;OszlInput<>””); HA(SorInput*OszlInput<25;1-SorInput*OszlInput;1+SorInput*OszlInput); ””) With ActiveSheet .Range("TeljesTabla").Table RowInput:=Range("SorInput"),ColumnInput:=Range("OszlInput") End With

2-1.Házi feladat: Függvénytáblázat készítése adattáblával A 2-5.Gyakorló feladat megoldását alakítsa át úgy, hogy a függvénytáblát adattáblával számolja! Oldja meg, hogy a tábla lekezelje, ha a felhasználó csak kevesebb sort/oszlopot használ benne! A megoldás: 2-1HaziMegoldas.xls • Látható, hogy a mintaképlet ugyan bonyolultabb lett, mint az előző esetben, mert az egységköltségeket is bele kényszerülünk tenni, nem számolhatók ki külön. Ez azért van, mert az adattábla a sor/oszlop input hivatkozáson kívül minden más hivatkozást abszolútnak vesz • Viszont, mivel a képletből csak 1db van, szépen tabulálhatjuk, megjegyzésben kommentezhetjük, így mégis érthető lesz • Az adattábla viszont borzasztó gyorsan számol, és rugalmasan illeszkedik a felhasználó által megkövetelt mérethez (maximális méretén belül)

Excel konzultáció 2. Gyakorlat Dr. Pauler Gá bor , egyetemi docens, ev. Adószám: 63673852-3-22