Gdy arkusz programu Excel zawiera ukryte wiersze, filtrowane dane lub dane zgrupowane, użyj funkcji SUMY CZĘŚCIOWE programu Excel. Funkcja SUMY. CZĘŚCIOWE może uwzględniać lub wykluczać w obliczeniach wartości ukryte. Oprócz znajdowania sumy grupy danych program Excel może obliczyć średnią, maksimum, minimum, odchylenie standardowe i wariancję danych. Oto jak wstawić sumy częściowe w programie Excel.
Instrukcje zawarte w tym artykule dotyczą programów Excel dla Microsoft 365, Excel 2019 i Excel 2016.
Składnia funkcji SUMA CZĘŚCIOWA
Użyj funkcji SUMA CZĘŚCIOWA w programie Excel, aby podsumować wartości w arkuszu na różne sposoby. Jest to szczególnie przydatne, gdy arkusz zawiera ukryte wiersze, które chcesz uwzględnić w obliczeniach.
Składnia funkcji SUMY. CZĘŚCIOWE to: SUM. CZĘŚCIOWA(numer_funkcji, ref1, ref2, …)
Argument function_num jest wymagany i określa typ operacji matematycznej, która ma być użyta dla sumy częściowej. Funkcja SUMY CZĘŚCIOWE może dodawać liczby, obliczać średnią wartość wybranych liczb, znajdować wartości maksymalne i minimalne w zakresie, liczyć liczbę wartości w wybranym zakresie i nie tylko.
Funkcja SUMA CZĘŚCIOWA ignoruje komórki, które nie zawierają danych oraz komórki z wartościami nienumerycznymi.
Ten argument jest liczbą i zależy od tego, czy chcesz uwzględnić ukryte wiersze w wyniku, czy też wykluczyć ukryte wiersze z wyniku. Te wiersze mogą być ręcznie ukryte lub ukryte przez filtr.
Argumenty numer_funkcji obejmują:
| Zadanie funkcyjne | function_num | function_num |
|---|---|---|
| (zawiera ukryte wartości) | (wyklucza ukryte wartości) | |
| ŚREDNIA | 1 | 101 |
| LICZBA | 2 | 102 |
| LICZBA | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| PRODUKT | 6 | 106 |
| ODCH. STANDARDOWE | 7 | 107 |
| ODCH. STANDARDOWE | 8 | 108 |
| SUM | 9 | 109 |
| VAR | 10 | 110 |
| VARP | 11 | 111 |
Argumenty odwołania function_num od 1 do 11 zawierają wartości tylko w ukrytych wierszach, gdy używane jest polecenie Ukryj do ukrywania wierszy. Podczas korzystania z polecenia Filtruj obliczenia SUMY CZĘŚCIOWE nie uwzględniają ukrytych wyników filtrowania.
Argument ref1 jest wymagany. Są to komórki używane do obliczania wyników wybranego argumentu numer_funkcji. Ten argument może być wartością, pojedynczą komórką lub zakresem komórek.
Argumenty ref2, … są opcjonalne. Są to dodatkowe komórki, które są uwzględniane w obliczeniach.
Użyj funkcji SUMA CZĘŚCIOWA z ukrytymi wierszami
Funkcje programu Excel można wprowadzać ręcznie lub za pomocą okna dialogowego Argumenty funkcji. Aby zilustrować sposób ręcznego wprowadzania funkcji przy użyciu paska formuły, w poniższym przykładzie użyto argumentu LICZBA_funkcji w celu zliczenia liczby wartości w widocznych wierszach oraz w widocznych i ukrytych wierszach.
Aby użyć funkcji SUMA CZĘŚCIOWA do zliczenia liczby wierszy w arkuszu:
- Zacznij od arkusza roboczego zawierającego wiele wierszy danych.
- Wybierz komórkę, która będzie zawierać liczbę widocznych wierszy.
-
Na pasku funkcji wprowadź =SUBTOTAL. Podczas pisania program Excel sugeruje funkcję. Kliknij dwukrotnie funkcję SUBTOTAL.
Aby użyć okna dialogowego Argumenty funkcji do wprowadzenia funkcji SUMY CZĘŚCIOWE, przejdź do Formuły i wybierz Math & Trig >SUBTOTAL.
Image -
W rozwijanym menu, które się pojawi, kliknij dwukrotnie argument 102 – COUNT function_num.
Image -
Wpisz przecinek (,).
Image -
W arkuszu zaznacz komórki, które chcesz uwzględnić w formule.
Image -
Naciśnij Enter, aby zobaczyć wynik w komórce wybranej w kroku 2.
Image - Wybierz komórkę, która będzie zawierać liczbę widocznych i ukrytych wierszy.
-
Na pasku funkcji wprowadź =SUBTOTAL. Podczas pisania program Excel sugeruje funkcję. Kliknij dwukrotnie funkcję SUBTOTAL.
- W wyświetlonym menu rozwijanym kliknij dwukrotnie argument 2 – COUNT numer_funkcji, a następnie wpisz przecinek (,).
-
W arkuszu zaznacz komórki, które chcesz uwzględnić w formule, a następnie naciśnij Enter.
Image -
Ukryj kilka wierszy danych. W tym przykładzie ukryte zostały wiersze ze sprzedażą poniżej 100 000 USD.
Image
Użyj funkcji SUMA CZĘŚCIOWA z filtrowanymi danymi
Użycie funkcji SUMA CZĘŚCIOWA na filtrowanych danych powoduje ignorowanie danych w wierszach, które zostały usunięte przez filtr. Za każdym razem, gdy zmieniają się kryteria filtrowania, funkcja oblicza ponownie, aby pokazać sumę częściową dla widocznych wierszy.
Aby użyć funkcji SUMA CZĘŚCIOWA, aby zobaczyć różnice w wynikach obliczeń podczas filtrowania danych:
-
Utwórz formuły SUMA CZĘŚCIOWA. Na przykład utwórz formuły, aby określić sumy częściowe i średnie wartości filtrowanych danych.
Nie ma znaczenia, czy użyjesz argumentu numer_funkcji dla widocznych czy ukrytych wierszy. Oba argumenty dają ten sam wynik w filtrowanych danych.
Image - Wybierz dowolną komórkę w zestawie danych.
-
Przejdź do Home, a następnie wybierz Sortuj i filtruj > Filter.
Image -
Użyj strzałek rozwijanych, aby przefiltrować dane arkusza roboczego.
Image -
Zwróć uwagę, jak wartości zmieniają się za każdym razem, gdy wybierasz inne kryteria filtrowania.
Image
Użyj funkcji SUMA CZĘŚCIOWA z danymi zgrupowanymi
Gdy dane są zgrupowane, istnieje sposób na zastosowanie funkcji SUMY CZĘŚCIOWE dla każdej grupy, a następnie obliczenie sumy całkowitej dla całego zestawu danych.
- Wybierz dowolną komórkę w zestawie danych.
-
Wybierz Dane > Suma częściowa, aby otworzyć okno dialogowe Suma częściowa.
Image - Wybierz strzałkę rozwijaną Przy każdej zmianie w i wybierz grupę, dla której zostanie obliczona każda suma częściowa.
- Wybierz strzałkę menu Użyj funkcji i wybierz numer funkcji.
- Na liście Dodaj sumę częściową do wybierz kolumnę, do której zostanie zastosowana formuła.
-
Wybierz OK.
Image -
Podsumy są wstawiane dla każdej grupy danych, a suma końcowa jest wstawiana na dole zestawu danych.
Image - Aby zmienić numer_funkcji, zaznacz dowolną komórkę w zestawie danych i wybierz Dane > Suma częściowa. Następnie dokonaj wyboru w oknie dialogowym Suma częściowa.
