Funkcja Excel SUBTOTAL (spis treści)
- SUBTOTAL w programie Excel
- SUBTOTAL Formula w Excelu
- Jak korzystać z funkcji SUBTOTAL w Excelu?
SUBTOTAL w programie Excel
- Funkcja sumy częściowej jest wbudowaną funkcją programu Excel, która jest podzielona na funkcje matematyczne / wyzwalające
- Suma pośrednia nie tylko sumuje liczby w określonym zakresie komórek, ale pomaga w wykonywaniu różnych operacji arytmetycznych
- SUBTOTAL jest wszechstronny. Możesz go użyć do uśrednienia, podsumowania, zliczenia komórek i innych obliczeń
Definicja
Zwraca sumę częściową w bazie danych lub liście, tj. Aby znaleźć sumę częściową danego zakresu komórek. Wykonuje określone obliczenia dla danego zestawu wartości.
SUBTOTAL Formula w Excelu
Wzór na funkcję SUBTOTAL jest następujący:
SUBTOTAL (numer_funkcji, ref1, (ref2), …) Gdzie
Numer_funkcji lub kod operacji: Wskazuje, która funkcja powinna być użyta do obliczeń sum częściowych na danej liście lub Jest to rodzaj operacji matematycznej, którą chcesz wykonać
Argument function_num jest stosowany na podstawie dwóch wymienionych poniżej kryteriów lub metody
- Dołącz ukryte wartości Gdy argument function_num zawiera się między 1-11, funkcja SUBTOTAL będzie zawierać ukryte wartości.
- Ignoruj ukryte wartości Gdy argument function_num zawiera się między 101-111, funkcja SUBTOTAL wyklucza lub ignoruje ukryte wartości. Znajduje wartości tylko niewidoczne wiersze ignoruje ukryte wartości w obliczeniach.
| Fun_num Obejmuje ukryte wartości | Fun_num Nie obejmuje ukrytych wartości | Funkcjonować | Opis | |||
| 1 | 101 | ŚREDNI | Oblicza średnią z określonego zakresu. | |||
| 2) | 102 | LICZYĆ | Policzy liczbę komórek zawierających wartości liczbowe. | |||
| 3) | 103 | COUNTA | Zlicza niepuste komórki w określonym zakresie. | |||
| 4 | 104 | MAX | Odkryje największą wartość w podanym zakresie. | |||
| 5 | 105 | MIN | Wyszuka najmniejszą wartość w określonym zakresie. | |||
| 6 | 106 | PRODUKT | Oblicza iloczyn komórek w określonym zakresie. | |||
| 7 | 107 | STDEV | Szacuje odchylenie standardowe w podanym zakresie. | |||
| 8 | 108 | STDEVP | Oblicza odchylenie standardowe na podstawie całej populacji. | |||
| 9 | 109 | SUMA | Oblicza sumę określonego zakresu. | |||
| 10 | 110 | VAR | Szacuje wariancję w określonym zakresie. | |||
| 11 | 111 | VARP | Szacuje wariancję na podstawie całej populacji. |
ref1, (ref2): Jedno lub więcej odwołań do komórek zawierających wartości, które należy wykonać dla sumy częściowej.
Jak korzystać z funkcji SUBTOTAL w Excelu?
Zobaczmy, jak działa funkcja SUBTOTAL w programie Excel.
Możesz pobrać ten SUBTOTALNY szablon funkcji Excel tutaj - SUBTOTALNY szablon Excel funkcjiPrzykład # 1 - SUBTOTALNA formuła dla wartości ukrytych za pomocą programu Excel Autofiltr lub wierszy ukrytych przez filtr
W poniższym przykładzie tabela zawiera Kwartalne dane sprzedaży, gdzie poszczególne kwartały w kolumnie E i dane sprzedaży w kolumnie F.
Funkcja sumy jest stosowana w komórce „F16”, tj. = SUMA (F4: F15).
Zwraca lub daje wartość 1184 .
Funkcja SUM zwraca tę samą wartość. tzn. 1184 zamiast 194 dla danych z IV kwartału, jeśli wiersze są ukryte przez filtr (poniższy zrzut ekranu).
Jeśli chcę tylko poprawnej wartości SUM danych Q4, muszę użyć funkcji SUBTOTAL.
Przed zastosowaniem funkcji SUBTOTAL, na pasku narzędzi danych zastosuj filtr dla wiersza nagłówka.
Teraz obliczmy osobno dane dotyczące sprzedaży w pierwszym lub drugim kwartale, drugim lub trzecim kwartale lub czwartym osobno dla danych filtrowanych.
Użyj 109 jako argumentu function_num w funkcji SUBTOTAL, aby zsumować dane. tj. = SUBTOTALNY (109, C17: C28)
Funkcja SUBTOTAL ignoruje wiersze ukryte przez filtr i oblicza poprawny wynik.
Tutaj wartość sumy częściowej zmienia się dynamicznie zgodnie z filtrem, znajdzie sumę wartości tylko w widocznych wierszach; ukryte wiersze są pomijane przez funkcję SUBTOTAL. (Poniżej wymienione np.)
Pytanie 4
Pytanie 3
Q2
Pytanie 1
Przykład # 2 - Funkcja SUBTOTAL dla ręcznie ukrytych wierszy
W arkuszu programu Excel wiersze nr 15, 16, 19 i 20 zostały ukryte za pomocą opcji formatowania wierszy, zaznaczając te wiersze, klikając prawym przyciskiem myszy i wybierając opcję Ukryj.
Funkcja sumy częściowej jest używana w komórce C23 arkusza kalkulacyjnego do obliczania sumy widocznych komórek.
Musimy użyć 109 jako argumentu function_num w funkcji SUBTOTAL, aby zsumować dane.
tj. = SUBTOTALNY (109, C14: C21)
Tutaj funkcja SUBTOTAL ignoruje ręcznie ukryte wiersze i oblicza poprawny wynik.
Znajdzie sumę wartości tylko w widocznych wierszach; ukryte wiersze są pomijane przez funkcję SUBTOTAL
Przykład # 3 - SUBTOTAL Funkcja dla wartości normalnej
Ponieważ wszystkie komórki z zakresu kwartalnych danych sprzedaży są widoczne, żadna z nich nie jest filtrowana lub wiersze są ukryte, zawiera wszystkie wartości.
W poniższym przykładzie zastosowano wiele funkcji sumy częściowej.
W komórce stosowana jest funkcja SUBTOTALNA C14, C15 i C16.
Aby obliczyć wartość SUMY, funkcja numer 9 lub 109 jest używana w funkcji sumy częściowej, gdzie oblicza Suma określonego zakresu, tj. = SUBTOTAL (9, C4: C11)
Daje nam sumę wszystkich wartości w C4: C11, pod warunkiem, że żadne nie są filtrowane, a wiersze są ukryte w tym określonym zakresie.
Aby obliczyć wartość MIN, funkcja numer 5 lub 105 jest używana w funkcji sumy częściowej, w której znajdzie najmniejszą wartość w określonym zakresie. W komórce, C15, stosowana jest funkcja SUBTOTALNA, tj. = SUBTOTAL (5, C4: C11)
Wyszuka najmniejszą wartość w określonym zakresie, tj. C4: C11
Aby obliczyć wartość MAX, funkcja 4 lub 104 jest używana w funkcji sumy częściowej, w której znajdzie największą wartość w określonym zakresie. W komórce, C16, stosowana jest funkcja SUBTOTALNA, tj. = SUBTOTAL (4, C4: C11)
Odkryje największą wartość w podanym zakresie, tj. C4: C11.
O czym należy pamiętać o funkcji SUBTOTAL
- Można zastosować funkcję SUBTOTAL, aby znaleźć sumę przefiltrowanych wartości.
- Funkcja SUBTOTAL służy do ignorowania wartości w ukrytych wierszach.
- Wartość argumentu funkcji _ num nie powinna być większa niż 11 lub mniejsza niż 1, podobnie nie powinna być większa niż 111 lub mniejsza niż 101, w przeciwnym razie da # WARTOŚĆ! błąd.
- Funkcja SUBTOTAL ma zastosowanie tylko do wartości liczbowej.
- Jeśli istnieją jakieś sumy częściowe w zakresie SUBTOTAL, zostaną one pominięte, tj. Jeśli jakiekolwiek już istniejące formuły SUBTOTAL są obecne w zakresie wejściowym innej formuły SUBTOTAL, wówczas wartości te są pomijane przez funkcję sumy częściowej podczas obliczeń, dzięki czemu unika się podwójnego liczenia i zapobiega błędowi podwójnego liczenia.
- Za pomocą funkcji SUBTOTAL można dynamicznie podsumować dane.
- Puste komórki i komórki zawierające wartości nienumeryczne są ignorowane przez funkcję sumy częściowej podczas obliczania.
Polecane artykuły
To był przewodnik po SUBTOTAL. Tutaj omawiamy formułę SUBTOTAL i sposób korzystania z funkcji SUBTOTAL wraz z przykładem programu Excel i szablonami programu Excel do pobrania. Możesz również spojrzeć na te przydatne funkcje w programie Excel -
- Funkcja WYSZUKIWANIA w programie Excel
- ZNAJDŹ funkcję w programie Excel
- NIE w programie Excel
- TRANSPOSUJ w Excelu