Recalcularea formulelor în PowerPivot

Când lucrați cu date în Power Pivot, poate fi necesar din când în când să reîmprospătați datele din sursă, să recalculați formulele pe care le-ați creat în coloanele calculate sau să vă asigurați că datele prezentate într-un raport PivotTable sunt actuale.

Acest subiect explică diferența dintre reîmprospătarea datelor și recalcularea datelor, oferă o prezentare generală a modului în care se declanșează 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, dar datele pot fi reîmprospătate manual din fereastra Power Pivot sau automat dacă registrul de lucru este partajat în SharePoint.

Recalcularea înseamnă actualizarea tuturor coloanelor, tabelelor, diagramelor și rapoartelor PivotTable din registrul de lucru care conțin formule. Deoarece recalcularea unei formule atrage un cost de performanță, 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ă, deoarece în timpul recalculării sunt verificate dependențele coloanelor și veți fi notificat 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. Cu toate acestea, 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; Cu toate acestea, vă recomandăm insistent să 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 va declanșa recalcularea întregii coloane care conține o formulă. Următoarele modificări necesită întotdeauna recalcularea formulelor:

• Valorile dintr-o sursă de date externă au fost reîmprospătate.
• Definiția formulei s-a modificat.
• Numele tabelelor sau coloanelor la care se face referire într-o formulă au fost modificate.
• Relațiile dintre tabele au fost adăugate, modificate sau șterse.
• Au fost adăugate noi măsuri sau coloane calculate.
• S-au efectuat modificări la alte formule din registrul de lucru, de aceea coloanele sau calculele care depind de acest calcul ar trebui să fie actualizate.
• S-au inserat sau s-au șters rânduri.
• Ați aplicat un filtru care necesită executarea unei interogări pentru a actualiza setul de date. Filtrul ar fi putut fi aplicat fie într-o formulă, fie ca parte a unui raport PivotTable sau PivotChart.

Utilizarea recalculării manuale

Puteți utiliza recalcularea manuală pentru a nu suporta costuri de calcul ale 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.

Rețineți că, atât timp cât registrul de lucru este setat la modul de calcul manual, Power Pivot în Excel nu efectuează validări sau verificări ale formulelor, cu următoarele rezultate:

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

Pentru a configura registrul de lucru pentru recalculare manuală

1. În Power Pivot, faceți clic pe Calcule de proiectare>> Opțiuni >de calculMod calcul manual.
2. Pentru a recalcula toate tabelele, faceți clic pe Opțiuni >de calculCalculare acum.
   Formulele din registrul de lucru sunt verificate pentru a găsi erori, iar tabelele sunt actualizate cu rezultate, dacă este cazul. În funcție de volumul de date și de numărul de calcule, registrul de lucru poate deveni blocat de răspuns pentru o perioadă de timp.

Depanarea recalculării

Dependențe

Atunci când o coloană depinde de altă coloană și conținutul acelei coloane se modifică în vreun fel, poate fi necesară recalcularea tuturor coloanelor asociate. Ori de câte ori se efectuează modificări la registrul de lucru Power Pivot, Power Pivot în Excel efectuează o analiză a datelor Power Pivot existente 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 la tabelele Produs sau CategorieProdus va determina recalcularea tuturor coloanelor calculate din tabelul Vânzări . Acest lucru are sens dacă luați în considerare că este posibil să aveți formule care cumulează vânzările după categorie sau după produs. Prin urmare, pentru a fi sigur că rezultatele sunt corecte; Formulele bazate pe date trebuie recalculate.

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 precum ș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. Aceasta deoarece numele coloanelor sunt utilizate ca identificatori în formule.

În unele cazuri, Power Pivot poate determina că coloanele pot fi excluse din recalculare. De exemplu, dacă aveți o formulă care caută o valoare cum ar fi [Culoare produs] din tabelul Produse , iar coloana care este modificată 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

Dependențele sunt calculate înainte de orice recalculare. Dacă există mai multe coloane care depind una de cealaltă, Power Pivot urmează secvența de dependențe. Acest lucru asigură faptul că coloanele sunt procesate în ordinea corectă la viteză maximă.

Tranzacții

Operațiunile care recalculează sau reîmprospătează date au loc ca o tranzacție. Acest lucru înseamnă că, dacă o parte a operațiunii de reîmprospătare nu reușește, operațiunile rămase sunt readuse la versiunea anterioară. Acest lucru 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 ar fi NOW, RAND sau TODAY, nu au valori fixe. Pentru a evita problemele de performanță, executarea unei interogări sau filtrarea nu va determina reevaluarea unor astfel de funcții dacă sunt utilizate într-o coloană calculată. Rezultatele pentru aceste funcții sunt recalculate numai când se recalculează întreaga coloană. Aceste situații includ reîmprospătarea de la o sursă externă de date sau editarea manuală a datelor ce determină reevaluarea formulelor care conțin aceste funcții. Cu toate acestea, funcțiile volatile, cum ar fi NOW, RAND sau TODAY, vor fi întotdeauna recalculate dacă funcția este utilizată în definiția unui câmp calculat.