Ta epizoda prikazuje tri načine za doseganje tekočih vsot.
Tekoči seštevek je za seznam številskih vrednosti vsota vrednosti od prve vrstice do vrstice tekočega seštevka. Pogoste uporabe tekočega seštevka so v registru čekovne knjige ali računovodskem listu. Obstaja veliko načinov za ustvarjanje tekočega skupaj, od katerih sta opisana spodaj.
Najenostavnejša tehnika je, da v vsaki vrstici vrednosti tekoče vrednosti iz zgornje vrstice dodate vrednosti v vrstici. Prva formula v vrstici 2 je torej:
=SUM(D1,C2)
Razlog, da uporabljamo funkcijo SUM, je, ker v prvi vrstici gledamo glavo v zgornji vrstici. Če uporabimo enostavnejšo, bolj intuitivno formulo, =D1+C2se bo ustvarila napaka, ker je vrednost glave besedilo v primerjavi s številko. Magija je v tem, da funkcija SUM prezre besedilne vrednosti, ki so dodane kot nič vrednosti. Ko je formula kopirana v vse vrstice, v katerih je zaželen tekoči seštevek, se sklici na celice ustrezno prilagodijo:
Druga tehnika prav tako uporablja funkcijo SUM, vendar vsaka formula sešteje vse vrednosti od prve vrstice do vrstice, ki prikazuje tekoče skupno število. V tem primeru uporabljamo znak za dolar ($), da prva celica v sklicu postane absolutna referenca, kar pomeni, da pri kopiranju ni prilagojena:
Razvrščanje in brisanje vrstic ne vpliva na obe tehniki, vendar je treba pri vstavljanju vrstic formulo kopirati v nove vrstice.
Excel 2007 je predstavil tabelo, ki je ponovna izvedba seznama v Excelu 2003. Tabele so uvedle številne zelo uporabne funkcije za podatkovne tabele, kot so oblikovanje, razvrščanje in filtriranje. Z uvedbo tabel smo dobili tudi nov način sklicevanja na dele tabele. Ta novi slog referenciranja se imenuje strukturirano referenciranje.
Za pretvorbo zgornjega primera v tabelo izberemo podatke, ki jih želimo vključiti v tabelo, in pritisnemo Ctrl + T. Po prikazu poziva, ki nas prosi, da potrdimo obseg tabele in ali obstajajo glave, Excel pretvori podatke v formatirano tabelo:
Upoštevajte, da so formule, ki smo jih vnesli prej, enake.
Ena od uporabnih funkcij, ki jih ponuja Tabele, je samodejno oblikovanje in vzdrževanje formule, ko so vrstice dodane, odstranjene, razvrščene in filtrirane. Osredotočili se bomo predvsem na vzdrževanje formule, ki je lahko problematično. Da bi tabele še naprej delovale, medtem ko z njimi manipulirate, Excel uporablja izračunane stolpce, ki so stolpci s formulami, kot je stolpec D v zgornjem primeru. Ko so vstavljene nove vrstice, ki so dodane na dno, Excel samodejno zapolni nove vrstice s privzeto formulo za ta stolpec. Težava z zgornjim primerom je, da se Excel zameša s standardnimi formulami in z njimi ne ravna vedno pravilno. To se pokaže, ko se na dno tabele dodajo nove vrstice (z izbiro spodnje desne celice v tabeli in pritiskom na TAB):
Ta pomanjkljivost je odpravljena z uporabo novejših strukturiranih referenc. Strukturirano sklicevanje odpravlja potrebo po sklicevanju na določene celice z uporabo referenčnega sloga A1 ali R1C1 in namesto tega uporablja imena stolpcev in druge ključne besede za prepoznavanje in sklicevanje na dele tabele. Na primer, za ustvarjanje enake formule za tekoče skupno število, uporabljene zgoraj, vendar z uporabo strukturiranega sklicevanja, imamo:
=SUM(INDEX((Sales),1):(@Sales))
V tem primeru imamo sklic na ime stolpca, »Prodaja«, skupaj z znakom at (@) za sklicevanje na vrstico v stolpcu, v katerem je formula, ki je znana tudi kot trenutna vrstica.
Za izvedbo prvega zgornjega primera, kjer smo dodali tekočo skupno vrednost v prejšnji vrstici znesku prodaje v trenutni vrstici, lahko uporabite funkcijo OFFSET:
=SUM(OFFSET((@(Running Total)),-1,0),(@Sales))
Če se zneski, uporabljeni za izračun tekočega seštevka, nahajajo v dveh stolpcih, na primer enem za "Debit" in enega za "Kredite", potem je formula:
=SUM(INDEX( (Credit),1):(@Credit))- SUM(INDEX( (Debit),1):(@Debit))
Tu uporabljamo funkcijo INDEX za iskanje kreditnih in debetnih celic prve vrstice ter seštevanje celotnega stolpca do vključno vrednosti trenutne vrstice. Tekoči seštevek je vsota vseh dobropisov do vključno trenutne vrstice, zmanjšana za vsoto vseh obremenitev do vključno vrstice.
Za več informacij zlasti o strukturiranih referencah in Tabelah na splošno priporočamo knjigo Excel Tables: Popoln vodnik za ustvarjanje, uporabo in avtomatizacijo seznamov in tabel Zacka Barresseja in Kevina Jonesa.
Ko sem bralce prosil, naj glasujejo za njihove najljubše nasvete, so bile mize priljubljene. Hvala Petru Albertu, Snorreju Eikelandu, Nancy Federice, Colinu Michaelu, Jamesu E. Moedeju, Keyurju Patelu in Paulu Petonu, da so predlagali to funkcijo. Peter Albert je napisal bonus Nasvet za bralne reference. Zack Barresse je napisal bonus nasvet Running Totals. Štirje bralci so predlagali uporabo programa OFFSET za ustvarjanje razširjenih razponov za dinamične lestvice: Charley Baak, Don Knowles, Francis Logan in Cecelia Rieb. Mize zdaj v večini primerov počnejo isto.
Oglejte si video
- Ta epizoda prikazuje tri načine za doseganje tekočih vsot
- Prva metoda ima v vrstici 2 drugačno formulo kot vse druge vrstice
- Prva metoda je = Levo v vrstici 2 in = Levo + Gor v vrsticah 3 do N
- Če poskusite uporabiti isto formulo, se prikaže napaka #Value z = Skupno + Število
- Metoda 2 uporablja
=SUM(Up,Left)oz=SUM(Previous Total,This Row Amount) - SUM prezre besedilo, tako da ne dobite napake VALUE
- Metoda 3 uporablja razširjeni obseg:
=SUM(B$2:B2) - Razširljivi obsegi so sicer kul, vendar počasni
- Preberite knjigo Charlesa Williamsa o hitrosti formule Excel
- Tretja metoda je težava, če uporabite Ctrl + T in dodate nove vrstice
- Excel ne more ugotoviti, kako napisati formulo
- Rešitve zahtevajo nekaj znanja o strukturiranem sklicevanju v tabelah
- Rešitev 1 je počasno
=SUM(INDEX((Qty),1):(@Qty)) - Rešitev 2 je hlapna
=SUM(OFFSET((@Total),-1,0),(@Qty)) - (@Qty) se nanaša na Qty v tej vrstici
- (Qty) se nanaša na vse vrednosti Qty
Video zapis
Naučite se Excel za podcast, Epizoda 2004 - Izvajanje vsote
Celotno knjigo bom objavil v oddaji. Kliknite, da I v zgornjem desnem kotu, da se naročite.
Hej, dobrodošli nazaj v mreži mističnih celic. Jaz sem Bill Jelen. Zdaj to temo v knjigi mi je prispeval moj prijatelj Zach Parise. Če govorimo o Excelovih tabelah, je Zach svetovni strokovnjak za tabele Excel. Napisal je knjigo o Excelovih tabelah, a najprej se pogovorimo o zagonu vsote ne v tabelah.
Torej, ko pomislim na tekoče vsote, obstajajo trije različni načini, kako narediti tekoče vsote, in način, s katerim sem vedno začel, je v prvi vrstici, ki jo pravkar izgovorite, pripelje vrednost. Torej enakovredno, kar je levo od mene. Torej, ta oblika tukaj je samo = B2. Vse to je besedilo formule tukaj v desnem kotu, tako da vidite, kaj uporabljamo, nato pa od tu naprej navzdol, to je preprosta majhna formula enaka prejšnji vrednosti, plus trenutna vrednost desno in jo kopirajte navzdol , ampak zdaj veste, imamo to težavo, da sta zahtevali dve različni formuli in veste, da imate v popolni situaciji popolnoma enako formulo do konca, razlog, da moramo v prvi vrstici imeti drugačno formulo, pa je da ko poskusite dodati 7, plus besedo skupaj, gre za napako vrednosti,ampak kul delavec tukaj zunaj je, da ne uporablja le levega plus navzgor, ampak da uporabi = (SUM) prejšnje vrednosti in količine v tej vrstici, in glejte, da je nekaj dovolj daleč, da ignorirate besedila. Tako, da to omogoča isto formulo. vse do konca.
V redu, takrat, ko sem začel uporabljati Excel, sem to uporabljal, nato pa sem odkril razširitev obsega, razširitev obsega pravi, da bomo naredili L $ 2: L2 in kaj se zgodi, to se vedno začne v vrstici 2, potem pa gre v trenutno vrstico. Torej, ko pogledate, kako to deluje, ko se kopira, smo vedno začeli z vrstico 2, vendar se spustimo v trenutno vrstico in to je postala moja najljubša metoda. Bil sem kot, oh, to je toliko bolj dodelano in ko gremo v možnosti Excel, pojdimo na zavihek Formule in v referenčnem slogu izberemo R1C1. V redu, R1C1, vse te formule so popolnoma enake do konca. Ne vem, če razumete R1C1, dobro je vedeti, da imamo enake formule R1C1 do konca.
Vrnimo se nazaj. Torej, ta metoda tukaj je metoda, ki mi je bila všeč, dokler Charles Williams, Excel MBP iz Anglije, ki ima neverjeten prispevek o hitrosti formule, hitrosti formule Excel, popolnoma ni razkril te metode. Ta metoda, recimo, da imate 10.000 vrstic, vsaka posamezna formula gleda na dve sklici. Torej gledate 20.000 referenc, toda ta, ta gleda na dve, to gleda na tri, to gleda na štiri, to gleda na pet, zadnja pa na 10.000 referenc in je strašno počasnejša in zato sem prenehal uporabljati to metodo.
Nato nadaljujem z branjem Zacka v knjigi Kevina Jonesa o Excelovih tabelah in odkrijem še eno težavo s to metodo. Torej je ena od uporabnih funkcij, ki jih ponujajo tabele, "samodejno oblikovanje in vzdrževanje vrstic formul se dodajo, odstranijo, razvrstijo in filtrirajo". V redu, to je citat iz njegove knjige. Če želite dodati vrstico v tabelo, preprosto pojdite v zadnjo celico na mizi in pritisnite jeziček. Tukaj torej vse deluje. Desno smo do 70, to je super, potem pa A104 in tukaj bom postavil 100. V redu, tako da bi se 70 moralo spremeniti na 170 in se, vendar se teh 70 sploh ne bi smelo spremeniti. V redu 68 + 2 ni 170. Ponovil bom. A 104 in v zadnji postavi še sto, je prav. To dvoje nima prav. V redu, imamo nekaj čudnih situacij, da če "Če ponovno uporabite to formulo in pretvorite v tabelo, začnete dodajati vrstice, tekoče skupno število ne bo delovalo. Kako slabo je to?
V redu, torej Zack ponuja dva načina dela in oba zahtevata malo znanja o tem, kako delujejo referenčne strukture. Tukaj bomo imeli samo nov stolpec in če bi hotel narediti količino, enako količino, kajne, tako da = (@ Qty) pove količino v tej vrstici. Oh, kul, no, obstaja še ena vrsta reference, kjer Qty uporabljamo brez znaka @. Poglej to. Torej = SUM (INDEX ((Qty), 1: (@ Qty)) pomeni vse količine in rekli bomo, da želimo SUMATI od prve količine, torej (INDEX ((Qty), 1 pravi, da prva vrednost tukaj, do trenutne količine vrstic, in to z uporabo res posebne različice indeksa, ko indeksu sledi dvopičje, se dejansko spremeni v referenco celice. Torej, ta rešitev žal krši pravilo Charlesa Williamsa od,morali si bomo ogledati vsako posamezno referenco, zato bo, ko dobite 10.000 vrstic, to šlo zelo, zelo počasi.
Zach ima še eno rešitev, ki ne krši težave Charlesa Williamsa, vendar uporablja strašljiv OFFSET. OFFSET je volatilna funkcija, zato se bo vsakič, ko boste nekaj izračunali, OFFSET ponovno izračunal in vse spodnje črte iz OFFSET-a se bodo preračunale. To je samo odličen način, da popolnoma, popolnoma zamočite svoje formule, in kaj to počne, pravi, vzamemo vsoto iz te vrstice, gremo eno vrstico navzgor nad nič stolpci in tako to počne: vzemite skupni znesek iz prejšnje vrstice in mu nato dodamo količino iz te vrstice V redu, zdaj je vse skupaj vsakič videti na dveh referencah, žal pa OFFSET uvaja hlapne funkcije.
No, tu ste, bolj kot ste kdaj želeli vedeti o Running Totals. Mislim, da je moje končno mnenje uporaba te metode, ker je videti le dve. Ista formula do konca in vaše strukturirane referenčne tabele bodo delovale.
Za to raziskovanje in 39 drugih res dobrih nasvetov si oglejte knjigo XL, 40 največjih Excelovih nasvetov vseh časov.
V povzetku te epizode smo govorili o treh načinih za doseganje tekočih vsot. Prva metoda ima drugačno formulo, vrstica 2, kot vse druge vrstice. V vrstici 2 je enako levo in nato v vrsticah od 3 do N enako levo plus gor, če pa poskusite uporabiti isto formulo, enako levo plus gor, vse do konca, kako boste dobili napako #Value . Torej = SUM (zgoraj, levo), kar je prejšnji skupni znesek, plus ta načrt, ki deluje odlično, brez vrednostnih napak in nato razširjenega obsega, ki ga imam rad. So kul, toda dokler ne preberem bele knjige Charlesa Williamsa o Excelovi obliki hitrosti. Potem sem začel sovražiti te razširjene reference. Težava ima tudi, ko uporabljate CTRL T in dodate nove vrstice. Excel ne more ugotoviti, kako razširiti to formulo in kako dodati nove vrstice. Všeč mi je, da se ta nasvet pomakne do zadnje celice v mizi in pritisne Tab,to bo dodalo novo vrstico, nato pa smo govorili o strukturiranem sklicevanju, kjer v tej vrstici uporabljamo količino in nato vse količine. = SUM (OFFSET ((@ Skupaj), - 1,00, (@ Količina)).
V redu, zahvaljujem se Zachu, ker je prispeval to namig. Želim se vam zahvaliti, da ste se ustavili. Se vidimo naslednjič za še eno oddajo iz.
Prenesite datoteko
Prenesite vzorčno datoteko tukaj: Podcast2004.xlsx
