Metoda 1. Formuły
Zacznijmy na rozgrzewkę od najprostszej opcji – formuł. Jeśli jako dane wejściowe mamy małą tabelkę posortowaną według daty, to do obliczenia sumy bieżącej w osobnej kolumnie potrzebujemy podstawowej formuły:
Główną cechą jest tutaj trudne ustalenie zakresu wewnątrz funkcji SUMA – odniesienie do początku zakresu jest bezwzględne (ze znakami dolara), a do końca względne (bez dolarów). Odpowiednio, kopiując formułę w dół do całej kolumny, otrzymujemy rozszerzający się zakres, którego sumę obliczamy.
Wady tego podejścia są oczywiste:
- Tabela musi być posortowana według daty.
- Przy dodawaniu nowych wierszy z danymi formuła będzie musiała zostać rozszerzona ręcznie.
Metoda 2. Tabela przestawna
Ta metoda jest nieco bardziej skomplikowana, ale znacznie przyjemniejsza. A żeby się zaostrzyć, rozważmy poważniejszy problem – tabelę 2000 wierszy danych, gdzie nie ma sortowania po kolumnie daty, ale są powtórzenia (czyli możemy sprzedać kilka razy tego samego dnia):
Konwertujemy naszą oryginalną tabelę na „inteligentny” (dynamiczny) skrót klawiaturowy Ctrl+T lub zespół Strona główna – Formatuj jako tabelę (Strona główna — Formatuj jako tabelę), a następnie budujemy na nim tabelę przestawną za pomocą polecenia Wstaw – tabela przestawna (Wstaw — tabela przestawna). W podsumowaniu umieszczamy datę w obszarze wierszy, a liczbę sprzedanych towarów w obszarze wartości:
Pamiętaj, że jeśli masz niezbyt starą wersję programu Excel, daty są automatycznie grupowane według lat, kwartałów i miesięcy. Jeśli potrzebujesz innego grupowania (lub w ogóle go nie potrzebujesz), możesz to naprawić, klikając prawym przyciskiem myszy dowolną datę i wybierając polecenia Grupuj / Rozgrupuj (Grupuj / Rozgrupuj).
Jeśli chcesz zobaczyć zarówno wynikowe sumy według okresów, jak i sumę bieżącą w osobnej kolumnie, sensowne jest wrzucenie pola do obszaru wartości Sprzedany ponownie, aby uzyskać duplikat pola – w nim włączymy wyświetlanie sum bieżących. Aby to zrobić, kliknij prawym przyciskiem myszy na polu i wybierz polecenie Obliczenia dodatkowe – Suma skumulowana (Pokaż wartości jako — sumy bieżące):
Tam też można wybrać opcję narastania sum procentowo, a w kolejnym oknie należy wybrać pole, dla którego będzie iść kumulacja – w naszym przypadku jest to pole daty:
Zalety tego podejścia:
- Duża ilość danych jest szybko odczytywana.
- Nie trzeba wprowadzać żadnych formuł ręcznie.
- Przy zmianie danych źródłowych wystarczy zaktualizować zestawienie prawym przyciskiem myszy lub poleceniem Dane – Odśwież wszystko.
Wady wynikają z tego, że jest to podsumowanie, co oznacza, że nie możesz w nim robić, co chcesz (wstawiać wiersze, pisać formuły, budować dowolne diagramy itp.) przestanie działać.
Metoda 3: Power Query
Załadujmy naszą „inteligentną” tabelę z danymi źródłowymi do edytora zapytań Power Query za pomocą polecenia Dane — z tabeli/zakresu (Dane — z tabeli/zakresu). Nawiasem mówiąc, w najnowszych wersjach Excela zmieniono jego nazwę – teraz nazywa się Z liśćmi (z arkusza):
Następnie wykonamy następujące kroki:
1. Posortuj tabelę w porządku rosnącym według kolumny daty za pomocą polecenia Sortuj rosnąco na liście rozwijanej filtrów w nagłówku tabeli.
2. Nieco później, aby obliczyć sumę bieżącą, potrzebujemy kolumny pomocniczej z porządkowym numerem wiersza. Dodajmy to poleceniem Dodaj kolumnę – kolumnę indeksu – od 1 (Dodaj kolumnę — Kolumnę indeksu — Od 1).
3. Ponadto, aby obliczyć sumę bieżącą, potrzebujemy odniesienia do kolumny Sprzedany, gdzie znajdują się nasze podsumowane dane. W dodatku Power Query kolumny są również nazywane listami (listami) i aby uzyskać do nich link, kliknij prawym przyciskiem myszy nagłówek kolumny i wybierz polecenie Detailing (Pokaż szczegół). Potrzebne nam wyrażenie pojawi się na pasku formuły, składające się z nazwy poprzedniego kroku #”Dodano indeks”, skąd bierzemy tabelę i nazwę kolumny [Sprzedaż] z tej tabeli w nawiasach kwadratowych:
Skopiuj to wyrażenie do schowka w celu dalszego wykorzystania.
4. Usuń niepotrzebny kolejny ostatni krok Sprzedany i dodaj zamiast tego obliczoną kolumnę do obliczenia sumy bieżącej za pomocą polecenia Dodawanie kolumny – niestandardowa kolumna (Dodaj kolumnę — Kolumna niestandardowa). Formuła, której potrzebujemy, będzie wyglądać tak:
Tutaj funkcja Lista.Zakres pobiera oryginalną listę (kolumna [Sprzedaż]) i wyodrębnia z niego elementy, zaczynając od pierwszego (w formule jest to 0, ponieważ numeracja w dodatku Power Query zaczyna się od zera). Liczba elementów do pobrania to numer wiersza, który pobieramy z kolumny [Indeks]. Tak więc ta funkcja dla pierwszego wiersza zwraca tylko jedną pierwszą komórkę kolumny Sprzedany. W drugim wierszu – już dwie pierwsze komórki, w trzecim – pierwsze trzy itd.
Cóż, wtedy funkcja Lista.Suma sumuje wyodrębnione wartości i otrzymujemy w każdym wierszu sumę wszystkich poprzednich elementów, czyli skumulowaną sumę:
Pozostaje usunąć kolumnę Indeks, której już nie potrzebujemy, i przesłać wyniki z powrotem do programu Excel za pomocą polecenia Strona główna – Zamknij i załaduj do.
Problem jest rozwiązany.
Szybko i wściekle
W zasadzie można było to powstrzymać, ale w maści jest mała mucha – stworzona przez nas prośba działa z prędkością żółwia. Na przykład na moim nie najsłabszym komputerze tabela zawierająca tylko 2000 wierszy jest przetwarzana w 17 sekund. A jeśli będzie więcej danych?
Aby przyspieszyć, możesz użyć buforowania za pomocą specjalnej funkcji List.Buffer, która ładuje podaną mu listę (listę) jako argument do pamięci RAM, co znacznie przyspiesza dostęp do niej w przyszłości. W naszym przypadku sensowne jest buforowanie listy #”Indeks dodany”[Sprzedane], do której dodatek Power Query ma dostęp podczas obliczania sumy bieżącej w każdym wierszu naszej tabeli z 2000 wierszy.
Aby to zrobić, w edytorze Power Query na karcie Główne kliknij przycisk Edytor zaawansowany (Strona główna — Edytor zaawansowany), aby otworzyć kod źródłowy naszego zapytania w języku M wbudowanym w Power Query:
A potem dodaj tam linię ze zmienną Moja lista, którego wartość zwraca funkcja buforująca, a w kolejnym kroku zastępujemy wywołanie listy tą zmienną:
Po wprowadzeniu tych zmian nasze zapytanie stanie się znacznie szybsze i poradzi sobie z 2000-wierszową tabelą w zaledwie 0.3 sekundy!
Kolejna rzecz, prawda?
- Wykres Pareto (80/20) i jak go zbudować w Excelu
- Wyszukiwanie słów kluczowych w tekście i buforowanie zapytań w dodatku Power Query