Jooksev kokku (nimetatakse ka kumulatiivne summa) kasutatakse üsna sageli paljudes olukordades. See on näitaja, mis ütleb teile, mis on seniste väärtuste summa.
Näiteks kui teil on igakuised müügiandmed, siis jooksev kogusumma näitab teile, kui palju on müüki tehtud konkreetse päevani alates kuu esimesest päevast.
On ka mõningaid muid olukordi, kus jooksvat kogusummat kasutatakse sageli, näiteks sularahajäägi arvutamine pangakonto väljavõtetes/pearaamatus, kalorite lugemine toiduplaanis jne.
Microsoft Excelis on jooksvate kogusummade arvutamiseks mitmeid erinevaid viise.
Teie valitud meetod sõltub ka sellest, kuidas teie andmed on üles ehitatud.
Näiteks kui teil on lihtsad tabeliandmed, saate kasutada lihtsat SUM -valemit, kuid kui teil on Exceli tabel, on parem kasutada struktureeritud viiteid. Selleks saate kasutada ka Power Queryt.
Selles õpetuses käsitlen kõiki neid erinevaid meetodeid arvutage Excelis jooksvad kogusummad.
Nii et alustame!
Jooksva summa arvutamine tabeli andmetega
Kui teil on tabeliandmed (st Exceli tabel, mida ei muudeta Exceli tabeliks), saate jooksvate kogusummade arvutamiseks kasutada mõnda lihtsat valemit.
Lisamisoperaatori kasutamine
Oletame, et teil on kuupäevade kaupa müügiandmed ja soovite arvutada jooksva kogusumma veerus C.
Allpool on toodud sammud selle tegemiseks.
Samm 1 - Sisestage lahtrisse C2, mis on esimene lahter, kuhu soovite jooksva summa
= B2
See annab lahtris B2 lihtsalt samad müügiväärtused.
2. samm - Sisestage lahtrisse C3 järgmine valem:
= C2+B3
3. samm - Rakendage valem kogu veerule. Selle valimiseks ja lohistamiseks võite kasutada täitekäepidet või lihtsalt kopeerida ja kleepida lahtri C3 kõikidesse ülejäänud lahtritesse (mis kohandaks automaatselt viidet ja annaks õige tulemuse).
See annab teile tulemuse, nagu allpool näidatud.
See on tõesti lihtne meetod ja töötab enamikul juhtudel hästi.
Loogika on lihtne - iga lahter võtab selle kohal oleva väärtuse (mis on kumulatiivne summa kuni eelneva kuupäevani) ja lisab selle kõrval oleva lahtri väärtuse (mis on selle päeva müügiväärtus).
Sellel on ainult üks puudus - kui kustutate mõne selle andmekogumi olemasoleva rea, siis kõik allpool olevad lahtrid tagastavad viitevea (#REF!)
Kui see on teie andmekogumiga võimalik, kasutage järgmist meetodit, mis kasutab SUM valemit
SUM -i kasutamine osaliselt lukustatud lahtri viitega
Oletame, et teil on kuupäevade kaupa müügiandmed ja soovite veerus C arvutada jooksva kogusumma.
Allpool on SUM valem, mis annab teile jooksva summa.
= SUMMA ($ B $ 2: B2)
Las ma selgitan, kuidas see valem töötab.
Ülaltoodud SUM -valemis olen kasutanud viidet $ B $ 2: B2 lisamiseks
- $ B $ 2 - see on absoluutne viide, mis tähendab, et kui kopeerin sama valemi allolevatesse lahtritesse, ei muutu see viide. Nii et valemi kopeerimisel allolevas lahtris muutuks valem väärtuseks SUM ($ B $ 2: B3)
- B2 - see on viite teine osa, mis on suhteline viide, mis tähendab, et see kohandub, kui valemit alla või paremale kopeerin. Seega, kui kopeerida valemit allolevasse lahtrisse, saab sellest väärtusest B3
Loe ka: Absoluutsed, suhtelised ja segarakulised viited Excelis
Selle meetodi suurepärane omadus on see, et kui kustutate mõne andmekogumi rea, siis see valem kohandab ja annab teile ikkagi õiged jooksvad kogusummad.
Jooksva summa arvutamine Exceli tabelis
Excelis tabeliandmetega töötamisel on hea mõte need Exceli tabeliks teisendada. See muudab andmete haldamise palju lihtsamaks ja võimaldab hõlpsasti kasutada ka selliseid tööriistu nagu Power Query ja Power Pivot.
Exceli tabelite töötamisel on eeliseid, näiteks struktureeritud viiteid (mis muudab tabeli andmetele viitamise ja valemites kasutamise tõesti lihtsaks) ning viidete automaatse kohandamise juhuks, kui lisate või kustutate tabelist andmeid.
Kuigi saate endiselt kasutada ülaltoodud valemit, mille olen teile Exceli tabelis näidanud, lubage mul näidata teile mõningaid paremaid meetodeid.
Oletame, et teil on Exceli tabel, nagu allpool näidatud, ja soovite veerus C arvutada jooksva summa.
Allpool on valem, mis seda teeb:
= SUM (müügiandmed [[#päised], [müük]]: [@müük])
Ülaltoodud valem võib tunduda natuke pikk, kuid te ei pea seda ise kirjutama. Summa valemis nähtuid nimetatakse struktureeritud viideteks, mis on Exceli tõhus viis Exceli tabeli konkreetsetele andmepunktidele viitamiseks.
Näiteks SalesData [[#Headers], [Sale]] viitab müügipäise tabelile SalesData (SalesData on Exceli tabeli nimi, mille andsin tabeli loomisel)
Ja [@Sale] viitab väärtusele veeru Müük sama rea lahtris.
Ma lihtsalt selgitasin seda siin teie mõistmiseks, kuid isegi kui te ei tea struktureeritud viidetest midagi, saate selle valemi hõlpsalt luua.
Allpool on toodud sammud selle tegemiseks.
- Lahtrisse C2 sisestage = SUM (
- Valige lahter B1, mis on müügiväärtusega veeru päis. Võite kasutada hiirt või nooleklahve. Märkate, et Excel sisestab automaatselt selle lahtri struktureeritud viite
- Lisage: (kooloni sümbol)
- Valige lahter B2. Excel sisestaks uuesti automaatselt lahtri struktureeritud viite
- Sulgege sulg ja vajutage sisestusklahvi
Samuti märkate, et te ei pea valemit kogu veergu kopeerima, Exceli tabel teeb seda teie eest automaatselt.
Teine selle meetodi suurepärane asi on see, et kui lisate sellesse andmekogumisse uue kirje, arvutab Exceli tabel automaatselt kõigi uute kirjete jooksva summa.
Kuigi oleme oma valemisse lisanud veeru päise, pidage meeles, et valem eiraks päise teksti ja arvestaks ainult veeru andmetega
Jooksva summa arvutamine Power Query abil
Power Query on hämmastav tööriist andmebaasidega ühenduse loomiseks, andmete hankimiseks mitmest allikast ja nende teisendamiseks enne Excelisse lisamist.
Kui te juba töötate Power Queryga, oleks tõhusam lisada jooksvaid kogusummasid andmete muutmise ajal Power Query redaktorisse (selle asemel, et esimest korda Excelist andmeid hankida ja seejärel jooksvaid kogusummasid kasutada, kasutades mõnda ülaltoodud meetodit) eespool käsitletud meetodid).
Kuigi Power Query'is pole sisseehitatud funktsiooni jooksvate kogusummade lisamiseks (mida ma sooviksin), saate seda siiski teha lihtsa valemi abil.
Oletame, et teil on Exceli tabel, nagu allpool näidatud, ja soovite nendele andmetele lisada jooksvad kogusummad:
Allpool on toodud sammud selle tegemiseks.
- Valige Exceli tabeli mis tahes lahter
- Klõpsake nuppu Andmed
- Vahekaardil Hangi ja teisenda klõpsake tabeli/vahemiku ikooni. See avab tabeli Power Query redaktoris
- [Valikuline] Kui teie veerg Kuupäev pole veel sorteeritud, klõpsake veerus Kuupäev filtriikooni ja seejärel käsku Sorteeri kasvavalt
- Klõpsake Power Query redaktoris vahekaarti Lisa veerg
- Klõpsake rühmas Üldine rippmenüüd Indeksiveerg (lisavõimaluste kuvamiseks ärge klõpsake indeksi veeru ikooni, vaid selle kõrval olevat väikest musta kallutatud noolt)
- Klõpsake valikul „Alates 1”. Seda tehes lisatakse uus indeksveerg, mis algaks ühest ja sisestaks kogu veergu 1 -ga kasvavad numbrid
- Klõpsake ikooni „Kohandatud veerg” (mis asub ka vahekaardil Lisa veerg)
- Avanevas kohandatud veeru dialoogiboksis sisestage uue veeru nimi. selles näites kasutan nime „Running Total”
- Sisestage väljale Kohandatud veeru valem järgmine valem: List.Sum (List.Range (#”Lisatud indeks” [Sale], 0, [Index]))
- Veenduge, et dialoogiboksi allosas oleks märkeruut „Süntaksivigu pole tuvastatud”
- Klõpsake nuppu OK. See lisab uue jooksva kogu veeru
- Eemaldage veerg Indeks
- Klõpsake vahekaarti Fail ja seejärel nuppu „Sule ja laadi”
Ülaltoodud toimingud sisestaksid teie töövihikusse uue lehe koos tabeliga, millel on jooksvad kogusummad.
Nüüd, kui arvate, et need on lihtsate valemite eelmiste meetoditega võrreldes liiga palju samme, on teil õigus.
Kui teil on juba andmekogum ja peate lihtsalt lisama jooksvaid kogusummasid, on parem Power Query mitte kasutada.
Power Query kasutamine on mõistlik, kui peate andmebaasist andmeid välja võtma või kombineerima andmeid mitmest erinevast töövihikust ja lisama selle käigus ka jooksvaid kogusummasid.
Lisaks, kui teete seda automatiseerimist Power Query abil, ei pea te järgmisel korral, kui teie andmekogum muutub, uuesti tegema, saate päringut lihtsalt värskendada ja see annab teile uue andmekogumi põhjal tulemuse.
Kuidas see töötab?
Nüüd selgitan kiiresti, mis selle meetodiga juhtub.
Esimene asi, mida me Power Query redaktoris teeme, on sisestada indeksveerg, alustades ühest ja suurendades lahtritest allapoole.
Me teeme seda, kuna peame seda veergu kasutama, kui arvutame jooksva kogusumma teises veerus, mille lisame järgmisse etappi.
Seejärel sisestame kohandatud veeru ja kasutame allolevat valemit
List.Sum (List.Range (#"Lisatud indeks" [Sale], 0, [Index]))
See on List.Sum valem, mis annaks teile selle vahemiku summa.
Ja see vahemik määratakse funktsiooni List.Range abil.
Funktsioon List.Range annab väljundina müügiveerus määratud vahemiku ja see vahemik muutub vastavalt indeksi väärtusele. Näiteks esimese kirje puhul oleks vahemik lihtsalt esimese müügi väärtus. Ja kui lähete lahtritesse, laieneb see vahemik.
Niisiis, esimese lahtri jaoks. Loend. Summa annab teile ainult esimese müügiväärtuse summa ja teise lahtri puhul kahe esimese müügiväärtuse summa ja nii edasi.
Kuigi see meetod töötab hästi, muutub see suurte andmekogumitega - tuhandete ridadega - tõesti aeglaseks. Kui tegelete suure andmekogumiga ja soovite sellele lisada jooksvaid kogusummasid, vaadake seda õpetust, mis näitab muid kiiremaid meetodeid.Jooksva summa arvutamine kriteeriumide alusel
Siiani oleme näinud näiteid, kus oleme arvutanud jooksva kogusumma kõigi veeru väärtuste jaoks.
Kuid võib juhtuda, et soovite arvutada konkreetsete kirjete jooksva summa.
Näiteks allpool on mul andmekogum ja ma tahan arvutada printerite ja skannerite jooksva summa eraldi kahes erinevas veerus.
Seda saab teha SUMIF -valemi abil, mis arvutab jooksva kogusumma, veendudes samal ajal, et määratud tingimus on täidetud.
Allpool on valem, mis seda printeri veergude jaoks teeb:
= SUMIF ($ C $ 2: C2, $ D $ 1, $ B $ 2: B2)
Skannerite jooksva kogusumma arvutamiseks kasutage samamoodi järgmist valemit:
= SUMIF ($ C $ 2: C2, $ E $ 1, $ B $ 2: B2)
Ülaltoodud valemites olen kasutanud SUMIF -i, mis annaks mulle summa vahemikus, kui määratud kriteeriumid on täidetud.
Valem sisaldab kolme argumenti:
- vahemik: see on kriteeriumide vahemik, mida kontrollitakse kindlaksmääratud kriteeriumide alusel
- kriteeriumid: need on kriteeriumid, mis registreeritakse ainult siis, kui see kriteerium on täidetud, siis lisatakse kolmanda argumendi väärtused, mis on summa vahemik
- [summa_vahemik]: see on summa vahemik, millest väärtused lisatakse, kui kriteeriumid on täidetud
Samuti, vahemik ja summa_vahemik argumendi, olen lukustanud viite teise osa, nii et kui me lahtritest alla läheme, laieneb vahemik pidevalt. See võimaldab meil arvestada ja lisada väärtusi ainult selle vahemikuni (seega jooksevad kogusummad).
Selles valemis olen kasutanud kriteeriumina päiseveergu (Printer ja skanner). saate kriteeriumid ka kõvakodeerida, kui teie veerupäised ei ole täpselt samad, mis kriteeriumitekstis.
Kui teil on mitu tingimust, mida peate kontrollima, saate kasutada valemit SUMIFS.
Kokku jookseb liigendtabelites
Kui soovite Pivot Tabeli tulemusele lisada jooksvaid kogusummasid, saate seda hõlpsalt teha, kasutades Pivot -tabelite sisseehitatud funktsioone.
Oletame, et teil on Pivot -tabel, nagu allpool näidatud, kus mul on ühes veerus kuupäev ja teises veerus müügiväärtus.
Allpool on toodud sammud täiendava veeru lisamiseks, mis näitab jooksvat müügi kogusummat kuupäeva järgi:
- Lohistage välja Müük ja pange see väärtuse alale.
- See lisab veel ühe veeru müügiväärtustega
- Klõpsake väärtuse alas suvandil Müügi summa2
- Klõpsake suvandit „Väärtusvälja seaded”
- Muutke dialoogiboksis Väärtusväljade seaded kohandatud nimi olekuks „Running Totals”
- Klõpsake vahekaarti „Kuva väärtus kui”
- Valige rippmenüüst Näita väärtust valik „Running Total in”
- Veenduge, et baasvälja suvandites on valitud kuupäev
- Klõpsake nuppu OK
Ülaltoodud toimingud muudaksid teise müügi veeru veeruks Jooksev kogusumma.
Nii et need on mõned viisid, mida saate kasutada Exceli jooksva summa arvutamiseks. Kui teil on andmeid tabelivormingus, saate kasutada lihtsaid valemeid ja kui teil on Exceli tabel, siis võite kasutada struktureeritud viiteid kasutavaid valemeid.
Samuti olen käsitlenud jooksva kogusumma arvutamist Power Query ja Pivot -tabelite abil.
Loodan, et leidsite selle õpetuse kasulikuks.
