Selles episoodis on kolm võimalust jooksvate kogusummade tegemiseks.
Jooksev kogusumma on arvväärtuste loendi puhul väärtuste summa esimesest reast jooksva summa reani. Jooksva summa üldkasutus on tšekiraamatus või raamatupidamislehel. Jooksva kokku loomiseks on palju viise, millest kahte kirjeldatakse allpool.
Lihtsaim tehnika on lisada igale reale ülaltoodud rea jooksev kogusumma rea väärtusele. Seega on 2. rea esimene valem järgmine:
=SUM(D1,C2)
Funktsiooni SUM kasutamise põhjus on see, et esimeses reas vaatame ülaltoodud rea päist. Kui kasutame lihtsamat ja intuitiivsemat valemit, =D1+C2luuakse viga, kuna päise väärtus on tekst versus arvuline. Maagiline on see, et funktsioon SUM ignoreerib tekstiväärtusi, mis lisatakse nullväärtustena. Kui valem kopeeritakse kõikidele ridadele, milles soovitakse jooksvat koguarvu, kohandatakse lahtriviiteid vastavalt:
Teine tehnika kasutab ka funktsiooni SUM, kuid iga valem summeerib kõik väärtused esimesest reast reani, mis näitab jooksvat koguarvu. Sel juhul kasutame dollarimärki ($), et teha viite esimesest lahtrist absoluutne viide, mis tähendab, et seda ei korrigeerita kopeerimisel:
Ridade sortimine ja kustutamine ei mõjuta mõlemat tehnikat, kuid ridade lisamisel tuleb valem uutesse ridadesse kopeerida.
Excel 2007 tutvustas tabelit, mis on loendi uuesti rakendamine programmis Excel 2003. Tabelid tutvustasid mitmeid andmetabelite jaoks väga kasulikke funktsioone, nagu vormindamine, sortimine ja filtreerimine. Tabelite kasutuselevõtuga pakuti meile ka uut viisi tabeli osadele viitamiseks. Seda uut viitamisstiili nimetatakse struktureeritud viitamiseks.
Ülaltoodud näite tabeliks teisendamiseks valime andmed, mille tahame tabelisse lisada, ja vajutage klahvikombinatsiooni Ctrl + T. Pärast seda, kui kuvatakse viip, mis palub meil kinnitada tabeli vahemik ja kas olemasolevad päised on olemas, teisendab Excel andmed vormindatud tabelisse:
Pange tähele, et varem sisestatud valemid jäävad samaks.
Üks tabelite pakutavaid kasulikke funktsioone on automaatne vormindamine ja valemihooldus, kui ridu lisatakse, eemaldatakse, sorteeritakse ja filtreeritakse. Eriti keskendume valemihooldusele, mis võib olla problemaatiline. Tabelite töötamise ajal töötamise ajal kasutab Excel arvutatud veerge, mis on valemitega veerud, nagu ülaltoodud näites veerg D. Uute ridade lisamisel põhja lisab Excel uued read automaatselt selle veeru vaikevalemiga. Ülaltoodud näite probleem on see, et Excel läheb segi tavaliste valemitega ja ei käitu nendega alati õigesti. See ilmneb siis, kui tabeli alla lisatakse uued read (valides tabelis alumise parema lahtri ja vajutades tabeldusklahvi).
See puudus kõrvaldatakse uuema struktureeritud viitamise abil. Struktureeritud viitamine välistab vajaduse viidata konkreetsetele lahtritele A1 või R1C1 viitamisstiili abil ning kasutab tabeli osade tuvastamiseks ja neile viitamiseks hoopis veerunimesid ja muid märksõnu. Näiteks sama jooksva kogu valemi loomiseks, mida kasutati eespool, kuid kasutades struktureeritud viidet, on meil:
=SUM(INDEX((Sales),1):(@Sales))
Selles näites on meil viide veeru nimele "Müük" koos märgiga at (@), et viidata reale veerus, kus valem asub, mida nimetatakse ka praeguseks reaks.
Esimese ülaltoodud näite rakendamiseks, kus eelmise rea jooksva koguväärtuse lisasime praeguse rea müügisummale, saate kasutada funktsiooni OFFSET:
=SUM(OFFSET((@(Running Total)),-1,0),(@Sales))
Kui jooksva summa arvutamiseks kasutatud summad on kahes veerus, näiteks üks „Debite” ja üks „Credits”, siis on valem järgmine:
=SUM(INDEX( (Credit),1):(@Credit))- SUM(INDEX( (Debit),1):(@Debit))
Siin kasutame funktsiooni INDEX, et leida esimese rea lahtrid Kreedit ja Deebet ning summeerida kogu veerg kuni praeguse rea väärtusteni (kaasa arvatud). Jooksev kogusumma on kõigi krediitide summa kuni praeguse reani (kaasa arvatud), millest on lahutatud kõigi debiteerimiste summa kuni praeguse reani (kaasa arvatud).
Täpsemalt struktureeritud viidete ja tabelite kohta lisateabe saamiseks soovitame Zack Barresse ja Kevin Jonesi raamatut Exceli tabelid: täielik nimekirjade ja tabelite loomise, kasutamise ja automatiseerimise juhend.
Kui palusin lugejatel hääletada nende lemmiknõuannete poolt, olid tabelid populaarsed. Tänu Peter Albertile, Snorre Eikelandile, Nancy Federice'ile, Colin Michaelile, James E. Moede'ile, Keyur Patelile ja Paul Petonile selle funktsiooni soovitamise eest. Peter Albert kirjutas loetavate viidete boonusenõu. Zack Barresse kirjutas Running Totals boonusenõu. Neli lugejat soovitas dünaamiliste graafikute laienevate vahemike loomiseks kasutada OFFSET-i: Charley Baak, Don Knowles, Francis Logan ja Cecelia Rieb. Nüüd teevad tabelid enamikul juhtudel sama asja.
Vaata videot
- Selles episoodis on kolm võimalust jooksvate kogusummade tegemiseks
- Esimesel meetodil on 2. real erinev valem kui kõigil teistel ridadel
- Esimene meetod on = vasakpoolne 2. reas ja = vasak + üles rida 3 kuni N
- Kui proovite kasutada sama valemit, saate vea #Value väärtusega = Kokku + arv
- 2. meetodil kasutatakse
=SUM(Up,Left)või=SUM(Previous Total,This Row Amount) - SUM ignoreerib teksti, nii et te ei saa viga VALUE
- 3. meetod kasutab laienevat vahemikku:
=SUM(B$2:B2) - Vahemike laiendamine on lahe, kuid aeglane
- Loe Exceli vormelikiirusel Charles Williamsi valgepaberit
- Kolmas meetod on probleem, kui kasutate klahve Ctrl + T ja lisate uusi ridu
- Excel ei saa aru, kuidas valemit kirjutada
- Lahendused vajavad teatud teadmisi tabelites struktureeritud viidete kohta
- 1. lahendus on aeglane
=SUM(INDEX((Qty),1):(@Qty)) - 2. lahendus on kõikuv
=SUM(OFFSET((@Total),-1,0),(@Qty)) - (@Qty) viitab selle rea kogusele
- (Kogus) viitab kõigile Koguse väärtustele
Video ärakiri
Õppige Exceli Podcasti jaoks, 2004. aasta jagu - Kokkujooksmine
Podcastin kogu seda raamatut. Tellimiseks klõpsake paremas ülanurgas valikut I.
Tere tulemast tagasi müstilise raku netisaate juurde. Olen Bill Jelen. Nüüd selle teema raamatus panustas mulle mu sõber Zach Parise. Rääkige Exceli tabelitest: Zach on maailma Exceli tabelite ekspert. Ta on kirjutanud raamatu Exceli tabelite kohta, kuid kõigepealt räägime kogusummade käitamisest mitte tabelites.
Nii et kui ma mõtlen jooksvate kogusummade peale, siis on jooksvate kogusummade tegemiseks kolm erinevat viisi ja see, kuidas ma alati alustasin, on esimeses reas, mida te lihtsalt ütlete, viige väärtus üle. Nii võrdne, mis on minust vasakul. Hästi, nii et see vorming siin on lihtsalt = B2. Need on kõik valemitekstid siin paremas nurgas, nii et näete, mida me kasutame, ja siis sealt edasi, see on lihtne väike valem, mis võrdub eelmise väärtusega, pluss praegune väärtus paremal ja kopeerige see alla , kuid teate nüüd, meil on see probleem, et see nõudis kahte erinevat valemit ja teate, et ideaalses olukorras on teil täpselt sama valem ja põhjus, miks meil peab olema esimene valem teises valemis, on et kui proovite lisada võrdse 7 pluss sõna kokku, on see väärtuse viga,aga lahe töötaja siin ei pea lihtsalt kasutama vasakut pluss üles, vaid kasutama eelmise väärtuse = (SUM) pluss selle rea kogust ja nägema, et mõni neist on tekstide ignoreerimiseks piisavalt kaugel. Täpselt nii, et võimaldab sama valemit. kogu tee alla.
Hästi, see oli siis, kui ma hakkasin Exceli kasutama, ma kasutasin seda ja siis avastasin laieneva vahemiku, laienev vahemik ütleb, et teeme L $ 2: L2 ja mis juhtub, see algab alati 2. reast, aga siis läheb see alla praegusele reale. Nii et kui vaatate, kuidas see kopeerimisel töötab, alustasime alati 2. rida, kuid me läheme alla praegusele reale ja see sai minu lemmikmeetodiks. Mulle meeldis, et oh, see on nii palju keerukam ja kui läheme Exceli suvanditesse, minge vahekaardile Valemid ja valige viite stiilis R1C1. Hea küll, R1C1, kõik need valemid on kogu tee täpselt ühesugused. Ma ei tea, kas saate R1C1-st aru, on lihtsalt hea teada, et meil on kogu aeg identsed R1C1-valemid.
Läheme tagasi. Nii et see meetod on siin see meetod, mis mulle meeldis, kuni Inglismaalt pärit Exceli MBP Charles Williams, kellel on hämmastav paber valemikiiruse, Exceli valemi kiiruse kohta, selle meetodi täielikult lahti tegi. Oletame, et see meetod on teil 10 000 rida. Iga valem vaatleb kahte viidet. Nii et vaatate 20 000 viidet, aga see, see vaatab kahte, see vaatab kolme, see vaatab nelja, see vaatab viit, viimane vaatab 10 000 viidet ja see on kohutavalt aeglasem ja nii lõpetasin selle meetodi kasutamise.
Seejärel loen Zacki Kevin Jonesi raamatust Exceli tabelite kohta ja avastan selle meetodiga veel ühe probleemi. Nii et üks tabelites pakutavatest kasulikest omadustest on "automaatse vormindamise ja valemihoolduse ridade lisamine, eemaldamine, sorteerimine ja filtreerimine". Olgu, see on tsitaat tema raamatust. Ja tabelisse rea lisamiseks minge lihtsalt tabeli viimasesse lahtrisse ja vajutage tabulaatorit. Nii et kõik töötab siin. Oleme 70-aastased, see on vinge, ja siis A104 ja ma panen siia 100. Hästi, nii et 70 peaks muutuma 170-ks ja see muutub, kuid see 70 ei oleks pidanud üldse muutuma. Hea 68 + 2 pole 170. Ma teen seda uuesti. A 104 ja viimane veel sada on õige. Need kaks pole õiged. Hästi, nii et meil on imelik olukord, et kui saKasutades seda valemit ja teisendate tabeliks, alustate ridade lisamist, jooksev kogusumma ei toimi. Kui halb see on?
Hästi, nii et Zack pakub kahte ümberkäimist ja mõlemad vajavad natuke teadmisi struktuuriviidete toimimise kohta. Meil on siin lihtsalt uus veerg ja kui ma sooviksin teha kogust, siis võrdne kogus, eks, nii et = (@ Kogus) ütleb selles reas koguse. Oh lahe, noh, seal on teist tüüpi viide, kus me kasutame Qty ilma @. Vaata seda. Nii et = SUM (INDEKS ((Kogus), 1: (@ Kogus)) tähendab kõiki koguseid ja me ütleme, et tahame summat esimesest kogusest, nii et (INDEKS ((Kogus), 1 ütleb esimene väärtus siin, kuni praeguse rea koguse ja see kasutab tõeliselt spetsiaalset indeksi versiooni, kui indeksile järgneb koolon, muutub see tegelikult lahtriviideks. Hästi, see lahendus rikub kahjuks Charles Williamsi reeglit alates, mepeame vaatama kõiki viiteid, ja nii kui saate 10 000 rida, läheb see tõesti väga aeglaselt.
Zachil on veel üks lahendus, mis ei riku Charles Williamsi probleemi, kuid ta kasutab kardetud OFFSETi. OFFSET on kõikuv funktsioon, nii et iga kord, kui midagi arvutate, arvutab OFFSET ümber ja kõik, mis OFFSETist läheb, arvutab ümber. See on lihtsalt suurepärane võimalus oma valemid täielikult, täielikult kokku keerata, ja mida see teeb, ütleme nii, et võtame sellest reast koguarvu, liikudes ühe rea võrra ülespoole veerge, nii et see, mida see teeb, ütleb: haarake eelmise rea kogusumma ja lisame sellele selle rea koguse. Olgu, nüüd vaatame nüüd iga kord kahte viidet, kuid kahjuks tutvustab OFFSET kõikuvaid funktsioone.
Noh, teil on see olemas, rohkem kui olete kunagi tahtnud Running Totalsi kohta teada saada. Ma arvan, et minu lõplik arvamus on siin selle meetodi kasutamine, sest see näeb välja ainult kaks. Sama valem lõpuni ja teie struktureeritud tabeliviited toimivad.
Selle uurimise ja 39 muu tõeliselt hea näpunäite saamiseks vaadake seda raamatut XL, 40 kõigi aegade suurimat Exceli nõuannet.
Selle episoodi kokkuvõtteks rääkisime kolmest võimalusest jooksvate kogusummade tegemiseks. Esimesel meetodil on erinev valem, 2. rida, kui kõigil teistel ridadel. See on võrdne vasakule 2. reas ja seejärel võrdne vasak pluss ülespoole ridades 3 kuni N, kuid kui proovite ja kasutate lihtsalt sama valemit, võrdne vasak pluss üles, ülalt alla, kuidas saate vea #Value . Nii = SUM (üles, vasakule), mis on eelmine kogusumma, pluss see suurepäraselt toimiv teekaart, pole ühtegi väärtusviga ja siis laienev vahemik, mida ma armastan. Need on lahedad, aga kuni ma lugesin Charles Williamsi valget raamatut Exceli kiiruse vormis. Siis hakkasin neid laienevaid viiteid vihkama. Samuti on probleem, kui kasutate CTRL T-d ja lisate uusi ridu. Excel ei saa aru, kuidas seda valemit laiendada, kuidas uusi ridu lisada. Mulle meeldib, et see näpunäide läheb tabeli kõige viimasesse lahtrisse ja vajuta Tab,see lisab uue rea ja siis rääkisime mõnest struktureeritud viitest, kus kasutame selles reas kogust ja seejärel kõiki koguseid. = SUMMA (NÕUETE ((@ kokku), - 1,00, (@ Kogus)).
Olgu, ma tahan tänada Zachi selle näpunäite panustamise eest. Tahan teid tänada, et peatusite. Järgmisel korral näeme järgmise võrguülekande jaoks.
Laadige fail alla
Laadige näidisfail alla siit: Podcast2004.xlsx
