Methode 1. Formules
Laten we beginnen, voor de warming-up, met de eenvoudigste optie: formules. Als we een kleine tabel hebben gesorteerd op datum als invoer, dan hebben we een elementaire formule nodig om het lopende totaal in een aparte kolom te berekenen:
Het belangrijkste kenmerk hier is de lastige vaststelling van het bereik binnen de SOM-functie - de verwijzing naar het begin van het bereik wordt absoluut gemaakt (met dollartekens) en naar het einde - relatief (zonder dollars). Dienovereenkomstig krijgen we bij het kopiëren van de formule naar de hele kolom een groeiend bereik, waarvan we de som berekenen.
De nadelen van deze aanpak zijn duidelijk:
- De tabel moet op datum gesorteerd zijn.
- Bij het toevoegen van nieuwe rijen met gegevens zal de formule handmatig moeten worden uitgebreid.
Methode 2. Draaitabel
Deze methode is iets ingewikkelder, maar veel prettiger. En om het nog erger te maken, laten we eens kijken naar een serieuzer probleem: een tabel met 2000 rijen gegevens, waarbij er niet op de datumkolom wordt gesorteerd, maar er zijn herhalingen (dwz we kunnen meerdere keren op dezelfde dag verkopen):
We zetten onze originele tabel om in een "slimme" (dynamische) sneltoets Ctrl+T of team Home – Opmaak als tabel (Home - Opmaken als tabel), en dan bouwen we er een draaitabel op met het commando Invoegen – draaitabel (Invoegen — draaitabel). We plaatsen de datum in het rijengebied in het overzicht en het aantal verkochte goederen in het waardengebied:
Houd er rekening mee dat als u een nog niet helemaal oude versie van Excel heeft, de datums automatisch worden gegroepeerd per jaar, kwartalen en maanden. Als je een andere groepering nodig hebt (of helemaal niet nodig hebt), dan kun je dit oplossen door met de rechtermuisknop op een datum te klikken en opdrachten te selecteren Groeperen / Groep opheffen (Groeperen / Groep opheffen).
Als u zowel de resulterende totalen per periode als het lopende totaal in een aparte kolom wilt zien, dan is het logisch om het veld in het waardegebied te gooien Verkocht nogmaals om een duplicaat van het veld te krijgen - daarin zullen we de weergave van lopende totalen inschakelen. Klik hiervoor met de rechtermuisknop op het veld en selecteer de opdracht Aanvullende berekeningen – cumulatief totaal (Waarden weergeven als — Lopende totalen):
Daar kunt u ook de optie selecteren om de totalen als percentage te laten groeien, en in het volgende venster moet u het veld selecteren waarvoor de accumulatie zal gaan - in ons geval is dit het datumveld:
De voordelen van deze aanpak:
- Een grote hoeveelheid data wordt snel uitgelezen.
- Er hoeven geen formules handmatig te worden ingevoerd.
- Bij het wijzigen van de brongegevens is het voldoende om het overzicht bij te werken met de rechtermuisknop of met het commando Data – Alles vernieuwen.
De nadelen vloeien voort uit het feit dat dit een samenvatting is, wat betekent dat je niet meer kunt doen wat je wilt (regels invoegen, formules schrijven, eventuele diagrammen maken, enz.) zal niet meer werken.
Methode 3: Power-query
Laten we onze "slimme" tabel met brongegevens in de Power Query-queryeditor laden met behulp van de opdracht Gegevens – Uit tabel/bereik (Gegevens — Uit tabel/bereik). In de nieuwste versies van Excel werd het trouwens hernoemd - nu heet het Met bladeren (Van Blad):
Dan voeren we de volgende stappen uit:
1. Sorteer de tabel in oplopende volgorde op de datumkolom met het commando Oplopend sorteren in de vervolgkeuzelijst filter in de tabelkop.
2. Even later, om het lopende totaal te berekenen, hebben we een hulpkolom nodig met het ordinale rijnummer. Laten we het toevoegen met het commando Kolom toevoegen – Indexkolom – Van 1 (Kolom toevoegen — Indexkolom — Van 1).
3. Om het lopende totaal te berekenen, hebben we ook een verwijzing naar de kolom nodig Verkocht, waar onze samengevatte gegevens liggen. In Power Query worden kolommen ook lijsten (lijst) genoemd en om er een link naar te krijgen, klikt u met de rechtermuisknop op de kolomkop en selecteert u de opdracht detaillering (Laat details zien). De uitdrukking die we nodig hebben, verschijnt in de formulebalk, bestaande uit de naam van de vorige stap #”Index toegevoegd”, van waaruit we de tabel en de kolomnaam nemen [Verkoop] uit deze tabel tussen vierkante haken:
Kopieer deze uitdrukking naar het klembord voor verder gebruik.
4. Verwijder onnodig meer laatste stap Verkocht en voeg in plaats daarvan een berekende kolom toe voor het berekenen van het lopende totaal met de opdracht Een kolom toevoegen – Aangepaste kolom (Kolom toevoegen — Aangepaste kolom). De formule die we nodig hebben, ziet er als volgt uit:
Hier de functie Lijst.Bereik neemt de originele lijst (kolom [Verkoop]) en extraheert er elementen uit, beginnend bij de eerste (in de formule is dit 0, aangezien de nummering in Power Query vanaf nul begint). Het aantal op te halen elementen is het rijnummer dat we uit de kolom halen [Inhoudsopgave]. Dus deze functie voor de eerste rij retourneert slechts één eerste cel van de kolom Verkocht. Voor de tweede regel - al de eerste twee cellen, voor de derde - de eerste drie, enz.
Nou, dan de functie Lijst.Sum telt de geëxtraheerde waarden op en we krijgen in elke rij de som van alle voorgaande elementen, dwz cumulatief totaal:
Het blijft om de Index-kolom te verwijderen die we niet langer nodig hebben en de resultaten terug te uploaden naar Excel met de Home - Close & Load to-opdracht.
Het probleem is opgelost.
Snel en Furious
In principe had dit gestopt kunnen worden, maar er zit een klein vliegje in de zalf – het verzoek dat we hebben gemaakt werkt met de snelheid van een schildpad. Op mijn niet de zwakste pc wordt bijvoorbeeld een tabel van slechts 2000 rijen in 17 seconden verwerkt. Wat als er meer gegevens zijn?
Om te versnellen, kunt u buffering gebruiken met behulp van de speciale List.Buffer-functie, die de lijst (lijst) die eraan is gegeven als een argument in RAM laadt, wat de toegang ertoe in de toekomst aanzienlijk versnelt. In ons geval is het logisch om de lijst #”Toegevoegde index” [Verkocht] te bufferen, waartoe Power Query toegang moet hebben bij het berekenen van het lopende totaal in elke rij van onze tabel met 2000 rijen.
Om dit te doen, klikt u in de Power Query-editor op het tabblad Start op de knop Geavanceerde editor (Home - Geavanceerde editor) om de broncode van onze query te openen in de M-taal die is ingebouwd in Power Query:
En voeg daar dan een regel met een variabele toe Mijn lijst, waarvan de waarde wordt geretourneerd door de bufferfunctie, en bij de volgende stap vervangen we de aanroep van de lijst door deze variabele:
Nadat we deze wijzigingen hebben aangebracht, wordt onze zoekopdracht aanzienlijk sneller en kan een tabel met 2000 rijen in slechts 0.3 seconden worden verwerkt!
Nog iets, toch?
- Pareto-diagram (80/20) en hoe het in Excel te bouwen
- Zoeken op trefwoord in tekst en querybuffering in Power Query