priebežný celkový počet (známy aj akokumulatívna suma) sa v mnohých prípadoch používa veľmi bežne.Toto je indikátor, ktorý vám povie, aký je súčet doterajších hodnôt.
Ak máte napríklad mesačné údaje o predaji, priebežný súčet vám povie, koľko predajov sa uskutočnilo od prvého dňa v mesiaci do konkrétneho dátumu.
Existujú aj iné situácie, v ktorých sa často používajú súčty, ako napríklad výpočet hotovostných zostatkov v bankových výpisoch/účtovných knihách, výpočet kalórií v plánoch stravovania atď.
V programe Microsoft Excel existuje niekoľko rôznych spôsobov výpočtu priebežných súčtov.
Spôsob, ktorý zvolíte, závisí aj od štruktúry údajov.
Napríklad, ak máte jednoduché tabuľkové údaje, môžete použiť jednoduchý vzorec SUM, ale ak máte excelovskú tabuľku, je lepšie použiť štruktúrované odkazy.Môžete na to použiť aj Power Query.
V tomto návode sa budem venovať všetkýmVypočítajte priebežný súčet v Excelirôzne metódy.
Tak poďme na to!
Obsah
Vypočítajte priebežné súčty pomocou tabuľkových údajov
Ak máte tabuľkové údaje (t. j. tabuľky v Exceli, ktoré nie sú skonvertované na excelové tabuľky), môžete na výpočet priebežných súčtov použiť niekoľko jednoduchých vzorcov.
Použite operátor sčítania
Predpokladajme, že máte údaje o predaji podľa dátumu a chcete在 v stĺpci CvypočítaťPriebežný súčet.
Tu sú kroky, ako to urobiť.
krok 1 – Do bunky C2 zadajte prvú bunku, pre ktorú chcete spustiť súčet
= B2
Tým sa získa rovnaká hodnota predaja v bunke B2.
Krok 2– Do bunky C3 zadajte nasledujúci vzorec:
= C2 + B3
Krok 3– Použite vzorec na celý stĺpec.Na výber a presunutie môžete použiť rukoväť výplne alebo jednoducho skopírujte a prilepte bunku C3 do všetkých zostávajúcich buniek (tým sa automaticky upraví odkaz a poskytne sa správny výsledok).
Získate tak výsledok uvedený nižšie.
Ide o veľmi jednoduchú metódu, ktorá vo väčšine prípadov funguje dobre.
Logika je jednoduchá – každá bunka prevezme svoju hodnotu (čo je kumulatívny súčet za predchádzajúci deň) a pridá hodnotu v susednej bunke (čo je predajná hodnota aktuálneho dňa).
Má to len jednu nevýhodu – ak vymažete akýkoľvek existujúci riadok v tejto množine údajov, všetky bunky nižšie vrátia chybu odkazu (#REF!)
Ak má vaša množina údajov túto možnosť, použite nasledujúcu metódu pomocou vzorca SUM
Použite SUM s čiastočne uzamknutými odkazmi na bunky
Predpokladajme, že máte údaje o predaji podľa dátumu a chcete vypočítať priebežný súčet v stĺpci C.
Nižšie je uvedený vzorec SUM, ktorý vám poskytne priebežný súčet.
=SUM($B$2:B2)
Dovoľte mi vysvetliť, ako tento vzorec funguje.
Vo vzorci SUM vyššie som použil odkaz na pridanie ako $ B $ 2: B2
- $B$2 – Toto je absolútny odkaz, čo znamená, že tento odkaz sa nezmení, keď skopírujem rovnaký vzorec do bunky nižšie.Takže keď skopírujete vzorec v bunke nižšie, vzorec sa zmení na SUM($B$2:B3)
- B2 – Toto je druhá časť referencie, je to relatívna referencia, čo znamená, že keď skopírujem vzorec dole alebo vpravo, upraví sa to.Takže pri kopírovaní vzorca v bunke nižšie sa hodnota zmení na B3
Na tomto prístupe je pekné, že ak odstránite akékoľvek riadky v množine údajov, tento vzorec sa upraví a stále vám poskytne správny priebežný súčet.
Vypočítajte priebežný súčet v hárku programu Excel
Pri práci s tabuľkovými údajmi v Exceli je najlepšie ich previesť na excelovú tabuľku.Zjednodušuje správu údajov a tiež uľahčuje používanie nástrojov, ako sú Power Query a Power Pivot.
Používanie excelovej tabuľky poskytuje niekoľko výhod, ako sú štruktúrované odkazy (ktoré veľmi zjednodušujú odkazovanie na údaje v tabuľke a ich použitie vo vzorcoch) a automatickú úpravu odkazov pri pridávaní alebo odstraňovaní údajov z tabuľky.
Aj keď stále môžete použiť vyššie uvedený vzorec, ktorý som vám ukázal v hárku programu Excel, dovoľte mi ukázať vám niekoľko lepších spôsobov, ako to urobiť.
Predpokladajme, že máte tabuľku programu Excel, ako je tá nižšie, a chcete vypočítať priebežný súčet v stĺpci C.
Tu je vzorec, ako to urobiť:
=SUM(Údaje o predaji[[#Headers],[Výpredaj]]:[@Výpredaj])
Vyššie uvedený vzorec sa môže zdať trochu dlhý, ale nemusíte ho písať sami.To, čo vidíte v súhrnnom vzorci, sa nazýva štruktúrovaný odkaz a je to efektívny spôsob, ako Excel odkazovať na konkrétne údajové body v excelovej tabuľke.
Napríklad SalesData[[#Headers],[Sale]] odkazuje na hlavičku Sales v tabuľke SalesData (SalesData je názov excelovej tabuľky, ktorú som dal pri vytváraní tabuľky)
Zatiaľ čo [@Predaj] odkazuje na hodnotu v bunke v rovnakom riadku stĺpca Predaj.
Vysvetľujem to tu len pre vaše pochopenie, ale aj keď neviete nič o štruktúrovaných referenciách, stále môžete ľahko vytvoriť tento vzorec.
Tu sú 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šimnite si, že Excel automaticky zadá štruktúrovaný odkaz na bunku
- Pridať: (dvojbodka)
- Vyberte bunku B2.Excel opäť automaticky vloží štruktúrované odkazy na bunky
- Zatvorte zátvorku a stlačte kláves Enter
Tiež si všimnete, že nemusíte kopírovať celý stĺpec vzorcov, hárok Excel to urobí za vás automaticky.
Ďalšou výhodou tohto prístupu je, že ak do tejto množiny údajov pridáte nový záznam, hárok Excel automaticky vypočíta priebežný súčet pre všetky nové záznamy.
Hoci sme do vzorca zahrnuli hlavičky stĺpcov, majte na pamäti, že vzorec ignoruje text hlavičky a zohľadňuje iba údaje v stĺpci
Vypočítajte priebežné súčty pomocou Power Query
Power Query je úžasný nástroj, pokiaľ ide o pripojenie k databázam, extrahovanie údajov z viacerých zdrojov a ich transformáciu pred vložením do Excelu.
Ak už používate Power Query, je efektívnejšie pridať priebežné súčty pri transformácii údajov v samotnom editore Power Query (namiesto toho, aby ste najprv získali údaje v Exceli a potom pridali priebežné súčty pomocou niektorej z vyššie uvedených metód).
Aj keď v Power Query nie je žiadna vstavaná funkcia na pridávanie priebežných súčtov (prial by som si, aby existovala), stále to môžete urobiť pomocou jednoduchého vzorca.
Predpokladajme, že máte tabuľku programu Excel, ktorá vyzerá takto, a chcete k týmto údajom pridať priebežné súčty:
Tu sú kroky, ako to urobiť:
- Vyberte ľubovoľnú bunku v excelovej tabuľke
- údaje o kliknutí
- Na karte Získať a transformovať kliknite na ikonu z tabuľky/rozsahu.Tým sa otvorí tabuľka v editore Power Query
- [Voliteľné] Ak stĺpec dátumu ešte nie je zoradený, kliknite na ikonu filtra v stĺpci dátumu a potom kliknite na položku Zoradiť vzostupne
- Kliknite na kartu Pridať stĺpec v editore Power Query
- V skupine Všeobecné kliknite na rozbaľovaciu ponuku Indexový stĺpec (neklikajte na ikonu Indexový stĺpec, ale na malú čiernu šikmú šípku vedľa nej, čím zobrazíte ďalšie možnosti)
- Kliknite na možnosť „Od 1“.Ak tak urobíte, pridá sa nový indexový stĺpec, ktorý bude začínať na 1 a zadá čísla zvýšené o 1 do celého stĺpca
- Kliknite na ikonu Vlastné stĺpce (tiež na karte Pridať stĺpce)
- V dialógovom okne Vlastné stĺpce, ktoré sa otvorí, zadajte názov nového stĺpca.V tomto príklade použijem názov „Running Total“
- Do poľa Vzorec vlastného stĺpca zadajte nasledujúci vzorec:List.Sum(List.Range(#"Added Index"[Predaj],0,[Index]))
- Uistite sa, že v spodnej časti dialógového okna je začiarkavacie políčko „Neboli zistené žiadne chyby syntaxe“
- Kliknite na tlačidlo OK.Tým sa pridá nový stĺpec priebežného súčtu
- stĺpec indexu poklesu
- Kliknite na kartu Súbor a potom kliknite na položku Zavrieť a načítať
Vyššie uvedené kroky vložia do vášho zošita nový pracovný hárok s tabuľkou s priebežnými súčtami.
Teraz, ak si myslíte, že je to príliš veľa krokov v porovnaní s predchádzajúcim prístupom s jednoduchými vzorcami, máte pravdu.
Ak už máte množinu údajov a všetko, čo musíte urobiť, je pridať priebežné súčty, potom je najlepšie nepoužívať Power Query.
Používanie Power Query má zmysel v situáciách, keď musíte extrahovať údaje z databázy alebo skombinovať údaje z niekoľkých rôznych zošitov a tiež k nim v procese pridať priebežné súčty.
Keď to zautomatizujete pomocou Power Query, pri ďalšej zmene množiny údajov to už nemusíte robiť znova, stačí obnoviť dotaz a poskytne vám výsledky založené na novej množine údajov.
Ako to funguje?
Teraz mi dovoľte rýchlo vysvetliť, čo sa stane s týmto prístupom.
Prvá vec, ktorú urobíme v editore Power Query, je vloženie stĺpca indexu, ktorý začína na 1 a postupne sa zvyšuje v bunke.
Robíme to preto, lebo tento stĺpec potrebujeme použiť pri výpočte priebežného súčtu v inom stĺpci, ktorý sa vloží v ďalšom kroku.
Potom vložíme vlastný stĺpec a použijeme nasledujúci vzorec
List.Sum(List.Range(#"Added Index"[Predaj],0,[Index]))
Toto je vzorec List.Sum, ktorý vám poskytne súčet rozsahu špecifikovaného v ňom.
Rozsah je určený pomocou funkcie List.Range.
Funkcia List.Range berie ako výstup špecifikovaný rozsah v stĺpci predaja a rozsah sa mení podľa hodnoty Index.Napríklad pre prvý záznam je rozsah prvou predajnou hodnotou.Tento rozsah sa rozširuje, keď prechádzate bunkou.
Takže pre prvú bunku.List.Sum vám dá len súčet prvej predajnej hodnoty, pre druhú bunku vám dá súčet prvých dvoch predajných hodnôt atď.
Aj keď tento prístup funguje dobre, môže byť veľmi pomalý pre veľké množiny údajov (tisíce riadkov).
Vypočítajte priebežné súčty na základe kritérií
Doteraz sme videli príklady výpočtu priebežného súčtu všetkých hodnôt v stĺpci.
V niektorých prípadoch však možno budete chcieť vypočítať priebežný súčet pre konkrétny záznam.
Napríklad nižšie mám množinu údajov, kde chcem spočítať celkový počet tlačiarní a skenerov v dvoch rôznych stĺpcoch.
Dá sa to urobiť pomocou vzorca SUMIF, ktorý vypočíta priebežný súčet a zároveň zabezpečí splnenie špecifikovaných podmienok.
Tu je vzorec, ako to urobiť pre stĺpec tlačiarne:
=SUMIF($C$2:C2,$D$1,$B$2:B2)
Podobne na výpočet priebežného súčtu skenera použite nasledujúci vzorec:
=SUMIF($C$2:C2,$E$1,$B$2:B2)
Vo vyššie uvedenom vzorci som použil SUMIF, ktorý mi pri splnení špecifikovaných podmienok poskytne sumu v rozsahu.
Vzorec má tri parametre:
- rozsah: Toto je rozsah podmienok, ktoré sa budú porovnávať so zadanou podmienkou
- Kritériá: toto je kritérium, ktoré sa skontroluje iba vtedy, ak je toto kritérium splnené, potom sa pridá hodnota v treťom parametri, rozsah súčtu
- [rozsah_sumu]: toto je rozsah súčtu hodnôt, ktoré sa majú pridať, keď je podmienka splnená
Okrem toho vrozsahArozsah_sumuparameter, uzamkol som druhú časť odkazu, takže keď sa pohybujeme po bunke nadol, rozsah sa neustále rozširuje.To nám umožňuje brať do úvahy a pridávať hodnoty iba do tohto rozsahu (teda priebežný súčet).
V tomto vzorci používam ako kritérium stĺpec hlavičky (tlačiareň a skener).Štandard môžete tiež napevno zakódovať, ak nadpisy stĺpcov nie sú úplne rovnaké ako štandardný text.
Ak potrebujete skontrolovať viacero podmienok, môžete použiť vzorec SUMIFS.
Priebežné súčty v kontingenčnej tabuľke
Ak chcete k výsledkom kontingenčnej tabuľky pridať priebežný súčet, môžete to jednoducho urobiť pomocou vstavanej funkcie v kontingenčných tabuľkách.
Predpokladajme, že máte kontingenčnú tabuľku, ako je tá nižšie, s dátumami v jednom stĺpci a hodnotami predaja v inom.
Tu sú kroky na pridanie ďalšieho stĺpca, ktorý bude zobrazovať kumulatívne predaje podľa dátumu:
- Presuňte pole Predaj a pustite ho do oblasti Hodnota.
- Tým sa pridá ďalší stĺpec s hodnotou predaja
- Kliknite na možnosť Celkový predaj 2 v oblasti Hodnota
- Kliknite na možnosť Nastavenia poľa hodnôt
- V dialógovom okne Nastavenia poľa hodnôt zmeňte Vlastný názov na Priebežný súčet
- Kliknite na kartu „Zobraziť hodnotu ako“.
- V rozbaľovacej ponuke Zobraziť hodnotu ako vyberte možnosť „Súčet v priebehu“.
- V možnostiach Základné polia skontrolujte, či je vybratá možnosť Dátum
- Kliknite na tlačidlo OK
Vyššie uvedené kroky zmenia druhý stĺpec predaja na stĺpec „bežný súčet“.
Toto sú niektoré z metód, ktoré môžete použiť na výpočet priebežných súčtov v Exceli.Ak máte údaje v tabuľkovom formáte, môžete použiť jednoduché vzorce a ak máte tabuľky programu Excel, môžete použiť vzorce, ktoré využívajú štruktúrované odkazy.
Tiež som sa venoval tomu, ako vypočítať priebežné súčty pomocou Power Query a kontingenčných tabuliek.
Dúfam, že vám bol tento návod užitočný.
