Topic outline

  • General

    logo_UE_KL_SGGW.png

    Zaawansowana obsługa pakietu oprogramowania - Moduł 2

  • Topic 1

    Arkusz kalkulacyjny Excel - część 1

  • Topic 2

    Arkusz kalkulacyjny Excel - część 2

    • FORMUŁY TEKSTOWE

    • Zadanie 1

      Otwórz plik Baza2.xls i w nim rozwiązuj wszystkie kolejne zadania. Wstaw nową kolumnę A, nazwij ją LP i umieść w niej formułę wstawiającą kolejny numer pracownika w taki sposób, aby formuła była niezależna od sortowania.

    • Zadanie 2

      Wstaw nową kolumnę D, nazwij ją PRACOWNIK i umieść w niej formułę łączącą imię pracownika z kolumny B (IMIĘ) oraz nazwisko z kolumny C (NAZWISKO) nie używając funkcji.

    • Zadanie 3

      Wstaw nową kolumnę E, nazwij ją PRACOWNIK.2 i umieść w niej formułę łączącą imię pracownika z kolumny B (IMIĘ) oraz nazwisko z kolumny C (NAZWISKO) używając funkcji w taki sposób, aby nazwisko było zapisane wielkimi literami.

    • Zadanie 4

      Łatwo zauważyć, że niektóre dane pracowników w kolumnach D (PRACOWNIK) i E (PRACOWNIK.2) zawierają błędy ? niepotrzebne spacje. Zmodyfikuj formułę umieszczoną w kolumnie E (PRACOWNIK.2) w taki sposób, aby usunąć niepotrzebne spacje.

    • Zadanie 5

      Wstaw dwie nowe kolumny F, G, nazwij je IMIĘ.2, NAZWISKO.2 i umieść w nich formuły wycinające z kolumny E (PRACOWNIK.2) imię pracownika oraz nazwisko.

    • Zadanie 6

      Wstaw nową kolumnę H, nazwij ją PŁEĆ i umieść w niej formułę wstawiającą literę ?K? lub ?M? w zależności od tego, czy pracownik jest kobietą, czy mężczyzną.

    • FORMUŁY DATY I CZASU

    • Zadanie 7

      Łatwo zauważyć, że dane w kolumnie I (ZATRUDNIENIE) nie są zapisane w formacie daty, lecz w formacie tekstowym. Dane zostały zapisane w takim formacie, że nie pomaga zmiana formatowania komórki. Wstaw nową kolumnę J, nazwij ją DATA.ZATRUDNIENIA i umieść w niej formułę zamieniającą dane z kolumny I (ZATRUDNIENIE) na datę.

    • Zadanie 8

      Wstaw nową kolumnę K, nazwij ją STAŻ i umieść w niej formułę obliczającą staż pracy na podstawie kolumny J (DATA.ZATRUDNIENIA).

    • Zadanie 9

      Wstaw nową kolumnę P nazwij ją WIEK i umieść w niej formułę zmieniającą dane umieszczone w kolumnie O (URODZENIE) na datę.

    • Zadanie 10

      Zmień formułę umieszczoną w kolumnie P (WIEK) tak aby obliczała wiek pracownika na podstawie kolumny Q (URODZENIE).

    • FORMUŁY WARUNKOWE I WYSZUKIWANIA

    • Zadanie 11

      Wstaw nową kolumnę U, nazwij ją SAMODZIELNY i umieść w niej formułę wstawiającą ciąg znaków ?NS? lub ?S? w zależności od tego, czy pracownik jest profesorem lub doktorem habilitowanym, czy też nie.

    • Zadanie 12

      Wstaw nową kolumnę T, nazwij ją PENSUM i umieść w niej formułę wstawiającą ilość godzin składającą się na roczne pensum pracownika w zależności od stanowiska zgodnie ze słownikiem umieszczonym w arkuszu Słowniki, w zakresie A1:D8.

    • Zadanie 13

      Wstaw nową kolumnę U, nazwij ją PENSUM2 i umieść w niej formułę wstawiającą ilość godzin składającą się na roczne pensum pracownika w zależności od stanowiska zgodnie ze słownikiem umieszczonym w arkuszu Słowniki, w zakresie A1:D8.

    • Zadanie 14

      Wstaw kolumnę V (PENSUM3), w której skoryguj formułę, uwzględniając zmniejszenie pensum dla Dziekanów i Prodziekanów zgodnie ze słownikiem umieszczonym w arkuszu Słowniki, w zakresie A10:B12.

    • Zadanie 15

      W celu wygodniejszego przeglądania arkusza podziel go w taki sposób, aby zawsze widoczne były: 1 wiersz i pierwsze 3 kolumny.

    • Zadanie 16

      Nazwij kolumnę Y PODSTAWA i umieść w niej formułę wstawiającą kwotę podstawy pensji w zależności od stanowiska zgodnie ze słownikiem umieszczonym w arkuszu Słowniki, w zakresie A1:B8.

    • Zadanie 17

      Nazwij kolumnę Z WYSŁUGA i umieść w niej formułę obliczającą kwotę wysługi lat w wysokości 1% podstawy pensji za każdy przepracowany pełny rok, jeżeli pracownik pracuje krócej niż 20 lat oraz 20% podstawy pensji dla pozostałych. Użyj funkcji JEŻELI.

    • Zadanie 18

      Nazwij kolumnę AA FUNKCYJNY i umieść w niej formułę wstawiającą kwotę dodatku funkcyjnego w zależności od zajmowanej funkcji zgodnie ze słownikiem umieszczonym w arkuszu Słowniki, w zakresie F1:G5.

    • Zadanie 19

      Wstaw nową kolumnę Z, nazwij ją NADGODZINY i umieść w niej formułę obliczającą wysokość dodatku za nadgodziny w sposób najkorzystniejszy dla pracownika. Oznacza to, że maksymalna liczba godzin przepracowanych na studiach zaocznych powinna zostać wliczona w nadgodziny , ponieważ każda z nich jest mnożona przez współczynnik 1,2. Jeżeli pracownik nie wypracuje swojego pensum dostaje oczywiście 0 zł dodatku za nadgodziny.

    • Zadanie 20

      Wstaw dwie nowe kolumny AB, AC, nazwij je BRUTTO, NETTO i umieść w nich formuły obliczające wypłatę brutto oraz netto w wysokości 80% brutto.

  • Topic 3

    Arkusz kalkulacyjny Excel - część 3

    • FORMUŁY PODSUMOWUJĄCE - KLASYCZNE

    • Zadanie 1

      W arkuszu Zestawienia, w zakresie komórek B2:B3 wprowadź formułę obliczającą liczbę pracowników poszczególnych płci według wzoru przedstawionego poniżej na podstawie danych z arkusza Dane.

    • Zadanie 2

      W arkuszu Zestawienia, w zakresie komórek C2:C3 wprowadź formułę sumującą roczną liczbę godzin przepracowanych przez pracowników poszczególnych płci według wzoru przedstawionego poniżej na podstawie danych z arkusza Dane.

    • Zadanie 3

      W arkuszu Zestawienia, w zakresie komórek D2:D3 wprowadź formułę sumującą roczną liczbę nadgodzin przepracowanych przez pracowników poszczególnych płci według wzoru przedstawionego poniżej na podstawie danych z arkusza Dane.

    • Zadanie 4

      W arkuszu Zestawienia, w zakresie komórek E2:E3 wprowadź formułę obliczającą średnią miesięczne pensje brutto zarabiane przez pracowników poszczególnych płci według wzoru przedstawionego poniżej na podstawie danych z arkusza Dane.

    • Zadanie 5

      W arkuszu Zestawienia, w zakresie komórek E2:E3 wprowadź formułę obliczającą średnią miesięczne pensje brutto zarabiane przez pracowników poszczególnych płci według wzoru przedstawionego poniżej na podstawie danych z arkusza Dane.

    • Zadanie 6

      W arkuszu Zestawienia, w zakresie komórek B4:D4 podsumuj formuły wprowadzone w kolumnach B, C i D.

    • Zadanie 7

      W arkuszu Zestawienia, w zakresie komórek E4:F4 oblicz średnie miesięczne pensje brutto i netto dla wszystkich pracowników.

    • Zadanie 8

      W arkuszu Zestawienia, w zakresie komórek A6:D14 wykonaj zestawienie według wzoru przedstawionego poniżej przy pomocy odpowiednich formuł.

    • Zadanie 9

      Po wykonaniu Zadania 8 otwórz nowy skoroszyt, przekopiuj do niego wykonane zestawienie, kopiując jedynie wartości i formaty oraz zapisz go w katalogu roboczym (w którym pracujesz) pod nazwą Stanowiska.

    • Zadanie 10

      W arkuszu Zestawienia, w zakresie komórek A6:D14 popraw formuły wykonane w Zadaniu 8, tak aby w każdej kolumnie była tylko jedna uniwersalna formuła.

    • Zadanie 11

      W arkuszu Zestawienia, w komórce E7 (zaznaczona żółtym tłem) wpisz formułę sumującą wszystkich pracowników (zakres komórek A7:A13) używając autosumowania, a następnie zidentyfikuj i usuń przyczynę nie wyświetlenia wartości sumy.

    • Zadanie 12

      W arkuszu Zestawienia, w zakresie komórek G7:L11 wykonaj zestawienie według wzoru przedstawionego poniżej przy pomocy odpowiednich formuł.

    • Zadanie 13

      W arkuszu Zestawienia, w zakresie komórek G7:L12 wprowadź uniwersalne formuły wykonujące obliczenia jak w Zadaniu 12 lub obliczające sumę albo średnią w wierszu 12 zawierającym podsumowania.

    • Zadanie 14

      Po wykonaniu Zadań 12 i 13 wstaw do skoroszytu nowy arkusz, nazwij go Katedry i przekopiuj do niego wartości i formaty z wykonanego zestawienia.

    • FORMUŁY PODSUMOWUJĄCE - WEKTOROWE

    • Zadanie 15

      W zakresie komórek O7:P12 oblicz średnie liczby godzin i średnie pensum według wzoru przedstawionego poniżej używając formuł tablicowych (wektorowych) zamiast funkcji ŚREDNIA.

    • Zadanie 16

      W zakresie komórek N15:P12 oblicz średnie liczby godzin i średnie pensum według wzoru przedstawionego poniżej analogicznie jak w Zadaniu 15 używając formuł tablicowych (wektorowych) zamiast funkcji ŚREDNIA i dodatkowo wykorzystując notację tablicową.

    • FORMUŁY PODSUMOWUJĄCE - BAZODANOWE

    • Zadanie 17

      W zakresie komórek G16:J20 wykonaj zestawienie dla wszystkich osób w bazie według wzoru przedstawionego poniżej przy pomocy odpowiednich formuł bazodanowych.

    • Zadanie 18

      W zakresie komórek G23:J27 wykonaj zestawienie dla wszystkich osób w bazie według wzoru przedstawionego poniżej przy pomocy odpowiednich formuł bazodanowych.

    • Zadanie 19

      Wstaw do skoroszytu nowy arkusz, nazwij go Filtr i przekopiuj do niego całą zawartość arkusza Dane.

    • Zadanie 20

      W zakresie komórek A22:A27 wykonaj zestawienie dla wszystkich osób w bazie według wzoru przedstawionego poniżej przy pomocy odpowiednich formuł bazodanowych.

  • Topic 4

    Arkusz kalkulacyjny Excel - część 4

  • Topic 5

    Arkusz kalkulacyjny Excel - część 5

    • FILTRY ZAAWANSOWANE

    • Uwagi ogólne

      Przed wykonaniem każdego zadania dotyczącego filtrów zaawansowanych (zadania 1 ? 12) wstaw nowy arkusz, zmień jego nazwę na FiltrX, gdzie X jest numerem kolejnego zadania, przekopiuj do niego zawartość arkusza Dane, a następnie rozwiąż w nim zadanie.

    • Zadanie 1

      Wybierz wiersze z danymi osób pracujących w katedrach A, B i E filtrując tabelę w miejscu.

    • Zadanie 2

      Wybierz wiersze z danymi osób pracujących w zakładach 1 i 3 katedry A, w katedrze C i zakładzie 9 katedry D filtrując tabelę w miejscu.

    • Zadanie 3

      Wybierz wiersze z danymi magistrów inżynierów i doktorów habilitowanych, którzy mają nazwisko kończące się na "cki", "cka" lub "ski", "ska" filtrując tabelę w miejscu.

    • Zadanie 4

      Wybierz wiersze z danymi osób pracujących w zakładach 2, 4 i 6 lub których imiona składają się z 3, 5 lub 7 liter używając filtrowania kaskadowego, czyli umieszczając tabelę wynikową pod tabelą źródłową.

    • Zadanie 5

      Wykonaj Zadanie 2 używając filtrowania kaskadowego. Następnie wybierz wiersze z danymi osób, w których imionach lub nazwiskach występują litery J lub M, używając tej samej techniki.

    • Zadanie 6

      Wykonaj Zadanie 1 używając filtrowania kaskadowego. Następnie wybierz wszystkie osoby, których imiona lub nazwiska składają się z 5 lub 6 liter, później wybierz wszystkich mężczyzn pełniących funkcję kierownika.

    • Zadanie 7

      Wybierz wiersze osób zarabiających brutto od 2.000 do 3.000 zł oraz od 4.000 do 5.000 zł filtrując tabelę w miejscu.

    • Zadanie 8

      Wybierz wiersze osób, które zarobiły miesięcznie mniej niż 100 zł, od 300 do 400 zł oraz więcej niż 500 zł włącznie z tytułu nadgodzin, za wyjątkiem osób, które nie miały nadgodzin, filtrując tabelę w miejscu.

    • Zadanie 9

      Wybierz wiersze osób w wieku od 25 do 35 lat lub od 45 do 55 lat, które przepracowały na studiach dziennych od 150 do 200 godzin lub od 250 do 300 godzin używając filtrowania kaskadowego.

    • Zadanie 10

      Wybierz wiersze wszystkich adiunktów, wykładowców i st. wykładowców, którzy przepracowali od 100 do 200 lub powyżej 350 godzin włącznie na studiach dziennych używając filtrowania w miejscu.

    • Zadanie 11

      Wybierz rekordy wszystkich doktorów i doktorów habilitowanych, którzy z tytułu wysługi lat otrzymują mniej niż 400 zł lub od 800 do 1200 zł włącznie oraz pracują w katedrach C, D, E używając filtrowania kaskadowego.

    • Zadanie 12

      Wybierz rekordy wszystkich asystentów, adiunktów i profesorów, których wypłata za nadgodziny wynosi od 150 do 300 zł lub jest wyższa niż 500 zł oraz zarabiają brutto od 2.000 do 4.000 zł lub od 6.000 do 10.000 zł używając filtrowania kaskadowego

    • KONSOLIDACJA DANYCH

    • Zadanie 13

      Przejrzyj zawartość arkuszy 2005, ..., 2009 i przygotuj je do konsolidacji danych według wzoru przedstawionego poniżej.

    • Uwagi ogólne

      Przed wykonaniem każdego zadania dotyczącego konsolidacji danych (zadania 14 - 16) wstaw nowy arkusz, zmień jego nazwę na ZestawienieX, gdzie X jest numerem kolejnego zadania, a następnie rozwiąż w nim zadanie.

    • Zadanie 14

      Wykonaj zestawienie sumarycznej liczby godzin w latach 2005 - 2009 przepracowanych przez pracowników zatrudnionych na poszczególnych stanowiskach według wzoru przedstawionego poniżej wykorzystując konsolidację danych.

    • Zadanie 15

      Wykonaj zestawienie średniej liczby pracowników zatrudnionych w latach 2005 - 2009 na poszczególnych stanowiskach oraz średniej liczby godzin i nadgodzin przepracowanych w tym okresie wykorzystując konsolidację danych z zachowaniem łącza do danych źródłowych.

    • Zadanie 16

      Wykonaj zestawienie średniej liczby pracowników zatrudnionych w latach 2005 - 2009 na poszczególnych stanowiskach oraz sumaryczne liczby godzin i nadgodzin przepracowanych w tym okresie wykorzystując funkcje 3D, które umożliwiają operowanie na zakresach arkuszy.

    • Zadanie 17

      Wstaw nowy arkusz, zmień jego nazwę na Scenariusz, a następnie w zakresie komórek A1:H6 wykonaj zestawienie zarobków miesięcznych pojedynczego pracownika według wzoru przedstawionego poniżej wykorzystując odpowiednie formuły wykorzystując dane zapisane w arkuszu Słowniki.

    • Zadanie 18

      Zbuduj scenariusze, które nazwij Osoba1, Osoba2 i Osoba3 określając komórki z zakresu B2:B6 jako komórki zmieniane nadając im wartości według wzorów przedstawionych poniżej.

    • Zadanie 19

      Wyświetl wyniki działania scenariuszy Osoba1, Osoba2 i Osoba3 zbudowanych w zadaniu 18.

    • Zadanie 20

      Wyświetl raport zbiorczy dla scenariuszy Osoba1, Osoba2 i Osoba3 określając komórki z zakresu G2:H2.

  • Topic 6

    Arkusz kalkulacyjny Excel - część 6

    • TABELE PRZESTAWNE

    • Uwagi ogólne

      Przed wykonaniem każdego zadania dotyczącego tabel i wykresów przestawnych (zadania 1 - 11) wstaw nowy arkusz, zmień jego nazwę na ZadanieX, gdzie X jest numerem kolejnego zadania, a następnie umieść w nim przygotowaną w zadaniu tabelę lub wykres przestawny.

    • Zadanie 1

      Utwórz tabelę przestawną, w której umieść zestawienie średnich pensji brutto w poszczególnych katedrach (jako wiersze) dla poszczególnych stanowisk (jako kolumny).

    • Zadanie 2

      Przekopiuj tabelę przestawną wykonaną w Zadaniu 1 do nowego arkusza. Zmodyfikuj ją umieszczając zamiast średnich pensji brutto, sumę pensji brutto na danym stanowisku, w danej katedrze wyświetlając wszystkie kwoty w formacie księgowym z dwoma miejscami po przecinku oraz sortując katedry malejąco według sumy pensji brutto wszystkich pracowników.

    • Zadanie 3

      Przekopiuj tabelę przestawną wykonaną w Zadaniu 2 do nowego arkusza. Zmodyfikuj ją wyświetlając katedry jako kolumny, a stanowiska jako wiersze oraz dodatkowo obliczając liczbę pracowników w poszczególnych kategoriach i sortując stanowiska malejąco według liczby pracowników zatrudnionych na danym stanowisku.

    • Zadanie 4

      Przekopiuj tabelę przestawną wykonaną w Zadaniu 3. Zmodyfikuj ją, dodając dane na temat sumy rocznego pensum oraz uwzględniając podział katedr na zakłady.

    • Zadanie 5

      Przekopiuj tabelę przestawną wykonaną w Zadaniu 4. Zmodyfikuj ją, usuwając dane na temat średniej pensji brutto, sumy rocznego pensum, a dodając dane na temat średniej liczby godzin przepracowanych na studiach dziennych i zaocznych, sumy wypłat za nadgodziny oraz uwzględniając dodatkowo podział pracowników ze względu na płeć. Nie wyświetlaj podsumowań dla całych wierszy i kolumn.

    • Zadanie 6

      Przekopiuj tabelę przestawną wykonaną w Zadaniu 5. Zmodyfikuj ją, w taki sposób, żeby wyświetlane były w niej jedynie dane na temat mężczyzn na stanowiskach adiunktów i profesorów nadzwyczajnych, pracujących w katedrach A i B, w zakładach o nieparzystych numerach.

    • Zadanie 7

      Przekopiuj tabelę przestawną wykonaną w Zadaniu 6. Zmodyfikuj ją, w taki sposób, żeby wyświetlane były procentowe udziały pracowników na konkretnych stanowiskach zatrudnionych w poszczególnych katedrach w stosunku do wszystkich pracowników zatrudnionych na danym stanowisku bez uwzględniania podziałów ze względu na płeć i zakłady. Wyświetl podsumowanie dla całych wierszy i kolumn.

    • Zadanie 8

      Wykonaj w jednym arkuszu tabele przestawne podsumowujące zarobki pracowników samodzielnych i niesamodzielnych według wzoru przedstawionego poniżej.

    • WYKRESY PRZESTAWNE

    • Zadanie 9

      Wykonaj wykres przestawny średnich pensji brutto na poszczególnych stanowiskach we wszystkich katedrach. Najpierw wykonaj odpowiednią tabelę przestawną.

    • Zadanie 10

      Przekopiuj tabelę i wykres przestawne wykonane w Zadaniu 9. Zmodyfikuj je według wzoru przedstawionego poniżej.

    • Zadanie 11

      Wykonaj wykres przestawny według wzoru przedstawionego poniżej zapisz go jako osobny arkusz.

    • SUMY POŚREDNIE

      Uwagi ogólne

      Przed wykonaniem każdego zadania dotyczącego sum pośrednich (zadania 12 ? 20) wstaw nowy arkusz, zmień jego nazwę na ZadX, gdzie X jest numerem kolejnego zadania, przekopiuj do niego zawartość arkusza Dane, a następnie rozwiązuj w nim zadanie.

    • Zadanie 12

      Przy użyciu sum częściowych wyświetl sumy godzin przepracowanych na studiach dziennych, zaocznych, pensum, poborów brutto i netto z podziałem na katedry. Poniżej danych wyświetl podsumowanie dla wszystkich kategorii.

    • Zadanie 13

      Przy użyciu sum częściowych wyświetl średnie liczby godzin przepracowanych na studiach dziennych, zaocznych, pensum, poborów brutto i netto z podziałem na katedry i zakłady. Poniżej danych wyświetl podsumowania dla wszystkich kategorii.

    • Zadanie 14

      Przy użyciu sum częściowych wyświetl ilość pracowników samodzielnych i niesamodzielnych z podziałem na katedry i zakłady. Wyświetl podsumowanie dla wszystkich kategorii.

    • Zadanie 15

      Przy użyciu sum częściowych wyświetl ilość pracowników z podziałem na katedry, zakłady i stanowiska oraz najwyższą wypłatę za nadgodziny i najniższe pobory netto w każdej z kategorii. Poniżej danych wyświetl podsumowanie dla wszystkich kategorii.

    • Zadanie 16

      Przy użyciu sum częściowych wyświetl średnie roczne liczby godzin na studiach dziennych, zaocznych i pobory netto z podziałem na płeć i stanowiska. Wyświetl podsumowanie dla wszystkich kategorii.

    • Zadanie 17

      Wykonaj polecenie odwrotne do Zadania 16, czyli przy użyciu sum częściowych wyświetl średnie roczne liczby godzin na studiach dziennych, zaocznych i pobory netto z podziałem na stanowiska i płeć. Wyświetl podsumowanie dla wszystkich kategorii.

    • Zadanie 18

      Przy użyciu sum częściowych wyświetl minimalną, maksymalną i średnią pensję netto dla pracowników funkcyjnych. Wyświetl podsumowanie dla wszystkich kategorii poniżej danych.

    • Zadanie 19

      Przy użyciu sum częściowych wyświetl minimalną, maksymalną i średnią liczbę godzin przepracowanych na studiach dziennych w poszczególnych katedrach i zakładach.

    • Zadanie 20

      Przy użyciu sum częściowych wyświetl liczbę pracowników z poszczególnymi tytułami oraz ich minimalną, maksymalną i średnią pensję brutto w poszczególnych zakładach.