Šajā epizodē parādīti trīs veidi, kā veikt kopējo summu.
Skrējienu kopsumma skaitlisko vērtību sarakstam ir vērtību summa no pirmās rindas līdz tekošās kopējās rindas. Bieži izmantojamās summas tiek izmantotas čeku reģistrā vai grāmatvedības lapā. Ir daudz veidu, kā izveidot kopējo darbību - divi no tiem ir aprakstīti zemāk.
Vienkāršākais paņēmiens ir katrā rindā pievienot iepriekšējās rindas kopējo summu rindas vērtībai. Tātad pirmā formula 2. rindā ir:
=SUM(D1,C2)
Funkcijas SUM izmantošanas iemesls ir tas, ka pirmajā rindā mēs aplūkojam galveni augšējā rindā. Ja izmantosim vienkāršāku, intuitīvāku formulu, =D1+C2tad tiks ģenerēta kļūda, jo galvenes vērtība ir teksts pret skaitlisku. Burvība ir tāda, ka funkcija SUM ignorē teksta vērtības, kas tiek pievienotas kā nulles vērtības. Kad formula tiek nokopēta visās rindās, kurās ir vēlams rādīt kopējo summu, šūnu atsauces tiek attiecīgi pielāgotas:
Cits paņēmiens izmanto arī funkciju SUM, bet katra formula summē visas vērtības no pirmās rindas līdz rindai, kurā parādīts kopējais rādītājs. Šajā gadījumā mēs izmantojam dolāra zīmi ($), lai atsauces pirmā šūna būtu absolūta atsauce, kas nozīmē, ka tā netiek koriģēta kopējot:
Rindu kārtošana un dzēšana neietekmē abus paņēmienus, bet, ievietojot rindas, formula ir jākopē jaunajās rindās.
Excel 2007 ieviesa tabulu, kas ir saraksta atkārtota ieviešana programmā Excel 2003.. Tabulās tika ieviesti vairāki ļoti noderīgi datu tabulu līdzekļi, piemēram, formatēšana, šķirošana un filtrēšana. Ieviešot tabulas, mums tika piedāvāts arī jauns veids, kā atsaukties uz tabulas daļām. Šo jauno atsauču stilu sauc par strukturētu atsauci.
Lai pārveidotu iepriekš minēto piemēru tabulā, mēs atlasām datus, kurus vēlamies iekļaut tabulā, un nospiediet Ctrl + T. Pēc tam, kad parādījāt uzvedni, kurā mums lūdz apstiprināt tabulas diapazonu un vai ir vai nav esošas galvenes, Excel konvertē datus formatētā tabulā:
Ņemiet vērā, ka iepriekš ievadītās formulas paliek nemainīgas.
Viena no noderīgajām iespējām, ko piedāvā tabulas, ir automātiska formatēšana un formulu uzturēšana, pievienojot, noņemot, sakārtojot un filtrējot rindas. Īpaši koncentrējamies uz formulu uzturēšanu, kas var būt problemātiska. Lai tabulas darbotos, kamēr ar tām manipulē, Excel izmanto aprēķinātās kolonnas, kas ir kolonnas ar formulām, piemēram, D kolonna iepriekš minētajā piemērā. Kad apakšā tiek pievienotas jaunas rindas, Excel automātiski aizpilda jaunās rindas ar šīs slejas noklusējuma formulu. Iepriekš minētā piemēra problēma ir tā, ka Excel tiek sajaukts ar standarta formulām un ne vienmēr rīkojas pareizi. Tas tiek parādīts, kad tabulas apakšdaļā tiek pievienotas jaunas rindas (tabulā atlasot apakšējo labo šūnu un nospiežot TAB):
Šis trūkums tiek novērsts, izmantojot jaunāku strukturēto atsauci. Strukturēta atsaukšana novērš nepieciešamību norādīt konkrētas šūnas, izmantojot A1 vai R1C1 atsauces stilu, un tā vietā izmanto kolonnu nosaukumus un citus atslēgvārdus, lai identificētu un atsauktu tabulas daļas. Piemēram, lai izveidotu to pašu iepriekš izmantoto kopējo formulu, bet izmantojot strukturētas atsauces, mums ir:
=SUM(INDEX((Sales),1):(@Sales))
Šajā piemērā mums ir atsauce uz kolonnas nosaukumu “Pārdošana” kopā ar at zīmi (@), lai atsauktos uz rindu kolonnā, kurā atrodas formula, kuru sauc arī par pašreizējo rindu.
Lai ieviestu pirmo iepriekš minēto piemēru, kurā iepriekšējā rindā esošo kopējo vērtību pievienojām pārdošanas summai pašreizējā rindā, varat izmantot funkciju OFFSET:
=SUM(OFFSET((@(Running Total)),-1,0),(@Sales))
Ja kopsummas aprēķināšanai izmantotās summas ir divās kolonnās, piemēram, viena “Debetiem” un viena “Kredīti”, tad formula ir šāda:
=SUM(INDEX( (Credit),1):(@Credit))- SUM(INDEX( (Debit),1):(@Debit))
Šeit mēs izmantojam funkciju INDEX, lai atrastu pirmās rindas Kredīta un Debeta šūnas, un summējot visu kolonnu līdz pašreizējās rindas vērtībām un ieskaitot tās. Kopsumma ir visu kredītu summa līdz pašreizējai rindai (ieskaitot), atskaitot visu debetu summu līdz pašreizējai rindai (ieskaitot).
Lai iegūtu plašāku informāciju par strukturētajām atsaucēm un tabulām kopumā, iesakām Zack Barresse un Kevin Jones grāmatu Excel Tables: Pilnīgs ceļvedis sarakstu un tabulu izveidošanai, lietošanai un automatizēšanai.
Kad es palūdzu lasītājus balsot par iecienītākajiem padomiem, populāras bija tabulas. Paldies Pīteram Albertam, Snorram Eikelandam, Nensijai Federicei, Kolinam Maiklam, Džeimsam E. Moedem, Kiktam Patelam un Polam Petonam par šīs funkcijas ieteikšanu. Pīters Alberts uzrakstīja bonusa padomu lasāmām atsaucēm. Zaks Barresse uzrakstīja Running Totals bonusa padomu. Četri lasītāji ieteica izmantot OFFSET, lai izveidotu dinamisko diagrammu paplašināšanas diapazonus: Charley Baak, Don Knowles, Francis Logan un Cecelia Rieb. Galdi tagad vairumā gadījumu dara to pašu.
Skatīties video
- Šajā epizodē parādīti trīs veidi, kā veikt kopējo summu
- Pirmajai metodei 2. rindā ir atšķirīga formula nekā visām pārējām rindām
- Pirmā metode ir = pa kreisi 2. rindā un = pa kreisi + augšup no 3. līdz N rindai
- Mēģinot izmantot to pašu formulu, tiek parādīta kļūda #Value ar = Kopā + Skaits
- 2. metodē tiek izmantots
=SUM(Up,Left)vai=SUM(Previous Total,This Row Amount) - SUM ignorē tekstu, lai jūs nesaņemtu VALUE kļūdu
- 3. metodē tiek izmantots paplašināšanas diapazons:
=SUM(B$2:B2) - Diapazonu paplašināšana ir forša, bet lēna
- Lasiet Čārlza Viljamsa balto grāmatu par Excel Formula Speed
- Trešā metode ir problēma, kad izmantojat Ctrl + T un pievienojat jaunas rindas
- Excel nevar saprast, kā rakstīt formulu
- Risinājumi prasa zināmas zināšanas par strukturētām atsaucēm tabulās
- 1. risinājums ir lēns
=SUM(INDEX((Qty),1):(@Qty)) - 2. risinājums ir nepastāvīgs
=SUM(OFFSET((@Total),-1,0),(@Qty)) - (@Qty) attiecas uz daudzumu šajā rindā
- (Daudzums) attiecas uz visām daudzuma vērtībām
Video atšifrējums
Uzziniet programmu Excel Podcast, 2004. gada sērija - Kopējais rādītājs
Es apraidīšu visu šo grāmatu. Lai abonētu, augšējā labajā stūrī noklikšķiniet uz I.
Sveiki, laipni aicināti atgriezties mistisko šūnu apraidē. Es esmu Bils Jelens. Tagad šo tēmu grāmatā man palīdzēja mans draugs Zaks Parise. Runājot par Excel tabulām, Zaks ir pasaules Excel tabulu eksperts. Viņš ir uzrakstījis grāmatu par Excel tabulām, bet vispirms parunāsim par kopsummu rādīšanu nevis tabulās.
Tātad, kad es domāju par kopējo rādītāju, ir trīs dažādi veidi, kā veikt kopējos rādītājus, un veids, kā es vienmēr sāku, ir pirmajā rindā, kuru jūs vienkārši sakāt, pārnesiet vērtību. Tik vienāds, kas man pa kreisi. Labi, tāpēc šis formāts šeit ir tikai = B2. Tie visi ir formulas teksts šeit labajā stūrī, lai jūs redzētu, ko mēs izmantojam, un tad no turienes uz leju, tā ir vienkārša maza formula, kas vienāda ar iepriekšējo vērtību, plus pašreizējā vērtība pa labi un kopē to , bet jūs tagad zināt, ka mums ir šī problēma, ka tam bija nepieciešamas divas dažādas formulas, un jūs zināt, ka ideālā situācijā jums ir tieši tāda pati formula līdz galam, un iemesls, kāpēc mums pirmajā rindā ir jābūt citādai formulai, ir ka, mēģinot pievienot vienādus 7 plus vārdu kopā, tā ir vērtības kļūda,bet foršais darbinieks šeit ir ne tikai izmantot kreiso plus uz augšu, bet izmantot = (SUM) no iepriekšējās vērtības plus daudzuma šajā rindā, un redzēt, ka daži ir pietiekami tālu, lai ignorētu tekstus. Pareizi, lai atļautu to pašu formulu. visu ceļu uz leju.
Labi, tas bija tad, kad es sāku izmantot Excel, es to izmantoju, un tad es atklāju paplašinošo diapazonu, paplašināšanas diapazonā teikts, ka mēs darīsim L $ 2: L2, un kas notiek, tas vienmēr sākas ar 2. rindu bet tad tas iet uz leju līdz pašreizējai rindai. Tātad, kad paskatās, kā tas darbojas, kad tas tiek kopēts, mēs vienmēr sākām 2. rindu, bet mēs ejam uz pašreizējo rindu, un šī kļuva par manu iecienītāko metodi. Man bija, piemēram, oh, tas ir daudz sarežģītāk, un, pārejot uz Excel opcijām, dodieties uz cilni Formulas un atsauces stilā izvēlieties R1C1. Labi, R1C1, visas šīs formulas ir pilnīgi vienādas līdz galam. Es nezinu, vai jūs saprotat R1C1, ir tikai labi zināt, ka mums līdz galam ir identiskas R1C1 formulas.
Atgriezīsimies. Tātad šī metode šeit ir tā metode, kas man patika, līdz brīdim, kad Čārlzs Viljamss, Excel MBP no Anglijas, kuram ir pārsteidzošs papīrs par formulu ātrumu, Excel formulu ātrumu, pilnībā atcēla šo metodi. Pieņemsim, ka ar šo metodi jums ir 10 000 rindu, katrā formulā tiek aplūkotas divas atsauces. Tātad jūs skatāties uz 20 000 atsaucēm, bet šī, šī skatās divas, šī - trīs, šī - četras, šī - piecas, pēdējā - 10 000 atsauces, un tas ir šausmīgi lēnāk un tāpēc es pārtraucu izmantot šo metodi.
Tad es turpinu lasīt Zaku Kevina Džonsa grāmatā par Excel tabulām, un es atklāju vēl vienu šīs metodes problēmu. Tātad viena no noderīgajām funkcijām, ko piedāvā tabulas, ir “automātiskās formatēšanas un formulu uzturēšanas rindas tiek pievienotas, noņemtas, sakārtotas un filtrētas”. Labi, tas ir citāts no viņa grāmatas. Lai tabulai pievienotu rindu, vienkārši dodieties uz tabulas pēdējo šūnu un nospiediet tabulēšanas taustiņu. Tātad šeit viss darbojas. Mums ir līdz 70, tas ir lieliski, un pēc tam A104, un es šeit ievietošu 100. Labi, lai 70 būtu jāmaina uz 170, un tā arī notiek, taču šiem 70 nemaz nevajadzēja mainīties. Labi, 68 + 2 nav 170. Es to izdarīšu vēlreiz. Pareizi ir 104 un ielikt vēl simtu pēdējā. Šie divi nav pareizi. Labi, tāpēc mums ir dīvaina situācija, ka, ja jūsatkārtoti izmantojot šo formulu un jūs pārveidojat par tabulu, sākat pievienot rindas, kopējais rādītājs nedarbosies. Cik tas ir slikti?
Labi, tāpēc Zaks piedāvā divus apvedceļus, un abiem ir nepieciešamas nedaudz zināšanas par to, kā darbojas struktūras atsauces. Mums vienkārši šeit būs jauna kolonna, un, ja es gribētu izdarīt daudzumu, vienādu daudzumu, pareizi, tā, ka = (@ Daudzums) šajā rindā saka daudzumu. Ak forši, labi, ka ir cita veida atsauce, kur mēs izmantojam Qty bez @. Pārbaudiet to. Tātad = SUM (INDEX ((Daudzums), 1: (@ Daudzums)) nozīmē visus daudzumus, un mēs teiksim, ka mēs vēlamies SUMMĒT no pirmā daudzuma, tāpēc (INDEX ((Daudzums), 1 saka pirmā vērtība šeit, līdz pašreizējam rindas daudzumam, un tiek izmantota patiešām īpaša indeksa versija, ja indeksam seko kols, tā faktiski tiek mainīta uz šūnas atsauci. Labi, šis risinājums diemžēl pārkāpj Čārlza Viljamsa likumu no, mēsmums būs jāaplūko katra atsauce, un tad, kad saņemsit 10 000 rindu, tas notiks ļoti, ļoti lēni.
Zacham ir vēl viens risinājums, kas nepārkāpj Čārlza Viljamsa problēmu, taču tas izmanto baidīto OFFSET. OFFSET ir nepastāvīga funkcija, tāpēc katru reizi, kad jūs kaut ko aprēķināt, OFFSET pārrēķinās, un viss no OFFSET pārrēķinās. Tas ir tikai lielisks veids, kā pilnīgi, pilnībā izskrūvēt savas formulas, un tas, ko tas dara, sakot, mēs ņemam kopējo summu no šīs rindas, pārejam vienā rindā augšup pa nullēm kolonnu, un tas, ko tas dara, saka: paņemiet kopējo summu no iepriekšējās rindas, un tad mēs tam pievienojam daudzumu no šīs rindas. Labi, tāpēc tagad katru reizi tiek skatītas divas atsauces, taču diemžēl OFFSET ievieš nepastāvīgas funkcijas.
Nu, tur jums tas ir vairāk, nekā jūs jebkad gribējāt uzzināt par Running Totals. Es domāju, ka mans galīgais viedoklis šeit ir izmantot šo metodi, jo tas izskatās tikai divi. Tā pati formula līdz galam un jūsu strukturētās tabulas atsauces darbosies.
Par šo izpēti un 39 citiem patiešām labiem padomiem skatiet šo grāmatu XL, 40 visu laiku lielākos Excel padomus.
Šīs epizodes kopsavilkums mēs runājām par trim veidiem, kā veikt kopsummas. Pirmajai metodei ir atšķirīga formula, 2. rinda, nekā visām pārējām rindām. Tas ir vienāds pa kreisi 2. rindā un pēc tam vienāds pa kreisi plus uz augšu no 3. līdz N rindai, bet, ja jūs mēģināt un vienkārši izmantojat to pašu formulu, vienāds pa kreisi plus uz augšu, līdz galam, kā jūs saņemsit kļūdu #Value . Tātad = SUM (augšup, pa kreisi), kas ir iepriekšējais kopsumma, kā arī šī plāns, kas darbojas lieliski, bez vērtības kļūdām un pēc tam paplašinātais diapazons, kuru es mīlu. Viņi ir forši, bet, kamēr es neizlasīju Čārlza Viljamsa balto grāmatu par Excel ātruma formu. Tad es sāku ienīst šīs paplašinātās atsauces. Tam ir arī problēma, kad izmantojat CTRL T un pievienojat jaunas rindas. Excel nevar saprast, kā paplašināt šo formulu, kā pievienot jaunas rindas. Man patīk, ka šis padoms jāiet uz tabulas pēdējo šūnu un jānospiež Tab,kas pievienos jaunu rindu, un pēc tam mēs runājām par dažām strukturētām atsaucēm, kur šajā rindā mēs izmantojam daudzumu un pēc tam visus daudzumus. = SUMMA (OFFSET ((@ Kopā), - 1,00, (@ Daudzums)).
Labi, es vēlos pateikties Zacham par ieguldījumu šajā padomā. Es gribu pateikties, ka apstājāties. Tiksimies nākamreiz, lai skatītu citu netcast raidījumu no.
Lejupielādēt failu
Lejupielādējiet faila paraugu šeit: Podcast2004.xlsx
