Tämä jakso näyttää kolme tapaa suorittaa juoksevat summat.
Juokseva summa on numeeristen arvojen luettelon osalta summa ensimmäisestä rivistä juoksevan kokonaisuuden riville. Juoksevan summan yleiset käyttötarkoitukset ovat sekkirekisterissä tai kirjanpitolomakkeessa. On olemassa monia tapoja luoda juokseva summa, joista kaksi on kuvattu alla.
Yksinkertaisin tekniikka on lisätä jokaisella rivillä juokseva summa edellisestä rivistä rivin arvoon. Joten rivin 2 ensimmäinen kaava on:
=SUM(D1,C2)
Syy, miksi käytämme SUM-toimintoa, johtuu siitä, että ensimmäisellä rivillä tarkastelemme yllä olevan rivin otsikkoa. Jos käytämme yksinkertaisempaa, intuitiivisempaa kaavaa, =D1+C2syntyy virhe, koska otsikon arvo on teksti vs. numeerinen. Taika on, että SUM-funktio jättää huomiotta tekstiarvot, jotka lisätään nolla-arvoina. Kun kaava kopioidaan alaspäin kaikkiin riveihin, joissa juokseva summa halutaan, soluviittauksia mukautetaan vastaavasti:
Toinen tekniikka käyttää myös SUM-funktiota, mutta kukin kaava summaa kaikki arvot ensimmäiseltä riviltä riville, joka näyttää juoksevan summan. Tässä tapauksessa käytämme dollarin merkkiä ($), jolloin referenssin ensimmäisestä solusta tehdään absoluuttinen viite, mikä tarkoittaa, että sitä ei muuteta kopioinnin yhteydessä:
Rivien lajittelu ja poistaminen ei vaikuta molempiin tekniikoihin, mutta kun lisäät rivejä, kaava on kopioitava uusille riveille.
Excel 2007 esitteli taulukon, joka on luettelon uudelleen käyttöönotto Excel 2003: ssa. Taulukot esittivät useita hyödyllisiä ominaisuuksia taulukoille, kuten muotoilu, lajittelu ja suodatus. Taulukoiden käyttöönoton myötä saimme myös uuden tavan viitata taulukon osiin. Tätä uutta viittaustyyliä kutsutaan strukturoiduksi viittaukseksi.
Jos haluat muuntaa yllä olevan esimerkin taulukoksi, valitsemme tiedot, jotka haluamme sisällyttää taulukkoon, ja painamme Ctrl + T.Kun näytölle tulee kehote, jossa pyydetään vahvistamaan taulukon alue ja onko olemassa olemassa olevia otsikoita, Excel muuntaa tiedot alustettuun taulukkoon:
Huomaa, että aiemmin kirjoittamamme kaavat pysyvät samoina.
Yksi taulukoiden tarjoamista hyödyllisistä ominaisuuksista on automaattinen muotoilu ja kaavan ylläpito, kun rivejä lisätään, poistetaan, lajitellaan ja suodatetaan. Keskitymme erityisesti kaavan ylläpitoon, mikä voi olla ongelmallista. Jotta taulukot pysyisivät toiminnassa, kun niitä käsitellään, Excel käyttää laskettuja sarakkeita, jotka ovat sarakkeita, joilla on kaavat, kuten yllä olevassa esimerkissä sarake D. Kun uusia rivejä lisätään alaosaan, Excel täyttää uudet rivit automaattisesti kyseisen sarakkeen "oletus" -kaavalla. Yllä olevan esimerkin ongelmana on, että Excel sekoittuu tavallisiin kaavoihin eikä aina käsittele niitä oikein. Tämä käy ilmi, kun taulukon alareunaan lisätään uusia rivejä (valitsemalla taulukon oikea alakulma ja painamalla sarkainta):
Tämä puute korjataan käyttämällä uudempaa strukturoitua viittausta. Strukturoitu viittaus eliminoi tarpeen viitata tiettyihin soluihin A1- tai R1C1-viitetyyliä käyttäen ja käyttää sen sijaan sarakkeiden nimiä ja muita avainsanoja taulukon osien tunnistamiseen ja viittaamiseen. Esimerkiksi sellaisen yllä olevan kokonaiskaavan luomiseksi, jota käytetään yllä, mutta käyttämällä jäsennettyä viittausta, meillä on
=SUM(INDEX((Sales),1):(@Sales))
Tässä esimerkissä meillä on viittaus sarakkeen nimeen "Myynti" yhdessä at-merkin (@) kanssa viittaamaan riviin sarakkeessa, jossa kaava sijaitsee, joka tunnetaan myös nimellä nykyinen rivi.
Voit käyttää yllä olevaa ensimmäistä esimerkkiä, jossa lisättiin edellisen rivin juokseva kokonaisarvo nykyisen rivin myyntisummaan, käyttämällä OFFSET-toimintoa:
=SUM(OFFSET((@(Running Total)),-1,0),(@Sales))
Jos juoksevan kokonaissumman laskemiseen käytetyt summat ovat kahdessa sarakkeessa, esimerkiksi yksi kohdassa ”Velat” ja yksi ”Luotot”, kaava on seuraava:
=SUM(INDEX( (Credit),1):(@Credit))- SUM(INDEX( (Debit),1):(@Debit))
Täällä käytämme INDEX-funktiota ensimmäisen rivin hyvitys- ja veloitussolujen paikantamiseen ja koko sarakkeen yhteenlaskemiseen nykyisen rivin arvoihin. Juokseva summa on kaikkien nykyiseen riviin asti sisältyvien hyvitysten summa, josta on vähennetty kaikkien nykyiseen riviin asti luettujen veloitusten summa.
Lisätietoja strukturoiduista viitteistä ja taulukoista yleensä, suosittelemme Zack Barressen ja Kevin Jonesin kirjaa Excel-taulukot: täydellinen opas luetteloiden ja taulukoiden luomiseen, käyttämiseen ja automatisointiin.
Kun pyysin lukijoita äänestämään suosikkivihjeiden puolesta, taulukot olivat suosittuja. Kiitos Peter Albertille, Snorre Eikelandille, Nancy Federicelle, Colin Michaelille, James E. Moedelle, Keyur Patelille ja Paul Petonille tämän ominaisuuden ehdottamisesta. Peter Albert kirjoitti Readable References -bonusvihjeen. Zack Barresse kirjoitti Running Totals -bonusvinkin. Neljä lukijaa ehdotti OFFSETin käyttöä laajentuvien alueiden luomiseen dynaamisille kaavioille: Charley Baak, Don Knowles, Francis Logan ja Cecelia Rieb. Taulukot tekevät nyt saman asian useimmissa tapauksissa.
Katso video
- Tämä jakso näyttää kolme tapaa suorittaa juoksevat kokonaissummat
- Ensimmäisellä menetelmällä on erilainen kaava rivillä 2 kuin kaikilla muilla riveillä
- Ensimmäinen menetelmä on = Vasen rivillä 2 ja = Vasen + Ylös rivillä 3 - N
- Jos yrität käyttää samaa kaavaa, saat #Value -virheen = Kokonais + Luku
- Menetelmässä 2 käytetään
=SUM(Up,Left)tai=SUM(Previous Total,This Row Amount) - SUM ohittaa tekstin, joten et saa VALUE-virhettä
- Tapa 3 käyttää laajenevaa aluetta:
=SUM(B$2:B2) - Alueiden laajentaminen on hienoa, mutta hidasta
- Lue Charles Williamsin julkaisu Excel Formula Speed -sivustolta
- Kolmas menetelmä on ongelma, kun käytät Ctrl + T ja lisäät uusia rivejä
- Excel ei pysty selvittämään, kuinka kaava kirjoitetaan
- Kiertotavat edellyttävät jonkin verran tietoa jäsennellystä viittauksesta taulukoissa
- Kiertotapa 1 on hidas
=SUM(INDEX((Qty),1):(@Qty)) - Kiertotapa 2 on epävakaa
=SUM(OFFSET((@Total),-1,0),(@Qty)) - (@Qty) viittaa tämän rivin määrään
- (Määrä) viittaa kaikkiin Määrä-arvoihin
Videon transkriptio
Opi Excel for Podcast, jakso 2004 - juoksevat summat
Podcastin koko tämän kirjan. Tilaa napsauttamalla oikeassa yläkulmassa olevaa I-kuvaketta.
Hei tervetuloa takaisin mystiseen soluun. Olen Bill Jelen. Nyt tämän kirjan aiheen kirjoitti ystäväni Zach Parise. Puhu Excel-taulukoista, Zach on maailman Excel-taulukoiden asiantuntija. Hän on kirjoittanut kirjan Excel-taulukoista, mutta ensin puhutaan kokonaissummien ajamisesta ei taulukoissa.
Joten kun ajattelen juoksusummia, on kolme erilaista tapaa suorittaa juoksevat kokonaismäärät, ja tapa, jolla aloitin aina, on ensimmäisellä rivillä, jonka sanot, tuo arvo yli. Joten yhtä tasainen mitä vasemmalla puolellani on. Selvä, joten tämä muoto tässä on vain = B2. Nämä ovat kaikki kaavateksti täällä oikeassa kulmassa, joten näet mitä käytämme, ja sitten siitä alaspäin, se on yksinkertainen pieni kaava, joka on sama kuin edellinen arvo, plus nykyinen arvo oikealla ja kopioi se alas , mutta tiedät nyt, että meillä on tämä ongelma, että se vaati kahta erilaista kaavaa ja tiedät täydellisessä tilanteessa, että sinulla on täsmälleen sama kaava kokonaan, ja syy, miksi meillä on oltava erilainen kaava siellä ensimmäisellä rivillä on että kun yrität lisätä yhtä paljon 7 plus sana yhteensä, se on arvovirhe,mutta viileä työntekijä täällä, ei ole vain käyttää vasenta plus ylös, vaan käyttää = (SUM) edellisestä arvosta plus tämän rivin määrä, ja nähdä, että jotkut ovat tarpeeksi kaukana tekstien ohittamiseksi. Aivan niin, että sallitaan sama kaava. koko matkan.
Selvä, niin silloin kun aloitin Excelin käytön, käytin sitä ja sitten löysin laajenevan alueen, laajenevan alueen mukaan aiomme tehdä L $ 2: L2 ja mitä tapahtuu, tämä alkaa aina riviltä 2, mutta sitten se menee alas nykyiselle riville. Joten kun katsot kuinka tämä toimii, kun se kopioidaan, aloitimme aina rivin 2, mutta menemme alas nykyiselle riville ja tästä tuli suosikkitapani. Olin kuin, oh, tämä on paljon kehittyneempiä, ja kun siirrymme Excel-asetuksiin, siirry Kaavat-välilehteen ja valitse R1C1 viitetyyliin. Selvä, R1C1, kaikki nämä kaavat ovat täsmälleen samat koko matkan. En tiedä, ymmärrätkö R1C1: n, on vain hyvä tietää, että meillä on samanlaiset R1C1-kaavat koko matkan.
Mennään takaisin. Joten tämä menetelmä tässä on menetelmä, josta pidin, kunnes Charles Williams, englantilainen Excel MBP, jolla on hämmästyttävä paperi kaavanopeudesta, Excel-kaavanopeudesta, kumosi tämän menetelmän kokonaan. Oletetaan, että tällä menetelmällä on 10000 riviä, jokainen kaava tarkastelee kahta viittausta. Joten katsot 20000 viitettä, mutta tämä, tämä tarkastelee kahta, tämä tarkastelee kolmea, tämä tarkastelee neljää, tämä tarkastelee viittä ja viimeinen 10000 viitettä, ja se on kammottavasti hitaampaa joten lopetin tämän menetelmän käytön.
Sitten jatkan lukemista Zack Kevin Jonesin kirjassa Excel-taulukoista ja löydän vielä yhden ongelman tällä menetelmällä. Joten yksi taulukoiden tarjoamista hyödyllisistä ominaisuuksista on 'automaattinen muotoilu ja kaavan ylläpitorivit lisätään, poistetaan, lajitellaan ja suodatetaan'. Selvä, se on lainaus hänen kirjastaan. Voit lisätä rivin taulukkoon siirtymällä taulukon viimeiseen soluun ja painamalla sarkainta. Joten kaikki toimii täällä. Olemme 70-vuotiaita, mikä on mahtavaa, ja sitten A104 ja laitan 100. Selvä, niin että 70: n pitäisi muuttua 170: ksi ja se muuttuu, mutta tämän 70: n ei olisi pitänyt muuttua ollenkaan. Selvä 68 + 2 ei ole 170. Teen sen uudestaan. 104 ja laittaa vielä sata viimeiseen on oikeassa. Nämä kaksi eivät ole oikeassa. Selvä, joten meillä on outo tilanne, että joskäytät tätä kaavaa uudelleen ja muunnat taulukkoon, kun aloitat rivien lisäämisen, juokseva summa ei toimi. Kuinka paha se on?
Selvä, joten Zack tarjoaa kaksi kiertotietoa ja molemmat vaativat vähän tietoa siitä, miten rakenneviitteet toimivat. Meillä on juuri uusi sarake täällä, ja jos halusin tehdä määrän, yhtä suuri määrä, niin, niin että = (@ Määrä) sanoo määrän tässä rivissä. Voi siistiä, hyvin, on olemassa eräänlainen viittaus, jossa käytämme Määrä ilman @ Tsekkaa tämä. Joten = SUMMA (INDEKSI ((Määrä), 1: (@ Määrä)) tarkoittaa kaikkia määriä, ja aiomme sanoa, että haluamme SUMMUTTA ensimmäisestä määrästä, joten (HAKEMISTO ((Määrä), 1 sanoo ensimmäinen arvo täällä, nykyiseen rivimäärään asti, ja tämä käyttää todella erityistä indeksiversiota, kun indeksiä seuraa kaksoispiste, se muuttuu tosiasiallisesti soluviittaukseksi. Selvä, niin tämä kiertotapa rikkoo valitettavasti Charles Williams -sääntöä of, me 'Meidän on tarkasteltava jokaista viittausta, joten kun saat 10000 riviä, tämä menee todella, todella hitaasti.
Zachilla on toinen kiertotapa, joka ei riko Charles Williamsin ongelmaa, mutta se käyttää pelättyä OFFSET-toimintoa. OFFSET on haihtuva toiminto, joten joka kerta, kun lasket jotain, OFFSET laskee uudelleen ja kaikki OFFSETin alalinjan laskevat uudelleen. Se on vain hieno tapa kaataa kaavasi kokonaan, kokonaan, ja mitä tämä tekee, sanotaan, otamme kokonaissumman tältä riviltä, nousemme yhdellä rivillä, yli nollan sarakkeen, joten mitä se tekee, sanoo: tartu edellisen rivin kokonaismäärään ja sitten lisätään siihen tämän rivin määrä. Selvä, joten nyt kaikki tarkastellaan joka kerta kahta viittausta, mutta valitettavasti OFFSET tuo käyttöön epävakaat toiminnot.
No, sinulla on se, enemmän kuin olet koskaan halunnut tietää Running Totalsista. Luulen, että lopullinen mielipiteeni on käyttää tätä menetelmää, koska se näyttää siltä vain kahdelta. Sama kaava koko matkan ja strukturoidut taulukkoviitteet toimivat.
Tutustu tähän tutkimukseen ja 39 muuhun todella hyvään vinkkiin tästä kirjasta XL, joka on kaikkien aikojen 40 suurinta Excel-vinkkiä.
Tämän jakson yhteenvetona puhuimme kolmesta tapasta suorittaa juoksevat summat. Ensimmäisellä menetelmällä on erilainen kaava, rivi 2, kuin kaikilla muilla riveillä. Se on yhtä suuri vasemmalla rivillä 2 ja sitten yhtä suuri vasen plus ylös riveillä 3 - N, mutta jos yrität vain käyttää samaa kaavaa, yhtä suuri vasen plus ylös, kokonaan alas, miten aiot saada #Value -virheen . Joten = SUM (Ylös, Vasen), joka on edellinen summa, sekä tämä toimiva etenemissuunnitelma, joka toimii hyvin, ei arvovirheitä ja sitten laajeneva alue, jota käytän rakastamaan. He ovat hienoja, mutta kunnes luin Charles Williamsin valkoisen kirjan Excel-nopeuden muodossa. Sitten aloin vihata näitä laajenevia viitteitä. Sillä on myös ongelma, kun käytät CTRL T: tä ja lisäät uusia rivejä. Excel ei pysty selvittämään, kuinka kaavaa laajennetaan, miten uusia rivejä lisätään. Rakastan tätä vinkkiä menemällä taulukon viimeiseen soluun ja painamalla sarkainta,se lisää uuden rivin ja sitten puhuimme joistakin jäsennellyistä viittauksista, joissa käytämme määrää tällä rivillä ja sitten kaikkia määriä. = SUMMA (OFFSET ((@ Yhteensä), - 1,00, (@ Määrä)).
Okei, haluan kiittää Zachia siitä, että annoit sen vinkin. Haluan kiittää sinua pysähtymisestä. Nähdään ensi kerralla toisesta netcastista.
Lataa tiedosto
Lataa esimerkkitiedosto täältä: Podcast2004.xlsx
