Űrlapkezelés Copy

Az űrlapok olyan az adatbevitelt támogató objektumok, amelyek sok állandó szövegrész mellett viszonylag kisszámú kitöltendő mezőt tartalmaznak. Az űrlapok különleges vezérlőkkel és képletekkel az űrlap kitöltését és a beírt adatok feldolgozását automatizáló, formázott munkalapok. Egy űrlapot védett sablonná téve megtehetjük, hogy a felhasználók csak a változó mezőket írhatják be. A változó mezők bevitelére párbeszédpanelt is létrehozhatunk. Ezek a lehetőségek az Excelben a Visual Basic programozással együtt használhatók ki igazán.

Az ilyen párbeszédpaneleken létrehozott nyomógombok egy makrót futtatnak, ha rákattintunk. Az űrlapok és makrók kezelésének bemutatására egyetlen, részletes példát alkalmazunk.

6. Feladat

Készítsünk új autók részletfizetéses értékesítéséhez egy olyan nyilvántartást, amelyben lekérdezhető a részletfizetés mértéke a különböző bruttó árak és kezdőrészletek figyelembe vételével! A végleges megoldásban minden feltételt űrlap vezérlőelemekkel, vagyis kapcsolókkal, jelölőnégyzetekkel, listával állítsunk be! Az ilyen megoldásokat bátran odaadhatjuk „laikus” Excel felhasználónak is, megfelelő védelem (és archiválás) mellett kárt nem tehet benne, viszont biztosak lehetünk a megfelelő eredményekben.

A feladat alapadatai (más termékek is lehetnek…)

Megoldás

  1. Rögzítsük az alapadatokat a mellékelt minta szerint!
  2. Jelenítsük meg a Fejlesztőeszközök szalagot (ha a szalag füle nem látszana, akkor adjuk ki a Fájl szalagmenü Beállítások parancsát, majd Az Excel beállításai párbeszédpanelen, a Menüszalag testreszabása kategóriában jelöljük be a Fejlesztőeszközök jelölőnégyzetet)!
  3. Válasszuk ki a Beszúrás gomb menüjében az Űrlap-vezérlőelemek közül a választókapcsolót, majd húzással adjuk meg a munkalapon a rádiógomb helyét! Hasonló megoldással járhatunk el, ha az ActiveX-vezérlők közül választjuk az OptionButton elemet, azonban ekkor a vezérlő formázása később kissé eltér.

Az első választókapcsoló

  1. Kattintsunk a most létrehozott objektum szövegébe és nevezzük át 65 %-ra!

Nevezzük el csökkenő sorrendben a választókapcsolókat

  1. Nyolc kezdőrészletet alakítsunk ki 5 %-os lépésekben. Ehhez a Ctrl billentyű nyomva tartása közben húzzuk új helyre az objektumot, majd a Ctrl billentyű felengedésével megtörténik a másolás. Végezzük el ezt a műveletsort hétszer! 
  2. Ezzel az első kezdőrészlet alatt megjelenik a többi hét választókapcsoló, amelyeket nevezzünk át a kezdőrészleteknek megfelelően. Az átnevezéshez a jobb egérgombbal kattintsunk az objektumba! Ha így nem sikerülne a szerkesztő üzemmódba lépnünk, akkor a kapcsoló kijelölése után adjuk ki a helyi menü Szöveg szerkesztése parancsát!
  3. Jelöljük ki a választókapcsolókat és a Rajzeszközök – Formátum szalag Igazítás – Balra igazítás paranccsal rendezzük egy oszlopba! 
  4. Az Igazítás – Függőleges elosztás paranccsal rendezzük őket egyenlő távolságra!
  5. Foglaljuk egy csoportba a választókapcsolókat az Űrlap-vezérlőelemek csoportpanel elemével! A csoportpanel gombra kattintás után húzással kerítsük körbe a választókapcsolókat!

A választókapcsolók egy csoportba tartoznak

  1. A csoportpanelt nevezzük át Előlegre!
  2. Válasszuk az Űrlap-vezérlőelemek csoport Beviteli lista elemét, majd húzással hozzuk létre a legördülő listát!

A lenyíló listában majd a gépkocsikat választjuk ki

  1. Adjuk ki a Fejlesztőeszközök szalag Tulajdonságok parancsát (vagy a jobb egérgombbal kattintsunk a beillesztett kombi panelre és adjuk ki a helyi menü Vezérlő formázása parancsát)! A Vezérlő formázása párbeszédpanelen kattintsunk a Bemeneti tartomány mezőbe, majd húzással jelöljük ki a listaelemeket tartalmazó cellatartományt, példánk szerint az A2:A17 tartományt!

A legördülő listát a táblázat adataihoz kapcsoljuk

  1. Kattintsunk a Cellacsatolás mezőbe, majd a C1 cellába! Itt jelenik meg majd a kiválasztott elem sorszáma, amit a továbbiakban felhasználunk.
  2. A gépkocsi bruttó árát a G15 cellában automatizáltan jelenítjük meg. Ehhez írjuk a tárolt adatokra, és a C1 cellában lévő cellacsatolásra, mint indexre hivatkozó következő képletet a cellába:  =INDEX(B2:B17;C1)*1,25. A szorzást a bruttó ár miatt végezzük.
  3. Módosítsuk úgy a megoldást, hogy egy jelölőnégyzettel lehessen választani, a bruttó és a nettó ár között. Kattintsunk ehhez az Űrlapok eszköztár jelölőnégyzet ikonjára, majd húzással rajzoljuk meg és nevezzük át „25%-os ÁFA mellett”-re!

A gépkocsi árát függvénnyel keressük ki a tárolt adatok közül

  1. A Vezérlő formázása párbeszédpanelen ezt az objektumot csatoljuk a D1 cellához, amely majd vezérli az árat és a címkét tartalmazó cellákat! A vezérléshez írjuk át a G15 cella képletét a következőre: =HA(D1=Igaz;INDEX(B2:B17;C1)*1,27;INDEX(B2:B17;C1))

A jelölőnégyzet szabályozza az áfás megjelenítést

  1. Az E15 cellában lévő ármegjelölés változzon a jelölőnégyzettől függően. Ehhez az E15 cella tartalmát cseréljük ki a következő képletre:       
    =HA(D1=IGAZ;”A gépkocsi bruttó ára:”;”A gépkocsi nettó ára:”) Most már a szöveg és az érték is egyaránt változik a jelölőnégyzet állásától függően.
  2. Most a finanszírozási értékeket számoljuk ki. Ehhez a választókapcsolókat használjuk. Jelöljük ki az egyik választókapcsolót, majd jelenítsük meg a Vezérlő formázása párbeszédpanelt! Adjuk meg cellacsatolásként az E1 cellát!

Elég egyetlen vezérlőt formázni a csoportból

  1. Most az előleg összegét számoljuk ki a választókapcsolóknak megfelelően. A 8 választókapcsoló már eléggé összetett HA utasítást eredményezne, ezért inkább a változóérték alapján vezérelt többirányú elágazást alkalmazzuk, mégpedig Visual Basic szerkesztőben megadott felhasználói függvényként. Írjuk a következő ábra szerinti kódot a szerkesztőablakba! Ehhez előbb adjuk ki a Fejlesztőeszközök szalag Visual Basic parancsát, vagy nyomjuk meg az Alt+F11 billentyűkombinációt, utána kattintsunk a jobb egérgombbal a projektablakban a munkalapunk (Autókereskedés) nevére, majd adjuk ki a helyi menü Insert 🞂 Module parancsát! Most már bevihetjük a függvény kódját, amely a modulba kerül (különben nem működött volna a függvény).
  2. Az előlegszámító függvénynek két paramétere van, az előlegfizetés százaléka, amelynek cellakapcsolata az E1 cellával van, valamint a gépkocsi ára, ami a G15 cellában található. Írjuk ezért az =előleg(E1;G15) képletet a G16 cellába! Próbáljuk ki az eddigi elemek működését!
  3. Most készítsük el az Előleg csoporthoz hasonlóan a Kezelési költség csoportot is, amelybe négyféle (0, 5%, 10% és 15%) kezelési költség beillesztését biztosító választókapcsolót vegyünk fel a Beszúrás gombmenü Űrlap-vezérlőelemek csoportjából! 

Az előlegszámító függvény

  1. Válasszuk ki ez egyik csoportbeli választókapcsolót, majd az Vezérlő formázása párbeszédpanel Vezérlő lapján adjuk meg a cellacsatolást az F1 cellához!
  2. A finanszírozott összeg a bruttó ár-előleggel egyenlő, ez kerül a G17 cellába: =INDEX(B2:B17;C1)-G16
  3. A kezelési költséget kiszámoló képletet ekkor a G18 cellába írjuk:    =HA(F1=1;0;HA(F1=2;G17*0,05;HA(F1=3;G17*0,1; HA(F1=4;G17*0,15)))),    
    vagyis a finanszírozott összegből számoljuk a kezelési költséget a választókapcsolók állásának megfelelően.
  4. Hozzuk létre a Futamidő beviteli cellát a G19 cellában!
  5. Készítsük el a Kamat beviteli mezőt is a G20 cellában! 
  6. Formázzuk százalék megjelenítésűre, a többi pénzérték megjelenítési cellát pénznem formátumra formázzuk és töröljük a tizedesek megjelenítését! 

A kezelési költséget számoló képlet és választókapcsolói

  1. Hozzuk létre az eredmény megjelenítésére a Havi részlet mezőt a G21 cellában, melynek számítási képlete: =RÉSZLET(G20/12;G19;-G17)
  2. Rejtsük el a cellarácsot a Nézet szalag Rácsvonalak jelölőnégyzetének törlésével!
  3. Adjunk egy pasztellszínű hátteret a számítási területhez! Ehhez jelöljük ki a C8:H22 területet, majd a kitöltőszín gombról, vagy a Cellák formázása párbeszédpanel Mintázat lapjáról állítsunk be háttérszínt.
  4. A C1:F1, G19 és a G20 cellák védelmét kapcsoljuk ki, hiszen a vezérlőkön kívül csak itt lehet majd bevinni adatot. A C1:F1 cellák azok, amelyekhez a vezérlők cellacsatolásait megadtuk, így ezek zárolása esetén a vezérlő sem lenne használható, viszont az alábbiak szerint az első sort is elrejthetjük.

Kikapcsoljuk a cellák védelmét

  1. AC8:H22 területen kívüli összes sort és oszlopot rejtsük el! Ehhez jelöljük ki a megfelelő sorokat és adjuk ki a Kezdőlap/Formátum gombmenü Láthatóság/Elrejtés és felfedés Sorok elrejtése, valamint az oszlopok kijelölése után az Oszlopok elrejtése parancsokat! Emlékeztetőül: a törlendő sorok kijelöléséhez válasszuk ki a 23 jelű sort, majd nyomjuk meg a Ctrl+Shift+🡫 kombinációt, a törlendő oszlopok kijelöléséhez válasszuk ki az I jelű oszlopot, majd nyomjuk meg a Ctrl+Shift+🡪 kombinációt!
  2. Kapcsoljuk be a lapvédelmet a Véleményezés szalag Lapvédelem parancsával! A párbeszédpanelen nem kell állítani semmit, legfeljebb a védelem feloldásához szükséges jelszót adjuk meg. Ezt viszont jól jegyezzük meg, mert elfelejtése esetén készíthetjük a feladatot elölről! Érdemes lehet a munkafüzetet védelem nélkül is elmenteni, de csak a védett változatot közzétenni. A védelem bekapcsolható a Fájl szalagfül Füzetvédelem Lapvédelem parancsával is (lásd az ábrán).

Lapvédelem bekapcsolása a Fájl menüből

A modell működik, próbáljuk ki. Adatokat csak a G19:G20 cellákba vihetünk be gépeléssel, a futamidő és a kamat értékén kívül minden paramétert a vezérlőkkel adunk meg. 

Természetesen az eljárás továbbfejleszthető további autókkal (ekkor a cellacsatolást kell módosítani a kombipanelhez) az extra tartozékok (listáról történő) kiválasztásának és ezek értékének figyelembe vételével, amely értékek a nettó árhoz adódnak. A tartozékok figyelembe vételéhez az autók kiválasztásához hasonló kombi panelt javaslunk.

A kész megoldás

Akár a sor- és oszlopazonosítókat is elrejthetjük Az Excel beállításai párbeszédpanel Speciális lapján a Beállítások ehhez a munkalaphoz csoport Sor- és oszlopazonosítók megjelenítése jelölőnégyzetének törlésével (lásd az ábrát).