INFORMATYKA EKONOMICZNA — laboratorium
arkusz kalkulacyjny: MS Excel
mgr inż. Adam Czarnecki, mgr inż. Artur Ziółkowski, 2010
Wydział Zarządzania i Ekonomii Politechniki Gdańskiej
1/9
ST.IiE
Laboratorium 5
1 Formanty formularza — wprowadzenie
Formanty
są obiektami graficznymi umieszczanymi w formularzu,
służącymi do wyświetlania lub wprowadzania danych, wykonywania
akcji lub zwiększania czytelności formularza. Przykładami tych obiektów
są pola tekstowe, pola list, przyciski opcji, przyciski poleceń itp.
Formanty zapewniają użytkownikom opcje, które można zaznaczać, lub
przyciski, które można klikać w celu uruchomienia makr lub skryptów sieci Web. Program Microsoft
Excel ma dwa typy formantów: formularza i ActiveX.
Formanty formularza
są prostsze w użyciu, gdyż nie wymagają pisania kodu w języku Visual Basic
for Applications (VBA). I z nich będziemy korzystać na zajęciach. Formanty ActiveX wymagają
umiejętności tworzenia makr w języku VBA, ale dzięki temu oferują znacznie więcej możliwości.
Zazwyczaj, aby skorzystać z efektów działania konkretnego formantu, należy na nim kliknąć prawym
przyciskiem myszy, wybrać z menu kontekstowego pozycję Formatuj formant, a następnie w zakładce
Formant
ustawić właściwe opcje. Jedną z nich jest Łącze komórki — tu należy wskazać, do której
komórki w arkuszu ma trafiać informacja będąca efektem działania formantu.
Aby w Excelu 2007 uzyskać dostęp do formantów, należy włączyć pokazywanie karty Deweloper:
1. Kliknij przycisk Microsoft Office
, a następnie kliknij polecenie Opcje programu Excel.
2.
Kliknij opcję Popularne, a następnie zaznacz pole wyboru Pokaż kartę Deweloper na Wstążce.
Rysunek 1 Formanty na karcie Deweloper.
2 Fabryka samochodów i motocykli
Wykonamy w Arkusz1
prosty formularz służący ustalaniu planu produkcji dla europejskiej firmy
motoryzacyjnej. Wykorzystamy forman
ty formularza. Pozwolą one podać, co będzie produkowane, jak
zorganizowana ma być produkcja, w jakim kolorze będzie wyrób oraz ile sztuk ma zostać wytworzone.
W tym zestawie zadań korzystamy wyłącznie z formantów formularza, a nie ActiveX.
2.1 Wstawianie formantów do arkusza
2.1.1 Przyciski opcji
Zestaw przycisków opcji pozwala na wybranie dokładnie jednej opcji poprzez zaznaczenie przy niej
kropki. Aby móc wykorzystać więcej niż jeden niezależnie działających od siebie zestawów przycisków
opcji, musimy każdy zestaw obramować formantem pola grupy. W innym przypadku wszystkie
przyciski opcji działają jako jeden system z jednym łączem komórki.
Na początek narysujmy dwa przyciski opcji. Jeden niech mieści się mniej więcej w granicach
komórek B3:C3 (opiszmy go Samochody), a drugi — B5:C5 (Motocykle
). Za ich pomocą będziemy
określali, co zakład ma produkować. Otoczmy przyciski opcji polem grupy opisanym słowem Produkt.
Legenda:
aplikacja, program
opcja menu, funkcja
plik, folder, ścieżka
formuła, pole, kod pola
KLAWISZ
tekst do przepisania
miejsce częstych błędów
7
INFORMATYKA EKONOMICZNA — laboratorium
arkusz kalkulacyjny: MS Excel
mgr inż. Adam Czarnecki, mgr inż. Artur Ziółkowski, 2010
Wydział Zarządzania i Ekonomii Politechniki Gdańskiej
2/9
ST.IiE
W
przypadku większej liczby przycisków opcji pole grupy pozwala podzielić je na mniejsze, niezależne
od siebie systemy.
Ważne wtedy jest, by obramowania przycisków opcji (które widać, gdy przycisk jest
w trybie edycji, jak pokazuje to Rysunek 2)
nie wystawały poza krawędzie pola grupy. Przy kolejnych
opisywanych formantach możesz zastosować pola grupy, jak pokazano to na rysunkach.
Rysunek 2 Mechanizm przycisków opcji.
Spróbuj klikać na przemian na przyciski opcji. Teraz informację o tym, który przycisk jest aktywny,
trzeba przekazać do jakiejś komórki arkusza. Kliknij na którymkolwiek z dwóch przycisków opcji
prawym przyciskiem myszy i z menu kontekstowego wybierz Formatuj formant
. Ostatnia zakładka
(Formant
) pozwala wskazać tzw. łącze komórki. Wskaż, by była to komórka E5. Zamknij okno
formatowania formantu. Zobacz, co t
eraz będzie się działo w komórce E5, gdy przełączasz przyciski
opcji.
W komórce E5 pojawia się 1, gdy wybrane są Samochody lub 2, gdy wybrane są Motocykle.
Będziemy teraz chcieli, aby zależnie od wybranej opcji w komórce F5 pojawiał się napis „samochody”
lub „motocykle”.
Wykorzystamy do tego funkcję WYSZUKAJ.PIONOWO. Najpierw przygotujmy
niewielką tabelę pokazującą, jakiej wartości przycisków opcji odpowiada jaki produkt (komórki E2:F3
— zobacz Rysunek 2
). Następnie do komórki F5 wprowadźmy funkcję WYSZUKAJ.PIONOWO, która
będzie wartości z komórki, która jest połączona z przyciskami opcji (E5) szukać w pierwszej od lewej
kolumnie tabeli E2:F3 i zwróci nam z drugiej kolumny tej tabeli rodzaj produktu. Dla większej
dokładności można podać czwarty, opcjonalny argument — FAŁSZ. Gotową funkcję widać na pasku
formuły na rys. 2.
Mamy gotowy moduł wyboru głównego produktu. W kolejnym miejscu określimy dodatkowe parametry
związane z organizacją produkcji. Wykorzystamy do tego inny typ formantu formularza — pole
wyboru.
Opisano to w poniższym punkcie.
2.1.2 Pole wyboru
Pola wyboru
działają zawsze niezależnie od siebie. Każde może być włączone (wtedy do połączonej
komórki trafia wartość PRAWDA) lub wyłączone (FAŁSZ).
Poniżej przycisków opcji stwórz pola wyboru, jak pokazano na rys. 3. Łącze komórki do pola
opisanego jako Praca zmianowa
znajduje się w E9, a do pola Kontrola statystyczna w E11 (czytaj:
ustaw łącza z formantów do tych komórek).
Rysunek 3 Mechanizm pola wyboru.
INFORMATYKA EKONOMICZNA — laboratorium
arkusz kalkulacyjny: MS Excel
mgr inż. Adam Czarnecki, mgr inż. Artur Ziółkowski, 2010
Wydział Zarządzania i Ekonomii Politechniki Gdańskiej
3/9
ST.IiE
Za pomocą funkcji JEŻELI ustaw, by w komórkach F9 i F11 wyświetlał się wyraz „tak”, gdy dana opcja
jest zaznaczona, lub „nie”
, gdy jest wyłączona. Zwróć uwagę, że nie musimy sprawdzać żadnego
złożonego warunku logicznego, skoro komórki E9 i E11 od razu przyjmują wartości PRAWDA lub
FAŁSZ.
Teraz, poniżej mechanizmu pól wyboru, zajmijmy się możliwością wyboru koloru lakieru dla
produkowanych samochodów lub motocykli. Do tego celu przyda nam się pole kombi opisane
w
kolejnym punkcie ćwiczeń.
2.1.3 Pole kombi
Po
le kombi to rodzaj rozwijanej listy, z której można wybrać jedną opcję. Źródłem dla listy jest zwarty
zakres ułożonych w jednej kolumnie komórek. Do łącza komórki zwracana jest liczba porządkowa
wybranego elementu listy. Funkcjonalnie pole kombi jest bardzo podobne do przycisków opcji.
Przygotuj mechanizm pola kombi tak, jak pokazano na rys. 4
. Dla tego pola kombi zakres wejściowy to
F14:F18 (nazwy kolorów)
, łącze komórki znajduje się w E20, a linie rzutu niech wynoszą 5, bo tyle
mamy kolorów do wyboru (sam
o się to nie zrobi — ustaw odpowiednie opcje formantu). Do F20 wpisz
funkcję WYSZUKAJ.PIONOWO, która — podobnie jak przy przyciskach opcji — będzie zwracać
nazwę wybranego koloru w oparciu o komórkę połączoną z polem kombi.
Rysunek 4 Mechanizm pola kombi.
Ostatnim parametrem będzie określenie liczby sztuk wyrobu, jaką europejska fabryka ma
wyprodukować. Do tego celu przyda się pasek przewijania.
2.1.4 Pasek przewijania
Poniżej mechanizmu pola kombi narysuj poziomy pasek przewijania (rys. 5). Ustaw łącze komórki do
tego formantu w C26. Pozostałe ustawienia paska mogą zostać bez zmian. Sprawdź, czy pasek
działa.
Rysunek 5 Mechanizm paska przewijania.
INFORMATYKA EKONOMICZNA — laboratorium
arkusz kalkulacyjny: MS Excel
mgr inż. Adam Czarnecki, mgr inż. Artur Ziółkowski, 2010
Wydział Zarządzania i Ekonomii Politechniki Gdańskiej
4/9
ST.IiE
Użyliśmy czterech typów formantów do określenia szczegółów planu dla działu produkcji. Teraz
zbierzmy informacje o tych decyzjach w
jednym miejscu, poniżej mechanizmu paska przewijania.
Na rys. 6.
pokazano, jak należy zebrać przygotowane w poprzednich krokach dane w, informację
zbiorczą na temat zlecenia produkcyjnego.
Na tym etapie można by jeszcze zastanowić się nad ergonomią i estetyką wykonania formularza, np.
pozbycia się widocznych krawędzi komórek przy formantach, zmieniając kolor wypełnienia tych
komórek na biały. Autorzy ćwiczenia pozostawiają tę kwestię do samodzielnego rozważenia.
W kolejnym punkcie zajmiemy się ochroną arkusza przed zmianami.
Rysunek 6 Formularz zlecenia produkcyjnego
z pokazaniem zależności między komórkami.
INFORMATYKA EKONOMICZNA — laboratorium
arkusz kalkulacyjny: MS Excel
mgr inż. Adam Czarnecki, mgr inż. Artur Ziółkowski, 2010
Wydział Zarządzania i Ekonomii Politechniki Gdańskiej
5/9
ST.IiE
2.1.5 Ochrona arkusza
Projektując arkusz, w którym znajdują się formanty, warto zadbać, by docelowi użytkownicy nie mogli
przypadkowo (lub celowo)
zmienić jego układu. W tym celu wykorzystamy poznane na wcześniejszych
zajęciach narzędzie ochrony arkusza:
1.
Włącz ochronę arkusza Arkusz1 (Recenzja
Zmiany
Chr
oń arkusz). Możesz pozostawić
domyślne opcje i nie podawać hasła.
2.
Spróbuj przełączyć opcje w każdym z formantów. Zobacz, jaki jest efekt. Dlaczego?
3.
Wyłącz ochronę arkusza.
4.
Zmień ustawienia ochrony 5 komórek, które powodują, że formanty w trybie ochrony arkusza
przestają działać. Które to komórki?
5.
Włącz ponownie ochronę arkusza i sprawdź, czy teraz każdy z formantów działa, a jednocześnie
arkusz jest ochroniony przed niepożądanymi zmianami.
2.2 Formanty w oknie dialogowym
W tym ćwiczeniu spróbujemy uzyskać ten sam efekt, jak w zadaniu z punktu 2.1. Tym razem za
pomocą okna dialogowego. Wstawmy do Excela arkusz dialogu — kliknij prawym przyciskiem myszy
na zakładkach arkuszy u dołu ekranu. Wybierz z menu kontekstowego Wstaw, a z okna, które
zostanie otwarte, MS Excel 5.0 — dialog. Excel doda nowy arkusz o nazwie Dialog1. W nim znajdzie
się szablon okna dialogowego (rys. 7).
Rysunek 7 Szablon okna dialogowego.
Umieść w obrębie okna takie same typy formantów, jak poprzednio w arkuszu i ustaw im łącza do tych
samych komórek w arkuszu Arkusz1
, do których prowadzą łącza identycznych formantów, które
zostały przez Ciebie wstawione wcześniej
(Uwaga! Może być problem z paskiem przewijania, gdy
trzeba kliknięciem wybrać jako łącze komórkę C26 w Arkusz1. Najlepiej wtedy kliknąć w inną komórkę
w tym arkuszu, a adres poprawić ręcznie. Powodem problemu jest pole grupy obramowujące m.in.
komórkę C26)
. Przykładowy układ formantów w oknie pokazano na rys. 8.
Rysunek 8 Gotowe uruchomione okno dialogowe zlecenia produkcyjnego.
INFORMATYKA EKONOMICZNA — laboratorium
arkusz kalkulacyjny: MS Excel
mgr inż. Adam Czarnecki, mgr inż. Artur Ziółkowski, 2010
Wydział Zarządzania i Ekonomii Politechniki Gdańskiej
6/9
ST.IiE
Aby ułatwić sobie testowanie działania okna, zarejestruj najpierw proste makro (jak tworzyć makra:
http://office.microsoft.com/pl-pl/excel/HP100141111045.aspx#RecordMacro
):
będąc
w arkuszu
Dialog1 uruchom okno dialogowe
(za pomocą opcji o takiej nazwie na karcie Deweloper), a następnie
je zamknij.
Pamiętaj o zatrzymaniu rejestrowania makra!
Potem będzie można to makro uruchamiać w arkuszu Arkusz1, by przywoływać okno, np. przypisując
je do przycisku (jak:
http://office.microsoft.com/pl-pl/excel/HP100141111045.aspx#AssignMacro
).
Przetestuj działanie okna dialogowego mając wyświetlony Arkusz1. Zobacz, co dzieje się
z
formantami osadzonymi bezpośrednio w arkuszu oraz z zależnymi od nich komórkami.
Zapisz wykonany przez siebie skoroszyt i —
jeśli chcesz — zachowaj jako pomoc w wykonaniu pracy
domowej znajdującej się na kolejnych stronach oraz do przygotowania się do sprawdzianu i do
realizacji projektu.
3 Praca domowa — salon samochodowy
Wykonaj system wspomagania obsługi klienta w małym salonie samochodowym. Wykorzystaj
formanty formularza
wstawione bezpośrednio do arkusza, a nie do okna dialogowego.
3.1 Zadania skoroszytu
Zaprojektowany skoroszyt musi realizować następujące funkcje:
•
Obliczanie ceny samochodu w zależności od wybranego modelu i wybranych dodatków oraz
pakietu ubezpieczeniowego.
•
Kalkulowanie wysokości rat kredytowych przy zakupie ratalnym (np. za pomocą funkcji finansowej
PMT).
•
Sporządzanie gotowego do wydruku zamówienia klienta.
Zakładamy, że klient:
• kupuje tylko jeden samochód,
•
do auta może dobrać dowolną kombinację dodatków,
•
może wybrać tylko jeden pakiet ubezpieczenia lub zdecydować o tym, by w ogóle nie wykupywać
polisy,
•
auto może nabyć jedną z trzech dróg: (1) za gotówkę, (2) płacąc przelewem lub (3) na raty.
3.2 Dane
W tabelach 1–
4 przedstawiono informacje, z których powinien korzystać skoroszyt.
Tabela 1 Modele samochodów i ich ceny
Lp.
Model
Cena podstawowa
1
Seicento
23
000 zł
2
Punto
32
000 zł
3
Croma
56
000 zł
Cena podstawowa
Cena dodatków
Ubezpieczenie
Cena samochodu
Gotówka
Przelew
Kredyt
INFORMATYKA EKONOMICZNA — laboratorium
arkusz kalkulacyjny: MS Excel
mgr inż. Adam Czarnecki, mgr inż. Artur Ziółkowski, 2010
Wydział Zarządzania i Ekonomii Politechniki Gdańskiej
7/9
ST.IiE
Tabela 2 Cennik dodatków
Lp.
Rodzaj
Cena
1
Klimatyzacja
4
000 zł
2
Aluminiowe felgi
1
500 zł
3
Radio+6 głośników
2
300 zł
4
Skórzana tapicerka
3
000 zł
Tabela 3 Cennik pakietów ubezpieczeniowych
Lp.
Rodzaj pakietu
Cena
1
Ubezpieczenie samochodu na rok
10% ceny podstawowej
2
Ubezpieczenie auta + ubezpieczenie mieszkania
300 zł + 10% ceny podstawowej
3
Ubezpieczenie dla stałych klientów
1
150 zł
Tabela 4 Dane do kalkulatora rat kredytowych
Stopa procentowa 10% w skali roku
Liczba rat
Zależna od klienta
Wpłata własna Zależna od klienta
Podstawa kredytu = Cena samochodu -
Wpłata własna
Elementy dodatkowe:
•
łatwe przejście z arkusza głównego do arkusza z potwierdzeniem zamówienia (np. przycisk).
Dane o kliencie do wprowadzenia w arkuszu głównym:
• W osobnych komórkach:
•
Imię,
• Nazwisko,
• Ulica,
• Nr domu,
• Kod Pocztowy,
•
Miejscowość.
Decyzja klienta
dotycząca formy płatności (gotówka, przelew lub raty) powinna być uwzględniona
w potwierdzeniu zamówienia.
3.3 Wskazówki
Podobno łatwiej dawać dobre rady niż je przyjmować. Mimo to:
•
Aby w Excelu 2007 uzyskać dostęp do formantów formularza należy włączyć kartę Deweloper.
Jak?
http://office.microsoft.com/pl-pl/excel/HA101730521045.aspx
•
Zauważ, że wiele wymagań w tym zadaniu bazuje na sprawdzaniu warunków logicznych
przydatna
będzie
funkcja
JEŻELI.
Czasami
wygodniej
będzie
użyć
funkcji
WYSZUKAJ.PIONOWO.
•
Do wyboru modelu samochodu czy innych opcji można wykorzystać pola dostępne z narzędzi
Formantów formularzy
. Niektóre przydają się bardziej w sytuacjach, gdzie należy wskazać jedną
tylko pozycję. Inne — gdzie potrzeba wskazać dwie lub więcej możliwości.
INFORMATYKA EKONOMICZNA — laboratorium
arkusz kalkulacyjny: MS Excel
mgr inż. Adam Czarnecki, mgr inż. Artur Ziółkowski, 2010
Wydział Zarządzania i Ekonomii Politechniki Gdańskiej
8/9
ST.IiE
•
Postaraj się tak zaprojektować skoroszyt, by cały „mechanizm”, dzięki któremu działa wybór
samochodu, był ukryty przed użytkownikiem (w innym arkuszu? w ukrytych kolumnach lub
wierszach?). Pamiętaj, że aplikacja ma być przyjazna w korzystaniu.
• W arkuszu
potwierdzenia zamówienia użyj odwołań do arkusza, gdzie dane o zamówieniu są
zbierane dzięki formantom. Być może też w jednej komórce trzeba będzie połączyć dane z wielu
komórek (np. połączyć imię z nazwiskiem) czy zastosować określone formatowanie (ceny powinny
mieć format walutowy).
•
Za przyciskiem pozwalającym przełączać się do innego arkusza kryje się proste zarejestrowane
makro. Jaką czynność zarejestrowano?
3.4 Wzór potwierdzenia zamówienia
klient:
adres:
kod:
Fiat Croma
56 000,00 zł
6 300,00 zł
2 800,00 zł
65 100,00 zł
forma płatności
kredyt
45 100,00 zł
60
950,32 zł
cena za ubezpieczenie
razem do zapłaty
Podpis klienta
……………………………
rata kredytu
okres spłaty (miesiące)
wielkość kredytowana
marka:
cena auta
cena za dodatki
zakup
Jan Kowalski
ul. Zielona 12
12-345 Małdyty
Realizacja zamówienia - zestawienie
4
Pytania sprawdzające wiedzę i umiejętności
1.
Jak działają następujące formanty: przyciski opcji, pole grupy, pole wyboru, pole kombi, pasek
przewijania, pokrętło i przycisk?
2.
Przyciski opcji i pole kombi pełnią tę samą funkcję: ze zbioru możliwości pozwalają wybrać jedną.
Kiedy lepiej jest zastosować przyciski opcji, a kiedy pole kombi?
3.
Jak działa funkcja WYSZUKAJ.PIONOWO? Do czego można jej użyć?
4.
Jakie są zalety wprowadzania danych za pomocą formantów?
5.
Jak można korzystać z formantów, by cały „mechanizm” ich działania nie był widoczny dla osoby
korzystającej ze skoroszytu?
6. J
ak można sprawić, by w gotowym formularzu użytkownik mógł tylko korzystać z formantów, ale
nie mógł ich przesuwać, zmieniać rozmiaru czy edytować właściwości?
7.
Jak wstawiać do komórek komentarze (na rys. 6 ich treść to „łącze komórki”)?
8.
Jak wyświetlić takie niebieskie strzałki, jak na rys. 6 i czemu one służą?
9.
Jak rejestruje się makro?
Document Outline
