Sumy częściowe Excel

Narzędzie sum częściowej przydaje się w Excelu do podsumowania danych w Twojej tabeli. Jeśli masz w niej pewne grupy informacji, to możesz policzyć pewne wartości dla każdej z tych grup z osobna.

SPIS TREŚCI

Kiedy sumy częściowe mogą się w Excelu przydać?

Wyobraź sobie, że masz tabelę z pracownikami firmy. Pracują oni w różnych działach. Możesz policzyć np. ilu jest pracowników w każdym z działów, ile średnio zarabiają w każdym z działów, czy ile w sumie zarabiają w każdym dziale. Narzędzie przydaje się więc wtedy, gdy możesz w tabeli wyróżnić pewne grupy danych – w przykładzie, tymi grupami są działy w firmie.

BUTTON

Rysunek 1. Przykładowa tabela w Excelu, w której można użyć sum częściowych..

Efekt użycia sumy częściowej

Jakich zmian możemy się spodziewać w naszym pliku Excela po zastosowaniu sumy częściowej? Oto ich lista:

• • Wstawienie do tabeli dodatkowych wierszy, w których znajduje się podsumowanie każdej z grup.
  • Wstawienie wiersza podsumowującego całą tabelę.
  • Wstawienie grupowań wierszy, według odpowiednich grup danych, dzięki czemu, możesz wygodnie chować i odkrywać szczegółowe informacje w tabeli (podobnie jak w narzędziu grupowania).

BUTTON

Rysunek 2. Efekty użycia sum częściowych w Excelu.

Sposób działania sum częściowych w Excelu

W tej sekcji znajdziesz informacje, potrzebne do zrozumienia sum częściowych, użytych w tabeli Excela.

Kolejne kroki do wykonania

Aby wstawić sumę częściową należy:

• • Najpierw tabelę posortować według tej kolumny, według której dane będą grupowane. Nie ma znaczenia, czy sortowanie będzie rosnące czy malejące – ważne jest aby te same nazwy (kategorie) znajdowały się obok siebie i tworzyły w ten sposób grupę.
  • Zaznaczyć teraz całą tabelę.
  • Przejść na wstążce do zakładki Dane i nacisnąć Suma częściowa.
  • W polu Dla każdej zmiany w wybrać kolumnę, według której sortowano przed chwilą dane, czyli według której chcesz dane zgrupować.
  • Wybrać teraz, jakiej funkcji chcesz użyć do podsumowania danych – nie musi to być zawsze SUMA.
  • Wybrać, w jakiej kolumnie chcesz wstawić podsumowanie – jeśli chcesz policzyć sumę wynagrodzeń, to wybierz właśnie tę kolumnę. Zazwyczaj wybiera się kolumnę z liczbami, w których można policzyć np. sumę albo średnią.
  • Nacisnąć OK. W tym momencie:
    • Zostały dodane dodatkowe wiersze, podsumowujące każdą z grup, we wskazanej kolumnie.
    • Po lewej stronie, pojawiły się przyciski grupowania, które pozwalają szybko chować i odkrywać wybrane grupy.

BUTTON

Rysunek 3. Okno, za pomocą którego, wstawia się w Excelu sumę częściową.

Na jakiej zasadzie grupowane są dane

Bardzo istotne jest posortowanie najpierw tabeli Excela według kluczowej dla Ciebie kolumny. Gdy otwierasz okno sum częściowych, w polu Dla każdej zmiany w wskazujesz właśnie tę kolumnę. Narzędzie sumy częściowej analizuje później tę kolumnę – idzie z góry na dół i za każdym razem gdy zobaczy nową wartość, to uznaje, że kończy się pewna grupa i Excel wstawia wiersz podsumowujący. Jeśli dane nie byłyby posortowane, to co chwilę inne wartości byłyby w tej kluczowej kolumnie i co chwilę wstawiane były wiersze podsumowujące.

BUTTON

Rysunek 4. Efekt zastosowania sumy częściowej bez wcześniejszego posortowania tabeli.

Co jest podsumowywane?

Podsumowane są wartości z tej kolumny, którą wskażesz w polu Dodaj sumę częściową do. Możesz więc wybrać wiele kolumn jednocześnie. Pojawią się wówczas w tych kolumnach formuły – każda formuła podsumowuje jedną grupę.

Zagnieżdżanie sumy częściowej w Excelu

Może zdarzyć się sytuacja, gdy chcesz zgrupować dane według jednej kolumny, a następnie, w ramach pierwszego grupowania, pogrupować dane jeszcze bardziej szczegółowo, według kolejnej kolumny.

Przykład: chcesz policzyć średnie wynagrodzenie pracowników pewnej firmy w każdej lokalizacji (mieście), w którym firma ma biuro. Dodatkowo, chcesz policzyć średnie wynagrodzenie dla każdego działu, w każdym z takich miast. Masz więc kluczową kolumnę „Lokalizacja” oraz „Dział”, z czego „Lokalizacja” jest nadrzędna, więc od niej trzeba rozpocząć tworzenie sumy częściowej.

BUTTON

Rysunek 5. Tabela z oddziałami i działami firmy.

Początek – czyli sortowanie tabeli Excela

Zawsze trzeba rozpocząć od prawidłowego posortowania. W opisanym przykładzie, sortowania wymagają dwie kolumny. Jeśli zwyczajnie posortujesz (przyciskami na wstążce lub autofiltrem) najpierw kolumnę nadrzędną, czyli lokalizację, a następnie kolumnę dział, to okazuje się, że przy sortowaniu działu, kolejność lokalizacji jest wymieszana. Jeśli potrzebujesz posortować więcej niż jedną kolumnę to wykonaj jedną z dwóch rzeczy:

• • Sortuj od końca, czyli od kolumny najbardziej „podrzędnej”. W tym przykładzie zacznij od działu, a dopiero później posortuj lokalizację.
  • Otwórz okno sortowania – przejdź na wstążce do zakładki Dane i naciśnij Sortuj. W skrócie, na pierwszej liście wybierz lokalizację, na drugiej liście pozostaw wartości, a na trzeciej wybierz dowolną kolejność. Naciśnij przycisk Dodaj poziom. Drugi poziom ma dotyczyć kolumny Dział – wypełnij go analogicznie. Naciśnij OK.

Teraz kolumna z lokalizacja jest posortowana, a w ramach niej, posortowane są także działy. Możesz teraz przejść do narzędzia sum częściowych – czytaj kolejne akapity.

Wstawianie sum częściowych

Gdy dane są już posortowane, to możesz wstawić sumę częściową. Musisz każdą z sum częściowych wstawić z osobna, czyli schemat wygląda tak:

• • Wstawiasz sumę częściową dla najbardziej nadrzędnej kolumny, czyli dla lokalizacji – jest to bardzo ważne, bo jeśli zmienisz kolejność, to Excel będzie nieprawidłowo wstawiać wiersze z sumami.
  • Przechodzisz ponownie do narzędzia sum częściowych, wybierasz kolejną kolumnę, czyli dział. Jeśli chcesz, to możesz także wybrać inną funkcję niż przy pierwszej kolumnie (np. SUMA, ŚREDNIA, LICZNIK). Pamiętaj o odznaczeniu opcji Zamień bieżące sumy częściowe.
  • Naciśnij OK i ciesz się podwójną sumą częściową, jedną zagnieżdżoną w drugiej.

Rysunek 6. Opcja „Zamień bieżące sumy częściowe” powinna być wyłączona.

Możesz w ten sposób zagnieżdżać dowolnie wiele razy.

Usuwanie sum częściowych

Nie usuwaj sum częściowych ręcznie – czyli nie usuwaj ręcznie wierszy z podsumowaniami i grupowań znajdujących się po lewej stronie arkusza. Wystarczy, że zaznaczysz swoją tabelę, przejdziesz do okna sum częściowych i naciśniesz Usuń wszystko. Wszystkie wstawione wiersze oraz przyciski grupowania znikną. Dane pozostaną posortowane, bo to nie było de facto częścią sum częściowych.

Jeśli zaczniesz ręcznie usuwać lub dodawać jakieś wiersze do tabeli z sumami częściowymi, to możliwe jest, że naciśnięcie później Usuń wszystko wcale nie usunie wszystkiego co chcesz, bo wcześniej struktura narzędzia została przez te ręczne usuwanie/dodawanie zmieniona.

Rysunek 7. Przycisk „Usuń wszystko”.

Funkcje podsumowania sumy częściowej

Tworząc sumy częściowe, możesz użyć jednej z jedenastu funkcji Excela: Suma, Licznik, Średnia, Maksimum, Minimum, Iloczyn, Licznik num., OdchStd, OdchStdc, Wariancja, Wariancja populacji. Każda z nich przedstawi inny wynik, który zostanie umieszczony w wierszach podsumowujących dane. Funkcje zostaną wstawione jedynie w kolumnach wskazanych w polu Dodaj sumę częściową do:

Funkcje wykonują następujące obliczenia:

1. Suma – oblicza zwykłą sumę.
2. Licznik – oblicza ile jakichkolwiek wartości znajduje się w komórkach. Innymi słowy ile jest niepustych komórek.
3. Średnia – oblicza zwykłą średnią arytmetyczną.
4. Maksimum – wyszukuje największą wartość liczbową i wyświetla ją. Działa również w przypadku dat – prezentuje wówczas najpóźniejszą datę.
5. Minimum – wyszukuje najmniejszą wartość liczbową i wyświetla ją. Działa również w przypadku dat – prezentuje wówczas najwcześniejszą datę.
6. Iloczyn – oblicza iloczyn wszystkich wartości liczbowych.
7. Licznik num. – liczy ile jest komórek zawierających liczby. Liczy również komórki z datami.
8. OdchStd – liczy współczynnik statystyczny – odchylenie standardowe. Funkcja używana w przypadku obliczeń na próbce populacji.
9. OdchStdc – liczy współczynnik statystyczny – odchylenie standardowe. Funkcja używana w przypadku obliczeń na kompletnej populacji.
10. Wariancja – liczy współczynnik statystyczny – wariancje. Funkcja używana w przypadku obliczeń na próbce populacji.
11. Wariancja populacji – liczy współczynnik statystyczny – wariancje. Funkcja używana w przypadku obliczeń na próbce populacji.

Nie możesz użyć innych funkcji, niż te, które dostępne są na liście.

Rysunek 8. Lista funkcji sumy częściowej.

Czym jest funkcja SUMY.CZĘŚCIOWE, która pojawia się w komórkach?

Te jedenaście funkcji, które są dostępne w polu Użyj funkcji, nie pojawiają się wprost w wierszach podsumowujących – pojawia się tam funkcja SUMY.CZĘŚCIOWE. Jest ona „uniwersalną” funkcją, która potrafi policzyć te wszystkie 11 rzeczy. Nie martw się więc, że np. zamiast SUMY pojawiła się „jakaś dziwna funkcja” – liczy ona dokładnie tak, jak można by się spodziewać.

Rysunek 9. Funkcja SUMY.CZĘŚCIOWE w komórce Excela.

Funkcja SUMY.CZĘŚCIOWE ma charakterystyczną cechę – nie uwzględnia ona w swoich obliczeniach innych komórek z funkcją SUMY.CZĘŚCIOWE. Co z tego wynika? Jeśli na dole tabeli oblicza się podsumowanie całej tabeli, to nie mogą być uwzględniane w tej sumie, poszczególne sumy częściowe bo wynik byłby podwojony. Dzięki temu, możemy być spokojni, że funkcja nie będzie pokazywać błędnych wyników.

Policzenie jednocześnie kilku funkcji, np. SUMA i ŚREDNIA

Jeśli chcesz policzyć dla wybranej kolumny kilka funkcji, to musisz po prostu uruchomić narzędzie sum częściowych kilka razy – za każdym razem wybierając inną funkcję. Schemat działania jest więc taki:

• • Wstawiasz sumy częściowe normalnie, wybierając np. funkcję SUMA.
  • Przechodzisz ponownie do narzędzia sum częściowych, wybierasz inną funkcję, np. ŚREDNIA i odznaczasz opcję Zamień bieżące sumy częściowe. Jest to bardzo ważne – jeśli tego nie zrobisz, to druga suma częściowa nadpisze pierwszą, czyli zniknie SUMA, a w jej miejsce pojawi się ŚREDNIA.
  • Naciskasz OK i masz pod każdą grupą danych wstawione dwa wiersze – jeden z SUMA i drugi z ŚREDNIA.

Rysunek 10. Dwie funkcje użyte w jednej tabeli.

Jeśli liczysz więcej niż jedną sumę częściową w jednej tabeli, tak jak w przykładzie powyżej, to każda kolejna suma częściowa będzie dostawiała nowy wiersz z formułami. Nie ma możliwości ustawienia kilku różnych informacji, z różnych sum częściowych, w jednym wierszu – no chyba, że będziesz ręcznie kopiować formuły.