Arkusz do naliczania opłat za udział w zajęciach dodatkowych ... · Jako bazowy program...

Organizacja pracy kuchni

© Copyright by Dr Josef Raabe, Spółka Wydawnicza sp. z o.o.

1

I2.3Intendentura

Doradca intendenta przedszkola ■ maj 2012

U04

0512

U04

0512

Dariusz Kwiecień

Arkusz do naliczania opłat za udział w zajęciach dodatkowych organizowanych w przedszkolu – w programie MS Excel

Spis treści1. Wstęp . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22. Tabela podstawowych danych . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23. Filtry . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44. Arkusz obecności . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85. Tworzenie formuł obliczeniowych . . . . . . . . . . . . . . . . . . . . . . . . 136. Adresowanie względne i bezwzględne . . . . . . . . . . . . . . . . . . . . . 147. Reguły naliczania opłat . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168. Zabezpieczanie przed przypadkową edycją . . . . . . . . . . . . . . . . . 189. Ochrona danych osobowych . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20

9167_I_2_3_Kwiecien.indd 1 27.04.2012 15:49

2


I2.3


Intendentura


U04

0512

U04

0512

1. Wstęp

Celem artykułu jest przygotowanie z Czytelnikiem arkusza do naliczania opłat za zajęcia dodatkowe w przedszkolu. Krok po kroku przedstawimy i opiszemy czynności, które należy wykonać, aby stworzyć arkusz uła-twiający pracę. Przedstawiamy najprostszy sposób przygotowania arku-sza, tak aby praca z nim nie sprawiała problemu początkującym użyt-kownikom.

Jako bazowy program przyjęliśmy arkusz kalkulacyjny MS Excel 2010, jednak podajemy także przykłady z arkusza MS Excel 2003 i Calc, będą-cego składową darmowego pakietu OpenOffice (w najnowszej dostępnej wersji 3.3).

2. Tabela podstawowych danych

Zadaniem każdej bazy danych jest przechowywanie danych oraz udo-stępnianie ich użytkownikowi w jak najbardziej przyjazny sposób. Baza powinna być zbudowana tak, by dane wprowadzać do niej tylko jeden raz. Powinna odwoływać się tylko do jednego miejsca, dzięki czemu, gdy zajdzie potrzeba aktualizacji danych, będziemy zmieniać je tylko w jed-nym miejscu. Dlatego tak ważnym etapem jest samo projektowanie bazy.

Otwórzmy nowy dokument programu MS Excel. Zwykle wraz z nowym dokumentem zostają otwarte trzy arkusze, o czym świadczą odpowiednie zakładki znajdujące się w dolnej części okna programu po lewej stronie.

Rys. 1. Widok standardowych arkuszy

Aby zmienić nazwę arkusza, kliknijmy na domyślną nazwę „Arkusz 1” prawym klawiszem myszy, następnie z wyświetlonego menu wybierzmy opcję „Zmień nazwę”. Nadajmy temu arkuszowi nazwę „Lista_dzieci”.

W tym arkuszu zgromadzimy podstawowe dane, które będą wykorzysty-wane w całym programie. Zacznijmy więc od nazwania kolumn (pól).

9167_I_2_3_Kwiecien.indd 2 27.04.2012 15:49



3

I2.3Intendentura


U04

0512

U04

0512

Rys. 2. Tworzenie tabeli podstawowych danych – lista dzieci

Pierwsze pole (kolumnę) przeznaczymy na nazwisko i imię oraz grupę, do której przypisane jest dziecko. Wprowadzenie grupy pozwoli nam od-powiednio filtrować listę dzieci oraz identyfikować konkretne nazwisko i imię w sytuacji, gdyby w przedszkolu były dzieci, które nazywają się tak samo. Gdyby zdarzyło się, że w tej samej grupie znajdują się dzieci o tym samym imieniu i nazwisku, należy dla nich wprowadzić jeszcze do-datkowy element identyfikujący, np. drugie imię lub oznaczenie literowe wskazujące na powszechnie znaną cechę charakterystyczną dziecka.

Od razu można wybrać styl dla tabeli, co pozwoli programowi określić wiersz nagłówkowy oraz włączyć filtry. W tym celu należy zaznaczyć ko-mórkę A1 do A9, następnie: Office 2010 i 2007 – wybieramy z menu górnego zakładkę „Narzędzia główne”, dalej ikonę „Formatuj jako tabelę”. Z rozwiniętego menu wybieramy dowolny styl tabeli. Zostanie wyświe-tlone okno dialogowe, w którym program ustala z nami obszar tabeli. Sprawdzamy, czy w arkuszu zostały zaznaczone pływającą ramką wybra-ne komórki oraz czy została zaznaczona opcja „Moja tabela ma nagłówki”, i akceptujemy wybór, przyciskając klawisz „OK” (rys. 3).

Rys. 3. Ustalenie obszaru przy wyborze stylu tabeli

9167_I_2_3_Kwiecien.indd 3 27.04.2012 15:49

4


I2.3


Intendentura


U04

0512

U04

0512

Tabela zostanie sformatowana zgodnie z wybranym stylem. W pierwszym polu wyświetli się strzałka, której obecność mówi nam, że są włączone filtry. Filtry będą bardzo przydatne w późniejszej analizie danych.

Rys. 4. Wygląd tabeli po zastosowaniu stylu tabeli

Może się wydawać, że tabela jest ograniczona do liczby wierszy zapeł-nionych danymi. Jednak gdy tylko dopiszemy kolejne nazwisko, tabela zostanie automatycznie rozszerzona o nowe wiersze.

Excel 2003 – również zaznaczamy komórki A1 do A9 (lub kolejne komór-ki, jeśli zawierają dane), następnie wybieramy z menu górnego „Format/Autoformatowanie” (rys. 5).

Rys. 5. Wybór formatowania tabeli w Excel 2003

W oknie dialogowym, które zostanie wyświetlone, wybieramy styl tabeli i akceptujemy wybór poprzez kliknięcie przycisku „OK”.

3. Filtry

Następnie należy włączyć filtry. W tym celu zaznaczamy ponownie komórki zawierające dane, na przedstawionym przykładzie są to komórki A1 do A9, a z menu górnego wybieramy „Dane/Filtr/Autofiltr” (rys. 6).

9167_I_2_3_Kwiecien.indd 4 27.04.2012 15:49



5

I2.3Intendentura


U04

0512

U04

0512

Rys. 6. Aktywacja autofiltru w Excel 2003

Cal (Open Office) – w tym programie rezygnujemy z wyboru stylu dla tabeli, włączamy jedynie filtr, dokładnie w taki sam sposób, jak w przy-padku MS Excel 2003.

Przygotowaliśmy zatem tabelę, w której mogą pojawić się dane.

Opisany sposób organizacji danych został celowo uproszczony, tak aby ułatwić ich filtrowanie. Jednak aby filtrowanie było skuteczne, należy być konsekwentnym we wprowadzaniu danych. Należy przestrzegać założonego układu, tzn. najpierw wpisujemy nazwisko, rozpoczynając od wielkiej litery, następnie, po spacji, imię oraz – również po spacji – oznaczenie grupy, które nie zawiera spacji. Można oczywiście przyjąć inny wzorzec danych, najważ-niejsza jest jednak konsekwencja w trzymaniu się założonych stylów.

Filtrowanie przydaje się przede wszystkim w sytuacji, gdy danych jest dużo. Dzięki włączonemu filtrowi dla pola (kolumny) z nazwiskami dzieci mamy możliwość w łatwy sposób szybko ograniczyć wyświetlanie danych do podanych kryteriów filtrowania.

Załóżmy, że chcemy wyświetlić nazwiska tylko tych dzieci, które są przy-pisane do konkretnej grupy. Klikamy na strzałkę znajdującą się w wier-szu nagłówkowym, następnie z rozwiniętego menu wybieramy „Filtry tek-stu” oraz „Zawiera” (rys. 7).

Rys. 7. Filtrowanie pól tekstowych

9167_I_2_3_Kwiecien.indd 5 27.04.2012 15:49

6


I2.3


Intendentura


U04

0512

U04

0512

W wyświetlonym oknie dialogowym wpisujemy ciąg znaków, które chce-my odnaleźć w przeszukiwanym polu (kolumnie) (rys. 8).

Rys. 8. Określenie filtrującego ciągu znaków

Po zaakceptowaniu wyboru zostaną wyświetlone tylko te rekordy (wiersze), które zawierają wpisany ciąg znaków. Przy symbolu strzałki wyświetli się dodatkowy symbol mówiący nam, że pole (kolumna) jest filtrowane, a co za tym idzie – ukryte są te wpisy, które nie spełniają warunku filtrowania (rys. 9). Numery wierszy wyświetlają się w innym kolorze (niebieskim), tak aby pokazać użytkownikowi, że pomiędzy wyświetlonymi wierszami mogą znajdować się jeszcze inne, ukryte w wyniku filtrowania danych.

Rys. 9. Efekt filtrowania oraz symbol wskazujący na włączenie filtru

Aby odwołać działanie filtru, należy kliknąć w ikonę symbolizującą włą-czenie filtru, następnie z wyświetlonego menu należy wybrać opcję „Wy-czyść filtr z…” (rys. 10).

Rys. 10. Odwołanie filtru

9167_I_2_3_Kwiecien.indd 6 27.04.2012 15:49



7

I2.3Intendentura


U04

0512

U04

0512

Excel 2003 – w tej wersji programu, aby uruchomić filtr, również klika-my w ikonkę strzałki w prawym górnym rogu wybranego pola, następnie z rozwiniętego menu wybieramy opcję „Niestandardowe” (rys. 11).

Rys. 11. Wybór filtra niestandardowego dla Excel 2003

W wyświetlonym oknie dialogowym wskazujemy warunek i akceptujemy wybór poprzez kliknięcie przycisku „OK” (rys. 12).

Rys. 12. Wybór filtra ciągu znaków w Excel 2003

Calc (OpenOffice) – podobnie klikamy w wiersz nagłówkowy, z menu gór-nego wybieramy „Dane/Filtr/Filtr standardowy” (rys. 13).

Rys. 13. Aktywacja filtru w OpenOffice Calc

9167_I_2_3_Kwiecien.indd 7 27.04.2012 15:49

8


I2.3


Intendentura


U04

0512

U04

0512

W wyświetlonym oknie dialogowym umieszczamy warunek oraz badaną wartość (rys. 14).

Rys. 14. Określenie warunku filtrowania w OpenOffice Calc

4. Arkusz obecności

Przejdźmy do następnego arkusza i zmieńmy jego nazwę, np. na „Obec-nosci” (rys. 15). Exel 2003 – tu nie przechodzimy do nowego arkusza, opis danych tworzymy np. w kolumnie B.

Rys. 15. Ustalenie nazwy kolejnego arkusza

Tworzymy opis danych, które chcemy wprowadzać do tabeli, określamy przyjazny wygląd oraz wpisujemy dane przykładowe (rys. 16).

Rys. 16. Umieszczenie danych źródłowych

Należy zwrócić uwagę, aby dane zostały wprowadzone w formacie ak-ceptowanym przez arkusz kalkulacyjny. Tak więc w odpowiedni sposób wprowadzamy datę (np. rok-miesiąc-dzień), liczby oraz wartości waluto-we (wraz z symbolem waluty).

Aby odpowiednio umieścić dane, niektóre komórki można połączyć (scalić). W tym celu należy zaznaczyć komórki, które mają być scalone (na rys. 16 są to np. komórki C1 i D1), następnie w menu górnym wybieramy zakładkę „Narzędzia główne” i jedną z dostępnych opcji w grupie scalania (rys. 17).

9167_I_2_3_Kwiecien.indd 8 27.04.2012 15:49



9

I2.3Intendentura


U04

0512

U04

0512

Rys. 17. Scalanie zaznaczonych komórek

Excel 2003 – aby scalić komórki, należy zaznaczyć komórki, które mają być połączone. Następnie z menu górnego wybrać opcję „Format/Komór-ki”. W wyświetlonym oknie dialogowym należy wybrać zakładkę „Wyrów-nanie” i zaznaczyć opcję „Scal komórki” (rys. 18). W ten sam sposób moż-na scalać komórki również w Excel 2007 i 2010.

Rys. 18. Scalanie komórek Excel 2003

OpenOffice Calc – aby scalić komórki, po ich zaznaczeniu należy skorzy-stać z ikonki znajdującej się w górnym pasku ikon (rys. 19).

Rys. 19. Ikona scalania w OpenOffice Calc

9167_I_2_3_Kwiecien.indd 9 27.04.2012 15:49

10


I2.3


Intendentura


U04

0512

U04

0512

W kolejnym kroku przygotujemy miejsce na wprowadzanie nazwisk dzie-ci uczestniczących w konkretnych zajęciach wraz z terminami i możliwo-ścią zaznaczania frekwencji, jak również pola obliczeniowe – sumujące należne wpłaty (rys. 20).

Rys. 20. Organizacja danych obliczeniowych

W pierwszym arkuszu mamy już tabelę z nazwiskami dzieci. Każda baza powinna być tak zaprojektowana, aby nie trzeba było wielokrotnie wprowadzać tych samych danych. Dlatego dane z pierwszego arkusza (Lista_dzieci) potrak- tujemy jako listę danych, które mogą być wczytywane do innych tabel (arkuszy). W tych miejscach zostaną wyświetlone listy, z których będziemy mogli wybrać odpowiednie nazwisko, zamiast wpisywać je ponownie.

W tym celu zaznaczamy w arkuszu „Obecnosci” komórki, w których mają znaleźć się nazwiska uczniów. Następnie z menu górnego wybieramy zakładkę „Dane” i klikamy ikonę „Poprawność danych”. W rozwiniętym menu wybieramy opcję „Poprawność danych” (rys. 21).

Rys. 21. Wybór opcji poprawności danych

Wyświetli się okno dialogowe (rys. 22), w którym w polu „Dozwolone” wy-bieramy pozycję „Lista”. Klikamy pole „Źródło”.

Rys. 22. Tworzenie listy wyboru

9167_I_2_3_Kwiecien.indd 10 27.04.2012 15:49



11

I2.3Intendentura


U04

0512

U04

0512

Przechodzimy do arkusza „Lista_dzieci” i zaznaczamy kolumnę przezna-czoną do wpisywania nazwisk dzieci. Nie zaznaczamy wiersza nagłów-kowego. Ewentualnie wpisujemy w pole „Źródło” adresowanie źródła da-nych (rys. 23).

Rys. 23. Wskazanie źródła danych dla listy

Zatwierdzamy wybór poprzez kliknięcie przycisku „OK”. W arkuszu „Obec-nosci” po kliknięciu komórki, w której ma się znaleźć nazwisko dziecka, zostanie wyświetlona strzałka pozwalająca rozwinąć listę (rys. 24).

Rys. 24. Działanie listy wyboru

Excel 2003 – listę wyboru tworzy się w podobny sposób jak w przypadku Excel 2007 i 2010. Należy zaznaczyć komórki, w których lista wyboru ma się uaktywnić, następnie z menu górnego wybrać „Dane/Sprawdzanie poprawności” (rys. 25). Różnica polega jedynie na tym, że dane źródłowe (lista nazwisk) i komórki z rozwijalną listą muszą znajdować się w tym samym arkuszu. Dlatego listę dzieci, po wprowadzeniu wszystkich na-zwisk, można przygotować w kolumnie A, następnie ukryć tę kolumnę. W tym celu należy kliknąć prawym klawiszem myszy nazwę kolumny „A” i z rozwiniętego menu wybrać opcję „Ukryj”. Aby odkryć ukrytą kolum-nę, należy zaznaczyć kolumny, pomiędzy którymi znajduje się kolumna ukryta, następnie w nagłówek kolumn kliknąć prawym klawiszem myszy i z rozwiniętego menu wybrać opcję „Odkryj”.

9167_I_2_3_Kwiecien.indd 11 27.04.2012 15:49

12


I2.3


Intendentura


U04

0512

U04

0512

Rys. 25. Aktywowanie listy wyboru w Excel 2003

OpenOffice Calc – w przypadku tego programu lista nie będzie pobiera-na z tabeli. Listę wyboru definiujemy bezpośrednio w oknie dialogowym służącym do określenia listy. Okno to wywołujemy poprzez kliknięcie w menu górnym opcji „Dane/Poprawność danych” (rys. 26).

Rys. 26. Lista wyboru w OpenOffice Calc

W arkuszu „Obecnosci” umieściliśmy daty dni, w których odbywają się zajęcia. Ponieważ obecność lub nieobecność na zajęciach możemy zazna-czać tylko jednym znakiem, kolumna zagospodarowana przez datę może być węższa (rys. 27).

Rys. 27. Domyślny, poziomy sposób wpisywania danych do komórek

9167_I_2_3_Kwiecien.indd 12 27.04.2012 15:49



13

I2.3Intendentura


U04

0512

U04

0512

Zmienimy orientację wpisów w komórkach daty. Aby to zrobić, zaznacza-my te komórki, następnie klikamy prawym klawiszem myszy zaznaczony obszar i z wyświetlonego menu wybieramy „Formatuj komórki”. Wybiera-my zakładkę „Wyrównanie” i zmieniamy kąt na 90o (rys. 28).

Rys. 28. Zmiana orientacji tekstu we wskazanych komórkach

Po ostatnich zmianach wyglądu arkusz „Obecnosci” może wyglądać tak jak na rysunku 29.

Rys. 29. Arkusz Obecnosci

5. Tworzenie formuł obliczeniowych

Dotychczasowe czynności miały na celu przygotowanie arkusza do wy-godnego wprowadzania danych oraz filtrowania ich w miarę potrzeby. Ma on wyręczyć użytkownika w przeliczaniu opłat za zajęcia, liczby zajęć oraz frekwencji.

Na początek zajmiemy się zliczeniem godzin obecności dziecka na zaję-ciach. Zanim jednak nauczymy arkusz, skąd ma czerpać dane i jak je analizować, musimy zdecydować, w jaki sposób oznaczamy w arkuszu frekwencję – czy umieszczamy umówiony lub dowolny znak w komórce odpowiadającej dacie zajęć jako oznaczenie obecności, czy nieobecności. W ćwiczeniu zakładam, że znak „X” oznacza obecność na zajęciach.

9167_I_2_3_Kwiecien.indd 13 27.04.2012 15:49

14


I2.3


Intendentura


U04

0512

U04

0512

W arkuszu kalkulacyjnym możemy w komórkach umieszczać dane wpi-sywane z klawiatury, ale możemy również tworzyć formuły obliczeniowe, za pomocą których arkusz kalkulacyjny przeprowadzi wskazane oblicze-nia i wyświetli wynik. Każda formuła obliczeniowa rozpoczyna się od zna-ku równości (=), który mówi programowi, że ma przeprowadzić obliczenia. Arkusze kalkulacyjne mają wiele wbudowanych funkcji, które wykorzy-stuje się w trakcie pisania formuł obliczeniowych.

W kolumnie „Lista obecnych” skorzystamy z funkcji „ile.niepustych()”. Klikamy komórkę C9 (rys. 30) i wprowadzamy formułę: „=ILE.NIEPUSTY-CH(E9:M9)”. Zadaniem tej formuły jest policzenie komórek, w których znajduje się jakikolwiek znak ze wskazanego zakresu: od komórki E9 do komórki M9.

Rys. 30. Wprowadzenie formuły z wykorzystaniem funkcji „ile.niepustych()”

6. Adresowanie względne i bezwzględne

Formuły obliczeniowe można w łatwy sposób kopiować, aby nie trzeba było w każdej komórce ponownie wprowadzać nowych formuł. W tym celu należy kliknąć komórkę, z której ma być skopiowana formuła, umie-ścić kursor myszy na małym kwadraciku znajdującym się w prawym dol-nym rogu wskazanej komórki, wcisnąć lewy klawisz myszy i przeciągnąć kursor na komórki, do których formuła ma być skopiowana.

Można również skorzystać z bardziej znanego sposobu kopiowania – poprzez „schowek”. Klikamy komórkę, w której znajduje się wpisana formuła, uży-wamy skrótu klawiaturowego Ctrl+C (trzymając wciśnięty klawisz Ctrl, na-ciskamy klawisz C na klawiaturze), klikamy komórkę, do której formuła ma być skopiowana, i używamy skrótu klawiaturowego Ctrl+V, aby ją wkleić.

Formuły są kopiowane w inteligentny sposób, to znaczy nie jest kopiowana dosłownie taka sama treść, jaka została wskazana w komórce źródłowej.

9167_I_2_3_Kwiecien.indd 14 27.04.2012 15:49



15

I2.3Intendentura


U04

0512

U04

0512

Treść po wklejeniu zostaje zmodyfikowana tak, aby formuła dokonywała obliczenia dla wiersza, do którego została wklejona. Zawdzięczamy to tzw. mechanizmowi adresowania względnego w arkuszu kalkulacyjnym.

Mamy zatem obliczoną liczbę godzin, w których dziecko uczestniczyło, oraz cenę za każdą godzinę. Możemy teraz napisać formułę obliczającą kwotę do zapłaty za odbyte zajęcia.

Umieszczamy kursor w komórce D9 (rys. 31) i wpisujemy formułę: =C9XC4 .

Rys. 31. Formuła obliczająca kwotę do zapłaty

Tym razem nie uda się od razu skopiować formuły obliczeniowej na pozo-stałe komórki tak, aby we wszystkich przeliczenia były poprawne (rys. 32). Mogą pojawić się nieoczekiwane wyniki, a nawet komunikaty o błędach.

Rys. 32. Nieprawidłowe działanie formuły obliczeniowej po kopiowaniu

W przypadku pierwszego kopiowania formuł obliczeniowych pomocne było adresowanie komórek w sposób względny, tym razem takie adreso-wanie okazało się przyczyną nieprawidłowości.

Adresowanie względne polega na zapamiętaniu, gdzie znajduje się formu-ła w arkuszu. Pobieranie danych do formuły nie odbywa się bezpośrednio

9167_I_2_3_Kwiecien.indd 15 27.04.2012 15:49

16


I2.3


Intendentura


U04

0512

U04

0512

za pomocą wskazanego adresu, np. E5, ale według instrukcji, np. dwie komórki wyżej i trzy komórki w prawo. Tak skonstruowane adresowanie względne po skopiowaniu w inne miejsce zachowuje swoją funkcjonalność.

W drugim przypadku, gdy obliczamy należność za odbyte zajęcia, korzy-stamy z wartości komórki C4, w której została wpisana cena godziny zajęć, i mnożymy ją przez liczbę godzin, w których dziecko uczestniczyło. Liczba ta znajduje się w komórce po lewej stronie od komórki, w której obliczamy należną kwotę. Gdy taką formułę skopiujemy o jeden wiersz niżej, popraw-nie zachowa się odwołanie do komórki na lewo od tej, w której jest formuła. Jednak niepoprawnie zostaną pobrane dane z komórki C5.

Aby formuła zawsze, bez względu na swoje położenie, odwoływała się do wskazanej komórki, należy zastosować tzw. adres bezwzględny komórki. Pełny adres bezwzględny uzyskuje się poprzez znak dolara ($) umieszczo-ny w adresie względnym przed literowym oznaczeniem kolumny i cyfro-wym oznaczeniem wiersza, np. $A$15 (rys. 33).

Rys. 33. Zastosowanie adresu bezwzględnego

Dopiero tak przygotowaną formułę, wykorzystującą adresowanie bez-względne, można skopiować do komórek znajdujących się w kolejnych (niżej) wierszach.

7. Reguły naliczania opłat

W naszym projekcie założyliśmy istnienie różnych reguł naliczania opłat za zajęcia, w których dziecko nie uczestniczyło. Mogą być zajęcia, za które zo-staje pobrana opłata tylko, jeśli dziecko w nich uczestniczy. Mogą też być zajęcia organizowane na zasadzie odpłatności za cały okres (np. miesiąc), bez względu na to, czy dziecko w nich uczestniczy, czy nie. W takim wy-padku nie ma zwrotów za niewykorzystane zajęcia. Może też być przyjęta zasada zwrotu za niewykorzystane zajęcia pod warunkiem, że dziecko nie uczestniczyło w liczbie zajęć przekraczającej określony poziom procentowy.

9167_I_2_3_Kwiecien.indd 16 27.04.2012 15:49



17

I2.3Intendentura


U04

0512

U04

0512

Przyjmijmy dla przykładu zasadę, że jeśli dziecko opuściło więcej niż 50% zajęć w ciągu miesiąca, otrzymuje zwrot za niewykorzystane zajęcia, jeże-li opuściło mniej niż 50% zajęć, płaci pełną sumę za cały okres rozlicze-niowy (np. za miesiąc).

Aby przygotować arkusz potrzebny do tego typu obliczenia, musimy po-służyć się instrukcją warunkową. Za jej pomocą badamy, czy został speł-niony jakiś warunek (np. frekwencja mniejsza niż 50%) oraz mówimy programowi, jak ma się zachować, gdy warunek jest spełniony, i jak ma się zachować, gdy warunek nie jest spełniony.

Ogólna składnia instrukcji warunkowej wygląda następująco:= jeżeli (warunek; co ma się wydarzyć w sytuacji spełnienia warunku; co ma się wydarzyć w sytuacji niespełnienia warunku).

Napiszmy więc instrukcję dla naszej aplikacji: jeżeli(dziecko opuściło przynajmniej 50% zajęć; policz dziecku kwotę wynikającą z pomnoże-nia liczby zajęć, w których dziecko uczestniczyło, razy stawkę za godzinę; w przeciwnym wypadku policz dziecku stawkę za cały okres rozliczeniowy). Czyli: =JEŻELI($C$3–C9>=$C$5X$C$3;C9X$C$4;$C$3X$C$4)

Rys. 34. Instrukcja warunkowa do obliczeń należności

Wpisujemy instrukcję (rys. 34) do komórki D9. Zwracamy uwagę na uży-cie adresów bezwzględnych, co umożliwi nam kopiowanie tej instrukcji do komórek D10, D11 i kolejnych w dół – dla pozostałych dzieci.

Tego typu rozwiązanie pozwala nam określać w jednym miejscu wartości liczby zajęć w okresie rozliczeniowym, cenę za godzinę oraz procentowy próg absencji, po przekroczeniu którego następuje zwrot za niewykorzy-stane zajęcia. Wszystkie obliczenia dokonują się automatycznie po wpro-wadzeniu nowych wartości przeliczeniowych.

Chcielibyśmy jeszcze zsumować wszystkie ewentualne zwroty za cały okres rozliczeniowy. Policzymy więc, ile dzieci uczestniczy w zajęciach, pomnożymy tę liczbę przez liczbę zajęć i cenę za godzinę, następnie odej-miemy wyliczone kwoty do zapłaty (rys. 35).

9167_I_2_3_Kwiecien.indd 17 27.04.2012 15:49

18


I2.3


Intendentura


U04

0512

U04

0512

Rys. 35. Obliczanie wartości zwrotów

W przedstawionej formule wykorzystano funkcję „max”, która zwraca najwyższą wartość z kolumny „liczba porządkowa”, dzięki czemu dowia-dujemy się, ile dzieci uczestniczy w zajęciach.

W powyższych przykładach założono, że grupa liczy najwyżej siedmioro dzieci. Gdyby tabela miała być dłuższa, wszystkie wpisy w formułach ob-liczeniowych, które wskazują na zakres obliczanych komórek, od wiersza 9 do wiersza 15 należy zmodyfikować, czyli w miejsce liczby 15 należy wprowadzić odpowiednio większą liczbę odpowiadającą długości tabeli.

8. Zabezpieczanie przed przypadkową edycją

Po przygotowaniu arkusza obliczeniowego warto zabezpieczyć przed przy-padkową edycją te pola, które nie podlegają powtórnej edycji. Przed za-bezpieczeniem arkusza należy zaznaczyć te komórki, które mają pozo-stać edytowalne po zablokowaniu arkusza. Z pewnością będą to komórki, w których wpisywana jest frekwencja na zajęciach. Po zaznaczeniu ich należy kliknąć w obszar zaznaczony prawym klawiszem myszy, z rozwi-niętego menu wybieramy „Formatuj komórki” (rys. 36).

Rys. 36. Wybór opcji formatowania komórek

9167_I_2_3_Kwiecien.indd 18 27.04.2012 15:49



19

I2.3Intendentura


U04

0512

U04

0512

W wyświetlonym oknie „Formatowanie komórek” wybieramy zakładkę „Ochrona”, następnie odznaczamy opcję „Zablokuj” (rys. 37) i potwier-dzamy wybór przyciskiem „OK”. W ten sposób wskazane komórki nie będą blokowane, dzięki czemu będzie możliwa dalsza edycja ich wartości.

Rys. 37. Brak blokowania wskazanych komórek

Z menu górnego wybieramy zakładkę „Recenzja” i klikamy ikonę „Chroń arkusz” (rys. 38).

Rys. 38. Aktywacja ochrony arkusza

W wyświetlonym oknie dialogowym istnieje możliwość wprowadzenia ha-sła do arkusza. Na użytek naszego programu nie musimy jednak niczego zmieniać, wystarczy zaakceptować standardowe ustawienia przez klik-nięcie przycisku „OK”.

Od tej pory arkusz będzie chroniony, a przy próbie wprowadzenia zmian w komórkach blokowanych użytkownik będzie informowany odpowied-nim komunikatem (rys. 39).

Rys. 39. Komunikat o blokadzie komórki

Jeżeli zajdzie potrzeba ponownej edycji blokowanych komórek arkusza, należy w zakładce „Recenzja” kliknąć ikonę „Nie chroń arkusza”.

9167_I_2_3_Kwiecien.indd 19 27.04.2012 15:49

20


I2.3


Intendentura


U04

0512

9. Ochrona danych osobowych

Przygotowując tego typu aplikację, należy pamiętać, że przechowujemy w niej dane osobowe. W rozumieniu prawa za dane osobowe uważa się takie dane, za pomocą których w łatwy sposób można zidentyfikować właściciela tych danych. Jeżeli tworzymy bazę danych dzieci w przed-szkolu i posługujemy się tylko imieniem i nazwiskiem, dane te zdecydo-wanie są danymi osobowymi. Dane osobowe podlegają ochronie prawnej, bez względu na to, czy są to dane osoby pełnoletniej, czy niepełnoletniej.

Ustawa o ochronie danych osobowych (Ustawa z 29 sierpnia 1997 r. o ochronie danych osobowych, tekst jedn.: DzU 2002 nr 101, poz. 926 ze zm.) oraz zarządzenie wykonawcze do ustawy (Rozporządzenia Ministra Spraw Wewnętrznych i Administracji z 29 kwietnia 2004 r. w sprawie dokumentacji przetwarzania danych osobowych oraz warunków technicz-nych i organizacyjnych, jakim powinny odpowiadać urządzenia i systemy informatyczne służące do przetwarzania danych osobowych, DzU 2004 nr 100, poz. 1024) szczegółowo określa, jak dane powinny być zabez-pieczone, szczególnie jeśli do ich przetwarzania jest wykorzystany sys-tem informatyczny, oraz jakie wymogi powinna spełniać instytucja, która dane osobowe przetwarza.

Generalną zasadą jest, aby do danych osobowych miały dostęp tylko oso-by uprawnione. Tak więc jeżeli zdecydujemy się prowadzić jakiekolwiek ewidencje elektroniczne zawierające dane osobowe, należy zabezpieczyć komputer hasłem dostępu, a także nośniki danych, na których bazy są przechowywane. Jeżeli nie ma takiej potrzeby, lepiej, żeby komputer, na którym przechowywane są dane osobowe, nie był podłączony do interne-tu, w przeciwnym wypadku na podstawie prawa obowiązkiem instytucji jest wdrożenie najbardziej restrykcyjnych zasad ochrony zasobów.

Gromadząc dane w formie elektronicznej, należy również pamiętać o two-rzeniu kopii zapasowych na nośnikach zewnętrznych (płyty CD, pendrive itp.). Nie ma takiego urządzenia, które nie mogłoby się zepsuć, podob-nie jest z komputerem i dyskiem w komputerze. Odzyskanie utraconych danych może być niemożliwe lub bardzo kosztowne. Należy też zwrócić uwagę na odpowiednie zabezpieczenie kopii zapasowych, zwłaszcza gdy zawierają dane osobowe. Procedury tworzenia kopii zapasowych, ich przechowywania i niszczenia zawarte są w zarządzeniu wykonawczym do ustawy o ochronie danych osobowych.

9167_I_2_3_Kwiecien.indd 20 27.04.2012 15:49

Arkusz do naliczania opłat za udział w zajęciach dodatkowych ... · Jako bazowy program...

Documents

Transcript of Arkusz do naliczania opłat za udział w zajęciach dodatkowych ... · Jako bazowy program...