Anotācija: Šī ir pirmā apmācība sērijā, kas paredzēta, lai jūs varētu iepazīties un ērti izmantot programmu Excel un tās iebūvētos datu apkopošanas un analīzes līdzekļus. Šajās apmācībās mēs no jauna izveidosim un precizēsim Excel darbgrāmatu, veidosim datu modeli un pēc tam izveidosim pārsteidzošas interaktīvas atskaites, izmantojot Power View. Šīs apmācības ir paredzētas, lai demonstrētu Microsoft biznesa informācijas līdzekļus un iespējas programmā Excel, rakurstabulās, pievienojumprogrammā Power Pivot un Power View.
Šajās apmācībās uzzināsit, kā importēt un izpētīt datus programmā Excel, kā izveidot un precizēt datu modeli, izmantojot Power Pivot, un kā izveidot interaktīvas atskaites, izmantojot Power View, kuras var publicēt, aizsargāt un koplietot.
Šajā sērijā ir šādas apmācību tēmas:
- Datu importēšana programmā Excel 2016 un datu modeļa izveide
- Datu modeļa relāciju paplašināšana, izmantojot Excel, Power Pivot un DAX
- Tādu Power View atskaišu izveide, kuru pamatā ir kartes
- Interneta datu iekļaušana un Power View atskaišu noklusējuma vērtību iestatīšana
- Power Pivot palīdzība
- Lielisku Power View atskaišu izveide — 2. daļa
Šajā apmācībā sāksim darbu tukšā Excel darbgrāmatā.
Š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 kontroldarbs, ko var 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ē.
Noklikšķiniet uz šīm saitēm, lai lejupielādētu failus, kurus izmantosim šajā apmācību sērijā. Lejupielādējiet visus četrus failus viegli pieejamā vietā, piemēram, Lejupielādes vai Mani dokumenti, vai arī jaunizveidotā mapē.
> OlympicMedals.accdb Access datu bāze
> Excel darbgrāmatasOlympicSports.xlsx
> Excel darbgrāmatasPopulation.xlsx
> Excel darbgrāmatasDiscImage_table.xlsxProgrammā Excel atveriet tukšu darbgrāmatu.
Noklikšķiniet uz Dati > , lai iegūtu datus > no datu bāzes > no Microsoft Access datu bāzes. 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 šajā ekrānuzņēmumā.
Atlasiet lejupielādēto failu OlympicMedals.accdb un noklikšķiniet uz Importēt. Tiek parādīts šāds navigatora logs, 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 Vairāku tabulu atlase un atlasiet visas tabulas. Pēc tam noklikšķiniet uz Ielādēt > ielādēt.
Tiek atvērts logs Datu importēšana.
Piezīme
Ievērojiet izvēles rūtiņu loga lejasdaļā, kas ļauj pievienot šos datus datu modelim tālāk esošajā ekrānuzņēmumā. Datu modelis tiek izveidots automātiski, kad importējat vai strādājat ar divām vai vairākām tabulām vienlaikus. Datu modelis integrē tabulas, nodrošinot plašu analīzi, izmantojot rakurstabulas, Power Pivot un Power View. Importējot tabulas no datu bāzes, datu modeļa izveidei programmā Excel tiek izmantotas esošās datu relācijas starp šīm tabulām. Datu modelis programmā Excel ir caurspīdīgs, bet to var skatīt un modificēt tieši, izmantojot pievienojumprogrammu Power Pivot. Datu modelis ir sīkāk aplūkots tālāk šajā apmācībā.
Atlasiet rakurstabulu atskaites opciju, kas importē tabulas programmā Excel un sagatavo rakurstabulu importēto tabulu analīzei, un noklikšķiniet uz Labi.
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.
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.
Pēc tam no tabulas Disciplines velciet disciplīnas lauku apgabalā RINDAS.
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.
- Noklikšķiniet jebkurā rakurstabulas vietā, lai nodrošinātu, ka ir atlasīta Excel rakurstabula. Rakurstabulas lauku sarakstā, kur ir izvērsta tabula Disciplīnas, novietojiet kursoru virs tās lauka Disciplīna, 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.
- 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.
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.
Tabulā Medals vēlreiz atlasiet Medal un ievelciet to apgabalā FILTRI.
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:
- Rakurstabulā noklikšķiniet uz nolaižamā saraksta pa labi no Kolonnu etiķetes.
- Atlasiet Vērtību filtri un izvēlieties Lielāks par...
- 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.
Ievietojiet jaunu Excel darblapu un piešķiriet tai nosaukumu Sports.
Atrodiet mapi, kurā ir lejupielādētie parauga datu faili un atveriet OlympicSports.xlsx.
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.
Darbgrāmatā Sports novietojiet kursoru šūnā A1 un ielīmējiet datus.
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, atlasot 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ātā 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, pievienojumprogrammā Power Pivot un Power View.
Piešķiriet tabulai nosaukumu. Sadaļā TABULAS NOFORMĒJUMA > rekvizīti atrodiet lauku Tabulas nosaukums un ierakstiet Sports. Darbgrāmata izskatīsies tā, kā redzams nākamajā ekrānuzņēmumā.
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.
- Ievietojiet jaunu Excel darblapu un piešķiriet tai nosaukumu Hosts.
- Atlasiet un kopējiet šādu tabulu, ieskaitot tabulas galvenes.
- Programmā Excel novietojiet kursoru darblapas Hosts šūnā A1 un ielīmējiet datus.
- Formatējiet datus kā tabulu. Kā aprakstīts iepriekš šajā apmācībā, nospiediet taustiņu kombināciju Ctrl + T, lai formatētu datus kā tabulu, vai no SĀKUMS > Formatēt kā tabulu. Tā kā datiem ir galvenes, parādītajā logā Tabulas izveide atlasiet Manai tabulai ir galvenes.
- Piešķiriet tabulai nosaukumu. Sadaļā TABULAS NOFORMĒJUMA > rekvizīti atrodiet lauku Tabulas nosaukums un ierakstiet Hosts.
- Atlasiet kolonnu Edition un cilnē SĀKUMS formatējiet to kā Skaitlis ar 0 decimālvietām aiz komata.
- 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.
Lapā1,rakurstabulas lauku augšdaļā noklikšķiniet uzVisi, lai skatītu pilnu pieejamo tabulu sarakstu, kā parādīts nākamajā ekrānuzņēmumā.
Ritināt sarakstu, lai atrastu tikko pievienotās jaunās tabulas.
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.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ā.
Sadaļā Tabula nolaižamajā sarakstā izvēlieties Datu modeļa tabula: Disciplīnas.
Nolaižamajā sarakstā Kolonna (ārējā) izvēlieties SportID.
Saistītajā tabulā izvēlieties Datu modeļa tabula: Sporta veidi.
Sarakstā Saistītā kolonna (primārā) izvēlieties SportID.
Noklikšķiniet uz Labi.
Rakurstabula tiek mainīta, lai atspoguļotu jauno relāciju. Taču rakurstabula vēl neizskatās gluži pareizi, jo apgabalā RINDAS lauki ir sakārtoti. Disciplīna ir konkrēta sporta veida apakškategorija, bet, tā kā mēs sakārtojām Disciplīna virs Sporta ROWS apgabalā, tā nav pareizi organizēta. Nākamajā ekrānuzņēmumā ir redzama šī nevēlamā secība.
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**" izmantosit šeit apgūto un pakāpeniski paplašināsit datu modeli, izmantojot jaudīgu un vizuālu Excel pievienojumprogrammu Power Pivot. Uzzināsit arī, kā aprēķināt tabulas kolonnas un kā izmantot šo aprēķināto kolonnu, 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, kad tie ir izslēgti no datu modeļa, tie ir pieejami rakurstabulās, pievienojumprogrammā 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, pievienojumprogrammā Power Pivot un 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
- Pareizā atbilde: C
- Pareizā atbilde: D
- Pareizā atbilde: B
- Pareizā atbilde: D
Piezīme
Šajā apmācību sērijā izmantoto datu un attēlu avots:
- Olimpisko spēļu datu kopa no Guardian News & Media Ltd.
- Karogu attēli no CIA Factbook (cia.gov)
- Dati par iedzīvotājiem no Pasaules Bankas (worldbank.org)
- Olimpisko sporta veidu piktogrammu autori: Thadius856 un Parutakupiu