Acest episod prezintă trei moduri de a efectua totaluri.
Un total de rulare este, pentru o listă de valori numerice, o sumă a valorilor de la primul rând la rândul totalului de rulare. Utilizările obișnuite ale unui total curent se află într-un registru de cecuri sau într-o foaie contabilă. Există multe modalități de a crea un total de rulare, dintre care două sunt descrise mai jos.
Cea mai simplă tehnică este de a adăuga, pe fiecare rând, totalul de rulare din rândul de mai sus la valoarea din rând. Deci, prima formulă din rândul 2 este:
=SUM(D1,C2)
Motivul pentru care folosim funcția SUM este că, în primul rând, ne uităm la antetul din rândul de mai sus. Dacă folosim formula mai simplă și mai intuitivă, =D1+C2atunci se va genera o eroare deoarece valoarea antetului este text comparativ cu numerică. Magia este că funcția SUM ignoră valorile textului, care sunt adăugate ca valori zero. Când formula este copiată pe toate rândurile în care se dorește un total de rulare, referințele de celulă sunt ajustate în consecință:
Cealaltă tehnică folosește și funcția SUM, dar fiecare formulă însumează toate valorile de la primul rând până la rândul care afișează totalul de rulare. În acest caz, folosim un semn de dolar ($) pentru a face prima celulă din referință o referință absolută, ceea ce înseamnă că nu este ajustată atunci când este copiată:
Ambele tehnici nu sunt afectate prin sortarea și ștergerea rândurilor, dar, atunci când introduceți rânduri, formula trebuie copiată în noile rânduri.
Excel 2007 a introdus tabelul, care este o reimplementare a listei în Excel 2003. Tabelele au introdus o serie de caracteristici foarte utile pentru tabelele de date, cum ar fi formatarea, sortarea și filtrarea. Odată cu introducerea tabelelor, ni s-a oferit, de asemenea, un nou mod de referențiere a părților unui tabel. Acest nou stil de referință se numește referință structurată.
Pentru a converti exemplul de mai sus într-un tabel, selectăm datele pe care dorim să le includem în tabel și apăsăm Ctrl + T. După afișarea unui prompt care ne cere să confirmăm intervalul tabelului și dacă există sau nu anteturi existente, Excel convertește datele într-un tabel formatat:
Rețineți că formulele pe care le-am introdus anterior rămân aceleași.
Una dintre caracteristicile utile oferite de Tables este formatarea automată și întreținerea formulelor pe măsură ce rândurile sunt adăugate, eliminate, sortate și filtrate. În special, ne vom concentra pe formula de întreținere și care poate fi problematică. Pentru a menține funcționarea tabelelor în timp ce acestea sunt manipulate, Excel utilizează coloane calculate care sunt coloane cu formule precum coloana D din exemplul de mai sus. Când sunt inserate rânduri noi sunt adăugate în partea de jos, Excel completează automat noile rânduri cu formula „implicită” pentru acea coloană. Problema cu exemplul de mai sus este că Excel se confundă cu formulele standard și nu le tratează întotdeauna corect. Acest lucru este evident atunci când se adaugă rânduri noi în partea de jos a tabelului (selectând celula din dreapta jos în tabel și apăsând TAB):
Această deficiență este rezolvată prin utilizarea referințelor structurate mai noi. Referințarea structurată elimină necesitatea referințării celulelor specifice utilizând stilul de referință A1 sau R1C1 și folosește în schimb nume de coloane și alte cuvinte cheie pentru a identifica și a face referire la părțile unui tabel. De exemplu, pentru a crea aceeași formulă totală de rulare folosită mai sus, dar folosind referințe structurate avem:
=SUM(INDEX((Sales),1):(@Sales))
În acest exemplu avem o referință la numele coloanei, „Vânzări”, împreună cu semnul la (@) pentru a face referire la rândul din coloana în care se află formula, care este, de asemenea, cunoscut sub numele de rândul curent.
Pentru a implementa primul exemplu de mai sus, în care am adăugat valoarea totală executată în rândul precedent la valoarea vânzărilor din rândul curent, puteți utiliza funcția OFFSET:
=SUM(OFFSET((@(Running Total)),-1,0),(@Sales))
Dacă sumele utilizate pentru a calcula totalul curent sunt în două coloane, de exemplu una pentru „Debite” și una pentru „Credite”, atunci formula este:
=SUM(INDEX( (Credit),1):(@Credit))- SUM(INDEX( (Debit),1):(@Debit))
Aici folosim funcția INDEX pentru a localiza celulele de credit și de debit ale primului rând și însumăm întreaga coloană până la valorile rândului curent și inclusiv. Totalul curent este suma tuturor creditelor până la rândul curent inclusiv inclusiv suma tuturor debitelor până la rândul curent inclusiv.
Pentru mai multe informații despre referințele structurate în special și Tabelele în general, vă recomandăm cartea Excel Tables: Un ghid complet pentru crearea, utilizarea și automatizarea listelor și tabelelor de Zack Barresse și Kevin Jones.
Când am cerut cititorilor să voteze pentru sfaturile lor preferate, mesele erau populare. Mulțumim lui Peter Albert, Snorre Eikeland, Nancy Federice, Colin Michael, James E. Moede, Keyur Patel și Paul Peton pentru sugerarea acestei caracteristici. Peter Albert a scris Sfatul bonus referințe citibile. Zack Barresse a scris pontul bonus Total alergări. Patru cititori au sugerat utilizarea OFFSET pentru a crea game de expansiune pentru diagrame dinamice: Charley Baak, Don Knowles, Francis Logan și Cecelia Rieb. Tabelele fac acum același lucru în majoritatea cazurilor.
Urmăriți videoclipul
- Acest episod prezintă trei moduri de a efectua totaluri
- Prima metodă are o formulă diferită în rândul 2 decât toate celelalte rânduri
- Prima metodă este = Stânga în rândul 2 și = Stânga + Sus în rândurile 3 până la N
- Dacă încercați să utilizați aceeași formulă, primiți o eroare #Value cu = Total + Număr
- Metoda 2 utilizează
=SUM(Up,Left)sau=SUM(Previous Total,This Row Amount) - SUM ignoră textul, astfel încât să nu primiți o eroare de VALOARE
- Metoda 3 folosește un domeniu extins:
=SUM(B$2:B2) - Intervalele de expansiune sunt reci, dar sunt lente
- Citiți cartea albă a lui Charles Williams pe Excel Formula Speed
- A treia metodă este o problemă atunci când utilizați Ctrl + T și adăugați rânduri noi
- Excel nu își poate da seama cum să scrie formula
- Soluțiile alternative necesită unele cunoștințe de referință structurată în tabele
- Soluția 1 este cea lentă
=SUM(INDEX((Qty),1):(@Qty)) - Soluția 2 este volatila
=SUM(OFFSET((@Total),-1,0),(@Qty)) - (@Qty) se referă la Qty pe acest rând
- (Cantitate) se referă la toate valorile Cantității
Transcriere video
Aflați Excel pentru Podcast, Episodul 2004 - Totaluri rulate
Voi fi podcast pe toată această carte. Faceți clic pe butonul I din colțul din dreapta sus pentru a vă abona.
Bine ați venit înapoi la celula mistică pe netcast. Eu sunt Bill Jelen. Acum, acest subiect din carte, am fost contribuit de prietenul meu Zach Parise. Vorbește despre tabele Excel, Zach este expertul mondial în tabele Excel. A scris o carte despre tabelele Excel, dar mai întâi să vorbim despre rularea totalelor nu în tabele.
Deci, când mă gândesc la executarea totalurilor, există trei moduri diferite de a face totalurile de rulare și modul în care am început întotdeauna este în primul rând pe care tocmai îl spui, aduceți valoarea peste. Deci, egal cu orice e în stânga mea. Bine, deci acest format aici este doar = B2. Acestea sunt toate textul formulelor aici, în colțul din dreapta, astfel încât să vedeți ce folosim, și apoi, de acolo în jos, este o formulă simplă, egală cu valoarea anterioară, plus valoarea curentă din dreapta și copiați-o în jos , dar știți acum, avem această problemă că a necesitat două formule diferite și știți într-o situație perfectă că aveți exact aceeași formulă până la capăt, iar motivul pentru care trebuie să avem o formulă diferită acolo în primul rând este că atunci când încercați să adăugați egal 7 plus cuvântul total este o eroare de valoare,dar lucrătorul grozav de aici nu este doar să folosești stânga plus în sus, ci să folosești = (SUMA) valorii anterioare plus cantitatea din acest rând și să vezi unele este suficient de departe pentru a ignora textele. Corect, astfel încât să permită aceeași formulă. până la capăt.
Bine, așa că atunci când începeam să folosesc Excel, îl foloseam și apoi am descoperit gama de expansiune, gama de expansiune spune că vom face L $ 2: L2 și ceea ce se întâmplă este că începe întotdeauna de la rândul 2, dar apoi va coborî la rândul curent. Așadar, când vă uitați la modul în care funcționează acest lucru atunci când este copiat, am început întotdeauna rândul 2, dar coborâm la rândul curent și aceasta a devenit metoda mea preferată. Am fost ca, oh, acest lucru este mult mai sofisticat și când intrăm în Opțiuni Excel, accesați fila Formule și alegeți R1C1 în Stil de referință. Bine, R1C1, toate aceste formule sunt exact la fel până la capăt. Nu știu dacă înțelegeți R1C1, este bine să știți că avem formule R1C1 identice până la capăt.
Să ne întoarcem. Deci, această metodă de aici este metoda care mi-a plăcut, până când Charles Williams, un MBP Excel din Anglia, care are o lucrare uimitoare despre viteza formulelor, viteza formulelor Excel, a dezacreditat complet această metodă. Această metodă, să presupunem că aveți 10.000 de rânduri, fiecare formulă analizează două referințe. Așadar, te uiți la 20.000 de referințe, dar acesta, acesta privește două, acesta privește trei, acesta privește patru, acesta privește cinci și ultimul privește 10.000 de referințe și este oribil mai lent și așa că am încetat să folosesc această metodă.
Apoi citesc Zack în cartea lui Kevin Jones despre tabelele Excel și descopăr încă o problemă cu această metodă. Deci, una dintre caracteristicile utile pe care le oferă tabelele este „formatarea automată și rândurile de întreținere a formulelor sunt adăugate, eliminate, sortate și filtrate”. Bine, acesta este un citat din cartea sa. Și pentru a adăuga un rând la un tabel, trebuie doar să mergeți la ultima celulă de pe masă și să apăsați tab. Deci totul funcționează aici. Suntem la 70, chiar este minunat și apoi A104 și voi pune un 100 aici. Bine, așa că 70 ar trebui să se schimbe la 170 și se întâmplă, dar acest 70 nu ar fi trebuit să se schimbe deloc. Bine 68 + 2 nu este un 170. O voi face din nou. A 104 și pune încă o sută în ultima are dreptate. Acești doi nu au dreptate. Bine, deci avem o situație ciudată că dacăFolosiți din nou această formulă și convertiți în tabel, începeți să adăugați rânduri, totalul care rulează nu va funcționa. Cât de rău este asta?
Bine, așa că Zack oferă două lucrări și ambele necesită un pic de cunoștințe despre modul în care funcționează referințele de structură. Vom avea doar o nouă coloană aici și dacă doream să fac cantitate, cantitate egală, corect, astfel încât = (@ Qty) să spună cantitatea în acest rând. Oh, cool, ei bine, există un alt tip de referință în care folosim cantitatea fără @. Verificați acest lucru. Deci = SUM (INDEX ((Qty), 1: (@ Qty)) înseamnă toate cantitățile și vom spune că vrem să SUMăm de la prima cantitate, deci (INDEX ((Qty), 1 spune că prima valoare aici, până la cantitatea de rând curentă, iar aceasta folosește o versiune cu adevărat specială a indexului, atunci când indexul este urmat de două puncte, acesta se schimbă de fapt într-o referință de celulă. Bine, deci, această soluție încalcă regulă Charles Williams de, noi 'Va trebui să te uiți la fiecare referință, așa că, atunci când vei primi 10.000 de rânduri, va merge foarte, foarte lent.
Zach are o altă soluție care nu încalcă problema lui Charles Williams, dar folosește temutul OFFSET. OFFSET este o funcție volatilă, așa că de fiecare dată când calculați ceva, OFFSET va recalcula și totul va coborî de la OFFSET. Este doar o modalitate excelentă de a vă înșela complet, complet, formulele și, ceea ce face acest lucru, spune, luăm totalul de pe acest rând, urcăm un rând, depășim zero coloane și deci ceea ce face este să spunem: apucați totalul din rândul anterior și apoi adăugăm cantitatea din acest rând. Bine, deci, acum totul se uită la două referințe de fiecare dată, dar din păcate OFFSET introduce funcții volatile.
Ei bine, iată-l, mai mult decât v-ați dorit vreodată să știți despre Totalul alergător. Cred că ultima mea părere aici este să folosesc această metodă, pentru că arată doar două. Aceeași formulă până la capăt și referințele la tabele structurate vor funcționa.
Pentru această explorare și alte 39 de sfaturi foarte bune, consultați această carte XL, cele mai mari 40 de sfaturi Excel din toate timpurile.
Recapitulare pentru acest episod am vorbit despre trei moduri de a efectua totaluri. Prima metodă are o formulă diferită, rândul 2, decât toate celelalte rânduri. Este egal la stânga în rândul 2 și apoi egal la stânga plus în sus în rândurile 3 până la N, dar dacă încercați și folosiți aceeași formulă, stânga egală plus în sus, până la capăt, cum veți obține o eroare #Value . Deci = SUM (sus, stânga), care este totalul anterior, plus această foaie de parcurs, care funcționează excelent, fără erori de valoare și apoi gama de expansiune pe care o iubesc. Sunt grozave, dar până când citesc hârtia albă a lui Charles Williams pe forma de viteză Excel. Apoi am început să urăsc aceste referințe în expansiune. De asemenea, are o problemă atunci când utilizați CTRL T și adăugați rânduri noi. Excel nu își poate da seama cum să extindeți acea formulă, cum să adăugați rânduri noi. Îmi place acest sfat, du-te la ultima celulă din tabel și apasă Tab,care va adăuga un rând nou și apoi am vorbit despre unele referințe structurate, în care folosim cantitatea în acest rând și apoi toate cantitățile. = SUM (OFFSET ((@ Total), - 1,00, (@ Qty)).
Bine, vreau să îi mulțumesc lui Zach că a contribuit la acest sfat. Vreau să vă mulțumesc că ați trecut pe aici. Ne vedem data viitoare pentru un alt netcast de la.
Descărcare fișier
Descărcați exemplul de fișier aici: Podcast2004.xlsx
