grudzień 2016 - grupowanie danych

Tekst został napisany w oparciu o arkusz kalkulacyjny MS Excel 2013. Inne wersje Excela mogą się nieznacznie różnić od prezentowanej.

W tym pliku znajdują się informacje o liczbie dzieci i średnim dochodzie na osobę w rodzinach korzystających z pomocy pewnej fundacji. W oparciu o umiejętności zdobyte w październiku i listopadzie za pomocą tabel przestawnych bez trudu znajdziemy liczbę rodzin w bazie z daną liczbą dzieci oraz średni dochód w rodzinach z daną liczbą dzieci. W pierwszym wypadku w polu WIERSZE należy umieścić liczbę dzieci, a w polu WARTOŚCI cokolwiek, byle dokonać zliczania wierszy (a nie np. sumowania wartości).

W drugim przypadku w polu WARTOŚCI należy umieścić dochód i ustawić wykonywaną operację na obliczanie średniej.

Ponieważ rodziny z liczbą dzieci przekraczającą czwórkę zdarzają się rzadko, można się zastanowić, czy nie wprowadzić jednej kategorii pod względem liczby dzieci: pięcioro i więcej, a następnie dokonywać wszelkich podsumowań bez rozróżnienia rozróżnienia na rodziny z piątką, szóstką czy siódemką dzieci. Jak w łatwy sposób osiągnąć ten efekt w oparciu o tabelę przestawną?

Jeśli chcemy się dowiedzieć, ile jest rodzin z jednym, dwójką, trójką, czwórką oraz łącznie piątką i większą liczbą dzieci, musimy utworzyć tabelę przestawną służącą do określenia liczby rodzin z daną liczbą dzieci (jak to wyżej wspomniano). Następnie w tabeli przestawnej zaznaczamy te etykiety, które chcemy zgrupować - w naszym wypadku będą to: 5, 6 i 7, a następnie klikamy zaznaczony obszar prawym przyciskiem myszy i wybieramy opcję Grupuj. W tym momencie w tabeli przestawnej pojawi się dodatkowa pozycja w obszarze wierszy, która automatycznie otrzyma nazwę Liczba dzieci2. Jak widzimy, rodziny z wybraną liczbą dzieci zostały w niej połączone w jedną kategorię.

Jeśli chcemy się dowiedzieć, ile jest rodzin z piątką i większą liczbą dzieci, wystarczy zwinąć utworzone automatycznie pole Grupuj1, klikając ikonkę z minusem.

Jeśli chcemy cofnąć operację grupowania, wystarczy prawym przyciskiem myszy kliknąć na etykiecie Grupuj1 i wybrać opcję Rozgrupuj.

Etykieta Grupuj1 została utworzona automatycznie, ale nie jest ona ani zbyt ładna, ani zbyt użyteczna, bo nie wiadomo, co się za nią kryje. Możemy ją zmienić. Jesteśmy przyzwyczajeni, że aby wpisać coś do komórki arkusza kalkulacyjnego, dwukrotnie klikamy w tę komórkę prawym przyciskiem myszy. Zmiany etykiety w tabeli przestawnej nie możemy dokonać w ten sposób, lecz musimy podświetlić wybraną komórkę, a następnie kliknąć F2. Dopiero wówczas komórka otworzy się do edycji. (Warto wspomnieć, że każdą komórkę arkusza kalkulacyjnego tak właśnie można otwierać do edycji). Etykietę Grupuj1 możemy zamienić np. na 5 lub więcej.

Jeśli ponadto chcielibyśmy połączyć w jedną kategorię rodziny z jednym i z dwójką dzieci oraz z trójką i czwórką dzieci, musimy wykonać takie same operacje jak poprzednio: zaznaczyć odpowiednie etykiety, kliknąć w zaznaczony obszar prawy przyciskiem myszy i wybrać Grupuj. Nowym grupom również możemy nadać własne nazwy.

Może się zdarzyć, że po dokonaniu grupowania nie będą nas dłużej interesowały oryginalne kategorie, na jakie były podzielone dane, a jedynie utworzone przez nas grupy. W takiej sytuacji śmiało możemy usunąć z pola WIERSZE tabeli przestawnej pozycję Liczba dzieci (poprzez jej odznaczenie w prawym panelu) i pozostawić jedynie pozycję Liczba dzieci2, na podstawie której dane zostały pogrupowane.

Umieszczając w polu WARTOŚCI dochód, możemy łatwo obliczyć średni dochód w rodzinach z odpowiednio pogrupowaną liczbą dzieci.

Możemy sobie postawić jeszcze inne pytanie związane z grupowaniem danych: Ile jest rodzin w bazie, których dochód mieści się w określonym przedziale? Powiedzmy, że interesują nas przedziały: do 400 zł, między 400 i 600 zł oraz powyżej 600 zł. Aby udzielić odpowiedzi na to pytanie, musimy utworzyć tabelę przestawną, w której w polu WIERSZE umieścimy dochód, a w polu WARTOŚCI cokolwiek, byleby zliczać rodziny. Powstała w ten sposób tabela przestawna jest zapewne mało estetyczna i jeszcze niewiele mówi, ponieważ każdej kwocie prawdopodobnie przypada tylko jedna rodzina. Niemniej teraz zaznaczając odpowiednie kwoty, możemy je zgrupować, by uzyskać interesującą nas informację. Następnie możemy wprowadzić dla grup własne etykiety i ostatecznie z pola WIERSZE tabeli przestawnej pozycję Dochód, pozostawiając jedynie pozycję Dochód2, na podstawie której dokonuje się grupowanie.

Jeśli grupowania dokonujemy na podstawie kolumny, w której są liczby, i chcemy dokonać grupowania na przedziały równej długości (np. po 100 zł), możemy dokonać tego w inny, nieco wygodniejszy sposób. Tworzymy tabelę przestawną jak powyżej, a następnie bez zaznaczania czegokolwiek klikamy prawym przyciskiem myszy na dowolne pole z dochodem i wybieramy Grupuj. Pojawi się okno, w którym Początek i Koniec oznaczają odpowiednio lewy koniec pierwszego przedziału i prawy koniec ostatniego przedziału. Domyślnie jako Początek ustawiono najmniejszy dochód w tabeli - w naszym wypadku jest to 333,48 zł - ale możemy to zmienić. Jeśli ustawimy Początek na kwocie 300 zł, wszystkie rodziny zmieszczą się do wyznaczonych przez nas przedziałów, a pierwszy z nich będzie liczony od 300 zł. (Jeśli jako Początek ustawilibyśmy kwotę większą niż najmniejszy dochód w tabeli, zostanie otworzony jeszcze jeden przedział dla wszystkich wartości mniejszych od tej kwoty). Długość przedziału ustawiamy, wpisując 100 w pole Według. Po zatwierdzeniu operacji przyciskiem OK otrzymujemy przejrzyste podsumowanie wraz z dochodem pogrupowanym w przedziały. Tutaj domyślne etykiety są już bardziej przejrzyste.

Analogicznie do pola Początek przy ustawianiu lewego końca pierwszego przedziału możemy się również posłużyć polem Koniec dla ustawienia prawego końca ostatniego przedziału. Rodziny z dochodem przekraczającym to, co wpisano w pole Koniec zostaną połączone w jedną kategorię.

Jeśli w jednym pliku znajduje się kilka table przestawnych (niekoniecznie w jednym arkuszu) utworzonych na podstawie tego samego zbioru danych, mogą występować problemy z grupowaniem danych w nich zawartych. W takiej sytuacji sugerujemy skopiować do osobnego pliku dane i tam utworzyć kolejną tabelę przestawną, a następnie dokonać grupowania.

Na podstawie danych z arkusza kalkulacyjnego, którego dotyczą przykłady omówione w miniwykładzie, utwórz tabele przestawne, które pozwolą odpowiedzieć na następujące pytania:

Zad. 1. Ile jest rodzin w bazie w podziale na województwa, których nazwy rozpoczynają się na określoną literę?

Zad. 2. Ile jest rodzin z jednym lub dwójką dzieci, trójką lub czwórką dzieci oraz piątką lub większą liczbą dzieci w poszczególnych województwach?

Zad. 3. Ile jest w bazie rodzin, którym przysługuje zasiłek rodzinny w pełnej kwocie tzn. rodzin, w których dochód na osobę nie przekracza 674 zł?

Zad. 4. A ile jest takich rodzin jak w pyt. 3 z jednym dzieckiem, dwójką dzieci itd.?

Zad. 5. Jaki procent wszystkich rodzin w bazie jest uprawniony do pobierania zasiłku rodzinnego zgodnie z przedstawionym powyżej kryterium?

Zad. 6. Ile jest rodzin w bazie ze średnim dochodem w przedziałach 300-450 zł, 450-600 zł, 600-750 zł 750-900 zł i powyżej 900 zł?

Jako rozwiązanie prześlij odpowiedzi na powyższe pytania wraz z arkuszem kalkulacyjnym, w którym będą się znajdowały tabele przestawne, na podstawie których można udzielić odpowiedzi. Każda tabela przestawna powinna się znajdować w osobnej karcie, a karty powinny być podpisane numerami pytań. Jeśli pojawi się problem w związku z obecnością w jednym pliku kilku tabel przestawnych z pogrupowanymi danymi, możesz wysłać kilka plików, byleby wyraźnie było opisane, gdzie znajduje się odpowiedź na dane pytanie. Odpowiedź na każde pytanie (wraz z potwierdzającą ją tabelą przestawną) warta jest 0,5 punktu.