Pobierz przykładowy skoroszyt
Ten samouczek pokazuje, jak używać Funkcja Excel SUMA CZĘŚCIOWA w programie Excel, aby obliczyć statystyki podsumowujące.
SUMA CZĘŚCIOWA Przegląd funkcji
Funkcja SUMY.CZĘŚCIOWE Oblicza statystykę podsumowującą dla serii danych. Dostępne statystyki obejmują między innymi średnią, odchylenie standardowe, liczbę, min i max. Zobacz pełną listę poniżej w sekcji wejść funkcji:
Aby użyć funkcji arkusza programu Excel SUMA CZĘŚCIOWA, wybierz komórkę i wpisz:
(Zwróć uwagę, jak pojawiają się dane wejściowe formuły)
SUBTOTAL Składnia funkcji i wejścia:
| 1 | = SUMA CZĘŚCIOWA(numer_funkcji;ODN1) |
numer_funkcji – liczba reprezentująca operację do wykonania.
REF1 - Zakresy lub referencje zawierające dane do obliczenia.
Co to jest funkcja SUMA CZĘŚCIOWA?
SUMA CZĘŚCIOWA jest jedną z unikalnych funkcji w arkuszach kalkulacyjnych, ponieważ pozwala odróżnić komórki ukryte od nieukrytych. Może to okazać się bardzo pomocne, gdy mamy do czynienia z filtrowanymi zakresami lub gdy trzeba skonfigurować obliczenia na podstawie różnych wyborów użytkowników. Ponieważ wie również, że ma ignorować inne funkcje SUMY CZĘŚCIOWE w swoich obliczeniach, możemy go również używać w dużych podsumowanych danych bez obawy o podwójne liczenie.
Podsumowanie podstawowe z SUBTOTAL
Załóżmy, że masz tabelę posortowanych sprzedaży produktów i chcesz utworzyć sumy dla każdego produktu, a także utworzyć sumę ogólną. Możesz użyć tabeli przestawnej lub wstawić niektóre formuły. Rozważ ten układ:
Umieściłem kilka funkcji SUBTOTAL w komórkach B5 i B8, które wyglądają jak
| 1 | =SUMA CZĘŚCIOWA(9; B2:B4) |
Ze składni możesz użyć różnych liczb jako pierwszego argumentu. W naszym konkretnym przypadku używamy 9, aby wskazać, że chcemy zsumować.
Skupmy się na komórce B9. Zawiera tę formułę, która obejmuje cały zakres danych kolumny B, ale nie obejmuje innych sum częściowych.
| 1 | =SUMA CZĘŚCIOWA(9; B2:B8) |
UWAGA: Jeśli nie chcesz samodzielnie pisać wszystkich formuł podsumowujących, możesz przejść do wstążki Dane i użyć kreatora Konspekt - sumy częściowe. Automatycznie wstawi wiersze i umieści formuły za Ciebie.
Różnica w pierwszych argumentach
W pierwszym przykładzie użyliśmy 9, aby wskazać, że chcemy zsumować. Różnica między używaniem 9 i 109 polega na tym, jak chcemy, aby funkcja obsługiwała ukryte wiersze. Jeśli użyjesz oznaczeń 1XX, funkcja nie będzie zawierać wierszy, które zostały ręcznie ukryte lub przefiltrowane.
Oto nasz stół z wcześniej. Przesunęliśmy funkcje, aby zobaczyć różnicę między 9 a 109 argumentami. Gdy wszystko jest widoczne, wyniki są takie same.
Jeśli zastosujemy filtr, aby odfiltrować wartość 6 w kolumnie B, obie funkcje pozostaną takie same.
Jeśli ręcznie ukryjemy wiersze, zobaczymy różnicę. Funkcja 109 była w stanie zignorować ukryty wiersz, podczas gdy funkcja 9 nie.
Zmień operację matematyczną za pomocą SUBTOTAL
Czasami możesz chcieć dać użytkownikowi możliwość zmiany rodzaju wykonywanych obliczeń. Na przykład, czy chcą otrzymać sumę lub średnią. Ponieważ SUMA.CZĘŚCIOWA kontroluje operację matematyczną za pomocą numeru argumentu, możesz zapisać to w jednej formule. Oto nasza konfiguracja:
Stworzyliśmy listę rozwijaną w D2, w której użytkownik może wybrać „Suma” lub „Średnia”. Wzór w E2 to:
| 1 | =SUMA CZĘŚCIOWA(JEŻELI(D2="Średnia",1,JEŻELI(D2="Suma",9)),B2:B4) |
W tym przypadku funkcja JEŻELI określi, który argument liczbowy należy podać do SUMY CZĘŚCIOWEJ. Jeśli A5 jest „Średnia”, to wygeneruje 1, a SUMA CZĘŚCIOWA da średnią B2:B4. Lub, jeśli A5 równa się „Suma”, to JEŻELI wypisuje 9 i otrzymujemy inny wynik.
Możesz rozszerzyć tę funkcję, używając tabeli przeglądowej, aby wyświetlić jeszcze więcej typów operacji, które chcesz wykonać. Twoja tabela przeglądowa może wyglądać tak
Następnie możesz zmienić formułę w E2 na
| 1 | =SUMA CZĘŚCIOWA(WYSZUKAJ.PIONOWO(A5;Tabela przeglądowa; 2; 0); B2:B4) |
Formuły warunkowe z SUBTOTAL
Chociaż SUBTOTAL ma wiele operacji, które może wykonać, nie może samodzielnie sprawdzać kryteriów. Możemy jednak użyć go w kolumnie pomocniczej do wykonania tej operacji. Gdy masz kolumnę danych, o której wiesz, że będą zawsze masz w sobie fragment danych, możesz użyć funkcji SUMY CZĘŚCIOWE, aby wykryć ukryte wiersze.
Oto tabela, z którą będziemy pracować w tym przykładzie. Docelowo chcielibyśmy mieć możliwość zsumowania wartości „Apple”, ale także umożliwić użytkownikowi filtrowanie kolumny Ilość.
Najpierw utwórz kolumnę pomocniczą, która będzie zawierać funkcję SUBTOTAL. W C2 wzór to:
| 1 | =SUMA CZĘŚCIOWA(103, A2) |
Pamiętaj, że 103 oznacza, że chcemy wykonać COUNTA. Polecam użycie funkcji ILE.NIEPUSTYCH, ponieważ możesz wtedy wypełnić komórkę referencyjną A2 albo numery lub tekst. Będziesz mieć teraz tabelę, która wygląda tak:
Na początku nie wydaje się to pomocne, ponieważ wszystkie wartości to tylko 1. Jeśli jednak ukryjemy wiersz 3, to „1” w C3 zmieni się na 0, ponieważ wskazuje ukryty wiersz. Chociaż niemożliwe jest uzyskanie obrazu przedstawiającego określoną wartość ukrytej komórki, możesz to sprawdzić, ukrywając wiersz, a następnie pisząc podstawową formułę, taką jak ta, aby sprawdzić.
| 1 | =C3 |
Teraz, gdy mamy kolumnę, której wartość będzie się zmieniać w zależności od tego, czy jest ukryta, czy nie, jesteśmy gotowi do napisania końcowego równania. Tak będą wyglądać nasze SUMIFy
W tej formule będziemy sumować wartości z kolumny B tylko wtedy, gdy kolumna A równa się „Jabłko”, oraz wartość w kolumnie C to 1 (czyli wiersz nie jest ukryty). Powiedzmy, że nasz użytkownik chce odfiltrować 600, ponieważ wydaje się nienormalnie wysoki. Widzimy, że nasza formuła daje poprawny wynik.
Dzięki tej możliwości możesz zastosować czek do LICZ., SUMI., a nawet SUMPRODUCT. Dodajesz możliwość umożliwienia użytkownikom kontrolowania niektórych fragmentatorów tabel i jesteś gotowy do stworzenia niesamowitego pulpitu nawigacyjnego.
SUMA CZĘŚCIOWA w Arkuszach Google
Funkcja SUBTOTAL działa dokładnie tak samo w Arkuszach Google, jak w programie Excel:
SUMA CZĘŚCIOWA Przykłady w VBA
Możesz także użyć funkcji SUMY CZĘŚCIOWE w VBA. Rodzaj:application.worksheetfunction.subtotal(function_num,reh1)
Wykonywanie następujących instrukcji VBA
| 1234567891011121314151617 | Zakres("C7") = Application.WorksheetFunction.Subtotal(1, Range("C2:C5"))Zakres("C8") = Application.WorksheetFunction.Subtotal(2, Range("C2:C5"))Zakres("C9") = Application.WorksheetFunction.Subtotal(4, Range("C2:C5"))Zakres("C10") = Application.WorksheetFunction.Subtotal(5, Range("C2:C5"))Zakres("C11") = Application.WorksheetFunction.Subtotal(9, Range("C2:CE5"))Range("D7") = Aplikacja.WorksheetFunction.Subtotal(1, Range("D2:D5"))Range("D8") = Aplikacja.WorksheetFunction.Subtotal(2, Range("D2:D5"))Range("D9") = Aplikacja.WorksheetFunction.Subtotal(4, Range("D2:D5"))Zakres("D10") = Application.WorksheetFunction.Subtotal(5, Range("D2:D5"))Zakres("D11") = Application.WorksheetFunction.Subtotal(9, Range("D2:D5"))Range("E7") = Aplikacja.WorksheetFunction.Subtotal(1, Range("E2:E5"))Range("E8") = Aplikacja.WorksheetFunction.Subtotal(2, Range("E2:E5"))Range("E9") = Aplikacja.WorksheetFunction.Subtotal(4, Range("E2:E5"))Range("E10") = Aplikacja.WorksheetFunction.Subtotal(5, Range("E2:E5"))Range("E11") = Aplikacja.WorksheetFunction.Subtotal(9, Range("E2:E5")) |
przyniesie następujące wyniki
W przypadku argumentów funkcji (numer_funkcji itp.) można wprowadzić je bezpośrednio do funkcji lub zdefiniować zmienne, które będą używane w zamian.
Wróć do listy wszystkich funkcji w Excelu
