Käynnissä yhteensä (kutsutaan myös kumulatiivinen summa) käytetään melko yleisesti monissa tilanteissa. Se on mittari, joka kertoo, mikä on arvojen summa tähän mennessä.
Jos sinulla on esimerkiksi kuukausittaiset myyntitiedot, juokseva kokonaissumma kertoo, kuinka paljon myyntiä on tehty tiettyyn päivään kuukauden ensimmäisestä päivästä alkaen.
On myös joitakin muita tilanteita, joissa juoksevaa kokonaismäärää käytetään usein, kuten käteissaldon laskeminen tiliotteessasi/kirjanpidossasi, kalorien laskeminen ateriasuunnitelmassasi jne.
Microsoft Excelissä on useita eri tapoja laskea käynnissä olevat kokonaissummat.
Valitsemasi menetelmä riippuu myös siitä, miten tietosi on rakennettu.
Jos sinulla on esimerkiksi yksinkertaisia taulukkotietoja, voit käyttää yksinkertaista SUM -kaavaa, mutta jos sinulla on Excel -taulukko, on parasta käyttää strukturoituja viittauksia. Voit käyttää tätä myös Power Queryn avulla.
Tässä opetusohjelmassa aion käsitellä kaikkia näitä erilaisia menetelmiä laskea juoksevat summat Excelissä.
Aloitetaan siis!
Juoksevan kokonaisuuden laskeminen taulukkotiedoilla
Jos sinulla on taulukkotietoja (eli Excelin taulukko, jota ei ole muunnettu Excel -taulukkoksi), voit käyttää juoksevia kokonaismääriä laskemalla joitain yksinkertaisia kaavoja.
Lisäysoperaattorin käyttäminen
Oletetaan, että sinulla on päivämääräkohtaiset myyntitiedot ja haluat laskea juoksevan summan sarakkeessa C.
Alla on vaiheet tämän tekemiseen.
Vaihe 1 - Kirjoita soluun C2, joka on ensimmäinen solu, johon haluat juoksevan summan
= B2
Tämä yksinkertaisesti saa samat myyntiarvot solussa B2.
Vaihe 2 - Anna soluun C3 seuraava kaava:
= C2+B3
Vaihe 3 - Käytä kaavaa koko sarakkeeseen. Voit valita ja vetää sen täyttökahvan avulla tai yksinkertaisesti kopioida ja liittää solun C3 kaikkiin jäljellä oleviin soluihin (mikä säätää automaattisesti viitettä ja antaa oikean tuloksen).
Tämä antaa sinulle alla olevan tuloksen.
Se on todella yksinkertainen menetelmä ja toimii useimmissa tapauksissa hyvin.
Logiikka on yksinkertainen - jokainen solu valitsee sen yläpuolella olevan arvon (joka on kumulatiivinen summa edellisenä päivänä) ja lisää arvon sen vieressä olevaan soluun (joka on kyseisen päivän myyntiarvo).
On vain yksi haittapuoli - jos poistat minkä tahansa tämän tietojoukon olemassa olevista riveistä, kaikki alla olevat solut palauttavat viittausvirheen (#REF!)
Jos tämä on mahdollista tietojoukossasi, käytä seuraavaa SUM -kaavaa käyttävää menetelmää
SUM: n käyttäminen osittain lukitun soluviittauksen kanssa
Oletetaan, että sinulla on päivämääräkohtaiset myyntitiedot ja haluat laskea juoksevan summan sarakkeessa C.
Alla on SUM -kaava, joka antaa sinulle juoksevan summan.
= SUMMA ($ B $ 2: B2)
Selitän kuinka tämä kaava toimii.
Yllä olevassa SUM -kaavassa olen käyttänyt viittausta lisätäkseni $ B $ 2: B2
- $ B $ 2 - tämä on ehdoton viite, mikä tarkoittaa, että kun kopioin saman kaavan alla oleviin soluihin, tämä viittaus ei muutu. Joten kun kopioit kaavan alla olevaan soluun, kaava muuttuu SUM ($ B $ 2: B3)
- B2 - tämä on viittauksen toinen osa, joka on suhteellinen viite, mikä tarkoittaa, että tämä muuttuu, kun kopioin kaavan alas tai oikealle. Joten kun kopioit kaavan alla olevaan soluun, tästä arvosta tulee B3
Lue myös: Absoluuttiset, suhteelliset ja sekoitetut soluviitteet Excelissä
Hienoa tässä menetelmässä on, että jos poistat jonkin tietojoukon riveistä, tämä kaava mukauttaa ja antaa sinulle silti oikeat juoksevat summat.
Juoksun kokonaismäärän laskeminen Excel -taulukossa
Kun käytät taulukkotietoja Excelissä, on hyvä muuttaa ne Excel -taulukkoksi. Se helpottaa tietojen hallintaa huomattavasti ja mahdollistaa myös työkalujen, kuten Power Queryn ja Power Pivotin, käytön.
Excel -taulukoiden käsittelyssä on etuja, kuten strukturoituja viittauksia (mikä tekee todella helpoksi viitata taulukon tietoihin ja käyttää niitä kaavoissa) ja viittausten automaattista säätöä, jos lisäät tai poistat tietoja taulukosta.
Vaikka voit edelleen käyttää yllä olevaa kaavaa, jonka olen näyttänyt sinulle Excel -taulukossa, näytän sinulle joitain parempia tapoja tehdä tämä.
Oletetaan, että sinulla on alla olevan taulukon mukainen Excel -taulukko ja haluat laskea juoksevan summan sarakkeessa C.
Alla on kaava, jolla tämä tehdään:
= SUMMA (SalesData [[#Headers], [Sale]]: [@Sale])
Yllä oleva kaava voi näyttää hieman pitkältä, mutta sinun ei tarvitse kirjoittaa sitä itse. Summakaavassa näkyvää sisältöä kutsutaan strukturoiduiksi viitteiksi, mikä on Excelin tehokas tapa viitata tiettyihin datapisteisiin Excel -taulukossa.
Esimerkiksi SalesData [[#Headers], [Sale]] viittaa SalesData -taulukon Myynti -otsikkoon (SalesData on sen Excel -taulukon nimi, jonka annoin taulukkoa luodessani)
Ja [@Sale] viittaa arvon arvoon Sale -sarakkeen samalla rivillä.
Selitin tämän vain ymmärryksesi vuoksi, mutta vaikka et tiedä mitään strukturoiduista viitteistä, voit silti luoda tämän kaavan helposti.
Alla on vaiheet tämän tekemiseen:
- Kirjoita soluun C2 = SUM (
- Valitse solu B1, joka on myyntiarvon sisältävän sarakkeen otsikko. Voit käyttää hiirtä tai nuolinäppäimiä. Huomaat, että Excel syöttää automaattisesti kyseisen solun strukturoidun viitteen
- Lisää: (kaksoispiste)
- Valitse solu B2. Excel lisäisi jälleen automaattisesti solun strukturoidun viittauksen
- Sulje kiinnike ja paina enter
Huomaat myös, että sinun ei tarvitse kopioida kaavaa koko sarakkeeseen, Excel -taulukko tekee sen automaattisesti puolestasi.
Toinen hieno asia tässä menetelmässä on, että jos lisäät uuden tietueen tähän tietojoukkoon, Excel -taulukko laskee automaattisesti kaikkien uusien tietueiden juoksevan summan.
Vaikka olemme sisällyttäneet sarakkeen otsikon kaavaamme, muista, että kaava ohittaa otsikkotekstin ja ottaa huomioon vain sarakkeen tiedot
Kokonaislaskennan laskeminen Power Queryn avulla
Power Query on hämmästyttävä työkalu, kun on kyse yhdistämisestä tietokantoihin, tietojen poimimisesta useista lähteistä ja muuntamisesta ennen Exceliin lisäämistä.
Jos työskentelet jo Power Queryn kanssa, olisi tehokkaampaa lisätä käynnissä olevia kokonaissummia, kun muunnat tietoja itse Power Query -editorissa (sen sijaan, että ensimmäinen hankki tiedot Excelistä ja lisää sitten käynnissä olevat kokonaismäärät käyttämällä mitä tahansa yllä olevista edellä kuvattuja menetelmiä).
Vaikka Power Queryssä ei ole sisäänrakennettua ominaisuutta käynnissä olevien kokonaissummien lisäämiseksi (jonka toivoisin olevan), voit silti tehdä sen käyttämällä yksinkertaista kaavaa.
Oletetaan, että sinulla on alla olevan taulukon mukainen Excel -taulukko ja haluat lisätä juoksevat summat näihin tietoihin:
Alla on vaiheet tämän tekemiseen:
- Valitse mikä tahansa Excel -taulukon solu
- Napsauta Tiedot
- Napsauta Hae ja muunna -välilehdessä Taulukko/alue -kuvaketta. Tämä avaa taulukon Power Query -editorissa
- [Valinnainen] Jos Päivämäärä -saraketta ei ole jo lajiteltu, napsauta Päivämäärä -sarakkeen suodatinkuvaketta ja valitse sitten Lajittele nousevasti
- Napsauta Lisää sarake -välilehteä Power Query -editorissa
- Napsauta Yleiset -ryhmässä avattavaa Indeksisarake -valikkoa (älä napsauta Indeksisarake -kuvaketta, vaan sen vieressä olevaa pientä mustaa kallistettua nuolta, jos haluat nähdä lisää vaihtoehtoja)
- Napsauta "From 1" -vaihtoehtoa. Tämä lisää uuden hakemistosarakkeen, joka alkaa yhdestä ja syöttää numeroita, jotka kasvavat yhdellä koko sarakkeeseen
- Napsauta mukautetun sarakkeen kuvaketta (joka on myös Lisää sarake -välilehdessä)
- Kirjoita avautuvan mukautetun sarakkeen valintaikkunaan uuden sarakkeen nimi. Tässä esimerkissä käytän nimeä "Running Total"
- Kirjoita alla oleva kaava Mukautetun sarakkeen kaava -kenttään: List.Sum (List.Range (#”Added Index” [Sale], 0, [Index]))
- Varmista, että valintaikkunan alareunassa on valintaruutu, jossa lukee "Syntaksivirheitä ei ole havaittu".
- Napsauta OK. Tämä lisäisi uuden käynnissä olevan kokonaissarakkeen
- Poista hakemistosarake
- Napsauta Tiedosto -välilehteä ja napsauta sitten Sulje ja lataa
Yllä olevat vaiheet lisäisivät työkirjaasi uuden taulukon, jossa on taulukko, jossa on juoksevat summat.
Jos nyt ajattelet, että nämä ovat aivan liian monta vaihetta verrattuna aiempiin yksinkertaisten kaavojen käyttömenetelmiin, olet oikeassa.
Jos sinulla on jo tietojoukko ja sinun tarvitsee vain lisätä käynnissä olevat kokonaissummat, on parempi olla käyttämättä Power Queryä.
Power Queryn käyttäminen on järkevää, kun sinun on poimittava tietoja tietokannasta tai yhdistettävä tiedot useista eri työkirjoista ja lisättävä prosessiin myös juoksevia summia.
Lisäksi kun teet tämän automatisoinnin Power Queryn avulla, seuraavan kerran, kun tietojoukko muuttuu, sinun ei tarvitse tehdä sitä uudelleen, voit yksinkertaisesti päivittää kyselyn ja se antaa sinulle tuloksen uuden tietojoukon perusteella.
Miten tämä toimii?
Selitän nyt nopeasti, mitä tässä menetelmässä tapahtuu.
Ensimmäinen asia, jonka teemme Power Query -editorissa, on lisätä hakemistosarake, joka alkaa yhdestä ja kasvaa yhdellä soluja alaspäin.
Teemme tämän, koska meidän on käytettävä tätä saraketta laskettaessa juokseva summa toisessa sarakkeessa, jonka lisäämme seuraavaan vaiheeseen.
Sitten lisäämme mukautetun sarakkeen ja käytämme alla olevaa kaavaa
List.Sum (List.Range (#"Lisätty hakemisto" [Sale], 0, [Index]))
Tämä on List.Sum -kaava, joka antaa sinulle sen sisällä määritetyn alueen summan.
Ja tämä alue määritetään List.Range -toiminnolla.
List.Range -funktio antaa myyntisarakkeessa määritellyn alueen tulostena ja tämä alue muuttuu indeksin arvon perusteella. Esimerkiksi ensimmäisen tietueen osalta alue olisi yksinkertaisesti ensimmäinen myyntiarvo. Ja kun menet alas soluihin, tämä alue laajenisi.
Joten ensimmäiselle solulle. Lista.Summa antaa sinulle vain ensimmäisen myyntiarvon summan ja toisessa solussa kaksi ensimmäistä myyntiarvoa ja niin edelleen.
Vaikka tämä menetelmä toimii hyvin, se on todella hidasta suurten tietojoukkojen kanssa - tuhansia rivejä. Jos käsittelet suurta tietojoukkoa ja haluat lisätä siihen käynnissä olevia kokonaismääriä, katso tämä opetusohjelma, joka näyttää muita nopeampia menetelmiä.Juoksun kokonaismäärän laskeminen kriteerien perusteella
Toistaiseksi olemme nähneet esimerkkejä, joissa olemme laskeneet juoksevan summan kaikille sarakkeen arvoille.
Mutta voi olla tapauksia, joissa haluat laskea tiettyjen tietueiden juoksevan summan.
Esimerkiksi alla minulla on tietojoukko ja haluan laskea tulostimien ja skannerien juoksevan summan erikseen kahdessa eri sarakkeessa.
Tämä voidaan tehdä käyttämällä SUMIF -kaavaa, joka laskee juoksevan summan ja varmistaa samalla, että määritetty ehto täyttyy.
Alla on kaava, joka tekee tämän Tulostin -sarakkeille:
= SUMIF ($ C $ 2: C2, $ D $ 1, $ B $ 2: B2)
Voit myös laskea skannerien juoksevan summan alla olevan kaavan avulla:
= SUMIF ($ C $ 2: C2, $ E $ 1, $ B $ 2: B2)
Yllä olevissa kaavoissa olen käyttänyt SUMIFia, joka antaisi minulle summan alueella, kun määritetyt ehdot täyttyvät.
Kaava sisältää kolme argumenttia:
- valikoima: tämä on ehtoalue, joka tarkistetaan määritettyjen ehtojen perusteella
- kriteeri: tämä on ehto, joka kirjataan sisään vain, jos tämä ehto täyttyy, kolmannen argumentin, joka on summa -alue, arvot lisätään
- [summa_alue]: tämä on summa -alue, josta arvot lisätään, jos kriteerit täyttyvät
Myös, valikoima ja summa_alue argumentti, olen lukinnut viittauksen toisen osan, jotta soluja alaspäin laskettaessa alue laajenisi jatkuvasti. Tämän ansiosta voimme vain ottaa huomioon ja lisätä arvoja tähän alueeseen saakka (siis kokonaissummat).
Tässä kaavassa olen käyttänyt kriteerinä otsikkosaraketta (tulostin ja skanneri). voit myös kovakoodata ehdot, jos sarakkeiden otsikot eivät ole täsmälleen samat kuin ehtojen teksti.
Jos sinulla on useita ehtoja, jotka sinun on tarkistettava, voit käyttää SUMIFS -kaavaa.
Ajo yhteensä pivot -taulukoissa
Jos haluat lisätä juoksevia summia Pivot -taulukon tulokseen, voit tehdä sen helposti käyttämällä sisäänrakennettua toimintoa Pivot -taulukoissa.
Oletetaan, että sinulla on alla oleva pivot -taulukko, jossa minulla on päivämäärä yhdessä sarakkeessa ja myyntiarvo toisessa sarakkeessa.
Alla on ohjeet lisäsarakkeen lisäämiseen, joka näyttää myynnin kokonaismäärän päivämäärän mukaan:
- Vedä Myynti -kenttä ja aseta se Arvo -alueelle.
- Tämä lisää uuden sarakkeen, jossa on Myynti -arvot
- Napsauta Arvon alueella myyntivaihtoehto2
- Napsauta "Arvokenttäasetukset" -vaihtoehtoa
- Muuta Arvokentän asetukset -valintaikkunassa mukautettu nimi "Juokseva summa"
- Napsauta Näytä arvo nimellä -välilehteä
- Valitse avattavasta Näytä arvo avattavasta valikosta Juokseva summa -vaihtoehto
- Varmista Peruskenttä -vaihtoehdoista, että Päivämäärä on valittuna
- Napsauta OK
Yllä olevat vaiheet muuttavat toisen myynnin sarakkeen Running Total -sarakkeeksi.
Joten nämä ovat joitakin tapoja, joilla voit laskea juoksevan summan Excelissä. Jos sinulla on tietoja taulukkomuodossa, voit käyttää yksinkertaisia kaavoja ja jos sinulla on Excel -taulukko, voit käyttää kaavoja, joissa käytetään strukturoituja viittauksia.
Olen myös käsitellyt kuinka lasketaan juokseva kokonaismäärä Power Queryn ja pivot -taulukoiden avulla.
Toivottavasti pidit tätä opetusohjelmaa hyödyllisenä.
