SUBTOTAL Formula w Excelu (spis treści)
- SUBTOTAL Formula w Excelu
- Jak korzystać z formuły SUBTOTAL w programie Excel?
SUBTOTAL Formula w Excelu
Czasami musimy znaleźć sumę częściową kategorii z dużego zestawu danych, który ma wiele kategorii. W takiej sytuacji funkcja sumy częściowej pomoże nam znaleźć mądrą kategorię całkowitą. Nie tylko sumy częściowe możemy obliczyć średnią, liczbę i maksimum oraz wiele innych.
Definicja
Funkcja, która zwraca sumę częściową z listy lub bazy danych, może być zdefiniowana jako funkcja sumy częściowej. Suma częściowa jest funkcją specjalną w porównaniu z innymi funkcjami programu Excel, ponieważ inne funkcje programu Excel wykonają tylko jedną określoną operację, ale specjalność funkcji sumy częściowej polega na tym, że nie tylko wykonuje ona obliczenia sumy częściowej, ale także wiele operacji arytmetycznych i logicznych zależy od numeru funkcji.
Składnia formuły SUBTOTAL
Argumenty:
Funkcja num : Odnosi się do rodzaju operacji matematycznej, którą zamierzamy wykonać na określonym zakresie danych.
Ref1, Ref 2: Odnosi się do zakresu komórek.
Dostępnych jest wiele numerów funkcji, ale nie ma problemu, nie musimy pamiętać wszystkich tych numerów funkcji, ponieważ podczas korzystania ze wzoru SUBTOTAL excel automatycznie wyświetli listę dostępnych numerów funkcji, jak poniżej.
Musisz się zastanawiać, dlaczego istnieją dwa numery funkcji dla tej samej funkcji.
- Numery funkcji 1–11 powinny być używane, gdy chcemy zignorować odfiltrowane komórki i rozważyć ręcznie ukryte komórki.
- Numery funkcji 101 - 111 powinny być używane, gdy chcemy zignorować wszystkie ukryte komórki, które są odfiltrowane i ręcznie ukryte komórki.
Zobaczymy kilka przykładów, aby zrozumieć, jak funkcja sumy częściowej będzie działać dla różnych numerów funkcji.
Jak korzystać z formuły SUBTOTAL w programie Excel?
SUBTOTAL Formula w Excelu jest bardzo prosta i łatwa. Zobaczmy, jak używać formuły SUBTOTAL w programie Excel z kilkoma przykładami.
Możesz pobrać SUBTOTALNY szablon Excel Formula tutaj - SUBTOTAL Formula Excel szablonPrzykład 1
Rozważ małą tabelę, która zawiera dane z różnych kategorii, jak poniżej.
Jeśli przestrzegasz powyższej tabeli, mamy kategorię produktu, kategorię koloru i ilość.
Zastosuj formułę SUBTOTAL jak poniżej z funkcją nr 9.
Po zastosowaniu tej formuły wynik jest taki, jak pokazano poniżej.
Zastosuj wzór SUBTOTAL jak poniżej z funkcją nr 109.
Po zastosowaniu formuły SUBTOTAL wynik pokazano poniżej.
W Formule użyliśmy zarówno funkcji 9, jak i 109 do wykonania SUMY w dwóch różnych kolumnach. C2: C9 to zakres danych, które wykonujemy obliczeń.
Teraz łączna suma wynosi 385 dla obu formuł. Ukryj kilka wierszy i obserwuj wyniki dla obu formuł.
Po zastosowaniu formuły SUBTOTAL Formula pokazano poniżej.
Wiersze od 50 do 60 są ukryte, dlatego wyniki funkcji numer 109 zmieniły się na 255, ponieważ nie uwzględnia ręcznie ukrytych danych, podczas gdy suma funkcji 9 pozostaje taka sama, co oznacza, że weźmie pod uwagę dane, mimo że dane ręcznie chowamy .
Zastosuj filtr do danych i filtruj tylko jeden kolor, tutaj wybieramy kolor czarny.
Po zastosowaniu formuły SUBTOTAL Formula jest pokazany poniżej.
Zastosuj formułę SUBTOTAL jak poniżej dla funkcji nr 109.
Po zastosowaniu formuły SUBTOTAL Formula jest pokazany poniżej.
Jeśli obserwujemy powyższy zrzut ekranu, oba numery funkcji nie uwzględniają ilości odfiltrowanych danych. Po odfiltrowaniu danych i ukryciu wierszy obie formuły dadzą takie same wyniki, że obie formuły nie uznają danych niewidocznych.
Po zastosowaniu formuły SUBTOTAL wynik jest taki, jak pokazano poniżej.
Zastosuj formułę SUBTOTAL jak poniżej dla funkcji nr 109.
Po zastosowaniu tej formuły wynik jest taki, jak pokazano poniżej.
Przykład nr 2
Do tej pory widzieliśmy operację SUM, teraz wykonamy operację ŚREDNIA z tym samym zakresem danych. 1 i 101 są liczbami funkcji do wykonania średniej. Przejrzyj powyższą tabelę, aby uzyskać szczegółowe informacje na temat numerów funkcji.
Po zastosowaniu formuły SUBTOTAL Formula jest pokazany poniżej.
W następnej komórce ponownie zastosuj formułę SUBTOTAL z funkcją nr 109.
Po zastosowaniu formuły wynik jest taki, jak pokazano poniżej.
Dzięki tej samej formule SUBTOTAL możemy również obliczyć średnią z jedyną zmianą numeru funkcji.
Po zastosowaniu formuły SUBTOTAL Formula jest pokazany poniżej.
W następnej komórce ponownie zastosuj formułę SUBTOTAL z numerem funkcji 101.
Po zastosowaniu formuły SUBTOTAL wynik pokazano poniżej.
Ukryj kilka wierszy, teraz obserwuj zmiany średniej. Średnia z funkcją numer 1 pozostaje taka sama, jak w dalszym ciągu, uwzględnia również ukrytą ilość. Średnia z funkcją numer 101 uległa zmianie, ponieważ ignoruje ręcznie ukryte dane. W ten sposób możesz wypróbować pozostałe numery funkcji i sprawdzić funkcjonalność każdego z nich, ponieważ podstawowa koncepcja jest taka sama, będą one również działać w ten sam sposób.
Przykład nr 3
Suma częściowa ma jeszcze jedną zaletę, której nie rozważy, jeśli w sumach danych będą dostępne sumy częściowe. Zobaczymy jeden przykład, aby lepiej zrozumieć. Wprowadź formuły, aby wykonać operację SUM w odniesieniu do produktów, takich jak A, B, C i D.
Po zastosowaniu formuły SUBTOTAL wynik jest taki, jak pokazano poniżej.
Ponownie użyj formuły SUBTOTAL, aby sprawdzić SUMĘ B.
Po zastosowaniu formuły SUBTOTAL wynik jest taki, jak pokazano poniżej.
Użyj formuły SUBTOTAL ponownie, aby sprawdzić SUMĘ C.
Po zastosowaniu formuły SUBTOTAL wynik jest taki, jak pokazano poniżej.
Użyj ponownie formuły SUBTOTAL, aby sprawdzić SUMĘ D.
Po zastosowaniu formuły SUBTOTAL wynik jest taki, jak pokazano poniżej.
Użyj ponownie formuły SUBTOTAL do następnej komórki.
Po zastosowaniu formuły SUBTOTAL wynik jest taki, jak pokazano poniżej.
Użyj ponownie formuły SUBTOTAL do następnej komórki.
Po zastosowaniu formuły SUBTOTAL wynik pokazano poniżej.
Jeśli obserwujemy powyższe zrzuty ekranu, istnieje suma pod względem produktu ubocznego, a wykonując sumę całkowitą, zawarliśmy sumy pod względem kategorii do sumy całkowitej (komórki od 36 do 48), ale nie uwzględni się tych sum częściowych (komórki od 44 do 47 ). Możemy więc obliczyć sumy pośrednie między zakresem danych bez wpływu na całkowitą wartość sumy.
Mam nadzieję, że rozumiesz, jak używać sumy częściowej i jak z niej korzystać.
O czym należy pamiętać o formule SUBTOTAL w programie Excel
- Podając numer funkcji, nie podawaj liczby spoza zakresu numerów funkcji, ponieważ nie jest on predefiniowany przez program Excel, otrzymamy komunikat o błędzie. Zawsze powinniśmy podawać liczbę od 1 do 11 i od 101 do 111, w przeciwnym razie wyświetli się komunikat o błędzie # wartość.
- Podczas korzystania z operacji dzielenia pamiętaj, że żadna liczba nie powinna być podzielna przez zero. Oznacza to, że x / 0 jest niepoprawnym formatem.
- Jeśli zastosujesz sumę częściową do danych poziomych, które pochodzą z A1: F1, a ukrycie jakichkolwiek kolumn nie wpłynie na sumę częściową.
- Podczas stosowania sumy częściowej, jeśli jakieś komórki nie mają danych lub danych nienumerycznych, funkcja zignoruje te komórki.
- Użyj numerów funkcji, rozumiejąc funkcjonalność i użyj, w przeciwnym razie możesz nie uzyskać poprawnych wyników, jak się spodziewasz.
Polecane artykuły
Jest to przewodnik po formule SUBTOTAL w programie Excel. Tutaj omawiamy, jak używać SUBTOTAL Formula w Excelu, wraz z praktycznymi przykładami i szablonem Excel do pobrania. Możesz także przejrzeć nasze inne sugerowane artykuły-
- Jak korzystać z formuły IRR programu Excel?
- Lewe PRZYCINANIE w Excelu (wzór, przykłady)
- Samouczek WYSZUKAJ.PIONOWO w programie Excel
- Formuła Excel ISNUMBER