Ten odcinek przedstawia trzy sposoby wykonywania podsumowań biegowych.
Suma bieżąca jest, w przypadku listy wartości liczbowych, sumą wartości z pierwszego wiersza do wiersza sumy bieżącej. Typowe zastosowania sum bieżących znajdują się w rejestrze książeczki czekowej lub arkuszu rozliczeniowym. Istnieje wiele sposobów tworzenia działającej sumy - dwa z nich opisano poniżej.
Najprostszą techniką jest dodanie w każdym wierszu sumy bieżącej z wiersza powyżej do wartości w wierszu. Zatem pierwsza formuła w wierszu 2 to:
=SUM(D1,C2)
Powodem, dla którego używamy funkcji SUMA, jest to, że w pierwszym wierszu patrzymy na nagłówek w wierszu powyżej. Jeśli użyjemy prostszej, bardziej intuicyjnej formuły, =D1+C2to zostanie wygenerowany błąd, ponieważ wartość nagłówka jest tekstowa, a nie numeryczna. Magia polega na tym, że funkcja SUMA ignoruje wartości tekstowe, które są dodawane jako wartości zerowe. Gdy formuła jest kopiowana do wszystkich wierszy, w których wymagana jest suma bieżąca, odwołania do komórek są odpowiednio dostosowywane:
Druga technika również wykorzystuje funkcję SUMA, ale każda formuła sumuje wszystkie wartości od pierwszego wiersza do wiersza, w którym jest wyświetlana suma bieżąca. W tym przypadku używamy znaku dolara ($), aby pierwsza komórka w odwołaniu była odniesieniem bezwzględnym, co oznacza, że nie jest dostosowywana podczas kopiowania:
Sortowanie i usuwanie wierszy nie wpływa na obie techniki, ale przy wstawianiu wierszy formuła musi zostać skopiowana do nowych wierszy.
W programie Excel 2007 wprowadzono tabelę, która jest ponownym wdrożeniem listy w programie Excel 2003. Tabele wprowadziły szereg bardzo przydatnych funkcji tabel danych, takich jak formatowanie, sortowanie i filtrowanie. Wraz z wprowadzeniem tabel otrzymaliśmy również nowy sposób odwoływania się do części tabeli. Ten nowy styl odwołań nazywa się odwołaniami strukturalnymi.
Aby przekonwertować powyższy przykład na tabelę, wybieramy dane, które chcemy zawrzeć w tabeli i wciskamy Ctrl + T. Po wyświetleniu monitu z prośbą o potwierdzenie zakresu tabeli i tego, czy istnieją nagłówki, Excel konwertuje dane do sformatowanej tabeli:
Zwróć uwagę, że formuły, które wprowadziliśmy wcześniej, pozostają takie same.
Jedną z przydatnych funkcji oferowanych przez tabele jest automatyczne formatowanie i obsługa formuł w miarę dodawania, usuwania, sortowania i filtrowania wierszy. W szczególności skupimy się na utrzymaniu formuły i może być problematyczne. Aby tabele działały podczas manipulacji, program Excel wykorzystuje kolumny obliczeniowe, które są kolumnami z formułami, takimi jak kolumna D w powyższym przykładzie. Gdy nowe wiersze są wstawiane na dole, program Excel automatycznie wypełnia nowe wiersze „domyślną” formułą dla tej kolumny. Problem z powyższym przykładem polega na tym, że program Excel jest mylony ze standardowymi formułami i nie zawsze obsługuje je poprawnie. Jest to widoczne, gdy nowe wiersze są dodawane na dole tabeli (wybierając prawą dolną komórkę w tabeli i naciskając klawisz TAB):
Ten brak jest rozwiązany za pomocą nowszych odwołań strukturalnych. Odwołania strukturalne eliminują potrzebę odwoływania się do określonych komórek za pomocą stylu odwołań A1 lub R1C1, a zamiast tego używają nazw kolumn i innych słów kluczowych do identyfikowania i odwoływania się do części tabeli. Na przykład, aby utworzyć tę samą bieżącą formułę sumaryczną, którą zastosowano powyżej, ale używając odwołań strukturalnych, mamy:
=SUM(INDEX((Sales),1):(@Sales))
W tym przykładzie mamy odniesienie do nazwy kolumny „Sales” wraz ze znakiem „małpa” (@), aby odwołać się do wiersza w kolumnie, w której znajduje się formuła, znanej również jako wiersz bieżący.
Aby zaimplementować pierwszy przykład powyżej, w którym dodaliśmy bieżącą wartość całkowitą z poprzedniego wiersza do kwoty sprzedaży w bieżącym wierszu, możesz użyć funkcji PRZESUNIĘCIE:
=SUM(OFFSET((@(Running Total)),-1,0),(@Sales))
Jeśli kwoty użyte do obliczenia sumy bieżącej znajdują się w dwóch kolumnach, na przykład jednej dla „Obciążeń” i jednej dla „Kredytów”, wzór jest następujący:
=SUM(INDEX( (Credit),1):(@Credit))- SUM(INDEX( (Debit),1):(@Debit))
Tutaj używamy funkcji INDEKS do zlokalizowania komórek Kredyt i Obciążenia w pierwszym wierszu i zsumowania całej kolumny do wartości bieżącego wiersza włącznie. Suma bieżąca to suma wszystkich kredytów do bieżącego wiersza włącznie pomniejszona o sumę wszystkich obciążeń do bieżącego wiersza włącznie.
Aby uzyskać więcej informacji na temat w szczególności odwołań strukturalnych i ogólnie tabel, zalecamy książkę Excel Tables: kompletny przewodnik po tworzeniu, używaniu i automatyzacji list i tabel autorstwa Zacka Barresse'a i Kevina Jonesa.
Kiedy poprosiłem czytelników, aby głosowali na ulubione wskazówki, popularne były tabele. Podziękowania dla Petera Alberta, Snorre Eikelanda, Nancy Federice, Colina Michaela, Jamesa E. Moede, Keyura Patela i Paula Petona za zasugerowanie tej funkcji. Peter Albert napisał dodatkową wskazówkę dotyczącą czytelnych odniesień. Zack Barresse napisał wskazówkę dotyczącą bonusów Running Totals. Czterech czytelników zasugerowało użycie OFFSET-u do tworzenia rozszerzających się zakresów dla wykresów dynamicznych: Charley Baak, Don Knowles, Francis Logan i Cecelia Rieb. Tabele robią teraz to samo w większości przypadków.
Obejrzyj wideo
- Ten odcinek przedstawia trzy sposoby obliczania sum biegowych
- Pierwsza metoda ma inną formułę w wierszu 2 niż wszystkie pozostałe wiersze
- Pierwsza metoda to = Lewa w wierszu 2 i = Lewa + Góra w wierszach od 3 do N
- Jeśli spróbujesz użyć tej samej formuły, zostanie wyświetlony błąd # Wartość z = Suma + Liczba
- Metoda 2 wykorzystuje
=SUM(Up,Left)lub=SUM(Previous Total,This Row Amount) - SUMA ignoruje tekst, więc nie pojawia się błąd VALUE
- Metoda 3 wykorzystuje rozszerzający się zakres:
=SUM(B$2:B2) - Rozszerzanie zakresów jest fajne, ale działa wolno
- Przeczytaj raport Charlesa Williamsa na temat Excel Formula Speed
- Trzecia metoda jest problemem, gdy używasz Ctrl + T i dodajesz nowe wiersze
- Excel nie może dowiedzieć się, jak napisać formułę
- Obejścia wymagają pewnej wiedzy na temat odwołań strukturalnych w tabelach
- Obejście 1 jest powolne
=SUM(INDEX((Qty),1):(@Qty)) - Obejście 2 jest niestabilne
=SUM(OFFSET((@Total),-1,0),(@Qty)) - (@Qty) odnosi się do ilości w tym wierszu
- (Qty) odnosi się do wszystkich wartości Qty
Transkrypcja wideo
Learn Excel for Podcast, odcinek 2004 - Running Totals
Będę podcastował całą tę książkę. Kliknij, że ja w prawym górnym rogu, aby zasubskrybować.
Hej, witaj ponownie w netcastie mistycznej komórki. Jestem Bill Jelen. Ten temat w książce został napisany przez mojego przyjaciela Zacha Parise'a. Mówiąc o tabelach Excela, Zach jest światowym ekspertem w zakresie tabel Excel. Napisał książkę o tabelach Excela, ale najpierw porozmawiajmy o uruchamianiu sum nie w tabelach.
Więc kiedy myślę o obliczaniu sum, są trzy różne sposoby obliczania sum bieżących, a sposób, od którego zawsze zaczynałam, znajduje się w pierwszym wierszu, o którym mówisz, przynieś wartość. Więc równij się temu, co jest na lewo ode mnie. W porządku, więc ten format to po prostu = B2. To wszystko jest tekstem formuły tutaj w prawym rogu, więc widzisz, czego używamy, a następnie od tego momentu jest to prosta mała formuła równa poprzedniej wartości plus bieżąca wartość po prawej i skopiuj ją w dół ale wiesz teraz, mamy ten problem, że wymagał dwóch różnych formuł i wiesz, że w idealnej sytuacji masz dokładnie tę samą formułę przez cały czas, a powodem, dla którego musimy mieć inną formułę w pierwszym wierszu, jest że kiedy próbujesz dodać równe 7 plus słowo suma, jest to błąd wartości,ale fajny pracownik ma nie tylko użyć lewy plus w górę, ale użyć = (SUMA) poprzedniej wartości plus ilość w tym wierszu i zobaczyć, że niektóre są wystarczająco daleko, aby zignorować teksty. Tak, to pozwala na ten sam wzór. w dół.
W porządku, tak było, kiedy zaczynałem używać Excela, używałem tego, a potem odkryłem rozszerzający się zakres, rozszerzający się zakres mówi, że zamierzamy zrobić L 2 $: L2 i to, co się dzieje, zawsze zaczyna się od wiersza 2, ale potem przechodzi do bieżącego wiersza. Kiedy więc przyjrzysz się, jak to działa po skopiowaniu, zawsze zaczynaliśmy od wiersza 2, ale przechodzimy do bieżącego wiersza i stała się to moja ulubiona metoda. Pomyślałem, och, to jest o wiele bardziej wyrafinowane i kiedy przejdziemy do opcji programu Excel, przejdź do zakładki Formuły i wybierz R1C1 w stylu odniesienia. W porządku, R1C1, wszystkie te formuły są dokładnie takie same przez całą drogę. Nie wiem, czy rozumiesz W1K1, po prostu dobrze jest wiedzieć, że mamy identyczne formuły R1C1 przez cały czas.
Wracajmy. Więc ta metoda tutaj jest metodą, którą lubiłem, dopóki Charles Williams, Excel MBP z Anglii, który ma niesamowity artykuł na temat szybkości formuł, szybkości formuły Excela, całkowicie obalił tę metodę. Ta metoda, powiedzmy, że masz 10000 wierszy, każda formuła sprawdza dwa odwołania. Więc patrzysz na 20000 odniesień, ale ten, ten patrzy na dwa, to patrzy na trzy, to patrzy na cztery, to patrzy na pięć, a ostatni na 10000 referencji, i jest strasznie wolniejszy więc przestałem używać tej metody.
Następnie czytam Zacka w książce Kevina Jonesa o tabelach Excela i odkrywam kolejny problem związany z tą metodą. Tak więc jedną z przydatnych funkcji oferowanych przez tabele jest „automatyczne formatowanie i zapisywanie wierszy formuł, dodawanie, usuwanie, sortowanie i filtrowanie”. W porządku, to cytat z jego książki. Aby dodać wiersz do tabeli, po prostu przejdź do ostatniej komórki w tabeli i naciśnij klawisz tab. Więc wszystko tutaj działa. Zeszliśmy do 70, to świetnie, a potem A104 i wstawię tutaj 100. W porządku, więc 70 powinno zmienić się na 170 i tak jest, ale to 70 nie powinno się w ogóle zmienić. W porządku 68 + 2 to nie 170. Zrobię to jeszcze raz. 104 i wstawienie kolejnej setki do ostatniego jest poprawne. Ci dwaj nie mają racji. W porządku, więc mamy dziwną sytuację, jeśliponownie używamy tej formuły i konwertujesz do tabeli, zaczynasz dodawać wiersze, suma bieżąca nie zadziała. Jak źle to jest?
W porządku, więc Zack oferuje dwa obejścia i oba wymagają trochę wiedzy, jak działają odniesienia do struktury. Będziemy mieć tutaj nową kolumnę i gdybym chciał zrobić ilość, równą ilość, tak, więc = (@ Qty) mówi ilość w tym wierszu. Och fajnie, cóż, jest inny rodzaj odniesienia, w którym używamy Qty bez znaku @. Spójrz na to. Więc = SUMA (INDEKS ((Ilość), 1: (@ Ilość)) oznacza wszystkie ilości i powiemy, że chcemy SUMA z pierwszej ilości, więc (INDEKS ((Ilość), 1 mówi pierwsza wartość w tym miejscu, aż do bieżącej ilości wiersza, i to przy użyciu naprawdę specjalnej wersji indeksu, gdy po indeksie występuje dwukropek, w rzeczywistości zmienia się na odwołanie do komórki. W porządku, więc to obejście niestety narusza regułę Charlesa Williamsa z, my 'Będziemy musieli przyjrzeć się każdemu odwołaniu, więc kiedy otrzymasz 10000 wierszy, będzie to działać bardzo, bardzo wolno.
Zach ma inne obejście, które nie narusza problemu Charlesa Williamsa, ale używa przerażającego PRZESUNIĘCIA. OFFSET jest funkcją zmienną, więc za każdym razem, gdy coś obliczysz, OFFSET przeliczy, a wszystko w dół od OFFSET-u przeliczy. To po prostu świetny sposób na całkowite, całkowite zepsucie twoich formuł, a co to robi, to znaczy, że bierzemy sumę z tego wiersza, przechodzimy o jeden wiersz w górę, przez zero kolumn, a to oznacza: weź sumę z poprzedniego wiersza, a następnie dodamy do niej ilość z tego wiersza. W porządku, więc teraz za każdym razem patrzymy na dwie referencje, ale niestety OFFSET wprowadza zmienne funkcje.
Cóż, masz to, więcej niż kiedykolwiek chciałeś wiedzieć o Running Totals. Myślę, że moja ostateczna opinia jest taka, aby użyć tej metody, ponieważ wygląda na to tylko dwie. Ta sama formuła w dół, a odwołania do tabel strukturalnych będą działać.
Jeśli chodzi o tę eksplorację i 39 innych naprawdę dobrych wskazówek, zapoznaj się z tą książką XL, 40 najwspanialszymi wskazówkami dotyczącymi Excela wszechczasów.
Podsumowując ten odcinek, rozmawialiśmy o trzech sposobach wykonywania podsumowań biegowych. Pierwsza metoda ma inną formułę, wiersz 2, niż wszystkie pozostałe wiersze. Jest równy po lewej w wierszu 2, a następnie równy od lewej plus w górę w wierszach od 3 do N, ale jeśli spróbujesz po prostu użyć tej samej formuły, równy lewy plus góra, cały czas w dół, w jaki sposób uzyskasz błąd #Value . Więc = SUMA (góra, lewo), czyli poprzednia suma, plus ta mapa drogowa, która działa świetnie, bez błędów wartości, a następnie rozszerzający się zakres, który uwielbiam. Są fajne, ale dopóki nie przeczytałem białej księgi Charlesa Williamsa na temat szybkości w Excelu. Wtedy zacząłem nienawidzić tych rozszerzających się odniesień. Występuje również problem, gdy używasz CTRL T i dodajesz nowe wiersze. Program Excel nie może dowiedzieć się, jak rozwinąć tę formułę, jak dodać nowe wiersze. Podoba mi się ta wskazówka, przejdź do ostatniej komórki w tabeli i naciśnij Tab,to doda nowy wiersz, a następnie rozmawialiśmy o pewnych strukturalnych odniesieniach, w których używamy ilości w tym wierszu, a następnie wszystkich ilości. = SUMA (OFFSET ((@ Total), - 1,00, (@ Qty)).
Ok, chcę podziękować Zachowi za przesłanie tej wskazówki. Chcę ci podziękować za zatrzymanie się. Do zobaczenia następnym razem z kolejnym netcastem od.
Pobieranie pliku
Pobierz przykładowy plik tutaj: Podcast2004.xlsx
