Apmācība: datu importēšana programmā Excel un datu modeļa izveide

Šai apmācībai ir šādas sadaļas:

• Datu importēšana no datu bāzes

• Datu importēšana no izklājlapas

• Datu importēšana, kopējot un ielīmējot

• Relācijas izveide starp importētiem datiem

• Kontrolpunkts un viktorīna

Šīs apmācības beigās ir viktorīna, ko varat izpildīt, lai pārbaudītu apgūtās zināšanas.

Šajā apmācību sērijā ir izmantoti dati, kas apraksta Olimpisko spēļu medaļas, rīkotājvalstis un dažādus Olimpisko spēļu sporta pasākumus. Apmācības ieteicams apgūt secīgi. 

Datu importēšana no datu bāzes

Šajā apmācībā sāksim darbu tukšā Excel darbgrāmatā. Šīs sadaļas mērķis ir izveidot savienojumu ar ārēju datu avotu un importēt šos datus programmā Excel turpmākai analīzei.

Iesākumā lejupielādēsim datus no interneta. Tie ir dati par Olimpisko spēļu medaļām Microsoft Access datu bāzē.

1. Noklikšķiniet uz šīm saitēm, lai lejupielādētu failus, kurus izmantosim šajā apmācību sērijā. Lejupielādējiet katru no četriem failiem ērti pieejamā atrašanās vietā, piemēram, Lejupielādes vai Mani dokumenti, vai arī izveidotā jaunā mapē:
   > OlympicMedals.accdb Access datu bāze
   > OlympicSports.xlsx Excel darbgrāmata
   > Population.xlsx Excel darbgrāmata
   > DiscImage_table.xlsx Excel darbgrāmata

2. Programmā Excel atveriet tukšu darbgrāmatu.

3. Noklikšķiniet uz DATI > Iegūt ārējos datus > No programmas Access. Lente tiek pielāgota automātiski, pamatojoties uz darbgrāmatas platumu, tāpēc jūsu lentes komandu izskats var nedaudz atšķirties no tā, kas redzams šajos ekrānuzņēmumos. Pirmajā ekrānā ir parādīta lente, kad darbgrāmata ir izvērsta, bet otrajā attēlā ir mainīti darbgrāmatas izmēri, lai tā aizņemtu tikai daļu ekrāna.
   
   
   
   
   
    

4. Atlasiet lejupielādēto failu OlympicMedals.accdb un noklikšķiniet uz Atvērt. Tiek atvērts tālāk norādītais logs Tabulas atlasīšana, kurā ir parādītas datu bāzē atrastās tabulas. Datu bāzes tabulas ir līdzīgas darblapām vai tabulām programmā Excel. Atzīmējiet izvēles rūtiņu Iespējot vairāku tabulu atlasi un atlasiet visas tabulas. Noklikšķiniet uz Labi.
   
   

5. Tiek atvērts logs Datu importēšana.

   Piezīme.: Ievērojiet izvēles rūtiņu loga apakšdaļā, kas ļauj pievienot šos datus datu modelim, kā parādīts nākamajā ekrānuzņēmumā. Datu modelis tiek izveidots automātiski, importējot vai vienlaikus strādājot ar divām vai vairākām tabulām. Datu modelī ir integrētas tabulas, nodrošinot plašu analīzi, izmantojot rakurstabulas, Power Pivot un Power View. Importējot tabulas no datu bāzes, starp šīm tabulām esošās datu bāzu relācijas tiek izmantotas, lai izveidotu datu modeli programmā Excel. Programmā Excel datu modelis ir caurspīdīgs, taču to var tieši skatīt un modificēt, Power Pivot pievienojumprogrammu. Tālāk šajā apmācībā ir sīkāk apspriests datu modelis.

   
   Atlasiet opciju PivotTable atskaite — tas importē tabulas programmā Excel un sagatavo rakurstabulu importēto tabulu analizēšanai, pēc tam noklikšķiniet uz Labi.
   
   

6. Kad dati ir importēti, tiek izveidota rakurstabula, izmantojot importētās tabulas.
   
   

Kad dati ir importēti programmā Excel un ir automātiski izveidots datu modelis, varat sākt datu pārlūkošanu.

Datu pārlūkošana, izmantojot rakurstabulu

Importētos datus var viegli pārlūkot, izmantojot rakurstabulu. Rakurstabulā velciet laukus (līdzīgi kā kolonnas programmā Excel) no tabulām (piemēram, tabulām, kuras tikko importējāt no Access datu bāzes) dažādos rakurstabulas apgabalos, lai pielāgotu datu parādīšanas veidus. Rakurstabulai ir četri apgabali: FILTRI, KOLONNAS, RINDAS un VĒRTĪBAS.

Iespējams, būs nedaudz jāpaeksperimentē, lai noteiktu, uz kuru apgabalu lauks jāvelk. Varat vilkt tik daudz lauku, cik vēlaties, ja vien rakurstabulā dati tiek attēloti tā, kā vajadzīgs. Varat droši pārlūkot datus, velkot laukus dažādos rakurstabulas apgabalos – kārtojot laukus rakurstabulā, pamatā esošie dati netiek ietekmēti.

Pārlūkosim datus par Olimpisko spēļu medaļām rakurstabulā – iesākumā sakārtosim Olimpisko spēļu medaļu ieguvējus pēc disciplīnas, medaļas veida un sportista valsts vai reģiona.

1. Apgabalā Rakurstabulas lauki izvērsiet tabulu Medals, noklikšķinot uz bultiņas tai blakus. Izvērstajā tabulā Medals atrodiet lauku NOC_CountryRegion un velciet to apgabalā KOLONNAS. NOC apzīmē nacionālās Olimpiskās komitejas, kas ir valsts vai reģiona Olimpiskās organizācijas.

2. Pēc tam no tabulas Disciplines velciet disciplīnas lauku apgabalā RINDAS.

3. Tagad filtrēsim tabulu Disciplines, lai tiktu parādīti tikai pieci sporta veidi: Archery, Diving, Fencing, Figure Skating un Speed Skating. To var izdarīt apgabalā Rakurstabulas lauki vai pašā rakurstabulā, izmantojot filtru Rindu etiķetes.

   1. Noklikšķiniet jebkurā rakurstabulas vietā, lai pārliecinātos, vai ir atlasīta Excel rakurstabula. Sarakstā Rakurstabulas lauki , kur ir izvērsta tabula Disciplines , novietojiet kursoru virs lauka Discipline, un pa labi no lauka tiek parādīta nolaižamā saraksta bultiņa. Noklikšķiniet uz nolaižamās izvēlnes, noklikšķiniet uz (Atlasīt visu), lai noņemtu visu atlasi, pēc tam ritiniet uz leju un atlasiet Archery, Diving, Fencing, Figure Skating un Speed Skating. Noklikšķiniet uz Labi.

   2. Varat arī rakurstabulas sadaļā Rindu etiķetes noklikšķināt uz nolaižamā saraksta blakus rakurstabulas sadaļai Rindu etiķetes, noklikšķināt uz (Atlasīt visu), lai noņemtu visu atlasi, un pēc tam atlasīt Archery, Diving, Fencing, Figure Skating un Speed Skating. Noklikšķiniet uz Labi.

4. Sadaļas Rakurstabulas lauki tabulā Medals ievelciet Medal apgabalā VĒRTĪBAS. Tā kā vērtībām jābūt skaitliskām, Excel automātiski pārdēvē Medal par Count of Medal.

5. Tabulā Medals vēlreiz atlasiet Medal un ievelciet to apgabalā FILTRI.

6. Pamēģināsim filtrēt rakurstabulu, lai parādītu tikai tās valstis vai reģionus, kuru medaļu kopskaits pārsniedz 90. To var izdarīt šādi:

   1. Rakurstabulā noklikšķiniet uz nolaižamā saraksta pa labi no Kolonnu etiķetes.

   2. Atlasiet Vērtību filtri un izvēlieties Lielāks par...

   3. Pēdējā laukā (labajā pusē) ierakstiet 90. Noklikšķiniet uz Labi.
      

Rakurstabula izskatīsies tā, kā parādīts nākamajā ekrānuzņēmumā.

Bez īpašas piepūles esam izveidojuši vienkāršu rakurstabulu, kurā ir iekļauti lauki no četrām dažādām tabulām. Šis uzdevums bija tik vienkāršs, jo starp tabulām jau iepriekš bija relācijas. Tā kā tabulu relācijas pastāvēja avota datu bāzē un tā kā datus importējāt vienā operācijā, programma Excel datu modelī varēja atjaunot šīs tabulu relācijas.

Taču, ko darīt, ja dati ir no dažādiem avotiem vai tiek importēti vēlāk? Parasti ir iespējams izveidot relācijas ar jauniem datiem, pamatojoties uz atbilstošām kolonnām. Nākamajā darbībā importēsim papildu tabulas un noskaidrosim, kādas darbības jāveic, lai izveidotu jaunas relācijas.

Datu importēšana no izklājlapas

Tagad importēsim datus no cita avota (šoreiz no esošas darbgrāmatas) un pēc tam norādīsim relācijas starp esošajiem datiem un jaunajiem datiem. Relācijas programmā Excel ļauj analizēt datu kolekcijas un no importētajiem datiem izveidot interesantas un imersīvas vizualizācijas.

Iesākumā izveidosim tukšu darblapu un pēc tam importēsim datus no Excel darbgrāmatas.

1. Ievietojiet jaunu Excel darblapu un piešķiriet tai nosaukumu Sports.

2. Atrodiet mapi, kurā ir lejupielādētie parauga datu faili un atveriet OlympicSports.xlsx.

3. Atlasiet un kopējiet datus Lapā1. Ja atlasāt šūnu ar datiem, piemēram, šūnu A1, varat nospiest taustiņu kombināciju Ctrl + A, lai atlasītu visus blakus esošos datus. Aizveriet darbgrāmatu OlympicSports.xlsx.

4. Darbgrāmatā Sports novietojiet kursoru šūnā A1 un ielīmējiet datus.

5. Lai datus formatētu kā tabulu, nospiediet taustiņu kombināciju Ctrl + T, kad tie joprojām ir iezīmēti. Datus var formatēt kā tabulu arī, izmantojot lenti, — atlasiet SĀKUMS > Formatēt kā tabulu. Tā kā datiem ir galvenes, atvērtajā logā Tabulas izveide (kā redzams attēlā) atlasiet Manai tabulai ir galvenes.
   
   
   
   Datu formatēšanai tabulas formā ir daudz priekšrocību. Jūs varat piešķirt tabulai nosaukumu, lai to varētu viegli identificēt. Varat izveidot arī relācijas starp tabulām, iespējojot pārlūkošanu un analīzi rakurstabulās, kā arī pievienojumprogrammā Power Pivot un līdzeklī Power View.

6. Piešķiriet tabulai nosaukumu. Sadaļā TABULU RĪKI > NOFORMĒJUMS > Rekvizīti atrodiet lauku Tabulas nosaukums un ierakstiet Sports. Darbgrāmata izskatīsies tā, kā redzams nākamajā ekrānuzņēmumā.
   

7. Saglabājiet darbgrāmatu.

Datu importēšana, kopējot un ielīmējot

Kad esam importējuši datus no Excel darbgrāmatas, importēsim datus no tabulas, kas atrodama tīmekļa lapā vai citā avotā, no kura varam kopēt un ielīmēt programmā Excel. Nākamajās darbībās pievienosim Olimpisko spēļu norises pilsētas no tabulas.

1. Ievietojiet jaunu Excel darblapu un piešķiriet tai nosaukumu Hosts.

2. Atlasiet un kopējiet šādu tabulu, ieskaitot tabulas galvenes.

1. Programmā Excel novietojiet kursoru darblapas Hosts šūnā A1 un ielīmējiet datus.

2. Formatējiet datus kā tabulu. Kā aprakstīts iepriekš šajā apmācībā, lai formatētu datus kā tabulu, jānospiež taustiņu kombinācija Ctrl+T vai jānoklikšķina uz SĀKUMS > Formatēt kā tabulu. Tā kā datiem ir galvenes, parādītajā logā Tabulas izveide atlasiet Manai tabulai ir galvenes.

3. Piešķiriet tabulai nosaukumu. Sadaļā TABULU RĪKI > NOFORMĒJUMS > Rekvizīti atrodiet lauku Tabulas nosaukums un ierakstiet Hosts.

4. Atlasiet kolonnu Edition un cilnē SĀKUMS formatējiet to kā Skaitlis ar 0 decimālvietām aiz komata.

5. Saglabājiet darbgrāmatu. Darbgrāmata izskatīsies tā, kā parādīts nākamajā ekrānuzņēmumā.

Kad ir izveidota Excel darbgrāmata ar tabulām, varat starp tām izveidot relācijas. Izveidojot relācijas starp tabulām, jūs varat apkopot datus no abām tabulām.

Relācijas izveide starp importētiem datiem

Varat savā rakurstabulā uzreiz sākt izmantot laukus no importētajām tabulām. Ja programma Excel nevar noteikt, kā iekļaut lauku rakurstabulā, esošajā datu modelī jāizveido relācija. Nākamajos darbībās noskaidrosim, kā izveidot relāciju starp datiem, kas importēti no dažādiem avotiem.

1. Lapā Lapa1 rakurstabulas lauku augšdaļā noklikšķiniet uzVisi, lai skatītu pilnu pieejamo tabulu sarakstu, kā parādīts nākamajā ekrānuzņēmumā.
   

2. Ritināt sarakstu, lai atrastu tikko pievienotās jaunās tabulas.

3. Izvērsiet vienumu Sports un atlasiet Sports, lai to pievienotu rakurstabulai. Ievērojiet, ka programma Excel aicina izveidot relāciju, kā parādīts nākamajā ekrānuzņēmumā.
   
    

   Šis paziņojums tiek parādīts, jo izmantojat laukus no tabulas, kas nav iekļauta pamatā esošajā datu modelī. Viens no veidiem, kā tabulu pievienot datu modelim, ir relācijas izveide ar tabulu, kas jau ir iekļauta datu modelī. Lai izveidotu relāciju, vienā no tabulām jābūt kolonnai ar unikālām vērtībām, kas neatkārtojas. Parauga datos no datu bāzes importētajā tabulā Disciplines ir lauki ar sporta veidu kodiem, ko sauc par SportID. Šie paši sporta veidu kodi ir iekļauti kā lauks importētajos Excel datos. Tagad izveidosim relāciju.

4. Iezīmētajā apgabalā Rakurstabulas lauki noklikšķiniet uz IZVEIDOT..., lai atvērtu dialoglodziņu Relācijas izveide, kā parādīts nākamajā ekrānuzņēmumā.
   
   

5. Nolaižamajā sarakstā Tabula izvēlieties Disciplines.

6. Nolaižamajā sarakstā Kolonna (ārējā) izvēlieties SportID.

7. Sarakstā Saistītā tabula izvēlieties Sports.

8. Sarakstā Saistītā kolonna (primārā) izvēlieties SportID.

9. Noklikšķiniet uz Labi.

Rakurstabula mainās, atspoguļojot jauno relāciju. Tomēr rakurstabula pagaidām vēl neizskatās pareizi, jo apgabalā RINDAS ir lauku secība. Discipline ir konkrētā sporta veida apakškategorija, bet, tā kā apgabalā RINDAS vienums Discipline virs Sport ir sakārtota pareizi, tā nav pareizi organizēta. Nākamajā ekrānā ir parādīta šāda nevēlama secība.

1. Apgabalā RINDAS pārvietojiet Sport virs Discipline. Tā ir daudz labāk – kā redzams nākamajā ekrānuzņēmumā, tagad rakurstabulā dati tiek parādīti tā, kā vajadzīgs.
   
   

Fonā programmā Excel tiek izveidots datu modelis, kuru var lietot visā darbgrāmatā, turklāt jebkurā rakurstabulā, rakursdiagrammā, pievienojumprogrammā Power Pivot vai jebkurā Power View atskaitē. Tabulu relācijas ir datu modeļa pamatā, un tieši tās nosaka navigācijas un aprēķinu ceļus.

Nākamajā apmācībā datu modeļa relāciju paplašināšana, izmantojot Excel, Power Pivot un DAX, tiek attīstīta šeit apgūtā informācija, kā arī soli pa solim jāpaplašina datu modelis, izmantojot jaudīgu un vizuālu Excel pievienojumprogrammu, ko sauc par Power Pivot. Jūs arī uzzināsit, kā aprēķināt kolonnas tabulā, un šo aprēķināto kolonnu varat izmantot, lai datu modelim varētu pievienot citādi nesaistītu tabulu.

Kontrolpunkts un viktorīna

Pārskats par apgūto

Jums tagad ir izveidota Excel darbgrāmata, kurā ir rakurstabula, kas piekļūst datiem vairākās tabulās, dažas no kurām esat importējis atsevišķi. Jūs uzzinājāt, kā importēt no datu bāzes un citas Excel darbgrāmatas un kā kopēt un ielīmēt datus programmā Excel.

Lai šos datus varētu kopīgi izmantot, jūs izveidojāt tabulu relācijas, kas programmā Excel tika lietotas rindu korelācijai. Apguvāt arī to, ka relāciju izveidei un saistīto rindu uzmeklēšanai ir būtiski, lai kolonnas vienā tabulā korelētu ar datiem citā tabulā.

Tagad varat pāriet pie nākamās šīs sērijas apmācības. Apmācības saite:

Apmācība: datu modeļa relāciju paplašināšana, izmantojot Excel, Power Pivot un DAX

KONTROLDARBS

Vai gribat zināt, cik labi atceraties apgūto? Šeit to varat pārbaudīt. Šajā kontroldarbā ir uzsvērti līdzekļi, iespējas vai prasības, par kurām uzzinājāt šajā apmācībā. Atbildes ir norādītas lapas apakšā. Lai veicas!

1. jautājums Kāpēc ir svarīgi pārvērst importētos datus tabulās?

A. Dati nav jāpārvērš tabulās, jo visi importētie dati tiek automātiski pārvērsti tabulās.

B. Konvertējot importētos datus tabulās, tie tiks izslēgti no datu modeļa. Tikai tad, ja dati ir izslēgti no datu modeļa, tie ir pieejami rakurstabulās, Power Pivot un Power View.

C. Pārvēršot importētos datus par tabulām, tos var iekļaut datu modelī un izmantot rakurstabulās, kā arī pievienojumprogrammā Power Pivot un līdzeklī Power View.

D. Importētos datus nevar pārvērst par tabulām.

2. jautājums Kuru no šiem datu avotiem var importēt programmā Excel un iekļaut datu modelī?

A. Access datu bāzes, kā arī daudzas citas datu bāzes.

B. Esošus Excel failus.

C. Jebkādus datus, ko var kopēt un ielīmēt programmā Excel, un formatēt kā tabulu, tai skaitā tabulas tīmekļa vietnēs, dokumentus vai citus datus, ko var ielīmēt programmā Excel.

D. Viss iepriekš minētais

3. jautājums Kas notiek, ja pārkārto laukus četros rakurstabulas lauku apgabalos?

A. Nekas – kad lauki ir ievietoti rakurstabulas lauku apgabalos, tos nevar pārkārtot.

B. Tiek mainīts rakurstabulas formāts, lai atspoguļotu jauno izkārtojumu, taču pamatā esošie dati netiek ietekmēti.

C. Tiek mainīts rakurstabulas formāts, lai atspoguļotu jauno izkārtojumu, un tiek neatgriezeniski mainīti visi pamatā esošie dati.

D. Ir mainīti pamatā esošie dati un izveidotas jaunas datu kopas.

4. jautājums Kas ir nepieciešams, veidojot relāciju starp tabulām?

A. Nevienā no tabulām nedrīkst būt kolonnā, kurā ir unikālas vērtības, kas neatkārtojas.

B. Viena no tabulām nedrīkst būt iekļauta Excel darbgrāmatā.

C. Kolonnas nedrīkst pārvērst par tabulām.

D. Neviena no iepriekš minētajām atbildēm nav pareiza.

Viktorīnas atbildes

1. Pareizā atbilde: C

2. Pareizā atbilde: D

3. Pareizā atbilde: B

4. Pareizā atbilde: D