Izračunajte tekoče vsote (kumulativne vsote) v Excelu - 5 preprostih načinov

tekoči skupni znesek (znan tudi kotkumulativna vsota) se v mnogih primerih zelo pogosto uporablja.To je indikator, ki vam pove, kakšna je vsota dosedanjih vrednosti.

Na primer, če imate mesečne podatke o prodaji, vam bo tekoča vsota povedala, koliko prodaj je bilo opravljenih od prvega dne v mesecu do določenega datuma.

Obstajajo tudi druge situacije, kjer se pogosto uporabljajo vsote, kot je izračun stanja gotovine na bančnih izpiskih/knjigah, izračun kalorij v načrtih obrokov itd.

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

Metoda, ki jo izberete, je odvisna tudi od strukture podatkov.

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

V tej vadnici jih bom obravnaval vseIzračunajte tekočo vsoto v Excelurazlične metode.

Pa začnimo!

Izračunajte tekoče vsote s pomočjo tabelarnih podatkov

Če imate tabelarne podatke (to je tabele v Excelu, ki niso pretvorjene v Excelove tabele), lahko uporabite nekaj preprostih formul za izračun tekočih vsot.

Uporabite operator seštevanja

Recimo, da imate podatke o prodaji po datumu in jih želite在 v stolpcu C計算Tekoče skupno.

Tukaj so koraki za to.

korak 1 – V celico C2 vnesite prvo celico, za katero želite zagnati vsoto

= B2

To samo dobi enako prodajno vrednost v celici B2.

2. korak– V celico C3 vnesite naslednjo formulo:

= C2 + B3

3. korak– Formulo uporabite za celoten stolpec.Uporabite lahko ročico za polnjenje, da jo izberete in povlečete, ali preprosto kopirate in prilepite celico C3 v vse preostale celice (to bo samodejno prilagodilo referenco in dalo pravilen rezultat).

To vam bo dalo rezultat, prikazan spodaj.

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

Logika je preprosta - vsaka celica prevzame vrednost na sebi (ki je kumulativna vsota do prejšnjega dne) in doda vrednost v sosednji celici (ki je prodajna vrednost za trenutni dan).

Obstaja samo ena slaba stran - če izbrišete katero koli obstoječo vrstico v tem naboru podatkov, bodo vse spodnje celice vrnile referenčno napako (#REF!)

Če ima vaš nabor podatkov to možnost, uporabite naslednjo metodo s formulo SUM

Uporabite SUM z delno zaklenjenimi referencami celic

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

Spodaj je formula SUM, ki vam bo dala tekoči vsoto.

= 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 ta referenca ne spremeni, ko kopiram isto formulo v spodnjo celico.Torej, ko kopirate formulo v spodnjo celico, se bo formula spremenila v SUM($B$2:B3)
• B2 - To je drugi del sklicevanja, to je relativna referenca, kar pomeni, da se to prilagodi, ko kopiram formulo navzdol ali desno.Torej, ko kopirate formulo v spodnjo celico, vrednost postane B3

Dobra stvar pri tem pristopu je, da če izbrišete katero koli vrstico v naboru podatkov, se ta formula prilagodi in vam še vedno daje pravilno tekočo vsoto.

Izračunajte tekoči vsoto v Excelovem listu

Pri delu s tabelarnimi podatki v Excelu je najbolje, da jih pretvorite v Excelovo tabelo.Olajšuje upravljanje podatkov, prav tako pa omogoča preprosto uporabo orodij, kot sta Power Query in Power Pivot.

Uporaba Excelove tabele ponuja več prednosti, kot so strukturirane reference (za katere je zelo enostavno sklicevanje na podatke v tabeli in njihovo uporabo v formulah) in samodejno prilagajanje sklicevanj, ko dodate ali odstranite podatke iz tabele.

Čeprav lahko še vedno uporabljate zgornjo formulo, ki sem vam jo pokazal na Excelovem listu, naj vam pokažem nekaj boljših načinov za to.

Recimo, da imate Excelovo tabelo, kot je spodnja, in želite izračunati tekočo vsoto v stolpcu C.

Tukaj je formula za to:

=SUM(Podatki o prodaji[[#Headers],[Prodaja]]:[@Razprodaja])

Zgornja formula se morda zdi nekoliko dolga, vendar vam je ni treba napisati sami.Kar vidite v formuli seštevanja, se imenuje strukturirana referenca in je učinkovit način, da se Excel sklicuje na določene podatkovne točke v Excelovi tabeli.

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

Medtem ko se [@Sale] nanaša na vrednost v celici v isti vrstici stolpca Prodaja.

Tukaj razlagam samo za vaše razumevanje, a tudi če ne veste ničesar o strukturiranih referencah, lahko še vedno preprosto ustvarite to formulo.

Tu so koraki za to:

1. V celico C2 vnesite =SUM(
2. Izberite celico B1, ki je glava stolpca z prodajno vrednostjo.Uporabite lahko miško ali puščične tipke.Opazili boste, da Excel samodejno vnese strukturirano sklic na celico
3. Dodaj: (dvopičje)
4. Izberite celico B2.Excel bo samodejno znova vstavil strukturirane reference v celice
5. Zaprite oklepaj in pritisnite Enter

Opazili boste tudi, da vam formule ni treba kopirati v celoten stolpec, Excelov list to naredi samodejno.

Druga prednost tega pristopa je, da če temu naboru podatkov dodate nov zapis, Excelov list samodejno izračuna tekočo vsoto za vse nove zapise.

Čeprav smo v formulo vključili glave stolpcev, ne pozabite, da formula ignorira besedilo glave in upošteva samo podatke v stolpcu

Izračunajte tekoče vsote z Power Query

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

Če že uporabljate Power Query, je učinkoviteje dodajati tekoče vsote pri preoblikovanju podatkov v samem urejevalniku Power Query (namesto, da bi podatke najprej pridobili v Excelu in nato dodali tekoče vsote s katero koli od zgornjih metod).

Čeprav v Power Query ni vgrajene funkcije za dodajanje tekočih seštevkov (želim si, da bi bila), lahko to še vedno storite s preprosto formulo.

Recimo, da imate Excelovo tabelo, ki izgleda takole, in želite tem podatkom dodati tekoče vsote:

Tu so koraki za to:

1. Izberite katero koli celico v Excelovi tabeli
2. klikni podatki
3. Na zavihku Get & Transform kliknite ikono iz tabele/obsega.To bo odprlo tabelo v urejevalniku Power Query
4. [Izbirno] Če vaš datumski stolpec še ni razvrščen, kliknite ikono filtra v stolpcu z datumom, nato kliknite Razvrsti naraščajoče
5. Kliknite zavihek Dodaj stolpec v urejevalniku Power Query
6. V skupini Splošno kliknite spustni meni Stolpec indeksa (ne kliknite ikone Stolpec indeksa, ampak majhno črno poševno puščico poleg nje, da prikažete več možnosti)
7. Kliknite možnost »Od 1«.S tem boste dodali nov indeksni stolpec, ki se bo začel pri 1 in v stolpec vnesel številke, povečane za 1
8. Kliknite ikono Stolpci po meri (tudi na zavihku Dodaj stolpce)
9. V pogovornem oknu Stolpci 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 naslednjo formulo:Seznam.Sum(List.Range(#"Dodano indeks"[Prodaja],0,[Indeks]))
11. Prepričajte se, da je na dnu pogovornega okna potrditveno polje »Ni zaznanih sintaksičnih napak«
12. Kliknite V redu.To bo dodalo nov stolpec tekoče skupne vrednosti
13. spusti indeksni stolpec
14. Kliknite zavihek Datoteka in nato Zapri in naloži

Zgornji koraki bodo v vaš delovni zvezek vstavili nov delovni list s tabelo s tekočimi vsoti.

Če menite, da je to preveč korakov v primerjavi s prejšnjim pristopom s preprostimi formulami, imate prav.

Če že imate nabor podatkov in vse, kar morate storiti, je dodati tekoče vsote, potem je najbolje, da ne uporabljate Power Query.

Uporaba Power Query je smiselna v situacijah, ko morate izvleči podatke iz baze podatkov ali združiti podatke iz več različnih delovnih zvezkov in jim v procesu dodati tekoče vsote.

Ko enkrat to avtomatizirate s Power Query, vam naslednjič, ko se vaš nabor podatkov spremeni, tega ni treba ponoviti, lahko samo osvežite poizvedbo in dala vam bo rezultate na podlagi novega nabora podatkov.

Kako to deluje?

Zdaj naj na hitro razložim, kaj se zgodi s tem pristopom.

Prva stvar, ki jo naredimo v urejevalniku Power Query, je, da vstavimo indeksni stolpec, ki se začne pri 1 in se povečuje, ko gre navzdol po celici.

To naredimo, ker moramo ta stolpec uporabiti pri izračunu tekoče vsote v drugem stolpcu, ki je vstavljen v naslednjem koraku.

Nato vstavimo stolpec po meri in uporabimo naslednjo formulo

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

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

Obseg je določen s funkcijo List.Range.

Funkcija List.Range vzame kot izhod določen obseg v stolpcu prodaje, obseg pa se razlikuje glede na vrednost indeksa.Na primer, za prvi zapis je obseg prva prodajna vrednost.Ta razpon se širi, ko se spuščate po celici.

Torej, za prvo celico.List.Sum vam bo dal samo vsoto prve prodajne vrednosti, za drugo celico pa vsoto prvih dveh prodajnih vrednosti itd.

Čeprav ta pristop dobro deluje, lahko postane zelo počasen za velike nabore podatkov (na tisoče vrstic).

Izračunajte tekoče vsote na podlagi meril

Do sedaj smo videli primere izračuna tekočega vsote vseh vrednosti v stolpcu.

Toda v nekaterih primerih boste morda želeli izračunati tekočo vsoto za določen zapis.

Spodaj imam na primer nabor podatkov, kjer želim prešteti tekočo vsoto tiskalnikov in optičnih bralnikov v dveh različnih stolpcih.

To je mogoče storiti s formulo SUMIF, ki izračuna tekočo vsoto, hkrati pa zagotovi, da so izpolnjeni določeni pogoji.

Tukaj je formula za to za stolpec tiskalnika:

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

Podobno za izračun tekoče vsote optičnega bralnika uporabite naslednjo formulo:

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

V zgornji formuli sem uporabil SUMIF, ki mi bo dal vsoto v obsegu, ko so izpolnjeni določeni pogoji.

Formula ima tri parametre:

1. območje: To je obseg pogojev, ki bodo preverjeni glede na določen pogoj
2. Merila: to je merilo, ki bo preverjeno le, če je ta kriterij izpolnjen, nato se doda vrednost v tretjem parametru, obseg vsote
3. [obseg_vsote]: to je obseg vsote vrednosti, ki jih je treba dodati, ko je pogoj izpolnjen

Poleg tega je vobmočjeInvsota_razponparameter, sem zaklenil drugi del reference, tako da se ob pomiku navzdol po celici obseg širi.To nam omogoča, da upoštevamo in dodajamo samo vrednosti do tega obsega (torej tekoči vsota).

V tej formuli uporabljam stolpce glave (tiskalniki in skenerji) kot merila.Standard lahko tudi trdo kodirate, če naslovi vaših stolpcev niso popolnoma enaki standardnemu besedilu.

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

Tekoči seštevek v vrtilni tabeli

Če želite rezultatom vrtilne tabele dodati tekočo vsoto, lahko to preprosto storite z vgrajeno funkcionalnostjo v vrtilnih tabelah.

Recimo, da imate vrtilno tabelo, kot je spodnja, z datumi v enem stolpcu in prodajnimi vrednostmi v drugem.

Tu so koraki za dodajanje dodatnega stolpca, ki bo prikazal skupno prodajo po datumu:

1. Povlecite polje Prodaja in ga spustite v območje Vrednost.
2. To bo dodalo še en stolpec s prodajno vrednostjo
3. Kliknite možnost Skupna prodaja 2 v območju Vrednost
4. Kliknite možnost Nastavitve polja vrednosti
5. V pogovornem oknu Nastavitve polja vrednosti spremenite Ime po meri v Running Total
6. Kliknite zavihek »Prikaži vrednost kot«.
7. V spustnem meniju Prikaži vrednost kot izberite možnost »Vseka v teku«.
8. V možnostih Osnovna polja se prepričajte, da je izbran datum
9. Kliknite V redu

Zgornji koraki bodo spremenili drugi stolpec prodaje v stolpec »tekoča skupna vrednost«.

Torej, to je nekaj metod, ki jih lahko uporabite za izračun tekočih vsot v Excelu.Če imate tabelarne podatke, lahko uporabite preproste formule, in če imate Excelove tabele, lahko uporabite formule, ki uporabljajo strukturirane reference.

Zajel sem tudi, kako izračunati tekoče vsote z uporabo Power Query in vrtilnih tabel.

Upam, da se vam je ta vadnica zdela koristna.