Total de rulare (numit și sumă cumulativă) este destul de frecvent utilizat în multe situații. Este o valoare care vă arată care este suma valorilor de până acum.
De exemplu, dacă aveți datele lunare de vânzări, atunci un total curent vă va spune cât s-au realizat vânzări până la o anumită zi din prima zi a lunii.
Există, de asemenea, alte situații în care se folosește adesea totalul curent, cum ar fi calcularea soldului dvs. de numerar în extrasele / registrul dvs. bancar, numărarea caloriilor în planul de masă etc.
În Microsoft Excel, există mai multe moduri diferite de a calcula totalurile care rulează.
Metoda pe care o alegeți va depinde, de asemenea, de modul în care sunt structurate datele dvs.
De exemplu, dacă aveți date tabulare simple, puteți utiliza o formulă simplă SUM, dar dacă aveți un tabel Excel, atunci cel mai bine este să utilizați referințe structurate. De asemenea, puteți utiliza Power Query pentru a face acest lucru.
În acest tutorial, voi acoperi toate aceste metode diferite calculați totalurile de rulare în Excel.
Asadar, haideti sa începem!
Calculul totalului de rulare cu date tabulare
Dacă aveți date tabulare (de exemplu, un tabel în Excel care nu este convertit într-un tabel Excel), puteți utiliza câteva formule simple pentru a calcula totalurile curente.
Utilizarea operatorului de adăugare
Să presupunem că aveți date despre vânzări în funcție de dată și doriți să calculați totalul rulat în coloana C.
Mai jos sunt pașii pentru a face acest lucru.
Pasul 1 - În celula C2, care este prima celulă în care doriți să ruleze totalul, introduceți
= B2
Aceasta va obține pur și simplu aceleași valori de vânzare în celula B2.
Pasul 2 - În celula C3, introduceți formula de mai jos:
= C2 + B3
Pasul 3 - Aplicați formula la întreaga coloană. Puteți utiliza mânerul de umplere pentru a-l selecta și trage, sau pur și simplu și copiați-lipiți celula C3 în toate celulele rămase (care ar regla automat referința și ar da rezultatul corect).
Acest lucru vă va oferi rezultatul așa cum se arată mai jos.
Este o metodă foarte simplă și funcționează bine în majoritatea cazurilor.
Logica este simplă - fiecare celulă preia valoarea de deasupra ei (care este suma cumulată până la data anterioară) și adaugă valoarea în celula adiacentă (care este valoarea de vânzare pentru ziua respectivă).
Există un singur dezavantaj - în cazul în care ștergeți oricare dintre rândurile existente din acest set de date, toate celulele de mai jos care ar returna o eroare de referință (#REF!)
Dacă aceasta este o posibilitate cu setul de date, utilizați următoarea metodă care utilizează formula SUM
Utilizarea SUM cu referință de celulă blocată parțial
Să presupunem că aveți date despre vânzări în funcție de dată și doriți să calculați totalul rulat în coloana C.
Mai jos este formula SUMĂ care vă va oferi totalul de rulare.
= SUMĂ ($ B $ 2: B2)
Permiteți-mi să vă explic cum funcționează această formulă.
În formula SUM de mai sus, am folosit referința pentru a adăuga ca $ B $ 2: B2
- $ B $ 2 - aceasta este o referință absolută, ceea ce înseamnă că atunci când copiez aceeași formulă în celulele de mai jos, această referință nu se va schimba. Deci, atunci când copiați formula în celula de mai jos, formula s-ar schimba în SUM ($ B $ 2: B3)
- B2 - aceasta este a doua parte a referinței, care este o referință relativă, ceea ce înseamnă că acest lucru s-ar regla pe măsură ce copiez formula în jos sau în dreapta. Deci, atunci când copiați formula în celula de mai jos, această valoare va deveni B3
De asemenea, citiți: Referințe de celule absolute, relative și mixte în Excel
Lucrul grozav despre această metodă este că, în cazul în care ștergeți oricare dintre rândurile din setul de date, această formulă s-ar regla și vă va oferi totuși totalurile corecte.
Calculul totalului de rulare în tabelul Excel
Când lucrați cu date tabulare în Excel, este o idee bună să le convertiți într-un tabel Excel. Este mult mai ușoară gestionarea datelor și, de asemenea, permite utilizarea ușoară a unor instrumente precum Power Query și Power Pivot.
Funcționarea tabelelor Excel are beneficii precum referințe structurate (ceea ce face foarte ușor să faceți referire la datele din tabel și să le utilizați în formule) și ajustarea automată a referințelor în cazul în care adăugați sau ștergeți date din tabel.
Deși puteți utiliza în continuare formula de mai sus pe care v-am arătat-o într-un tabel Excel, permiteți-mi să vă arăt câteva metode mai bune pentru a face acest lucru.
Să presupunem că aveți un tabel Excel așa cum se arată mai jos și doriți să calculați totalul de rulare în coloana C.
Mai jos este formula care va face acest lucru:
= SUM (SalesData [[# Headers], [Sale]]: [@ Sale])
Formula de mai sus poate părea puțin lungă, dar nu trebuie să o scrieți singur. ceea ce vedeți în formula sumă se numește referințe structurate, care este modalitatea eficientă a Excel de a face referire la anumite puncte de date într-un tabel Excel.
De exemplu, SalesData [[# Headers], [Sale]] se referă la antetul Sale din tabelul SalesData (SalesData este numele tabelului Excel pe care l-am dat când am creat tabelul)
Și [@Sale] se referă la valoarea din celula din același rând din coloana Vânzare.
Tocmai am explicat acest lucru aici pentru înțelegerea dvs., dar chiar dacă nu știți nimic despre referințele structurate, puteți crea cu ușurință această formulă.
Mai jos sunt pașii pentru a face acest lucru:
- În celula C2, introduceți = SUM (
- Selectați celula B1, care este antetul coloanei care are valoarea de vânzare. Puteți utiliza mouse-ul sau săgețile. Veți observa că Excel introduce automat referința structurată pentru celula respectivă
- Adăugați un: (simbol colon)
- Selectați celula B2. Excel va introduce din nou automat referința structurată pentru celulă
- Închideți paranteză și apăsați Enter
De asemenea, veți observa că nu trebuie să copiați formula în întreaga coloană, un tabel Excel o face automat pentru dvs.
Un alt lucru extraordinar despre această metodă este că, în cazul în care adăugați o nouă înregistrare în acest set de date, tabelul Excel va calcula automat totalul de rulare pentru toate noile înregistrări.
Deși am inclus antetul coloanei în formula noastră, amintiți-vă că formula ar ignora textul antetului și va lua în considerare doar datele din coloană
Calculul rulării totale utilizând Power Query
Power Query este un instrument uimitor atunci când vine vorba de conectarea cu baze de date, extragerea datelor din mai multe surse și transformarea acestora înainte de a le introduce în Excel.
Dacă lucrați deja cu Power Query, ar fi mai eficient să adăugați totaluri de rulare în timp ce transformați datele în editorul Power Query în sine (în loc să primiți primele date în Excel și apoi să adăugați totalurile de rulare folosind oricare dintre cele de mai sus metode acoperite mai sus).
Deși nu există nicio funcție încorporată în Power Query pentru a adăuga totaluri de rulare (ceea ce mi-aș dori să existe), puteți face acest lucru folosind o formulă simplă.
Să presupunem că aveți un tabel Excel așa cum se arată mai jos și doriți să adăugați totalurile de rulare la aceste date:
Mai jos sunt pașii pentru a face acest lucru:
- Selectați orice celulă din tabelul Excel
- Faceți clic pe Date
- În fila Obțineți și transformați, faceți clic pe pictograma din tabel / interval. Aceasta va deschide tabelul în editorul Power Query
- [Opțional] În cazul în care coloana Dată nu este deja sortată, faceți clic pe pictograma filtrului din coloana Dată, apoi faceți clic pe Sortare crescătoare
- Faceți clic pe fila Adăugați coloană din editorul Power Query
- În grupul General, faceți clic pe meniul derulant Coloană index (nu faceți clic pe pictograma Coloană index, ci pe săgeata mică și neagră înclinată chiar lângă ea pentru a afișa mai multe opțiuni)
- Faceți clic pe opțiunea „De la 1”. Dacă faceți acest lucru, se va adăuga o nouă coloană index care ar începe de la una și va introduce numere care cresc cu 1 în întreaga coloană
- Faceți clic pe pictograma „Coloană personalizată” (care se află și în fila Adăugați coloană)
- În caseta de dialog a coloanei personalizate care se deschide, introduceți un nume pentru noua coloană. în acest exemplu, voi folosi numele „Total rulare”
- În câmpul Formula coloanei personalizate, introduceți formula de mai jos: List.Sum (List.Range (# „Index adăugat” [Vânzare], 0, [Index]))
- Asigurați-vă că există o casetă de selectare în partea de jos a casetei de dialog care spune - „Nu au fost detectate erori de sintaxă”
- Faceți clic pe OK. Aceasta ar adăuga o nouă coloană totală care rulează
- Eliminați coloana Index
- Faceți clic pe fila Fișier, apoi faceți clic pe „Închidere și încărcare”
Pașii de mai sus ar insera o foaie nouă în registrul dvs. de lucru cu un tabel care conține totalurile curente.
Acum, dacă vă gândiți că aceștia sunt doar prea mulți pași în comparație cu metodele anterioare de utilizare a formulelor simple, aveți dreptate.
Dacă aveți deja un set de date și tot ce trebuie să faceți este să adăugați totaluri de rulare, este mai bine să nu utilizați Power Query.
Utilizarea Power Query are sens în cazul în care trebuie să extrageți date dintr-o bază de date sau să combinați date din mai multe registre de lucru diferite și, în acest proces, adăugați și totaluri de rulare.
De asemenea, odată ce faceți această automatizare utilizând Power Query, data viitoare când se modifică setul de date, nu trebuie să o mai faceți din nou, pur și simplu puteți reîmprospăta interogarea și vă va oferi rezultatul pe baza noului set de date.
Cum funcționează asta?
Acum permiteți-mi să explic rapid ce se întâmplă în această metodă.
Primul lucru pe care îl facem în editorul Power Query este să introducem o coloană index începând de la una și crescând cu una pe măsură ce coboară celulele.
Facem acest lucru pentru că trebuie să folosim această coloană în timp ce calculăm totalul de rulare într-o altă coloană pe care o inserăm în pasul următor.
Apoi, inserăm o coloană personalizată și folosim formula de mai jos
List.Sum (List.Range (# "Index adăugat" [Vânzare], 0, [Index]))
Aceasta este o formulă List.Sum care vă va oferi suma intervalului specificat în cadrul acestuia.
Și acest interval este specificat folosind funcția List.Range.
Funcția List.Range oferă intervalul specificat în coloana de vânzare ca rezultat și acest interval se modifică pe baza valorii Indexului. De exemplu, pentru prima înregistrare, gama ar fi pur și simplu prima valoare de vânzare. Și pe măsură ce coborâți în celule, acest interval se va extinde.
Deci, pentru prima celulă. List.Sum vă va oferi doar suma primei valori de vânzare și, pentru a doua celulă, vă va oferi suma pentru primele două valori de vânzare și așa mai departe.
În timp ce această metodă funcționează bine, devine foarte lentă cu seturi de date mari - mii de rânduri. Dacă aveți de-a face cu un set mare de date și doriți să adăugați totaluri de rulare, consultați acest tutorial care prezintă alte metode care sunt mai rapide.Calculul alergării totale pe baza criteriilor
Până acum, am văzut exemple în care am calculat totalul de rulare pentru toate valorile dintr-o coloană.
Dar ar putea exista cazuri în care doriți să calculați totalul de rulare pentru înregistrări specifice.
De exemplu, mai jos am un set de date și vreau să calculez totalul de rulare pentru imprimante și scanere separat, în două coloane diferite.
Acest lucru se poate face folosind o formulă SUMIF care calculează totalul de rulare, asigurându-se în același timp că este îndeplinită condiția specificată.
Mai jos este formula care va face acest lucru pentru coloanele Printer:
= SUMIF ($ C $ 2: C2, $ D $ 1, $ B $ 2: B2)
În mod similar, pentru a calcula totalul de rulare pentru scanere, utilizați formula de mai jos:
= SUMIF ($ C $ 2: C2, $ E $ 1, $ B $ 2: B2)
În formulele de mai sus, am folosit SUMIF, care mi-ar da suma într-un interval atunci când sunt îndeplinite criteriile specificate.
Formula ia trei argumente:
- gamă: acesta este intervalul de criterii care ar fi verificat în raport cu criteriile specificate
- criterii: acesta este criteriul care ar fi verificat numai dacă acest criteriu este îndeplinit, apoi s-ar adăuga valorile din al treilea argument, care este intervalul sumă
- [sum_range]: acesta este intervalul de sumă din care s-ar adăuga valorile dacă sunt îndeplinite criteriile
De asemenea, în gamă și gamă_sumă argument, am blocat a doua parte a referinței, astfel încât, pe măsură ce coborâm în celule, gama să se extindă în continuare. Acest lucru ne permite să luăm în considerare și să adăugăm valori până la acel interval (deci rulăm totaluri).
În această formulă, am folosit coloana antet (Printer and Scanner) ca criterii. puteți, de asemenea, să codificați criteriile dacă anteturile de coloană nu sunt exact aceleași cu textul criteriilor.
În cazul în care aveți mai multe condiții pe care trebuie să le verificați, puteți utiliza formula SUMIFS.
Rularea totală în tabelele pivot
Dacă doriți să adăugați totaluri de rulare într-un rezultat al tabelului pivot, puteți face acest lucru cu ușurință utilizând o funcționalitate încorporată în tabelele pivot.
Să presupunem că aveți un tabel pivot așa cum se arată mai jos, unde am data într-o coloană și valoarea de vânzare în cealaltă coloană.
Mai jos sunt pașii pentru a adăuga o coloană suplimentară, care va afișa numărul total al vânzărilor în funcție de dată:
- Trageți câmpul Vânzare și puneți-l în zona Valoare.
- Aceasta va adăuga o altă coloană cu valorile de vânzări
- Faceți clic pe opțiunea Sumă de vânzare2 din zona Valoare
- Faceți clic pe opțiunea „Setări câmp valoric”
- În caseta de dialog Setări câmp valoric, schimbați numele personalizat în „Totaluri rulante”
- Faceți clic pe fila „Afișați valoarea ca”
- În meniul derulant Afișați valoarea ca, selectați opțiunea „Executare totală în”
- În opțiunile câmpului de bază, asigurați-vă că Data este selectată
- Faceți clic pe Ok
Pașii de mai sus ar schimba cea de-a doua coloană de vânzare în coloana Total curent.
Deci, acestea sunt câteva dintre modalitățile pe care le puteți utiliza pentru a calcula totalul de rulare în Excel. Dacă aveți date în format tabelar, puteți utiliza formule simple și, dacă aveți un tabel Excel, puteți utiliza formule care utilizează referințe structurate.
De asemenea, am prezentat modul de calcul al totalului de rulare utilizând Power Query și în tabelele pivot.
Sper că ți s-a părut util acest tutorial.
