Suma bieżąca (zwana także skumulowana suma) jest dość często używany w wielu sytuacjach. Jest to metryka, która mówi, jaka jest dotychczasowa suma wartości.
Na przykład, jeśli masz miesięczne dane sprzedaży, suma bieżąca powie Ci, ile sprzedaży zostało zrealizowane do określonego dnia od pierwszego dnia miesiąca.
Istnieją również inne sytuacje, w których często stosuje się sumę bieżącą, takie jak obliczanie salda gotówkowego na wyciągach bankowych / księdze rachunkowej, liczenie kalorii w planie posiłków itp.
W programie Microsoft Excel istnieje wiele różnych sposobów obliczania sum bieżących.
Wybrana metoda będzie również zależeć od struktury danych.
Na przykład, jeśli masz proste dane tabelaryczne, możesz użyć prostej formuły SUMA, ale jeśli masz tabelę Excela, najlepiej użyć odwołań strukturalnych. W tym celu możesz również użyć dodatku Power Query.
W tym samouczku omówię wszystkie te różne metody, aby oblicz bieżące sumy w Excelu.
Więc zacznijmy!
Obliczanie sumy bieżącej z danymi tabelarycznymi
Jeśli masz dane tabelaryczne (tj. tabelę w programie Excel, która nie jest konwertowana na tabelę programu Excel), możesz użyć kilku prostych formuł do obliczenia sum bieżących.
Korzystanie z operatora dodawania
Załóżmy, że masz dane sprzedaży z datą i chcesz obliczyć sumę bieżącą w kolumnie C.
Poniżej znajdują się kroki, aby to zrobić.
Krok 1 - W komórce C2, która jest pierwszą komórką, w której chcesz uzyskać bieżącą sumę, wprowadź
=B2
Spowoduje to po prostu uzyskanie tych samych wartości sprzedaży w komórce B2.
Krok 2 - W komórce C3 wprowadź poniższą formułę:
=C2+B3
Krok 3 - Zastosuj formułę do całej kolumny. Możesz użyć uchwytu wypełnienia, aby go zaznaczyć i przeciągnąć, lub po prostu skopiować i wkleić komórkę C3 do wszystkich pozostałych komórek (co automatycznie dostosuje odwołanie i da właściwy wynik).
To da wynik, jak pokazano poniżej.
To naprawdę prosta metoda i w większości przypadków działa dobrze.
Logika jest prosta - każda komórka pobiera wartość nad nią (która jest sumą skumulowaną do poprzedniego dnia) i dodaje wartość w sąsiedniej komórce (która jest wartością sprzedaży na ten dzień).
Jest tylko jedna wada - w przypadku usunięcia dowolnego z istniejących wierszy w tym zestawie danych, wszystkie poniższe komórki zwróciłyby błąd odwołania (#REF!)
Jeśli jest to możliwe w przypadku twojego zestawu danych, użyj następnej metody, która wykorzystuje formułę SUMA
Używanie SUMA z częściowo zablokowanym odwołaniem do komórki
Załóżmy, że masz dane sprzedaży z datą i chcesz obliczyć sumę bieżącą w kolumnie C.
Poniżej znajduje się formuła SUMA, która daje bieżącą sumę.
=SUMA($B$2:B2)
Pozwólcie, że wyjaśnię, jak działa ta formuła.
W powyższej formule SUMA użyłem odniesienia, aby dodać jako $ B $ 2: B2
- $ B $ 2 - jest to odwołanie bezwzględne, co oznacza, że gdy skopiuję tę samą formułę do komórek poniżej, to odwołanie się nie zmieni. Tak więc podczas kopiowania formuły w poniższej komórce formuła zmieni się na SUMA ($ B $ 2: B3)
- B2 - to jest druga część odwołania, która jest odwołaniem względnym, co oznacza, że zmieniłoby się to, gdy skopiuję formułę w dół lub w prawo. Tak więc podczas kopiowania formuły w komórce poniżej ta wartość stanie się B3
Przeczytaj także: Bezwzględne, względne i mieszane odniesienia do komórek w programie Excel
Wspaniałą rzeczą w tej metodzie jest to, że w przypadku usunięcia dowolnego wiersza z zestawu danych ta formuła dostosuje się i nadal będzie zapewniać prawidłowe sumy bieżące.
Obliczanie sumy bieżącej w tabeli Excel
Podczas pracy z danymi tabelarycznymi w programie Excel dobrym pomysłem jest przekonwertowanie ich na tabelę programu Excel. Ułatwia zarządzanie danymi, a także ułatwia korzystanie z narzędzi, takich jak Power Query i Power Pivot.
Praca z tabelami programu Excel niesie ze sobą korzyści, takie jak odwołania strukturalne (co bardzo ułatwia odwoływanie się do danych w tabeli i używanie ich w formułach) oraz automatyczne dostosowywanie odwołań w przypadku dodawania lub usuwania danych z tabeli.
Chociaż nadal możesz użyć powyższej formuły, którą pokazałem ci w tabeli Excela, pozwól, że pokażę ci kilka lepszych metod, aby to zrobić.
Załóżmy, że masz tabelę programu Excel, jak pokazano poniżej, i chcesz obliczyć sumę bieżącą w kolumnie C.
Poniżej znajduje się formuła, która to zrobi:
=SUMA(DaneSprzedaży[[#Nagłówki];[Sprzedaż]]:[@Sprzedaż])
Powyższa formuła może wydawać się nieco długa, ale nie musisz jej pisać samodzielnie. to, co widzisz w formule sumy, nazywa się odwołaniami strukturalnymi, co jest skutecznym sposobem programu Excel na odwoływanie się do określonych punktów danych w tabeli programu Excel.
Na przykład SalesData[[#Headers],[Sale]] odwołuje się do nagłówka Sales w tabeli SalesData (SalesData to nazwa tabeli programu Excel, którą podałem podczas tworzenia tabeli)
A [@Sale] odnosi się do wartości w komórce w tym samym wierszu w kolumnie Sprzedaż.
Właśnie wyjaśniłem to tutaj dla twojego zrozumienia, ale nawet jeśli nic nie wiesz o odwołaniach strukturalnych, nadal możesz łatwo stworzyć tę formułę.
Poniżej znajdują się kroki, aby to zrobić:
- W komórce C2 wprowadź = SUMA (
- Wybierz komórkę B1, która jest nagłówkiem kolumny zawierającej wartość sprzedaży. Możesz użyć myszy lub użyć klawiszy strzałek. Zauważysz, że Excel automatycznie wprowadza strukturalne odwołanie dla tej komórki
- Dodaj : (symbol dwukropka)
- Wybierz komórkę B2. Excel ponownie automatycznie wstawi strukturalne odwołanie do komórki
- Zamknij nawias i naciśnij enter
Zauważysz również, że nie musisz kopiować formuły w całej kolumnie, tabela programu Excel zrobi to automatycznie za Ciebie.
Kolejną wielką zaletą tej metody jest to, że w przypadku dodania nowego rekordu do tego zestawu danych, tabela programu Excel automatycznie obliczy sumę bieżącą dla wszystkich nowych rekordów.
Chociaż w naszej formule uwzględniliśmy nagłówek kolumny, pamiętaj, że formuła zignoruje tekst nagłówka i uwzględni tylko dane w kolumnie
Obliczanie sumy bieżącej za pomocą dodatku Power Query
Power Query to niesamowite narzędzie, jeśli chodzi o łączenie się z bazami danych, wyodrębnianie danych z wielu źródeł i przekształcanie ich przed umieszczeniem ich w programie Excel.
Jeśli już pracujesz z dodatkiem Power Query, bardziej wydajne byłoby dodanie sum bieżących podczas przekształcania danych w samym edytorze dodatku Power Query (zamiast najpierw pobierać dane w programie Excel, a następnie dodawać sumy bieżące za pomocą dowolnego z powyższych metody opisane powyżej).
Chociaż w dodatku Power Query nie ma wbudowanej funkcji dodawania sum bieżących (co chciałbym, aby było), nadal możesz to zrobić za pomocą prostej formuły.
Załóżmy, że masz tabelę programu Excel, jak pokazano poniżej i chcesz dodać bieżące sumy do tych danych:
Poniżej znajdują się kroki, aby to zrobić:
- Wybierz dowolną komórkę w tabeli Excel
- Kliknij Dane
- Na karcie Pobierz i przekształć kliknij ikonę z tabeli/zakresu. Spowoduje to otwarcie tabeli w edytorze Power Query
- [Opcjonalnie] Jeśli kolumna Data nie jest jeszcze posortowana, kliknij ikonę filtra w kolumnie Data, a następnie kliknij Sortuj rosnąco
- Kliknij kartę Dodaj kolumnę w edytorze Power Query
- W grupie Ogólne kliknij listę rozwijaną Kolumna indeksu (nie klikaj ikony Kolumna indeksu, ale małą czarną pochyloną strzałkę tuż obok niej, aby wyświetlić więcej opcji)
- Kliknij opcję „Od 1”. Spowoduje to dodanie nowej kolumny indeksu, która zacznie się od jedności i wprowadzi liczby zwiększające się o 1 w całej kolumnie
- Kliknij ikonę „Niestandardowa kolumna” (która również znajduje się na karcie Dodaj kolumnę)
- W wyświetlonym oknie dialogowym kolumny niestandardowej wprowadź nazwę nowej kolumny. w tym przykładzie użyję nazwy „Running Total”
- W polu Formuła kolumny niestandardowej wprowadź poniższą formułę: List.Sum(List.Range(#”Dodano indeks”[Sprzedaż],0,[Indeks]))
- Upewnij się, że na dole okna dialogowego znajduje się pole wyboru z napisem „Nie wykryto błędów składniowych”
- Kliknij OK. Spowoduje to dodanie nowej kolumny bieżącej sumy
- Usuń kolumnę indeksu
- Kliknij kartę Plik, a następnie kliknij „Zamknij i załaduj”
Powyższe kroki spowodowały wstawienie nowego arkusza do skoroszytu z tabelą zawierającą bieżące sumy.
Teraz, jeśli myślisz, że to zbyt wiele kroków w porównaniu do poprzednich metod używania prostych formuł, masz rację.
Jeśli masz już zestaw danych i wszystko, co musisz zrobić, to dodać sumy bieżące, lepiej nie używać dodatku Power Query.
Korzystanie z dodatku Power Query ma sens, gdy trzeba wyodrębnić dane z bazy danych lub połączyć dane z wielu różnych skoroszytów, a także dodać do nich sumy bieżące.
Ponadto po wykonaniu tej automatyzacji za pomocą dodatku Power Query następnym razem, gdy zestaw danych zmieni się, nie trzeba tego robić ponownie, wystarczy odświeżyć zapytanie, aby uzyskać wynik na podstawie nowego zestawu danych.
Jak to działa?
Teraz szybko wyjaśnię, co dzieje się w tej metodzie.
Pierwszą czynnością, jaką wykonujemy w edytorze Power Query, jest wstawienie kolumny indeksu rozpoczynającej się od jednego i zwiększanej o jeden w miarę przemieszczania się w dół komórek.
Robimy to, ponieważ musimy użyć tej kolumny podczas obliczania sumy bieżącej w innej kolumnie, którą wstawiamy w następnym kroku.
Następnie wstawiamy niestandardową kolumnę i używamy poniższej formuły
List.Sum(List.Range(#"Dodano indeks"[Sprzedaż],0,[Indeks]))
Jest to formuła List.Sum, która daje sumę określonego w niej zakresu.
A ten zakres jest określany za pomocą funkcji List.Range.
Funkcja List.Range podaje jako wynik określony zakres w kolumnie sprzedaży, a zakres ten zmienia się w zależności od wartości Index. Na przykład dla pierwszego rekordu zakres byłby po prostu wartością pierwszej sprzedaży. A gdy schodzisz w dół komórek, ten zakres się rozszerza.
A więc dla pierwszej komórki. Lista.Sum dałaby tylko sumę pierwszej wartości sprzedaży, a dla drugiej komórki sumę dwóch pierwszych wartości sprzedaży i tak dalej.
Chociaż ta metoda działa dobrze, działa bardzo wolno przy dużych zestawach danych - tysiącach wierszy. Jeśli masz do czynienia z dużym zestawem danych i chcesz dodać do niego bieżące sumy, zapoznaj się z tym samouczkiem, który pokazuje inne, szybsze metody.Obliczanie sumy bieżącej na podstawie kryteriów
Do tej pory widzieliśmy przykłady, w których obliczyliśmy sumę bieżącą dla wszystkich wartości w kolumnie.
Ale mogą wystąpić przypadki, w których chcesz obliczyć sumę bieżącą dla określonych rekordów.
Na przykład poniżej mam zestaw danych i chcę obliczyć sumę bieżącą dla drukarek i skanerów osobno w dwóch różnych kolumnach.
Można to zrobić za pomocą formuły SUMA.JEŻELI, która oblicza sumę bieżącą, upewniając się, że spełniony jest określony warunek.
Poniżej znajduje się formuła, która zrobi to dla kolumn Drukarka:
=SUMA.JEŻELI($C$2:C2,$D$1,$B$2:B2)
Podobnie, aby obliczyć sumę bieżącą dla skanerów, użyj poniższego wzoru:
=SUMA.JEŻELI($C$2:C2,$E$1,$B$2:B2)
W powyższych formułach użyłem SUMA.JEŻELI, co dałoby mi sumę w zakresie przy spełnieniu określonych kryteriów.
Formuła przyjmuje trzy argumenty:
- zasięg: jest to zakres kryteriów, który zostanie sprawdzony pod kątem określonych kryteriów
- kryteria: jest to kryterium, które zostanie sprawdzone tylko w przypadku spełnienia tego kryterium, zostaną dodane wartości w trzecim argumencie, który jest zakresem sumy
- [zakres_sum]: jest to sumaryczny zakres, z którego zostaną dodane wartości, jeśli kryteria zostaną spełnione
Również w zasięg oraz sum_zakres argumentem, zablokowałem drugą część odwołania, aby w miarę przechodzenia w dół komórek zakres wciąż się rozszerzał. To pozwala nam rozważać i dodawać wartości tylko do tego zakresu (stąd bieżące sumy).
W tej formule jako kryterium użyłem kolumny nagłówka (Drukarka i Skaner). możesz również na stałe zakodować kryteria, jeśli nagłówki kolumn nie są dokładnie takie same jak tekst kryteriów.
Jeśli masz wiele warunków, które musisz sprawdzić, możesz użyć formuły SUMIFS.
Suma bieżąca w tabelach przestawnych
Jeśli chcesz dodać sumy bieżące w wyniku tabeli przestawnej, możesz to łatwo zrobić, korzystając z wbudowanej funkcji w tabelach przestawnych.
Załóżmy, że masz tabelę przestawną, jak pokazano poniżej, w której mam datę w jednej kolumnie i wartość sprzedaży w drugiej kolumnie.
Poniżej znajdują się kroki, aby dodać dodatkową kolumnę, która pokaże bieżącą sumę sprzedaży według daty:
- Przeciągnij pole Sprzedaż i umieść je w obszarze Wartość.
- Spowoduje to dodanie kolejnej kolumny z wartościami sprzedaży
- Kliknij opcję Suma sprzedaży2 w obszarze Wartość
- Kliknij opcję „Ustawienia pola wartości”
- W oknie dialogowym Ustawienia pola wartości zmień nazwę niestandardową na „Podsumowania bieżące”
- Kliknij kartę „Pokaż wartość jako”
- Z listy rozwijanej Pokaż wartość jako wybierz opcję „Suma bieżąca w”
- W opcjach pola podstawowego upewnij się, że wybrano opcję Data
- Kliknij OK
Powyższe kroki zmienią drugą kolumnę sprzedaży w kolumnę Suma bieżąca.
Oto niektóre ze sposobów obliczania sumy bieżącej w programie Excel. Jeśli masz dane w formacie tabelarycznym, możesz użyć prostych formuł, a jeśli masz tabelę programu Excel, możesz użyć formuł korzystających z odwołań strukturalnych.
Omówiłem również sposób obliczania sumy bieżącej za pomocą dodatku Power Query i tabel przestawnych.
Mam nadzieję, że ten samouczek okazał się przydatny.
