probabil că veți întâlni nevoia de a rula totaluri dacă aveți de-a face cu orice fel de date zilnice.

Imaginați-vă că urmăriți vânzările în fiecare zi. Datele dvs. conțin un rând pentru fiecare dată cu o sumă totală de vânzări, dar poate doriți să cunoașteți vânzările totale pentru luna în fiecare zi. Acesta este un total de funcționare, este suma tuturor vânzărilor până la și inclusiv zilele curente de vânzări.

în această postare vom acoperi mai multe moduri de a calcula un total de rulare pentru datele dvs. zilnice. Vom explora modul de utilizare a formulelor foii de lucru, a tabelelor pivot, a pivotului de alimentare cu DAX și a interogării de alimentare.

vom explora, de asemenea, ce se întâmplă cu calculul total care rulează atunci când introduceți sau ștergeți rânduri de date și cum să actualizați rezultatele.

obțineți fișierul cu toate exemplele.

totaluri de rulare cu o formulă simplă

este posibil să creați o formulă totală de rulare de bază folosind operatorul+.

cu toate acestea, va trebui să folosim două formule diferite pentru a face treaba.

1. =C3 va fi prima formulă și va fi doar în primul rând din totalul de rulare.
2. =C4 + D3 va fi în al doilea rând și pot fi copiate în jos rândurile rămase pentru totalul de funcționare.

formula din primul nostru rând nu poate adăuga celula de deasupra ei la total, deoarece conține o valoare text pentru un titlu de coloană. Acest lucru ar provoca o valoare#! eroare care apare în totalul de rulare, deoarece + nu poate gestiona valorile textului. Evităm acest lucru cu o formulă diferită în primul rând care nu face referire la celula de mai sus.

ce se întâmplă cu totalul de rulare atunci când inserăm sau ștergem rânduri în datele noastre?

introducerea unui nou rând va duce la un decalaj în totalul de rulare. Pentru a remedia acest lucru, va trebui să copiem formula în jos din prima celulă de deasupra rândurilor nou inserate până la ultimul rând.

ștergerea oricăror rânduri va duce la #REF! erori de la ștergerea unui rând înseamnă ștergerea unei celule la care se face referire prin formula de sub ea. Pentru a remedia acest lucru, va trebui să copiem formula în jos din ultima celulă fără erori până la ultimul rând.

rularea totalurilor cu o formulă sumă

putem evita stânjenirea utilizării a două formule diferite în coloana noastră totală de rulare utilizând funcția sumă în locul operatorului+. Când funcția SUM întâlnește o celulă de text, o va trata la fel ca și cum ar conține un 0.

în acest fel putem folosi următoarea formulă uniform pentru fiecare rând, inclusiv primul rând.

=SUM(C3,D2)

această formulă va face referire la titlul coloanei care conține text pentru primul rând, dar acest lucru este ok, deoarece este tratat ca un 0.

la introducerea sau ștergerea rândurilor, vom întâmpina în continuare aceleași probleme cu celulele goale și erorile. Le putem remedia la fel ca în cazul totalurilor de rulare în metoda formulei simple.

totaluri rulante cu un interval parțial fix

o altă opțiune cu funcția SUM este de a face referire doar la coloana de vânzări și de a utiliza o referință de interval parțial fixă.

dacă vom folosi următoarea formulă =SUM($C$3:C3), putem copia și lipi acest jos gama. Nu va face referire la niciun titlu de coloană, iar intervalul la care se face referire va crește la fiecare rând.

Din păcate, și aceasta va avea aceleași probleme (și soluții) cu inserarea sau ștergerea rândurilor.

rularea totalurilor cu un interval relativ numit

putem evita problemele cu inserarea și ștergerea rândurilor din datele noastre dacă folosim un interval relativ numit. Aceasta se va referi la celula direct deasupra, indiferent câte rânduri introducem sau ștergem.

acesta este un truc care implică trecerea temporară a stilului de referință Excel de la A1 la R1C1. Apoi definirea unui interval numit folosind notația R1C1. Apoi comutarea stilului de referință înapoi la A1.

în stilul de referință R1C1, celulele sunt menționate prin cât de departe sunt de celulă folosind referința. De exemplu, =RC se referă la celula 2 în sus și 3 din dreapta celulei folosind această formulă.

putem folosi această referință relativă pentru a crea un interval numit care este întotdeauna o celulă deasupra celulei de referință cu formula =RC.

pentru a comuta stilul de referință, accesați fila Fișier, apoi alegeți opțiuni. Accesați secțiunea formulă din meniul Opțiuni Excel și bifați caseta stil de referință R1C1, apoi apăsați butonul OK.

acum putem adăuga gama noastră numită. Accesați fila Formula din Panglica Excel și alegeți comanda definire nume.

introduceți un nume ca „deasupra” ca nume al intervalului. Adăugați formula =RC În se referă la intrare și apăsați butonul OK.

acum putem comuta Excel înapoi la stilul de referință implicit. Accesați fila Fișier > Opțiuni secțiunea formulă > debifați caseta de stil de referință R1C1 > apoi apăsați butonul OK.

acum putem folosi formula =SUM(,de mai sus) în coloana noastră totală de rulare.

intervalul numit de mai sus se va referi întotdeauna la celula direct de mai sus. Când inserăm sau ștergem rânduri, intervalul relativ numit se va ajusta în consecință și nu este necesară nicio acțiune.

de fapt, dacă plasăm datele noastre într-un tabel Excel, atunci formula se va umple automat pentru orice rânduri noi, deoarece formula este uniformă pentru întreaga coloană. Nu este necesară nicio acțiune pentru a copia formulele.

rularea totalurilor cu un tabel Pivot

tabelele Pivot sunt foarte utile pentru rezumarea oricărui tip de date. Există mai mult pentru ei decât adăugarea, numărarea și găsirea mediilor. Există multe alte tipuri de calcule construite în, și există de fapt un calcul total de funcționare!

În primul rând, trebuie să inserăm un tabel pivot bazat pe date. Selectați o celulă din interiorul datelor și accesați fila Inserare și alegeți comanda PivotTable. Apoi treceți prin fereastra Creare PivotTable pentru a alege unde doriți tabelul pivot, fie într-o foaie de lucru nouă, fie undeva într-una existentă.

adăugați câmpul dată în zona rânduri a tabelului pivot, apoi adăugați câmpul vânzări în zona Valori a tabelului pivot. Acum adăugați o altă instanță a câmpului de vânzări în zona rânduri.

ar trebui să avem acum două câmpuri de vânzări identice, unul dintre ele fiind etichetat suma Vânzărilor2. Putem redenumi această etichetă oricând prin simpla tastare peste ea cu ceva de genul rulează Total.

faceți clic dreapta pe oricare dintre valorile din câmpul Sum of Sales2 și selectați Afișare Valoare ca apoi alegeți Running Total In.

vrem să afișăm totalul de rulare după dată, deci în fereastra următoare trebuie să selectăm data ca câmp de bază.

asta e, Avem acum un nou calcul care afișează totalul de funcționare a vânzărilor noastre în interiorul tabelului pivot.

Ce se întâmplă dacă adăugăm sau ștergem un rând în datele noastre sursă, cum afectează acest lucru totalul de rulare? Calculele tabelului pivot sunt dinamice și vor lua în considerare orice date noi în calculul total de funcționare, va trebui doar să reîmprospătăm tabelul pivot.

faceți clic dreapta oriunde în tabelul pivot și alegeți reîmprospătare din meniu.

totaluri de rulare cu Power Pivot și măsuri DAX

primii pași pentru acest lucru sunt exact aceiași folosind un tabel pivot regulat.

Selectați o celulă din date și accesați fila Inserare și alegeți comanda PivotTable.

când veniți la meniul Creare PivotTable, bifați caseta Adăugați aceste date la modelul de date pentru a adăuga datele la modelul de date și a le activa pentru a fi utilizate cu Power pivot.

plasați câmpul dată în zona rânduri și câmpul vânzări în zona Valori din tabelul pivot.

cu Power pivot, va trebui să creăm orice calcule suplimentare pe care le dorim folosind limbajul DAX. Faceți clic dreapta pe numele tabelului din fereastra câmpuri PivotTable, apoi selectați Adăugare măsură pentru a crea un nou calcul. Notă, Acest lucru este disponibil numai cu modelul de date.

=CALCULATE ( SUM ( Sales ), FILTER ( ALL (Sales ), Sales 

acum putem crea noua noastră măsură totală de rulare.

1. În fereastra măsură, trebuie să adăugăm un nume de măsură. În acest caz, putem numi noua măsură ca total de rulare.
2. de asemenea, trebuie să adăugăm formula de mai sus în caseta de formule.
3. lucrul interesant despre Power pivot este capacitatea de a atribui un format de număr unei măsuri. Putem alege formatul valutar pentru măsura noastră. Ori de câte ori folosim această măsură într-un tabel pivot, Formatul va fi aplicat automat.

apăsați butonul OK și noua măsură va fi creată.

va fi afișat un câmp nou în fereastra câmpuri PivotTable. Are o mică pictogramă fx în stânga pentru a indica faptul că este o măsură și nu un câmp regulat în date.

putem folosi acest câmp nou la fel ca orice alt câmp și trageți-l în zona de valori pentru a adăuga calculul nostru total de rulare în tabelul pivot.

ce se întâmplă cu totalul care rulează atunci când adăugăm sau eliminăm date din tabelul sursă? La fel ca un tabel pivot obișnuit, trebuie doar să facem clic dreapta pe tabelul pivot și să selectăm reîmprospătare pentru a actualiza calculul.

totaluri de rulare cu o interogare de alimentare

putem adăuga, de asemenea, totaluri de rulare la datele noastre folosind power query.

Mai întâi trebuie să importăm tabelul în power query. Selectați tabelul de date și accesați fila Date și alegeți opțiunea din tabel/interval. Aceasta va deschide editorul Power query.

în continuare ne putem sorta datele după dată. Acesta este un pas opțional pe care îl putem adăuga, astfel încât, dacă schimbăm ordinea datelor noastre sursă, totalul de rulare va apărea în continuare după dată.

Faceți clic pe comutatorul filtru din titlul coloanei date și alegeți Sortare ascendentă din opțiuni.

trebuie să adăugăm o coloană index. Acest lucru va fi utilizat în calculul total de funcționare mai târziu. Accesați fila Adăugare coloană și faceți clic pe săgeata mică de lângă coloana Index pentru a insera un index începând de la 1 în primul rând.

trebuie să adăugăm o nouă coloană la interogarea noastră pentru a calcula totalul de rulare. Accesați fila Adăugare coloană și alegeți comanda coloană personalizată.

putem denumi coloana ca rulează Total și se adaugă următoarea formulă.

lista.Funcția Range creează o listă de Valori din coloana vânzări începând de la rândul 1 (articolul 0) care se întinde pe un număr de rânduri pe baza valorii din coloana index.

lista.Funcția sumă adaugă apoi această listă de valori care este totalul nostru de funcționare.

nu mai avem nevoie de coloana index, ea și-a îndeplinit scopul și o putem elimina. Faceți clic dreapta pe titlul coloanei și selectați Eliminare din opțiuni.

avem totalul nostru de funcționare și am terminat cu editorul de interogări. Putem închide interogarea și încărca rezultatele într-o nouă foaie de lucru. Accesați fila Acasă a editorului de interogări și apăsați butonul de încărcare Close &.

Ce se întâmplă cu totalul de rulare atunci când adăugăm sau eliminăm rânduri din datele noastre sursă? Va trebui să reîmprospătăm tabelul de ieșire power query pentru a actualiza totalul de rulare cu modificările. Faceți clic dreapta oriunde pe masă și alegeți reîmprospătare pentru a actualiza tabelul.

cu pasul de sortare opțional de mai sus, dacă adăugăm date în ordine la datele sursă, power query va sorta după dată și va returna ordinea corectă după dată pentru totalul de rulare.

concluzii

există multe opțiuni diferite pentru calcularea totalurilor de rulare în Excel.

am explorat opțiuni, inclusiv formule în foaia de lucru, tabele pivot, formule Dax power pivot și power query. Unele oferă o soluție mai robustă atunci când adăugați sau eliminați rânduri din date, alte metode oferă o implementare mai ușoară.

formulele Simple din foaia de lucru sunt ușor de configurat, dar nu se vor ocupa de inserarea sau ștergerea cu ușurință a noilor rânduri de date. Alte soluții, cum ar fi tabelele pivot, DAX și power query, sunt mai robuste și se ocupă ușor de inserarea sau ștergerea rândurilor de date, dar sunt mai greu de configurat.este bine să fii conștient de avantajele și dezavantajele fiecărei metode și să o alegi pe cea mai potrivită. Dacă nu veți insera sau șterge date noi, atunci formulele foii de lucru ar putea fi calea de urmat.