Bėgimo suma (dar vadinama kaupiamoji suma) yra gana dažnai naudojamas daugelyje situacijų. Tai metrika, nurodanti, kokia yra iki šiol buvusių verčių suma.
Pvz., Jei turite mėnesio pardavimo duomenis, einamoji suma parodys, kiek pardavimų buvo atlikta iki konkrečios dienos nuo pirmos mėnesio dienos.
Taip pat yra keletas kitų situacijų, kai dažnai naudojama einamoji suma, pavyzdžiui, apskaičiuojant grynųjų pinigų likutį banko ataskaitose/knygoje, skaičiuojant kalorijas valgymo plane ir pan.
„Microsoft Excel“ yra daug skirtingų būdų, kaip apskaičiuoti veikiančias sumas.
Pasirinktas metodas taip pat priklausys nuo jūsų duomenų struktūros.
Pavyzdžiui, jei turite paprastus lentelės duomenis, galite naudoti paprastą SUM formulę, bet jei turite „Excel“ lentelę, geriausia naudoti struktūrines nuorodas. Norėdami tai padaryti, taip pat galite naudoti „Power Query“.
Šioje pamokoje aptarsiu visus šiuos skirtingus metodus apskaičiuoti veikiančias sumas „Excel“.
Taigi pradėkime!
Bėgimo sumos apskaičiavimas naudojant lentelės duomenis
Jei turite lentelės duomenų (t. Y. „Excel“ lentelę, kuri nėra paversta „Excel“ lentele), galite naudoti keletą paprastų formulių, kad apskaičiuotumėte veikiančias sumas.
Papildymo operatoriaus naudojimas
Tarkime, kad turite pardavimo duomenis pagal datą ir norite apskaičiuoti einamąją sumą C stulpelyje.
Žemiau pateikiami žingsniai, kaip tai padaryti.
1 žingsnis - C2 langelyje, kuris yra pirmasis langelis, kuriame norite rodyti bendrą sumą, įveskite
= B2
Tai tiesiog gaus tas pačias pardavimo vertes langelyje B2.
2 žingsnis - C3 langelyje įveskite šią formulę:
= C2+B3
3 žingsnis - Taikykite formulę visam stulpeliui. Galite naudoti užpildymo rankenėlę, kad pasirinktumėte ir vilktumėte, arba tiesiog nukopijuokite ir įklijuokite langelį C3 į visas likusias ląsteles (kurios automatiškai pakoreguotų nuorodą ir duotų teisingą rezultatą).
Tai suteiks jums rezultatą, kaip parodyta žemiau.
Tai tikrai paprastas metodas ir daugeliu atvejų gerai veikia.
Logika paprasta - kiekviena ląstelė paima aukščiau esančią vertę (kuri yra kaupiama suma iki ankstesnės datos) ir prideda vertę šalia jos esančiame langelyje (tai yra tos dienos pardavimo vertė).
Yra tik vienas trūkumas - jei ištrinsite bet kurią esamą šio duomenų rinkinio eilutę, visos žemiau esančios ląstelės grąžins nuorodos klaidą (#REF!)
Jei tai įmanoma su jūsų duomenų rinkiniu, naudokite kitą metodą, kuriame naudojama SUM formulė
Naudojant SUM su iš dalies užrakinta ląstelių nuoroda
Tarkime, kad turite pardavimo duomenis pagal datą ir norite apskaičiuoti einamąją sumą C stulpelyje.
Žemiau yra SUM formulė, kuri suteiks jums einamąją sumą.
= SUMA ($ B $ 2: B2)
Leiskite man paaiškinti, kaip ši formulė veikia.
Aukščiau pateiktoje SUM formulėje naudoju nuorodą, kad pridėčiau kaip $ B $ 2: B2
- $ B $ 2 - tai absoliuti nuoroda, o tai reiškia, kad kai nukopijuoju tą pačią formulę žemiau esančiuose langeliuose, ši nuoroda nesikeis. Taigi, kopijuojant formulę žemiau esančiame langelyje, formulė pasikeis į SUM ($ B $ 2: B3)
- B2 - tai yra antroji nuorodos dalis, kuri yra santykinė nuoroda, o tai reiškia, kad tai koreguotų, kai kopijuosiu formulę žemyn arba į dešinę. Taigi, kopijuojant formulę žemiau esančiame langelyje, ši vertė taps B3
Taip pat skaitykite: Absoliučios, santykinės ir mišrios ląstelių nuorodos „Excel“
Puikus šio metodo dalykas yra tas, kad jei ištrinsite kurią nors duomenų rinkinio eilutę, ši formulė pakoreguotų ir vis tiek suteiktų jums tinkamas veikimo sumas.
Bėgimo sumos apskaičiavimas „Excel“ lentelėje
Dirbant su lentelės duomenimis „Excel“, patartina juos paversti „Excel“ lentele. Tai labai palengvina duomenų valdymą, taip pat leidžia lengvai naudoti tokius įrankius kaip „Power Query“ ir „Power Pivot“.
Dirbant su „Excel“ lentelėmis, yra tokių privalumų, kaip struktūrinės nuorodos (todėl tikrai lengva kreiptis į lentelės duomenis ir naudoti jas formulėse) ir automatinis nuorodų koregavimas, jei pridėsite arba ištrinsite duomenis iš lentelės.
Nors vis tiek galite naudoti aukščiau pateiktą formulę, kurią aš jums parodžiau „Excel“ lentelėje, leiskite parodyti keletą geresnių būdų tai padaryti.
Tarkime, kad turite „Excel“ lentelę, kaip parodyta žemiau, ir norite apskaičiuoti einamąją sumą C stulpelyje.
Žemiau yra formulė, kuri tai padarys:
= SUMA (pardavimo duomenys [[#antraštės], [išpardavimas]]: [@išpardavimas])
Aukščiau pateikta formulė gali atrodyti šiek tiek ilga, tačiau jums nereikia jos rašyti. tai, ką matote sumos formulėje, vadinamos struktūrinėmis nuorodomis, o tai yra efektyvus „Excel“ būdas nurodyti konkrečius duomenų taškus „Excel“ lentelėje.
Pvz., „SalesData“ [[#antraštės], [išpardavimas]] nurodo pardavimo antraštę „SalesData“ lentelėje („SalesData“ yra „Excel“ lentelės, kurią daviau kurdamas lentelę, pavadinimas)
O [@Sale] nurodo reikšmę tos pačios stulpelio „Pardavimas“ eilutės langelyje.
Čia ką tik paaiškinau, kad suprastumėte, bet net jei nieko nežinote apie struktūrines nuorodas, vis tiek galite lengvai sukurti šią formulę.
Žemiau pateikiami žingsniai, kaip tai padaryti:
- C2 langelyje įveskite = SUM (
- Pasirinkite langelį B1, kuris yra stulpelio, kuriame yra pardavimo vertė, antraštė. Galite naudoti pelę arba rodyklių klavišus. Pastebėsite, kad „Excel“ automatiškai įveda to langelio struktūrinę nuorodą
- Pridėkite: (dvitaškio simbolį)
- Pasirinkite langelį B2. „Excel“ vėl automatiškai įterptų langelio struktūrinę nuorodą
- Uždarykite laikiklį ir paspauskite „Enter“
Taip pat pastebėsite, kad jums nereikia kopijuoti formulės visame stulpelyje, „Excel“ lentelė tai automatiškai atlieka už jus.
Kitas puikus šio metodo dalykas yra tas, kad jei į šį duomenų rinkinį įtraukiate naują įrašą, „Excel“ lentelė automatiškai apskaičiuoja visų naujų įrašų veikimo sumą.
Nors į savo formulę įtraukėme stulpelio antraštę, atminkite, kad formulė nepaisys antraštės teksto ir atsižvelgs tik į stulpelio duomenis
Bėgimo sumos apskaičiavimas naudojant „Power Query“
„Power Query“ yra nuostabus įrankis, kai reikia prisijungti prie duomenų bazių, išgauti duomenis iš kelių šaltinių ir transformuoti juos prieš įdedant juos į „Excel“.
Jei jau dirbate su „Power Query“, efektyviau būtų pridėti veikiančias sumas, kai transformuojate duomenis pačiame „Power Query“ redaktoriuje (vietoj to, kad pirmą kartą gautumėte duomenis „Excel“ ir pridėtumėte veikiančias sumas naudodami bet kurį iš aukščiau išvardytų būdų) aukščiau aprašytus metodus).
Nors „Power Query“ nėra integruotos funkcijos, leidžiančios pridėti veikiančias sumas (taip norėčiau), vis tiek galite tai padaryti naudodami paprastą formulę.
Tarkime, kad turite „Excel“ lentelę, kaip parodyta žemiau, ir norite prie šių duomenų pridėti veikiančių sumų:
Žemiau pateikiami žingsniai, kaip tai padaryti:
- „Excel“ lentelėje pasirinkite bet kurį langelį
- Spustelėkite Duomenys
- Skirtuke Gauti ir transformuoti spustelėkite piktogramą iš lentelės/diapazono. Tai atvers „Power Query“ redaktoriaus lentelę
- [Pasirenkama] Jei stulpelis Data dar nėra surūšiuotas, spustelėkite filtro piktogramą stulpelyje Data, tada spustelėkite Rūšiuoti didėjančia tvarka
- „Power Query“ redaktoriuje spustelėkite skirtuką Pridėti stulpelį
- Grupėje Bendra spustelėkite išskleidžiamąjį meniu Rodyklės stulpelis (nespauskite rodyklės stulpelio piktogramos, bet šalia esančios mažos juodos pakreiptos rodyklės, kad būtų rodomos daugiau parinkčių)
- Spustelėkite parinktį „Nuo 1“. Tai padarius, bus pridėtas naujas rodyklės stulpelis, kuris prasidėtų nuo vieno, o visame stulpelyje būtų įvesti skaičiai, didėjantys po 1
- Spustelėkite piktogramą „Tinkintas stulpelis“ (kuri taip pat yra skirtuke Pridėti stulpelį)
- Atsidariusiame pasirinktinio stulpelio dialogo lange įveskite naujo stulpelio pavadinimą. šiame pavyzdyje naudosiu pavadinimą „Running Total“
- Lauke Tinkinta stulpelio formulė įveskite šią formulę: Sąrašas.Suma (Sąrašas.Range (#"Pridėta rodyklė" [išpardavimas], 0, [indeksas]))
- Įsitikinkite, kad dialogo lango apačioje yra žymimasis laukelis „Nerasta jokių sintaksės klaidų“
- Spustelėkite Gerai. Tai pridėtų naują veikiančio viso stulpelį
- Pašalinkite rodyklės stulpelį
- Spustelėkite skirtuką Failas, tada spustelėkite „Uždaryti ir įkelti“
Pirmiau minėti veiksmai į jūsų darbaknygę įterptų naują lapą su lentele, kurioje yra einamosios sumos.
Dabar, jei manote, kad tai tik per daug žingsnių, palyginti su ankstesniais paprastų formulių naudojimo metodais, jūs teisus.
Jei jau turite duomenų rinkinį ir viskas, ką jums reikia padaryti, yra pridėti veikiančių sumų, geriau nenaudoti „Power Query“.
„Power Query“ naudojimas yra prasmingas ten, kur turite išgauti duomenis iš duomenų bazės arba sujungti duomenis iš kelių skirtingų darbaknygių, o taip pat pridėti prie jos veikiančias sumas.
Be to, kai atliksite šį automatizavimą naudodami „Power Query“, kitą kartą, kai jūsų duomenų rinkinys bus pakeistas, jums nebereikės to daryti dar kartą, galite tiesiog atnaujinti užklausą ir tai duos jums rezultatą pagal naują duomenų rinkinį.
Kaip tai veikia?
Dabar leiskite man greitai paaiškinti, kas atsitinka naudojant šį metodą.
Pirmas dalykas, kurį mes darome „Power Query“ redaktoriuje, yra įterpti rodyklės stulpelį, pradedant nuo vieno ir didinant po vieną, kai jis eina žemyn langeliuose.
Mes tai darome, nes turime naudoti šį stulpelį, kol apskaičiuojame einamąją sumą kitame stulpelyje, kurį įterpiame kitame žingsnyje.
Tada įterpiame pasirinktinį stulpelį ir naudojame toliau pateiktą formulę
List.Sum (List.Range (#"Pridėta rodyklė" [išpardavimas], 0, [indeksas])
Tai yra „List.Sum“ formulė, kuri suteiktų jums joje nurodyto diapazono sumą.
Ir šis diapazonas nurodomas naudojant funkciją List.Range.
Funkcija „List.Range“ nurodo pardavimo stulpelyje nurodytą diapazoną kaip išvestį ir šis diapazonas keičiasi atsižvelgiant į indekso vertę. Pavyzdžiui, pirmajam įrašui diapazonas būtų tiesiog pirmoji pardavimo vertė. Ir kai jūs einate žemyn, šis diapazonas išsiplėtė.
Taigi, pirmajai ląstelei. Sąrašas. Suma suteiks jums tik pirmosios pardavimo vertės sumą, o antrame langelyje - pirmųjų dviejų pardavimo verčių sumą ir pan.
Nors šis metodas veikia gerai, jis tampa labai lėtas naudojant didelius duomenų rinkinius - tūkstančius eilučių. Jei susiduriate su dideliu duomenų rinkiniu ir norite prie jo pridėti veikiančias sumas, peržiūrėkite šią mokymo programą, kurioje rodomi kiti greitesni metodai.Bėgimo sumos apskaičiavimas pagal kriterijus
Iki šiol matėme pavyzdžių, kai apskaičiavome visų stulpelio verčių einamąją sumą.
Tačiau gali būti atvejų, kai norite apskaičiuoti konkrečių įrašų einamąją sumą.
Pavyzdžiui, žemiau turiu duomenų rinkinį ir noriu atskirai apskaičiuoti spausdintuvų ir skaitytuvų veikimo sumą dviejuose skirtinguose stulpeliuose.
Tai galima padaryti naudojant SUMIF formulę, kuri apskaičiuoja einamąją sumą ir įsitikina, kad įvykdyta nurodyta sąlyga.
Žemiau yra formulė, kuri tai padarys spausdintuvo stulpeliuose:
= SUMIF ($ C $ 2: C2, $ D $ 1, $ B $ 2: B2)
Panašiai, norėdami apskaičiuoti bendrą skaitytuvų veikimą, naudokite šią formulę:
= SUMIF ($ C $ 2: C2, $ E $ 1, $ B $ 2: B2)
Pirmiau pateiktose formulėse aš naudoju SUMIF, kuris man suteiktų sumą diapazone, kai bus įvykdyti nurodyti kriterijai.
Formulė apima tris argumentus:
- diapazonas: tai kriterijų diapazonas, kuris būtų tikrinamas pagal nurodytus kriterijus
- kriterijai: tai yra kriterijai, kurie būtų patikrinti tik tuo atveju, jei šis kriterijus būtų įvykdytas, tada būtų pridėtos trečiojo argumento, kuris yra sumos diapazonas, vertės
- [sumos_s]: tai yra sumų diapazonas, nuo kurio bus pridėtos vertės, jei kriterijai bus įvykdyti
Taip pat, diapazonas ir sumos_diapazonas argumentą, užrakinau antrąją nuorodos dalį, kad, einant žemyn langeliais, diapazonas nuolat plėstųsi. Tai leidžia mums tik apsvarstyti ir pridėti vertes iki šio diapazono (taigi paleisti sumas).
Šioje formulėje kaip kriterijus naudoju antraštės stulpelį (Spausdintuvas ir skaitytuvas). taip pat galite koduoti kriterijus, jei stulpelių antraštės nėra visiškai tokios pačios kaip kriterijų tekstas.
Jei turite keletą sąlygų, kurias turite patikrinti, galite naudoti SUMIFS formulę.
Iš viso rodoma „Pivot“ lentelėse
Jei norite pridėti veikiančių sumų į „Pivot Table“ rezultatą, tai galite lengvai padaryti naudodami integruotą „Pivot“ lentelių funkciją.
Tarkime, kad turite „Pivot“ lentelę, kaip parodyta žemiau, kur viename stulpelyje yra data, o kitame stulpelyje - pardavimo vertė.
Žemiau pateikiami veiksmai, kaip pridėti papildomą stulpelį, kuriame bus rodoma bendra pardavimo suma pagal datą:
- Vilkite lauką Pardavimas ir padėkite jį į sritį Vertė.
- Tai pridės dar vieną stulpelį su pardavimo vertėmis
- Srityje Vertė spustelėkite parinktį Pardavimo suma2
- Spustelėkite parinktį „Vertės lauko nustatymai“
- Dialogo lange Vertės lauko nustatymai pakeiskite pasirinktinį pavadinimą į „Vykdomas sumas“
- Spustelėkite skirtuką „Rodyti vertę kaip“
- Išskleidžiamajame meniu Rodyti vertę pasirinkite parinktį „Veikti iš viso“
- Pagrindo lauko parinktyse įsitikinkite, kad pasirinkta data
- Spustelėkite Gerai
Pirmiau minėti veiksmai pakeistų antrąjį pardavimo stulpelį į stulpelį „Veikia visa suma“.
Taigi tai yra keletas būdų, kaip galite apskaičiuoti veikiančią sumą „Excel“. Jei turite duomenų lentelės formatu, galite naudoti paprastas formules, o jei turite „Excel“ lentelę, tada galite naudoti formules, kuriose naudojamos struktūrinės nuorodos.
Taip pat aptariau, kaip apskaičiuoti bendrą veikimą naudojant „Power Query“ ir „Pivot“ lentelėse.
Tikiuosi, kad ši pamoka jums buvo naudinga.
