Tato epizoda ukazuje tři způsoby, jak provést průběžné součty.
Průběžný součet je pro seznam číselných hodnot součtem hodnot od prvního řádku po řádek průběžného součtu. Běžná použití průběžného součtu jsou v registru šekové knížky nebo v účetním listu. Existuje mnoho způsobů, jak vytvořit běžící celkem - dva z nich jsou popsány níže.
Nejjednodušší technikou je na každém řádku přidat průběžný součet z výše uvedeného řádku k hodnotě v řádku. Takže první vzorec v řádku 2 je:
=SUM(D1,C2)
Důvod, proč používáme funkci SUMA, je ten, že v prvním řádku se díváme na záhlaví v řádku výše. Použijeme-li jednodušší a intuitivnější vzorec, =D1+C2vygeneruje se chyba, protože hodnota záhlaví je text versus numerická. Kouzlo je v tom, že funkce SUM ignoruje textové hodnoty, které se přidávají jako nulové hodnoty. Když je vzorec zkopírován do všech řádků, ve kterých je požadován průběžný součet, odkazy na buňky se odpovídajícím způsobem upraví:
Druhá technika také používá funkci SUMA, ale každý vzorec sečte všechny hodnoty od prvního řádku po řádek zobrazující průběžný součet. V tomto případě použijeme znak dolaru ($), aby se první buňka v odkazu stala absolutní referencí, což znamená, že se při kopírování neupraví:
Obě techniky nejsou ovlivněny seřazením a odstraněním řádků, ale při vkládání řádků musí být vzorec zkopírován do nových řádků.
Excel 2007 představil tabulku, což je re-implementace seznamu v aplikaci Excel 2003. Tabulky představily řadu velmi užitečných funkcí pro datové tabulky, jako je formátování, třídění a filtrování. Se zavedením tabulek nám byl také poskytnut nový způsob odkazování na části tabulky. Tento nový styl odkazování se nazývá strukturované odkazování.
Chcete-li převést výše uvedený příklad na tabulku, vybereme data, která chceme do tabulky zahrnout, a stiskneme Ctrl + T. Po zobrazení výzvy s žádostí o potvrzení rozsahu tabulky a toho, zda existují existující záhlaví, Excel převede data do formátované tabulky:
Všimněte si, že vzorce, které jsme zadali dříve, zůstávají stejné.
Jednou z užitečných funkcí, které tabulky nabízejí, je automatické formátování a údržba vzorců při přidávání, odebírání, třídění a filtrování řádků. Zejména se budeme soustředit na údržbu vzorce, která může být problematická. Aby tabulky fungovaly i při manipulaci, používá Excel vypočítané sloupce, které jsou sloupci se vzorci, jako je sloupec D ve výše uvedeném příkladu. Když jsou do dolní části přidány nové řádky, Excel automaticky naplní nové řádky „výchozím“ vzorcem pro daný sloupec. Problém výše uvedeného příkladu spočívá v tom, že se Excel zaměňuje se standardními vzorci a ne vždy s nimi zachází správně. To je zřejmé, když jsou do dolní části tabulky přidány nové řádky (výběrem pravé dolní buňky v tabulce a stisknutím klávesy TAB):
Tento nedostatek je vyřešen pomocí novějšího strukturovaného odkazování. Strukturované odkazování eliminuje potřebu odkazovat na konkrétní buňky pomocí stylu odkazu A1 nebo R1C1 a místo toho používá názvy sloupců a další klíčová slova k identifikaci a odkazování na části tabulky. Například k vytvoření stejného vzorce celkového běhu použitého výše, ale pomocí strukturovaného odkazování máme:
=SUM(INDEX((Sales),1):(@Sales))
V tomto příkladu máme odkaz na název sloupce „Prodej“ spolu s zavináčem (@), který odkazuje na řádek ve sloupci, ve kterém je umístěn vzorec, který je také známý jako aktuální řádek.
Chcete-li implementovat první příklad výše, kde jsme přidali průběžnou celkovou hodnotu v předchozím řádku k částce prodeje v aktuálním řádku, můžete použít funkci OFFSET:
=SUM(OFFSET((@(Running Total)),-1,0),(@Sales))
Pokud jsou částky použité k výpočtu průběžného součtu ve dvou sloupcích, například jeden pro „debety“ a jeden pro „kredity“, pak vzorec je:
=SUM(INDEX( (Credit),1):(@Credit))- SUM(INDEX( (Debit),1):(@Debit))
Zde používáme funkci INDEX k vyhledání kreditních a debetních buněk prvního řádku a sečtením celého sloupce až po hodnoty aktuálního řádku včetně. Průběžný součet je součet všech kreditů až do aktuálního řádku včetně, mínus součet všech debetů až do aktuálního řádku včetně.
Pro více informací o strukturovaných referencích konkrétně a tabulkách obecně doporučujeme knihu Excel Tables: Kompletní průvodce pro vytváření, používání a automatizaci seznamů a tabulek od Zacka Barresse a Kevina Jonese.
Když jsem požádal čtenáře, aby hlasovali pro jejich oblíbené tipy, stoly byly populární. Děkujeme Peteru Albertovi, Snorre Eikelandovi, Nancy Federice, Colinovi Michaelovi, Jamesi E. Moedeovi, Keyurovi Patelovi a Paulu Petonovi za návrh této funkce. Peter Albert napsal bonusový tip Čitelné reference. Zack Barresse napsal bonusový tip Running Totals. Čtyři čtenáři navrhli použít OFFSET k vytvoření rozšiřujících se rozsahů pro dynamické grafy: Charley Baak, Don Knowles, Francis Logan a Cecelia Rieb. Tabulky nyní ve většině případů dělají totéž.
Sledovat video
- Tato epizoda ukazuje tři způsoby, jak provést průběžné součty
- První metoda má v řádku 2 jiný vzorec než všechny ostatní řádky
- První metoda je = Left in row 2 and = Left + Up in lines 3 through N
- Pokud se pokusíte použít stejný vzorec, zobrazí se chyba #hodnota s = celkem + číslo
- Metoda 2 používá
=SUM(Up,Left)nebo=SUM(Previous Total,This Row Amount) - Funkce SUM ignoruje text, abyste nedostali chybu HODNOTA
- Metoda 3 používá rozšiřující se rozsah:
=SUM(B$2:B2) - Rozšiřující se rozsahy jsou skvělé, ale jsou pomalé
- Přečtěte si Whitepaper Charlese Williamse o Excel Formula Speed
- Třetí metoda je problém, když použijete Ctrl + T a přidáte nové řádky
- Excel nemůže přijít na to, jak napsat vzorec
- Řešení vyžadují určité znalosti strukturovaného odkazování v tabulkách
- Řešení 1 je pomalé
=SUM(INDEX((Qty),1):(@Qty)) - Řešení 2 je volatilní
=SUM(OFFSET((@Total),-1,0),(@Qty)) - (@Qty) odkazuje na množství na tomto řádku
- (Množství) odkazuje na všechny hodnoty Množství
Přepis videa
Learn Excel for Podcast, Episode 2004 - Running Totals
Budu podcastovat celou tuto knihu. Kliknutím na I v pravém horním rohu se přihlaste k odběru.
Ahoj, vítej zpět na mystickém mobilním netcastu. Jsem Bill Jelen. Nyní k tomuto tématu v knize přispěl můj přítel Zach Parise. Když mluvíme o tabulkách Excel, Zach je světovým expertem na tabulky Excel. Napsal knihu o tabulkách Excel, ale nejprve si promluvme o běhu součtů, nikoli v tabulkách.
Takže když přemýšlím o běhu součtů, existují tři různé způsoby, jak běhat součty, a způsob, kterým jsem vždy začínal, je v první řadě, kterou řeknete, přenést hodnotu znovu. Takže stejné, co je ode mě vlevo. Dobře, takže tento formát je zde = = B2. Jedná se o text vzorců zde v pravém rohu, takže vidíte, co používáme, a odtamtud dolů je to jednoduchý malý vzorec, který se rovná předchozí hodnotě plus aktuální hodnota vpravo a zkopírujte to dolů , ale teď víte, máme tento problém, že to vyžadovalo dva různé vzorce a víte, že v perfektní situaci máte úplně stejný vzorec až dolů, a důvod, proč tam musíme mít v prvním řádku jiný vzorec, je že když se pokusíte přidat rovných 7 plus slovo celkem, je to chyba hodnoty,ale skvělý pracovník zde není jen použít left plus up, ale použít = (SUM) předchozí hodnoty plus množství v tomto řádku a vidět, že některé jsou dost daleko na to, aby ignorovaly texty. Správně, takže umožňuje stejný vzorec. úplně dolů.
Dobře, takže když jsem začínal používat Excel, používal jsem to a pak jsem objevil rozšiřující se rozsah, rozšiřující se rozsah říká, že uděláme L $ 2: L2 a co se stane, je to vždy začíná na řádku 2, ale pak to jde dolů na aktuální řádek. Takže když se podíváte na to, jak to funguje, když se to zkopíruje, vždy jsme spustili řádek 2, ale přejdeme k aktuálnímu řádku a toto se stalo mou oblíbenou metodou. Byl jsem rád, ach, je to mnohem sofistikovanější, a když přejdeme do Možnosti aplikace Excel, přejděte na kartu Vzorce a vyberte R1C1 v Referenčním stylu. Dobře, R1C1, všechny tyto vzorce jsou úplně stejné úplně dolů. Nevím, jestli rozumíte R1C1, je dobré vědět, že máme úplně stejné vzorce R1C1.
Vraťme se. Takže tato metoda je tady metoda, která se mi líbila, dokud tuto metodu zcela neodhalil Charles Williams, Excel MBP z Anglie, který má úžasný dokument o rychlosti formule, rychlosti formule Excel. Tato metoda, řekněme, že máte 10 000 řádků, každý vzorec se dívá na dva odkazy. Díváte se tedy na 20 000 referencí, ale tento, tento se dívá na dva, tento se dívá na tři, tento se dívá na čtyři, tento se dívá na pět a poslední se dívá na 10 000 referencí, a je strašně pomalejší a tak jsem přestal používat tuto metodu.
Pak pokračuji ve čtení Zacka v knize Kevina Jonese o tabulkách Excel a objevím ještě další problém s touto metodou. Jednou z užitečných funkcí, které tabulky nabízejí, je tedy „automatické formátování a přidávání, odebírání, třídění a filtrování řádků údržby vzorců“. Dobře, to je citát z jeho knihy. Chcete-li přidat řádek do tabulky, přejděte do poslední buňky v tabulce a stiskněte tabulátor. Takže tady vše funguje. Jsme dole na 70, to je úžasné, a pak A104 a já sem vložím 100. Dobře, takže 70 by se mělo změnit na 170 a také ano, ale 70 se nemělo vůbec změnit. Alright 68 + 2 není 170. Udělám to znovu. 104 a dal další sto v poslední má pravdu. Tito dva nemají pravdu. Dobře, takže máme divnou situaci, že pokudznovu pomocí tohoto vzorce a převedete na tabulku začnete přidávat řádky, průběžný součet nebude fungovat. Jak je to špatné?
Dobře, takže Zack nabízí dvě řešení a oba vyžadují trochu znalostí o tom, jak fungují odkazy na strukturu. Prostě tu budeme mít nový sloupec a kdybych chtěl udělat množství, stejné množství, správně, takže = (@ množství) říká množství v tomto řádku. Super, dobře, existuje další druh odkazu, kde používáme množství bez znaku @. Koukej na tohle. Takže = SUM (INDEX ((Qty), 1: (@ Qty)) znamená všechna množství a my řekneme, že chceme SUM od prvního množství, takže (INDEX ((Qty), 1 říká první hodnota zde, až po aktuální počet řádků, a to je použití opravdu speciální verze indexu, když je index následován dvojtečkou, ve skutečnosti se změní na odkaz na buňku. Dobře, toto řešení bohužel porušuje pravidlo Charlese Williamse z, my 'Budeme se muset podívat na každou jednotlivou referenci, a tak když získáte 10 000 řádků, půjde to opravdu, opravdu pomalu.
Zach has another workaround that doesn't violate the Charles Williams problem, but it's using the dreaded OFFSET. OFFSET is a volatile function so every time that you calculate something, OFFSET is going to recalculate and everything down line from the OFFSET's going to recalculate. It's just a great way to completely, completely screw up your your formulas, and what this is doing, it's saying, we're taking the total from this row, going up one row, over zero columns and so what that's doing is saying: grab the total from the previous row and then we're adding to it the quantity from this row. Alright, so, now it's all looking at two references each time, but unfortunately the OFFSET is introducing volatile functions.
Well, there you have it, more than you ever wanted to know about Running Totals. I guess my final opinion here is to use this method, because it only looks it two. Same formula all the way down and your structured table references will work.
For this exploration and 39 other really good tips, check out this book XL, the 40 greatest Excel tips of all time.
Recap for this episode we talked about three ways to do running totals. The first method has a different formula, row 2, than all the other rows. It's equal left in row 2 and then equal left plus up in rows 3 through N, but if you try and just use that same formula, equal left plus up, all the way down, how you're going to get a #Value Error. So =SUM(Up,Left), which is previous total, plus this roadmap, that works great, no Value Errors and then the expanding range which I use to love. They're cool, but until I read Charles Williams white paper on Excel form of speed. Then I started to hate these expanding references. It also has a problem when you use CTRL T and add new rows. Excel can't figure out how to expand that formula, how to add new rows. I love this tip go to the very last cell in the table and press Tab, that will add a new row and then we talked about some structured referencing, where we're using quantity in this row and then all quantities. =SUM(OFFSET((@Total),-1,00,(@Qty)).
Dobře, chci poděkovat Zachovi za přispění tímto tipem. Chci vám poděkovat, že jste se zastavili. Uvidíme se příště na dalším netcastu z.
Stáhnout soubor
Stáhněte si ukázkový soubor zde: Podcast2004.xlsx
