Înțelegerea și crearea tabelelor de date în Power Pivot în Excel

Crearea unui tabel de date în Excel

Puteți să creați un tabel de date în Excel apoi să-l copiați într-un tabel nou din modelul de date. Acest lucru este foarte ușor de făcut și vă oferă multă flexibilitate.

Când creați un tabel de date în Excel, începeți cu o singură coloană cu un interval contiguu de date. Apoi puteți crea coloane suplimentare, cum ar fi An, Trimestru, Lună, An fiscal, Perioadă etc. în foaia de lucru Excel utilizând formule Excel sau, după ce copiați tabelul în Modelul de date, le puteți crea drept coloane calculate. Crearea coloanelor de date suplimentare în Power Pivot este descrisă în secțiunea Adăugarea coloanelor de dată nouă în tabelul de date din cuprinsul ulterior al articolului.

Adăugarea de coloane noi de date în tabelul de date

Un tabel de date cu o singură coloană de date care are un rând pentru fiecare zi pentru fiecare an este important pentru definirea tuturor datelor dintr-un interval de date. De asemenea, este necesar pentru a crea o relație între tabelul de informații și tabelul de date. Dar acea singură coloană de date cu un rând pentru fiecare zi nu este utilă atunci când analizați după date dintr-un raport PivotTable sau Power View. Doriți ca tabelul de date să includă coloane care vă ajută să agregarea datele pentru o zonă sau un grup de date. De exemplu, poate că doriți să însumați vânzările după lună sau trimestru sau să creați o măsură care calculează creșterea de la an la an. În fiecare dintre aceste cazuri, tabelul de date necesită coloane de an, lună sau trimestru care vă permit să agregți datele pentru acea perioadă.

Dacă ați importat tabelul de date dintr-o sursă de date relațională, este posibil ca acesta să includă deja diferitele tipuri de coloane de date dorite. În unele cazuri, este posibil să doriți să modificați unele dintre aceste coloane sau să creați coloane de date suplimentare. Acest lucru este valabil mai ales dacă creați propriul tabel de date în Excel și îl copiați în modelul de date. Din fericire, crearea de noi coloane de date în Power Pivot este destul de simplă cu funcțiile pentru dată și oră din DAX.

Funcțiile DAX pentru dată și oră

Dacă ați lucrat vreodată cu funcții de dată și oră în Excel formule, probabil că veți fi familiarizat cu funcțiile pentru dată și oră. Deși aceste funcții sunt similare cu corespondentele lor din Excel, există câteva diferențe importante:

• Funcțiile DAX Date și Oră utilizează un tip de date datetime.

• Acestea pot lua valori dintr-o coloană ca argument.

• Acestea pot fi utilizate pentru a returna și/sau a manipula valorile de dată.

Aceste funcții sunt utilizate adesea atunci când creați coloane de date particularizate într-un tabel de date, așa că sunt importante de înțeles. Vom utiliza un număr dintre aceste funcții pentru a crea coloane pentru An, Trimestru,Month și așa mai departe.

DAX include următoarele funcții pentru dată și oră:

• DATE

• DATEVALUE

• NEXTDAY

• EDATE

• EOMONTH

• HOUR

• MINUTE

• MONTH

• NOW

• SECOND

• TIME

• TIMEVALUE

• TODAY

• WEEKDAY

• WEEKNUM

• YEAR

• YEARFRAC

Există multe alte funcții DAX pe care le puteți utiliza și în formule. De exemplu, multe dintre formulele descrise aici utilizează funcții matematice și trigonometrice precum MOD și TRUNC,funcții logice ca IFși text, cum ar fi FORMAT Pentru mai multe informații despre alte funcții DAX, consultați secțiunea Resurse suplimentare din acest articol.

Exemple de formule pentru un an calendaristic

Următoarele exemple descriu formulele utilizate pentru a crea coloane suplimentare într-un tabel de date numit Calendar. O coloană, denumită Dată, există deja și conține un interval contiguu de date între 01.01.2010 și 31.12.2016.

Nume lună

=FORMAT([dată],"mmmm")

În această formulă, pentru a obține numele lunii, utilizăm funcția FORMAT pentru a efectua conversia unei valori numerice din coloana Dată în text. Specificăm coloana Dată ca prim argument, apoi formatul; Dorim ca numele lunii să arate toate caracterele, așa că utilizăm "mmmm". Rezultatul nostru arată astfel:

Dacă dorim să returnăm numele lunii abreviat la trei litere, vom utiliza "mmm" în argumentul de formatare.

Ziua săptămânii

=FORMAT([dată],"ddd")

În această formulă, utilizăm funcția FORMAT pentru a obține numele zilei. Pentru că dorim doar un nume abreviat pentru zi, specificăm "ddd" în argumentul de format.

Raport PivotTable eșantion

Odată ce aveți câmpuri pentru date cum ar fi An, Trimestru, Lună etc., le puteți utiliza într-un raport Sau PivotTable. De exemplu, următoarea imagine arată câmpul Dimensiune Vânzări din tabelul Date vânzări în VALORI și An și Trimestru din tabelul de dimensiune Calendar din RÂNDURI. SalesAmount este agregată pentru contextul de an și trimestru.

Exemple de formule pentru un an fiscal

Sărbători sau date speciale

Poate că doriți să includeți o coloană de date care indică faptul că anumite date sunt sărbători sau alte date speciale. De exemplu, poate doriți să însumați totalurile de vânzări pentru Ziua de Anul nou, adăugând un câmp Sărbători într-un PivotTable, ca slicer sau filtrând. În alte cazuri, este posibil să doriți să excludeți acele date din alte coloane de date calendaristice sau într-o măsură.

Includerea sărbătorilor sau zilelor speciale este destul de simplă. Puteți crea un tabel în tabele Excel datele pe care doriți să le includeți. Apoi puteți să copiați sau să utilizați Adăugare la model de date pentru a-l adăuga la modelul de date ca tabel legat. În majoritatea cazurilor, nu este necesar să creați o relație între tabel și tabelul Calendar. Toate formulele care fac referire la ea pot utiliza funcția LOOKUPVALUE pentru a returna valori.

Mai jos este un exemplu de tabel creat în Excel care include sărbători de adăugat la tabelul de date:

În tabelul de date, creăm o coloană denumită Sărbători și utilizăm o formulă ca aceasta:

=LOOKUPVALUE(Sărbători[Sărbători],Sărbători[dată],Calendar[dată])

Să privim mai cu atenție această formulă.

Utilizăm funcția LOOKUPVALUE pentru a obține valori din coloana Sărbători din tabelul Sărbători. În primul argument, specificăm coloana unde va fi valoarea rezultatului nostru. Specificăm coloana Sărbători din tabelul Sărbători, deoarece aceasta este valoarea pe care dorim să o returnăm.

=LOOKUPVALUE(Sărbători[Sărbători],Sărbători[dată],Calendar[dată])

Apoi specificăm al doilea argument, coloana de căutare care are datele pe care doriți să le căutați. Specificăm coloana Dată din tabelul Sărbători, astfel:

=LOOKUPVALUE(Sărbători[Sărbători],Sărbători[dată],Calendar[dată])

În sfârșit, specificăm coloana din tabelul Calendar, care conține datele pe care doriți să le căutați în tabelul Sărbători. Desigur, coloana Data este în tabelul Calendar.

=LOOKUPVALUE(Sărbători[Sărbători],Sărbători[dată],Calendar[dată])

Coloana Sărbători va returna numele sărbătorilor pentru fiecare rând care are o valoare de dată care se potrivește cu o dată din tabelul Sărbători.

Calendar particularizat - treisprezece perioade de patru săptămâni

Unele organizații, cum ar fi serviciile de vânzare cu amănuntul sau pentru alimente, raportează adesea perioade diferite, cum ar fi cele trei perioade de patru săptămâni. Cu un calendar perioadă de patru săptămâni, fiecare perioadă este de 28 de zile; Prin urmare, fiecare perioadă conține patru zile de luni, patru marți, patru miercuri și așa mai departe. Fiecare perioadă conține același număr de zile și, de obicei, sărbătorile se vor încadrează în aceeași perioadă din fiecare an. Puteți alege să începeți un punct în orice zi a săptămânii. La fel ca în cazul datelor dintr-un calendar sau într-un an fiscal, puteți utiliza DAX pentru a crea coloane suplimentare cu date particularizate.

În exemplele de mai jos, prima perioadă completă începe în prima duminică a anului fiscal. În acest caz, anul fiscal începe pe 01.07.

Punct

Perioada din acest calendar particularizat conține 28 de zile și va începe întotdeauna într-o duminică. Această coloană va returna numărul perioadei care începe cu prima duminică din primul an fiscal.

=INT(([Săptămână]+3)/4)

Să privim mai cu atenție această formulă.

Mai întâi, creăm o formulă care returnează o valoare din coloana Săptămână ca un întreg, astfel:

=INT([Săptămână])

Apoi adăugați 3 la acea valoare, astfel:

=INT([Săptămână]+3)

Apoi împărțiți rezultatul la 4, astfel:

=INT(([Săptămână]+3)/4)

Rezultatul arată astfel:

Perioadă An fiscal

Această valoare returnează anul fiscal pentru o perioadă.

=INT(([Punct]+12)/13)+2008

Să privim mai cu atenție această formulă.

Mai întâi, creăm o formulă care returnează o valoare din Punct și adună 12:

= ([Punct]+12)

Împărțim rezultatul la 13, deoarece există întotdeauna 28 de perioade de câte 28 de zile în anul fiscal:

=(([Punct]+12)/13)

Adăugăm 2010, deoarece este primul an din tabel:

=(([Punct]+12)/13)+2010

În fine, utilizăm funcția INT pentru a elimina orice fracțiune a rezultatului și a returna un număr întreg, atunci când este împărțit la 13, astfel:

=INT(([Punct]+12)/13)+2010

Rezultatul arată astfel:

Perioada din Anul Fiscal

Această valoare returnează numărul perioadei, 1 -13, începând cu prima perioadă completă (începând cu duminică) din fiecare an fiscal.

=IF(MOD([Punct],13), MOD([Punct],13),13)

Această formulă este puțin mai complexă, așa că o vom descrie mai întâi într-o limbă pe care o înțelegem mai bine. Această formulă spune că împărțiți valoarea din [Punct] la 13 pentru a obține un număr perioadă (1-13) din an. Dacă acel număr este 0, returnează 13.

Mai întâi, creăm o formulă care returnează restul valorii din punct la 13. Putem utiliza MOD (funcții matematice și trigonometrice) astfel:

=MOD([Punct],13)

Acest lucru, în cea mai mare parte, ne oferă rezultatul dorit, cu excepția cazului în care valoarea pentru Perioadă este 0, deoarece acele date nu se încadrează în primul an fiscal, ca în primele cinci zile din exemplul nostru de tabel de date Calendar. Ne putem ocupa de acest lucru cu o funcție IF. În cazul în care rezultatul este 0, vom returna 13, astfel:

=IF(MOD([Punct],13),MOD([Punct],13),13)

Rezultatul arată astfel:

Mai multe relații

În unele cazuri, poate fi necesar să creați mai multe relații sau să creați mai multe tabele de date. De exemplu, dacă există mai multe câmpuri dată în tabelul Date vânzări, cum ar fi DateKey, ShipDate și ReturnDate, toate pot avea relații cu câmpul Dată din tabelul de date Calendar, dar numai unul dintre acestea poate fi o relație activă. În acest caz, deoarece DateKey reprezintă data tranzacției și, prin urmare, data cea mai importantă, aceasta ar servi cel mai bine ca relație activă. Celelalte au relații inactive.

Următorul raport PivotTable calculează vânzările totale după An fiscal și Trimestru fiscal. O măsură denumită Total vânzări, cu formula Total vânzări:=SUM([Valoare SalesAmount]), este plasată în VALUES, iar câmpurile An Fiscal și FiscalQuarter din tabelul de date Calendar sunt plasate în ROWS.

Acest raport PivotTable direct funcționează corect deoarece dorim să însumăm vânzările totale la data tranzacțieidin DateKey. Măsura noastră Total vânzări utilizează datele din DateKey și este însumată după anul fiscal și trimestrul fiscal, deoarece există o relație între DateKey din tabelul Vânzări și coloana Date din tabelul de date Calendar.

Relații inactive

Dar ce putem face dacă am dori să însumăm totalul vânzărilor nu după data tranzacției, ci după data de livrare? Avem nevoie de o relație între coloana DatăLivrare din tabelul Vânzări și coloana Dată din tabelul Calendar. Dacă nu creăm acea relație, agregările noastre se bazează întotdeauna pe data tranzacției. Cu toate acestea, putem avea mai multe relații, chiar dacă numai una poate fi activă și, deoarece data tranzacției este cea mai importantă, ea primește relația activă cu tabelul Calendar.

În acest caz, ShipDate are o relație inactivă, astfel că orice formulă de măsură creată pentru a agrega date pe baza datelor de livrare trebuie să specifice relația inactivă utilizând funcția USERELATIONSHIP.

De exemplu, deoarece există o relație inactivă între coloana DatăLivrare din tabelul Vânzări și coloana Dată din tabelul Calendar, putem crea o măsură care însumează totalul vânzărilor după data de livrare. Utilizăm o formulă ca aceasta pentru a specifica relația de utilizat:

Total vânzări după data de livrare:=CALCULATE(SUM(Vânzări[Valoare SalesAmount]), USERELATIONSHIP(Vânzări[DatăLivrare], Calendar[Dată]))

Această formulă eșuează: Calculați o sumă pentru Cantitatea SalesAmount, dar filtrați utilizând relația dintre coloana DatăLivrare din tabelul Vânzări și coloana Dată din tabelul Calendar.

Acum, dacă creăm un raport PivotTable și am pus măsura Total vânzări după data de livrare în VALORI și An fiscal și Trimestru fiscal pe RÂNDURI, vedem același Total general, dar toate celelalte sume pentru anul fiscal și trimestrul fiscal sunt diferite, deoarece se bazează pe data de livrare și nu pe data tranzacției.

Utilizarea relațiilor inactive vă permite să utilizați un singur tabel de date, dar necesită ca orice măsuri (cum ar fi Total vânzări după data de livrare) să facă referire la relația inactivă din formula sa. Există o altă alternativă, mai ales utilizarea mai multor tabele de date.

Mai multe tabele de date

Altă modalitate de a lucra cu mai multe coloane de date în tabelul de date este să creați mai multe tabele de date și să creați relații active separate între ele. Să privim din nou exemplul cu tabelul Vânzări. Avem trei coloane cu date pentru care am putea dori să agregați date:

• O DateKey cu data de vânzare pentru fiecare tranzacție.

• O DatăLivrare - cu data și ora la care articolele vândute au fost expediate clientului.

• A ReturnDate - with the date and time when one or when one or more item returned was received.

Rețineți, câmpul DateKey cu data tranzacției este cel mai important. Vom face majoritatea agregările noastre pe baza acestor date, așa că cu siguranță ne vom dori o relație între ea și coloana Dată din tabelul Calendar. Dacă nu doriți să creăm relații inactive între DatăLivrare și DatăLivrare și câmpul Dată din tabelul Calendar, necesitând astfel formule de măsură speciale, putem crea tabele de date suplimentare pentru data de livrare și data de returnare. Apoi putem crea relații active între ele.

În acest exemplu, am creat un alt tabel de date numit ShipCalendar. Desigur, acest lucru înseamnă și crearea de coloane de date suplimentare și, deoarece aceste coloane de date sunt într-un alt tabel de date, dorim să le depăsim prin faptul că le diferențiază de aceleași coloane din tabelul Calendar. De exemplu, am creat coloane denumite An ShipYear, ShipMonth, ShipQuarter etc.

Dacă creăm raportul PivotTable și am pus măsura Total vânzări în VALUES și ShipFiscalYear și ShipFiscalQuarter pe ROWS, vedem aceleași rezultate pe care le-am văzut atunci când am creat o relație inactivă și un câmp calculat Total vânzări în funcție de data de livrare.

Fiecare dintre aceste abordări necesită atenție. Atunci când utilizați mai multe relații cu un singur tabel de date, poate fi nevoie să creați măsuri speciale care tranzitează relațiile inactive utilizând funcția USERELATIONSHIP. Pe de altă parte, crearea mai multor tabele de date poate fi derutantă într-o Listă de câmpuri și, deoarece aveți mai multe tabele în modelul de date, va fi nevoie de mai multă memorie. Experimentați cu ceea ce funcționează cel mai bine pentru voi.

Cum să: Setați proprietatea Tabel de date

1. În fereastra PowerPivot de date, selectați tabelul Calendar.

2. Pe fila Proiectare, faceți clic pe Marcare ca tabel de date.

3. În caseta de dialog Marcare ca tabel dată, selectați o coloană cu valori unice și tipul de date Dată.

Conversia datelor de tip de date text într-un tip de date dată

În unele cazuri, un tabel de informații cu date despre tranzacții poate conține date de tip de date text. Aceasta înseamnă că o dată care apare ca 2012-12-04T11:47:09 este de fapt nu este deloc o dată sau cel puțin nu tipul de dată pe care Power Pivot îl poate înțelege. Este, de fapt, doar un text care citește ca o dată. Pentru a crea o relație între o coloană de date din tabelul de date și o coloană de date dintr-un tabel de date, ambele coloane trebuie să fie de tipul Date.

De obicei, când încercați să modificați tipul de date pentru o coloană de date care sunt de tip text într-un tip de date dată, Power Pivot poate interpreta datele și le poate transforma automat într-un tip de date dată adevărat. Dacă Power Pivot nu poate face o conversie a tipului de date, veți primi o eroare de nepotrivire tip.

Cu toate acestea, datele se pot transforma într-un tip adevărat de date. Puteți să creați o nouă coloană calculată și să utilizați o formulă DAX pentru a analiza anul, luna, ziua, ora etc. din șirurile text, apoi să o concatenați la loc într-un mod în care Power Pivot poate citi ca dată reală.

În acest exemplu, am importat un tabel de date denumit Vânzări în Power Pivot. Conține o coloană numită DateTime. Valorile apar astfel:

Dacă privim Tipul de date în fila Pornire din grupul Formatare din Power Pivot, vedem că acesta este tip de date Text.

Nu putem crea o relație între coloana DateTime și coloana Data din tabelul de date, deoarece tipurile de date nu se potrivesc. Dacă încercăm să schimbăm tipul de date la Dată,apare o eroare de nepotrivire tip:

În acest caz, Power Pivot nu a putut efectua conversia tipului de date de la text la dată. Putem utiliza în continuare această coloană, dar pentru a o transforma într-un tip adevărat de date, trebuie să creăm o coloană nouă care analiza textul și îl creează din nou într-o valoare Pe care Power Pivot o poate crea într-un tip de date Dată.

Rețineți: în secțiunea Lucrul cu timpul de mai sus din acest articol; dacă nu este necesar ca analiza să fie la un nivel de precizie a zilei, trebuie să efectuați conversia datelor din tabelul de date într-un nivel de precizie a zilei. Cu acest lucru în minte, dorim ca valorile din noua coloană să fie la nivelul de precizie al zilei (excluzând ora). Putem atât să facem conversia valorilor din coloana DatăOră într-un tip de date date, cât și să eliminăm nivelul de precizie al orei cu următoarea formulă:

=DATE(LEFT([DatăOră],4), MID([DatăOră],6,2), MID([DatăOră],9,2))

Acest lucru ne oferă o coloană nouă (în acest caz, denumită Dată). Power Pivot chiar detectează valorile ca date și setează tipul de date automat la Dată.

Dacă dorim să păstrăm nivelul de timp de precizie, extindem formula pentru a include orele, minutele și secundele.

=DATE(LEFT([DatăOră],4), MID([DatăOră],6,2), MID([DatăOră],9,2)) +

TIME(MID([DatăOră],12,2), MID([DatăOră],15,2), MID([DatăOră],18,2))

Acum, că avem o coloană Dată din tipul de date Dată, putem crea o relație între aceasta și o coloană de date într-o dată.

Resurse suplimentare

Datele calendaristice în PowerPivot

Calculele în Power Pivot

Introducere rapidă: Aflați noțiunile de bază despre DAX în 30 de minute

Referința data analysis expressions

Centrul de resurse DAX