Táto epizóda ukazuje tri spôsoby, ako prevádzkovať súčty.
Priebežný súčet je pre zoznam číselných hodnôt súčtom hodnôt od prvého riadku po riadok priebežného súčtu. Bežné použitie priebežného súčtu je v registri šekovej knižky alebo v účtovnom liste. Existuje mnoho spôsobov, ako vytvoriť bežný súčet - dva z nich sú popísané nižšie.
Najjednoduchšou technikou je spočítať na každom riadku priebežný súčet z riadku vyššie k hodnote v riadku. Prvý vzorec v riadku 2 je teda:
=SUM(D1,C2)
Dôvod, prečo používame funkciu SUM, je ten, že v prvom riadku sa pozeráme na hlavičku v riadku vyššie. Ak použijeme jednoduchší a intuitívnejší vzorec, =D1+C2vygeneruje sa chyba, pretože hodnota hlavičky je text verzus numerická. Kúzlo je v tom, že funkcia SUM ignoruje textové hodnoty, ktoré sa pridávajú ako nulové hodnoty. Keď sa vzorec skopíruje do všetkých riadkov, v ktorých je požadovaný priebežný súčet, odkazy na bunky sa zodpovedajúcim spôsobom upravia:
Druhá technika tiež používa funkciu SUMA, ale každý vzorec sčíta všetky hodnoty od prvého riadku po riadok so zobrazením priebežného súčtu. V takom prípade použijeme znak dolára ($), aby sa z prvej bunky v referencii stala absolútna referencia, čo znamená, že sa pri kopírovaní neupraví:
Obidve techniky nie sú ovplyvnené triedením a mazaním riadkov, ale pri vkladaní riadkov musí byť vzorec skopírovaný do nových riadkov.
Program Excel 2007 predstavil tabuľku, ktorá je opätovnou implementáciou zoznamu v programe Excel 2003. Tabuľky predstavili množstvo veľmi užitočných funkcií pre údajové tabuľky, ako je formátovanie, triedenie a filtrovanie. Zavedením tabuliek sa nám tiež poskytol nový spôsob odkazovania na časti tabuľky. Tento nový štýl odkazovania sa nazýva štruktúrované odkazovanie.
Ak chcete previesť vyššie uvedený príklad na tabuľku, vyberieme údaje, ktoré chceme do tabuľky zahrnúť, a stlačíme Ctrl + T. Po zobrazení výzvy s výzvou na potvrdenie rozsahu tabuľky a toho, či existujú alebo neexistujú hlavičky, Excel prevedie údaje do naformátovanej tabuľky:
Upozorňujeme, že vzorce, ktoré sme zadali skôr, zostávajú rovnaké.
Jednou z užitočných funkcií, ktoré tabuľky ponúkajú, je automatické formátovanie a údržba vzorcov pri pridávaní, odstraňovaní, triedení a filtrovaní riadkov. Konkrétne sa zameriame na údržbu vzorcov, ktorá môže byť problematická. Na zabezpečenie toho, aby tabuľky počas manipulácie pracovali, Excel využíva vypočítané stĺpce, ktoré sú stĺpcami so vzorcami, ako je napríklad stĺpec D vo vyššie uvedenom príklade. Keď sa do spodnej časti pridajú nové riadky, Excel automaticky vyplní nové riadky „predvoleným“ vzorcom pre daný stĺpec. Problém vyššie uvedeného príkladu je, že program Excel sa zamieňa so štandardnými vzorcami a nie vždy s nimi pracuje správne. To je zrejmé, keď sa do dolnej časti tabuľky pridajú nové riadky (výberom pravej dolnej bunky v tabuľke a stlačením klávesu TAB):
Tento nedostatok je vyriešený použitím novšieho štruktúrovaného odkazovania. Štruktúrované odkazovanie eliminuje potrebu odkazovať na konkrétne bunky pomocou referenčného štýlu A1 alebo R1C1 a namiesto toho používa na identifikáciu a odkazovanie na časti tabuľky názvy stĺpcov a ďalšie kľúčové slová. Napríklad na vytvorenie rovnakého vzorca bežného súčtu použitého vyššie, ale pomocou štruktúrovaného odkazovania máme:
=SUM(INDEX((Sales),1):(@Sales))
V tomto príklade máme odkaz na názov stĺpca „Predaj“ spolu s zavináčom (@) na odkaz na riadok v stĺpci, v ktorom je umiestnený vzorec, ktorý je tiež známy ako aktuálny riadok.
Ak chcete implementovať prvý príklad vyššie, kde sme pridali priebežnú celkovú hodnotu v predchádzajúcom riadku k čiastke predaja v aktuálnom riadku, môžete použiť funkciu OFFSET:
=SUM(OFFSET((@(Running Total)),-1,0),(@Sales))
Ak sú sumy použité na výpočet priebežného súčtu v dvoch stĺpcoch, napríklad jeden pre „debety“ a jeden pre „kredity“, potom platí vzorec:
=SUM(INDEX( (Credit),1):(@Credit))- SUM(INDEX( (Debit),1):(@Debit))
Tu používame funkciu INDEX na vyhľadanie kreditných a debetných buniek prvého riadku a sčítanie celého stĺpca až po hodnoty aktuálneho riadku vrátane. Priebežný súčet je súčtom všetkých kreditov do a vrátane aktuálneho riadku mínus súčet všetkých debetov do a vrátane aktuálneho riadku.
Pre viac informácií najmä o štruktúrovaných referenciách a tabuľkách všeobecne odporúčame knihu Excel Tables: a Complete Guide for Creating, Use and Automating Lists and Tables od Zacka Barresse a Kevina Jonesa.
Keď som požiadal čitateľov, aby hlasovali za ich obľúbené tipy, tabuľky boli populárne. Ďakujem Peterovi Albertovi, Snorre Eikelandovi, Nancy Federice, Colinovi Michaelovi, Jamesovi E. Moedeovi, Keyurovi Patelovi a Paulovi Petonovi za navrhnutie tejto funkcie. Peter Albert napísal bonusový tip Čitateľné referencie. Zack Barresse napísal bonusový tip Running Totals. Štyria čitatelia navrhli použiť program OFFSET na vytvorenie rozširujúcich sa rozsahov pre dynamické grafy: Charley Baak, Don Knowles, Francis Logan a Cecelia Rieb. Tabuľky teraz vo väčšine prípadov robia to isté.
Pozeraj video
- Táto epizóda ukazuje tri spôsoby, ako prevádzkovať súčty
- Prvá metóda má v riadku 2 iný vzorec ako všetky ostatné riadky
- Prvá metóda je = vľavo v riadku 2 a = vľavo + hore v riadkoch 3 až N
- Ak sa pokúsite použiť rovnaký vzorec, zobrazí sa chyba #hodnota s hodnotou = spolu + číslo
- Metóda 2 používa
=SUM(Up,Left)alebo=SUM(Previous Total,This Row Amount) - Funkcia SUM ignoruje text, aby sa vám nezobrazila chyba HODNOTY
- Metóda 3 používa rozširujúci sa rozsah:
=SUM(B$2:B2) - Rozširujúce sa rozsahy sú v pohode, ale sú pomalé
- Prečítajte si Whitepaper Charlesa Williamsa o Excel Formula Speed
- Tretia metóda predstavuje problém, keď použijete kombináciu klávesov Ctrl + T a pridáte nové riadky
- Excel nemôže prísť na to, ako napísať vzorec
- Riešenia vyžadujú určité znalosti štruktúrovaného odkazovania v tabuľkách
- Riešenie 1 je pomalé
=SUM(INDEX((Qty),1):(@Qty)) - Riešením 2 je volatilné
=SUM(OFFSET((@Total),-1,0),(@Qty)) - (@Qty) označuje množstvo v tomto riadku
- (Množstvo) sa vzťahuje na všetky hodnoty množstva
Prepis videa
Naučte sa Excel pre Podcast, Epizóda 2004 - Priebežné súčty
Budem podcastovať celú túto knihu. Kliknutím na znak I v pravom hornom rohu sa prihláste na odber.
Ahoj, vitaj späť na mystickom mobilnom netcaste. Som Bill Jelen. Teraz k tejto téme v knihe mi prispel môj priateľ Zach Parise. Keď už hovoríme o tabuľkách Excel, Zach je svetovým expertom na tabuľky Excel. Napísal knihu o tabuľkách programu Excel, najskôr si však povieme niečo o priebežných súčtoch, nie o tabuľkách.
Takže keď premýšľam o spustení súčtov, existujú tri rôzne spôsoby, ako urobiť súčty súčtov, a spôsob, ktorým som vždy začínal, je v prvom riadku, ktorý jednoducho poviete, prinesie hodnotu znova. Takže rovnaké, čo je odo mňa vľavo. Dobre, takže tento formát je tu iba = B2. Toto všetko je text vzorca v pravom rohu, takže vidíte, čo používame, a potom odtiaľto dole je to jednoduchý malý vzorec, ktorý sa rovná predchádzajúcej hodnote plus aktuálna hodnota vpravo a skopírujte to dole , ale teraz viete, máme tento problém, že vyžadoval dva rôzne vzorce a viete, že v perfektnej situácii máte úplne rovnaký vzorec až dole, a dôvod, prečo v prvom riadku musíme mať iný vzorec, je že keď skúsite pridať rovných 7 plus slovo celkom, je to chyba hodnoty,ale super pracovník tu nie je len použiť left plus, ale aj použiť = (SUM) predchádzajúcej hodnoty plus množstvo v tomto riadku a niektoré sú dosť ďaleko na to, aby ignorovali texty. Správne, takže umožňuje rovnaký vzorec. úplne dole.
Dobre, takže keď som začínal používať Excel, používal som to a potom som objavil rozširujúci sa rozsah, rozširujúci sa rozsah hovorí, že urobíme L $ 2: L2 a čo sa stane, to vždy začína na riadku 2, ale potom to ide dole do aktuálneho riadku. Takže keď sa pozriete na to, ako to funguje, keď sa to skopíruje, vždy sme začali riadok 2, ale pôjdeme dole do aktuálneho riadku a tento sa stal mojou obľúbenou metódou. Bol som rád, ach, toto je oveľa sofistikovanejšie a keď ideme do Možnosti programu Excel, choďte na kartu Vzorce a vyberte R1C1 v Referenčnom štýle. Dobre, R1C1, všetky tieto vzorce sú úplne rovnaké úplne dole. Neviem, či rozumiete R1C1, je len dobré vedieť, že máme rovnaké vzorce R1C1 úplne dole.
Poďme späť. Takže táto metóda je tu metódou, ktorá sa mi páčila, až kým Charles Williams, Excel MBP z Anglicka, ktorý má úžasný dokument o rýchlosti vzorcov, rýchlosti vzorcov Excel, túto metódu úplne neodhalil. Táto metóda, povedzme, že máte 10 000 riadkov, sa každý vzorec pozerá na dve referencie. Pozeráte sa teda na 20 000 referencií, ale tento, tento sa pozerá na dva, tento sa pozerá na tri, tento sa pozerá na štyri, tento sa pozerá na päť a posledný pozerá na 10 000 referencií, a je to strašne pomalšie a tak som prestal používať túto metódu.
Potom pokračujem v čítaní Zacka v knihe Kevina Jonesa o tabuľkách Excel a objavím ešte ďalší problém s touto metódou. Jednou z užitočných funkcií, ktoré tabuľky ponúkajú, je teda „automatické formátovanie a riadky údržby vzorcov sa pridávajú, odoberajú, triedia a filtrujú“. Dobre, to je citát z jeho knihy. Ak chcete do tabuľky pridať riadok, prejdite do úplne poslednej bunky tabuľky a stlačte kláves Tab. Všetko tu teda funguje. Sme dole na 70, to je úžasné, a potom A104 a ja sem vložím stovku. Dobre, takže 70 by sa malo zmeniť na 170 a robí to, ale táto 70 sa nemala zmeniť vôbec. Alright 68 + 2 nie je 170. Urobím to znova. 104 a dať ďalšiu stovku do poslednej má pravdu. Títo dvaja nemajú pravdu. Dobre, takže máme divnú situáciu, že akre pomocou tohto vzorca a prevediete na tabuľku začnete pridávať riadky, priebežný súčet nebude fungovať. Aké zlé je to?
Dobre, takže Zack ponúka dve riešenia a obe vyžadujú trochu vedomostí o tom, ako fungujú odkazy na štruktúru. Len tu budeme mať nový stĺpec a ak by som chcel urobiť množstvo, rovnaké množstvo, správne, takže = (@ množstvo) hovorí v tomto riadku množstvo. Och, v pohode, existuje ďalší druh odkazu, kde používame množstvo bez znaku @. Pozri na toto. Takže = SUMA (INDEX ((množstvo), 1: (@ množstvo)) znamená všetky veličiny a my si povieme, že chceme SUMA od prvého množstva, takže (INDEX ((množstvo), 1 hovorí prvá hodnota tu, až po množstvo aktuálneho riadku, a toto používa skutočne špeciálnu verziu indexu, keď za indexom nasleduje dvojbodka, v skutočnosti sa zmení na odkaz na bunku. V poriadku, toto riešenie bohužiaľ porušuje pravidlo Charlesa Williamsa z, my 'Budem sa musieť pozrieť na každú jednu referenciu, a tak keď získate 10 000 riadkov, bude to ísť naozaj, ale pomaly.
Zach má ďalšie riešenie, ktoré neporušuje problém Charlesa Williamsa, ale využíva obávaný OFFSET. OFFSET je volatilná funkcia, takže zakaždým, keď niečo vypočítate, bude sa OFFSET prepočítavať a všetko, čo sa bude počítať od prepočtu OFFSETU. Je to skvelý spôsob, ako úplne a úplne zmanipulovať svoje vzorce. Čo hovorí, to robí, berieme súčet z tohto riadku, ideme hore o jeden riadok, cez nulové stĺpce, a teda to, čo robíme, je: uchopte súčet z predchádzajúceho riadku a potom k nemu pripočítame množstvo z tohto riadku. Dobre, takže teraz sa vždy pozeráme na dve referencie, ale bohužiaľ OFSET zavádza volatilné funkcie.
No, tu to máte, viac ako ste kedy chceli vedieť o Running Totals. Myslím, že môj posledný názor je, že použijem túto metódu, pretože to vyzerá len ako dvojka. Rovnaký vzorec až do konca a vaše odkazy na štruktúrovanú tabuľku budú fungovať.
Pre tento prieskum a 39 ďalších skutočne dobrých tipov si pozrite túto knihu XL, 40 najlepších tipov na Excel všetkých čias.
Rekapituláciu tejto epizódy sme hovorili o troch spôsoboch, ako prevádzkovať súčty. Prvá metóda má iný vzorec, riadok 2, ako všetky ostatné riadky. V ľavom riadku 2 je rovnaký a potom v ľavom plus v riadkoch 3 až N rovnaký, ale ak vyskúšate a použijete ten istý vzorec, rovnaký vľavo plus hore, úplne nadol, ako sa zobrazí chyba #hodnota . Takže = SUM (hore, vľavo), čo je predchádzajúci súčet, plus tento plán, ktorý funguje skvele, žiadne chyby hodnoty a potom sa rozširujúci rozsah, ktorý používam k láske. Sú v pohode, ale kým som si neprečítal bielu knihu Charlesa Williamsa o excelovej rýchlosti. Potom som začal tieto rozširujúce sa referencie nenávidieť. Tiež to robí problém, keď použijete CTRL T a pridáte nové riadky. Excel nemôže prísť na to, ako rozšíriť tento vzorec a ako pridať nové riadky. Páči sa mi tento tip, choďte do úplne poslednej bunky v tabuľke a stlačte kláves Tab,ktorý pridá nový riadok a potom sme hovorili o nejakom štruktúrovanom odkazovaní, kde v tomto riadku používame kvantitu a potom všetky kvantity. = SUMA (OFFSET ((@ celkom), - 1,00, (@ množstvo)).
Dobre, chcem sa poďakovať Zachovi za prispenie týmto tipom. Chcem sa vám poďakovať, že ste sa zastavili. Uvidíme sa nabudúce pri ďalšom netcaste z.
Stiahnuť súbor
Stiahnutie vzorového súboru nájdete tu: Podcast2004.xlsx
