Tabele przestawne — przykłady

Na początku miesiąca na blogu przedstawiłem tabele przestawne, dość skomplikowaną i niezwykle przydatną funkcję arkusza kalkulacyjnego. Tamten artykuł zakończyłem listą zadań do samodzielnego wykonania. Dzisiaj przyjrzymy się ich rozwiązaniu.

Na samym początku chciałbym poczynić dwie uwagi.

Po pierwsze, podane rozwiązania mają charakter przykładowy, tzn. opisują tylko jeden sposób na otrzymanie poprawnego wyniku. W przypadku większości zadań do odpowiedzi można dojść również innymi drogami.

Po drugie, odradzam czytanie artykułu przed podjęciem próby samodzielnego znalezienia odpowiedzi na postawione pytania. Tekst należy raczej traktować jako pomoc w sytuacji „bez wyjścia” lub wzorzec, z którym można porównać własne rozwiązanie.

Pierwsze pięć pytań dotyczy pliku zarobki.ods, ostatnie trzy — nobel.ods.

W którym miesiącu najwięcej osób podpisuje umowę na czas nieokreślony?

Zadanie to nie różni się zasadniczo od jednego z zadań opisanych w poprzednim artykule. Tabelę przestawną projektujemy w taki sposób, aby w obszarze wierszy znalazła się data zatrudnienia, a w obszarze kolumn — rodzaj umowy. W obszarze danych umieszczamy dwukrotnie l.p.. W obu nakazujemy obliczyć liczbę, przy czym w drugim, w części Więcej, jako Typ definiujemy % przez wiersz.

W utworzonej tabeli umieszczamy kursor w pierwszej kolumnie i wybieramy z menu DaneKonspektGrupuj.... Z listy Odstępy wybieramy Miesiące (powinny być zaznaczone domyślnie) i zatwierdzamy zmiany. Jeśli chcemy, dodatkowo modyfikujemy układ tabeli w taki sposób, aby miary relatywne były obok liczb absolutnych, a nie pod nimi.

Posługując się liczbami bezwzględnymi, najwięcej osób podpisuje umowę na czas nieokreślony w sierpniu (15). Biorąc poprawkę na ogólną liczbę osób zatrudnionych w danym miesiącu — w marcu (47,37%).

Ile kobiet zatrudniono w 2005 roku w dziale obsługi klienta?

W porównaniu z poprzednim zadaniem, układ tabeli nie zmienia się znacząco. W obszarze wierszy pozostawiamy datę zatrudnienia, zaś w obszarze danych — l.p. (liczba). płećdział możemy przenieść na obszar strony.

Ponownie umieszczamy kursor w pierwszej kolumnie i wybieramy z menu DaneKonspektGrupuj.... Tym razem jednak z listy na dole okna wybieramy wyłącznie Lata.

Następnie klikamy ikony obok szarych komórek nad tabelą przestawną i w nowych okienkach wybieramy kolejno Kobieta oraz Obsługa klienta.

Po wykonaniu tych czynności możemy odczytać, że w 2005 roku w dziale obsługi klienta zatrudniono trzy kobiety.

Na jakich stanowiskach pracuje najwięcej osób zatrudnionych na umowę zlecenie?

To zadanie, w gruncie rzeczy, stanowi pewną modyfikację pierwszego z pytań.

Projektując tabelę, w obszarze wierszy umieszczamy stanowisko, w obszarze kolumn rodzaj umowy, zaś w obszarze danych — dwukrotnie l.p.. W obu przypadkach liczymy liczbę wystąpień, ale w drugim z nich z listy Typ wybieramy % przez wiersz.

Po utworzeniu tabeli widzimy, że — w liczbach bezwzględnych — najwięcej osób zatrudnionych na umowę zlecenie pracuje na stanowisku specjalisty (34). Biorąc jednak pod uwagę, że liczba pracowników na każdym ze stanowisk jest odmienna, na prowadzenie wysuwają się młodsi specjaliści (32,69%).

W którym dziale odsetek osób zatrudnionych na umowę na czas określony jest najwyższy?

Jest to kolejne zadanie utrzymane w schemacie zbliżonym do wcześniejszego, dlatego jego wykonanie nie powinno nikomu sprawić szczególnych kłopotów.

W obszarze wierszy umieszczamy dział a w obszarze kolumn — rodzaj umowy. W obszarze danych pozostawiamy jedno pole l.p.. W jego ustawieniach wybieramy liczbę, a jako typ określamy % przez wiersz.

Z powstałej tabeli z łatwością odczytamy, iż najwyższy odsetek osób zatrudnionych na umowę na czas określony pracuje w dziale marketingu (stanowią aż 58,33% jego pracowników).

Jaką część całości wydatków na pensje pracowników stanowią zarobki koordynatorów w zarządzie?

Ostatnie zadanie wymaga odrobinę odmiennego podejścia niż wcześniejsze.

Projektując tabelę, w obszarze wierszy powinniśmy umieścić dział, zaś w obszarze danych — płaca. W tym wypadku nakazujemy obliczyć sumę, zaś w listy Typ wybieramy % przez całość.

Formalnie powinniśmy jeszcze w obszarze kolumn umieścić stanowisko. My jednak wiemy, że w zarządzie pracując wyłącznie koordynatorzy, dlatego możemy pominąć tę czynność.

Po utworzeniu tabeli widzimy, że zarobki zarządu pochłaniają prawie jedną piątą całości wydatków na pensje pracowników.

Ile nagród przyznano w każdej z dziedzin? W którym roku przyznano pierwszą nagrodę w dziedzinie, w której liczba nagród jest najmniejsza?

Ostatnie trzy zadania dotyczą arkusza zawierającego listę laureatów Nagrody Nobla.

Pierwsze jest dość proste. W obszarze wierszy umieszczamy dziedzina, zaś w obszarze danych — id (liczba).

Utworzona tabela wyraźnie wskazuje, że najmniej nagród przyznano za osiągnięcia w ekonomii.

Teraz klikamy dwukrotnie lewym przyciskiem myszy na etykietę wiersza (komórkę A5) i z nowego okna wybieramy rok. Widzimy teraz, że pierwszą nagrodę w tej dyscyplinie przyznano w 1969 roku.

Ile lat mieli fizycy w roku otrzymania nagrody Nobla? Podaj średnią oraz stwórz wykres dla pięcioletnich przedziałów.

Odpowiedź na to pytanie wymaga utworzenia w zbiorze danych nowej kolumny. Będzie ona zawierała wiek laureata w roku otrzymania wyróżnienia.

Informację tę możemy uzyskać za pomocą formuły:

=A2-TEKST(G2;"YYYY")

Funkcja TEKST zamieni datę podaną jako pierwszy argument na tekst określony za pomocą symbolu określonego jako drugi argument. "YYYY" to rok wyrażony czterema cyframi, zaś pełny wykaz symboli znajduje się w pomocy programu.

Oczywiście musimy ją skopiować do wszystkich pozostałych wierszy w bazie.

Z nie do końca jasnego powodu, podczas wyboru danych do tabeli przestawnej arkusz często nie uwzględnia nowej kolumny. W takim przypadku w głównym oknie projektowania jej układu należy kliknąć Więcej i zmienić zakres źródłowy w pierwszym polu.

Przycisk reprezentujący dodaną przez nas kolumnę przenosimy na obszar wierszy. W obszarze danych umieszczamy dwa przyciski — dowolny, w którego ustawieniach wybieramy Liczba; liczbę lat, w ustawieniach której wybieramy Średnia.

Po utworzeniu tabeli otwieramy okno filtru, gdzie określamy jedno kryterium: dziedzina równa się fizyka.

Teraz umieszczamy kursor w pierwszej kolumnie tabeli i wybieramy z menu DaneKonspektGrupuj.... W ostatnim polu okna, które się pojawi, wstawiamy liczbę 5 (określa ona szerokość przedziałów, które zostaną utworzone).

W ten sposób powstanie tabela, którą z łatwością możemy wykorzystać do utworzenia wykresu. Z jej ostatniego wiersza możemy odczytać, iż fizycy w roku otrzymania Nagrody Nobla mieli ukończone średnio 54 lata.

Kto może pochwalić się wieloma nagrodami Nobla?

Ostatnie zadanie nie jest specjalnie trudne, o ile wszystkie działania wykonujemy w odpowiedniej kolejności.

Zacznijmy od projektu tabeli.

Najpierw umieszczamy dowolny, ale inny niż nazwisko, przycisk na obszarze danych. W jego ustawieniach wybieramy liczba.

Następnie przenosimy przycisk id na obszar wierszy. Otwieramy okno jego ustawień, gdzie klikamy Opcje. W kolejnym oknie wybieramy sortowanie malejąco zgodnie z ostatnim elementem listy rozwijanej (działaniem wykonywanym na przycisku umieszczonym w obszarze danych).

Powstała tabela podaje nam identyfikatory wszystkich laureatów uszeregowane w kolejności malejącej według liczby otrzymanych wyróżnień. Ponieważ zostały one określone arbitralnie, musimy je przekształcić na informacje zrozumiałe dla człowieka — np. imię i nazwisko. Pomoże nam w tym funkcja WYSZUKAJ.PIONOWO.

Działanie funkcji WYSZUKAJ.PIONOWO omawiałem w artykułach omawiających tworzenie minidziennika ucznia oraz arkusza zarobki.ods.

Gotowa formuła, zwracająca imię laureata, wygląda następująco. Umieszczamy ją w komórce D4.

=WYSZUKAJ.PIONOWO($A4;$laureaci.$D$2:$F$864;2;0)

Obok, w komórce D5, wklejamy formułę zwracającą nazwisko. Wygląda ona praktycznie identycznie jak ta powyższa, z tą różnicą, że jako trzeci argument podajemy liczbę 3.

Następnie kopiujemy obie formuły do pozostałych wierszy, przynajmniej do dziewiątego wiersza arkusza.

Jednocześnie widzimy, dlaczego w obszarze danych nie mogliśmy umieścić przycisku nazwisko — więcej niż jedną nagrodą zostały wyróżnione dwie instytucje (Międzynarodowy Komitet Czerwonego Krzyża oraz Wysoki Komisarz Narodów Zjednoczonych do spraw Uchodźców), którym w arkuszu źródłowym przypisano tylko imię.

Podobał Ci się ten artykuł? Zapisz się na listę subskrybentów i otrzymuj informacje o następnych

Komentarzy: 1

  • leszek napisał(a):

    Niestety ale tabele przestawne w LO to udręka. Wystarczy dokonać jednej zmiany w danych i całe nasze misterne formatowanie idzie w …