Bežecký súčet (nazývaný aj kumulatívna suma) sa v mnohých situáciách používa úplne bežne. Je to metrika, ktorá vám povie, aký je súčet týchto hodnôt doteraz.
Ak máte napríklad údaje o mesačných tržbách, bežný súčet vám povie, koľko predajov sa vykonalo do konkrétneho dňa od prvého dňa v mesiaci.
Existujú aj niektoré ďalšie situácie, kde sa často používa bežný súčet, ako je napríklad výpočet zostatku v hotovosti vo vašich bankových výpisoch/knihe, počítanie kalórií do vášho jedálnička atď.
V programe Microsoft Excel existuje niekoľko rôznych spôsobov výpočtu priebežných súčtov.
Metóda, ktorú si vyberiete, bude závisieť aj od štruktúry vašich údajov.
Ak máte napríklad jednoduché tabuľkové údaje, môžete použiť jednoduchý vzorec SUM, ale ak máte tabuľku programu Excel, je najlepšie použiť štruktúrované odkazy. Na to môžete použiť aj Power Query.
V tomto návode sa zameriam na všetky tieto rôzne metódy vypočítajte priebežné súčty v Exceli.
Začnime teda!
Výpočet priebežného súčtu pomocou tabuľkových údajov
Ak máte tabuľkové údaje (t. J. Tabuľku v programe Excel, ktorá nie je prevedená na tabuľku programu Excel), na výpočet priebežných súčtov môžete použiť niekoľko jednoduchých vzorcov.
Použitie doplnkového operátora
Predpokladajme, že máte údaje o predajoch podľa dátumu a chcete vypočítať priebežný súčet v stĺpci C.
Nasledujú kroky, ako to urobiť.
Krok 1 - Do bunky C2, ktorá je prvou bunkou, v ktorej chcete mať bežiaci súčet, zadajte
= B2
Jednoducho získate rovnaké predajné hodnoty v bunke B2.
Krok 2 - Do bunky C3 zadajte nasledujúci vzorec:
= C2+B3
Krok 3 - Vzorec aplikujte na celý stĺpec. Môžete použiť úchyt Vyplniť ho vybrať a presunúť, alebo jednoducho skopírovať a prilepiť bunku C3 do všetkých zostávajúcich buniek (čím by sa odkaz automaticky upravil a poskytol správny výsledok).
To vám poskytne výsledok, ako je uvedené nižšie.
Je to skutočne jednoduchá metóda a vo väčšine prípadov funguje dobre.
Logika je jednoduchá - každá bunka vyzdvihne hodnotu nad ňou (čo je kumulatívny súčet do predchádzajúceho dátumu) a pridá hodnotu do bunky, ktorá je vedľa nej (čo je predajná hodnota za daný deň).
Existuje iba jedna nevýhoda - v prípade, že odstránite ktorýkoľvek z existujúcich riadkov v tejto množine údajov, všetky bunky nižšie, ktoré by vrátili referenčnú chybu (#REF!)
Ak je to možné s vašou množinou údajov, použite ďalšiu metódu, ktorá používa vzorec SUM
Použitie SUM s čiastočne zamknutou referenciou bunky
Predpokladajme, že máte údaje o predajoch podľa dátumu a chcete vypočítať priebežný súčet v stĺpci C.
Nasleduje vzorec SUMA, ktorý vám poskytne celkový súčet.
= SUM ($ B $ 2: B2)
Vysvetlím, ako tento vzorec funguje.
Vo vyššie uvedenom vzorci SUM som použil odkaz na pridanie ako $ B $ 2: B2
- $ B $ 2 - toto je absolútna referencia, čo znamená, že keď skopírujem rovnaký vzorec do buniek nižšie, táto referencia sa nezmení. Pri kopírovaní vzorca do bunky nižšie sa teda vzorec zmení na SUM ($ B $ 2: B3)
- B2 - toto je druhá časť referencie, ktorá je relatívnou referenciou, čo znamená, že pri kopírovaní vzorca nadol alebo doprava sa to upraví. Takže pri kopírovaní vzorca do bunky nižšie sa z tejto hodnoty stane B3
Prečítajte si tiež: Absolútne, relatívne a zmiešané bunkové referencie v programe Excel
Skvelé na tejto metóde je, že v prípade, že odstránite niektorý z riadkov v množine údajov, tento vzorec sa upraví a stále vám poskytne správne priebežné súčty.
Výpočet priebežného súčtu v tabuľke programu Excel
Pri práci s tabuľkovými údajmi v programe Excel je vhodné ich previesť do tabuľky programu Excel. To veľmi uľahčuje správu údajov a tiež umožňuje jednoduché používanie nástrojov, ako sú Power Query a Power Pivot.
Práca s tabuľkami programu Excel prináša výhody, ako sú štruktúrované referencie (čo skutočne uľahčuje odkazovanie na údaje v tabuľke a ich použitie vo vzorcoch) a automatické prispôsobovanie referencií v prípade, že údaje z tabuľky pridávate alebo odstraňujete.
Aj keď stále môžete použiť vyššie uvedený vzorec, ktorý som vám ukázal v tabuľke programu Excel, ukážem vám niekoľko lepších spôsobov, ako to urobiť.
Predpokladajme, že máte tabuľku programu Excel, ako je uvedené nižšie, a chcete vypočítať priebežný súčet v stĺpci C.
Nasleduje vzorec, ktorý to urobí:
= SUM (Údaje o predaji [[#hlavičky], [Predaj]]: [@Výpredaj])
Vyššie uvedený vzorec môže vyzerať trochu dlho, ale nemusíte ho písať sami. to, čo vidíte v súčtovom vzorci, sa nazýva štruktúrované referencie, čo je účinný spôsob, ako Excel odkazovať na konkrétne údajové body v tabuľke programu Excel.
Napríklad SalesData [[#Headers], [Sale]] odkazuje na hlavičku Sale v tabuľke SalesData (SalesData je názov tabuľky programu Excel, ktorú som zadal pri vytváraní tabuľky)
A [@Sale] odkazuje na hodnotu v bunke v rovnakom riadku v stĺpci Predaj.
Práve som to vysvetlil, aby ste to pochopili, ale aj keď neviete nič o štruktúrovaných referenciách, tento vzorec môžete ľahko vytvoriť.
Nasledujú kroky, ako to urobiť:
- Do bunky C2 zadajte = SUM (
- Vyberte bunku B1, ktorá je hlavičkou stĺpca s hodnotou predaja. Môžete použiť myš alebo klávesy so šípkami. Všimnete si, že Excel automaticky zadá štruktúrovaný odkaz pre túto bunku
- Pridajte: (symbol dvojbodky)
- Vyberte bunku B2. Excel by znova automaticky vložil štruktúrovaný odkaz do bunky
- Zatvorte zátvorku a stlačte kláves Enter
Tiež si všimnete, že vzorec nemusíte kopírovať do celého stĺpca, tabuľka programu Excel to urobí automaticky za vás.
Ďalšou skvelou vecou na tejto metóde je, že v prípade, že do tejto množiny údajov pridáte nový záznam, tabuľka programu Excel automaticky vypočíta priebežný súčet pre všetky nové záznamy.
Aj keď sme do nášho vzorca zahrnuli hlavičku stĺpca, nezabudnite, že vzorec bude ignorovať text hlavičky a bude brať do úvahy iba údaje v stĺpci
Výpočet celkového behu pomocou Power Query
Power Query je úžasný nástroj, pokiaľ ide o prepojenie s databázami, extrahovanie údajov z viacerých zdrojov a ich transformáciu pred vložením do programu Excel.
Ak už pracujete s Power Query, bolo by efektívnejšie pridať priebežné súčty počas transformácie údajov v samotnom editore Power Query (namiesto 1. získania údajov v programe Excel a následného pridania bežeckých súčtov pomocou ktoréhokoľvek z vyššie uvedených metódy popísané vyššie).
Aj keď v Power Query nie je vstavaná funkcia na pridanie bežeckých súčtov (čo by som si želal, aby bola), stále to môžete urobiť pomocou jednoduchého vzorca.
Predpokladajme, že máte tabuľku programu Excel, ako je uvedené nižšie, a chcete k týmto údajom pridať priebežné súčty:
Nasledujú kroky, ako to urobiť:
- Vyberte ľubovoľnú bunku v tabuľke programu Excel
- Kliknite na Údaje
- Na karte Získať a transformovať kliknite na ikonu Z tabuľky/rozsahu. Tabuľka sa otvorí v editore Power Query
- [Voliteľné] V prípade, že váš stĺpec Dátum už nie je zoradený, kliknite na ikonu filtra v stĺpci Dátum a potom kliknite na položku Zoradiť vzostupne
- V editore Power Query kliknite na kartu Pridať stĺpec
- V skupine Všeobecné kliknite na rozbaľovaciu ponuku Indexový stĺpček (neklikajte na ikonu Indexový stĺpček, ale na malú čiernu naklonenú šípku hneď vedľa, aby sa zobrazili ďalšie možnosti)
- Kliknite na možnosť „Od 1“. V takom prípade sa pridá nový stĺpec indexu, ktorý bude začínať od jedného a do celého stĺpca bude zadávať čísla zvyšujúce sa o 1
- Kliknite na ikonu „Vlastný stĺpček“ (ktorá sa nachádza aj na karte Pridať stĺpec)
- V dialógovom okne vlastného stĺpca, ktoré sa otvorí, zadajte názov nového stĺpca. v tomto prípade použijem názov „Beh celkom“
- Do poľa Vzorec vlastného stĺpca zadajte nasledujúci vzorec: List.Sum (List.Range (#”Pridaný index“ [Predaj], 0, [Index]))
- Uistite sa, že v spodnej časti dialógového okna je začiarkavacie políčko s textom „Neboli zistené žiadne chyby syntaxe“
- Kliknite na tlačidlo OK. Tým by sa pridal nový stĺpec priebežného súčtu
- Odstráňte stĺpček indexu
- Kliknite na kartu Súbor a potom kliknite na „Zavrieť a načítať“
Vyššie uvedené kroky by vložili do vášho zošita nový list s tabuľkou, ktorá obsahuje priebežné súčty.
Ak si teraz myslíte, že je to príliš veľa krokov v porovnaní s predchádzajúcimi metódami používania jednoduchých vzorcov, máte pravdu.
Ak už máte množinu údajov a všetko, čo musíte urobiť, je pridať celkové súčty, je lepšie nepoužívať Power Query.
Použitie Power Query má zmysel tam, kde musíte extrahovať údaje z databázy alebo kombinovať údaje z viacerých rôznych zošitov a pritom do nej pridávať aj priebežné súčty.
Akonáhle vykonáte túto automatizáciu pomocou Power Query, pri ďalšej zmene sady údajov to nebudete musieť urobiť znova. Môžete jednoducho obnoviť dotaz a poskytne vám výsledok na základe novej sady údajov.
Ako to funguje?
Teraz mi rýchlo vysvetlite, čo sa deje pri tejto metóde.
Prvá vec, ktorú urobíme v editore Power Query, je vloženie stĺpca indexu začínajúceho od jedného a zvyšujúceho sa o jeden, keď ide do buniek.
Robíme to preto, že tento stĺpec musíme použiť, kým vypočítame priebežný súčet v inom stĺpci, ktorý vložíme v nasledujúcom kroku.
Potom vložíme vlastný stĺpec a použijeme nasledujúci vzorec
List.Sum (List.Range (#"Pridaný index" [predaj], 0, [index]))
Toto je vzorec List.Sum, ktorý by vám poskytol súčet rozsahu, ktorý je v ňom uvedený.
A tento rozsah je určený pomocou funkcie List.Range.
Funkcia List.Range uvádza ako výstup zadaný rozsah v stĺpci predaja a tento rozsah sa mení na základe hodnoty indexu. Napríklad pre prvý záznam bude rozsah jednoducho hodnotou prvého predaja. A ako pôjdete po bunkách nadol, tento rozsah by sa rozšíril.
Takže pre prvú bunku. List.Sum vám poskytne iba súčet hodnoty prvého predaja a pre druhú bunku vám poskytne súčet prvých dvoch hodnôt predaja a podobne.
Aj keď táto metóda funguje dobre, pri veľkých množinách údajov - tisíckach riadkov je skutočne pomalá. Ak máte do činenia s veľkým súborom údajov a chcete k nemu pridať celkové súčty, pozrite sa na tento návod, ktorý ukazuje ďalšie metódy, ktoré sú rýchlejšie.Výpočet bežeckého súčtu na základe kritérií
Doteraz sme videli príklady, kde sme vypočítali priebežný súčet pre všetky hodnoty v stĺpci.
Môžu však nastať prípady, keď chcete vypočítať priebežný súčet pre konkrétne záznamy.
Napríklad nižšie mám súbor údajov a chcem vypočítať bežiaci súčet pre tlačiarne a skenery oddelene v dvoch rôznych stĺpcoch.
To je možné vykonať pomocou vzorca SUMIF, ktorý vypočíta priebežný súčet a uistí sa, že je splnená zadaná podmienka.
Nasleduje vzorec, ktorý to urobí pre stĺpce Tlačiareň:
= SUMIF ($ C $ 2: C2, $ D $ 1, $ B $ 2: B2)
Podobne na výpočet priebežného súčtu pre skenery použite nasledujúci vzorec:
= SUMIF ($ C $ 2: C2, $ E $ 1, $ B $ 2: B2)
Vo vyššie uvedených vzorcoch som použil SUMIF, ktorý by mi poskytol súčet v rozsahu, ak sú splnené uvedené kritériá.
Vzorec má tri argumenty:
- rozsah: toto je rozsah kritérií, ktorý sa bude kontrolovať podľa zadaných kritérií
- kritériá: toto sú kritériá, ktoré by boli začiarknuté iba vtedy, ak je toto kritérium splnené, potom by boli pridané hodnoty v treťom argumente, ktorým je rozsah súčtov
- [rozsah_súčtu]: toto je rozsah súčtov, z ktorého by sa hodnoty pridali, ak sú splnené kritériá
Tiež v rozsah a rozsah_sumu Argument, uzamkol som druhú časť referencie, takže keď ideme dolu bunkami, rozsah sa bude stále rozširovať. To nám umožňuje iba zvažovať a pridávať hodnoty do tohto rozsahu (teda súčty).
V tomto vzorci som použil ako kritérium stĺpec záhlavia (tlačiareň a skener). Kritériá môžete tiež zadať v pevnom kóde, ak sa hlavičky stĺpcov nezhodujú presne s textom kritérií.
V prípade, že máte viac podmienok, ktoré musíte skontrolovať, môžete použiť vzorec SUMIFS.
Celkový počet v kontingenčných tabuľkách
Ak chcete pridať priebežné súčty do výsledku kontingenčnej tabuľky, môžete to ľahko urobiť pomocou vstavanej funkcie v kontingenčných tabuľkách.
Predpokladajme, že máte kontingenčnú tabuľku, ako je uvedené nižšie, kde v jednom stĺpci mám dátum a v druhom stĺpci hodnotu predaja.
Nasledujú kroky na pridanie dodatočného stĺpca, ktorý bude zobrazovať priebežný súčet tržieb podľa dátumu:
- Presuňte pole Predaj a vložte ho do oblasti Hodnota.
- Tým sa pridá ďalší stĺpec s hodnotami predaja
- Kliknite na možnosť Súčet predaja2 v oblasti Hodnota
- Kliknite na možnosť „Nastavenia poľa hodnoty“
- V dialógovom okne Nastavenia poľa hodnoty zmeňte vlastný názov na „Bežné súčty“
- Kliknite na kartu „Zobraziť hodnotu ako“
- V rozbaľovacom zozname Zobraziť hodnotu ako vyberte možnosť „Running Total in“
- V možnostiach poľa Základné skontrolujte, či je vybratý dátum
- Kliknite na Ok
Vyššie uvedené kroky by zmenili druhý stĺpec predaja na stĺpec Bežiaci súčet.
Toto je teda niekoľko spôsobov, ktoré môžete použiť na výpočet priebežného súčtu v programe Excel. Ak máte údaje v tabuľkovom formáte, môžete použiť jednoduché vzorce a ak máte tabuľku v Exceli, môžete použiť vzorce, ktoré používajú štruktúrované odkazy.
Tiež som sa zaoberal tým, ako vypočítať celkový beh pomocou Power Query a v kontingenčných tabuľkách.
Dúfam, že ste našli tento návod užitočný.
