1 klasa. Temat 21. Korzystanie z filtrów, tabele i wykresy przestawne

 

Krok 1. Filtrujemy dane zapisane w tabeli arkusza kalkulacyjnego

👉 W tabeli ocen uczniów chcemy wyświetlić nazwiska tych, którzy z fizyki i chemii otrzymali co najmniej piątki. 

👉 Ręczne wykonanie takiej operacji przy dużej liczbie uczniów byłoby bardzo pracochłonne. Jak to zrobić w arkuszu kalkulacyjnym?

⬇

👉 program Microsoft Excel ma funkcję, która ułatwi wykonanie tego typu zadania — filtry. Przy stosowaniu opcji filtrowania ważne jest, aby kolumny w tabeli miały nagłówki, np. nazwisko, imię, ulica, przedmiot, liczba sztuk.

Ćwiczenie 1. (1 p.) Filtrujemy dane zapisane w tabeli arkusza kalkulacyjnego

1) W arkuszu kalkulacyjnym utwórz tabelę przedstawioną na rysunku. 

Skopiuj dane z tabeli Uczniowie. Zapisz plik pod nazwą Uczniowie.

2) Oblicz średnie oceny uczniów. Dla danych z kolumny J ustal format Liczbowy z dwoma miejscami dziesiętnymi po przecinku.

3) Zaznacz dowolną komórkę tabeli na przykład B5. 

4) Na karcie Dane wybierz opcję filtrowania (Filtruj). Obok tytułów kolumn pojawia się przyciski ze strzałkami. 

5) Kliknięcie którejkolwiek ze strzałek powoduje rozwinięcie listy z możliwościami wyboru różnych kryteriów filtrowania oraz alfabetycznej listy wszystkich unikalnych wartości występujących w wybranej kolumnie tabeli.

Wybierając jedną z wartości, ograniczymy wyświetlanie do wierszy zawierających tę wartość w aktualnej kolumnie. Na przykład, jeżeli chcemy wyświetlić tylko nazwiska uczniów, którzy mają szóstkę z matematyki, z listy z ocenami z matematyki wybierajmy 6.

6) Skorzystamy z możliwości ustawiania niestandardowych filtrów (opcja Filtry liczb). Dla kolumn Fizyka i Chemia w filtrze niestandardowym należy ustalić warunek Większe niż lub równe 5. Po ustaleniu kryteriów wiersze, które ich nie spełniają, są ukrywane.

Nazwiska tych, którzy z fizyki otrzymali co najmniej piątki: 

⬇

Nazwiska tych, którzy z fizyki i chemii otrzymali co najmniej piątki. 

⬇

7) Aby ponownie wyświetlić wszystkie wiersze,  należy przy polach, dla których ustawiono filtry, zaznaczyć opcję wyświetlenia wszystkich wierszy lub opcję wyczyszczenia filtru.

8) Wyświetli nazwiska uczniów, którzy mają: 

→ z matematyki ocenę najwyższą 

→ średnią ocen większą lub równą 4 

→ średnią ocen większą od 4, a mniejszą od 5 

Uwaga: Przed wykonaniem każdego z kolejnych podpunktów wyświetl ponownie wszystkie wiersze. 

Nie zapisuj zmian.

Ćwiczenie 2. (1 p.) Filtrujemy dane zapisane w tabeli arkusza kalkulacyjnego
1) Otwórz plik Uczniowie, zapisany w Ćwiczeniu 1. Zastosuj filtr wyświetlający wiersze:
a) zawierające dane chłopców; zapisz plik pod nazwą Uczniowie_chłopcy, 
b) zawierające dane dziewcząt; zapisz plik pod nazwą Uczniowie_dziewczęta.
Wskazówka: Imiona dziewcząt podane w ćwiczeniu kończą się literą a. Z rozwijanej listy znajdującej się obok pola Imię należy wybrać odpowiednio filtrowanie niestandardowe i ustawić kryterium nie kończy się na a lub kończy się na a.

Krok 2. Tabele i wykresy przestawne

👉 Do bardziej kompleksowej analizy (zwłaszcza dużej liczby) danych, wykonywania różnych (często skomplikowanych) obliczeń i podsumowań oraz ich wyświetlania, możemy wykorzystać tabele przestawne. 

👉 Aby lepiej zrozumieć ideę tabel przestawnych, zaczniemy od prostego przykładu. Skorzystamy z tabeli przygotowanej w Ćwiczeniu 1. 

👉 Aby skorzystać z możliwości tabel przestawnych, musimy wcześniej odpowiednio przygotować tabelę arkusza kalkulacyjnego. Podobnie jak w przypadku filtrów, wszystkie kolumny muszą mieć nagłówki umieszczone w komórce nad kolumną danych. W przypadku tabel przestawnych nagłówki powinny mieć różne nazwy.

Ćwiczenie 3. (1 p.) Tworzenie tabeli przestawnej
Chcemy wiedzieć, ilu uczniów otrzymało poszczególne oceny (od dopuszczającej do celującej) z matematyki. 
1) Zaznacz tabelę lub zakres danych (łącznie z nagłówkami), które chcesz analizować. 
2) Na karcie Wstawianie wybierz opcję Tabela przestawna. 
3) W otwartym oknie sprawdź zakres danych w polu Zaznacz tabelę lub zakres, wybierz miejsce umieszczenia tabeli przestawnej (Nowy arkusz) i kliknij OK. 

4) W nowym arkuszu po prawej stronie pojawi się panel Pole tabeli przestawnej z listą pól tabeli, które wybraliśmy do analizy. 

Przeciągnij odpowiednio pola, w naszym przypadku: Matematyka — do obszaru Wiersze (Jeśli dane mają być umieszczone w wierszach) i Nazwisko — do obszaru Wartości.
5) Kliknij pole Nazwisko i wybierz z listy rozwijanej polecenie Ustawienia pola wartości, typ obliczeń Liczba. W tabeli pojawi się informacja o liczbie osób, które otrzymały poszczególne oceny.

6)  Zapisz plik pod nazwą Uczniowie_tab.

Ćwiczenie 4. (1 p.) Tworzymy wykres przestawny
Aby utworzyć wykres przestawny, należy kliknąć dowolną komórkę tabeli i na karcie Analiza tabeli przestawnej wybrać polecenie Wykres przestawny. 

1) Otwórz plik Uczniowie_tab zapisany w Ćwiczeniu 3.

2) Utwórz wykres przestawny (kolumnowy), przedstawiający liczbę uczniów, którzy otrzymali poszczególne oceny z matematyki. Zmień tytuł wykresu na "Liczba poszczególnych ocen z matematyki". Zapisz plik pod samą nazwą.

Ćwiczenie 5. (1 p.) Tworzymy tabelę i wykres przestawny

1) Otwórz plik Uczniowie_tab zapisany w Ćwiczeniu 4. 

2) W nowym arkuszu utwórz tabelę i wykres przestawny, przedstawiający liczbę uczniów, którzy otrzymali poszczególne oceny z języka polskiego. 

3) Zapisz plik pod tą samą nazwą.