3. ĆWICZENIA Z WYKORZYSTANIEM PROGRAMU EXCEL
Ćwiczenie 3.19
Cel:
Poznanie zarządzania formularzem listy danych w Excelu
Uwaga:
W miarę upływu czasu trzeba aktualizować datę sprzedaży i datę płatności. W
przeciwnym przypadku przykład w znacznej mierze traci sens.
Polecenie:
Poniższa tabela zawiera przykład źródłowy, w którym jeden rekord prezentuje wybrane atrybuty faktury wystawionej dla naszego klienta, który otrzymał przedłużony termin płatności.
Nazwa
Numer
Numer
Kwota
Data
Data
klienta
klienta
faktury
wystawienia
zapłaty
ABC
111
22345
12 345,45 zł
04-01-20 04-02-20
BCD
112
22346
2 345,00 zł
04-02-21 04-04-10
ABC
111
22347
567,12 zł
04-02-25 04-03-05
BCD
112
22348
3 498,90 zł
04-02-25 04-03-20
EFG
115
22349
2 312,00 zł
04-03-20 04-04-30
FGH
116
22350
239,76 zł
04-03-25 04-05-25
ABC
111
22351
36 912,99 zł
04-04-12 04-05-12
HIJ
118
22352
22 445,76 zł
04-04-16 04-05-31
IJK
119
22353
968,45 zł
02-05-23 04-06-23
BCD
112
22354
1 567,85 zł
04-05-24 04-06-13
KLM
121
22355
2 367,00 zł
04-05-24 04-06-24
LMN
122
22356
1 776,35 zł
04-05-25 04-06-25
BCD
112
22357
17 894,00 zł
04-05-26 04-06-26
ABC
111
22358
999,00 zł
04-05-26 04-06-30
BCD
112
22359
1963,67 zł
04-05-31 04-07-12
ABC
111
22360
116,75 zł
04-06-01 04-12-20
1. Założyć w nowym skoroszycie w arkuszu 1 ten przykład źródłowy od komórki A4. Jest to przykład, który stanowi podstawę wykonania kolejnych ćwiczeń.
2. Skopiować przykład źródłowy do arkusza 2
3. Wstawić nową kolumnę A i dokonać automatycznej numeracji rekordów (krotek) 4. Do komórki H4 wprowadzić etykietę Dni opóźnienia
5. Do komórki H5 wprowadzić formułę pozwalającą obliczyć dni opóźnienia 6. Dodać nowy rekord z Lp. 17 odpowiednio z następującymi danymi: ABC; 111; 22360; 116,75 zł;04-06-02;04-08-20 wprowadzając dane do listy bezpośrednio na arkuszu 7. Dodać nowy rekord z Lp. 18 odpowiednio z następującymi danymi: XYZ; 130; 22361;100999 zł04-07-30 oraz 04-11-30, wprowadzając dane do listy za pomocą formularza
8. Zmodyfikować, za pomocą formularza, w rekordzie 18 pole Kwota na 100888 zł
9. Za pomocą formularza wyszukać rekordy dla klienta o numerze 112
10. Arkusz 2 nazwać Formularz
11. Zapisać w pliku Bazy danych 1
Cel:
Poznanie różnych możliwości sortowania listy
Polecenie A:
Na podstawie przykładu źródłowego należy posortować listę według różnych kryteriów
1. Skopiować przykład z arkusza Formularz w pliku Bazy danych 1 do arkusza 3 od komórki np. A4
2. Arkusz 3 nazwać Sortowanie
3. Przykład zawarty w arkuszu Sortowanie posortować rosnąco (korzystając z ikony na pasku standardowym) według kwoty
4. Przywrócić liście poprzedni porządek
5. Korzystając z menu posortować listę według następujących kryteriów: Kwota malejąco, natomiast Nazwa klienta rosnąco
6. Przywrócić liście pierwotny porządek
Polecenie B:
Na podstawie przykładu dotyczącego projektów różnych systemów informatycznych należy posortować listę według nazwisk autorów.
1. W pliku Bazy danych 1 w arkuszu Sortowanie założyć tabelę od komórki np. K4
zawierającą następujące dane:
Data
Autor - Imię
Lp.
Tytuł projektu
oddania
i nazwisko
1 Płace
04-05-15 Anna Kabacka
2 Kadry
04-05-15 Beata Nowak
3 TPP
04-12-16 Adam Król
4 Gosp. Materiałowa
04-05-15 Agata Abt
5 Środki trwałe
04-12-17 Janusz Hak
6 Sprzedaż
04-12-17 Joanna Mita
7 Zaopatrzenie
04-12-17 Krzysztof Zet
8 Obsługa hurtowni
05-01-18 Piotr Wilk
9 Produkcja
05-01-07 Halina Habacka
10 F – K
05-06-09 Jan Opacki
11 Salon samochodowy
04-10-10 Grażyna Żak
2. W komórce O4 wprowadzić etykietę - Pole sortujące, po to, by w zakresie O5:O16 móc umieścić tylko nazwiska. Przyjmując oczywiście założenie, że nasza lista w praktyce jest bardzo długa, trzeba to wykonać za pomocą odpowiedniej formuły.
3. W komórce O5 wprowadzić odpowiednią formułę pozwalającą wydzielić tylko nazwisko autora, po to, by następnie móc posortować listę według nazwisk
4. Posortować listę według nazwisk
5. Przywrócić liście poprzedni porządek
6. Posortować listę według daty oddania i tytułu projektu
7. Przywrócić liście poprzedni porządek
8. Zapisać w pliku Bazy danych 1
Ćwiczenie 3.21
Cel:
Nabycie umiejętności filtrowania listy w Excelu z poziomu autofiltru Polecenie:
Dla przykładu źródłowego zawartego w pliku Bazy danych 1 , arkusz – Przykład źródłowy należy przeprowadzić odpowiednie filtrowanie za pomocą autofiltru 1. Wstawić nowy arkusz w pliku Bazy danych 1 i nazwać go Autofiltr
2. Do arkusza Autofiltr skopiować przykład źródłowy od komórki A3
3. Zastosować autofiltr dla pola - Data zapłaty
4. Odfiltrować rekordy dla klienta ABC
5. Zostawić autofiltr, ale powrócić do pierwotnej wersji listy
6. Usunąć autofiltr
7. Zastosować autofiltr dla wszystkich pól
8. Wykonać autofiltr dla pola Numer klienta 111
9. Usunąć autofiltr
10. Wykonać autofiltr dla pola Nazwa klienta ABC lub BCD (korzystając z opcji Inne...) 11. Zapisać efekt ćwiczenia w pliku Bazy danych 1
Ćwiczenie 3.22
Cel:
Nabycie umiejętności filtrowania listy
Polecenie:
Nowy arkusz należy nazwać Uwagi i wyświetlić w nim, korzystając z opcji Filtr zaawansowany, tylko te rekordy, które w polu Uwagi mają literę d.
1. Nowy arkusz nazwać Uwagi i skopiować do niego Przykład źródłowy od komórki A6
2. W komórce I6 umieścić etykietę Uwagi i odpowiednio od I7 w polu Uwagi wpisać – d, o, o, p, p, d, d, d, spacja, spacja, d, o, spacja, p, o, d
3. Odfiltrować wszystkie rekordy, które w polu Uwagi zawierają literę d korzystając z autofiltru
4. Usunąć autofiltr
5. Odfiltrować wszystkie rekordy, które w polu Uwagi zawierają literę d wykorzystując opcję Filtr zaawansowany
6. Zapisać w pliku Bazy danych 1
Ćwiczenie 3.23
Cel:
Nabycie umiejętności używania kompleksowych kryteriów filtrowania listy Polecenie:
Należy odfiltrować wszystkie rekordy dla klienta ABC i kwoty co najmniej 1000,00 zł, korzystając z filtru zaawansowanego.
1. Nowy arkusz nazwać Filtr zaawansowany 1 i skopiować do niego przykład źródłowy od komórki A6 (z pliku Bazy danych 1 i arkusza Przykład źródłowy)
2. Odfiltrować wszystkie rekordy dla klienta ABC i kwoty co najmniej 1000,00 zł.
3. Zapisać w pliku Bazy danych 1
Ćwiczenie 3.24
Cel:
Nabycie umiejętności używania kompleksowych kryteriów filtrowania listy Polecenie:
Należy odfiltrować wszystkie rekordy dla numeru klienta 112 lub dni opóźnienia co najwyżej 30
1. Nowy arkusz nazwać Filtr zaawansowany.2 i skopiować do niego przykład źródłowy od komórki A7 (z pliku Bazy danych 1 i arkusza Przykład źródłowy)
2. Odfiltrować wszystkie rekordy dla klienta o numerze 112 lub dni opóźnienia co najwyżej 30
3. Zapisać w pliku Bazy danych 1
Ćwiczenie 3.25
Cel:
Nabycie umiejętności używania kompleksowych kryteriów filtrowania listy Polecenie:
Należy odfiltrować wszystkie rekordy, dla tych klientów, których nazwa zaczyna się na literę A i zawiera literę E
1. Nowy arkusz nazwać Filtr zaawansowany.3 i skopiować do niego przykład źródłowy od komórki A7 (z pliku Bazy danych 1 i arkusza Przykład źródłowy)
2. W rekordzie 3 zmienić nazwę na ADEPT, a w rekordzie 14 na ASTER
3. Odfiltrować wszystkie rekordy tych klientów, których nazwa zaczyna się na literę A i zawiera literę E
4. Zapisać w pliku bazy danych 1
Ćwiczenie 3.26
Cel:
Tworzenie automatycznych sum częściowych
Polecenie:
Z pliku Bazy danych 1, z arkusza Przykład źródłowy należy skopiować listę Excela do nowego skoroszytu w celu tworzenia automatycznych sum częściowych
1. Skopiować przykład źródłowy do nowego arkusza od komórki A3 w nowym skoroszycie 2. Arkusz nazwać Sumy pośrednie
3. Obliczyć sumy pośrednie dotyczące sumy kwot na fakturze dla poszczególnych nazw klientów
4. Dodać kolejną sumę pośrednią dla każdego klienta dotyczącą maksymalnej ilości dni opóźnienia
5. Dodać kolejną sumę pośrednią dla każdego klienta dotyczącą ilości faktur z którymi klient zalega
6. Zapisać w pliku Bazy danych 2