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?

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

9/9

ST.IiE

5 Literatura

• Formularze:

http://office.microsoft.com/pl-pl/excel/CH062528021045.aspx

• Typy formantów:

http://office.microsoft.com/pl-pl/excel/HP052036041045.aspx

• Dodawanie formantów:

http://office.microsoft.com/pl-pl/excel/HP051984701045.aspx

• Makra:

http://office.microsoft.com/pl-pl/excel/CH101001571045.aspx

• Makro formantu:

http://office.microsoft.com/pl-pl/excel/HP051986451045.aspx

•

Funkcja JEŻELI:

http://office.microsoft.com/pl-pl/excel/HP100698291045.aspx

• Funkcja WYSZUKAJ.PIONOWO:

http://office.microsoft.com/pl-pl/excel/HP100698351045.aspx

•

Inspekcja formuł:

http://office.microsoft.com/pl-pl/excel/HP100662531045.aspx