Recalcularea formulelor în PowerPivot

Când lucrați cu date în Power Pivot, poate fi necesar să reîmprospătați datele din sursa acestora, să recalculați formulele din coloanele calculate sau să actualizați rapoartele PivotTable care utilizează modelul de date.

Acest articol explică diferența dintre reîmprospătarea datelor și recalculare, oferă o prezentare generală a modului în care este declanșată recalcularea și descrie opțiunile dvs. pentru controlul recalculării.

Înțelegerea reîmprospătării datelor versus recalcularea

Power Pivot utilizează atât reîmprospătarea datelor, cât și recalcularea:

Reîmprospătarea datelor înseamnă obținerea de date actualizate din surse de date externe. Power Pivot nu detectează automat modificările din sursele de date externe. Pentru a prelua date actualizate, reîmprospătați modelul de date.

Recalcularea înseamnă actualizarea tuturor coloanelor, tabelelor, diagramelor și rapoartelor PivotTable din registrul de lucru care conțin formule. Deoarece recalcularea unei formule afectează performanța, este important să înțelegeți dependențele asociate cu fiecare calcul.

Recalculare manuală vs. automată

În mod implicit, Power Pivot recalculează automat după cum este necesar, optimizând în același timp timpul necesar pentru procesare. Deși recalcularea poate necesita timp, este o activitate importantă. În timpul recalculării, Power Pivot verifică dependențele coloanelor și vă notifică dacă o coloană s-a modificat, dacă datele nu sunt valide sau dacă apare o eroare într-o formulă care obișnuia să funcționeze. Totuși, puteți alege să renunțați la validare și să actualizați calculele numai manual, mai ales dacă lucrați cu formule complexe sau seturi foarte mari de date și doriți să controlați momentul actualizărilor.

Atât modul manual, cât și modul automat au avantaje. Totuși, utilizați modul de recalculare automată. Acest mod menține sincronizate metadatele Power Pivot și previne problemele cauzate de ștergerea datelor, de modificări ale numelor sau tipurilor de date sau de dependențele lipsă. 

Utilizarea recalculării automate

Când utilizați modul de recalculare automată, orice modificare a datelor care ar determina modificarea rezultatului oricărei formule declanșează recalcularea întregii coloane care conține o formulă. Următoarele modificări necesită întotdeauna recalcularea formulelor:

• Valorile dintr-o sursă de date externă sunt reîmprospătate.
• Definiția formulei s-a modificat.
• Numele tabelelor sau coloanelor la care se face referire într-o formulă se modifică.
• Relațiile dintre tabele sunt adăugate, modificate sau șterse.
• Se adaugă noi măsuri sau coloane calculate.
• Alte formule din registrul de lucru se efectuează modificări, astfel încât coloanele sau calculele care depind de acest calcul ar trebui să fie actualizate.
• Rândurile sunt inserate sau șterse.
• Ați aplicat un filtru care necesită executarea unei interogări pentru a actualiza setul de date. Filtrul poate fi aplicat fie într-o formulă, fie ca parte a unui raport PivotTable sau PivotChart.

Utilizarea recalculării manuale

Utilizați recalcularea manuală pentru a evita suportul costurilor de calcul al rezultatelor formulei până când sunteți pregătit. Modul manual este util în special în aceste situații:

• Proiectați o formulă utilizând un șablon și doriți să modificați numele coloanelor și tabelelor utilizate în formulă înainte de a o valida.
• Știți că unele date din registrul de lucru s-au modificat, dar lucrați cu o altă coloană care nu s-a modificat, așa că doriți să amânați o recalculare.
• Lucrați într-un registru de lucru care are multe dependențe și doriți să amânați recalcularea până când sunteți sigur că au fost efectuate toate modificările necesare.

Dacă setați registrul de lucru la modul de calcul manual, Power Pivot în Excel nu efectuează validarea sau verificarea formulelor. Au loc următoarele rezultate:

• Toate formulele noi adăugate în registrul de lucru sunt semnalizate ca conținând o eroare.
• Nu apar rezultate în coloanele calculate noi.

Pentru a configura registrul de lucru pentru recalculare manuală

1. În Power Pivot, selectațiCalcule de proiectare>>Opțiuni de calcul Mod>de calcul manual.
2. Pentru a recalcula toate tabelele, selectați Opțiuni >de calculCalculați acum.
   Registrul de lucru verifică dacă există erori în formule și actualizează tabelele cu rezultate. În funcție de volumul de date și de numărul de calcule, registrul de lucru poate înceta temporar să mai răspundă.

Depanarea recalculării

Dependențe

Când o coloană depinde de altă coloană și conținutul acelei coloane se modifică în vreun fel, poate fi necesar să recalculați toate coloanele asociate. Ori de câte ori faceți modificări la registrul de lucru Power Pivot, Power Pivot în Excel analizează datele existente Power Pivot pentru a determina dacă este necesară o recalculare și efectuează actualizarea în cel mai eficient mod posibil.

De exemplu, să presupunem că aveți un tabel, Vânzări, care este asociat cu tabelele, Produs și CategorieProdus; iar formulele din tabelul Vânzări depind de ambele tabele. Orice modificare a tabelelor Produs sau CategorieProdus provoacă recalcularea tuturor coloanelor calculate din tabelul Vânzări . Acest comportament este logic dacă luați în considerare că este posibil să aveți formule care cumulează vânzările după categorie sau după produs. Pentru a vă asigura că rezultatele sunt corecte, trebuie să recalculați formulele pe baza datelor.

Power Pivot efectuează întotdeauna o recalculare completă pentru un tabel, deoarece o recalculare completă este mai eficientă decât verificarea valorilor modificate. Modificările care declanșează recalcularea pot include modificări majore, cum ar fi ștergerea unei coloane, modificarea tipului de date numerice al unei coloane sau adăugarea unei coloane noi. Cu toate acestea, modificări aparent nedorite, cum ar fi modificarea numelui unei coloane, pot declanșa, de asemenea, recalculări. Acest comportament apare deoarece numele coloanelor sunt utilizate ca identificatori în formule.

În unele cazuri, Power Pivot stabilește că poate exclude coloanele din recalculare. De exemplu, dacă aveți o formulă care caută o valoare cum ar fi [Culoare produs] din tabelul Produse , iar coloana pe care ați modificat-o este [Cantitate] în tabelul Vânzări , formula nu trebuie să fie recalculată, chiar dacă tabelele Vânzări și Produse sunt asociate. Cu toate acestea, dacă aveți formule care se bazează pe Vânzări[Cantitate], este necesară recalcularea.

Secvență de recalculare pentru coloanele dependente

Power Pivot calculează dependențele înainte de a recalcula orice coloane. Dacă mai multe coloane depind una de cealaltă, Power Pivot urmează secvența de dependențe. Această secvență asigură faptul că Power Pivot procesează coloanele în ordinea corectă și la viteza maximă.

Tranzacții

Operațiunile care recalculează sau reîmprospătează date au loc ca o tranzacție. Dacă o parte a operațiunii de reîmprospătare nu reușește, operațiunile rămase revin la versiunea anterioară. Acest comportament asigură faptul că datele nu sunt lăsate într-o stare procesată parțial. Nu puteți gestiona tranzacțiile așa cum procedați într-o bază de date relațională și nu puteți crea puncte de verificare.

Recalcularea funcțiilor volatile

Unele funcții, cum NOWar fi , RANDsau TODAY, nu au valori fixe. Pentru a evita problemele de performanță, executarea unei interogări sau filtrarea nu determină, de obicei, reevaluarea acestor funcții dacă le utilizați într-o coloană calculată. Power Pivot recalculează rezultatele pentru aceste funcții doar atunci când recalculează întreaga coloană. Aceste situații includ reîmprospătarea dintr-o sursă externă de date sau editarea manuală a datelor ce determină reevaluarea formulelor care conțin aceste funcții. Totuși, dacă utilizați funcții volatile cum ar fi NOW, RAND, sau TODAY în definiția unui câmp calculat, Power Pivot le recalculează întotdeauna.