Modelul de date Excel (Power Pivot) vă permite să conectați un set mare de date detaliate de realități la un buget de nivel superior utilizând tabele de asociere.
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 volumul de 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: un 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 bifați caseta 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.
Urmăriți videoclipul
- Aveți un mic set de date bugetare de sus în jos
- Doriți să comparați cu un set de date reale de jos
- Datele reale pot proveni dintr-un registru de facturi
- Modelul de date vă va permite să comparați aceste seturi de date de dimensiuni diferite
- Faceți ambele seturi de date într-un tabel Ctrl + T
- Pentru fiecare câmp text pe care doriți să îl raportați, creați un tabel de asociere
- Copiați valorile și eliminați duplicatele
- Pentru date, puteți include date din ambele tabele și puteți converti la sfârșitul lunii
- Faceți ca elementele de asamblare să fie tabele Ctrl + T
- Opțional, dar util pentru a denumi toate cele cinci tabele
- Creați un tabel pivot din Buget și alegeți Modelul de date
- Construiți un tabel pivot folosind Budget și Actual din tabelele originale
- Toate celelalte câmpuri trebuie să provină din tabelele de asociere
- Adăugați feliere după produs
- Creați trei relații de la Buget la Alăturați
- Creați trei relații de la Actual la Alăturați
- Mâine: cum este mai ușor să construiești relații cu Power Pivot și DAX Formulas
Transcriere video
Aflați Excel din podcast, episodul 2016 - Buget descendent față de actualități de jos în sus!
Hei, podcastez această carte întreagă, dă clic pe „i” din colțul din dreapta sus și urmărește lista de redare.
Hei, voi întrerupe acest lucru, acesta este Bill Jelen de acum 15 minute. Îmi dau seama acum că acesta este un podcast incredibil de lung și sunteți tentați doar să faceți clic direct pe el, dar permiteți-mi să vă ofer acest scurt. Dacă sunteți în Excel 2013 și ați avut vreodată un mic tabel bugetar și un tabel masiv de date reale și trebuie să le mapați împreună, aceasta este o abilitate nouă uimitoare pe care o avem în Excel 2013, pe care nu mulți oameni au explicat-o , și probabil că nu știți despre asta. Dacă ești tu, ești în 2013 și trebuie să mapezi aceste două seturi de date, ia-ți timp, poate astăzi, poate mâine, poate adaugă-l pe lista de urmărire, merită, este o tehnică uimitoare.
Bine, iată ce avem, în partea stângă avem un buget, acest buget, se face la nivel superior, de sus în jos, dreapta pentru fiecare linie de produse, pentru fiecare regiune, pentru fiecare lună, există un buget . Nu sunt multe înregistrări aici, număr de 55, pe partea dreaptă, încercăm să comparăm acest lucru cu realele. Datele reale provin dintr-un registru de facturi, deci avem Regiune, Produs și Venituri, dar sunt facturi individuale, mult mai multe date aici, suntem deja la jumătatea anului și am deja 423 de înregistrări. În regulă, deci cum îi mapezi pe acești 55 pe acești 423? Ar putea fi greu de făcut cu VLOOKUP, ar trebui să rezumați mai întâi, dar din fericire în Excel 2013, modelul de date face acest lucru foarte ușor. Ceea ce avem nevoie pentru a permite acestei mari mese masive să comunice cu această mică masă sunt intermediari, eu îi numesc asocieri.Tabelele mici, Produsul, Regiunea și Calendarul, vom alătura bugetul la aceste trei tabele, vom alătura realul la aceste trei tabele și în mod miraculos, tabelul pivot va funcționa. Bine, deci iată cum facem asta.
În primul rând, trebuie să creez tâmplari, așa că iau acest câmp Produs din coloana A și îl copiez în coloana F, apoi Date, Eliminare duplicate, faceți clic pe OK și rămân cu un mic mic tabel, 1 titlu 3 rânduri. Același lucru pentru regiune, luați regiunile, Ctrl + C, treceți la coloana G, lipiți, eliminați duplicatele, faceți clic pe OK, 3 rânduri 1 antet, bine. Acum, pentru date, datele nu sunt aceleași, acestea sunt date de sfârșit de lună, sunt de fapt stocate ca date de sfârșit de lună și acestea sunt zile lucrătoare. Voi lua ambele liste, Ctrl + C a doua listă și o voi lipi aici, Ctrl + V, apoi voi lua lista mai scurtă, o copiez și o voi lipi mai jos, bine. Și este foarte enervant faptul că, chiar dacă acestea sunt stocate ca date, acestea apar ca luni, iar Remove Duplicates nu le va vedea la fel.Așadar, înainte să folosesc Remove Duplicates, trebuie să îl schimb la o dată scurtă. Alegeți acele date, Date, Eliminați duplicatele, faceți clic pe OK și apoi un pic de sortare aici pentru a le pune în funcțiune.
Bine, acum nu vreau să raportez în funcție de data zilnică, așa că voi adăuga aici o coloană, o coloană de căutare care spune Lună, iar aceasta va fi egală cu ZIUA în acea dată,, 0, care ne va scoate la sfârșitul lunii. Acesta va fi formatat ca dată scurtă și o va copia, bine. Acum, trebuie să transformăm fiecare dintre acestea într-un tabel Ctrl + T, deci de aici Ctrl + T, tabelul meu are anteturi, frumos. Cele mici, nu își dă seama că acestea sunt anteturi acolo, așa că trebuie să ne asigurăm că bifăm acest lucru și Ctrl + T, bine, și numesc aceste tabele Tabelul 1, Tabelul 2, Tabelul 3, nume cu adevărat plictisitoare, nu? Așa că o să le redenumesc și o voi numi BudTable, ProdTable, RegTable, CalTable-ul meu și apoi ActTable, bine.
Începem chiar de la primul tabel și, apropo, nu vom folosi PowerPivot astăzi, vom face toate acestea cu modelul de date. Deci, Excel 2013 sau mai nou, aveți acest Insert, PivotTable, vom bifa caseta „Adăugați aceste date la Modelul de date”, faceți clic pe OK și obținem lista de câmpuri cu butonul magic All, care vă permite aleg din toate cele cinci tabele din registrul de lucru, Actual, Buget, Calendar, Produs, Regiune. Bine, așa că numerele vor veni din tabelul Buget, voi pune bugetul acolo și din tabelul Actual voi pune realul acolo, dar iată ce este restul tabelului pivot. Orice alte câmpuri de text pe care le vom pune în zona rândului sau zona coloanei sau ca feliere, trebuie să provină de la elementele de asamblare, trebuie să provină din acele tabele dintre tabele.
Bine, deci din tabelul Calendar vom lua câmpul Luna respectiv și îl vom pune deasupra, vom ignora alte relații chiar acum. Voi crea relațiile, dar vreau să le creez dintr-o dată. Și tabelul Regiune, puneți regiunile în lateral. Aș putea pune produsele pe partea laterală, dar de fapt voi folosi tabelul Produs ca un feliat, așa că Analizează, Inserează feliatorul, din nou, trebuie să mergi la Toate dacă nu ai folosit încă tabelul Produs. Deci, accesați Toate și veți vedea că produsul este disponibil pentru a crea o felie de felie din produse, așa. Bine acum, în acest moment nu am creat relații, așa că toate aceste numere sunt greșite. Și relațiile pe care trebuie să le creăm, trebuie să creăm 3 tabele din acest mic tabel bugetar, unul către produse, unul către regiuni, unul către calendar,adică 3 relații. Și apoi trebuie să creăm relații de la tabelul Actual la regiunea Produs din Calendar, deci un total de 6 tabele. Și da, acest lucru ar fi cu siguranță mai ușor dacă am avea PowerPivot, dar nu avem sau să presupunem că nu.
Și așa că voi folosi modul de modă veche, dialogul Creare aici, unde avem tabelul Buget în stânga și vom folosi câmpul Regiune și îl vom raporta la tabelul Regiune, câmpul Regiune . Bine, se creează 1/6. Voi alege Creați, din nou din tabelul Buget, mergem la Produs, apoi îl conectez la tabelul Produs, la Produs, faceți clic pe OK. Din tabelul Budget câmpul Date, mergem la tabelul Calendar, iar câmpul Destin, faceți clic pe OK, suntem la jumătatea drumului, bine. Din tabelul Actuals, mergem la Region, la tabelul Region, facem clic pe OK, din tabelul Actuals la Produs și de la tabelul Actuals la Calendar. De fapt, voi lua valorile și o voi face să coboare, bine. Proiectare, aspect raport, afișare în formă tabulară pentru a obține o vizualizare pe care o prefer, Repetați toate etichetele articolelor, bine,acest lucru este absolut uimitor! Acum avem acest mic tabel mic, un număr de 50 de înregistrări în acest tabel cu sute de înregistrări și am creat un singur tabel pivot datorită Modelului de date. Pentru fiecare în care putem vedea bugetul, putem vedea venitul, este împărțit pe regiuni, este împărțit în funcție de lună și este diferențiat după produs.
Acum, acest concept mi-a venit de la Rob Collie, care conduce Power Pivot Pro, iar Rob a creat o mulțime de cărți acolo, cea mai recentă a sa este „Power Pivot și Power BI”. Cred că acesta a fost de fapt în cartea „Power Pivot Alchemy”, este cea pe care am văzut-o și am spus „Ei bine, chiar dacă nu am milioane de rânduri de raportat prin Power Pivot, acesta este unul care ar Am făcut o diferență uriașă în viața mea, având două seturi de date de dimensiuni nepotrivite și trebuind să raportez de la amândoi. ” Ei bine, acest exemplu și multe altele sunt în această carte, în cele din urmă voi primi întregul podcast de carte, care pare că va dura două luni și jumătate. Dar puteți obține toată cartea astăzi, în același timp, mergeți acolo, cumpărați cartea, 10 USD pentru cartea electronică, 25 USD pentru cartea tipărită și puteți avea toate aceste sfaturi simultan.
În regulă, un episod foarte lung aici: avem un buget mic de sus în jos și un buget real, sunt de dimensiuni diferite, dar folosind modelul de date în Excel 2013 … Și apropo, dacă ești în 2010, ai putea , în teorie, faceți acest lucru obținând programul de completare Power Pivot și parcurgeți toți acești pași în 2010. Faceți ambele seturi de date într-un tabel Ctrl + T, apoi alăturați-vă tabelelor pentru orice doriți să raportați, în eticheta rândului, sau eticheta coloanei sau secționatoarele, deci copiați aceste valori peste și Eliminați duplicatele pentru date. De fapt, am luat valori din ambele tabele, pentru că existau unele valori unice în fiecare și apoi am folosit EOMONTH pentru a ajunge acolo, pentru a face ca acele tabele de asamblare să fie tabele controlate. Este opțional, dar am numit toate cele 5 tabele, deoarece mai ușor atunci când stabiliți acele relații, mai degrabă decât să fiți numit Table1,Tabelul 2, Tabelul 3.
Deci, începeți de la tabelul Buget, Insert, Tabel pivot, bifați caseta pentru Modelul de date și apoi construiți un tabel pivot folosind Buget și Actual. Orice altceva provine din tabelele de asamblare, deci Regiunea și Luna în zona rândurilor și coloanelor, feliatorii au venit din tabelul Produs. Și apoi a trebuit să creăm 3 relații de la buget la alături, 3 relații de la actual la alături și avem un tabel pivot uimitor. Acum mâine vom arunca o privire la utilizarea filei Power Pivot și la crearea unor calcule suplimentare. Deci, toate acestea sunt posibile, atunci când dorim să inserăm un câmp calculat, atunci trebuie să plătiți 2 USD în plus pe lună pentru a obține versiunea Pro Plus a Office 365.
Ei bine, mulțumită lui Rob Collie de la Power Pivot Pro pentru acest sfat și, mulțumită ție, că ai trecut pe aici, ne vedem data viitoare pentru un alt netcast de la!
Descărcare fișier
Descărcați exemplarul de fișier aici: Podcast2016.xlsx
