Model de date Excel (cuprins)

  • Introducere în modelul de date în Excel
  • Cum se creează un model de date în Excel?

Introducere în modelul de date în Excel

Caracteristica modelului de date Excel permite construirea ușoară a relațiilor între raportarea ușoară și seturile lor de date de fundal. Face analiza datelor mult mai ușoară. Permite integrarea datelor dintr-o multitudine de tabele răspândite pe mai multe foi de lucru prin simpla construire de relații între coloanele potrivite. Funcționează complet în spatele scenei și simplifică considerabil funcțiile de raportare, cum ar fi PivotTable etc.

În articolul nostru, vom încerca să arătăm cum să creăm o tabelă pivot din două tabele, folosind funcția Model de date, stabilind astfel o relație între două obiecte de tabel și, prin aceasta, creând un tabel rotativ.

Cum se creează un model de date în Excel?

Să înțelegem cum să creăm Modelul de date în Excel cu câteva exemple.

Puteți descărca acest șablon Excel Model de date aici - Model de Excel Model de date

Exemplul # 1

  • Avem o listă de produse și avem un cod de raft pentru fiecare produs. Avem nevoie de un tabel în care avem descrierea raftului împreună cu codurile rafturilor. Deci, cum să includem descrierile rafturilor cu fiecare cod de raft? Poate că mulți dintre noi am recurge la utilizarea VLOOKUP aici, dar vom elimina cu totul nevoia de a folosi VLOOKUP aici folosind Modelul de date Excel.

  • Tabelul din stânga este tabelul de date, iar tabelul din dreapta este tabelul de căutare. După cum putem vedea din date, este posibil să creăm o relație bazată pe coloane comune.

  • Acum, Modelul de date este compatibil numai cu obiectele din tabel. Așadar, poate fi necesar uneori convertirea seturilor de date în obiecte din tabel. Pentru aceasta, urmați pașii de mai jos.
  1. Faceți clic stânga oriunde în setul de date.
  2. Faceți clic pe fila Inserați și navigați până la Tabelul din grupul Tabele sau pur și simplu apăsați Ctrl + T.
  3. Debifează sau bifează opțiunea antet. În exemplul nostru, are într-adevăr un antet. Faceți clic pe OK.
  4. În timp ce încă ne concentrăm pe noul tabel, trebuie să furnizăm un nume care are semnificație în caseta Nume (spre stânga barei de formulare).

În exemplul nostru, am numit Personalul din tabel.

  • Acum trebuie să facem același proces și pentru tabelul de căutare și să-l denumim Cod raft.

Crearea unei relații

În primul rând, vom merge la fila Date și apoi vom selecta Relații în subgrupul Instrumente de date. După ce facem clic pe opțiunea Relații, la început, deoarece nu există nicio relație, deci nu vom avea nimic.

Mai întâi vom face clic pe Nou pentru a crea o relație. Acum va trebui să furnizăm numele tabelului principal și al căutării din lista derulantă și apoi să menționăm și coloana care este comună între cele două tabele, astfel încât să putem stabili relația dintre cele două tabele, din lista verticală de coloane.

  • Acum, tabelul principal este tabelul care conține datele. Este tabelul principal de date - Tabelul5. Pe de altă parte, tabelul înrudit este tabelul care conține datele de căutare - este tabelul nostru de căutare ShelfCodesTable. Tabelul principal este cel care este analizat pe baza tabelului de căutare care conține date de căutare, ceea ce va face ca datele raportate în final să fie mai semnificative.

  • Deci, coloana comună dintre cele două tabele este coloana Cod raft. Aceasta este ceea ce am folosit pentru a stabili relația dintre cele două tabele. Venind la coloane, Coloana (străină) este cea care se referă la tabelul de date unde pot exista valori duplicate. Pe de altă parte, Coloana înrudită (primară) se referă la coloana din tabelul de căutare unde avem valori unice. Pur și simplu setăm câmpul la valorile de căutare din tabelul de căutare din tabelul de date.
  • Odată ce am configurat acest lucru, Excel ar crea o relație între cei doi din spatele scenei. Integrează datele și creează un model de date bazat pe coloana comună. Aceasta nu este doar lumină pentru cerințele de memorie, ci și mult mai rapid decât utilizarea VLOOKUP în manualele mari. După definirea modelului de date, Excel ar trata aceste obiecte ca tabele cu model de date în loc de tabel cu foi de lucru.
  • Acum pentru a vedea ce a fost Excel, putem face clic pe Gestionați modele de date din Date -> Instrumente de date.

  • Putem obține, de asemenea, reprezentarea schematică a modelului de date schimbând vederea. Vom face clic pe opțiunea Vizualizare. Aceasta va deschide opțiunile de vizualizare. Vom selecta apoi Vizualizarea diagramei. Apoi vom vedea reprezentarea schematică, care arată cele două tabele și relația dintre ele, adică coloana comună - Cod raft.

  • Diagrama de mai sus arată o relație unu-la-mulți între valorile tabelului de căutare unice și tabelul de date cu valori duplicate.
  • Acum va trebui să creăm un tabel pivot. Pentru a face acest lucru, vom merge la fila Inserare și apoi vom face clic pe opțiunea Pivot Table.

În caseta de dialog Creare tabel rotativ din tabelul Pivot, vom selecta sursa ca „Utilizați modelul de date al acestei cărți de lucru”.

  • Aceasta va crea tabelul Pivot și putem vedea că ambele tabele sursă sunt disponibile în secțiunea sursă.

  • Acum vom crea un tabel pivot care arată numărul fiecărei persoane care a depozitat articole.

  • Vom selecta Personal în secțiunea Rânduri din tabelul 5 (tabel de date), urmat de Descriere (tabel de căutare).

  • Acum vom trage Codul de raft din Tabelul 5 în secțiunea Valori.

  • Acum vom adăuga luni din tabelul 5 la secțiunea Rânduri.

  • Sau am putea adăuga lunile ca filtru și să le adăugăm la secțiunea Filtre.

Exemplul # 2

  • Îl avem acum pe domnul Basu care conduce o fabrică numită Basu Corporation. Domnul Basu încearcă să estimeze veniturile pentru anul 2019 pe baza datelor din 2018.
  • Avem un tabel în care avem veniturile pe anul 2018 și veniturile ulterioare la diferite niveluri incrementale.

  • Deci, avem veniturile pentru 2018 - 1, 5 milioane dolari, iar creșterea minimă preconizată în anul următor este de 12%. Domnul Basu dorește un tabel care să arate veniturile la diferite niveluri incrementale.
  • Vom crea următorul tabel pentru proiecții la diferite niveluri incrementale pentru 2019.

  • Acum, vom oferi primului rând de venituri o referire la veniturile minime estimate pentru anul 2019, adică 1, 68 USD.

  • După utilizarea formulei, răspunsul este prezentat mai jos.

  • Acum vom selecta întreaga tabelă, adică D2: E12 și apoi vom merge la Date -> Prognoză -> Analiză Ce-Dacă -> Tabel date.

  • Aceasta va deschide caseta de dialog Tabel de date. Aici vom introduce procentul de increment minim de celula B4 în celula de intrare a coloanei. Motivul pentru aceasta este că procentele noastre de creștere estimate în tabel sunt aranjate în mod columnar.

  • După ce facem clic pe OK, Analiza Ce-Dacă va adăuga automat tabelul cu venituri proiectate la diferite procente incrementale.

Exemplul # 3

  • Să presupunem acum că avem același scenariu ca mai sus, cu excepția faptului că acum avem și o altă axă de luat în considerare. Să presupunem că pe lângă afișarea veniturilor proiectate în 2019 pe baza datelor din 2018 și a ratei de creștere minime așteptate, avem acum și rata estimată de reducere.

  • În primul rând, vom avea un tabel prezentat mai jos.

  • Acum vom face referire la veniturile minime proiectate pentru 2019, adică celula B5 la celula D8.

  • Acum vom selecta întreaga tabelă, adică D8: J18 și apoi vom merge la Date -> Prognoză -> Analiză Ce-Dacă -> Tabel date.

  • Aceasta va deschide caseta de dialog Tabel de date. Aici vom introduce procentul de increment minim de celula B3 în celula de intrare a coloanei. Motivul pentru aceasta este că procentele noastre de creștere estimate în tabel sunt aranjate în mod columnar. De asemenea, acum vom introduce suplimentar procentul de reducere minimă de la celula B4 în celula de intrare rând. Motivul pentru aceasta este că procentele noastre de reduceri proiectate din tabel sunt aranjate într-o manieră liniară.

  • Faceți clic pe OK. Acest lucru va face Analiza Ce-Dacă să populeze automat tabelul cu venituri proiectate la diferite procente incrementale, conform procentelor de reducere.

Lucruri de reținut despre modelul de date în Excel

  • După calcularea cu succes a valorilor din tabelul de date, un simplu Anulare, adică Ctrl + Z, nu va funcționa. Cu toate acestea, este posibilă ștergerea manuală a valorilor din tabel.
  • Nu este posibilă ștergerea unei singure celule din tabel. Este descris ca un tablou intern în Excel, de aceea va trebui să ștergem toate valorile.
  • Trebuie să selectăm în mod corespunzător celula de intrare rând și celula de intrare a coloanei.
  • Tabelul de date, spre deosebire de tabelul rotativ, nu trebuie reîmprospătat de fiecare dată.
  • Folosind Modelul de date în Excel, nu numai că putem îmbunătăți performanța, dar putem merge ușor și la cerințele de memorie din foile mari de lucru.
  • Modelele de date fac, de asemenea, analiza noastră mult mai simplă, în comparație cu utilizarea unui număr de formule complicate în toată cartea de muncă.

Articole recomandate

Acesta este un ghid pentru Modelul de date în Excel. Aici vom discuta despre cum să creăm Modelul de date în Excel, împreună cu exemple practice și șablon Excel descărcabil. De asemenea, puteți parcurge și alte articole sugerate -

  1. Formula Bar în Excel
  2. Tipăriți grile în Excel
  3. Urmăriți fereastra în Excel
  4. SUMIFE Excel cu date

Categorie:

Sfaturi Excel