Abstrakti: Tämä on sarjan ensimmäinen opetusohjelma, jonka tarkoituksena on tutustuttaa sinut Excelin ja sen tietojen yhdistämis- ja analysointitoimintojen käyttöön. Näissä opetusohjelmissa rakennetaan ja tarkennetaan Excel-työkirja tyhjästä, rakennetaan tietomalli ja luodaan sitten upeita interaktiivisia raportteja Power View'lla. Opetusohjelmien tarkoituksena on esitellä Microsoftin yritystieto-ominaisuuksia Excelissä, Pivot-taulukoissa, Power Pivotissa ja Power View'ssa.
Näissä opetusohjelmissa opit tuomaan ja tutkimaan tietoja Excelissä, luomaan ja tarkentamaan tietomalleja Power Pivotilla sekä luomaan Power View'ssa interaktiivisia raportteja, joita voit julkaista, suojata ja jakaa.
Tässä sarjassa on seuraavat opetusohjelmat:
- Tietojen tuonti Excel 2016:een ja tietomallin luominen
- Tietomallin suhteiden laajentaminen Excelin, Power Pivotin ja DAX-kielen avulla
- Karttapohjaisten Power View -raporttien luominen
- Internet-tietojen lisääminen ja Power View -raportin oletusarvojen määrittäminen
- PowerPivotin ohje
- Näyttävien Power View -raporttien luominen – osa 2
Tässä opetusohjelmassa aloitetaan tyhjästä Excel-työkirjasta.
Tässä opetusohjelmassa on seuraavat osiot:
- Tietojen tuonti tietokannasta
- Tietojen tuonti laskentataulukosta
- Tietojen tuonti kopioimalla ja liittämällä
- Yhteyden luominen tuotujen tietojen välille
- Tarkistuspiste ja testi
Tämän opetusohjelman lopussa on testi, jolla voit testata mitä olet oppinut.
Tässä opetusohjelmasarjassa käytetään tietoja, jotka kuvaavat olympiamitaleita, isäntämaita ja eri olympiakisatapahtumia. Suosittelemme kunkin opetusohjelman selaamista järjestyksessä.
Tietojen tuominen tietokannasta
Tämä opetusohjelma aloitetaan tyhjällä työkirjalla. Tämän osion tavoitteena on muodostaa yhteys ulkoiseen tietolähteeseen ja tuoda tietoja Exceliin lisäanalyysiä varten.
Aloitetaan lataamalla tietoja Internetistä. Tiedot kuvaavat olympiamitaleita, ja lähteenä on Microsoft Access -tietokanta.
Lataa tässä opetusohjelmasarjassa käytettävät tiedostot napsauttamalla seuraavia linkkejä. Lataa kukin neljästä tiedostosta sijaintiin, joka on helposti käytettävissä, kuten Ladatut tiedostot tai Omat tiedostot, tai uuteen luomaasi kansioon:
> OlympicMedals.accdb Access -tietokanta
> OlympicSports.xlsx Excel-työkirjaan
> Population.xlsx Excel-työkirjaan
> DiscImage_table.xlsx Excel-työkirjaanAvaa tyhjä työkirja Excelissä.
Valitse Tiedot > Hae tiedot > tietokannasta > Microsoft Access -tietokannasta. Valintanauha säätyy dynaamisesti työkirjan leveyden mukaan, joten valintanauhan komennot voivat erota seuraavassa kuvassa näkyvistä komennoista.
Valitse lataamasi Olympiamitalit.accdb-tiedosto ja valitse Tuo. Näkyviin tulee seuraava Etsintä-ikkuna, jossa näkyvät tietokannan taulukot. Tietokannan taulukot vastaavat Excelin laskentataulukoita tai taulukoita. Valitse Valitse useita taulukoita -ruutu ja valitse kaikki taulukot. Valitse sitten Lataa > kohteeseen.
Tietojen tuonti-ikkuna tulee näkyviin.
Huomautus
Huomaa ikkunan alareunassa oleva valintaruutu, jonka avulla voit lisätä nämä tiedot tietomalliin seuraavassa kuvassa. Tietomalli luodaan automaattisesti, kun tuot vähintään kaksi taulukkoa samanaikaisesti tai käsittelet niitä. Tietomalli integroi taulukot, mikä mahdollistaa laajan analyysin Pivot-taulukoiden, Power Pivotin ja Power View'n avulla. Kun tuot taulukoita tietokannasta, Excelin tietomalli luodaan näiden taulukoiden välisten olemassa olevien yhteyksien pohjalta. Tietomalli on läpinäkyvä Excelissä, mutta voit tarkastella ja muokata sitä suoraan Power Pivot -apuohjelmalla. Tietomallia käsitellään tarkemmin myöhemmin tässä opetusohjelmassa.
Valitse Pivot-taulukkoraportti-vaihtoehto, jolla taulukot tuodaan Exceliin ja Pivot-taulukko valmistellaan tuotujen taulukoiden analysointia varten, ja valitse OK.
Kun tiedot on tuotu, tuoduista taulukoista luodaan Pivot-taulukko.
Kun tiedot on tuotu Exceliin ja tietomalli on luotu automaattisesti, voit tarkastella tietoja.
Tietojen tarkastelu Pivot-taulukolla
Tuotujen tietojen tarkastelu käy helposti Pivot-taulukon avulla. Pivot-taulukossa kenttiä (Excelin sarakkeita vastaavia) vedetään taulukoista (kuten juuri Access-tietokannasta tuomasi taulukot) Pivot-taulukon eri alueille säätämään tietojen esitystapaa. Pivot-taulukossa on neljä aluetta: SUODATTIMET, SARAKKEET, RIVIT ja ARVOT.
Sen määrittäminen, mille alueelle kenttä olisi vedettävä, voi vaatia jonkin verran kokeilua. Voit vetää tarvitsemasi määrän kenttiä taulukoista, kunnes tiedot näkyvät haluamallasi tavalla Pivot-taulukossa. Voit vetää kenttiä Pivot-taulukon eri alueille; kenttien järjestäminen Pivot-taulukossa ei vaikuta pohjana oleviin tietoihin.
Tarkastellaan olympiamitaleja koskevia tietoja Pivot-taulukossa, ja aloitetaan lajin, mitalityypin ja urheilijan maan tai alueen mukaan järjestetyistä olympiamitalisteista.
Laajenna Pivot-taulukon kenttienMitalit-taulukkoa napsauttamalla sen vieressä olevaa nuolta. Etsi NOC_CountryRegion-kenttä laajennetusta Mitalit-taulukosta ja vedä se SARAKKEET-alueeseen. NOC viittaa kansallisiin olympiakomiteoihin (National Olympic Committee), jotka ovat maiden tai alueiden organisaatioyksikköjä.
Vedä seuraavaksi Urheilulajit-taulukosta jokin laji RIVIT-alueelle.
Suodatetaan lajit siten, että niitä näkyy vain viisi: jousiammunta, uimahypyt, miekkailu, taitoluistelu ja pikaluistelu. Voit tehdä tämän Pivot-taulukon kentät -alueelta tai Pivot-taulukon Riviotsikot-suodattimesta.
- Napsauta jotakin Pivot-taulukon kohtaa varmistaaksesi, että Excelin Pivot-taulukko on valittuna. Kun olet Pivot-taulukon kenttäluettelossa , jossa Urheilulajit-taulukko on laajennettu, pidä hiiren osoitinta sen Urheilulaji-kentän päällä, niin kentän oikealle puolelle tulee näkyviin avattavan valikon nuoli. Napsauta avattavaa valikkoa, napsauta **(Valitse kaikki)** poistaaksesi kaikki valinnat, vieritä sitten alas ja valitse jousiammunta, uimahypyt, miekkailu, taitoluistelu ja pikaluistelu. Valitse OK.
- Vaihtoehtoisesti voit napsauttaa Pivot-taulukon Riviotsikot-osan Riviotsikot-kentän avattavaa valikkoa, poista kaikki valinnat valitsemalla (Valitse kaikki), vieritä sitten alas ja valitse jousiammunta, uimahypyt, miekkailu, taitoluistelu ja pikaluistelu. Valitse OK.
Vedä Pivot-taulukon kenttienMitalit-taulukosta Mitali ARVOT-alueeseen. Arvojen on oltava numeerisia, joten Excel muuttaa mitalin automaattisesti Mitalimääräksi.
Valitse Mitalit-taulukosta uudelleen Mitali ja vedä se SUODATTIMET-alueelle.
Suodatetaan Pivot-taulukko näyttämään vain ne maat tai alueet, joissa on yhteensä yli 90 mitalia. Toimi seuraavasti.
- Napsauta Pivot-taulukon Sarakeotsikot-kentän oikealla puolella olevaa avattavaa valikkoa.
- Valitse Arvosuodattimet ja Suurempi kuin….
- Kirjoita 90 viimeiseen kenttään (oikealla). Valitse OK.
Pivot-taulukko näyttää seuraavalta ruudulta.
Olet nyt luonut vaivattomasti Pivot-perustaulukon, jossa on kenttiä kolmesta taulukosta. Tehtävä oli helppo, koska taulukoissa oli aiemmin luotuja yhteyksiä. Koska lähdetietokannassa oli aiemmin luotuja yhteyksiä ja koska toit kaikki taulukot samanaikaisesti, Excel pystyi toisintamaan nämä taulukoiden yhteydet tietomallissa.
Mutta entäpä tilanne, jossa tiedot ovat peräisin eri lähteistä tai ne on tuotu myöhemmin? Yleensä uudet tiedot voidaan lisätä luomalla toisiaan vastaaviin sarakkeisiin perustuvia yhteyksiä. Seuraavassa vaiheessa tuodaan lisää taulukoita, ja opit, miten luodaan uusia yhteyksiä.
Tietojen tuominen laskentataulukosta
Tuodaan nyt tietoja toisesta lähteestä, tällä kertaa työkirjasta, ja määritetään olemassa olevan ja uuden tiedon väliset suhteet. Suhteiden avulla voit analysoida tietokokoelmia Excelissä ja luoda mielenkiintoisia ja syventäviä visualisointeja tuomistasi tiedoista.
Aloitetaan luomalla tyhjä laskentataulukko ja tuomalla sitten tietoja vanhasta Excel-työkirjasta.
Lisää uusi Excel-laskentataulukko ja anna nimeksi Urheilu.
Etsi kansio, joka sisältää ladatut mallidatatiedostot, ja avaa Olympialajit.xlsx.
Valitse ja kopioi taulukon Taul1 tiedot. Jos valitset tietoja sisältävän solun, kuten solun A1, voit valita kaikki sen ympärillä olevat tiedot painamalla Ctrl+A-näppäinyhdistelmää. Sulje Olympialajit.xlsx-työkirja.
Siirrä kohdistin Urheilu-laskentataulukon soluun A1 ja liitä tiedot.
Kun tiedot ovat edelleen korostettuina, muotoile tiedot taulukoksi painamalla Ctrl+T-näppäinyhdistelmää. Voit muotoilla tiedot taulukoksi myös valintanauhasta valitsemalla ALOITUS > Muotoile taulukoksi. Koska tiedoissa on otsikot, valitse Taulukossa on otsikot näkyviin tulevassa Luo taulukko -ikkunassa, kuten kuvassa.
Tietojen muotoilulla taulukoksi on monia etuja. Voit nimetä taulukon, jotta se on helppo tunnistaa. Voit myös muodostaa suhteita taulukoiden välille, jolloin tietojen tarkastelu ja analysointi on mahdollista Pivot-taulukoissa, Power Pivotissa ja Power View'ssa.
Nimeä taulukko. Etsi TAULUKON RAKENNEOMINAISUUDET >-kohdassa Taulukon nimi -kenttä ja kirjoita Urheilu. Työkirja näyttää samalta kuin seuraavassa ruudussa.
Tallenna työkirja.
Tietojen tuonti kopioimalla ja liittämällä
Seuraavaksi voidaan tuoda tietoja verkkosivulla olevasta taulukosta tai mistä tahansa muusta lähteestä, josta tiedot voidaan kopioida ja liittää Exceliin. Seuraavissa vaiheissa olympiaisäntäkaupungit lisätään taulukosta.
- Lisää uusi Excel-laskentataulukko ja anna nimeksi Isännät.
- Valitse ja kopioi seuraava taulukko otsikkoineen.
- Siirrä Excelissä kohdistin Isännät-laskentataulukon soluun A1 ja liitä tiedot.
- Muotoile tiedot taulukoksi. Kuten kuvattu edellä, tiedot muotoillaan taulukoksi painamalla näppäinyhdistelmää Ctrl + T tai ALOITUSNÄYTÖSSÄ > Muotoile taulukoksi -vaihtoehtoa. Koska tiedoissa on otsikot, valitse Taulukossa on otsikot näkyviin tulevassa Luo taulukko -ikkunassa.
- Nimeä taulukko. Etsi TAULUKON RAKENNENÄKYMÄN > ominaisuudet -kohdasta Taulukon nimi -kenttä ja kirjoita Isännät.
- Valitse Versio-sarake ja muotoile se ALOITUS-välilehdestä Määräksi, jossa on 0 desimaalia.
- Tallenna työkirja. Työkirja on kuten seuraavassa ruudussa.
Nyt kun Excel-työkirjassa on taulukoita, voit luoda niiden välille yhteyksiä. Luomalla yhteyksiä taulukoiden välille voit yhdistää kahden taulukon tiedot.
Yhteyden luominen tuotujen tietojen välille
Voit aloittaa tuotujen taulukoiden käyttämisen heti Pivot-taulukoiden kentissä. Jos Excel ei voi määrittää, miten kenttä lisätään Pivot-taulukkoon, on muodostettava yhteys olemassa olevan tietomallin kanssa. Seuraavissa vaiheissa opetellaan luomaan yhteys eri lähteistä tuotujen tietojen välille.
Valitse Taul1-taulukonPivot-taulukon kenttien yläosassaKaikki, jotta saat näkyviin kaikki käytettävissä olevat taulukot seuraavassa kuvassa esitetyllä tavalla.
Näet juuri lisäämäsi uudet taulukot selaamalla luetteloa.
Laajenna Urheilu-taulukkoa ja lisää se Pivot-taulukkoon valitsemalla Urheilu. Excel kehottaa luomaan yhteyden niiden välille, kuten seuraavassa ruudussa.
Tämä ilmoitus annetaan, kun käytetään kenttiä taulukosta, joka ei kuulu pohjana olevaan tietomalliin. Yksi tapa lisätä taulukko tietomalliin on luoda yhteys taulukkoon, joka on jo tietomallissa. Yhteyden luomiseksi jossakin taulukoista on oltava sarake, jossa on yksilöllisiä, toistumattomia arvoja. Mallitiedoissa tietokannasta tuodussa Urheilulajit-taulukossa on urheilukoodeja sisältävä kenttä SportID. Samat urheilukoodit on esitetty kenttänä tuoduissa Excel-tiedoissa. Luodaan seuraavaksi yhteys.Valitsemalla LUO... korostetulla Pivot-taulukon kentät -alueella avaat Suhteen luominen -valintaikkunan, kuten seuraavassa ruudussa.
Valitse Taulukko-kohdan avattavasta luettelosta Tietomallitaulukko: Urheilulajit .
Valitse Sarake (viite) -kohdassa SportID.
Valitse Yhdistetty taulukko -kohdassa Tietomallitaulukko: Urheilu.
Valitse Liittyvä sarake (perus) -kohdassa SportID.
Valitse OK.
Pivot-taulukko muuttuu vastaamaan uutta yhteyttä. Pivot-taulukko ei näytä vielä täysin valmiilta RIVIT-alueen kenttien järjestyksen vuoksi. Urheilulaji on urheilun alaluokka, mutta koska Urheilulaji sijaitsee Urheilun yläpuolella RIVIT-alueella, sitä ei ole järjestetty kunnolla. Seuraavassa ruudussa näkyy tämä ei-toivottu järjestys.
Siirrä Urheilu Urheilulajin yläpuolelle RIVIT-alueella. Nyt Pivot-taulukon tiedot on esitetty haluamallasi tavalla, kuten seuraavassa ruudussa.
Excel muodostaa taustalla tietomallia, jota voidaan käyttää koko työkirjassa, kaikissa Pivot-taulukoissa, Pivot-kaavioissa, Power Pivot -raporteissa tai kaikissa Power View -raporteissa. Taulukoiden yhteydet ovat tietomallin perusta ja määrittävät siirtymis- ja laskentapolut.
Seuraavassa opetusohjelmassa, Tietomallin suhteiden laajentaminen Excelin, Power Pivotin** ja DAX-kielen** avulla, pohjaudutaan tässä opitun pohjalta ja laajennetaan tietomallia tehokkaalla ja visuaalisella Excelin apuohjelmalla nimeltä Power Pivot. Opit myös laskemaan taulukon sarakkeita ja käyttämään laskettua saraketta niin, että tietomalliin voidaan lisätä muutoin liittymätön taulukko.
Tarkistuspiste ja testi
Opitun kertaaminen
Olet luonut Excel-työkirjan, jossa on monien taulukoiden tietoja sisältävä Pivot-taulukko, joista monet on tuotu erikseen. Opit tuomaan tietoja tietokannasta, toisesta Excel-työkirjasta ja kopioimalla ja liittämällä niitä Exceliin.
Yhdistit tiedot luomalla taulukon yhteyden, jonka avulla Excel korreloi rivit. Lisäksi opit, että toisen taulukon sarakkeiden korrelointi toisen taulukon tietojen kanssa on tärkeää yhteyksien muodostamiselle ja toisiinsa liittyvien rivien hakemiselle.
Voit siirtyä sarjan seuraavaan opetusohjelmaan. Linkki:
Opetusohjelma: Tietomallin suhteiden laajentaminen Excelin, Power Pivotin ja DAX-kielen avulla
TESTI
Haluatko testata, miten hyvin muistat oppimasi asiat? Voit tehdä sen tässä. Seuraavassa testissä painotetaan toimintoja, ominaisuuksia tai vaatimuksia, joista olet saanut tietoja tässä opetusohjelmassa. Vastaukset on annettu sivun alaosassa. Onnea!
Kysymys 1: Miksi on tärkeää muuntaa tuodut tiedot taulukoiksi?
A: Niitä ei tarvitse muuntaa taulukoiksi, koska kaikki tuodut tiedot muuttuvat automaattisesti taulukoiksi.
B: Jos muunnat tuotuja tietoja taulukoiksi, niitä ei ole tietomallissa. Vain silloin, kun ne on jätetty tietomallin ulkopuolelle, ne ovat käytettävissä Pivot-taulukoissa, Power Pivotissa ja Power View'ssa.
C: Jos muunnat tuodut tiedot taulukoiksi, ne voidaan sisällyttää tietomalliin ja ottaa ne käyttöön Pivot-taulukoissa, Power Pivotissa ja Power View'ssa.
D: Tuotuja tietoja ei voi muuntaa taulukoiksi.
Kysymys 2: Mitkä seuraavista tietolähteistä voit tuoda Exceliin ja sisällyttää tietomalliin?
A: Access-tietokannat ja myös monet muut tietokannat.
B: Olemassa olevat Excel-tiedostot.
C: Kaikki, minkä voi kopioida ja liittää Exceliin ja muotoilla taulukoksi, kuten verkkosivustojen tietotaulukot, asiakirjat tai mitä tahansa muuta, minkä voi liittää Exceliin.
D: Kaikki edellä mainitut
Kysymys 3: Mitä Pivot-taulukolle tapahtuu, kun muutat kenttien järjestystä Pivot-taulukon kenttien neljällä alueella?
A: Ei mitään – kenttien järjestystä ei voi muuttaa, kun ne on sijoitettu Pivot-taulukon kenttien alueille.
B: Pivot-taulukon muoto muuttuu vastaamaan asettelua, mutta pohjana olevat tiedot eivät muutu.
C: Pivot-taulukon muoto muuttuu vastaamaan asettelua, ja kaikki pohjana olevat tiedot muuttuvat pysyvästi.
D: Pohjana olevat tiedot muuttuvat, ja tuloksena on uudet tietojoukot.
Kysymys 4: Mitä vaaditaan, kun taulukoiden välille muodostetaan yhteyttä?
A: Kummassakaan taulukossa ei voi olla sarakkeita, jotka sisältävät yksilöllisiä, toistumattomia arvoja.
B: Toinen taulukko ei saa olla Excel-työkirjan osa.
C: Sarakkeita ei saa muuntaa taulukoiksi.
D: Mikään edellä mainituista ei ole oikein.
Testin vastaukset
- Oikea vastaus: C
- Oikea vastaus: D
- Oikea vastaus: B
- Oikea vastaus: D
Huomautus
Tämän opetusohjelmasarjan tietojen ja kuvien lähteet:
- Olympialaisten tietojoukko: © Guardian News & Media Ltd.
- Lippukuvat: CIA Factbook (cia.gov)
- Väestötiedot: Maailmanpankki (worldbank.org)
- Olympialaisten urheilulajien kuvat: Thadius856 ja Parutakupiu