Excel 3-4 – extra zadania.

Plik do ściągnięcia – Zadanie1.xls

  1. Przygotowanie danych do pracy:

    1. Dopasuj szerokości kolumn do zawartości danych.
  2. W arkuszu Kod uzupełnij pole Sprzedawca jego imieniem i nazwiskiem, korzystając z odpowiedniej funkcji Excela.
  3. W bazie danych wstaw pustą kolumnę na początku bazy.
  4. Nazwij nagłówek nowo utworzonej kolumny Lp.
    1. Wypełnij kolumnę automatycznie numerując wiersze.
  5. Stworzoną formułę skopiuj do pozostałych wierszy w kolumnie A
  6. Wstaw pustą kolumnę w miejsce S i nazwij ją Nagrody.
  7. Za pomocą funkcji jeżeli, dla każdej transakcji o Wartości Końcowej powyżej 1000, przypisz nagrodę o wartości 50 – jeżeli będzie poniżej proszę zostawić pustą komórkę.

Plik do ściągnięcia – Zadanie2.xls

  1. Przygotowanie danych do pracy:
    1. Dopasuj szerokości kolumn do zawartości danych.
  2. Wykorzystując tabele przestawne, na bazie arkusza Baza_danych, przygotuj w oddzielnym arkuszu:
    1. Zestawienie wszystkich Sprzedawanych produktów i ich Ilości w krajach odbiorcy,
      (Wzór tabeli wynikowej):

tabela-wynikowa

2. Sformatuj nagłówki tak jak na powyższym przykładzie i dostosuj szerokości kolumn do danych.

3. Korzystając z funkcji MAX wyznacz (komórka W2) ile razy został sprzedany najczęściej sprzedawany produkt. Wyeksponuj jego nazwę (pogrubienie, kolor).

4.  Wyeksponuj nazwę kraju, w którym sprzedano największą ilość produktów.

Excel 2-3 (3h)

Ćwiczenie 1.

  1. Otwórz plik Praca_z_danymi.xls
  2. Zablokuj górny wiesz.
  3. Popraw GDYNAI na GDYNIA
  4. Posortuj dane wg “port wyładunku”
  5. Za pomocą formatowania warunkowego w kolumnie kon 40’wstaw zielone strzałki obok wartości większych niż 2.
    ti-cw1
  6. Dla kolumn kon ’20 i kon ’40 ustaw możliwość wpisywania tylko liczb naturalnych.
  7. W kolumnie M ogranicz możliwość wpisywania wartości do listy rozwijanej.

Ćwiczenie 2.

  1. Włącz “filtr”, aby uaktywnić funkcję autofiltru. Wyświetl wyniki dla “CIF”, biura “SZC”, gdzie port wyładunku to nie “GDYNIA”. Oblicz sumę kontenerów 40′.
  2. Za pomocą tabeli przestawnych uzyskaj poniższe widoki.
cw-ti
  1. Używając sum częściowych utwórz automatyczne zestawienie pokazujące sumy ilości kontenerów 20′ i 40′ dla poszczególnych przewoźników (pamiętaj aby posortować tabelę).
  2. (Przykład dodatkowy) Utwórz tabelę przestawną pokazującą sumy kon 40′ dla poszczególnych obsługujących z podziałem na miesiące.
cw3-ti

Ćwiczenie 3.

  1. Otwórz plik kody_pocztowe.xls
  2. Za pomocą funkcji wyszukaj.pionowo (vlookup) dopasuj miasto do kodów pocztowych.
  3. Otwórz plik praca_z_danymi.xls
  4. Wstaw kolumnę “Imię i Naziwsko” w arkuszu Dane. Użyj funkcji wyszukaj.pionowo. Na podstawie inicjałów obsługującego (Kolumna M) wpisz w nową kolumnę imię i nazwisko. Aby połączyć teksty użyj funkcji złącz.tekst.
  5. Zabezpiecz skoroszyt hasłem.
  6. (przykład dodatkowy). Utwórz kalkulator walutowy. Najpierw pobierz aktualną tabelę kursów NBP z http://www.nbp.pl/kursy/kursya.html. Następnie w polu1 za pomocą “Poprawność danych” utwórz pole wyboru spośród walut. W polu 2 możesz wpisać dowolną liczbę. W polu 3 za pomocą funkcji wyszukaj.pionowo wyświetl kurs aktualnie wybranej waluty z pola powyżej.
cw4-ti

 

Przeszukiwany zakres: Fałsz wpisujemy zawsze, gdy funkcja musi odszukać dokładny odpowiednik, czyli np. wyraz jak przypadku powyżej. Jeśli funkcja ma tylko określić przedział liczb, w jakim znajduje się wartość trzeba wpisać Prawda lub pozostawić okienko puste.

Pobierz załącznik: praca_z_danymi.xls  |   kody_pocztowe.xls

Ćwiczenie 1.

Utwórz nowy dokument MS Excel.

Zmień nazwę Arkusz1 na nazwę “Sprawozdanie”. Do arkusza Sprawozdanie przekopiuj 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:

 

 

 

 

  • Pamiętaj, aby zapisać swoją pracę.
  • Jeżeli szerokości kolumną są zbyt wąskie w stosunku do ich zawartości, to dopasuj je.
  • Wpisz datę przygotowania zestawienia jako dzisiejszą datę (użyj funkcji).
  • Oblicz średnią płacę oddzielnie dla każdego miesiąca oraz średnią ze wszystkich miesięcy.
  • W podobny sposób oblicz najwyższą i najniższą płacę korzystając z funkcji Max i Min. Wypełnij tabelę przez kopiowanie formuł.
  • Danle należy posortować według nazwisk w porządku alfabetycznym.

Ćwiczenie 2.

Utwórz nowy arkusz i zmień jego nazwę na “Koszty usług” i utwórz tabelę (poniżej) 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.

cw5-ti

 

Ćwiczenie 3.

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ą lub maleją przewidywane zarobki.

Excel 1

Plan zajęć obejmuje

  • MS Excel 8 godzin;
  • MS word 1 godzina;
  • Wyszukiwanie informacji 1 godziny;
  • MS Power Powerpoint 2 godziny;
  • Zaliczenie 2 godziny;
  • Wpisy.

Obecność na zajęciach obowiązkowa!! – 100% frekwencji!

Polecenia do pliku ćwiczeniowego:

  1. Wstaw automatyczną numerację wierszy (kolumna A) ? Nie używaj “przeciągania”.
  2. Sformatuj dane w kolumnie G na układ liczbowy, 1 liczba po przecinku i separator.
  3. Wstaw dzisiejszą datę w komórce C1 (użycie odpowiedniej funkcji).
  4. Oblicz w kolumnie I prowizję 10% od kwoty brutto (kolumna G).
  5. Oblicz w kolumnie J prowizję od kwoty brutto z zastosowanym odwołaniem bezwzględnym z komórki F5.
  6. Zaokrąglij prowizję do pełnych wartości w górę.
  7. W kolumnie K, wykorzystując funkcję JEŻELI, przyporządkuj kwotę 500 jeśli prowizja jest mniejsza od 500 złotych lub zachowaj obliczoną wcześniej prowizję jeśli przekracza ona 500 złotych.
  8. Dodaj komórki G i K oraz dodaj 20 zł kosztów delegacji dla transakcji spoza Gdańska. Sformatuj kolumnę G na format walutowy.
  9. Oblicz sumę wszystkich zamówień, średnią wartość zamówienia, minimalne, maksymalne zamówienie. Jaka będzie suma zamówień z rejonu “północ” ? Znajdź conajmniej dwa sposoby, aby to pokazać (funkcja suma.jeżeli, autofiltr, sumy częściowe, sumy pośrednie, tabela przestawna, sortowanie). Ile zamówień pochodzi z rejonu “zachód”?
  10. W kolejnym arkuszu zaimportuj średnie kursy walut wg NBP z portalu money.pl. Czy zaimportowane dane będą się automatycznie aktualizowały ? Jaka jest różnica między arkuszem a skoroszytem ?

 

Uwaga! Proszę regularnie zapisywać arkusz.
Jeśli w komórce pojawi się “#######” proszę rozszerzyć kolumnę.

Pobierz załącznik: Podstawy_msexcel.xls