Sumy częściowe

Sumy częściowe pozwalają dzielić dane na podgrupy i obliczać wybrane statystyki dla każdej z nich. Artykuł zawiera wszystko, co musisz wiedzieć, aby zacząć z nich korzystać.

Wstęp

Oczywiście nigdzie nie zajdziemy bez zbioru danych. W artykule wykorzystam bazę pracowników fikcyjnej korporacji — polecam ją pobrać i samodzielnie wykonywać opisane poniżej ćwiczenia.

Działaniu tytułowej funkcji, jej konfiguracji oraz ograniczeniom przyjrzymy się poszukując odpowiedzi na cztery pytania:

  1. Ilu ludzi pracuje w każdym z działów?
  2. W którym dziale bardziej opłaca się być specjalistą — marketingu czy księgowości?
  3. Jak wygląda wykres zarobków w dziale marketingu, z podziałem na stanowiska?
  4. Jakie są względne różnice w zarobkach na każdym stanowisku w każdym z działów?

Tworzenie sum częściowych

W pierwszej kolejności umieszczamy kursor gdziekolwiek w obrębie naszego zbioru danych i wybieramy z menu DaneSumy częściowe....

Pojawi się nowe okno. Za pomocą listy rozwijanej w jego górnej części wybieramy kryterium grupowania. Do jednej grupy zaliczane są wszystkie wiersze, które w danej kolumnie mają identyczną wartość. Wyodrębnionych zostanie tyle grup, ile odmiennych wartości znajduje się w tej kolumnie.

Poniżej wybieramy zmienne i rodzaj obliczenia. Działanie określone po prawej stronie zostanie wykonane na wartościach z kolumny zaznaczonej po lewej, osobno dla każdej z wyróżnionych grup. Każdej zmiennej możemy przypisać inną operację, nie możemy jednak wykonać dwóch różnych obliczeń na jednej kolumnie.

Ponieważ nas interesuje liczba pracowników w każdym dziale, z listy rozwijanej wybieramy dział, po lewej stronie zaznaczamy dowolną wartość, a po prawej — Liczba. Klikamy OK.

Poza „drzewem”, które pojawiło się przy lewym brzegu okna (przyjrzymy mu się później), nasz arkusz nie zmienił się. Dopiero gdy przesuniemy kursor niżej, zobaczymy, że Calc wstawił nowe, wyróżnione, wiersze. Każdy z nich zawiera etykietę składającą się z nazwy grupy i wykonanego obliczenia oraz jego wyniku.

Aby udzielić odpowiedzi na nasze pytanie, należy przejrzeć arkusz w poszukiwaniu dodatkowych wierszy i odczytać podane tam liczby.

Wiersz podsumowania automatycznie wstawiony przez arkusz

Gdy zechcemy przywrócić arkusz do stanu pierwotnego, powinniśmy wybrać z menu DaneSumy częściowe... i kliknąć przycisk Usuń w prawym dolnym rogu.

Ukrywanie zbędnych wyników

Odpowiedź na drugie pytanie możemy poznać porównując średnie zarobki osób na stanowisku „specjalisty” w działach marketingu i księgowości. Aby było to możliwe, musimy utworzyć nowy zestaw reguł sum częściowych.

Ponownie wybieramy z menu DaneSumy częściowe.... W nowym oknie z listy rozwijanej wybieramy dział i zaznaczamy dowolne pozycje na listach poniżej. Następnie przechodzimy na drugą kartę, gdzie z listy rozwijanej wybieramy stanowisko. Po lewej stronie zaznaczamy płaca, po prawej zaś średnią.

LibreOffice podzieli wszystkich pracowników na poszczególne działy (tak jak w poprzednim przykładzie), a następnie wyodrębni różne stanowiska występujące w obrębie każdego z nich. Dla każdej z tych mniejszych grup obliczy średnią płacę.

Stanie się tak, ponieważ nasze reguły grupowania mają charakter dwupoziomowy. Mówiąc bardziej ogólnie, cały zbiór danych zostaje podzielony zgodnie z wartościami w pierwszej kolumnie. Następnie każda z tych grup zostaje dalej rozdzielona, zgodnie z wartościami w drugiej kolumnie. Ponieważ Calc obsługuje trzy poziomy grupowania, każda z tych podgrup może zostać jeszcze rozdrobniona (na podpodgrupy).

Zgodnie z oczekiwaniami, arkusz wykonał obliczenia i przedstawił ich wyniki w nowych wierszach. Niestety, pomiędzy nimi znajdują się normalne dane, co znacznie utrudnia porównywanie grup.

W rozwiązaniu tego problemu pomoże nam wspomniane wcześniej „drzewo” (przy lewym brzegu okna). Wpierw klikamy ikonkę 2 w jego górnej partii, dzięki czemu ukryjemy wszystko oprócz wierszy podsumowania pierwszego poziomu grupowania (działów). Następnie wybieramy drugi plusik od góry — spowoduje to wyświetlenie średnich dla poszczególnych stanowisk w ramach działu księgowości (lub, mówiąc bardziej technicznie: wierszy podsumowania drugiego poziomu grupowania dla jednej z grup). Po kliknięciu plusika poniżej ukażą nam się średnie zarobki w dziale marketingu.

Tym samym uzyskaliśmy wszystkie informacje niezbędne do udzielenia odpowiedzi na drugie pytanie. Wyraźnie widać, że specjaliści od marketingu mogą liczyć na tysiąc więcej niż specjaliści od księgowości.

Grupowanie dwupoziomowe: Wynik po ukryciu wierszy z danymi

Warto przy okazji zauważyć, że najpierw pojawiają się nazwy stanowisk, a dopiero później nazwy działów; aby poznać średnie zarobki specjalistów w dziale marketingu, musimy odczytać wiersz „specjaliści” nad wierszem „marketing”. Dzieje się tak, ponieważ Calc wstawia dodatkowe wiersze od najniższego do najwyższego poziomu grupowania. Chociaż stoi za tym pewna logika, jest ona raczej nieintuicyjna.

Kopiowanie danych, tworzenie wykresów

Trzecie zadanie polega na stworzeniu wykresu dla zarobków w dziale marketingu. Ponieważ do jego wykonania nie musimy zmieniać ustawień sum częściowych, możemy od razu przystąpić do pracy.

Najprostszym rozwiązaniem jest zaznaczenie odpowiednich wierszy i wybranie z menu WstawWykres. Ponieważ sumy częściowe uszeregowały podgrupy w kolejności alfabetycznej, nasz wykres będzie zaczynał się od średniej koordynatorów. Warto to zmienić.

Wykres zarobków w dziale marketingu. Zaburzenie porządku zwiększania się słupków wpływa negatywnie na estetykę i zmniejsza czytelność

Niestety, Calc nie umożliwia uszeregowania grup według wyników dokonanego obliczenia. Pozostaje więc skopiowanie dodatkowych wierszy i odpowiednia modyfikacja kopii.

Jednak po wklejeniu zawartości schowka okaże się, że skopiowaliśmy również niewidoczne wiersze z danymi przyporządkowanymi do poszczególnych podgrup.

Możemy temu zapobiec zaznaczając komórki z wciśniętym klawiszem Ctrl. Przytrzymując go, wciskamy lewy przycisk myszy nad komórką z etykietą wiersza (F116); cały czas trzymając przycisk myszy przeciągamy kursor do komórki z wartością (H116). Operację powtarzamy dla kolejnych wierszy.

Jeżeli wszystko zrobiliśmy poprawnie, po skopiowaniu danych powinniśmy zauważyć migającą ramkę wewnątrz zakresu.

Teraz możemy spokojnie wkleić dane w innym miejscu arkusza, dokonać niezbędnych poprawek i utworzyć wykres.

Więcej niż trzy obliczenia

Ostatnie zadanie polega na obliczeniu względnej różnicy w zarobkach w każdej z podgrup. Możemy tego dokonać odnosząc najmniejszą płacę do najwyższej. Oznacza to, że musimy dokonać dwóch różnych obliczeń na jednej kolumnie.

Ponownie wybieramy z menu DaneSumy częściowe. Na pierwszej karcie ustalamy grupowanie według działu. Na drugiej karcie określamy grupowanie według stanowiska i zaznaczamy pole płaca oraz działanie Min. W trzeciej karcie również wybieramy z list stanowiskopłaca; jednak tym razem obliczeniem jest Maks. Zatwierdzamy zmiany.

Poznanie interesującej nas informacji to już tylko kwestia skonstruowania odpowiedniej formuły i czytelna prezentacja wyników.

Zmodyfikujmy odrobinę nasze pytanie. Przypuśćmy, że w firmie panuje wyraźna dysproporcja w zarobkach pomiędzy płciami. W świetle tej informacji powinniśmy porównywać nie tyle najmniejszą i najwyższą płacę specjalistów w dziale obsługi klienta, co najmniejszą i największą płacę kobiet na tym stanowisku w tym dziale; do tego, osobno, różnice w płacach mężczyzn.

Bez problemu wydzielimy odpowiednie grupy. Pierwszym kryterium wyodrębniania będzie dział, drugim stanowisko a trzecim płeć. Kłopot w tym, że wykorzystamy wszystkie dostępne poziomy grupowania i nie będziemy mogli skorzystać z opisanego wyżej sposobu na wykonanie dwóch obliczeń w każdej z podgrup.

Ograniczenie to możemy obejść przy odrobinie pomysłowości. Po umieszczeniu kursora w dowolnej komórce podsumowującej podpodgrupę zauważymy, że jej wartość nie jest wpisana na stałe, a obliczana przy pomocy formuły. Wykorzystana została znana nam już funkcja SUMY.CZĘŚCIOWE. Nic nie stoi na przeszkodzie, aby wprowadzić jej zmodyfikowaną wersję w dowolnej komórce arkusza.

Wpierw kopiujemy komórki z ostatniej kolumny (H) do kolumny obok niej (I). Zaznaczamy ją i wybieramy z menu EdycjaZnajdź i zamień.... Pojawi się nowe okno. W polu Szukaj wpisujemy „4;” (ze średnikiem), a w polu Zamień na — „5;”. Koniecznie odznaczamy Całe komórki i zaznaczamy Tylko bieżące zaznaczenie (w części Więcej opcji). Klikamy Zamień wszyst..

Jedna kolumna zawiera najwyższą, a druga najniższą płacę w każdej z podgrup. Teraz pozostało już tylko odnieść jedną liczbę do drugiej. Ja wykorzystam do tego kolumnę J. Wprowadzę w niej formułę, której wynik można interpretować jako „ile razy płaca najwyższa jest większa od najniższej?”:

=H9/I9

Po przygotowaniu danych należy je jeszcze zinterpretować, ale tym nie będziemy się już zajmować.

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

komentarzy 5

  • zibi5050 napisał(a):

    Plik wykorzystany do ćwiczeń jest niedostępny. Błąd 404 – Strona nie została znaleziona.

  • MJ napisał(a):

    http://przepis-na-lo.pl/plik/zarobki.ods => Błąd 404 – Strona nie została znaleziona :/

    • Mirosław Zalewski napisał(a):

      zibi5050, MJ: Literówka z mojej strony. Katalog nazywa się „pliki” (w liczbie mnogiej), ja podałem odnośnik do „plik” (w liczbie pojedynczej). Błąd już poprawiłem i plik jest dostępny.

  • Johng644 napisał(a):

    I dugg some of you post as I cogitated they were very useful extremely helpful cgkkfdacfeek

  • raaf napisał(a):

    można sobie łatwo poradzić z kopiowaniem wyników sumy częściowej, wystarczy zapisać wynik w pliku html, potem go otworzyć w przeglądarce i skopiować zawartość do nowego arkusza 🙂 działa doskonale