Pobierz przykładowy skoroszyt
W tym samouczku zademonstrujemy, jak obliczyć sumy częściowe według grup za pomocą funkcji SUMIFS w programie Excel i Arkuszach Google.
Tabela sum częściowych według kategorii lub grupy
Najpierw zademonstrujemy, jak utworzyć dynamiczną tabelę podsumowania sum częściowych z zakresu danych w programie Excel 365 lub Arkuszach Google.
Używamy funkcji UNIQUE i SUMIFS, aby automatycznie sumować Liczba produktów za pomocą Grupa produktów:
| 1 | =SUMA.WARUNKI(C3:C11;B3:B11;E3) |
Aby utworzyć tę tabelę sum częściowych, używamy standardowej aplikacji funkcji SUMIFS do sumowania Liczba produktów które pasują do każdego Grupa produktów. Zanim jednak będzie to możliwe, musimy stworzyć listę unikalnych Grupy produktów. Użytkownicy programu Microsoft Excel 365 i Arkuszy Google mają dostęp do UNIKALNEJ funkcji do tworzenia dynamicznej listy unikalnych wartości z zakresu komórek. W tym przykładzie do komórki E3 dodajemy następującą formułę:
| 1 | =UNIKALNE(B3:B11) |
Po wprowadzeniu tej formuły pod komórką zostanie automatycznie utworzona lista, aby wyświetlić wszystkie unikalne wartości znalezione w Grupa produktów zakres danych. W tym przykładzie lista rozszerzyła się o E3:E5, aby pokazać wszystkie 3 unikalne Grupa produktów wartości.
Jest to dynamiczna funkcja tablicowa, w której rozmiar listy wyników nie musi być definiowany i będzie ona automatycznie zmniejszać się i powiększać wraz ze zmianą wartości danych wejściowych.
Zauważ, że w Excel 365 funkcja UNIQUE nie rozróżnia wielkości liter, ale w Arkuszach Google tak. Rozważ listę {„A”; "a"; "B"; "C"}. Wyjście funkcji UNIQUE zależy od programu:
- {"A"; "B"; „c”} w programie Excel 365
- {"A"; "a"; "B"; „c”} w Arkuszach Google
Jeśli używasz wersji programu Excel starszej niż Excel 365, musisz zastosować inne podejście. Zostało to omówione w następnej sekcji.
Tabela sum częściowych według kategorii lub grupy - przed Excelem 365
Jeśli używasz wersji programu Excel starszej niż Excel 365, funkcja UNIQUE nie jest dostępna do użycia. Aby odtworzyć to samo zachowanie, możesz połączyć funkcję INDEKS i funkcję PODAJ.POZYCJĘ z funkcją LICZ.JEŻELI, aby utworzyć formułę tablicową, która utworzy listę unikatowych wartości z zakresu komórek:
| 1 | {=INDEKS($3$:$B$11,MATCH(0,COUNTIF($E$2:E2,$B$3:$B$11),0))} |
Aby ta formuła działała, stałe odwołania do komórek muszą być napisane ostrożnie, a funkcja LICZ.JEŻELI odwołuje się do zakresu $ E $ 2: E2, który jest zakresem od E2 do komórki nad komórką zawierającą formułę.
Formułę należy również wprowadzić jako formułę tablicową, naciskając klawisze CTRL + SHIFT + ENTER po jej zapisaniu. Ta formuła jest 1-komórkowa formuła tablicowa, które można następnie skopiować i wkleić do komórek E4, E5 itd. Nie należy wprowadzać tego jako formuły tablicowej dla całego zakresu E3:E5 w jednej akcji.
W ten sam sposób, jak w poprzednim przykładzie, funkcja SUMIFS jest następnie używana do sumowania częściowego Liczba produktów za pomocą Grupa produktów:
| 1 | =SUMA.WARUNKI(C3:C11;B3:B11;E3) |
Suma według kategorii lub grupy - sumy częściowe w tabelach danych
Jako alternatywę dla metody tabeli podsumowującej pokazanej powyżej, możemy dodać sumy częściowe bezpośrednio do tabeli danych. Zademonstrujemy to, używając funkcji IF wraz z funkcją SUMIFS, aby dodać Suma częściowa według grupy do oryginalnej tabeli danych.
| 1 | =JEŻELI(B3=B2,"",SUMA.WARUNKI(C3:C11;B3:B11;B3)) |
W tym przykładzie użyto funkcji SUMIFS zagnieżdżonej w funkcji JEŻELI. Podzielmy przykład na kroki:
Aby dodać statystyki podsumowujące bezpośrednio do tabeli danych, możemy użyć funkcji SUMIFS. Zaczynamy od zsumowania Liczba produktów które pasują do odpowiednich Grupa produktów:
| 1 | =SUMA.WARUNKI(C3:C11;B3:B11;B3) |
Ta formuła generuje sumę częściową dla każdego wiersza danych. Aby wyświetlić sumy częściowe tylko w pierwszym wierszu każdego z nich Grupa produktów, używamy funkcji JEŻELI. Pamiętaj, że dane muszą być już posortowane według Grupa produktów aby upewnić się, że sumy częściowe są wyświetlane poprawnie.
| 1 | =JEŻELI(B3=B2,"",SUMA.WARUNKI(C3:C11;B3:B11;B3)) |
Funkcja IF porównuje każdy wiersz danych Grupa produktów wartość z wierszem danych powyżej, a jeśli mają taką samą wartość, wyprowadza pustą komórkę („”).
Jeśli Grupa produktów wartości są różne, wyświetlana jest suma. W ten sposób każdy Grupa produktów suma jest wyświetlana tylko raz (w wierszu pierwszego wystąpienia).
Sortowanie zbiorów danych według grup
Jeśli dane nie są jeszcze posortowane, nadal możemy użyć tej samej formuły dla sumy częściowej.
Powyższy zbiór danych nie jest posortowany według Grupa produktów, więc Suma częściowa według grupy kolumna wyświetla każdą sumę częściową więcej niż raz. Aby uzyskać dane w żądanym formacie, możemy wybrać tabelę danych i kliknąć „Sortuj od A do Z”.
Blokowanie odwołań do komórek
Aby ułatwić czytanie naszych formuł, pokazaliśmy niektóre formuły bez zablokowanych odwołań do komórek:
| 1 | =JEŻELI(B3=B2,"",SUMA.WARUNKI(C3:C11;B3:B11;B3)) |
Ale te formuły nie będą działać poprawnie po skopiowaniu i wklejeniu w innym miejscu pliku. Zamiast tego powinieneś używać zablokowanych odwołań do komórek w następujący sposób:
| 1 | =JEŻELI(B3=B2,"",SUMA.WARUNKI($C$3:$C$11;$B$3:$B$11;B3)) |
Przeczytaj nasz artykuł na temat blokowania odwołań do komórek, aby dowiedzieć się więcej.
Używanie tabel przestawnych do wyświetlania sum częściowych
W celu usunięcia wymogu wstępnego sortowania danych według Grupa produktów, zamiast tego możemy użyć mocy tabel przestawnych do podsumowania danych. Tabele przestawne automatycznie obliczają sumy częściowe i wyświetlają sumy i sumy częściowe w kilku różnych formatach.
Suma według kategorii lub grupy w Arkuszach Google
Te formuły działają tak samo w Arkuszach Google, jak w programie Excel. Jednak w funkcji UNIQUE w Arkuszach Google rozróżniana jest wielkość liter.
