Ova epizoda prikazuje tri načina za izvođenje ukupnih rezultata.
Tekući zbroj je, za popis numeričkih vrijednosti, zbroj vrijednosti od prvog retka do retka tekućeg zbroja. Uobičajena upotreba tekućeg zbroja je u registru čekovne knjižice ili knjigovodstvenom listu. Postoji mnogo načina za stvaranje tekućeg ukupnog broja, od kojih su dva opisana u nastavku.
Najjednostavnija tehnika je dodavanje ukupnog iznosa iz gornjeg retka u svaki redak vrijednosti u retku. Dakle, prva formula u retku 2 je:
=SUM(D1,C2)
Razlog zbog kojeg koristimo funkciju SUM je taj što u prvom redu gledamo zaglavlje u gornjem retku. Ako upotrijebimo jednostavniju, intuitivniju formulu =D1+C2tada, generirat će se pogreška jer je vrijednost zaglavlja tekst nasuprot numeričkoj. Magija je u tome što SUM funkcija zanemaruje tekstualne vrijednosti, koje se dodaju kao nula vrijednosti. Kada se formula kopira dolje u sve retke u kojima se želi tekući zbroj, reference ćelija prilagođavaju se u skladu s tim:
Druga tehnika također koristi funkciju SUM, ali svaka formula zbraja sve vrijednosti od prvog retka do retka koji prikazuju tekući zbroj. U ovom slučaju koristimo znak dolara ($) kako bismo prvu ćeliju u referenci učinili apsolutnom referencom, što znači da se ne prilagođava prilikom kopiranja:
Sortiranje i brisanje redaka ne utječe na obje tehnike, ali prilikom umetanja redaka formula se mora kopirati u nove retke.
Excel 2007 predstavio je tablicu koja je ponovna provedba popisa u programu Excel 2003. Tablice su uvele niz vrlo korisnih značajki za tablice podataka, poput formatiranja, sortiranja i filtriranja. Uvođenjem tablica također smo dobili novi način referenciranja dijelova tablice. Ovaj novi stil referenciranja naziva se strukturirano referenciranje.
Da bismo gornji primjer pretvorili u Tablicu, odabiremo podatke koje želimo uključiti u Tablicu i pritisnemo Ctrl + T. Nakon što se prikaže upit koji traži da potvrdimo raspon Tablice i postoje li zaglavlja, Excel pretvara podatke u formatiranu tablicu:
Imajte na umu da formule koje smo ranije unijeli ostaju iste.
Jedna od korisnih značajki koje nudi Tablice je automatsko oblikovanje i održavanje formule dok se retci dodaju, uklanjaju, sortiraju i filtriraju. Upravo ćemo se na održavanje formule usredotočiti i što može biti problematično. Da bi tablice nastavile raditi dok se njima manipulira, Excel koristi izračunate stupce koji su stupci s formulama kao što je stupac D u gornjem primjeru. Kada se dodaju novi retci dodaju se na dno, Excel automatski popunjava nove retke "zadanom" formulom za taj stupac. Problem s gornjim primjerom je taj što se Excel zbunjuje sa standardnim formulama i ne obrađuje ih uvijek ispravno. To se očituje kada se na dno tablice dodaju novi retci (odabirom donje desne ćelije u tablici i pritiskom na TAB):
Taj se nedostatak rješava uporabom novijeg strukturiranog referenciranja. Strukturirano referenciranje eliminira potrebu za referencama na određene ćelije pomoću stila referenciranja A1 ili R1C1 i umjesto toga koristi nazive stupaca i druge ključne riječi za identificiranje i referenciranje dijelova tablice. Na primjer, da bismo stvorili istu gore navedenu formulu za ukupno izvođenje, ali koristeći strukturirano referenciranje, imamo:
=SUM(INDEX((Sales),1):(@Sales))
U ovom primjeru imamo referencu na naziv stupca, "Prodaja", zajedno sa znakom at (@) za referencu na redak u stupcu u kojem se nalazi formula koji je također poznat kao trenutni redak.
Da biste implementirali prvi gornji primjer gdje smo dodali tekuću ukupnu vrijednost u prethodnom retku iznosu prodaje u trenutnom retku, možete koristiti funkciju OFFSET:
=SUM(OFFSET((@(Running Total)),-1,0),(@Sales))
Ako se iznosi koji se koriste za izračunavanje tekućeg zbroja nalaze u dva stupca, na primjer jedan za „terećenja” i jedan za „kredite”, tada je formula:
=SUM(INDEX( (Credit),1):(@Credit))- SUM(INDEX( (Debit),1):(@Debit))
Ovdje koristimo funkciju INDEX za lociranje kreditnih i debitnih ćelija prvog retka i zbrajanje cijelog stupca do vrijednosti tekućeg retka. Ukupni iznos je zbroj svih bodova do i uključujući trenutni redak umanjen za zbroj svih terećenja do i uključujući trenutni redak.
Za više informacija o strukturiranim referencama posebno i Tablicama općenito, preporučujemo knjigu Excel Tables: Cjelovit vodič za stvaranje, korištenje i automatizaciju popisa i tablica Zacka Barressea i Kevina Jonesa.
Kad sam pitao čitatelje da glasaju za njihove omiljene savjete, tablice su bile popularne. Zahvaljujemo Peteru Albertu, Snorreu Eikelandu, Nancy Federice, Colinu Michaelu, Jamesu E. Moedeu, Keyuru Patelu i Paulu Petonu što su predložili ovu značajku. Peter Albert napisao je bonus za Savjet za čitljive reference. Zack Barresse napisao je bonus savjet Running Totals. Četiri čitatelja predložila su upotrebu OFFSET-a za stvaranje širećih raspona za dinamičke ljestvice: Charley Baak, Don Knowles, Francis Logan i Cecelia Rieb. Tablice sada u većini slučajeva rade istu stvar.
Gledaj video
- Ova epizoda prikazuje tri načina za izvođenje ukupnih rezultata
- Prva metoda ima različitu formulu u retku 2 od svih ostalih redaka
- Prva metoda je = Lijevo u redu 2 i = Lijevo + Gore u redovima 3 do N
- Ako pokušate koristiti istu formulu, dobit ćete pogrešku #Vrijednost s = Ukupno + Broj
- Metoda 2 koristi
=SUM(Up,Left)ili=SUM(Previous Total,This Row Amount) - SUM ignorira tekst kako ne biste dobili pogrešku VALUE
- Metoda 3 koristi opseg koji se širi:
=SUM(B$2:B2) - Rasponi koji se šire su cool, ali su spori
- Pročitajte Charles Williamsovu tehničku knjigu o Excel Formula Speed
- Treća metoda predstavlja problem kada koristite Ctrl + T i dodate nove retke
- Excel ne može shvatiti kako napisati formulu
- Zaobilazna rješenja zahtijevaju neko znanje o strukturiranom referenciranju u tablicama
- Zaobilazno rješenje 1 je sporo
=SUM(INDEX((Qty),1):(@Qty)) - Zaobilazno rješenje 2 je hlapljivo
=SUM(OFFSET((@Total),-1,0),(@Qty)) - (@Qty) odnosi se na Qty u ovom retku
- (Qty) odnosi se na sve vrijednosti Qty
Prijepis videozapisa
Naučite Excel za podcast, epizoda 2004 - Pokretanje ukupnih podataka
Podcast ću cijelu ovu knjigu. Kliknite na I u gornjem desnom kutu za pretplatu.
Hej, dobrodošli natrag u mrežu mističnih stanica. Ja sam Bill Jelen. Sada ovu temu u knjizi dao mi je moj prijatelj Zach Parise. Govorimo o Excel tablicama, Zach je svjetski stručnjak za Excel tablice. Napisao je knjigu o Excel tablicama, ali prvo razgovarajmo o izvođenju ukupnih vrijednosti, a ne u tablicama.
Dakle, kad razmišljam o tekućim zbrojevima, postoje tri različita načina za izvođenje ukupnih iznosa, a način na koji sam uvijek započeo je u prvom redu koji samo kažete, prenesite vrijednost. Tako izjednači sve što je s moje lijeve strane. Dobro, ovdje je ovaj format samo = B2. Ovo su svi tekstovi formula ovdje s desnog kuta, tako da vidite što koristimo, a od tada prema naprijed to je jednostavna mala formula jednaka prethodnoj vrijednosti, plus trenutna vrijednost desno i kopirajte to dolje , ali sada znate, imamo ovaj problem jer su mu bile potrebne dvije različite formule i znate da u savršenoj situaciji imate potpuno istu formulu skroz dolje, a razlog zašto u prvom redu moramo imati drugačiju formulu je da kada pokušate dodati 7, plus riječ total, to je vrijednosna pogreška,ali cool radnik ovdje je da ne koristi samo lijevo plus gore, već da koristi = (SUM) prethodne vrijednosti plus količina u ovom retku i vidi da je neki dovoljno daleko da se ignoriraju tekstovi. Točno, tako da omogućuje istu formulu. skroz dolje.
U redu, tako je bilo kad sam počinjao koristiti Excel, koristio sam to, a onda sam otkrio opseg koji se širi, proširivi opseg kaže da ćemo napraviti L $ 2: L2 i što se događa je da ovo uvijek počinje u redu 2, ali onda se spušta na trenutni redak. Dakle, kada pogledate kako ovo funkcionira kad se kopira, uvijek smo započeli redak 2, ali spuštamo se na trenutni redak i ovo je postala moja omiljena metoda. Bio sam poput, oh, ovo je puno sofisticiranije i kad uđemo u Excel mogućnosti, idemo na karticu Formule i u referentnom stilu odaberemo R1C1. Dobro, R1C1, sve su te formule potpuno jednake do kraja. Ne znam razumijete li R1C1, samo je dobro znati da imamo identične R1C1 formule skroz dolje.
Vratimo se. Dakle, ova metoda ovdje je metoda koja mi se svidjela, sve dok Charles Williams, Excel MBP iz Engleske, koji ima nevjerojatan rad o brzini formule, brzini formule Excel, nije potpuno razotkrio ovu metodu. Ova metoda, recimo da imate 10.000 redaka, svaka pojedinačna formula gleda na dvije reference. Dakle, gledate 20.000 referenci, ali ova, ova gleda dvije, ovo gleda tri, ovo gleda četiri, ovo gleda pet, a zadnja 10.000 referenci, i užasno je sporija i tako sam prestao koristiti ovu metodu.
Zatim nastavljam čitati Zacka u knjizi Kevina Jonesa o Excel tablicama i otkrivam još jedan problem s ovom metodom. Dakle, jedna od korisnih značajki koje tablice nude je "automatsko oblikovanje i redovi održavanja formula dodani su, uklonjeni, sortirani i filtrirani". U redu, to je citat iz njegove knjige. A da biste dodali redak u tablicu, jednostavno idite do zadnje stanice na tablici i pritisnite tab. Dakle, ovdje sve funkcionira. Smanjili smo se za 70, to je super, a zatim A104 i ovdje ću staviti 100. U redu, tako da bi se 70 trebalo promijeniti na 170 i to se i dogodi, ali ovih 70 uopće se nije trebalo promijeniti. U redu 68 + 2 nije 170. Ponovit ću to. A 104 i stavite još stotku u posljednji je u pravu. Ovo dvoje nisu u pravu. U redu, imamo čudnu situaciju da ako 'ponovno koristite ovu formulu i pretvorite u tablicu i počnete dodavati retke, ukupni iznos neće raditi. Koliko je to loše?
U redu, tako da Zack nudi dva radna područja i obojici je potrebno malo znanja o tome kako funkcioniraju reference na strukturu. Imat ćemo samo novi stupac ovdje i ako bih želio napraviti količinu, jednaku količinu, zar ne, tako da = (@ Qty) kaže količina u ovom retku. Oh, super, postoji još jedna vrsta reference gdje koristimo količinu bez znaka @. Pogledaj ovo. Dakle = SUM (INDEKS ((Količina), 1: (@ Količina)) znači sve količine i reći ćemo da želimo ZBIRATI od prve količine, pa (INDEKS ((Količina), 1 kaže prva vrijednost ovdje, do trenutne količine retka, a ovo koristi stvarno posebnu verziju indeksa, kada indeks slijedi dvotačka, ona se zapravo mijenja u referencu ćelije. U redu, tako da ovo rješenje nažalost krši pravilo Charlesa Williamsa od, mi 'morat ćete pogledati svaku pojedinu referencu, pa kad dobijete 10 000 redaka, ovo će ići jako, jako sporo.
Zach ima još jedno rješenje koje ne krši problem Charlesa Williamsa, ali koristi strašni OFFSET. OFFSET je hlapljiva funkcija, tako da će se svaki put kad nešto izračunate, OFFSET ponovno izračunati, a sve dolje iz OFFSET-a ponovno izračunati. To je samo sjajan način da u potpunosti, u potpunosti zabrljate svoje formule, a ovo što radi, kaže, uzimamo zbroj iz ovog retka, prelazeći jedan red, preko nula stupaca, pa ono što radimo kaže: zgrabite ukupan iznos iz prethodnog retka, a zatim mu dodajemo količinu iz ovog retka. U redu, sad se svaki put gledaju dvije reference, ali nažalost OFFSET uvodi hlapljive funkcije.
Pa, eto, više nego što ste ikada željeli znati o Running Totalsu. Pretpostavljam da je moje konačno mišljenje ovdje koristiti ovu metodu, jer izgleda samo dvije. Ista formula do kraja i vaše će strukturirane reference tablice raditi.
Za ovo istraživanje i 39 drugih zaista dobrih savjeta pogledajte ovu knjigu XL, 40 najboljih Excel savjeta svih vremena.
Ukratko o ovoj epizodi govorili smo o tri načina za izvođenje ukupnih rezultata. Prva metoda ima drugačiju formulu, redak 2, od svih ostalih redaka. Jednako je lijevo u retku 2, a zatim jednako lijevo plus gore u redovima 3 do N, ali ako pokušate i samo koristite tu istu formulu, jednako lijevo plus gore, skroz dolje, kako ćete dobiti pogrešku #Value . Dakle = SUM (gore, lijevo), što je prethodni zbroj, plus ovaj putokaz, koji izvrsno funkcionira, bez pogrešaka u vrijednosti, a zatim širi opseg koji volim da volim. Oni su cool, ali dok ne pročitam Charles Williamsov bijeli papir o Excel obliku brzine. Tada sam počeo mrziti ove reference koje se šire. Ima problema i kada koristite CTRL T i dodate nove retke. Excel ne može shvatiti kako proširiti tu formulu, kako dodati nove retke. Volim ovaj savjet otići do zadnje stanice u tablici i pritisnuti Tab,koji će dodati novi redak, a zatim smo razgovarali o strukturiranom referenciranju, gdje koristimo količinu u ovom retku, a zatim sve količine. = ZBOR (OFFSET ((@ Ukupno), - 1,00, (@ količina)).
Ok, želim zahvaliti Zachu što je dao svoj savjet. Želim vam zahvaliti što ste navratili. Vidimo se sljedeći put za još jedan prijenos od.
Preuzmi datoteku
Preuzmite datoteku uzorka ovdje: Podcast2004.xlsx
