Vai jums nav Power Pivot? Nav nozīmes. Lielākā daļa Power Pivot ir iebūvēta programmā Excel 2013 un vēl vairāk programmā Excel 2016. Šodien mūsu padoms no Ash ir pievienots tabulām šarnīra tabulā.
Katru trešdienu septiņu nedēļu laikā es parādīju vienu no iecienītākajiem Ash Sharma padomiem. Ešs ir produktu menedžeris Excel komandā. Viņa komanda atnes jums pagrieziena galdus un daudzas citas labas lietas. Šodien Eša iecienītākā iezīme ir vairāku datu kopu pievienošana, izmantojot Attiecības un Datu modeli.
Pieņemsim, ka jūsu IT nodaļa sniedz jums datu kopu, kas parādīta kolonnās A: D. Ir klienta un tirgus lauki. Noteikti tirgi jāapvieno reģionos. Katrs klients pieder nozarei. Reģions un sektors nav sākotnējos datos, taču jums ir uzmeklēšanas tabulas, lai sniegtu šo informāciju.
Parasti datus saplacināt, izmantojot VLOOKUP, lai datus no oranžās un dzeltenās tabulas ievilktu zilajā tabulā. Bet, tā kā atslēgas lauks neatrodas katras tabulas kreisajā pusē, jums būs vai nu jāpārslēdzas uz INDEX un MATCH, vai arī jāpārkārto uzmeklēšanas tabulas.
Sākot ar programmu Excel 2013, varat atstāt uzmeklēšanas tabulas tur, kur tās atrodas, un apvienot tās pašā rakurstabulas pārskatā.
Lai šī tehnika darbotos, visām trim tabulām jābūt formatētām kā tabulām. Katrā datu kopā atlasiet vienu šūnu un izvēlieties Sākums, Formatēt kā tabulu vai nospiediet Ctrl + T. Sākotnēji trīs tabulas tiks sauktas par 1., 2. un 3. tabulu. Es izmantoju lentes cilni Table Tools Design un katru tabulu pārdēvēju. Es arī mainu katras galda krāsu. Šajā piemērā zilo tabulu sauc par datiem. Oranžā tabula ir RegionTable. Dzeltenais galds ir SectorTable.
Piezīme
Daži jums pateiks, ka jums jāizmanto tādi vārdi kā Fact, TblSector un TblRegion. Ja kāds jums sagādā nepatikšanas, vienkārši nozagiet viņu kabatas aizsargu un dariet viņam zināmu, ka dodat priekšroku angliski skanošiem vārdiem.
Lai pārdēvētu tabulu, tabulas rīku noformējuma cilnes kreisajā pusē esošajā lodziņā ierakstiet jaunu nosaukumu. Tabulu nosaukumos nedrīkst būt atstarpes.
Kad trīs tabulas ir definētas, dodieties uz cilni Dati un noklikšķiniet uz Attiecības.
Dialogā Pārvaldīt attiecības noklikšķiniet uz Jauns. Dialoglodziņā Attiecību izveide norādiet, ka datu tabulas lauks Klients ir saistīts ar SectorTable klienta lauku. Noklikšķiniet uz Labi.
Definējiet vēl vienu jaunu attiecību starp lauku Market laukos Data un RegionTable. Pēc abu attiecību definēšanas tās redzēsit Pārvaldīt attiecības.
Apsveicam: jūs tikko esat izveidojis datu modeli savā darbgrāmatā. Ir pienācis laiks izveidot pagrieziena galdu.
Atlasiet tukšo šūnu, kurā jāparāda rakurstabula. Pēc noklusējuma dialoglodziņā Izveidot rakurstabulu tiks izvēlēts Izmantot šīs darbgrāmatas datu modeli. Pagrieziena tabulas atrašanās vieta pēc noklusējuma būs jūsu izvēlētā šūna. Noklikšķiniet uz Labi.
Sarakstā Pivot Table Fields tiks uzskaitītas visas trīs tabulas. Izmantojiet trīsstūri kreisajā tabulā, lai izvērstu tabulas nosaukumu, lai parādītu laukus.
Izvērsiet tabulu Dati. Atlasiet lauku Ieņēmumi. Tas automātiski pārvietosies uz Vērtību apgabalu. Izvērsiet SectorTable. Izvēlieties lauku Sector. Tas pārvietosies uz Rows apgabalu. Izvērsiet RegionTable. Velciet reģionu laukā kolonnu apgabalā. Tagad jums būs rakurstabula, kurā apkopoti trīs tabulu dati.
Piezīme
Katrā grāmatā, kuru esmu rakstījis šodien, es izmantoju citu paņēmienu, lai izveidotu šo ziņojumu. Pēc trīs tabulu definēšanas es izvēlos šūnu A1 un Insert, Pivot Table. Es atzīmēju izvēles rūtiņu Pievienot šos datus datu modelim. Sarakstā Pivot Table Fields saraksta augšdaļā atlasiet All. Izvēlieties pārskata laukus un pēc tam definējiet attiecības pēc fakta. Iepriekš aprakstītā tehnika šķiet vienmērīgāka un faktiski ietver nelielu plānošanu nākotnē. Cilvēkiem, kuri savā VBA kodā izmanto opciju Explicit, šī metode noteikti patiks.
Datu modeļa attiecības liek Excel justies vairāk kā Access vai SQL Server, bet ar visu Excel labumu.
Man patīk jautāt Excel komandai viņu iecienītākās funkcijas. Katru trešdienu es dalīšos ar vienu no viņu atbildēm. Paldies Ešam Šarmam par šīs idejas piegādi.
Excel dienas doma
Esmu lūdzis saviem Excel Master draugiem padomu par Excel. Šodienas doma apdomāt:
"Nemeklējiet, ja esat attiecībās"
Džons Mihaloudis
