Ćwiczenia



Pobieranie 139,28 Kb.
Data24.02.2019
Rozmiar139,28 Kb.

Ćwiczenia


Ćwiczenie 1.

W ramach ćwiczenia zmień nazwę Arkusz1 na nazwę Sprawozdanie klikając dwukrotnie lewym przyciskiem myszy, w momencie gdy kursor myszy znajduje się na nazwie arkusza Arkusz1.



W arkuszu Sprawozdanie wpisz dane dotyczące zarobków pracowników w poszczególnych miesiącach, według poniższej tabeli:


Imię

Nazwisko

styczeń

luty

marzec

Ala

Kowalska

1020

1670

2000

Ala

Nowak

980

1000

2450

Inga

Wesoła

990

800

1800

Iza

Grabowska

990

700

860

Kasia

Nowicka

1180

450

750

Anita

Ząbek

1106

1200

620

Zofia

Kowalczuk

1004

950

590

Alicja

Młyńska

1003

950

850

Olga

Piekarz

1108

780

450

Ania

Bednarz

1103

980

1450




Średnia:













Najwyższa płaca:













Najniższa płaca:













Data zestawienia:









Jeżeli szerokości kolumn są zbyt wąskie w stosunku do ich zawartości, to dopasuj je (zaznacz cały obszar z danymi i wybierz opcje Format \Kolumna \Autodopasowanie obszaru).

Wpisz datę przygotowania zestawienia jako dzisiejszą datę, wpisując do wybranej komórki =dziś(). Oblicz średnią płacę oddzielnie dla każdego miesiąca oraz średnią ze wszystkich miesięcy. W tym celu skorzystaj z ikony , wybierając funkcję Średnia. W podobny sposób oblicz najwyższą

i najniższą płacę korzystając z funkcji Max i Min. Obliczając dane dla poszczególnych miesięcy należy zaproponowaną formułę kopiować do kolejnych komórek a nie ponownie wpisywać od początku (można stosować np. opcje Edycja \Kopiuj i Edycja \Wklej lub kombinacje przycisków <Ctrl> + <C> i <Ctrl> + <V>).

Dane należy posortować według nazwisk w porządku alfabetycznym. W tym celu zaznacz obszar, który będzie podlegał sortowaniu i użyj opcji Dane \Sortuj. Pracę należy zachować, w celu ponownego wykorzystania w jednym z kolejnych ćwiczeń.
Ćwiczenie 2.

Ćwiczenie rozpoczyna się od stworzenia nowego pliku za pomocą opcji Plik \Nowy. W arkuszu Arkusz1 zmień jego nazwę na Koszty usług i utwórz tabelę (rys. 1) opisującą ceny wybranych rodzajów świadczonych usług. Cenę świadczonej usługi kalkuluje się w ten sposób, że firma dolicza do ceny netto określoną kwotę marży (np. 16%), której wartość umieszczono w komórce E2. Samodzielnie zaproponuj formułę, jaką wpisze się do kolumny Cena usługi i skopiuj tę formułę do poszczególnych komórek. Komórki w kolumnie Cena netto sformatuj jako zapis walutowy wybierając z paska narzędzi ikonę . Komórkę E2 trzeba sformatować jako liczby procentowe za pomocą ikony .


Rys. 1 Tabela kosztów usług


W arkuszu Arkusz2 zmień jego nazwę na Stan oszczędności i stwórz tabelę jak na rys. 2 W komórce C5 wpisz formułę w taki sposób aby skopiowana do komórek C6 i C7 dała wartość kwoty oszczędności wraz z odsetkami po roku oszczędzania. Następnie w komórce D5 wprowadź formułę tak, aby skopiowana do pozostałych wypełnianych komórek (D6, D7 oraz E5:F7) dała wartość kwoty oszczędności wraz z odsetkami po kolejnych latach oszczędzania. W tym przypadku, w adresie komórki należy zastosować adresowanie względne i bezwzględne.

Rys. 2 Tabela stanu oszczędności


Tekst Czas oszczędzania w komórce A4 został sformatowany
z opcją Zawijaj tekst ustawianą w oknie Formatuj komórki na karcie Wyrównanie. Okno Formatuj komórki można wywołać z menu za pomocą opcji Format \Komórki.
Ćwiczenie 3.

Na podstawie tabeli płac stworzonej w ćwiczeniu 1 utwórz wykres procentowego udziału w całkowitych zarobkach w miesiącu styczniu przez poszczególnych pracowników (wykres kołowy). Następnie przygotuj wykres kolumnowy średniej płacy w poszczególnych miesiącach. Każdy z tworzonych wykresów powinien być szczegółowo opisany (tzn. musi zawierać tytuły, odpowiednie opisy osi itp.). Wykres jest tworzony przez wywołanie opcji Wstaw \Wykres lub wybranie ikony . Jeżeli wykres jest tworzony na podstawie danych, które nie stanowią zwartego bloku komórek, dwa rozłączne obszary komórek są zaznaczane przy wciśniętym przycisku <Ctrl>.

W tabeli z ćwiczenia 1 dodaj kolumny o tytułach kolejnych trzech miesięcy roku. W tym celu zaznacz komórkę z ostatnio wpisanym miesiącem i gdy kursor myszy znajduje się nad uchwytem przeciągania, trzymając lewy przycisk myszy rozszerz zaznaczony obszar o kolejne komórki. Zaznaczone komórki zostaną automatycznie wypełnione nazwami kolejnych miesięcy. Do stworzonych kolumn wpisz płace za nowe miesiące, używając formuł tak, aby wzrastały zawsze o kilka procent w stosunku do wartości z poprzedniego miesiąca.

Na podstawie otrzymanych danych stwórz wykres dla jednego wybranego pracownika przedstawiającego jego dochody w kolejnych miesiącach (oś OX oznacza kolejne miesiące). Następnie uaktywnij serię danych na wykresie i wybierz opcję Wykres \Dodaj linię trendu, ustalając trend liniowy. Dodatkowa linia, która pojawiła się na wykresie pokazuje trend z jakim rosną przewidywane zarobki. Zadanie zachowaj w odrębnym skoroszycie


Ćwiczenie 4.

W ramach ćwiczenia w nowym skoroszycie zmień nazwę Arkusz1 na nazwę Kurs księgowania. Następnie stwórz tabelę z listą osób, które zgłosiły się na kurs księgowania. Poza tabelą, umieść w komórce E2 arkusza formułę, która wyznacza liczbę utworzonych grup słuchaczy przy założeniu, że dopuszczalna liczba osób w grupie znajduje się w komórce E1. W opisywanej formule można wykorzystać jedną z funkcji zaokrąglających, należących do kategorii Matematyczne (o nazwach zaczynających się od łańcucha znaków ZAOKR). Formuła spełniająca opisywane wymagania może mieć postać:



ZAOKR.W.GÓRĘ(MAX(A:A)/E1;1)
gdzie A oznacza nazwę kolumny w której będzie występował numer uczestnika kursu.

W pierwszym wolnym wierszu kolumny Lp. (komórka A5) wpisz formułę lub funkcję obliczającą numer słuchacza, który jest liczbą naturalną. Wywołanie przykładowej funkcji realizującej podane zadanie może mieć postać:



JEŻELI(CZY.NIE.TEKST(A4);A4+1;1)
Stworzoną formułę skopiuj do pozostałych wierszy w kolumnie A (aż do komórki A9). Następnie w komórce F5 wpisz formułę wyznaczającą numer grupy do której zostaje przydzielony słuchacz kursu. Przykładem takiej formuły jest:

ZAOKR.DÓŁ((A5+$E$1-1)/$E$1;0)
Formułę z komórki F5 skopiuj do komórek F6:F9.

Opis stosowanych funkcji można znaleźć korzystając z opcji Pomoc \Spis treści i indeks oraz podając w polu dialogowym karty Znajdź nazwę funkcji.




Rys. 3 Tabela uczestników kursu księgowania
Po skonstruowaniu tabeli, w komórce E1 zmień liczbę osób w grupie na równą 4 i sprawdź jak to wpłynęło na podział osób na grupy. W ramach ćwiczenia dowiedz się co oznaczają zastosowane funkcje.

W formułach arkusza można zamiast adresów bezwzględnych zdefiniować nazwę dla każdej komórki o danym adresie. Przykładowo, zamiast adresu $E$1 w formułach z kolumny F można wprowadzić nazwę Liczba.Słuchaczy i z niej korzystać przy pisaniu formuł. W ramach ćwiczenia zdefiniuj wymienioną nazwę i popraw odpowiednie formuły. W tym celu zaznacz komórkę, która będzie nazywana ($E$1), wybierz opcje Wstaw \Nazwa \Definiuj i wpisz własną nazwę Liczba.Słuchaczy.

Teraz popraw formułę z komórki F5, wybierając klawisz F2 i wpisując nazwę identyfikującą adres komórki $E$1. Zamianę adresów można wykonać automatycznie zaznaczając zakres z formułami do poprawienia i wybierając opcje Wstaw \Nazwa \Zastosuj.

Do tabeli dopisz dane o sześciu osobach korzystając z formularza. W tym celu ustaw komórkę aktywną na obszarze tabeli i wybierz z menu opcje Dane \Formularz. Z formularza korzysta się przy dopisywaniu, usuwaniu i oglądaniu danych. Warto zwrócić uwagę, że do formularza można wpisywać tylko te wartości, które nie są formułami. Formuły są kopiowane automatycznie.

Jeśli lista osób, które zgłosiły się na kurs księgowania jest już zamknięta to należy ją posortować pamiętając, że numer grupy musi być związany z daną osobą. Aby zapobiec sortowaniu formuł, według których obliczany jest numer grupy, trzeba zamienić formuły na wartości. W tym celu zaznacz zakres komórek obejmujący całą tabelę. Następnie wybierz opcje Edycja \Kopiuj, po czym opcje Edycja \Wklej specjalnie, Wklejamy wartości. Wprowadzone zmiany zachowaj w bieżącym skoroszycie.
Ćwiczenie 5.

W arkuszu Arkusz2 skoroszytu z poprzedniego ćwiczenia zmień jego nazwę na Wpłaty i stwórz tabelę jak na rys. 4.



Rys. 4 Tabela wpłat uczestników kursu
W kolumnie C wpisz formułę wyszukującą nazwisko i imię według numeru słuchacza z kolumny B, na podstawie danych z arkusza Kurs księgowania. W tym celu można wykorzystać dwie funkcję ZŁĄCZ.TEKSTY z kategorii tekstowe oraz WYSZUKAJ.PIONOWO
z kategorii wyszukiwania i adresu. Przykładowe wywołanie funkcji wpisanych do komórki C4 ma postać:

ZŁĄCZ.TEKSTY(

WYSZUKAJ.PIONOWO(B4;'Kurs księgowania'!A:F;2;fałsz);" ";

WYSZUKAJ.PIONOWO(B4;'Kurs księgowania'!A:F;3;fałsz)).

Proponowaną formułę można wpisać ręcznie, wprowadzając każdy znak za pomocą klawiatury lub korzystając z ikony Wklej funkcję wstawiając funkcję ZŁĄCZ.TEKSTY() bez podania jej argumentów. Następnie klikając ikonę Wklej funkcję należy wstawić funkcję WYSZUKAJ.PIONOWO() jako argumenty poprzednio wstawionej funkcji.

Tak przygotowana formuła powinna zwracać nazwisko i imię słuchacza kursu o numerze podanym w komórce o adresie B4. Skopiuj powyższą formułę do pozostałych komórek C5C9. Skonstruowana formuła uwzględnia adres tabeli z danymi podany w postaci nazw kolumn A:F tak, aby przy kopiowaniu formuły zakres tabeli ze spisem uczestników kursu nie zmieniał się.

Korzystając z opcji Dane \Filtr \Zaawansowany filtr zrób następujące zestawienia: wszystkie wpłaty Macieja Matysiaka; wszystkie wpłaty dokonywane od 4 do 6 maja; wszystkie wpłaty osób o nazwisku rozpoczynającym się na literę T lub M. Po każdym zastawieniu można powrócić do wyświetlania całej listy wybierając opcje Dane \Filtr \Pokaż wszystko. Sposób zapisu kryteriów przytoczono na rys. 14.

Korzystając z opcji Dane \Sumy pośrednie zrób następujące zestawienia:


  • sumy wpłat każdego słuchacza kursu;

  • sumy zebrane każdego dnia w kasie;

  • ilość wpłat każdego słuchacza kursu.

Warto pamiętać, że przed wykonaniem każdej operacji trzeba najpierw odpowiednio posortować dane na liście wpłat. Po każdym zestawieniu można powrócić do wyświetlania całej listy wybierając opcje Dane \Sumy pośrednie oraz Usuń wszystko.
Ćwiczenie 6.

W ramach ćwiczeń otwórz nowy plik i zmień nazwę arkusza Arkusz1 na nazwę Lista osób. W arkuszu Lista osób wpisz dane dotyczące uczestników kursu według podanej niżej tabeli:


A B C D E

Lp.

Nazwisko

Imię

Adres

Grupa

1

Anecka

Dominika

Krzywa 8

1

2

Jankowska

Monika

Leśna 3

1

3

Matysiak

Maciej

Leśna 5

1

4

Okoński

Michał

Akacjowa 9

1

5

Pawlicka

Jagoda

Polna 2

2

6

Sitko

Paweł

Opolska 3

2

7

Stryjska

Anna

Topolowa 4

2

8

Tarnowska

Maria

Spokojna 2/5

2

9

Tarnowska

Monika

Spokojna 2/5

3

10

Wierniak

Krzysztof

Pogodna 3/23

3

W kolejnym kroku zadania, w arkuszu Arkusz2, którego nazwa wymaga zmiany na Luty wpisz informację o dokonanych wpłatach, według zawartości tabeli:


A B C D

Data

Numer ucznia

Nazwisko i imię

Kwota

98-02-03

3

Matysiak Maciej

20

98-02-05

9

Tarnowska Monika

35

98-02-03

5

Pawlicka Jagoda

20

98-02-04

8

Tarnowska Maria

15

98-02-12

3

Matysiak Maciej

20

98-02-05

3

Matysiak Maciej

10

98-02-04

6

Sitko Paweł

20

98-02-07

7

Stryjska Anna

15

98-02-07

2

Jankowska Monika

15

98-02-10

4

Okoński Michał

10

98-02-11

1

Anecka Dominika

20

Pamiętaj, że w kolumnie Nazwisko i Imię można zastosować formułę z poprzedniego ćwiczenia:



ZŁĄCZ.TEKSTY(

WYSZUKAJ.PIONOWO(B2 ;'Lista osób'!A:F;2;fałsz);

" ";WYSZUKAJ.PIONOWO(B2;'Lista osób'!A:F;3;fałsz)).

Pracę przy wpisywaniu powyższych funkcji można sobie ułatwić, korzystając z kreatora funkcji i ustalania jej argumentów przez zaznaczenie kursorem wybranego obszaru komórek, również w przypadku gdy należą do nieaktywnego arkusza.

Za pomocą tabel przestawnych wykonaj zestawienie wpłat za miesiąc luty. W tym celu wybierz opcję Dane \Raport tabeli przestawnej. W kreatorze tabeli przestawnej przy tworzeniu zestawienia za miesiąc luty, po ustawieniu komórki aktywnej w obszarze wprowadzonych danych,
w pierwszym kroku wybierz opcję Lista lub baza danych Microsoft Excel. W drugim kroku przyjmij domyślny zakres danych klikając przycisk Dalej>. Do tabeli przestawnej wybierz pola: STRONA - Numer ucznia; WIERSZ - Nazwisko i imię, DANE - Kwota. Tabelę przestawną umieść w nowym arkuszu. Obejrzyj zestawienie dla studenta numer 3.
Ćwiczenie 7.

Korzystając z dotychczasowej wiedzy o arkuszu kalkulacyjnym, samodzielnie zaprojektuj arkusz wyznaczający wartość podatku do zapłacenia w rozliczeniu rocznym dla osoby fizycznej. Należny podatek jest wyznaczany na podstawie wartości uzyskiwanych co miesiąc dochodów


i płaconych zaliczek, zapisanych w postaci dwukolumnowej tabeli. W zadaniu należy przyjąć progi podatkowe według poniższej tabeli:


Dochód

Podatek

do 25252 zł

19% dochodu minus 336,60 zł

od 25252 zł do 50504 zł

4461,28 zł plus 30% od kwoty dochodu powyżej 25252 zł

Powyżej 50504 zł

12036,88 zł plus 40% od kwoty dochodu powyżej 50504 zł

W arkuszu, do obliczenia podatku można wykorzystać znaną z poprzednich ćwiczeń funkcję WYSZUKAJ.PIONOWO() lub funkcję JEŻELI() z kategorii logiczne.


Ćwiczenie 8.

Sklep prowadzi rejestr sprzedawanych towarów w arkuszu kalkulacyjnym (nazwa towaru, trójznakowy kod towaru, ilość, wartość za jednostkę). Istnieje potrzeba drukowania rachunków klientom sklepu. W ramach ćwiczenia, wykorzystując spis towarów umieszczony w jednym arkuszu, wypełnij drugi arkusz tak, aby wyszukiwał informację o produkcie na podstawie podanego kodu towaru. Wyszukaną informację umieść w komórkach tworzących rachunek dla klienta. Przygotuj makro, które spowoduje wydrukowanie rachunku i pomniejszenie liczby towarów znajdujących się w sklepie o sprzedane sztuki. Wiadomości o sposobie rejestrowania makr można uzyskać korzystając z dostępnej pomocy w programie (hasło makra, rejestrowanie w karcie Indeks).


Ćwiczenie 9.

Firma produkująca płyty gipsowe ma 6 fabryk w okolicy wybranych dużych miast Polski (Kraków, Gdynia, Gorzów, Warszawa, Zielona Góra, Białystok), które są również centrami dystrybucji. Koszty transportu jednej palety płyt między fabrykami są następujące:






Warszawa

Zielona Góra

Białystok

Kraków

3000

2600

3500

Gdynia

3100

3400

2900

Gorzów

3200

3700

2800

Zdolności produkcyjne fabryk w Krakowie, Gdyni i Gorzowie są większe niż zapotrzebowanie w odpowiadających im rejonach dystrybucji odpowiednio o 500, 400, 700 palet. Niezaspokojony popyt na płyty w regionach podległych poszczególnym fabrykom wynosi odpowiednio


w Warszawie 250, Zielonej Górze 400, Białymstoku 160 palet. Zadanie polega na określeniu jakie ilości palet między którymi fabrykami należy transportować, aby całkowity koszt zrównoważenia popytu był jak najmniejszy.

W celu rozwiązania postawionego zadania skorzystaj z dostępnego w arkuszu kalkulacyjnym narzędzia Solver. W pierwszym kroku wprowadź do arkusza dane dotyczące kosztów transportu (rys. 5, blok komórek B10:E14). W drugim kroku wpisz nagłówki tabeli która będzie zawierać obliczone liczby palet transportowanych między poszczególnymi ośrodkami. Następnie wprowadź do bloków komórek C21:E21 i F18:F20 funkcje sumy ze wszystkich wartości transportowanych z danego miasta dostawcy do miasta przeznaczenia. W kolejnym kroku wpisz do komórki G13 formułę określającą całkowity koszt transportu. Opisywana formuła jest sumą iloczynów liczby transportowanych palet z trzech fabryk przez jednostkowy koszt transportu dla tych trzech ośrodków.




=suma(c18:c20)

Rys.5 Arkusz z danymi dla zadania optymalizacji kosztów transportu

Zadaniem narzędzia Solver jest wyznaczenie wartości bloku komórek C18:E20 dla której funkcja celu, którą jest formuła umieszczona w komórce G13, posiada minimum. Wyznaczane wartości muszą spełniać kilka warunków ograniczających zakres dostępnych rozwiązań. Ich postać przytoczono w oknie dialogowym ustawiania parametrów narzędzia Solver (rys. 6).



Rys. 6 Okno ustawiania parametrów narzędzia Solver


Widoczne na rys. 6 kryteria ograniczają możliwe rozwiązania do:

  • dodatnich wartości transportowanych palet,

  • liczba transportowanych palet do poszczególnych ośrodków odpowiada występującemu popytowi,

  • suma wysyłanych palet z danej fabryki nie przekracza jej możliwości produkcyjnych.

Minimalne koszty transportu są obliczane przez zmianę liczby transportowanych palet z poszczególnych fabryk, z określonym krokiem (ustawianym po wybraniu przycisku Opcje w oknie dialogowym z rys. 6) aż do uzyskania rozwiązania według podanych warunków ograniczających.

Po dokonaniu obliczeń, w oknie dialogowym Solver-wyniki zaznacz dostępne rodzaje raportów i zapoznaj się z ich treścią.




©operacji.org 2019
wyślij wiadomość

    Strona główna