SUMY.CZĘŚCIOWE (SUBTOTAL) w Excelu: suma po filtrze

=SUMY.CZĘŚCIOWE(9;C2:C8) (po angielsku =SUBTOTAL(9,C2:C8)) dodaje liczby z C2:C8 jak SUMA, z dwiema różnicami: pomija każdą inną formułę SUMY.CZĘŚCIOWE w zakresie i pomija wiersze ukryte przez filtr. Pierwszy argument, 9, mówi, jakie obliczenie wykonać. Tabele pokazują formuły po angielsku, ale możesz w nich wpisywać formuły także po polsku, ze średnikami.

FormułyResetuj

C8fx

=SUBTOTAL(9,C2:C7)

	A	B	C	D	E	F
1	Region	Item	Sales		Check	Result
2	North	Apple	120		SUM of C2:C7	890
3	North	Pear	80
4	North total		200
5	South	Apple	200
6	South	Pear	45
7	South total		245
8	Grand total		445

Kliknij komórkę, aby zobaczyć jej formułę. Zmień liczbę albo formułę, a arkusz przeliczy się na nowo.W polskim Excelu: =SUMY.CZĘŚCIOWE(9;C2:C7)

Suma końcowa w C8 obejmuje całą kolumnę, razem z wierszami sum częściowych, a mimo to pokazuje 445: SUMY.CZĘŚCIOWE pomija C4 i C7, bo zawierają formuły SUMY.CZĘŚCIOWE. F2 robi to samo funkcją SUMA i pokazuje 890, czyli każdą sprzedaż policzoną dwa razy. Gdy każdy wiersz sumy używa SUMY.CZĘŚCIOWE, możesz dodawać albo przenosić grupy bez przepisywania sumy końcowej.

Numery funkcji w SUMY.CZĘŚCIOWE

=SUBTOTAL(function_num, ref1, [ref2], ...)

Gdy wpiszesz =SUMY.CZĘŚCIOWE(, Excel pokaże tę listę, więc nie musisz jej pamiętać. Najczęściej używa się 9 i 109 (SUMA), 1 (ŚREDNIA) i 103 (liczenie widocznych wierszy).

FormułyResetuj

F2fx

=SUBTOTAL(1,C2:C7)

	A	B	C	D	E	F
1	Region	Item	Sales		Calculation	Result
2	North	Apple	120		AVERAGE (1)	88.33
3	North	Pear	80		COUNTA (3)	6
4	South	Apple	200		MAX (4)	200
5	South	Pear	45		MIN (5)	30
6	East	Apple	55		Visible rows (103)	6
7	East	Plum	30		SUM (109)	530

Kliknij komórkę, aby zobaczyć jej formułę. Zmień liczbę albo formułę, a arkusz przeliczy się na nowo.W polskim Excelu: =SUMY.CZĘŚCIOWE(1;C2:C7)

Nic tu nie jest ukryte, więc każdy wiersz daje to samo co zwykła funkcja: średnią 88,33, 6 wierszy, MAX 200, MIN 30 i SUMĘ 530. Różnica pojawia się dopiero przy ukrytych wierszach, o czym mówi następna sekcja.

SUMY.CZĘŚCIOWE 9 a 109 i wiersze odfiltrowane

Włącz filtr poleceniem Dane > Filtruj (Ctrl+Shift+L, Cmd+Shift+F na Macu), a potem wybierz North na liście rozwijanej Region. Wiersze innych regionów zostaną ukryte:

• =SUMA(C2:C7) nadal dodaje wszystkie sześć wierszy.
• =SUMY.CZĘŚCIOWE(9;C2:C7) i =SUMY.CZĘŚCIOWE(109;C2:C7) dodają tylko widoczne wiersze North.
• =SUMY.CZĘŚCIOWE(103;A2:A7) liczy wiersze, które zostały na ekranie: 2, tyle samo, ile podaje pasek stanu (2 z 6 znalezionych rekordów).

Te dwie rodziny różnią się tylko dla wierszy ukrytych ręcznie (zaznacz wiersze, prawy przycisk > Ukryj). 9 nadal je dodaje, 109 nie. Jeśli suma ma zawsze odpowiadać temu, co widać na ekranie, użyj 109. Jeśli ukrywasz wiersze tylko dla porządku i nadal chcesz je mieć w sumie, użyj 9.

SUMY.CZĘŚCIOWE działa tylko na wierszach. Ukryte kolumny są zawsze uwzględniane, więc =SUMY.CZĘŚCIOWE(109;B2:G2) w poprzek wiersza dodaje też ukryte kolumny.

Najszybszy sposób na SUMY.CZĘŚCIOWE to przycisk Autosumowanie przy włączonym filtrze: Excel wpisuje wtedy =SUMY.CZĘŚCIOWE(9;...) zamiast SUMA. Dane > Suma częściowa idzie dalej: na liście posortowanej według kolumny wstawia wiersz sumy pod każdą grupą i sumę końcową, wszystko funkcją SUMY.CZĘŚCIOWE, oraz przyciski konspektu do zwijania grup.

AGREGUJ: SUMY.CZĘŚCIOWE, które pomijają błędy

Jeśli jedna komórka w zakresie zawiera błąd, SUMA i SUMY.CZĘŚCIOWE zwracają ten błąd. AGREGUJ (po angielsku AGGREGATE, Excel 2010 i nowszy) to SUMY.CZĘŚCIOWE z dodatkowym argumentem opcji; opcja 6 pomija wartości błędów.

FormułyResetuj

F3fx

=AGGREGATE(9,6,C2:C7)

	A	B	C	D	E	F
1	Region	Item	Sales		Formula	Result
2	North	Apple	120		SUBTOTAL	#N/A
3	North	Pear	#N/A		AGGREGATE, ignore errors	450
4	South	Apple	200		AGGREGATE, MAX	200
5	South	Pear	45
6	East	Apple	55
7	East	Plum	30

Kliknij komórkę, aby zobaczyć jej formułę. Zmień liczbę albo formułę, a arkusz przeliczy się na nowo.W polskim Excelu: =AGREGUJ(9;6;C2:C7)

C3 zawiera #N/A (w polskim Excelu #N/D!; tabele pokazują angielskie nazwy błędów), więc F2 też pokazuje #N/A. F3 go pomija i dodaje pozostałe pięć: 450. Jej pierwszy argument używa tych samych numerów co SUMY.CZĘŚCIOWE (9 to SUMA, 4 to MAX). Inne opcje: 5 pomija ukryte wiersze, 7 pomija ukryte wiersze i błędy, 3 pomija ukryte wiersze, błędy oraz zagnieżdżone formuły SUMY.CZĘŚCIOWE i AGREGUJ. Zastąp C3 liczbą, a F2 pokaże tę samą sumę co F3.

Ćwiczenie: suma końcowa nad sumami częściowymi

FormułyResetuj

C9fx

	A	B	C
1	Region	Item	Sales
2	North	Apple	120
3	North	Pear	80
4	North total		200
5	South	Apple	200
6	South	Pear	45
7	South	Plum	60
8	South total		305
9	Grand total

Kliknij komórkę, aby zobaczyć jej formułę. Zmień liczbę albo formułę, a arkusz przeliczy się na nowo.

Twoja kolej: Pod każdym regionem lista ma sumę częściową. Wpisz w C9 sumę końcową, która obejmuje C2:C8 i nie liczy wierszy sum częściowych dwa razy.

SprawdźPokaż odpowiedź

Dlaczego suma z SUMY.CZĘŚCIOWE nadal jest zła

• Sumy grup używają SUMA. SUMY.CZĘŚCIOWE pomija inne formuły SUMY.CZĘŚCIOWE w swoim zakresie, ale nie formuły SUMA. Suma grupy zapisana jako =SUMA(C2:C3) zostanie policzona jeszcze raz. Zmień każdy wiersz sumy na SUMY.CZĘŚCIOWE.
• Wiersze ukryto ręcznie, a numer funkcji to 9. Użyj 109.
• Dane są w kolumnach, a nie w wierszach. Ukryte kolumny nigdy nie są pomijane.
• Potrzebujesz warunku, a nie filtra. SUMY.CZĘŚCIOWE idzie za tym, co ukrywa filtr. Aby zsumować North bez filtrowania, użyj SUMA.JEŻELI. Podsumowanie wszystkich grup naraz, bez wierszy sum w danych, da tabela przestawna.