Microsoft Excel 2000

Zadania podstawowe

Po wykonaniu kolejnych zadań radzimy czytelnikowi

zapisanie ich na dysku jako zadanie1.xls, zadanie2.xls.... Ułatwi to ich znalezienie później, gdy w następnych zadaniach będziemy rozszerzać utworzone wcześniej tabele.

Zadanie 1.

Utworzyć tabelę wypłat dla 7 pracowników w oparciu o podane niżej

dane. Do komórek nie zacieniowanych wpisz dane bezpośrednio, natomiast wartości w pozostałych komórkach powinny być wyliczone

z wykorzystaniem formuł:

LISTA WYNAGRODZE

Nazwisko

Imi

Pensja

%Premii Premia

Wypłata

Wasiołek

Marian

710,00

0%

0,00

710,00

Zajdel

Anna

810,00

10%

81,00

891,00

Adamski

Zenon

670,00

10%

67,00

737,00

Wróbel

Krzysztof

600,00

15%

90,00

690,00

Bajor

Iwona

1 390,00

20%

278,00

1 668,00

Adamski

Jan

900,00

20%

180,00

1 080,00

Nowak

Wacław

1 280,00

20%

256,00

1 536,00

Suma premii

952,00

rednia wypłata

1 044,57

Maksymalny % premii

20%

Minimalny % premii

0%

Uwaga!

1. Tekst „LISTA WYNAGRODZEŃ” należy wpisać w komórce A1,

a następnie zaznaczyć zakres komórek i użyć odpowiedniej ikony

do scalenia komórek i wycentrowania napisu.

- 126 -

Microsoft Excel 2000

2. Należy zwrócić uwagę na odpowiednie sformatowanie kolumn liczb reprezentujących kwoty (miejsce na grosze!) i procenty. Tu również można skorzystać z odpowiednich ikon. W przypadku

procentów należy pamiętać, że procenty wyświetlane są jako

jednostki, ale ich reprezentacja wewnętrzna to ułamek.

3. Prawidłowa konstrukcja formuły, która występuje wielokrotnie (tu: w kolumnie z premią i wypłatą) powinna być uniwersalna, tzn

skopiowanie jej (w tym przypadku w pionie) powinno dać

poprawne efekty. Niedopuszczalne jest ręczne powtarzanie

formuły dla każdej pozycji (takie podejście jest nieprofesjonalne i pracochłonne, a czasem praktycznie niewykonalne).

4. Używając funkcji, których parametrami są zakresy (MIN, MAX, SUMA itp.) pamiętajmy, że ręczne wpisywanie zakresu NIE JEST

ZALECANE. Należy wskazać obszar myszą, jak to zostało

opisane wcześniej. Unikniemy w ten sposób trudnych do wykrycia

pomyłek i przyspieszymy jednocześnie wprowadzanie danych.

5. Nawet tak proste zadanie może być narzędziem wspomagającym i

częściowo automatyzującym pracę kierownictwa. Jeżeli tę tabelkę

stosuje

kierownik

zespołu

podczas

przydzielania

premii

pracownikom dzieląc wyznaczoną mu pulę pieniędzy, to może

uznaniowo ustalać procent premii (często stosowana metoda) i obserwować pole Suma premii by sprawdzić, czy już osiągnął

założoną kwotę.

Zadanie 2.

Uzupełnić tabelę z zadania 1 o kolumnę z liczbą porządkową, posortować wiersze wg nazwisk, a następnie imion i nadać jej estetyczną szatę graficzną (dodać ramki, cienie itp.).

Uwaga!

1. Najlepiej najpierw posortować wiersze, by nie popsuć liczby porządkowej.

- 127 -

Microsoft Excel 2000

2. Liczbę porządkową wprowadzić wypełniając wstawioną kolumnę serią danych. Dla siedmiu pracowników może wydać się łatwe wpisanie kolejnych liczb, ale warto nabrać dobrych nawyków!

Zadanie 3.

Przygotować tabelę, w której będzie codzienny raport kursu dolara wobec złotego, według wzoru:

Dzie

Data

tygodnia

Kurs USD

00-09-25 poniedziałek 4

,45 zł

00-09-26

wtorek 4

,00 zł

00-09-27

roda 4

,43 zł

Data w pierwszej kolumnie powinna obejmować okres od dzisiaj do końca roku. Dzień tygodnia ma być automatycznie wyznaczony na podstawie daty. Kolumna kwot (które będą wypełniane na bieżąco) powinna być wstępnie sformatowana.

W zadaniu należy kontrolować wprowadzane dane. Wprowadzenie

liczby spoza zakresu <3,50 zł, 5,50 zł> powinno być sygnalizowane jako błąd. Natomiast czerwoną pogrubioną kursywą ma być

wyróżniony kurs dolara wyraźnie odbiegający od oczekiwanego, załóżmy, że spoza zakresu 4,20 zł do 4,80 zł.

Uwaga!

1. Kolumnę dat wypełnić serią wpisując dwie początkowe wartości.

2. Numer dnia tygodnia pozwoli uzyskać funkcja DZIEŃ.TYG(...),

a postać (format!) nazwy dnia nadamy tej wartości tworząc nowy

format niestandardowy dddd (Wybieramy Format | Komórki |

Liczby | Niestandardowe i następnie nadpisujemy dowolną pozycję okienka nową wartością dddd – nadpisana pozycja nie

- 128 -

Microsoft Excel 2000

zostanie

skasowana!).

Można też zastosować funkcję

TEKST( adres komórki z datą;”dddd”).

3. Kontrolę zakresu danych możemy przeprowadzić wykonując na zakresie kwot operację Dane | Sprawdzanie poprawności.

a wyróżnienie kwot spoza zakresu <4,20 ł,4,80zł>uzyskamy przez Format | Formatowanie warunkowe.

Zadanie 4.

Uwzględniając swoją datę urodzin (przyjąć godzinę 00:00) podać nazwę dnia, w którym urodziłeś się oraz obliczyć swój dotychczasowy czas życia w minutach.

Uwaga!

1. Funkcje przydatne w zadaniu to TERAZ(), TEKST(...).

- 129 -

Microsoft Excel 2000

2. Trzeba uwzględnić, jaką częścią doby jest minuta.

Zadanie 5.

Wypełnić tabelę czynszów dla mieszkańców pewnego bloku. W

miejscach zacieniowanych mają się znaleźć formuły.

c.o.

zł 2,50 za m2

ciepła w oda zł12,00 na osob

eksploatacja

zł 3,00 za m2

w inda

zł5,00 na osob

Nazw isko

pi tro

m2

osób

c.o.

c.w .

eksploatacja

razem

Jankow ski

1

34

3

85,00

36,00

102,00

223,00

Borow ski

4

6

4

15,00

48,00

18,00

81,00

Ka mierczak

7

27

6

67,50

72,00

81,00

220,50

Pietruszka

0

55

4

137,50

48,00

165,00

350,50

Nogaj

9

48

3

120,00

36,00

144,00

300,00

Kapitan

3

48

5

120,00

60,00

144,00

324,00

Rostow ski

2

90

2

225,00

24,00

270,00

519,00

Aleksiak

1

27

3

67,50

36,00

81,00

184,50

Romanow ski

6

60

7

150,00

84,00

180,00

414,00

Suma

395,00

37

987,50

444,00

1185,00

2616,50

rednia

43,89

4,11

109,72

49,33

131,67

290,72

Uwaga!

1. Kolumna “piętro” oraz stawka za korzystanie z windy wydają się na razie niepotrzebne. Znajdą one zastosowanie w jednym z

kolejnych zadań.

2. Wartości stawek NIE NALEŻY wpisywać bezpośrednio do

formuł! Arkusz nie będzie wówczas reagował na zmiany w tabeli

stawek. Formuły dla kolumn z poszczególnymi składnikami

czynszu powinny być podobne do formuły dla c.o. pokazanej w ramce. Adresowanie bezwzględne komórki zawierającej stawkę zapewnia, że jej adres nie będzie się zmieniał przy kopiowaniu formuły w dół.

- 130 -

Microsoft Excel 2000

Zadanie 6.

Firma ubezpieczeniowa „Jedź ostrożnie” oblicza składkę podstawową

za ubezpieczenie samochodu jako 7,55% jego ceny. Składka jest obniżana o 5% za każde zainstalowane zabezpieczenie przed

kradzieżą.

1. Wypełnić formułami zacieniowane pola w tabeli

2. Składka podstawowa oraz zniżka powinny być zaokrąglone do pełnych groszy (funkcja ZAOKR)

Staw ka

podstaw ow a

7,55% ceny samochodu

Zni ka za

zabezpieczenia

5% składki podstaw ow ej

Cena

Liczba

Składka

Zni ka za

Nazw isko

samochodu

zabezpiecze

podstaw ow a zabezpieczenia Do zapłaty

Kow alski

25 000,00

1

1 887,50

94,38

1 793,12

Jaw orski

32 150,00

1

2 427,33

121,37

2 305,96

Now ak

49 500,00

3

3 737,25

560,59

3 176,66

Orłow ski

12 000,00

1

906,00

45,30

860,70

Zieli ski

55 000,00

3

4 152,50

622,88

3 529,62

Kami ski

32 400,00

2

2 446,20

244,62

2 201,58

Kus

27 900,00

1

2 106,45

105,32

2 001,13

Mokrzycki

18 700,00

0

1 411,85

0,00

1 411,85

Suma

252 650,00

12

19 075,08

1 794,46

17 280,62

Uwaga!

Niewstawienie funkcji zaokrąglenia jest najpowszechniejszym

błędem, którym obciąża się zwykle Excela, bo wydaje się wtedy, że

„komputer robi błędy” już przy zwykłym podsumowaniu!.

Tymczasem w tej sytuacji występuje różnica między wartością (dokładniejszą) używaną do obliczeń, a precyzją wyświetlania

- 131 -

Microsoft Excel 2000

(zwykle automatycznie zaokrągloną). Dlatego trzeba dbać (funkcje zaokrąglenia i precyzja formatu), by zaokrąglać i wyświetlać z tą samą liczbą miejsc po przecinku!

Zadanie 7.

Utworzyć „tabliczkę mnożenia” <1,10> * <1,10>.

Uwaga!

Na przecięciu wartości opisującej wiersz i kolumnę w lewym górnym

rogu wpisać wyrażenie oparte na adresacji mieszanej tak, by powielone na całą tablicę (Ctrl i Enter), dało w efekcie tabliczkę mnożenia.

Zadanie 8.

Utworzyć tablicę przedstawiającą wartość sprzedaży w 4 kwartałach bieżącego roku (kolumny) w rozbiciu na trzy grupy asortymentowe (wiersze).

Przewidujemy, że w następnym roku wartość sprzedaży w każdej z grup asortymentowych będzie skorygowana o pewien (ten sam w każdym kwartale) współczynnik. Przykładowa kolumna trzech współczynników: 0,9 1,2 1,3.

Należy zbudować tabelę planowanej, skorygowanej sprzedaży

korzystając z adresacji mieszanej.

Uwaga!

1. Trzeba utworzyć wyrażenie tylko dla 1-szego asortymentu 1-go kwartału i powielić na pozostałe pozycje nowej tablicy.

2. Ćwiczenie dodatkowe. Umieścić współczynniki w wierszu i spróbować rozwiązać tak zmodyfikowane zadanie.

- 132 -