Î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 și apoi să îl copiați într-un tabel nou în modelul de date. Acest lucru este într-adevăr destul de ușor de făcut și vă oferă o mulțime de 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 ca coloane calculate. Crearea coloanelor de date suplimentare în Power Pivot este descrisă în secțiunea Adăugarea de coloane de dată noi în tabelul de date din continuarea acestui articol.

Adding new date columns to the date table

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 să creați o relație între tabelul cu informații și tabelul de date. Dar acea coloană de dată unică cu un singur rând pentru fiecare zi nu este utilă atunci când analizați după datele dintr-un raport PivotTable sau Power View. Doriți ca tabelul de date să includă coloane care să vă ajute să agregați datele pentru un interval sau un grup de date. De exemplu, poate 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ă agregați datele pentru acea perioadă.

Dacă ați importat tabelul de date dintr-o sursă de date relațională, acesta poate include deja tipurile diferite de coloane de dată dorite. În unele cazuri, se recomandă să modificați unele dintre aceste coloane sau să creați coloane de dată 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 coloane de date noi î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 formule excel, probabil că veți fi familiarizat cu Funcțiile pentru dată și oră. Deși aceste funcții sunt similare cu cele din Excel, există câteva diferențe importante:

• Funcțiile DAX Dată și Oră utilizează un tip de date dată-oră.

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

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

Aceste funcții sunt utilizate adesea atunci când creați coloane de dată particularizate într-un tabel de date, astfel încât sunt importante pentru a fi înțelese. Vom utiliza o serie de aceste funcții pentru a crea coloane pentru An, Trimestru, Luna Fiscală și așa mai departe.

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

• DATĂ

• DATEVALUE

• ZIUA URMĂTOARE

• DATĂ DE EDATE

• LUNA EOMONTH

• ORĂ

• MINUT

• LUNĂ

• ACUM

• SECUNDĂ

• ORĂ

• TIMEVALUE

• AZI

• ZI A SĂPTĂMÂNII

• NUMĂR DE SĂPTĂMÂNI

• AN

• 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 , cum ar fi MOD și TRUNC, funcții logice precum IF și funcții text precum FORMAT Pentru mai multe informații despre alte funcții DAX, consultați secțiunea Resurse suplimentare din continuarea acestui articol.

Exemple de formule pentru un an calendaristic

Următoarele exemple descriu formulele utilizate pentru a crea coloane suplimentare într-un tabel de date denumit 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 noastre să afișeze 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 format.

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 de zi prescurtat, specificăm "ddd" în argumentul 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 VolumVânzare din tabelul Date vânzări din VALORI și An și Trimestru din tabelul dimensiune Calendar din RÂNDURI. VolumVânzare este agregat pentru contextul de an și trimestru.

Exemple de formule pentru un an fiscal

Sărbători sau date speciale

Se recomandă 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 vânzărilor pentru anul nou, adăugând un câmp Sărbători într-un raport PivotTable, ca slicer sau filtru. În alte cazuri, este posibil să doriți să excludeți acele date din alte coloane de date sau dintr-o măsură.

Inclusiv sărbători sau zile speciale este destul de simplu. Puteți crea în Excel un tabel care conține datele pe care doriți să le includeți. Apoi puteți să copiați sau să utilizați Adăugare la modelul 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. Orice formule care fac referire la aceasta pot utiliza funcția LOOKUPVALUE pentru a returna valori.

Mai jos se află un exemplu de tabel creat în Excel, care include sărbătorile 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ă analizăm mai atent 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. 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 conține datele pe care dorim să le căutăm. 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 dorim să le căutăm în tabelul Sărbători . Aceasta este, desigur, coloana Dată din 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 serviciul de vânzare cu amănuntul sau cel alimentar, raportează adesea perioade diferite, cum ar fi 13 perioade de patru săptămâni. Cu un calendar de 13 perioade de patru săptămâni, fiecare perioadă este de 28 de zile; Prin urmare, fiecare perioadă conține patru 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 încadra în aceeași perioadă în 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 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 la 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âna]+3)/4)

Să analizăm mai atent 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âna])

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

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

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

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

Rezultatul arată astfel:

An fiscal perioadă

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

=INT(([Perioadă]+12)/13)+2008

Să analizăm mai atent această formulă.

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

= ([Perioadă]+12)

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

=(([Perioadă]+12)/13)

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

=(([Perioadă]+12)/13)+2010

În cele din urmă 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(([Perioadă]+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([Perioadă],13), MOD([Perioadă],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ă arată, împărțiți valoarea din [Perioadă] la 13 pentru a obține un număr de perioadă (1-13) din an. Dacă acel număr este 0, atunci returnează 13.

Mai întâi, creăm o formulă care returnează restul valorii din Punct cu 13. Putem utiliza funcțiile MOD (matematică și trigonometrică) astfel:

=MOD([Perioadă],13)

Aceasta, î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 ale exemplului de tabel de date calendar. Ne putem ocupa de acest lucru cu o funcție IF. În cazul în care rezultatul nostru este 0, vom returna 13, astfel:

=IF(MOD([Perioadă],13),MOD([Perioadă],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 de date în tabelul Informații 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 una dintre acestea poate fi o relație activă. În acest caz, deoarece DateKey reprezintă data tranzacției și, prin urmare, cea mai importantă dată, aceasta ar servi cel mai bine drept relație activă . Ceilalți au relații inactive.

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

Acest Raport PivotTable direct funcționează corect, deoarece dorim să însumăm totalul vânzărilor noastre până la data tranzacției din 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 Dată din tabelul de date calendar.

Relații inactive

Dar dacă am dori să însumăm totalul vânzărilor nu după data tranzacției, ci după data livrării? Avem nevoie de o relație între coloana DatăExpediere 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 obține relația activă cu tabelul Calendar.

În acest caz, ShipDate are o relație inactivă, astfel încât 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ăExpediere din tabelul Vânzări și coloana Dată din tabelul Calendar, putem crea o măsură care însumează totalul vânzărilor după data livrării. Utilizăm o formulă ca aceasta pentru a specifica relația de utilizat:

Total vânzări după data livrării:=CALCULATE(SUM(Vânzări[VolumVânzări]), USERELATIONSHIP(Vânzări[DatăExpediere], Calendar[Dată]))

Această formulă arată pur și simplu: Calculați o sumă pentru VolumVânzări, dar filtrați utilizând relația dintre coloana DatăExpediere din tabelul Vânzări și coloana Dată din tabelul Calendar.

Acum, dacă creăm un raport PivotTable și punem măsura Total vânzări după data livrării în VALORI, An fiscal și Trimestru fiscal în RÂNDURI, vedem același Total general, dar toate celelalte sume pentru anul fiscal și trimestrul fiscal sunt diferite, deoarece sunt bazate pe data livrării ș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 livrării) să facă referire la relația inactivă în formula sa. Există o altă alternativă, mai exact, utilizarea mai multor tabele de date.

Tabele de date multiple

Altă modalitate de a lucra cu mai multe coloane de date în tabelul de informații este să creați mai multe tabele de date și să creați relații active separate între ele. Să ne uităm din nou la exemplul de tabel Vânzări. Avem trei coloane cu date pe care am putea dori să agregăm date:

• Un DateKey cu data vânzării pentru fiecare tranzacție.

• O DatăExpediere - cu data și ora când au fost expediate articolele vândute către client.

• ReturnDate - cu data și ora la care a fost primit unul sau mai multe elemente returnate.

Rețineți că este cel mai important câmpul DateKey cu data tranzacției. Vom face majoritatea agregărilor pe baza acestor date, așa că, mai mult ca sigur, ne vom dori o relație între aceasta și coloana Dată din tabelul Calendar. Dacă nu dorim să creăm relații inactive între ShipDate și ReturnDate și câmpul Date din tabelul Calendar, necesitând astfel formule de măsuri speciale, putem crea tabele de date suplimentare pentru data livrării și data returnării. Apoi putem crea relații active între ele.

În acest exemplu, am creat un alt tabel de date denumit ShipCalendar. Aceasta înseamnă, desigur, și crearea de coloane de dată suplimentare și, deoarece aceste coloane de dată se află într-un alt tabel de date, dorim să le denumim astfel încât să se diferențieze de aceleași coloane din tabelul Calendar. De exemplu, am creat coloane denumite AnExpediator, ShipMonth, ShipQuarter etc.

Dacă creăm raportul PivotTable și punem 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 special calculat Total vânzări după data livrării.

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 necesar 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 necesita mai multă memorie. Experimentați cu ceea ce funcționează cel mai bine pentru dvs.

Cum se face: Setarea proprietății Tabel de date

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

2. Pe fila Proiectare , faceți clic pe Marcare ca tabel dată.

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

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

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

De obicei, atunci când încercați să modificați tipul de date pentru o coloană de date care sunt de tip de date 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 efectua o conversie a tipului de date, veți primi o eroare de nepotrivire de tip.

Totuși, puteți efectua conversia datelor într-un tip de date dată adevărat. 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 de text, apoi să o concatenați într-un mod în care Power Pivot poate citi ca dată reală.

În acest exemplu, am importat un tabel cu informații denumit Vânzări în Power Pivot. Aceasta conține o coloană denumită DatăTime. Valorile apar astfel:

Dacă ne uităm la Tipul de date în fila Pornire din grupul Formatare din Power Pivot, vedem că este de tip de date Text.

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

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

Rețineți, din secțiunea Lucrul cu timpul, anterior în acest articol; cu excepția cazului în care este necesar ca analiza să fie la un nivel de precizie de timp din zi, ar trebui să efectuați conversia datelor din tabelul de informații la un nivel de precizie de zi. Cu acest lucru în minte, dorim ca valorile din noua noastră coloană să fie la nivelul de precizie al zilei (excluzând timpul). Amândoi putem efectua conversia valorilor din coloana DateTime într-un tip de date dată și putem elimina nivelul de precizie al timpului cu următoarea formulă:

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

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

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

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

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

Acum, că avem o coloană Dată cu tipul de date Dată, putem crea o relație între acesta ș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

Data Analysis Expressions Reference

Centrul de resurse DAX