Prawdopodobnie natkniesz się na potrzebę sum bieżących, jeśli masz do czynienia z jakimkolwiek rodzajem danych dziennych.
Wyobraź sobie, że śledzisz sprzedaż każdego dnia. Twoje dane zawierają wiersz dla każdej daty z całkowitą kwotą sprzedaży, ale może chcesz znać całkowitą sprzedaż w miesiącu na każdy dzień. Jest to suma bieżąca, jest to suma wszystkich sprzedaży do i włącznie z bieżącym dniem sprzedaży.
W tym poście omówimy wiele sposobów na obliczenie sumy bieżącej dla danych dziennych. Dowiemy się jak używać formuł arkusza, tabel przestawnych, Power Pivot z DAX oraz Power Query.
Dowiemy się również co dzieje się z obliczeniami sumy bieżącej podczas wstawiania lub usuwania wierszy danych oraz jak aktualizować wyniki.
Pobierz plik ze wszystkimi przykładami.
Table of Contents
Obliczanie sum bieżących za pomocą prostej formuły
Możliwe jest stworzenie podstawowej formuły sumy bieżącej za pomocą operatora +.
Jednakże będziemy musieli użyć dwóch różnych formuł, aby wykonać zadanie.
- =C3 będzie pierwszą formułą i znajdzie się tylko w pierwszym wierszu sumy bieżącej.
- =C4+D3 będzie w drugim rzędzie i może być skopiowany w dół pozostałych rzędów dla bieżącej sumy.
Formuła w naszym pierwszym rzędzie nie może dodać komórki powyżej do sumy, ponieważ zawiera ona wartość tekstową dla nagłówka kolumny. Spowodowałoby to wyświetlenie błędu #VALUE! w bieżącej sumie, ponieważ + nie może obsługiwać wartości tekstowych. Unikniemy tego, stosując inną formułę w pierwszym wierszu, która nie odwołuje się do komórki powyżej.
Co się stanie z sumą bieżącą, gdy wstawimy lub usuniemy wiersze w naszych danych?
Wstawienie nowego wiersza spowoduje lukę w sumie bieżącej. Aby to naprawić, musimy skopiować formułę z pierwszej komórki powyżej nowo wstawionego wiersza aż do ostatniego wiersza.
Usuwanie wierszy spowoduje błędy #REF! ponieważ usunięcie wiersza oznacza usunięcie komórki, do której odwołuje się formuła pod nim. Aby to naprawić, musimy skopiować formułę z ostatniej wolnej od błędów komórki aż do ostatniego wiersza.
Bieganie po sumach za pomocą formuły SUM
Możemy uniknąć niezręczności związanej z użyciem dwóch różnych formuł w naszej kolumnie sumy bieżącej, używając funkcji SUM zamiast operatora +. Gdy funkcja SUM napotka komórkę tekstową, potraktuje ją tak samo, jakby zawierała 0.
W ten sposób możemy użyć poniższej formuły jednolicie dla każdego wiersza, łącznie z pierwszym.
=SUM(C3,D2)
Ta formuła odniesie się do nagłówka kolumny zawierającej tekst dla pierwszego wiersza, ale to jest w porządku, ponieważ jest traktowana jak 0.
Przy wstawianiu lub usuwaniu wierszy nadal będziemy napotykać te same problemy z pustymi komórkami i błędami. Możemy je rozwiązać w taki sam sposób jak w przypadku sumowania metodą prostej formuły.
Uruchamianie sum z częściowo stałym zakresem
Inną opcją funkcji SUM jest odwołanie się tylko do kolumny Sprzedaż i użycie częściowo stałego zakresu.
Jeśli użyjemy następującej formuły =SUM($C$3:C3), możemy skopiować i wkleić ją w dół zakresu. Nie będzie ona odwoływała się do nagłówków kolumn, a zakres będzie się powiększał do każdego wiersza.
Niestety, to również będzie miało te same problemy (i rozwiązania) z wstawianiem lub usuwaniem wierszy.
Usuwanie sum przy użyciu względnego zakresu nazwanego
Możemy uniknąć problemów z wstawianiem i usuwaniem wierszy z naszych danych, jeśli użyjemy względnego zakresu nazwanego. Będzie on odnosił się do komórki znajdującej się bezpośrednio powyżej, bez względu na to, ile wierszy wstawimy lub usuniemy.
Jest to sztuczka, która polega na tymczasowym przełączeniu stylu odniesienia Excela z A1 na R1C1. Następnie zdefiniowanie zakresu nazwanego przy użyciu notacji R1C1. Następnie przełączenie stylu odniesienia z powrotem na A1.
W stylu odniesienia R1C1 do komórek odwołujemy się na podstawie tego, jak daleko są one od komórki, do której używamy odwołania. Na przykład =RC odnosi się do komórki znajdującej się 2 w górę i 3 w prawo od komórki używającej tej formuły.
Możemy użyć tego względnego odwołania do utworzenia zakresu nazwanego, który zawsze znajduje się jedną komórkę powyżej komórki odwołującej się do formuły =RC.
Aby przełączyć styl odwołania, przejdź na kartę Plik, a następnie wybierz Opcje. Przejdź do sekcji Formuła w menu Opcje programu Excel i zaznacz pole Styl odniesienia R1C1, a następnie naciśnij przycisk OK.
Teraz możemy dodać nasz nazwany zakres. Przechodzimy na kartę Formuły na wstążce Excela i wybieramy polecenie Zdefiniuj nazwę.
Jako nazwę zakresu wpisujemy nazwę typu „Powyżej”. Dodaj formułę =RC do pola Odnosi się do i naciśnij przycisk OK.
Możemy teraz przełączyć Excela z powrotem na domyślny styl odwołań. Przechodzimy na kartę Plik > Opcje sekcji Formuła > odznaczamy pole Styl odniesienia R1C1 > następnie naciskamy przycisk OK.
Teraz możemy użyć formuły =SUM(,Powyżej) w naszej kolumnie sumy bieżącej.
Nazwany zakres Powyżej będzie zawsze odnosił się do komórki znajdującej się bezpośrednio powyżej. Kiedy wstawiamy lub usuwamy wiersze, względny nazwany zakres dostosuje się odpowiednio i nie jest potrzebne żadne działanie.
W rzeczywistości, jeśli umieścimy nasze dane w tabeli Excela, formuła automatycznie wypełni się dla nowych wierszy, ponieważ formuła jest jednolita dla całej kolumny. Nie ma potrzeby kopiowania żadnych formuł.
Przeliczanie sum za pomocą tabeli przestawnej
Tabele przestawne są bardzo przydatne do podsumowania każdego rodzaju danych. Jest w nich coś więcej niż tylko dodawanie, liczenie i znajdowanie średnich. Jest w nich wbudowanych wiele innych typów obliczeń, a w rzeczywistości jest tam również obliczanie sumy bieżącej!
Po pierwsze, musimy wstawić tabelę przestawną na podstawie danych. Zaznaczamy komórkę wewnątrz danych i przechodzimy na kartę Wstawianie i wybieramy polecenie Tabela przestawna. Następnie przejdź przez okno Tworzenie tabeli przestawnej, aby wybrać miejsce, w którym ma się znaleźć tabela przestawna – albo w nowym arkuszu, albo gdzieś w istniejącym.
Dodaj pole Data do obszaru Wiersze tabeli przestawnej, a następnie dodaj pole Sprzedaż do obszaru Wartości tabeli przestawnej. Teraz dodaj kolejną instancję pola Sales do obszaru Rows.
Powinniśmy teraz mieć dwa identyczne pola Sprzedaż, z jednym z nich oznaczonym jako Suma sprzedaży2. W każdej chwili możemy zmienić nazwę tej etykiety, wpisując nad nią coś w rodzaju Running Total.
Kliknij prawym przyciskiem myszy na dowolną wartość w polu Suma sprzedaży2 i wybierz Pokaż wartość jako, a następnie wybierz Running Total In.
Chcemy wyświetlić sumę bieżącą według daty, dlatego w kolejnym oknie musimy wybrać Datę jako Pole bazowe.
To wszystko, mamy teraz nową kalkulację, która wyświetla sumę bieżącą naszej sprzedaży wewnątrz tabeli przestawnej.
Co się stanie, jeśli dodamy lub usuniemy wiersz w naszych danych źródłowych, jak to wpłynie na sumę bieżącą? Obliczenia tabeli przestawnej są dynamiczne i będą uwzględniać nowe dane w obliczeniach sumy bieżącej, musimy tylko odświeżyć tabelę przestawną.
Kliknij prawym przyciskiem myszy w dowolnym miejscu tabeli przestawnej i wybierz Odśwież z menu.
Obliczanie sum bieżących za pomocą Power Pivot i DAX Measures
Pierwsze kroki są dokładnie takie same, jak w przypadku zwykłej tabeli przestawnej.
Zaznacz komórkę w danych, przejdź na kartę Wstawianie i wybierz polecenie Tabela przestawna.
Po przejściu do menu Create PivotTable (Utwórz tabelę przestawną) zaznacz pole Add this data to the Data Model (Dodaj te dane do modelu danych), aby dodać dane do modelu danych i umożliwić ich użycie w tabeli przestawnej.
Umieść pole Data w obszarze Wiersze, a pole Sprzedaż w obszarze Wartości tabeli przestawnej.
W przypadku tabeli przestawnej Power Pivot będziemy musieli utworzyć dodatkowe obliczenia za pomocą języka DAX. Kliknij prawym przyciskiem myszy na nazwę tabeli w oknie Pola tabeli przestawnej, a następnie wybierz Dodaj miarę, aby utworzyć nowe obliczenie. Uwaga, ta funkcja jest dostępna tylko w modelu danych.
=CALCULATE ( SUM ( Sales ), FILTER ( ALL (Sales ), Sales Teraz możemy utworzyć naszą nową miarę sumy bieżącej.
- W oknie miary musimy dodać nazwę miary. W tym przypadku możemy nazwać nową miarę jako Running Total.
- Musimy również dodać powyższą formułę w polu Formula.
- Ciekawą rzeczą w Power Pivot jest możliwość przypisania formatu liczb do miary. My możemy wybrać format Currency dla naszej miary. Za każdym razem, gdy będziemy używać tej miary w tabeli przestawnej, format zostanie automatycznie zastosowany.
Naciśnij przycisk OK i nowa miara zostanie utworzona.
W oknie Pola tabeli przestawnej pojawi się nowe pole. Ma ono małą ikonę fx po lewej stronie, aby zaznaczyć, że jest to miara, a nie zwykłe pole w danych.
Możemy użyć tego nowego pola jak każdego innego i przeciągnąć je do obszaru Wartości, aby dodać nasze bieżące obliczenia sumy do tabeli przestawnej.
Co dzieje się z sumą bieżącą, gdy dodajemy lub usuwamy dane z tabeli źródłowej? Tak jak w przypadku zwykłej tabeli przestawnej, musimy po prostu kliknąć prawym przyciskiem myszy na tabeli przestawnej i wybrać opcję Odśwież, aby zaktualizować obliczenia.
Obliczanie sum bieżących za pomocą zapytania Power Query
Możemy również dodać sumy bieżące do naszych danych za pomocą zapytania Power Query.
Najpierw musimy zaimportować tabelę do zapytania Power Query. Zaznaczamy tabelę z danymi i przechodzimy do zakładki Data i wybieramy opcję From Table/Range. Spowoduje to otwarcie edytora zapytań Power Query.
Następnie możemy posortować nasze dane według daty. Jest to opcjonalny krok, który możemy dodać, aby w przypadku zmiany kolejności naszych danych źródłowych, suma bieżąca nadal wyświetlała się według daty.
Kliknij na przełącznik filtra w nagłówku kolumny daty i wybierz Sortowanie rosnące z opcji.
Musimy dodać kolumnę indeksu. Będzie ona później używana w obliczeniach sumy bieżącej. Przejdź do zakładki Dodaj kolumnę i kliknij na małą strzałkę obok Kolumna indeksu, aby wstawić indeks zaczynający się od 1 w pierwszym wierszu.
Musimy dodać nową kolumnę do naszego zapytania, aby obliczyć sumę bieżącą. Przechodzimy na kartę Dodaj kolumnę i wybieramy polecenie Kolumna niestandardowa.
Możemy nazwać kolumnę jako Suma biegnąca i dodać następującą formułę.
Funkcja List.Range tworzy listę wartości z kolumny Sales zaczynając od 1 wiersza (0th item), która obejmuje pewną liczbę wierszy w zależności od wartości w kolumnie index.
Funkcja List.Sum następnie sumuje tę listę wartości, która jest naszą bieżącą sumą.
Nie potrzebujemy już kolumny index, spełniła ona swoją rolę i możemy ją usunąć. Klikamy prawym przyciskiem myszy na nagłówek kolumny i z opcji wybieramy Usuń.
Mamy naszą bieżącą sumę i kończymy pracę z edytorem zapytań. Możemy zamknąć zapytanie i załadować wyniki do nowego arkusza. Przejdź do zakładki Strona główna edytora zapytań i naciśnij przycisk Zamknij & Załaduj.
Co się stanie z sumą bieżącą, gdy dodamy lub usuniemy wiersze z naszych danych źródłowych? Będziemy musieli odświeżyć tabelę wyjściową zapytania Power Query, aby zaktualizować sumę bieżącą o zmiany. Kliknij prawym przyciskiem myszy w dowolnym miejscu tabeli i wybierz polecenie Odśwież, aby zaktualizować tabelę.
Po wykonaniu opcjonalnego kroku sortowania powyżej, jeśli dodamy daty poza kolejnością do danych źródłowych, power query posortuje je według daty i zwróci prawidłową kolejność według daty dla sumy bieżącej.
Wnioski
Istnieje wiele różnych opcji obliczania sum bieżących w Excelu.
Zbadaliśmy opcje obejmujące formuły w arkuszu, tabele przestawne, formuły DAX Power Pivot oraz Power Query. Niektóre z nich oferują bardziej solidne rozwiązanie podczas dodawania lub usuwania wierszy z danych, inne metody oferują łatwiejsze wdrożenie.
Proste formuły w arkuszu są łatwe do skonfigurowania, ale nie poradzą sobie z łatwym wstawianiem lub usuwaniem nowych wierszy danych. Inne rozwiązania, takie jak tabele przestawne, DAX i power query są bardziej wytrzymałe i łatwo obsługują wstawianie lub usuwanie wierszy danych, ale są trudniejsze do skonfigurowania.
Dobrze jest być świadomym zalet i wad każdej z metod i wybrać tę, która jest najbardziej odpowiednia. Jeśli nie będziesz wstawiać ani usuwać nowych danych, formuły arkusza mogą być najlepszym rozwiązaniem.