Nu aveți Power Pivot? Nu contează. Cea mai mare parte a Power Pivot este integrată în Excel 2013 și chiar mai mult în Excel 2016. Astăzi, sfatul nostru de la Ash este alăturarea tabelelor într-un tabel pivot.
În fiecare miercuri timp de șapte săptămâni, prezint unul dintre sfaturile preferate de la Ash Sharma. Ash este manager de produs în echipa Excel. Echipa lui îți aduce tabele pivot și multe alte lucruri bune. Astăzi, caracteristica preferată a lui Ash este alăturarea mai multor seturi de date folosind Relații și Modelul de date.
Spuneți că departamentul IT vă oferă setul de date prezentat în coloanele A: D. Există câmpuri pentru clienți și piață. Trebuie să combinați anumite piețe în regiuni. Fiecare client aparține unui sector. Regiunea și sectorul nu se află în datele originale, dar aveți tabele de căutare pentru a furniza aceste informații.
În mod normal, ați aplatiza datele folosind VLOOKUP pentru a extrage datele din tabelele portocalii și galbene în tabelul albastru. Dar, deoarece câmpul cheie nu este în partea stângă a fiecărui tabel, va trebui fie să comutați la INDEX și MATCH, fie să rearanjați tabelele de căutare.
Începând din Excel 2013, puteți lăsa tabelele de căutare acolo unde sunt și le puteți combina în raportul propriu-zis al tabelului pivot.
Pentru ca această tehnică să funcționeze, toate cele trei tabele trebuie să fie formatate ca tabel. Selectați o celulă din fiecare set de date și alegeți Acasă, Formatare ca tabel sau apăsați Ctrl + T. Cele trei tabele vor fi numite inițial Tabelul 1, Tabelul 2 și Tabelul 3. Folosesc fila Proiectare instrumente tabel din panglică și redenumesc fiecare tabel. Schimb și culoarea fiecărui tabel. În acest exemplu, tabelul albastru se numește Date. Tabelul portocaliu este RegionTable. Tabelul galben este SectorTable.
Notă
Unii vă vor spune că ar trebui să utilizați nume ciudate, cum ar fi Fact, TblSector și TblRegion. Dacă cineva îți face probleme, fură-i protectorul de buzunar și anunță-l că preferi numele cu sunet englezesc.
Pentru a redenumi un tabel, tastați un nume nou în caseta din partea stângă a filei Tabela Proiectare instrumente. Numele tabelelor nu trebuie să aibă spații.
Odată definite cele trei tabele, mergeți la fila Date și faceți clic pe Relații.
În dialogul Gestionați relațiile, faceți clic pe Nou. În caseta de dialog Creare relație, specificați că câmpul Client al tabelului de date este legat de Câmpul Clientului SectorTable. Faceți clic pe OK.
Definiți o altă relație nouă între câmpul Market în câmpurile Data și RegionTable. După definirea ambelor relații, le veți vedea în dialogul Gestionați relațiile.
Felicitări: tocmai ați construit un model de date în registrul dvs. de lucru. Este timpul să construim un tabel pivot.
Selectați celula goală unde doriți să apară tabelul pivot. În mod implicit, dialogul Creare tabel pivot va alege Utilizați modelul de date al acestui registru de lucru. Locația tabelului pivot va fi implicit celula pe care ați ales-o. Faceți clic pe OK.
Lista câmpurilor tabelului pivot va enumera toate cele trei tabele. Utilizați triunghiul din stânga unui tabel pentru a extinde numele tabelului pentru a vă arăta câmpurile.
Extindeți tabelul de date. Selectați câmpul Venituri. Se va muta automat în zona Valori. Extindeți SectorTable. Alegeți câmpul Sector. Se va muta în zona Rânduri. Extindeți RegionTable. Trageți câmpul Regiune în zona Coloane. Acum veți avea un tabel pivot care să rezume datele din cele trei tabele.
Notă
În fiecare carte pe care am scris-o înainte astăzi, folosesc o tehnică diferită pentru a construi acest raport. După definirea celor trei tabele, aleg celula A1 și Insert, Pivot Table. Bifez caseta pentru Adăugați aceste date la modelul de date. În lista Câmpuri ale tabelului pivot, selectați Toate din partea de sus a listei. Alegeți câmpurile pentru raport, apoi definiți relațiile după fapt. Tehnica descrisă mai sus pare mai ușoară și implică de fapt o mică planificare în viitor. Cei care folosesc Option Explicit în codul lor VBA ar dori cu siguranță această metodă.
Relațiile din modelul de date fac ca Excel să se simtă mai mult ca Access sau SQL Server, dar cu toate bunătatea Excel.
Îmi place să cer echipei Excel caracteristicile lor preferate. În fiecare miercuri, voi împărtăși unul dintre răspunsurile lor. Mulțumim lui Ash Sharma pentru furnizarea acestei idei.
Gândul Excel al zilei
Le-am cerut prietenilor mei Excel Master sfatul lor despre Excel. Gândul de astăzi să medităm:
„Nu căutați dacă aveți o relație”
John Michaloudis
