Oletko joskus siirtänyt PHAKU-kaavalla sarakkeita taulukosta toiseen? Nyt kun Excelissä on sisäinen Tietomalli, PHAKU on vanhentunut. Voit luoda kahden taulukon välille suhteen, joka perustuu kunkin taulukon välisiin täsmääviin tietoihin. Sitten voit luoda Power View -taulukoita ja rakentaa Pivot-taulukoita ja muita raportteja, joissa käytetään kunkin taulukon kenttiä, vaikka taulukot olisivat peräisin eri lähteistä. Esimerkiksi jos tuot asiakasmyyntitietoja, voit haluta analysoida myyntitrendejä vuoden ja kuukauden mukaan lisäämällä ja liittämällä aikatietoja.
Kaikki työkirjan taulukot on lueteltu Pivot-taulukossa ja Power View -kenttäluetteloissa.
Kun tuot toisiinsa liittyviä taulukoita relaatiotietokannasta, Excel voi usein luoda näitä suhteita tietomallissa, jota se muodostaa taustalla. Kaikissa muissa tapauksissa suhteet on luotava manuaalisesti.
-
Varmista, että työkirjassa on vähintään kaksi taulukkoa, ja että jokaisessa taulukossa on sarake, joka voidaan määrittää toisen taulukon sarakkeeseen.
-
Tee jompikumpi seuraavista: Muotoile tiedot taulukoksi tai Tuo ulkoiset tiedot taulukoksi uudessa laskentataulukossa.
-
Anna kullekin taulukolle kuvaava nimi: valitse Taulukkotyökalut-kohdassa Rakenne > Taulukon nimi > kirjoita nimi.
-
Varmista, että yhden taulukon yhdessä sarakkeessa on yksilöllisiä, kopioimattomia tietoarvoja. Excel voi luoda suhteen vain, jos yhdessä sarakkeessa on vain yksilöllisiä arvoja.
Jos haluat esimerkiksi yhdistää asiakasmyynnin aikatietoihin, kummankin taulukon päivämäärien on oltava samassa muodossa (esimerkiksi 1.1.2012), ja vähintään yhdessä taulukossa (aikatiedot) kunkin päivämäärän tulee olla lueteltuna sarakkeessa vain kerran.
-
Valitse Tiedot > Yhteydet.
Jos Yhteydet näkyy harmaana, työkirjassa on vain yksi taulukko.
-
Valitse Yhteyksien hallinta -ikkunassa Uusi.
-
Napsauta Yhteyden luominen -ikkunan Taulukko-kohdan nuolta ja valitse taulukko luettelosta. Yksi-moneen-yhteydessä tämän taulukon tulisi olla monta-puolella. Jos käytetään asiakas- ja aikatietojen esimerkkiä, ensin on valittava asiakasmyyntitaulukko, koska myyntitapahtumia on todennäköisesti monia minä tahansa päivänä.
-
Valitse Sarake (viite) -kohdassa sarake, joka sisältää Yhdistetty sarake (ensisijainen) -kohtaan liittyvät tiedot. Esimerkiksi jos molemmissa taulukoissa olisi päivämääräsarake, valitsisit kyseisen sarakkeen.
-
Valitse Yhdistetty taulukko -kohdassa taulukko, jossa on vähintään yksi Taulukko-kohdassa valitsemaasi taulukkoon liittyvä tietosarake.
-
Valitse Yhdistetty sarake (ensisijainen) -kohdassa sarake, jossa on yksilöllisiä arvoja, jotka vastaavat Sarake-kohdassa valitsemasi sarakkeen arvoja.
-
Valitse OK.
Lisätietoja Excel-taulukoiden välisistä yhteyksistä
Huomautuksia yhteyksistä
-
Näet, onko yhteyttä olemassa, kun vedät kenttiä eri taulukoista Pivot-taulukon kenttäluetteloon. Jos et saa kehotusta luoda yhteyttä, Excelillä on jo tietojen liittämiseen tarvittavat yhteystiedot.
-
Suhteiden luominen vastaa PHAKU-toiminnon käyttöä: tarvitaan täsmääviä tietoja sisältäviä sarakkeita, jotta Excel voi tehdä ristiinviittauksen jonkin taulukon riveihin toisen taulukon riveillä. Aikatietojen esimerkissä Asiakas-taulukossa tulisi olla päivämääräarvoja, jotka ovat olemassa myös aikatietotaulukossa.
-
Tietomallissa taulukkoyhteydet voivat olla yksi-yhteen (jokaisella matkustajalla on yksi tarkistuskortti) tai yksi-moneen (jokaisella lennolla on monia matkustajia), mutta eivät monta-moneen. Monta-moneen-yhteydet johtavat kehäriippuvuusvirheisiin, kuten virheeseen "Kehäriippuvuus havaittu". Tämä virhe tapahtuu, jos luot suoran yhteyden kahden monta-moneen-taulukon välille, tai epäsuoria yhteyksiä (taulukkoyhteyksien ketju, jossa kunkin yhteyden sisällä on yksi-moneen-yhteyksiä, mutta päästä päähän katsottuna monta-moneen-yhteyksiä). Lisätietoja on kohdassa Taulukoiden väliset yhteydet tietomallissa.
-
Sarakkeiden tietotyyppien on oltava yhteensopivia. Lisätietoja on kohdassa Tietotyypit Excel-tietomalleissa.
-
Muut yhteyksien luomistavat voivat olla intuitiivisempia, varsinkin jos et ole varma, mitä sarakkeita pitäisi käyttää. Lisätietoja on kohdassa Yhteyden luominen Power Pivotin kaavionäkymässä.
Esimerkki: Aikatietojen liittäminen lentoyhtiön lentotietoihin
Voit tutustua taulukkoyhteyksiin ja aikatietoihin tarkemmin harjoittelemalla Microsoft Azure Marketplacesta maksutta saatavilla tiedoilla. Jotkut näistä tietopaketeista ovat hyvin suuria, ja tietojen lataamiseen kohtuullisessa ajassa vaaditaan nopea Internet-yhteys.
-
Käynnistä Power Pivot in Microsoft Excel -apuohjelma ja avaa Power Pivot -ikkuna.
-
Valitse Nouda ulkoiset tiedot > datapalvelusta > Microsoft Azure Marketplacesta. Microsoft Azure Marketplacen aloitussivu avautuu ohjatussa taulukon tuomisessa.
-
Valitse Hinta-kohdasta Maksuton.
-
Valitse Luokka-kohdasta Tiede ja tilastotiedot.
-
Etsi DateStream ja valitse Tilaa.
-
Kirjoita Microsoft-tili ja salasana ja valitse Kirjaudu sisään. Ikkunaan pitäisi avautua tietojen esikatselunäkymä.
-
Selaa näkymän alaosaan ja valitse Valintakysely.
-
Valitse Seuraava.
-
Valitse BasicCalendarUS, ja tuo tiedot valitsemalla Valmis. Tuomiseen kuluu noin minuutti nopealla Internet-yhteydellä. Kun tiedot on tuotu, näkyviin tulee tilaraportti 73 414 tuodusta rivistä. Valitse Sulje.
-
Tuo toinen tietojoukko valitsemalla Nouda ulkoiset tiedot > Tietopalvelusta > Microsoft Azure Marketplacesta.
-
Valitse Tyyppi-kohdasta Tiedot.
-
Valitse Hinta-kohdasta Maksuton.
-
Etsi USA:n lentoyhtiön viivästyneet lennot ja valitse Valitse.
-
Selaa näkymän alaosaan ja valitse Valintakysely.
-
Valitse Seuraava.
-
Tuo tiedot valitsemalla Valmis. Nopealla Internet-yhteydellä tuominen voi kestää 15 minuuttia. Kun tiedot on tuotu, näkyviin tulee tilaraportti 2 427 284 tuodusta rivistä. Valitse Sulje. Tietomallissa pitäisi olla nyt kaksi taulukkoa. Jokaisessa taulukossa tulee olla yhteensopivia sarakkeita, jotta taulukot voidaan yhdistää.
-
Huomaa, että DateKey-kohdassa BasicCalendarUS on muodossa 1.1.2012 12:00:00. On_Time_Performance -taulukossa on myös datetime-sarake FlightDate, jonka arvot on määritetty samassa muodossa: 1.1.2012 12:00:00. Nämä kaksi saraketta sisältävät täsmääviä tietoja, jotka ovat samaa tyyppiä, ja vähintään yksi sarakkeista (DateKey) sisältää vain yksilöllisiä arvoja. Seuraavia ohjeita noudattamalla voit käyttää näitä sarakkeita taulukoiden yhdistämiseen.
-
Luo Pivot-taulukko uuteen tai olemassa olevaan laskentataulukkoon valitsemalla Power Pivot -ikkunasta Pivot-taulukko.
-
Laajenna kenttäluettelosta On_Time_Performance ja lisää ArrDelayMinutes Arvot-alueelle napsauttamalla sitä. Pivot-taulukossa pitäisi näkyä viivästyneiden lentojen kokonaisaika minuutteina.
-
Laajenna BasicCalendarUS ja lisää MonthInCalendar Rivit-alueelle napsauttamalla sitä.
-
Huomaa, että Pivot-taulukossa näkyy nyt kuukaudet, mutta joka kuukauden kokonaisaika minuutteina on sama. Identtiset arvot tarkoittavat, että taulukoiden välille tarvitaan yhteys.
-
Valitse Luo kenttäluettelon kohdasta “Taulukoiden välille tarvitaan ehkä yhteydet”.
-
Valitse yhdistetystä taulukosta On_Time_Performance ja yhdistetystä sarakkeesta (ensisijainen) FlightDate.
-
Valitse taulukosta BasicCalendarUS ja sarakkeesta (viite) DateKey. Luo yhteys valitsemalla OK.
-
Huomaa, että minuuttien kokonaismäärä vaihtelee nyt eri kuukausien mukaan.
-
Vedä BasicCalendarUS-kohdan YearKey Rivit-alueelle MonthInCalendar-kohdan yläpuolelle.
Nyt voit osittaa saapumisaikojen viivästymiset vuoden ja kuukauden mukaan tai muiden kalenterin arvojen mukaan.
Vihjeitä: Kuukaudet on lueteltu oletusarvon mukaisesti aakkosjärjestyksessä. Kun käytät Power Pivot -apuohjelmaa, voit muuttaa lajittelua niin, että kuukaudet näkyvät kronologisessa järjestyksessä.
-
Varmista, että BasicCalendarUS-taulukko on auki Power Pivot -ikkunassa.
-
Valitse Aloitus-taulukosta Lajittele sarakkeen mukaan.
-
Valitse Lajittele-kohdasta MonthInCalendar
-
Valitse Lajitteluperuste-kohdasta MonthOfYear.
Pivot-taulukko lajittelee jokaisen kuukausi-vuosi-yhdistelmän (lokakuu 2011, marraskuu 2011) kuukauden numeron mukaan (10, 11). Lajittelujärjestyksen muuttaminen on helppoa, koska DateStream-syöte antaa kaikki tarvittavat sarakkeet, jotta tämä skenaario toimii. Jos käytät eri aikatietotaulukkoa, vaihe on erilainen.
"Taulukoiden väliset yhteydet voivat olla tarpeen"
Kun lisäät kenttiä Pivot-taulukkoon, saat ilmoituksen, jos taulukkoyhteyttä tarvitaan selventämään kenttiä, jotka olet valinnut Pivot-taulukkoon.
Excel voi ilmoittaa, kun yhteys tarvitaan, mutta se ei voi kertoa, mitä taulukoita ja sarakkeita on käytettävä tai onko taulukkoyhteys edes mahdollinen. Saat tarvitsemasi vastauksen noudattamalla seuraavia vaiheita.
Vaihe 1: Määritä, mitä taulukoita yhteyteen on määritettävä
Jos mallissa on vain muutama taulukko, voi olla heti selvää, mitä taulukoita tarvitaan. Suurissa malleissa voit kuitenkin tarvita hieman apua. Yksi tapa on käyttää kaavionäkymää Power Pivot -apuohjelmassa. Kaavionäkymä antaa visuaalisen esityksen kaikista tietomallin taulukoista. Kaavionäkymän avulla voit nopeasti määrittää, mitkä taulukot ovat erillisiä muusta mallista.
Huomautus: On mahdollista luoda monitulkintaisia suhteita, jotka eivät kelpaa käytettäviksi Pivot-taulukossa tai Power View -raportissa. Oletetaan, että kaikki taulukot liittyvät jollain tavalla mallin muihin taulukoihin, mutta kun yrität yhdistää eri taulukoiden kenttiä, saat viestin “Taulukoiden välille tarvitaan ehkä yhteys”. Todennäköisin syy on, että kyseessä on monta-moneen-yhteys. Jos seuraat käytettäviin taulukoihin liittyvien taulukkoyhteyksien ketjua, huomaat todennäköisesti, että siinä on kaksi tai useampia yksi-moneen-yhteyksiä. Kaikissa tilanteissa toimivaa, helppoa vaihtoehtoista menetelmää ei ole, mutta voit yrittääkoota käytettävät sarakkeet yhteen taulukkoon luomalla laskettuja sarakkeita.
Vaihe 2: Etsi sarakkeita, joilla voidaan luoda polku yhdestä taulukosta toiseen
Kun olet määrittänyt, mikä taulukko on erillinen muusta mallista, tarkista taulukon sarakkeet ja määritä, onko mallissa toinen samat arvot sisältävä sarake.
Oletetaan esimerkiksi, että mallissa on tuotteiden myyntitiedot alueen mukaan, ja haluat tarkistaa, onko myynnin ja demografisten trendien välillä korrelaatio, tuomalla demografiset tiedot. Demografiset tiedot tulevat eri tietolähteestä, joten nämä taulukot ovat aluksi erillään muusta mallista. Jos haluat integroida demografiset tiedot mallin muun osan kanssa, sinun on löydettävä jostakin demografisista taulukoista sarake, joka vastaa jo käyttämääsi taulukkoa. Jos demografiset tiedot on järjestetty esimerkiksi alueen mukaan, ja myyntitiedoissa on määritetty, millä alueella myynti on tapahtunut, voit yhdistää kaksi tietojoukkoa etsimällä yhteisen sarakkeen, kuten osavaltion, postinumeron tai alueen sarakkeen.
Täsmäävien arvojen lisäksi yhteyden luomiseen edellytetään muutamia lisävaatimuksia:
-
Hakusarakkeen tietoarvojen tulee olla yksilöllisiä. Toisin sanoen sarakkeessa ei saa olla kaksoisarvoja. Tietomallissa nollat ja tyhjät merkkijonot vastaavat tyhjää, joka on erillinen tietoarvo. Tämä tarkoittaa, että hakusarakkeessa ei saa olla useita nollia.
-
Niin lähdesarakkeen kuin hakusarakkeen tietotyyppien on oltava yhteensopivia. Lisätietoja tietotyypeistä on kohdassa Tietotyypit tietomalleissa.
Lisätietoa taulukkoyhteyksistä on kohdassa Taulukoiden väliset suhteet tietomallissa.
