Vai esat izmantojis funkciju VLOOKUP kolonnas pārnešanai no vienas tabulas otrā? Tagad programmā Excel ir iebūvēts datu modelis, tāpēc funkcija VLOOKUP ir novecojusi. Jūs varat izveidot relāciju starp divām datu tabulām, pamatojoties uz atbilstošiem datiem katrā tabulā. Pēc tam varat izveidot Power View lapas, PivotTable rakurstabulas un citas atskaites, izmantojot laukus no katras tabulas, pat ja tabulas ir no dažādiem avotiem. Piemēram, ja jums ir klientu pārdošanas dati, varat importēt laika informācijas datus un izveidot relāciju ar tiem, lai analizētu pārdošanas tendences pa gadiem un mēnešiem.
Visas darbgrāmatas tabulas uzskaitītas rakurstabulas un līdzekļa Power View lauku sarakstos.
Importējot saistītas tabulas no relāciju datu bāzes, programmā Excel bieži šīs relācijas var tikt izveidotas datu modelī, kas tiek veidots fonā. Citos gadījumos relācijas vajadzēs izveidot manuāli.
-
Pārliecinieties, vai darbgrāmatā ir vismaz divas tabulas un vai katrā tabulā ir kolonna, kuru var kartēt ar citas tabulas kolonnu.
-
Veiciet kādu no šīm darbībām: Formatējiet datus kātabulu vai importējiet ārējos datus kā tabulu jaunā darblapā.
-
Katrai tabulai piešķirt jēgpilnu nosaukumu. Lai ievadītu nosaukumu, sadaļā Tabulas rīki noklikšķiniet uz Noformējums > Tabulas nosaukums > ievadiet nosaukumu.
-
Pārliecinieties, ka vienas tabulas kolonnā ir unikālas datu vērtības bez dublikātiem. Programma Excel var izveidot relāciju tikai tad, ja viena kolonna satur unikālas vērtības.
Piemēram, lai saistītu klientu datus ar laika informāciju, abās tabulās datumiem jābūt vienādā formātā (piemēram, 1/1/2012) un vismaz vienas tabulas (laika informācijas tabulas) kolonnā katram datumam jābūt norādītam tikai vienreiz.
-
Noklikšķiniet uz Dati > Relācijas.
Ja opcija Relācijas ir pelēkota, darbgrāmatā ir tikai viena tabula.
-
Lodziņā Pārvaldīt relācijas noklikšķiniet uz Jauna.
-
Lodziņā Izveidot relāciju noklikšķiniet uz bultiņas pie opcijas Tabula un sarakstā atlasiet tabulu. Veidojot relāciju Viens pret daudziem, šai tabulai ir jābūt relācijas kopā Daudzi. Minētajā klientu un laika informācijas piemērā vispirms ir jāizvēlas klientu pārdošanas datu tabula, jo daudzi pārdošanas darījumi var notikt jebkurā dienā.
-
Laukā Kolonna (ārējā) atlasiet kolonnu, kurā ir iekļauti ar laukā Saistītā kolonna (primārā) norādīto kolonnu saistītie dati. Piemēram, ja abās tabulās ir datumu kolonna, ir jāizvēlas šī kolonna.
-
Laukā Saistītā tabula atlasiet tabulu, kurā ir vismaz viena kolonna, kuras dati ir saistīti ar laukā Tabula tikko atlasīto tabulu.
-
Laukā Saistīta kolonna (primārā) atlasiet kolonnu, kurā ir unikālas vērtības, kas atbilst laukā Kolonna atlasītās kolonnas vērtībām.
-
Noklikšķiniet uz Labi.
Papildinformācija par tabulu relācijām programmā Excel
Piezīmes par relācijām
-
Velkot dažādu tabulu laukus uz rakurstabulas lauku sarakstu, būs redzams, vai relācijas pastāv. Ja nav redzama uzvedne ar aicinājumu izveidot relāciju, programmā Excel jau ir nepieciešamā relācijas informācija, lai veidotu datu relāciju.
-
Relāciju izveide līdzinās funkcijas VLOOKUP lietošanai: ir jāizmanto kolonnas ar savstarpēji atbilstošiem datiem, lai programmā Excel varētu izveidot vienas tabulas rindu iekšējo atsauci uz otras tabulas rindām. Laika informācijas piemērā tabulā Klienti ir jābūt datumu vērtībām, kas ir iekļautas arī laika informācijas tabulā.
-
Datu modelī tabulu relācijas var būt "viens pret vienu" (katram iekšzemes pastam ir viens piegājš) vai viens pret daudziem (katram lidojumam ir daudzi pret daudziem), bet ne daudzi pret daudziem. Relācijas Daudzi pret daudziem izraisa riņķveida atkarības kļūdas, piemēram, "Tika konstatēta riņķveida atkarība". Šī kļūda rodas, ja tiek izveidots tiešs savienojums starp divām tabulām, kas ir daudzi pret daudziem vai netieši savienojumi (tabulu relāciju ķēde, kas katrā relācijā ir viens pret daudziem, bet daudzi pret daudziem, skatot beigās. Lasiet vairāk par relācijām starp tabulām datu modelī.
-
Datu tipiem abās kolonnās jābūt saderīgiem. Papildinformāciju skatiet sadaļā Datu tipi programmas Excel datu modeļos.
-
Ir pieejami arī citi relāciju izveides veidi, kas, iespējams, ir intuitīvāki — jo īpaši, ja neesat pārliecināts, kuras tabulas izmantot. Skatiet sadaļu Relācijas izveide pievienojumprogrammas diagrammas skatā Power Pivot.
Piemērs: laika informācijas datu saistīšana ar aviolīnijas lidojumu datiem
Informāciju par abu tabulu relācijām un laika informāciju varat uzzināt, izmantojot bezmaksas datus, kas pieejami Microsoft Azure Marketplace. Dažas no šīm datu kopām ir ļoti lielas, un nepieciešams ātrs interneta savienojums, lai datu lejupielādi pabeigtu pieņemamā laika posmā.
-
Startējiet pievienojumprogrammu Power Pivot programmā Microsoft Excel un atveriet Power Pivot logu.
-
Noklikšķiniet uz Ārējo datu iegūšana > No datu pakalpojuma > No Microsoft Azure Marketplace. Tabulu importēšanas vednī tiek atvērta Microsoft Azure Marketplace sākumlapa.
-
Sadaļā Price (Cena) noklikšķiniet uz Free (Bezmaksas).
-
Sadaļā Category (Kategorija) noklikšķiniet Science & Statistics (Zinātne un statistika).
-
Atrodiet DateStream un noklikšķiniet uz Abonēt.
-
Ievadiet savu Microsoft kontu un noklikšķiniet uz Sign in (Pierakstīties). Logā vajadzētu parādīties datu priekšskatījumam.
-
Ritiniet līdz apakšai un noklikšķiniet uz Select Query (Atlasīt vaicājumu).
-
Noklikšķiniet uz Next (Tālāk).
-
Izvēlieties BasicCalendarUS (ASV pamatkalendārs) un pēc tam noklikšķiniet uz Finish (Pabeigt), lai importētu datus. Izmantojot ātru interneta savienojumu, importēšana neaizņems ilgāk par minūti. Kad esat beidzis, vajadzētu būt redzamai statusa atskaitei, kurā norādīts ka pārsūtītas 73 414 rindas. Noklikšķiniet uz Close (Aizvērt).
-
Lai importētu otru datu kopu, noklikšķiniet uz Ārējo datu iegūšana > No datu pakalpojuma > No Microsoft Azure Marketplace.
-
Sadaļā Type (Tips) noklikšķiniet uz Data (Dati).
-
Sadaļā Price (Cena) noklikšķiniet uz Free (Bezmaksas).
-
Atrodiet US Air Carrier Flight Delays (ASV aviolīniju kavētie lidojumi) un noklikšķiniet uz Select (Atlasīt).
-
Ritiniet līdz apakšai un noklikšķiniet uz Select Query (Atlasīt vaicājumu).
-
Noklikšķiniet uz Next (Tālāk).
-
Noklikšķiniet uz Finish (Pabeigt), lai importētu datus. Izmantojot ātru interneta savienojumu, importēšana var aizņemt 15 minūtes. Kad esat beidzis, vajadzētu būt redzamai statusa atskaitei, kurā norādīts, ka pārsūtītas 2 427 284 rindas. Noklikšķiniet uz Close (Aizvērt). Tagad datu modelī vajadzētu būt divām tabulām. Lai tās saistītu, katrā tabulā jābūt saderīgām kolonnām.
-
Ņemiet vērā, ka sadaļas BasicCalendarUS (ASV pamatkalendārs) parametra DateKey (Datuma atslēga) formāts ir 1/1/2012 12:00:00 AM. Arī tabulā On_Time_Performance ir datuma un laika kolonna FlightDate, kuras vērtības ir norādītas tādā pašā formātā: 1/1/2012 12:00:00 AM. Abās kolonnās ir atbilstoši vienāda datu tipa dati, un vismaz vienā kolonnā (DateKey) ir tikai unikālas vērtības. Nākamajās darbībās izmantosim šīs kolonnas, lai saistītu tabulas.
-
Power Pivot logā noklikšķiniet uz Rakurstabula, lai izveidotu rakurstabulu jaunā vai esošā darblapā.
-
Lauku sarakstā izvērsiet On_Time_Performance un noklikšķiniet uz ArrDelayMinutes, lai to pievienotu vērtību apgabalam. Rakurstabulā vajadzētu būt redzamam lidojumu kavēšanās kopējam ilgumam minūtēs.
-
Izvērsiet BasicCalendarUS un noklikšķiniet MonthInCalendar, lai to pievienotu rindu apgabalam.
-
Ievērojiet, ka rakurstabulā tagad ir uzskaitīti mēneši, taču katram mēnesim minūšu kopsumma ir vienāda. Identiskas vērtības, kas atkārtojas, norāda uz to, ka nepieciešama relācija.
-
Lauku sarakstā sadaļā “Iespējams, starp tabulām ir nepieciešamas relācijas” noklikšķiniet uz Izveidot.
-
Sadaļā Saistītā tabula atlasiet On_Time_Performance sadaļā Saistītā kolonna (primārā) izvēlieties FlightDate.
-
Sadaļā Tabula atlasiet BasicCalendarUS un sadaļā Kolonna (ārēja) izvēlieties DateKey. Noklikšķiniet uz Labi, lai izveidotu relāciju.
-
Ievērojiet, ka tagad kavēto lidojumu minūšu summa katram mēnesim ir atšķirīga.
-
Sadaļā BasicCalendarUS velciet YearKey uz rindu apgabalu, kas atrodas virs MonthInCalendar.
Tagad varat sadalīt kavētos lidojums pēc gada un mēneša vai citām vērtībām kalendārā.
Padomi.: Pēc noklusējuma mēneši ir norādīti alfabētiskā secībā. Izmantojot pievienojumprogrammu Power Pivot, varat mainīt kārtošanu, lai mēneši parādītos hronoloģiskā secībā.
-
Pārliecinieties, vai logā Power Pivot ir atvērta tabula BasicCalendarUS.
-
Tabulā Sākums noklikšķiniet uz Kārtot pēc kolonnas.
-
Sadaļā Kārtot izvēlieties MonthInCalendar
-
Sadaļā Pēc izvēlieties MonthOfYear.
Tagad rakurstabula kārto visas mēneša–gada kombinācijas (2011. gada oktobris, 2011. gada novembris) pēc mēneša numura gadā (10: 11). Kārtošanas secības maiņa ir vienkārša, jo plūsma DateStream nodrošina visas nepieciešamās kolonnas, lai šis scenārijs darbotos. Ja izmantojat citu laika informācijas tabulu, jūsu darbība atšķirsies.
“Tabulu relācijas var būt vajadzīgas”
Pievienojot laukus rakurstabulai, jūs tiksit informēts, ja būs vajadzīga tabulas relācija, lai rakurstabulā atlasītajiem laukiem būtu jēga.
Lai gan programma Excel var norādīt uz relācijas nepieciešamību, tā nevar noteikt, kuras tabulas un kolonnas jāizmanto un vai tabulas relācija vispār ir iespējama. Lai saņemtu vajadzīgās atbildes, izmēģiniet tālāk aprakstītās darbības.
1. — nosakiet, kuras tabulas jānorāda relācijā
Ja jūsu modelī ir tikai dažas tabulas, varbūt uzreiz būs skaidrs, kuras no tām jāizmanto. Taču attiecībā uz lielākiem modeļiem, iespējams, būs vajadzīga palīdzība. Viena pieeja ir izmantot pievienojumprogrammas Power Pivot diagrammas skatu. Diagrammas skatā ir vizuāli attēlotas visas tabula datu modelī. Izmantojot diagrammas skatu, varat ātri noteikt, kuras tabulas ir savrupas no pārējā modeļa.
Piezīme.: Ir iespējams izveidot neviennozīmīgas relācijas, kas ir nederīgas, ja tās izmanto rakurstabulā vai Power View atskaitē. Pieņemsim, ka visas jūsu tabulas kaut kādā veidā ir saistītas ar citām tabulā, taču, mēģinot kombinēt laukus no dažādām tabulām, tiek parādīts ziņojums “Iespējams, starp tabulām ir nepieciešamas relācijas”. Ticamākais šāda ziņojuma iemesls ir relācija daudzi pret daudziem. Ja sekosit to tabulu relāciju ķēdei, kas savieno tabulas, kuras vēlaties izmantot, iespējams, atklāsit, ka jums ir divas vai vairākas tabulu relācijas viens pret daudziem. Nav tāda viegla risinājuma, kas derētu visām situācijām, taču varat mēģināt izveidot aprēķinātas kolonnas, lai konsolidētu kolonnas, ko vēlaties izmantot vienā tabulā.
2. — atrodiet kolonnas, ko var izmantot, lai izveidotu ceļu no vienas tabulas uz nākamo
Kad būsit identificējis, kura tabula ir atvienota no pārējā modeļa, pārskatiet tabulas kolonnas, lai konstatētu, vai cita kolonna citur modelī satur atbilstošas vērtības.
Piemēram, pieņemsim, ka jums ir modelis, kurā ir produktu pārdošanas dati pēc teritorijas, un ka pēc tam importējat demogrāfiskos datus, lai uzzinātu, vai pastāv attiecība starp pārdošanas apjomu un demogrāfiskajām tendencēm katrā teritorijā. Tā kā demogrāfiskie dati tiek iegūti no cita datu avota, tā tabulas sākotnēji ir izolētas no pārējā modeļa. Lai demogrāfiskos datus integrētu pārējā modelī, jums vienā no demogrāfiskajām tabulām jāatrod kolonna, kas atbilst jau izmantotai kolonnai. Piemēram, ja demogrāfiskie dati ir organizēti pēc reģiona un ja jūsu pārdošanas datos ir norādīts, kurā reģionā darījums noticis, varat saistīt šīs divas datu kopas, atrodot kopīgu kolonnu, piemēram, Novads, Pasta indekss vai Reģions, lai veiktu uzmeklēšanu.
Papildus vērtību atbilstībai ir vēl dažas citas prasības, lai izveidotu relāciju:
-
Uzmeklēšanas kolonnas datu vērtībām ir jābūt unikālām. Tas nozīmē, ka šajā kolonnā nedrīkst būt dublikātu. Datu modelī nulles un tukšas virknes ir vienādas ar tukšumiem, kas ir noteikta datu vērtība. Tas nozīmē, ka uzmeklēšanas kolonnā nedrīkst būt vairākas nulles.
-
Datu tipiem avota kolonnā ir jābūt saderīgiem ar datu tipiem uzmeklēšanas kolonnā. Papildinformācija par datu tipiem atrodama rakstā Datu tipi datu modeļos.
Papildinformāciju par tabulu relācijām skatiet sadaļā Datu modeļa tabulu relācijas.
