Adatok érvényessége és lapvédelem

Nem nagyon fordult elő, hogy versenyen az Excel ezen funkcióit kérték volna számon, pedig a mindennapi életben gyakran lehet szükség rájuk. Ezért, és mert könnyedén előfordulhat, hogy ennek a két témának a bevonása lesz a versenyek következő újítása, szeretnék mutatni egy példát a használatukra.

Versenypélda híján egy saját példán keresztül fogom szemléltetni a lehetőségeket, melynek alapötletét egy feladatgyűjteményből merítettem (Reményi Zoltán - Siegler Gábor - Szalayné Tahi Zsuzsanna: Érettségire felkészítő feladatgyűjtemény, 86. oldal, 2. példa - Virágcsokor).

A feladat egy "bolondbiztos" számlázó elkészítése. A munkafüzet alapja biztosítva van a források közt, így nem kell a formázással bíbelődnünk, koncentrálhatunk a lényegre. A feladat A-C része nem tartozik a témához, a teljesség kedvéért azonban leírom ezen részek megoldását is.

A kép (nagyobb változata) külön ablakban is megtekinthető.85_full.jpg85. ábra: Az elkészítendő munkalap, mely védve van hibás adatok megadásától.

• Nyissuk meg a forrásfájlt, és rögtön mentsük is a mentés másként funkcióval, hogy a nyersfájlon ne történjenek módosítások: vedett_szamla.xlsx!
• A H5:I5 egyesített cellában az éppen aktuális időpnt kitöltése függvénnyel történik: =MOST(). Formázzuk a cellát úgy, hogy a dátum mellett az idő is látszódjon (például éééé.hh.nn. óó:pp formátumkóddal)!
• Írjunk be egy előforduló terméknevet, hogy megkönnyítsük a munkánkat! A cikkszám megkeresésére most nem alkalmas az FKERES() függvény, mivel az mindig a kijelölt tábla bal szélső oszlopában keres, és ha a kijelölés emiatt a B oszloptól kezdődne, akkor az A oszlopban lévő értékeket nem tudná visszaadni. Szerencsére az INDEX() és HOL.VAN() függvényegyüttes mindig működik:
  =INDEX(
  cikkek!$A$2:$A$12;
  HOL.VAN(számla!A9;cikkek!$B$2:$B$12;0)
  )
• Másoljuk a képletet az egész oszlopra (ezért kellettek a dollárjelek)!
• Így persze a kitöltetlen soroknál a #HIÁNYZIK üzenet jelenik meg. Ezen könnyedén javíthatunk, egy HA() függvény bevonásával.
  =HA(
  A9<>"";
  INDEX(
  cikkek!$A$2:$A$12;
  HOL.VAN(számla!A9;cikkek!$B$2:$B$12;0)
  );
  ""
  )

• A egységár megkeresése az előbbihez hasonlóan történik, töltsük ki ezt az oszlopot is!
• A nettó ár oszlopértékeinek kiszámításához csak egy egyszerű szorzásra van szükség, de most már figyelni kell arra is, hogy a mennyiség is meg legyen adva. A két feltétel összekapcsolása ÉS() függvénnyel történik:
  =HA(
  ÉS(A9<>"";C9<>"");
  C9*D9;
  ""
  )
• Az ÁFA értékének, a bruttó árnak a kitöltése az előbbihez hasonlóan, egyszerű szorzással, illetve összeadással történik. Különbség csupán az, hogy az ÉS() függvénynek ezeknél már három argumentuma lesz.
• A 25. sor kitöltése szinte triviális, elég hozzá egy-egy SZUM() függvény. A megfelelő cellaformátum kiválasztásáról itt se feledkezzünk meg!

A feladat ez idáig igazából gyerekjáték volt, és a lényeg még csak most jön: egyrészről be kell állítanunk a számlakibocsátó által kitöltendő cellákra, hogy milyen értékeket vehetnek fel, és hogy amennyiben érvénytelen adatot adnak meg, akkor mit tegyen az Excel. Másrészről meg kell védenünk a képleteket tartalmazó cellákat, nehogy valaki véletlenül elrontsa őket. Valójában ezek megvalósítása is nagyon egyszerű, de az a tapasztalatom, hogy a diákok (vagy akár tanárok) valamiért kevésbé szokták ismerni ezt a lehetőséget. Pedig a mindennapi életben igencsak hasznos lehet.

• Jelöljük ki mindhárom, cím megadására fenntartott cellát, és válasszuk az adatok fül érvényesítés gombját!
• Először az érvényességi feltételt kell megadnunk. A feladat kérése alapján válasszuk a legördülő listából azt, hogy szöveghossz, majd állítsuk be, hogy Nagyobb vagy egyenlő, minimumnak pedig adjuk meg a tizenötöt!
• Ezután kattintsunk át az ablak hibajelzés fülére! Itt lehet megadni, hogy miként járjon el a program, ha érvénytelen adatot adtak meg. Most nekünk megállásra van szükségünk, hiszen nem fordulhat elő, hogy tizenötnél kevesebb karaktert adjanak meg cím gyanánt. Ugyanitt adjuk meg a hibaüzenetet is, másoljuk be a feladatban megadott szöveget! Adhatunk neki címet is, például "Figyelem!".
• A nevek érvényességének beállításánál hasonlóan járjunk el! Most azonban engedélyezett az "érvénytelen" adat megadása is, hiszen ha ritkán is, de előfordulhatnak nagyon rövid nevek. Amennyiben a hibajelzés fülön a megállás helyett a figyelmeztetést adjuk meg a hibajelzés stílusának, akkor lesz mód a figyelmeztető üzenet elolvasása után megerősíteni a programot abban, hogy direkt adtunk meg ilyen rövid nevet.
• A termékek nevének kiválasztásához könnyedén készíthetünk legördülő listát, ugyancsak az érvényesítés ablakban. A feltétel legyen lista, a forrást pedig jelöljük ki egérrel! Ettől még lesz lehetőség a terméknevet gépeléssel is megadni, de elírás esetén az Excel alapértelmezett hibaüzenete fog megjelenni, amennyiben nem állítottunk be a hibajelzés fülön egyebet.

• Az ÁFA beállítása úgy történik, mint az előbb. A hibajelzés stílusáról vagy az üzenetről most sem mondott semmit a feladat, tehát maradhat minden alapértelmezett.
• A mennyiség esetén fontos, hogy csak egész számot lehessen megadni. Az érvényesítés ablakban válasszuk az egész szám feltételt, és adjuk meg a minimumot és a maximumot is! A hibajelzés stílusa ismét megállás (hogy sehogy se lehessen mást megadni), az üzenetet másoljuk be!
• A dátum értékeinek felülbírálásához válasszuk a szokásos ablakban az időt, jellegnek pedig azt, hogy nagyobb vagy egyenlő. A kezdő időpont a kiállítás dátumától függ, ezért kattintsunk az ezt tartalmazó cellára! Ahhoz, hogy továbbmenjünk, emlékezni kell az Excel dátum- és időkezelésére: minden időpont egy tizedes tört, melynek egészrésze a napokat, törtrésze az időpontokat ábrázolja. Mivel negyvennyolc óra az két nap, ezért adjunk a kiállítás dátumához kettőt, kezdő időpont gyanánt!

Ezzel végeztünk a feladatnak azon részével, ami a cellák tartalmának korlátozásával kapcsolatos. Szeretném megjegyezni, hogy vannak még egyéb módszerek is ebben a témában. Például olykor az is elég lehet, hogy beállítjuk az érvényességi feltételeket, mindenféle üzenet vagy megállítás nélkül. Ezt követően választhatjuk az érvényesítés csoport azon funkcióját is, mely bekarikázza a rossz (érvénytelen) adatokat. Ez a mindennapi életben is hasznos lehet, de el tudnám képzelni versenyfeladatnak is: "Karikázzuk be a táblázatban a tíznél nagyobb értékeket!". Feltételes formázásnál ilyen formátumot nem lehet megadni, igaz, ahhoz, hogy ez a módszer egy ilyen feladat esetén jól működjön, minden változtatást követően rá kellene kattintani a bekarikázásra.

Amiről még nem beszéltem, az a feltétel megadásánál lévő jelölőnégyzet: üres cellák mellőzése. Alapértelmezettként ki van pipálva, hiszen ritkán fordul elő, hogy nem akarjuk engedélyezni a cellák - akár ideiglenesen történő - üresen hagyását.

A kis kitérő után térjünk vissza a feladathoz, hiszen még hátra van az E-H rész megoldása!

• A kiszámolt értékek cellájára ne állítsunk be érvényességi feltételt, hanem csak a figyelmeztető üzenet fülön adjuk meg a szöveget. Ki van pipálva, hogy már a cella kijelölésekor jelenjen meg az üzenet, ezen nem kell változtatni.
• Jelöljük ki az egész számlát (A1:I30), és válasszuk a helyi menü/ Cellaformázás parancsot! A Védelem fülön állítsuk be, hogy a cellák legyenek zároltak!
• Jelöljük ki a sárga cellákat, és állítsuk vissza, hogy ezek ne legyenek zároltak!

Ez a két lépés gyorsabb volt, mint ha kijelöltük volna az összes nem sárga cellát.

• Jelöljük ki a képleteket tartalmazó cellákat, és ezekre állítsuk be azt is, hogy legyenek rejtettek!

• Válasszuk a Korrektúra/ Lapvédelem parancsot! Az alapértelmezett beállítások megfelelőek lesznek, jelszót ne adjunk meg!

A valós életben persze nincs sok értelme a jelszó nélküli lapvédelemnek, hiszen így bárki ki tudja kapcsolni azt, és ismét szerkeszthetővé válnak a zárolt cellák, olvashatóvá a rejtett cellák képlete. Versenyen viszont garantáltan nem fogják tőlünk jelszó megadását kérni, hacsak nem egy megadott jelszóval. Az sem valószínű, hogy valaki Excellel készíti a számláit. Az általunk készített munkalapon például azonnal frissül a MOST() függvénnyel megadott kiállítási dátum, így az tényleg csak arra lenne jó, hogy kinyomtassuk. A feladatot mégis alkalmasnak éreztem a két funkció használatának bemutatására, és egy 45 perces óra kereteibe is tökéletesen illeszthető: a diákok ismételhetik a korábban tanultakat, és arra építve, új ismereteket is szerezhetnek.

Vissza a tartalomjegyzékhez