Šis epizodas parodo tris būdus, kaip padaryti bendrą sumą.
Skaitinių verčių sąrašo einamoji suma yra pirmosios eilutės iki einamosios sumos eilutės verčių suma. Dažniausiai naudojamos einamosios sumos yra čekių žurnale arba apskaitos lape. Yra daugybė būdų sukurti iš viso du iš jų aprašyti toliau.
Paprasčiausias būdas yra kiekvienoje eilutėje pridėti sumą iš aukščiau esančios eilutės prie eilutės vertės. Taigi pirmoji 2 eilutės formulė yra:
=SUM(D1,C2)
Priežastis, kodėl mes naudojame funkciją SUM, yra ta, kad pirmoje eilutėje mes žiūrime į antraštę aukščiau esančioje eilutėje. Jei naudosime paprastesnę, intuityvesnę formulę, =D1+C2tada bus sukurta klaida, nes antraštės vertė yra tekstas, palyginti su skaitine. Stebuklinga yra tai, kad funkcija SUM nepaiso teksto reikšmių, kurios pridedamos kaip nulinės vertės. Kai formulė nukopijuojama žemyn į visas eilutes, kuriose reikalinga einamoji suma, langelių nuorodos atitinkamai koreguojamos:
Kita technika taip pat naudoja funkciją SUM, tačiau kiekviena formulė susumuoja visas vertes nuo pirmos eilutės iki eilutės, rodančios einamąjį sumą. Šiuo atveju mes naudojame dolerio ženklą ($), kad pirmasis nuorodos langelis taptų absoliučia nuoroda, o tai reiškia, kad jis nėra koreguojamas kopijuojant:
Eilių rūšiavimas ir ištrynimas neturi įtakos abiem metodams, tačiau įterpiant eilutes, formulę reikia nukopijuoti į naujas eilutes.
„Excel 2007“ pristatė lentelę, kuri yra programos „Excel 2003“ pakartotinis diegimas. Lentelėse buvo pristatyta daugybė labai naudingų duomenų lentelių funkcijų, tokių kaip formatavimas, rūšiavimas ir filtravimas. Pristačius lenteles, mums taip pat buvo suteiktas naujas būdas nurodyti lentelės dalis. Šis naujas nuorodų stilius vadinamas struktūrine nuoroda.
Norėdami paversti pirmiau pateiktą pavyzdį į lentelę, mes pasirenkame duomenis, kuriuos norime įtraukti į lentelę, ir paspauskite Ctrl + T. Parodę raginimą, kuriame prašoma patvirtinti lentelės diapazoną ir ar esama antraščių, „Excel“ konvertuoja duomenis į suformatuotą lentelę:
Atkreipkite dėmesį, kad anksčiau įvestos formulės lieka tos pačios.
Viena iš naudingų lentelių funkcijų yra automatinis formatavimas ir formulių priežiūra, kai eilutės pridedamos, pašalinamos, rūšiuojamos ir filtruojamos. Ypač daug dėmesio skirsime formulės palaikymui, kuris gali būti problemiškas. Kad „Lentelės“ veiktų, kol jomis manipuliuojama, „Excel“ naudoja apskaičiuotus stulpelius, kurie yra stulpeliai su formulėmis, pavyzdžiui, D stulpelis aukščiau pateiktame pavyzdyje. Kai įterpiamos naujos eilutės į apačią, „Excel“ automatiškai užpildo naujas eilutes to stulpelio „numatytąja“ formule. Pirmiau pateikto pavyzdžio problema yra ta, kad „Excel“ painiojama su standartinėmis formulėmis ir ne visada jas tvarko tinkamai. Tai paaiškėja, kai lentelės apačioje pridedamos naujos eilutės (lentelėje pasirinkus apatinį dešinį langelį ir paspaudus TAB):
Šis trūkumas pašalinamas naudojant naujesnę struktūrizuotą nuorodą. Struktūruota nuoroda nereikalauja nurodyti konkrečių langelių, naudojant A1 arba R1C1 nuorodų stilių, o stulpelių pavadinimus ir kitus raktinius žodžius naudoja lentelės dalims identifikuoti ir nurodyti. Pvz., Norėdami sukurti tą pačią pirmiau naudojamą veikiančią bendrą formulę, bet naudodami struktūrinę nuorodą, turime:
=SUM(INDEX((Sales),1):(@Sales))
Šiame pavyzdyje mes turime nuorodą į stulpelio pavadinimą „Pardavimai“ kartu su at ženklu (@), nurodydami eilutę stulpelyje, kuriame yra formulė, kuri taip pat žinoma kaip dabartinė eilutė.
Norėdami įgyvendinti pirmąjį pirmiau pateiktą pavyzdį, kai prie dabartinės eilutės pardavimo sumos pridėjome ankstesnės eilutės bendrą einamąją vertę, galite naudoti funkciją OFFSET:
=SUM(OFFSET((@(Running Total)),-1,0),(@Sales))
Jei sumos, naudojamos apskaičiuojant einamąją sumą, yra dviejuose stulpeliuose, pavyzdžiui, vienas skirtas „Debetams“ ir vienas „Kreditams“, tada formulė yra:
=SUM(INDEX( (Credit),1):(@Credit))- SUM(INDEX( (Debit),1):(@Debit))
Čia mes naudojame funkciją INDEX, kad surastume pirmosios eilutės kreditų ir debeto langelius, ir susumuojame visą stulpelį iki dabartinės eilutės reikšmių ir jas įtraukiame. Einamoji suma yra visų kreditų suma iki dabartinės eilutės imtinai, atėmus visų debetų iki dabartinės eilutės imtinai sumą.
Norėdami gauti daugiau informacijos apie struktūrizuotas nuorodas ir apie lenteles apskritai, rekomenduojame Zacko Barresse ir Kevino Joneso knygą „Excel lentelės: pilnas sąrašas ir lentelių kūrimo, naudojimo ir automatizavimo vadovas“.
Kai paprašiau skaitytojų balsuoti už mėgstamus patarimus, lentelės buvo populiarios. Ačiū Peteriui Albertui, Snorre Eikelandui, Nancy Federice, Colinui Michaelui, Jamesui E. Moede'ui, Keyurui Pateliui ir Paului Petonui už šios funkcijos pasiūlymą. Peteris Albertas parašė „Readable References“ premijos patarimą. Zackas Barresse'as parašė „Bėgančių sumų“ premijos patarimą. Keturi skaitytojai pasiūlė naudoti OFFSET, kad sukurtų besiplečiančius dinaminių diagramų diapazonus: Charley Baak, Don Knowles, Francis Logan ir Cecelia Rieb. Lentelės dabar dažniausiai daro tą patį.
Žiūrėti video
- Šiame epizode parodomi trys būdai, kaip atlikti bėgimo sumas
- Pirmasis metodas turi 2 eilutėje kitokią formulę nei visos kitos eilutės
- Pirmasis metodas yra = kairė 2 eilutėje ir = kairė + aukštyn 3 - N eilėse
- Jei bandysite naudoti tą pačią formulę, gausite klaidą #Value su = Iš viso + skaičius
- 2 metodas naudoja
=SUM(Up,Left)arba=SUM(Previous Total,This Row Amount) - SUM nepaiso teksto, kad negautumėte VALUE klaidos
- 3 metodas naudoja besiplečiantį diapazoną:
=SUM(B$2:B2) - Plėsti diapazonai yra šaunūs, bet lėti
- Perskaitykite Charleso Williamso baltraštį apie „Excel Formula Speed“
- Trečiasis metodas yra problema, kai naudojate „Ctrl“ + T ir pridedate naujų eilučių
- „Excel“ negali suprasti, kaip parašyti formulę
- Norėdami išspręsti problemas, reikia šiek tiek išmanyti struktūrizuotas nuorodas lentelėse
- 1 sprendimas yra lėtas
=SUM(INDEX((Qty),1):(@Qty)) - 2 sprendimas yra nepastovus
=SUM(OFFSET((@Total),-1,0),(@Qty)) - (@Qty) nurodo kiekį šioje eilutėje
- (Kiekis) reiškia visas kiekio reikšmes
Vaizdo įrašo nuorašas
Sužinokite apie „Podcast“ skirtą „Excel“ programą, 2004 m. Serija
Aš transliuosiu visą šią knygą. Norėdami užsiprenumeruoti, viršutiniame dešiniajame kampe spustelėkite „I“.
Sveiki sugrįžę į mistinės kameros transliaciją. Aš esu Billas Jelenas. Dabar šia tema knygoje prisidėjo mano draugas Zachas Parise'as. Kalbant apie „Excel“ lenteles, Zachas yra „Excel“ lentelių ekspertas pasaulyje. Jis parašė knygą apie „Excel“ lenteles, bet pirmiausia pakalbėkime apie sumų skaičiavimą ne lentelėse.
Taigi, kai galvoju apie bėgimo sumas, yra trys skirtingi bėgimo sumų atlikimo būdai, ir tai, kaip aš visada pradėjau, yra pirmoje eilutėje, kurią jūs tiesiog sakote, perkelkite vertę. Taigi lygu, kas yra kairėje nuo manęs. Gerai, todėl šis formatas yra tiesiog = B2. Tai visas formulės tekstas dešiniajame kampe, kad pamatytumėte, ką naudojame, o tada iš ten žemyn - tai paprasta maža formulė, lygi ankstesnei vertei, pridėjus dabartinę vertę dešinėje ir nukopijuojant ją žemyn , bet jūs žinote dabar, mes turime šią problemą, kad tam reikėjo dviejų skirtingų formulių, ir jūs žinote, kad esant tobulai situacijai, jūs turite tą pačią formulę iki galo, ir priežastis, kodėl pirmoje eilutėje turime turėti kitą formulę, yra kad kai bandysite pridėti lygų 7 ir žodį „total“, tai reikšmės klaida,bet šaunus darbuotojas čia yra ne tik naudoti kairįjį pliusą aukštyn, bet ir naudoti = (SUM) ankstesnės vertės ir kiekį šioje eilutėje, o kai kurių yra pakankamai toli, kad nepaisytumėte tekstų. Teisingai, kad leidžiama ta pati formulė. iki pat galo.
Gerai, kad tai buvo tada, kai pradėjau naudoti „Excel“, aš jį naudojau ir tada atradau besiplečiantį diapazoną, besiplečiantis diapazonas sako, kad mes darysime L $ 2: L2 ir kas atsitinka, tai visada prasideda nuo 2 eilutės, bet tada einama žemyn į dabartinę eilę. Taigi, kai pažiūri, kaip tai veikia, kai jis nukopijuojamas, mes visada pradėjome 2 eilutę, bet mes einame žemyn į dabartinę eilutę ir tai tapo mano mėgstamiausiu metodu. Aš buvau panašus į tai, kad tai yra daug sudėtingiau, o kai mes einame į „Excel“ parinktis, eikite į skirtuką „Formulės“ ir „Reference Style“ pasirinkite R1C1. Gerai, R1C1, visos šios formulės yra visiškai vienodos. Nežinau, ar suprantate R1C1, tiesiog gera žinoti, kad mes turime vienodas R1C1 formules iki pat galo.
Eikime atgal. Taigi šis metodas yra toks, koks man patiko, kol Charlesas Williamsas, „Excel MBP“ iš Anglijos, turintis nuostabų dokumentą apie formulės greitį, „Excel“ formulės greitį, visiškai atmetė šį metodą. Tarkime, kad šiuo metodu turite 10 000 eilučių, kiekvienoje formulėje yra dvi nuorodos. Taigi jūs žiūrite į 20 000 nuorodų, bet ši, ši - dviejų, tai - trijų, tai - keturių, tai - penkių, o paskutinė - 10 000, ir siaubingai lėtesnė ir todėl aš nustojau naudoti šį metodą.
Tada aš skaitau Zacką Kevino Joneso knygoje apie „Excel“ lenteles ir atrandu dar vieną šio metodo problemą. Taigi viena iš naudingų funkcijų, kurias siūlo lentelės, yra „automatinio formatavimo ir formulių priežiūros eilučių įtraukimas, pašalinimas, rūšiavimas ir filtravimas“. Gerai, tai citata iš jo knygos. Norėdami pridėti eilutę prie lentelės, tiesiog eikite į paskutinį lentelės langelį ir paspauskite skirtuką. Taigi viskas čia veikia. Mes iki 70 metų, kad yra nuostabu, tada A104 ir aš įdėsiu 100 čia. Gerai, kad 70 turėtų pasikeisti į 170, ir tai keičiasi, bet šis 70 neturėjo visiškai pasikeisti. Gerai, 68 + 2 nėra 170. Aš tai padarysiu dar kartą. 104 ir įdėti dar šimtą į paskutinį yra teisus. Šie du nėra teisūs. Gerai, todėl turime keistą situaciją, kad jei tupakartotinai naudodamas šią formulę ir konvertuodamas į lentelę, pradedi pridėti eilučių, einamoji suma neveikia. Ar tai blogai?
Gerai, todėl Zackas siūlo du darbo variantus ir jiems abiem reikia šiek tiek žinių apie tai, kaip veikia struktūros nuorodos. Mes tiesiog turėsime naują stulpelį čia ir, jei norėčiau padaryti kiekį, lygų kiekį, teisingai, kad = (@ Kiekis) šioje eilutėje būtų nurodytas kiekis. O šaunu, gerai, yra dar viena rūšis, kur „Qty“ naudojame be „@“. Pažiūrėk. Taigi = SUM (INDEKSAS ((Kiekis), 1: (@ Kiekis)) reiškia visus kiekius ir sakysime, kad norime SUMINTI nuo pirmojo kiekio, taigi (INDEKSAS ((Kiekis), 1 sako čia yra pirmoji reikšmė iki dabartinio eilučių kiekio, o tam naudojama tikrai speciali indekso versija, kai po indekso yra dvitaškis, jis iš tikrųjų pasikeičia į langelio nuorodą. Gerai, kad šis sprendimas, deja, pažeidžia Charleso Williamso taisyklę iš mesturėsime peržiūrėti kiekvieną nuorodą, taigi, kai gausite 10 000 eilučių, tai eis tikrai labai lėtai.
Zachas turi dar vieną problemą, kuri nepažeidžia Charleso Williamso problemos, tačiau naudojasi bijomu OFFSET. OFFSET yra nepastovi funkcija, todėl kiekvieną kartą, kai ką nors apskaičiuosite, OFFSET perskaičiuos ir viskas, kas bus nuo OFFSET, perskaičiuos. Tai tiesiog puikus būdas visiškai, visiškai susukti savo formules, ir ką tai daro, sakoma, mes imame bendrą sumą iš šios eilutės, einame viena eile aukštyn, virš nulio stulpelių, ir tai, ką tai daro, sako: paimkite iš ankstesnės eilutės bendrą sumą, tada mes pridedame prie šios eilutės kiekį. Gerai, taigi, dabar viskas kaskart ieškoma po dvi nuorodas, deja, OFFSET pristato nepastovias funkcijas.
Na, jūs turite tai, daugiau nei jūs kada nors norėjote sužinoti apie „Running Totals“. Manau, kad mano galutinė nuomonė yra naudoti šį metodą, nes jis atrodo tik du. Ta pati formulė iki galo ir jūsų struktūrinės lentelės nuorodos veiks.
Norėdami sužinoti šį tyrimą ir dar 39 tikrai gerus patarimus, peržiūrėkite šią knygą XL - 40 didžiausių visų laikų „Excel“ patarimų.
Apibendrindami šį epizodą, mes kalbėjome apie tris būdus, kaip padaryti bendrą sumą. Pirmasis metodas turi kitą formulę, 2 eilutę, nei visos kitos eilutės. Jis yra lygus kairėje 2 eilutėje ir tada lygus kairėje plius aukštyn 3 - N eilėse, bet jei bandysite ir tiesiog naudosite tą pačią formulę, lygus kairysis plius aukštyn, iki galo, kaip gausite #Value klaidą . Taigi = SUM (aukštyn, kairėn), kuris yra ankstesnis bendras, taip pat šis puikiai veikiantis planas, be vertės klaidų ir tada besiplečiantis diapazonas, kurį aš mėgstu. Jie šaunūs, bet kol neperskaičiau Charleso Williamso baltosios knygos apie „Excel“ greičio formą. Tada pradėjau nekęsti šių besiplečiančių nuorodų. Taip pat kyla problemų, kai naudojate CTRL T ir pridedate naujų eilučių. „Excel“ negali suprasti, kaip išplėsti šią formulę, kaip pridėti naujų eilučių. Man patinka, kad šis patarimas eina į paskutinę lentelės langelį ir paspauskite tabuliavimo klavišą,tai pridės naują eilutę, o tada mes kalbėjome apie kai kurias struktūrines nuorodas, kur šioje eilutėje naudojame kiekį ir visus dydžius. = SUMA (OFFSET ((Iš viso), - 1,00, (@ Kiekis)).
Gerai, noriu padėkoti Zachui už indėlį. Noriu padėkoti, kad užsukote. Pamatysime kitą kartą kitai internetinei transliacijai iš.
Atsisiųsti failą
Parsisiųskite failo pavyzdį čia: Podcast2004.xlsx
