olet todennäköisesti menossa törmännyt tarvetta käynnissä loppusummat jos olet tekemisissä minkäänlaista päivittäistä tietoa.

Kuvittele, että seuraat myyntiä joka päivä. Tietosi sisältävät rivin jokaiselle päivämäärälle kokonaismyyntimäärän kanssa, mutta ehkä haluat tietää kuukauden kokonaismyynnin jokaisena päivänä. Tämä on käynnissä yhteensä, se on summa kaikki myynti asti ja mukaan lukien nykyisen päivän myynti.

tässä viestissä käsittelemme useita tapoja laskea juokseva summa päivittäisille tiedoillesi. Tutkimme, miten käyttää laskentataulukoita, pivot-taulukoita, power pivotia Daxin ja power-kyselyn avulla.

tutkimme myös, mitä tapahtuu käynnissä olevalle kokonaislaskennalle, kun datarivejä lisätään tai poistetaan, ja miten tuloksia päivitetään.

Hanki tiedosto, jossa on kaikki esimerkit.

juoksevat kokonaissummat yksinkertaisella kaavalla

on mahdollista luoda perusjuoksun kokonaissummakaava käyttämällä + – operaattoria.

homman hoitaminen vaatii kuitenkin kahta eri kaavaa.

1. =C3 on ensimmäinen kaava ja jää vain juoksusumman ensimmäiselle riville.
2. =C4+D3 tulee toiselle riville ja se voidaan kopioida alas jäljellä oleville riveille käynnissä olevan kokonaissumman osalta.

ensimmäisen rivin kaava ei voi lisätä sen yläpuolista solua kokonaisuuteen, koska se sisältää tekstiarvon sarakkeen otsikolle. Tämä aiheuttaisi # – arvon! virhe ilmestyä käynnissä olevaan kokonaissummaan, koska + ei voi käsitellä tekstiarvoja. Vältämme tämän toisella kaavalla ensimmäisellä rivillä, joka ei viittaa edellä olevaan soluun.

Mitä tapahtuu käynnissä olevalle kokonaisuudelle, kun lisäämme tai poistamme rivejä tiedoissamme?

uuden rivin lisääminen aiheuttaa aukon juoksusummaan. Korjata tämä, meidän täytyy kopioida kaava alas ensimmäisestä solusta yläpuolella äskettäin lisätty rivit aina alas viimeinen rivi.

kaikkien rivien poistaminen johtaa #REF! virheet, koska poistaminen rivi tarkoittaa poistamalla solu viitattu kaavalla sen alla. Korjata tämä, meidän täytyy kopioida kaava alas viime virheetön solu aina alas viimeinen rivi.

juoksevat loppusummat SUMMAKAAVALLA

voimme välttää kiusallisuuden käyttää kahta eri kaavaa juoksevassa kokonaissarakkeessamme käyttämällä SUMMAFUNKTIOTA + – operaattorin sijaan. Kun SUMMAFUNKTIO kohtaa tekstisolun, se käsittelee sitä samalla tavalla kuin a, vaikka se sisälsi 0: n.

näin voimme käyttää seuraavaa kaavaa tasaisesti jokaiselle riville mukaan lukien ensimmäinen rivi.

=summa(C3,D2)

Tämä kaava viittaa sarakkeen otsikkoon, joka sisältää tekstin ensimmäiselle riville, mutta tämä ok, koska sitä käsitellään kuin 0.

rivejä lisättäessä tai poistettaessa törmäämme edelleen samoihin tyhjiin soluihin ja virheisiin. Voimme korjata ne samalla tavalla kuin käynnissä yhteensä yksinkertainen kaava menetelmällä.

Running Totals with a Partially Fixed Range

toinen vaihtoehto, jossa on SUMMAFUNKTIO, on viitata vain Myyntisarakkeeseen ja käyttää osittain kiinteää vaihteluväliä.

Jos käytämme seuraavaa kaavaa =summa($C$3:C3), voimme kopioida ja liittää tämän vaihteluväliin. Se ei viittaa mihinkään sarakkeen otsikoita ja alue, johon viitataan kasvaa jokaiselle riville.

valitettavasti tässäkin on samoja ongelmia (ja ratkaisuja) rivien lisäämisessä tai poistamisessa.

Running Totals with a Relative Named Range

we can avoid the problems with inserting and deleting rivejä from our data if we use a relative named range. Tämä viittaa soluun suoraan yläpuolella riippumatta siitä, kuinka monta riviä lisäämme tai poistamme.

kyseessä on temppu, jossa Excel-referenssityyli vaihdetaan hetkellisesti A1: stä R1C1: een. Sitten määritellään nimetty alue käyttäen R1C1 merkintää. Vaihda sitten vertailutyyli Takaisin A1: een.

R1C1-viitetyylissä soluihin viitataan sillä, kuinka kaukana ne ovat solusta referenssiä käyttäen. Esimerkiksi =RC viittaa solun 2 ylös ja 3 oikealle solun käyttämällä tätä kaavaa.

Voimme käyttää tätä suhteellista viittausta luodaksemme nimetyn alueen, joka on aina yksi solu viittaavan solun yläpuolella kaavalla =RC.

Jos haluat vaihtaa viitetyyliä, siirry Tiedosto-välilehteen ja valitse sitten Asetukset. Mene Excel Options-valikon kaava-osioon ja tarkista R1C1-viitetyylilaatikko ja paina sitten OK-painiketta.

nyt voidaan lisätä nimetty alue. Siirry Excel-nauhan kaava-välilehteen ja valitse Määrittele nimi-komento.

merkitään alueen nimeksi ”yllä” kaltainen nimi. Lisää kaava =RC viittaa input ja paina OK-painiketta.

voimme nyt vaihtaa Excelin takaisin oletusviittaustyyliin. Siirry Tiedosto-välilehteen > Asetukset kaava-osio > poista R1C1-viitetyylilaatikko > paina sitten OK-painiketta.

nyt voidaan käyttää kaavaa =summa(,yllä) juoksevassa kokonaissarakkeessamme.

yllä oleva nimetty alue viittaa aina suoraan yllä olevaan soluun. Kun lisäämme tai poistamme rivejä, suhteellinen nimetty alue mukautuu vastaavasti eikä toimenpiteitä tarvita.

itse asiassa jos asetamme tietomme Excel-taulukkoon, kaava täyttyy automaattisesti uusille riveille, koska kaava on yhtenäinen koko sarakkeelle. Mitään toimia ei tarvita kopioimaan mitään kaavoja.

Running Totals with a Pivot Table

p > Pivot-taulukot ovat erittäin hyödyllisiä minkä tahansa tiedon tiivistämiseen. Niissä on muutakin kuin keskiarvojen lisääminen, laskeminen ja löytäminen. On olemassa monia muunlaisia laskelmia rakennettu, ja on todella käynnissä yhteensä laskenta!

ensin on lisättävä tietojen perusteella pivotaulukko. Valitse solu datan sisällä ja siirry Insert-välilehteen ja valitse Kääntyvä komento. Sitten läpi luo Kääntyvä ikkuna valita, missä haluat pivot taulukko, joko uudessa laskentataulukossa tai jossain olemassa.

Lisää päivämääräkenttä pivot-taulukon Rivialueeseen ja lisää Myyntikenttä pivot-taulukon arvo-alueeseen. Lisää Nyt toinen esimerkki myyntikentän Rivialueeseen.

meillä pitäisi nyt olla kaksi samanlaista Myyntikenttää, joista toinen merkitään myyntien Summa2. Voimme nimetä tämän nimikkeen milloin tahansa yksinkertaisesti kirjoittamalla sen päälle jotain käynnissä yhteensä.

Napsauta hiiren kakkospainikkeella mitä tahansa arvoa sales2-kentän Summassa ja valitse Show Value kuten Valitse Running Total In.

haluamme näyttää käynnissä olevan kokonaissumman päivämäärän mukaan, joten seuraavassa ikkunassa on valittava päivämäärä Peruskentäksi.

That ’ s it, we now have a new calculation which shows the running total of our sales inside The pivot table.

Mitä tapahtuu, jos lisäämme tai poistamme rivin lähdeaineistoomme, miten tämä vaikuttaa käynnissä olevaan kokonaisuuteen? Pivot taulukko laskelmat ovat dynaamisia ja ottaa uusia tietoja huomioon sen käynnissä kokonaislaskenta, meidän tarvitsee vain päivittää pivot taulukko.

Napsauta hiiren kakkospainikkeella missä tahansa pivot-taulukon sisällä ja valitse valikosta Päivitä.

käynnissä olevat kokonaissummat Power Pivotilla ja DAX-mitoilla

tämän pari ensimmäistä vaihetta ovat täsmälleen samat säännöllisen pivotaulukon avulla.

valitse tietojen sisällä oleva solu, siirry Insert-välilehteen ja valitse Kääntyvä komento.

Kun tulet Create PivotTable-valikkoon, valitse Lisää tämä data Tietomallilaatikkoon lisätäksesi tiedot tietomalliin ja ota se käyttöön power Pivotin kanssa.

Aseta päivämääräkenttä rivien alueelle ja Myyntikenttä pivot-taulukon arvo-alueelle.

power pivotilla joudumme tekemään DAX-kielellä mitä tahansa ylimääräisiä laskutoimituksia, joita haluamme. Napsauta hiiren kakkospainikkeella taulukon nimeä kääntyvissä kenttien ikkunassa ja valitse Lisää toimenpide luodaksesi uuden laskelman. Huomaa, että tämä on saatavilla vain tietomallin kanssa.

=CALCULATE ( SUM ( Sales ), FILTER ( ALL (Sales ), Sales 

nyt voimme luoda uuden juoksevan kokonaismittamme.

1. Mittaikkunaan on lisättävä Mittanimi. Tässä tapauksessa voimme nimetä uuden toimenpiteen juoksevaksi kokonaisuudeksi.
2. myös yllä oleva kaava on lisättävä Kaavalaatikkoon.
3. power pivotissa on hienoa kyky antaa numeromuoto mitalle. Voimme valita valuutan muodossa meidän toimenpide. Aina kun käytämme tätä toimenpidettä pivot-taulukossa, muotoa käytetään automaattisesti.

paina OK-painiketta ja uusi mitta luodaan.

kääntyvässä kenttien ikkunassa näkyy uusi kenttä. Se on pieni fx kuvake vasemmalla osoittamaan, että se on toimenpide eikä säännöllinen kenttä tiedot.

Voimme käyttää tätä uutta kenttää kuten mitä tahansa muuta kenttää ja vetää sen arvoalueelle lisätäksemme juoksevan kokonaislaskelmamme pivot-taulukkoon.

Mitä tapahtuu juoksevalle kokonaisuudelle, kun lisäämme tai poistamme lähdetaulukosta dataa? Aivan kuten tavallinen pivot taulukko, meidän tarvitsee vain oikealla klikkaa pivot taulukko ja valitse Päivitä päivittää laskelma.

käynnissä olevat kokonaissummat Tehokyselyllä

voimme myös lisätä käynnissä olevat kokonaissummat dataamme tehokyselyllä.

ensin on tuotava taulu tehokyselyyn. Valitse datataulukko ja siirry Data-välilehteen ja valitse from Table / Range-vaihtoehto. Tämä avaa power-kyselyn muokkaimen.

seuraavaksi voimme lajitella tietomme päivämäärän mukaan. Tämä on valinnainen vaihe, jonka voimme lisätä niin, että jos muutamme lähdetietojemme järjestystä, käynnissä oleva kokonaissumma näkyy edelleen päivämäärän mukaan.

Napsauta suodatinvaihdetta päiväys-sarakkeen otsikossa ja valitse asetuksista lajittelu Nouseva.

tarvitaan indeksisarake. Tätä käytetään juoksevassa kokonaislaskennassa myöhemmin. Siirry Lisää sarake-välilehteen ja napsauta pientä nuolta Indeksisarakkeen vieressä lisätäksesi indeksin, joka alkaa 1: stä ensimmäisellä rivillä.

meidän on lisättävä kyselyymme uusi sarake, jotta voimme laskea juoksevan kokonaissumman. Siirry Lisää sarake-välilehteen ja valitse Mukautettu sarake-komento.

voimme nimetä sarakkeen juoksevaksi kokonaisuudeksi ja lisätä seuraavan kaavan.

luettelo.Range-funktio luo arvoluettelon Myyntisarakkeesta, joka alkaa 1.krs: ltä (0. erä) ja joka kattaa useita rivejä indeksisarakkeessa olevan arvon perusteella.

luettelo.Summa funktio sitten lisää tämän luettelon arvoista, joka on meidän käynnissä yhteensä.

emme enää tarvitse indeksisaraketta, se on täyttänyt tarkoituksensa ja voimme poistaa sen. Napsauta hiiren kakkospainikkeella sarakkeen otsikkoa ja valitse Poista vaihtoehdoista.

saimme juoksevan kokonaisuuden ja olemme valmiit kyselymuokkaimella. Voimme sulkea kyselyn ja ladata tulokset uuteen laskentataulukkoon. Mene kyselyeditorin Koti-välilehdelle ja paina Sulje & latauspainike.

Mitä tapahtuu käynnissä olevalle kokonaisuudelle, kun lisäämme tai poistamme rivejä lähdeaineistostamme? Meidän täytyy päivittää power query output taulukko päivittää käynnissä yhteensä muutoksia. Napsauta hiiren oikealla painikkeella missä tahansa pöydällä ja valitse Päivitä päivittääksesi taulukon.

yllä olevalla valinnaisella lajitteluvaiheella, jos lisäämme lähdeaineistoon epäjärjestyksessä olevia päivämääriä, power-kysely lajittelee päivämäärän mukaan ja palauttaa oikean järjestyksen päivämäärän mukaan käynnissä olevan kokonaissumman osalta.

johtopäätökset

juoksevien loppusummien laskemiseen Excelissä on monia eri vaihtoehtoja.

olemme tutkineet vaihtoehtoja, mukaan lukien laskentataulukon kaavat, pivot-taulukot, power pivot DAX-kaavat ja power-kysely. Jotkut tarjoavat vankemman ratkaisun lisättäessä tai poistettaessa rivejä datasta, muut menetelmät tarjoavat helpomman toteutuksen.

laskentataulukon yksinkertaiset kaavat on helppo asentaa, mutta ne eivät käsittele uusien tietorivien lisäämistä tai poistamista helposti. Muut ratkaisut, kuten pivot-taulukot, DAX ja power query ovat vankempia ja käsittelevät datarivien lisäämistä tai poistamista helposti, mutta niitä on vaikeampi määrittää.

on hyvä olla tietoinen kunkin menetelmän hyvistä ja huonoista puolista ja valita se, joka sopii parhaiten. Jos et lisätä tai poistaa uusia tietoja, sitten laskentataulukko kaavoja voi olla tapa mennä.