prawdopodobnie natkniesz się na potrzebę uruchomionych Sum, jeśli masz do czynienia z wszelkiego rodzaju codziennymi danymi.

wyobraź sobie, że śledzisz sprzedaż każdego dnia. Twoje dane zawierają wiersz dla każdej daty z całkowitą kwotą sprzedaży, ale być może chcesz poznać całkowitą sprzedaż za miesiąc każdego dnia. Jest to suma bieżąca, jest to suma wszystkich sprzedaży do bieżącej sprzedaży dni włącznie.

w tym poście omówimy wiele sposobów obliczania sumy bieżącej dla danych dziennych. Zbadamy, jak korzystać z formuł arkusza roboczego, tabel przestawnych, pivot mocy z DAX i power query.

zbadamy również, co dzieje się z uruchomionymi obliczeniami podczas wstawiania lub usuwania wierszy danych i jak zaktualizować wyniki.

Pobierz plik ze wszystkimi przykładami.

Sumy bieżące przy pomocy prostej formuły

można utworzyć podstawową formułę sumy bieżące przy użyciu operatora+.

jednak, aby wykonać zadanie, będziemy musieli użyć dwóch różnych formuł.

1. =C3 będzie pierwszą formułą i będzie tylko w pierwszym rzędzie sumy bieżącej.
2. =C4+D3 będzie w drugim wierszu i może być skopiowany w dół do pozostałych wierszy dla sumy bieżącej.

formuła w naszym pierwszym wierszu nie może dodać komórki nad nią do całości, ponieważ zawiera wartość tekstową dla nagłówka kolumny. To spowodowałoby # wartość! błąd pojawiania się w sumie bieżącej, ponieważ + nie obsługuje wartości tekstowych. Unikamy tego za pomocą innego wzoru w pierwszym wierszu, który nie odwołuje się do powyższej komórki.

Co się dzieje z bieżącą sumą, gdy wstawiamy lub usuwamy wiersze w naszych danych?

wstawienie nowego wiersza spowoduje lukę w sumie bieżącej. Aby to naprawić, musimy skopiować formułę z pierwszej komórki nad nowo wstawionymi wierszami aż do ostatniego wiersza.

usunięcie wszystkich wierszy spowoduje powstanie #REF! błędy, 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 do ostatniego wiersza.

uruchamianie sumów ze wzorem sumy

możemy uniknąć niezręczności używania dwóch różnych formuł w naszej kolumnie suma bieżąca, wykorzystując funkcję sumy zamiast operatora+. Gdy funkcja SUM napotka komórkę tekstową, będzie traktowała ją tak samo jak a, chociaż zawierała 0.

w ten sposób możemy użyć poniższej formuły jednolicie dla każdego wiersza włącznie z pierwszym wierszem.

=SUM(C3,D2)

ta formuła będzie odwoływać się do nagłówka kolumny zawierającego tekst dla pierwszego wiersza, ale to ok, ponieważ jest traktowana jak 0.

podczas wstawiania lub usuwania wierszy nadal napotkamy te same problemy z pustymi komórkami i błędami. Możemy je naprawić w taki sam sposób, jak w przypadku uruchomionych wartości całkowitych w prostej metodzie formuły.

uruchamianie Sum z częściowo ustalonym zakresem

inną opcją z funkcją SUM jest tylko odniesienie do kolumny sprzedaży i użycie częściowo ustalonego zakresu odniesienia.

jeśli użyjemy następującej formuły =SUM($C$3:C3), możemy skopiować i wkleić to w dół zakresu. Nie będzie odwoływać się do żadnych nagłówków kolumn, a zakres, do którego odwołuje się, wzrośnie do każdego wiersza.

Niestety, to też będzie miało te same problemy (i rozwiązania) z wstawianiem lub usuwaniem wierszy.

uruchamiając sumy ze względnym zakresem nazwanym

możemy uniknąć problemów z wstawianiem i usuwaniem wierszy z naszych danych, jeśli używamy względnego zakresu nazwanego. Będzie to odnosić się do komórki bezpośrednio powyżej, bez względu na to, ile wierszy wstawimy lub usuniemy.

jest to trik, który polega na tymczasowej zmianie stylu odniesienia Excela z A1 na R1C1. Następnie definiowanie nazwanego zakresu przy użyciu notacji R1C1. Następnie przełączanie stylu odniesienia z powrotem do A1.

w stylu odniesienia R1C1 komórki są określane przez odległość od komórki za pomocą odniesienia. Na przykład =RC odnosi się do komórki 2 w górę i 3 po prawej stronie komórki przy użyciu tej formuły.

możemy użyć tego względnego odniesienia do utworzenia nazwanego zakresu, który jest zawsze o jedną komórkę nad komórką odsyłającą za pomocą formuły =RC.

aby zmienić styl odniesienia, przejdź do zakładki 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. Przejdź do zakładki formuła wstążki programu Excel i wybierz polecenie Zdefiniuj nazwę.

Wstaw nazwę typu „powyżej” jako nazwę zakresu. Dodaj formułę =RC do Refers to input i naciśnij przycisk OK.

możemy teraz przełączyć Excela z powrotem do domyślnego stylu odniesienia. Przejdź do zakładki plik> opcje sekcja Formuły> odznacz pole stylu referencyjnego R1C1>, a następnie naciśnij przycisk OK.

teraz możemy użyć formuły =SUM(,powyżej) w naszej uruchomionej kolumnie total.

podany powyżej zakres zawsze będzie odnosił się bezpośrednio do komórki powyżej. Kiedy wstawiamy lub usuwamy wiersze, względny nazwany zakres zostanie odpowiednio dopasowany i nie jest potrzebne żadne działanie.

w rzeczywistości, jeśli umieścimy nasze dane w tabeli Excela, formuła automatycznie wypełni wszystkie nowe wiersze, ponieważ formuła jest jednolita dla całej kolumny. Kopiowanie formuł nie wymaga żadnych działań.

uruchomione sumy z tabelą przestawną

Tabele przestawne są bardzo przydatne do podsumowania dowolnego typu danych. To nie tylko dodawanie, liczenie i znajdowanie średnich. Istnieje wiele innych rodzajów obliczeń wbudowanych, a w rzeczywistości jest to bieżące obliczenie całkowite!

najpierw musimy wstawić tabelę przestawną na podstawie danych. Wybierz komórkę wewnątrz danych i przejdź do Wstaw kartę i wybierz polecenie Tabela przestawna. Następnie przejdź przez Utwórz tabelę przestawną okno, aby wybrać, gdzie chcesz tabelę przestawną, w nowym arkuszu roboczym lub gdzieś w istniejącym.

Dodaj pole daty do obszaru wierszy tabeli przestawnej, a następnie dodaj pole sprzedaży do obszaru wartości tabeli przestawnej. Teraz dodaj kolejną instancję pola Sales do obszaru wierszy.

powinniśmy teraz mieć dwa identyczne pola sprzedaży z jednym z nich oznaczonym jako Sum of Sales2. Możemy zmienić nazwę tej etykiety w dowolnym momencie, po prostu wpisując na niej coś takiego jak Running Total.

kliknij prawym przyciskiem myszy dowolną wartość w polu suma sprzedaży 2 i wybierz Pokaż wartość jako, a następnie wybierz Running Total In.

chcemy pokazać bieżącą sumę według daty, więc w następnym oknie musimy wybrać datę jako pole bazowe.

To wszystko, mamy teraz nowe obliczenie, które 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 uwzględniają wszelkie nowe dane w całkowitych obliczeniach, wystarczy odświeżyć tabelę przestawną.

kliknij prawym przyciskiem myszy w dowolnym miejscu tabeli przestawnej i wybierz Odśwież z menu.

Bieganie sumy z Power Pivot i Dax miary

pierwsze kroki dla tego są dokładnie takie same przy użyciu zwykłej tabeli przestawnej.

wybierz komórkę wewnątrz danych i przejdź do Wstaw kartę i wybierz polecenie Tabela przestawna.

Po przejściu do menu Utwórz tabelę przestawną zaznacz pole Dodaj te dane do modelu danych, aby dodać dane do modelu danych i włączyć je do użytku z Power pivot.

umieść pole daty w obszarze wierszy, a pole sprzedaży w obszarze wartości tabeli przestawnej.

Z power pivot, będziemy musieli utworzyć dodatkowe obliczenia, które chcemy za pomocą języka DAX. Kliknij prawym przyciskiem myszy nazwę tabeli w oknie pola tabeli przestawnej, a następnie wybierz Dodaj miarę, aby utworzyć nowe obliczenie. Uwaga, jest to dostępne tylko w modelu danych.

=CALCULATE ( SUM ( Sales ), FILTER ( ALL (Sales ), Sales 

teraz możemy utworzyć naszą nową uruchomioną całkowitą miarę.

1. w oknie miary musimy dodać nazwę miary. W tym przypadku możemy nazwać nową miarę jako Running Total.
2. musimy również dodać powyższą formułę do pola Formuły.
3. fajną rzeczą w power pivot jest możliwość przypisania formatu liczb do miary. Możemy wybrać format waluty dla naszego środka. Za każdym razem, gdy używamy tej miary w tabeli przestawnej, format zostanie zastosowany automatycznie.

naciśnij przycisk OK, a Nowa miara zostanie utworzona.

w oknie pola tabeli przestawnej pojawi się nowe pole. Ma małą ikonę fx po lewej stronie, która oznacza, że jest to miara, a nie zwykłe pole w danych.

możemy użyć tego nowego pola tak jak każdego innego pola i przeciągnąć je do obszaru wartości, aby dodać nasze bieżące obliczenia do tabeli przestawnej.

Co się stanie z bieżącą sumą, gdy dodamy lub usuniemy dane z tabeli źródłowej? Podobnie jak w zwykłej tabeli przestawnej, wystarczy kliknąć prawym przyciskiem myszy tabelę przestawną i wybrać Odśwież, aby zaktualizować obliczenia.

uruchomione sumy za pomocą zapytania o moc

możemy również dodać uruchomione sumy do naszych danych za pomocą zapytania o moc.

najpierw musimy zaimportować tabelę do power query. Wybierz tabelę danych i przejdź do zakładki Dane i wybierz opcję Od tabeli / zakresu. Spowoduje to otwarcie edytora zapytań o moc.

następnie możemy sortować nasze dane według daty. Jest to opcjonalny krok, który możemy dodać tak, że jeśli zmienimy kolejność naszych danych źródłowych, suma bieżąca nadal będzie wyświetlana według daty.

kliknij przełącznik filtrowania w nagłówku kolumny Data i wybierz Sortuj rosnąco z opcji.

musimy dodać kolumnę indeksu. Zostanie to wykorzystane w późniejszym obliczeniu sumy bieżącej. Przejdź do zakładki Dodaj kolumnę i kliknij małą strzałkę obok kolumny Indeks, aby wstawić indeks zaczynający się od 1 w pierwszym wierszu.

musimy dodać nową kolumnę do naszego zapytania, aby obliczyć sumę bieżącą. Przejdź do zakładki Dodaj kolumnę i wybierz polecenie Niestandardowa Kolumna.

możemy nazwać kolumnę jako Running Total i dodać następującą formułę.

lista.Funkcja Range tworzy listę wartości z kolumny Sales rozpoczynającą się od 1.wiersza (0. pozycja), która obejmuje kilka wierszy na podstawie wartości w kolumnie index.

lista.Funkcja Sum następnie sumuje tę listę wartości, która jest naszą sumą bieżącą.

nie potrzebujemy już kolumny indeksu, spełniła ona swoje zadanie i możemy ją usunąć. Kliknij prawym przyciskiem myszy nagłówek kolumny i wybierz Usuń z opcji.

mamy już działającą sumę i kończymy z edytorem zapytań. Możemy zamknąć zapytanie i załadować wyniki do nowego arkusza roboczego. Przejdź do zakładki Strona główna edytora zapytań i naciśnij przycisk Zamknij & Load.

Co się dzieje z uruchomioną sumą, gdy dodajemy lub usuwamy wiersze z naszych danych źródłowych? Będziemy musieli odświeżyć tabelę wyników kwerendy zasilania, aby zaktualizować sumę bieżącą ze zmianami. Kliknij prawym przyciskiem myszy w dowolnym miejscu tabeli i wybierz Odśwież, aby zaktualizować tabelę.

dzięki powyższemu opcjonalnemu krokowi sortowania, jeśli dodamy daty Nie w porządku do danych źródłowych, power query posortuje według daty i zwróci prawidłową kolejność według daty dla sumy bieżącej.

wnioski

istnieje wiele różnych opcji obliczania bieżących kwot w programie Excel.

zbadaliśmy opcje, w tym formuły w arkuszu roboczym, tabele przestawne, formuły Power pivot DAX i power query. Niektóre oferują bardziej solidne rozwiązanie podczas dodawania lub usuwania wierszy z danych, inne metody oferują łatwiejszą implementację.

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 niezawodne i umożliwiają łatwe wstawianie lub usuwanie wierszy danych, ale są trudniejsze do skonfigurowania.

dobrze jest być świadomym zalet i wad każdej metody i wybrać tę, która najlepiej pasuje. Jeśli nie będziesz wstawiać ani usuwać nowych danych, formuły arkusza roboczego mogą być najlepszym rozwiązaniem.