Bugetele se realizează la nivelul cel mai înalt - venituri pe linie de produse, în funcție de regiune, în funcție de lună. Actualele se acumulează încet în timp - factură cu factură, element rând cu element rând. Compararea micului fișier bugetar cu voluminoasele date reale a fost o durere pentru totdeauna. Îmi place acest truc de la Rob Collie, alias PowerPivotPro.com.
Pentru a configura exemplul, aveți un tabel de buget de 54 de rânduri: 1 rând pe lună pe regiune per produs.
Fișierul cu facturi este la nivelul de detaliu: 422 de rânduri până în acest an.
Nu există nicio VLOOKUP în lume care să vă permită să vă potriviți cu aceste două seturi de date. Dar, datorită Power Pivot (alias Modelul de date în Excel 2013+), acest lucru devine ușor.
Trebuie să creați mici tabele mici pe care eu le numesc „alători” pentru a lega cele două seturi de date mai mari.
În cazul meu, produsul, regiunea și data sunt comune între cele două tabele. Tabelul Produs este un mic tabel cu patru celule. Idem pentru regiune. Creați fiecare dintre acestea copiind date dintr-un tabel și folosind Remove Duplicates.
Tabelul calendaristic din dreapta a fost de fapt mai greu de creat. Datele bugetare au un rând pe lună, care se încadrează întotdeauna la sfârșitul lunii. Datele facturii arată datele zilnice, de obicei în timpul săptămânii. Așadar, a trebuit să copiez câmpul Data din ambele seturi de date într-o singură coloană și apoi să elimin duplicatele pentru a mă asigura că toate datele sunt reprezentate. Apoi obișnuiam =TEXT(J4,"YYYY-MM")să creez o coloană lunară din datele zilnice.
Dacă nu aveți completarea completă Power Pivot, trebuie să creați un tabel pivot din tabelul Budget și să bifați caseta de selectare pentru Adăugați aceste date la modelul de date.
După cum sa discutat în sfatul anterior, pe măsură ce adăugați câmpuri în tabelul pivot, va trebui să definiți șase relații. În timp ce ați putea face acest lucru cu șase vizite la dialogul Creare relație, am pornit programul de completare Power Pivot și am folosit vizualizarea diagramă pentru a defini cele șase relații.
Iată cheia pentru ca toate acestea să funcționeze: sunteți liber să utilizați câmpurile numerice din Buget și din Actual. Dar dacă doriți să afișați Regiunea, Produsul sau Luna în tabelul pivot, acestea trebuie să provină din tabelele de asociere!
Iată un tabel pivot cu date provenind din cinci tabele. Coloana A provine de la tâmplăria Regiunii. Rândul 2 vine de la tâmplăria Calendar. Feliatorul de produse provine de la produsul tamplar. Numerele bugetului provin din tabelul Buget, iar numerele reale provin din tabelul Facturi.
Acest lucru funcționează deoarece tabelele de asociere aplică filtre tabelului Buget și Actual. Este o tehnică frumoasă și arată că Power Pivot nu este doar pentru big data.
