Deze aflevering laat drie manieren zien om lopende totalen te berekenen.
Een lopend totaal is, voor een lijst met numerieke waarden, een som van de waarden van de eerste rij tot de rij van het lopend totaal. Veelgebruikte toepassingen van een lopend totaal zijn in een chequeboekregister of een boekhoudblad. Er zijn veel manieren om een lopend totaal te maken, waarvan er twee hieronder worden beschreven.
De eenvoudigste techniek is om voor elke rij het lopende totaal van de rij erboven op te tellen bij de waarde in de rij. Dus de eerste formule in rij 2 is:
=SUM(D1,C2)
De reden dat we de functie SOM gebruiken, is omdat we in de eerste rij naar de koptekst in de rij erboven kijken. Als we de eenvoudigere, meer intuïtieve formule van gebruiken, =D1+C2wordt er een fout gegenereerd omdat de koptekstwaarde tekst versus numeriek is. De magie is dat de SOM-functie tekstwaarden negeert, die worden toegevoegd als nulwaarden. Wanneer de formule wordt gekopieerd naar alle rijen waarin een lopend totaal gewenst is, worden de celverwijzingen dienovereenkomstig aangepast:
De andere techniek gebruikt ook de functie SOM, maar elke formule telt alle waarden op van de eerste rij tot de rij met het lopende totaal. In dit geval gebruiken we een dollarteken ($) om van de eerste cel in de referentie een absolute referentie te maken, wat betekent dat deze niet wordt aangepast bij het kopiëren:
Beide technieken worden niet beïnvloed door het sorteren en verwijderen van rijen, maar bij het invoegen van rijen moet de formule naar de nieuwe rijen worden gekopieerd.
Excel 2007 introduceerde de tabel, die een herimplementatie is van de lijst in Excel 2003. Tabellen introduceerde een aantal zeer nuttige functies voor gegevenstabellen, zoals opmaak, sortering en filtering. Met de introductie van tabellen kregen we ook een nieuwe manier om naar de onderdelen van een tabel te verwijzen. Deze nieuwe referentiestijl wordt gestructureerd refereren genoemd.
Om het bovenstaande voorbeeld in een tabel om te zetten, selecteren we de gegevens die we in de tabel willen opnemen en drukken op Ctrl + T.Nadat we een prompt hebben weergegeven waarin ons wordt gevraagd het bereik van de tabel te bevestigen en of er al dan niet bestaande kopteksten zijn, converteert Excel de gegevens in een opgemaakte tabel:
Merk op dat de formules die we eerder hebben ingevoerd hetzelfde blijven.
Een van de handige functies die Tabellen bieden, is automatische opmaak en formuleonderhoud wanneer rijen worden toegevoegd, verwijderd, gesorteerd en gefilterd. Het is met name het onderhoud van de formule waarop we ons concentreren en die problematisch kunnen zijn. Om tabellen te laten werken terwijl ze worden gemanipuleerd, gebruikt Excel berekende kolommen, dit zijn kolommen met formules zoals kolom D in het bovenstaande voorbeeld. Wanneer nieuwe rijen worden ingevoegd onderaan worden toegevoegd, vult Excel automatisch de nieuwe rijen met de "standaard" formule voor die kolom. Het probleem met het bovenstaande voorbeeld is dat Excel wordt verward met standaardformules en deze niet altijd correct afhandelt. Dit wordt duidelijk wanneer er nieuwe rijen onder aan de tabel worden toegevoegd (door de cel rechtsonder in de tabel te selecteren en op TAB te drukken):
Dit tekort wordt verholpen door de nieuwere gestructureerde verwijzingen te gebruiken. Gestructureerde verwijzingen elimineren de noodzaak om naar specifieke cellen te verwijzen met behulp van de referentiestijl A1 of R1C1 en gebruikt in plaats daarvan kolomnamen en andere trefwoorden om de delen van een tabel te identificeren en ernaar te verwijzen. Om bijvoorbeeld dezelfde lopende totaalformule te maken die hierboven is gebruikt, maar met behulp van gestructureerde verwijzingen, hebben we:
=SUM(INDEX((Sales),1):(@Sales))
In dit voorbeeld hebben we een verwijzing naar de kolomnaam, "Verkoop", samen met het apenstaartje (@) om te verwijzen naar de rij in de kolom waarin de formule staat, ook wel de huidige rij genoemd.
Om het eerste voorbeeld hierboven te implementeren, waarbij we de lopende totale waarde in de voorgaande rij hebben toegevoegd aan het verkoopbedrag in de huidige rij, kun je de OFFSET-functie gebruiken:
=SUM(OFFSET((@(Running Total)),-1,0),(@Sales))
Als de bedragen die worden gebruikt om het lopend totaal te berekenen in twee kolommen staan, bijvoorbeeld één voor 'Debits' en één voor 'Credits', dan is de formule:
=SUM(INDEX( (Credit),1):(@Credit))- SUM(INDEX( (Debit),1):(@Debit))
Hier gebruiken we de INDEX-functie om de credit- en debetcellen van de eerste rij te lokaliseren en de hele kolom op te tellen tot en met de huidige rijwaarden. Het lopend totaal is de som van alle tegoeden tot en met de huidige rij verminderd met de som van alle afschrijvingen tot en met de huidige rij.
Voor meer informatie over gestructureerde verwijzingen in het bijzonder en tabellen in het algemeen, raden we het boek Excel Tables: A Complete Guide for Creating, Use and Automating Lists and Tables door Zack Barresse en Kevin Jones aan.
Toen ik lezers vroeg om op hun favoriete tips te stemmen, waren tafels populair. Met dank aan Peter Albert, Snorre Eikeland, Nancy Federice, Colin Michael, James E. Moede, Keyur Patel en Paul Peton voor het voorstellen van deze functie. Peter Albert schreef de Bonustip voor leesbare referenties. Zack Barresse schreef de bonustip Running Totals. Vier lezers stelden voor om OFFSET te gebruiken om uitbreidende reeksen voor dynamische grafieken te creëren: Charley Baak, Don Knowles, Francis Logan en Cecelia Rieb. Tabellen doen nu in de meeste gevallen hetzelfde.
Bekijk video
- Deze aflevering laat drie manieren zien om lopende totalen te berekenen
- De eerste methode heeft een andere formule in rij 2 dan alle andere rijen
- De eerste methode is = Links in rij 2 en = Links + Boven in rij 3 tot en met N
- Als u dezelfde formule probeert te gebruiken, krijgt u een #Value-fout met = Totaal + Getal
- Methode 2 gebruikt
=SUM(Up,Left)of=SUM(Previous Total,This Row Amount) - SUM negeert tekst, zodat u geen VALUE-fout krijgt
- Methode 3 maakt gebruik van een groeiend bereik:
=SUM(B$2:B2) - Uitbreidende reeksen zijn cool, maar ze zijn traag
- Lees de whitepaper van Charles Williams over Excel Formula Speed
- De derde methode is een probleem wanneer u Ctrl + T gebruikt en nieuwe rijen toevoegt
- Excel kan niet achterhalen hoe de formule moet worden geschreven
- De tijdelijke oplossingen vereisen enige kennis van gestructureerde verwijzingen in tabellen
- Tijdelijke oplossing 1 is de trage
=SUM(INDEX((Qty),1):(@Qty)) - Tijdelijke oplossing 2 is het vluchtige
=SUM(OFFSET((@Total),-1,0),(@Qty)) - (@Qty) verwijst naar Qty in deze rij
- (Qty) verwijst naar alle Qty-waarden
Videotranscriptie
Leer Excel voor podcast, aflevering 2004 - lopende totalen
Ik zal dit hele boek podcasten. Klik op die ik in de rechterbovenhoek om je in te schrijven.
Hallo, welkom terug bij de mystieke cel-netcast. Ik ben Bill Jelen. Nu dit onderwerp in het boek staat, werd ik bijgedragen door mijn vriend Zach Parise. Over Excel-tabellen gesproken, Zach is 's werelds expert op het gebied van Excel-tabellen. Hij heeft een boek geschreven over Excel-tabellen, maar laten we het eerst hebben over het uitvoeren van totalen, niet in tabellen.
Dus als ik denk aan lopende totalen, zijn er drie verschillende manieren om de lopende totalen te doen, en de manier waarop ik altijd begon is in de eerste rij, je zegt gewoon: breng de waarde over. Dus gelijk wat er links van me is. Oké, dus dit formaat is hier gewoon = B2. Dit zijn allemaal formuleteksten hier in de rechterhoek, dus je ziet wat we gebruiken, en vanaf daar is het een simpele formule die gelijk is aan de vorige waarde, plus de huidige waarde rechts en kopieer die naar beneden , maar je weet nu, we hebben dit probleem dat er twee verschillende formules voor nodig waren en je weet dat je in een perfecte situatie exact dezelfde formule helemaal naar beneden hebt, en de reden waarom we daar een andere formule op de eerste rij moeten hebben, is dat wanneer je probeert om 7 plus het woord totaal toe te voegen, het een waardefout is,maar de coole werker hier, is om niet alleen left plus up te gebruiken, maar om = (SUM) van de vorige waarde plus de hoeveelheid in deze rij te gebruiken, en te zien dat sommige ver genoeg zijn om teksten te negeren. Precies dus dat maakt dezelfde formule mogelijk. helemaal naar beneden.
Oké, dat was toen ik begon met Excel, ik gebruikte dat en toen ontdekte ik het uitbreidende bereik, het uitbreidende bereik zegt dat we L $ 2: L2 gaan doen en wat er gebeurt, is dat dit altijd begint bij rij 2, maar dan gaat het naar de huidige rij. Dus als je kijkt hoe dit werkt wanneer het wordt gekopieerd, zijn we altijd begonnen met rij 2, maar we gaan naar de huidige rij en dit werd mijn favoriete methode. Ik had zoiets van, oh, dit is zoveel geavanceerder en wanneer we naar Excel-opties gaan, ga naar het tabblad Formules en kies R1C1 in referentiestijl. Oké, R1C1, al deze formules zijn helemaal hetzelfde. Ik weet niet of je R1C1 begrijpt, het is gewoon goed om te weten dat we identieke R1C1-formules helemaal naar beneden hebben.
Laten we terug gaan. Dus deze methode hier is de methode die ik leuk vond, totdat Charles Williams, een Excel MBP uit Engeland, die een verbazingwekkende paper heeft over formule-snelheid, Excel-formule-snelheid, deze methode volledig ontkracht. Deze methode, laten we zeggen dat je dit 10.000 rijen hebt, elke formule kijkt naar twee referenties. Dus je kijkt naar 20.000 referenties, maar deze, dit kijkt naar twee, dit kijkt naar drie, dit kijkt naar vier, dit kijkt naar vijf en de laatste kijkt naar 10.000 referenties, en het is vreselijk langzamer en dus stopte ik met het gebruik van deze methode.
Daarna ga ik Zack lezen in het boek van Kevin Jones over Excel-tabellen en ontdek ik nog een ander probleem met deze methode. Een van de handige functies die de tabellen bieden, is dus 'automatische opmaak- en formuleonderhoudsrijen worden toegevoegd, verwijderd, gesorteerd en gefilterd'. Oké, dat is een citaat uit zijn boek. En om een rij aan een tafel toe te voegen, ga je gewoon naar de allerlaatste cel op de tafel en druk je op Tab. Dus alles werkt hier. We hebben nog maar 70, dat is geweldig en dan A104 en ik zal hier een 100 plaatsen. Oké, dus die 70 moet veranderen in 170 en dat doet het, maar deze 70 had helemaal niet mogen veranderen. Oké, 68 + 2 is geen 170. Ik zal het nog een keer doen. Een 104 en nog eens honderd in de laatste zetten heeft gelijk. Deze twee kloppen niet. Oké, dus we hebben een rare situatie dat als je 'als je deze formule gebruikt en je converteert naar een tabel, begin je rijen toe te voegen, het lopende totaal zal niet werken. Hoe erg is dat?
Oké, dus Zack biedt twee work-arounds en beide vereisen een beetje kennis van hoe structuurreferenties werken. We gaan hier gewoon een nieuwe kolom hebben en als ik hoeveelheid wil doen, gelijke hoeveelheid, juist, zodat = (@ Qty) hoeveelheid in deze rij zegt. Oh cool, er is een ander soort referentie waar we de Qty gebruiken zonder de @. Kijk hier eens naar. Dus = SUM (INDEX ((Qty), 1: (@ Qty)) betekent alle hoeveelheden en we gaan zeggen dat we de SUM willen van de eerste hoeveelheid, dus (INDEX ((Qty), 1 zegt de eerste waarde hier, tot de huidige rijhoeveelheid, en dit maakt gebruik van een heel speciale versie van index, wanneer index wordt gevolgd door een dubbele punt, verandert deze feitelijk in een celverwijzing. Oké, deze tijdelijke oplossing is helaas in strijd met de Charles Williams-regel van, wij 'we zullen naar elke afzonderlijke referentie moeten kijken, en als je er 10.000 rijen van krijgt, gaat dit heel erg langzaam.
Zach heeft nog een oplossing die het Charles Williams-probleem niet schendt, maar het gebruikt de gevreesde OFFSET. OFFSET is een vluchtige functie, dus elke keer dat u iets berekent, wordt OFFSET opnieuw berekend en wordt alles naar beneden vanaf de OFFSET opnieuw berekend. Het is gewoon een geweldige manier om je formules volledig, volledig te verknoeien, en wat dit doet, zegt: we nemen het totaal van deze rij, gaan een rij omhoog, over nul kolommen heen en wat dat doet, is zeggen: pak het totaal van de vorige rij en dan tellen we het aantal uit deze rij op. Oké, dus nu wordt er telkens naar twee referenties gekeken, maar helaas introduceert de OFFSET vluchtige functies.
Nou, daar heb je het, meer dan je ooit wilde weten over lopende totalen. Ik denk dat mijn uiteindelijke mening hier is om deze methode te gebruiken, omdat het er maar twee uitziet. Dezelfde formule helemaal naar beneden en uw gestructureerde tabelreferenties zullen werken.
Bekijk voor deze verkenning en 39 andere echt goede tips dit boek XL, de 40 beste Excel-tips aller tijden.
Samenvattend voor deze aflevering hebben we het gehad over drie manieren om lopende totalen te berekenen. De eerste methode heeft een andere formule, rij 2, dan alle andere rijen. Het is gelijk links in rij 2 en dan gelijk aan links plus omhoog in rijen 3 tot en met N, maar als je probeert en gewoon dezelfde formule gebruikt, gelijk links plus omhoog, helemaal naar beneden, hoe krijg je een #Value Error . So = SUM (Up, Left), wat het vorige totaal is, plus deze roadmap, die geweldig werkt, geen waardefouten en dan het uitbreidende bereik dat ik gebruik om van te houden. Ze zijn cool, maar totdat ik Charles Williams whitepaper over Excel-vorm van snelheid las. Toen begon ik deze groeiende verwijzingen te haten. Het heeft ook een probleem wanneer u CTRL T gebruikt en nieuwe rijen toevoegt. Excel kan niet achterhalen hoe die formule moet worden uitgebreid, hoe nieuwe rijen moeten worden toegevoegd. Ik hou van deze tip ga naar de allerlaatste cel in de tabel en druk op Tab,dat zal een nieuwe rij toevoegen en toen hadden we het over een aantal gestructureerde verwijzingen, waarbij we de hoeveelheid in deze rij gebruiken en vervolgens alle hoeveelheden. = SOM (VERSCHUIVING ((@ Totaal), - 1,00, (@ Aantal)).
Oké, ik wil Zach bedanken voor het geven van die tip. Ik wil je bedanken voor het langskomen. We zien je de volgende keer voor een nieuwe netcast van.
Download bestand
Download het voorbeeldbestand hier: Podcast2004.xlsx
