5 preprostih načinov za izračun tekoče seštevke v Excelu (kumulativna vsota)

Tek skupaj (imenovan tudi kumulativna vsota) se pogosto uporablja v mnogih situacijah. To je metrika, ki vam pove, koliko je vsota dosedanjih vrednosti.

Na primer, če imate mesečne podatke o prodaji, vam tekoči seštevek pove, koliko prodaje je bilo opravljenega do določenega dne od prvega dne v mesecu.

Obstajajo tudi nekatere druge situacije, v katerih se pogosto uporablja tekoči seštevek, na primer izračun stanja gotovine v bančnih izpiskih/knjigi, štetje kalorij v vašem obroku itd.

V Microsoft Excelu obstaja več različnih načinov za izračun tekočih seštevkov.

Izbira metode je odvisna tudi od tega, kako so podatki strukturirani.

Na primer, če imate preproste tabelarne podatke, lahko uporabite preprosto formulo SUM, če pa imate Excelovo tabelo, je najbolje uporabiti strukturirane reference. Za to lahko uporabite tudi Power Query.

V tej vadnici bom obravnaval vse te različne metode izračunajte skupne vsote v Excelu.

Pa začnimo!

Izračun tekoče seštevke s tabelarnimi podatki

Če imate tabelarne podatke (tj. Tabelo v Excelu, ki ni pretvorjena v Excelovo tabelo), lahko uporabite nekaj preprostih formul za izračun tekočih seštevkov.

Uporaba operaterja dodajanja

Recimo, da imate datumske podatke o prodaji in želite izračunati tekočo seštevek v stolpcu C.

Spodaj so navedeni koraki za to.

Korak 1 - Vnesite celico C2, ki je prva celica, v kateri želite prikazati seštevek

= B2

To bo preprosto dobilo enake prodajne vrednosti v celici B2.

2. korak - V celico C3 vnesite spodnjo formulo:

= C2+B3

3. korak - Formulo uporabite za celoten stolpec. Z ročico Fill ga lahko izberete in povlečete ali pa preprosto kopirate in prilepite celico C3 v vse preostale celice (kar bi samodejno prilagodilo referenco in dalo pravi rezultat).

Tako boste dobili rezultat, kot je prikazano spodaj.

To je zelo preprosta metoda in v večini primerov dobro deluje.

Logika je preprosta - vsaka celica pobere vrednost nad njo (ki je kumulativna vsota do datuma prej) in doda vrednost v celici, ki je poleg nje (kar je prodajna vrednost za ta dan).

Obstaja le ena pomanjkljivost - če izbrišete katero koli od obstoječih vrstic v tem naboru podatkov, bodo vse celice spodaj vrnile referenčno napako (#REF!)

Če je z vašim naborom podatkov to mogoče, uporabite naslednjo metodo, ki uporablja formulo SUM

Uporaba SUM z delno zaklenjenimi referencami celic

Recimo, da imate datumske podatke o prodaji in želite izračunati tekočo vsoto v stolpcu C.

Spodaj je formula SUM, ki vam bo dala tekoči seštevek.

= SUM ($ B $ 2: B2)

Naj razložim, kako deluje ta formula.

V zgornji formuli SUM sem sklic uporabil za dodajanje kot $ B $ 2: B2

• $ B $ 2 - to je absolutna referenca, kar pomeni, da se pri sklicu iste formule v spodnje celice ta sklic ne bo spremenil. Tako bi se pri kopiranju formule v spodnjo celico formula spremenila v SUM ($ B $ 2: B3)
• B2 - to je drugi del sklica, ki je relativna referenca, kar pomeni, da se bo to prilagodilo, ko bom kopiral formulo navzdol ali na desno. Tako bo pri kopiranju formule v spodnjo celico ta vrednost postala B3

Preberite tudi: Absolutne, relativne in mešane sklice na celice v Excelu

Odlična stvar pri tej metodi je, da bi se v primeru, da izbrišete katero koli vrstico v naboru podatkov, ta formula prilagodila in vam vseeno dala prave tekoče vsote.

Izračun tekoče seštevke v Excelovi tabeli

Pri delu s tabelarnimi podatki v Excelu je dobro, da jih pretvorite v Excelovo tabelo. Veliko olajša upravljanje podatkov in olajša uporabo orodij, kot sta Power Query in Power Pivot.

Delo z Excelovimi tabelami prinaša prednosti, kot so strukturirane reference (kar olajša sklicevanje na podatke v tabeli in njihovo uporabo v formulah) ter samodejno prilagajanje sklicev v primeru, da podatke dodate ali izbrišete iz tabele.

Čeprav lahko še vedno uporabite zgornjo formulo, ki sem vam jo prikazal v Excelovi tabeli, naj vam pokažem nekaj boljših načinov za to.

Recimo, da imate Excelovo tabelo, kot je prikazano spodaj, in želite izračunati tekočo seštevek v stolpcu C.

Spodaj je formula, ki bo to naredila:

= SUM (SalesData [[#Headers], [Sale]]: [@Sale])

Zgornja formula je morda videti nekoliko dolga, vendar vam je ni treba napisati sami. kar vidite v formuli za vsoto, se imenujejo strukturirane reference, kar je učinkovit način Excela za sklicevanje na določene podatkovne točke v Excelovi tabeli.

Na primer, SalesData [[#Headers], [Sale]] se nanaša na glavo Sale v tabeli SalesData (SalesData je ime tabele Excel, ki sem jo dal, ko sem ustvaril tabelo)

In [@Sale] se nanaša na vrednost v celici v isti vrstici v stolpcu Prodaja.

To sem razložil tukaj za vaše razumevanje, vendar tudi če ne veste nič o strukturiranih referencah, lahko to formulo preprosto ustvarite.

Spodaj so navedeni koraki za to:

1. V celico C2 vnesite = SUM (
2. Izberite celico B1, ki je glava stolpca, ki ima prodajno vrednost. Uporabite lahko miško ali puščične tipke. Opazili boste, da Excel samodejno vnese strukturirano referenco za to celico
3. Dodajte: (simbol dvopičja)
4. Izberite celico B2. Excel bi znova samodejno vstavil strukturirano referenco za celico
5. Zaprite oklepaj in pritisnite enter

Opazili boste tudi, da vam formule ni treba kopirati v celotnem stolpcu, tabela Excel to samodejno naredi namesto vas.

Še ena odlična stvar pri tej metodi je, da bi v primeru, če v ta nabor podatkov dodate nov zapis, tabela Excel samodejno izračunala tekočo seštevek za vse nove zapise.

Medtem ko smo v stolpec vključili glavo stolpca, ne pozabite, da formula ne upošteva besedila glave in upošteva le podatke v stolpcu

Izračun skupne vrednosti z uporabo Power Query

Power Query je neverjetno orodje za povezovanje z bazami podatkov, pridobivanje podatkov iz več virov in njihovo preoblikovanje, preden jih vnesete v Excel.

Če že delate s programom Power Query, bi bilo učinkoviteje dodati tekoče seštevke, medtem ko preoblikujete podatke v samem urejevalniku Power Query (namesto da najprej vnesete podatke v Excel in nato dodate tekoče vsote z uporabo katerega koli od zgornjih zgoraj opisane metode).

Čeprav v Power Queryju ni vgrajene funkcije za dodajanje tekočih seštevkov (kar bi si želel), lahko to storite s preprosto formulo.

Recimo, da imate Excelovo tabelo, kot je prikazano spodaj, in želite tem podatkom dodati tekoče vsote:

Spodaj so navedeni koraki za to:

1. Izberite katero koli celico v tabeli Excel
2. Kliknite na Podatki
3. Na zavihku Get & Transform kliknite ikono iz Table/Range. To bo odprlo tabelo v urejevalniku Power Query
4. [Izbirno] Če stolpec Datum še ni razvrščen, kliknite ikono filtra v stolpcu Datum in nato kliknite Razvrsti naraščajoče
5. Kliknite urejevalnik Dodaj stolpec v urejevalniku Power Query
6. V skupini Splošno kliknite spustni meni Indeksni stolpec (ne kliknite ikone stolpca indeksa, ampak majhno črno nagnjeno puščico tik ob njej, da prikažete več možnosti)
7. Kliknite možnost »Od 1«. S tem boste dodali nov stolpec indeksa, ki bi se začel od enega in v celoten stolpec vpisal številke, ki se povečujejo za 1
8. Kliknite ikono »Stolpec po meri« (ki je tudi na zavihku Dodaj stolpec)
9. V pogovornem oknu po meri, ki se odpre, vnesite ime za nov stolpec. v tem primeru bom uporabil ime 'Running Total'
10. V polje Formula stolpca po meri vnesite spodnjo formulo: List.Sum (List.Range (#”Dodan indeks” [Prodaja], 0, [Indeks]))
11. Prepričajte se, da je na dnu pogovornega okna potrditveno polje z napisom »Sintaksne napake niso bile zaznane«
12. Kliknite V redu. To bi dodalo nov tekoči stolpec skupne vrednosti
13. Odstranite stolpec indeksa
14. Kliknite zavihek Datoteka in nato »Zapri in naloži«

Zgornji koraki bi v vaš delovni zvezek vstavili nov list s tabelo s tekočimi seštevki.

Če mislite, da je to le preveč korakov v primerjavi s prejšnjimi metodami uporabe preprostih formul, imate prav.

Če že imate nabor podatkov in morate le dodati skupne vsote, je bolje, da ne uporabljate Power Query.

Uporaba programa Power Query je smiselna, če morate izvleči podatke iz zbirke podatkov ali združiti podatke iz več različnih delovnih zvezkov, pri tem pa ji dodati tudi tekoče vsote.

Ko naredite to avtomatizacijo z uporabo Power Query, vam naslednjič, ko se vaš nabor podatkov spremeni, ni treba ponoviti, lahko preprosto osvežite poizvedbo in rezultat bo na podlagi novega nabora podatkov.

Kako to deluje?

Zdaj pa naj hitro razložim, kaj se dogaja pri tej metodi.

Prva stvar, ki jo naredimo v urejevalniku Power Query, je, da vstavimo indeksni stolpec, ki se začne od enega in povečuje za eno, ko gre navzdol po celicah.

To počnemo, ker moramo uporabiti ta stolpec, medtem ko izračunamo tekoči seštevek v drugem stolpcu, ki ga vstavimo v naslednjem koraku.

Nato vstavimo stolpec po meri in uporabimo spodnjo formulo

List.Sum (List.Range (#"Dodan indeks" [Prodaja], 0, [Indeks]))

To je formula List.Sum, ki bi vam dala vsoto obsega, ki je določen v njej.

In to območje je določeno s funkcijo List.Range.

Funkcija List.Range poda podani obseg v stolpcu za prodajo kot izhod in ta obseg se spreminja glede na vrednost indeksa. Na primer, za prvi zapis bi bil obseg preprosto prva prodajna vrednost. Ko se spuščate po celicah, bi se to območje razširilo.

Torej, za prvo celico. List.Sum bi vam dal le vsoto prve prodajne vrednosti, za drugo celico pa vsoto za prvi dve prodajni vrednosti itd.

Čeprav ta metoda dobro deluje, se z velikimi nabori podatkov - na tisoče vrstic - zelo počasi. Če imate opravka z velikim naborom podatkov in mu želite dodati tekoče vsote, si oglejte to vadnico, ki prikazuje druge hitrejše metode.

Izračun tekoče seštevke na podlagi meril

Doslej smo videli primere, kjer smo izračunali tekočo seštevek za vse vrednosti v stolpcu.

Lahko pa obstajajo primeri, ko želite izračunati tekočo seštevek za določene zapise.

Spodaj imam na primer niz podatkov in želim izračunati skupno delovanje za tiskalnike in optične bralnike ločeno v dveh različnih stolpcih.

To lahko storite s formulo SUMIF, ki izračuna tekočo seštevek, pri tem pa preveri, ali je izpolnjen določen pogoj.

Spodaj je formula, ki bo to naredila za stolpce tiskalnika:

= SUMIF ($ C $ 2: C2, $ D $ 1, $ B $ 2: B2)

Podobno za izračun skupne vrednosti skenerjev uporabite spodnjo formulo:

= SUMIF ($ C $ 2: C2, $ E $ 1, $ B $ 2: B2)

V zgornjih formulah sem uporabil SUMIF, ki bi mi dal vsoto v razponu, ko so izpolnjeni določeni kriteriji.

Formula ima tri argumente:

1. obseg: to je obseg meril, ki bi jih preverili glede na navedena merila
2. merila: to je merilo, ki bi ga preverili le, če bi bilo to merilo izpolnjeno, bi bile dodane vrednosti v tretjem argumentu, ki je obseg vsote
3. [sum_range]: to je obseg vsote, iz katerega bi bile dodane vrednosti, če bi bila merila izpolnjena

Tudi v obseg in sum_range argument, drugi del sklica sem zaklenil, tako da se bo obseg med spuščanjem po celicah še povečeval. To nam omogoča, da upoštevamo in dodajamo vrednosti le do tega območja (torej tekoče seštevke).

V tej formuli sem kot merilo uporabil stolpec glave (tiskalnik in optični bralnik). merila lahko tudi trdo kodirate, če glave stolpcev niso popolnoma enake besedilu meril.

Če imate več pogojev, ki jih morate preveriti, lahko uporabite formulo SUMIFS.

Izvajanje seštevka v vrtilnih tabelah

Če želite v rezultat vrtilne tabele dodati tekoče seštevke, lahko to preprosto storite z vgrajeno funkcijo v vrtilnih tabelah.

Recimo, da imate vrtilno tabelo, kot je prikazano spodaj, kjer imam datum v enem stolpcu, vrednost prodaje pa v drugem.

Spodaj so navedeni koraki za dodajanje dodatnega stolpca, ki bo prikazal tekočo skupno prodajo po datumu:

1. Povlecite polje Prodaja in ga vnesite v območje Vrednost.
2. To bo dodalo še en stolpec z vrednostmi prodaje
3. V polju Vrednost kliknite možnost Vsota prodaje2
4. Kliknite možnost »Nastavitve polja vrednosti«
5. V pogovornem oknu Nastavitve polja vrednosti spremenite ime po meri v »Tečejo seštevki«
6. Kliknite zavihek »Prikaži vrednost kot«
7. V spustnem meniju Pokaži vrednost kot izberite možnost »Teče skupaj v«
8. V možnostih polja Base se prepričajte, da je izbran Date
9. Kliknite V redu

Zgornji koraki bi spremenili drugi stolpec prodaje v stolpec Running Total.

To je torej nekaj načinov, s katerimi lahko izračunate tekočo seštevek v Excelu. Če imate podatke v obliki tabele, lahko uporabite preproste formule, če pa imate tabelo Excel, potem lahko uporabite formule, ki uporabljajo strukturirane reference.

Prav tako sem obravnaval, kako izračunati skupno tekočino z uporabo Power Query in v vrtilnih tabelah.

Upam, da vam je bila ta vadnica koristna.