Funkcja SUMA CZĘŚCIOWA: Formuła i sposób korzystania z funkcji SUMA CZĘŚCIOWA w programie Excel

Co to jest funkcja PODSUMA w programie Excel ? Co to jest formuła SUMA CZĘŚCIOWA w programie Excel ? Przekonajmy się z LuckyTemplates.com!

Microsoft Excel ma wiele funkcji służących do obliczania sum, a jedną z nich jest PODSUMA. Suma częściowa różni się jednak od funkcji SUMA. To nie jest zwykła suma. Zamiast tego służy do obliczania sum częściowych na listach lub bazach danych, obliczania średnich i nie tylko.

Główna treść dotycząca funkcji SUBTOTAL

• Co to jest funkcja PODSUMA i do czego służy?
• Formuła SUMA CZĘŚCIOWA w programie Excel
• Jak korzystać z funkcji PODSUMA w programie Excel
  • 1. Oblicz sumę przefiltrowanych wierszy 
  • 2. Policz przefiltrowane komórki, które nie są puste
  • 3. Ignoruj ​​wartości w zagnieżdżonych formułach sumy częściowej
• Rzeczy do rozważenia przed użyciem funkcji SUMA CZĘŚCIOWA w programie Excel
• Jak popełnić błąd podczas wykonywania funkcji SUMA CZĘŚCIOWA w programie Excel

Co to jest funkcja PODSUMA i do czego służy?

Na pierwszy rzut oka możesz się zastanawiać: do czego służy funkcja PODSUMA? W końcu łatwo jest znaleźć sumę częściową z zakresu komórek, korzystając z istniejących funkcji, takich jak SUMA JEŻELI lub ŚREDNIA .

Chociaż prawdą jest, że niekoniecznie trzeba używać SUMY CZĘŚCIOWEJ, znacznie ułatwia to analizę docelowych danych. Formułę korzystającą z SUMA CZĘŚCIOWA można wstawić do zakresu komórek bez wpływu na sumę całkowitą, ponieważ SUMA CZĘŚCIOWA ignoruje inne komórki zawierające formułę SUMA CZĘŚCIOWA.

SUMA CZĘŚCIOWA współpracuje z innymi funkcjami programu Excel, takimi jak filtrowanie komórek. Jeśli przefiltrujesz tabelę według wartości, formuła SUMA CZĘŚCIOWA zostanie zaktualizowana, rozpoznaje filtr i wyklucza odpowiednie komórki. SUBTOTAL jest również przydatny do ignorowania ukrytych wartości, czego nie mogą zrobić inne funkcje (takie jak SUMA).

Na szczęście w ramach formuły SUMA CZĘŚCIOWA możesz użyć innych funkcji. Dostępnych jest 11 różnych metod sum częściowych, każda odpowiednia dla istniejącej funkcji programu Excel. Jeśli potrzebujesz formuły SUMA, która działa z filtrowanymi komórkami i ignoruje inne formuły SUMA CZĘŚCIOWA, najlepiej użyć SUMA CZĘŚCIOWA, chociaż tabela przestawna może działać w ten sam sposób.

Formuła SUMA CZĘŚCIOWA w programie Excel

Funkcja SUMA CZĘŚCIOWA działa poprzez obliczenie całkowitej wartości zakresu komórek w oparciu o inną funkcję matematyczną. SUMA CZĘŚCIOWA wykorzystuje do 11 różnych funkcji matematycznych (od ŚREDNIEJ do ZMIENNEJ.P), aby znaleźć odpowiednią wartość całkowitą, pracując w ramach parametrów funkcji (tj. ignorując inne formuły PODSUMA).

Funkcja SUBTOTAL ma składnię =SUBTOTAL(numer_funkcji, ref1, [ref2],…) . Tam:

• Numer_funkcji: Liczby od 1 do 11 i od 101 do 111 określają, która funkcja zostanie użyta do obliczenia SUMY CZĘŚCIOWEJ.
• Ref1, Ref2, ...: 1 lub więcej komórek lub zakres komórek do obliczenia sum częściowych, maksymalnie 254.

Notatka:

• Funkcja SUBTOTAL przeznaczona jest do obliczania pionowych kolumn danych.
• Jeżeli argumenty ref1, ref2,... zawierają funkcję SUBTOTAL, zostaną zignorowane, aby uniknąć podwójnego liczenia.
• Jeśli numer_funkcji wynosi od 1 do 11, funkcja SUBTOTAL oblicza, uwzględniając ukryte wartości w zbiorze danych, ponieważ wiersz zawierający ten zakres jest ukryty. Jeśli numer_funkcji wynosi od 101 do 111, funkcja SUBTOTAL oblicza tylko nieukryte wartości w zbiorze danych (nie licząc wartości w ukrytych wierszach).
• W przypadku obszarów danych ukrytych za pomocą filtra suma częściowa będzie ignorowana.

Jak korzystać z funkcji PODSUMA w programie Excel

1. Oblicz sumę przefiltrowanych wierszy 

Aby lepiej zrozumieć tego typu sumy zapoznaj się z artykułem Obliczanie sumy przefiltrowanych wartości list w Excelu . Zasadniczo funkcja SUBTOTAL w tym przypadku będzie wyglądać następująco:

=SUMA CZĘŚCIOWA(9,_zakres)

pham_range tutaj jest obszar, który chcesz zsumować po przefiltrowaniu danych.

2. Policz przefiltrowane komórki, które nie są puste

Użyjemy SUMY CZĘŚCIOWEJ 3 lub SUMY CZĘŚCIOWEJ 103. Jeśli jednak istnieją ukryte wiersze, należy użyć PODSUMY 103, aby dokładnie policzyć widoczne, niepuste komórki.

Na przykład powyższa tabela danych ukryje 2 wiersze 4 i 5. Użycie SUMY CZĘŚCIOWEJ 3 lub SUMY CZĘŚCIOWEJ 103 da 2 różne wyniki.

Wpisujemy formułę i wtedy Excel automatycznie wyświetli zestaw funkcji do wyboru bez konieczności zapamiętywania.

Wynik przy użyciu sumy częściowej 3 wyniesie 3, łącznie z ukrytymi komórkami w wierszu.

Jeśli chodzi o SUBTOTAL 103, wyświetli ona tylko niepuste komórki, które widzimy, ignorując ukryte wiersze.

3. Ignoruj ​​wartości w zagnieżdżonych formułach sumy częściowej

Przykładowo obliczymy średnią z wszystkich kilogramów tkaniny w magazynach A1 i A2.

Formuła średnia dla magazynu A2=SUMA CZĘŚCIOWA(1,C2:C4) i daje wynik 19.

Formuła średnia dla magazynu A1 = SUMA CZĘŚCIOWA(1,C5:C7) daje wynik 38.

Jednakże przy obliczaniu średniej z łącznej liczby tkanin w 2 magazynach, średnie wyniki z 2 magazynów zostaną pominięte. Mamy formułę =SUMA CZĘŚCIOWA(1,C2:C9), a wynik automatycznie usunie wcześniej obliczony średni wynik.

Rzeczy do rozważenia przed użyciem funkcji SUMA CZĘŚCIOWA w programie Excel

Chociaż PODTOTAL ma swoje zalety, przed rozpoczęciem korzystania z tej funkcji należy wziąć pod uwagę kilka rzeczy, w tym następujące:

• Jak wspomniano, użycie wartości 1-11 dla argumentu numer_funkcji zapewni, że SUMA PODZIAŁOWA obejmie wartości ukryte, natomiast wartość 101-111 zignoruje je.
• Użycie wartości innej niż 1-11 lub 101-111 spowoduje, że program Excel zwróci błąd #WARTOŚĆ!. Dzieje się tak również w przypadku odniesień do komórek 3D (gdzie ta sama komórka w wielu arkuszach ma odniesienia w zakresie).
• Gdy używane są poziome zakresy komórek, takie jak A1:D1, ukryte wartości są automatycznie uwzględniane (niezależnie od wartości argumentu numer_funkcji). Jest to ograniczenie funkcji SUBTOTAL i nie można go pokonać.
• Użycie SUBTOTAL na przefiltrowanych danych sprawi, że wartości ukryte będą zawsze ignorowane, niezależnie od użytego argumentu numer_funkcji.
• SUMA CZĘŚCIOWA może być używana jako funkcja zagnieżdżona jako część innych funkcji, w tym formuł zawierających funkcję JEŻELI.
• Jeśli w zakresie danych ref1 znajduje się inna formuła SUMA CZĘŚCIOWA, funkcja SUMA CZĘŚCIOWA ignoruje ją i wyklucza z ogólnych obliczeń.
• Excel umożliwia użycie w formule SUMA CZĘŚCIOWA maksymalnie 254 zakresów komórek, chociaż do działania formuły potrzebny jest tylko 1 zakres komórek.

Jak popełnić błąd podczas wykonywania funkcji SUMA CZĘŚCIOWA w programie Excel

Obliczając funkcję SUMA CZĘŚCIOWA w Excelu, często napotkasz kilka podstawowych błędów:

• #VALUE!: Identyfikator funkcji nie należy do zakresu 1-11 lub 101-111 lub ma odniesienie 3D.
• #DIV/0!: Występuje, gdy określona suma musi zostać podzielona przez 0 (na przykład podczas obliczania średniej lub odchylenia standardowego zakresu komórek, który nie zawiera wartości liczbowych).
• #NAZWA? Nazwa funkcji SUMA CZĘŚCIOWA została błędnie napisana.

Kilka wskazówek, jak korzystać z funkcji SUMA CZĘŚCIOWA w programie Microsoft Excel, o której możesz nie wiedzieć

Wskazówka 1: Załóżmy, że chcesz się upewnić, że wszystkie kolory t-shirtów są dostępne w każdym rozmiarze w dowolnym magazynie. Możesz wykonać następujące kroki:

Krok 1: Kliknij Suma częściowa . Pamiętaj, że ten krok polega na dodaniu większej liczby kryteriów do istniejących danych sumy częściowej.

Krok 2: Wybierz opcję LICZBA z menu rozwijanego i opcję Rozmiar z pola „ Dodaj pole sumy częściowej do ”. Następnie odznacz opcję Zastąp bieżące sumy częściowe . Po kliknięciu OK otrzymasz następującą tabelę danych:

Dzięki temu masz pewność, że masz wystarczającą liczbę różnych wymiarów i możesz klasyfikować dane w sposób niewymagający powtarzania operacji.

Wskazówka 2 : Zawsze warto sortować dane według kolumn.

Wskazówka 3: Pomaga umieścić etykietę w pierwszym wierszu.

Wskazówka 4: Jeśli chcesz podsumować dane, odznacz pole „ Podsumowanie pod danymi podczas wstawiania sumy częściowej ”. - Podsumowanie poniżej danych przy wstawianiu funkcji SUMA CZĘŚCIOWA.

Krótko mówiąc, jaka jest funkcja sumy częściowej w programie Excel?

Jak sama nazwa wskazuje, funkcja PODSUMA w Excelu służy do obliczania sum częściowych. Funkcja Excel SUBTOTAL zwraca zagregowane wyniki dla podanych wartości. SUMA CZĘŚCIOWA może obliczyć sumę, średnią, liczbę, maksimum… Ten Excel może również uwzględniać lub wykluczać wartości w ukrytych wierszach.

• Cel: Obliczenie sumy częściowej na liście lub w bazie danych.
• Wartość zwracana: liczba reprezentująca typ sumy częściowej.
• Argument:
  • function_num— Liczba określająca, której funkcji należy użyć do obliczenia sum częściowych na liście.
  • ref1- Nazwany zakres lub odniesienie do sumy częściowej.
  • ref2- [opcjonalnie] Nazwany zakres lub odniesienie do sumy częściowej.

Użycie funkcji SUMA CZĘŚCIOWA może pomóc w szybkiej analizie zbioru danych bez konieczności korzystania z bardziej złożonych tabel przestawnych. Jeśli utkniesz, możesz także skorzystać z funkcji Suma częściowa, znajdującej się na karcie Dane na wstążce, aby automatycznie utworzyć formułę SUMA CZĘŚCIOWA, którą możesz następnie edytować lub skopiować w innym miejscu.

Zobacz więcej:

• Funkcja SUMPRODUCT w programie Excel: Oblicz całkowity iloczyn odpowiednich wartości
• Jak połączyć 2 kolumny Imię i nazwisko w Excelu bez utraty treści