Kas olete kasutanud funktsiooni VLOOKUP veeru toomiseks ühest tabelist teise tabelisse? Excelis on andmemudel nüüd sisse ehitatud ja VLOOKUP pole enam vajalik. Saate kahe andmetabeli vahel luua seose, võttes aluseks kummaski tabelis asuvate andmete vastendamise. Seejärel saate luua Power View’ lehed ning koostada PivotTable-liigendtabeleid ja muid aruandeid, mis sisaldavad mõlema tabeli välju – seda ka siis, kui tabelid pärinevad erinevatest allikatest. Kui teil on näiteks klientide müügiandmed, võiksite importida ja seostada ka ajateabeandmed, et saaksite analüüsida müügitrende aastate ja kuude lõikes.
Kõik töövihiku tabelid on ära toodud PivotTable-liigendtabeli ja Power View’ väljaloendites.
Seotud tabelite relatsioonandmebaasist importimisel saab Excel tihti luua need seosed taustal koostatavas andmemudelis. Kõigil muudel juhtudel peate seosed käsitsi looma.
-
Veenduge, et töövihikus oleks vähemalt kaks tabelit ja et igas tabelis oleks veerg, mille saab vastendada teise tabeli veeruga.
-
Tehke ühte järgmistest. Vormindage andmed tabelina või Importige välisandmed uuel töölehel tabelina.
-
Pange igale tabelile tähenduslik nimi. Nime sisestamiseks klõpsake menüüs Tabeliriistad alammenüüd Kujundus ja siis nuppu Tabeli nimi.
-
Veenduge, et ühe tabeli veerus oleksid kordumatud, duplikaatideta andmeväärtused. Excel saab seose luua üksnes juhul, kui üks veerg sisaldab kordumatuid väärtusi.
Kui soovite näiteks omavahel seostada klientide müügiandmed ja ajateabe, peavad mõlemad tabelid sisaldama samas vormingus kuupäevi (nt 01.01.2012) ja vähemalt ühes tabelis (ajateabetabelis) peab iga kuupäev veerus esinema ainult üks kord.
-
Valige Andmed > Seosed.
Kui nupp Seosed on tuhm, on põhjus selles, et teie töövihik sisaldab ainult ühte tabelit.
-
Klõpsake dialoogiboksis Halda seoseid nuppu Uus.
-
Klõpsake dialoogiboksis Loo seos nupu Tabel allanoolt ja valige loendist soovitud tabel. Seoses „üks mitmele“ peaks see tabel olema „mitme“ poolel. Eeltoodud klientide ja ajateabe näidet kasutades tuleks esmalt valida klientide müügiandmete tabel, kuna igas päevas leiab tõenäoliselt aset mitu müüki.
-
Atribuudi Veerg (väline) jaoks valige veerg, mis sisaldab veeruga Seotud veerg (primaarne) seotud andmeid. Kui teil oli näiteks mõlemas tabelis kuupäevaveerg, tuleks nüüd see veerg valida.
-
Atribuudiks Seotud tabel valige tabel, milles on vähemalt üks veerg andmeid, mis on seotud tabeliga, mille valisite just atribuudiks Tabel.
-
Atribuudiks Seotud veerg (primaarne) valige veerg, milles on kordumatud väärtused, mis vastavad väärtustele veerus, mille valisite atribuudiks Veerg.
-
Klõpsake nuppu OK.
Lisateavet Exceli tabelivaheliste seoste kohta
Märkmed seoste kohta
-
Seose olemasolu kontrollimiseks lohistage väljad erinevatest tabelitest PivotTable-liigendtabeli väljaloendisse. Kui teilt ei küsita seose loomise kohta, on Excelil juba andmete seostamiseks vajalik teave olemas.
-
Seoste loomine sarnaneb funktsiooni VLOOKUP kasutamisega: vajate veerge, mis sisaldavad sobivaid andmeid, et Excel saaks luua ristviited ühe tabeli ja teise tabeli ridade vahel. Ajaanalüüsi näite korral peaks tabel Klient sisaldama kuupäevaväärtusi, mis on olemas ka ajaanalüüsi tabelis.
-
Andmemudelis võivad tabeliseosed olla nii „üks ühele“ (igal reisijal on üks pardakaart) kui ka „üks mitmele“ (igal lennul on palju reisijaid), kuid mitte „mitu mitmele“. „Mitu mitmele“ seosed põhjustavaid ringsõltuvustõrkeid (nt „Tuvastati ringsõltuvus.“). See tõrge tekib siis, kui loote otseühenduse kahe mitu-mitmele tabeli vahel või kaudsed ühendused (selliste tabeliseoste ahel, mille korral iga seos ise on küll üks-mitmele, ent üldkokkuvõttes vaadatuna mitu-mitmele). Lisateavet seoste kohta leiate artiklist Tabelitevahelised seosed andmemudelis.
-
Kahe veeru andmetüübid peavad omavahel ühilduma. Täpsemat teavet leiate artiklist Andmetüübid Exceli andmemudelites.
-
Seoste loomiseks on olemas ka teisi ja intuitiivsemaid viise – eriti juhul, kui te pole kindel, milliseid veerge kasutada. Lugege artiklit Seose loomine diagrammivaates lisandmoodulis Power Pivot.
Näide: ajateabeandmete seostamine lennuliini lennuandmetega
Nii tabeliseoste kui ka ajateabe kohta saate täpsemat teavet Microsoft Azure'i turuplatsil leiduvaid tasuta andmeid kasutades. Osa neid andmekomplekte on väga mahukad ja seetõttu tuleb arvestada, et andmete allalaadimiseks mõistliku aja jooksul on vaja kiiret Interneti-ühendust.
-
Käivitage lisandmoodul Power Pivot in Microsoft Excel ja avage Power Pivoti aken.
-
Valige Too välisandmed > Andmeteenusest > Microsoft Azure'i turuplatsilt. Tabeliimpordiviisardis avatakse Microsoft Azure'i turuplatsi avaleht.
-
Klõpsake jaotises Price (Hind) väärtust Free (Tasuta).
-
Klõpsake jaotises Category (Kategooria) väärtust Science & Statistics (Teadus ja statistika).
-
Otsi DateStream ja klõpsake Telli.
-
Sisestage oma Microsofti konto ja klõpsake nuppu Logi sisse. Aknas kuvatakse andmete eelvaade.
-
Liikuge allapoole ja klõpsake nuppu Valikpäring.
-
Klõpsake nuppu Edasi.
-
Valige BasicCalendarUS (Lihtne kalender, USA vorming) ja klõpsake siis andmete importimiseks nuppu Valmis. Kiire Interneti-ühenduse korral peaks importimine võtma umbes minuti. Kui olete valmis, peaksite nägema olekuteadet 73 414 rea ülekandmise kohta. Klõpsake nuppu Sule.
-
Teise andmehulga importimiseks valige Too välisandmed > Andmeteenusest > Microsoft Azure'i turuplatsilt.
-
Klõpsake jaotises Type (Tüüp) väärtust Data (Andmed).
-
Klõpsake jaotises Price (Hind) väärtust Free (Tasuta).
-
Leidke üles US Air Carrier Flight Delays (USA lennuliinide hilinemised) ja klõpsake nuppu Select (Vali).
-
Liikuge allapoole ja klõpsake nuppu Valikpäring.
-
Klõpsake nuppu Edasi.
-
Andmete importimiseks klõpsake nuppu Valmis. Kiire Interneti-ühenduse korral võib importimiseks kuluda ligikaudu 15 minutit. Kui olete valmis, peaksite nägema olekuteadet 2 427 284 rea ülekandmise kohta. Klõpsake nuppu Sule. Teie andmemudelis peaks nüüd olema kaks tabelit. Tabelite seostamiseks läheb kummaski tabelis vaja ühilduvaid veerge.
-
Nagu näete, on DateKey (KuupäevaKood) tabelis BasicCalendarUS kujul 1/1/2012 12:00:00 AM. Ka tabel On_Time_Performance sisaldab kuupäeva ja kellaaja veergu FlightDate (Lennukuupäev), mille väärtused on määratud samas vormingus: 1/1/2012 12:00:00 AM. Need kaks veergu sisaldavaid samalaadseid andmeid, mille andmetüüp on sama ning vähemalt üks veergudest (DateKey) sisaldab ainult kordumatuid väärtusi. Järgmistes toimingutes kirjeldatakse nende veergude kasutamist tabelite seostamiseks.
-
PivotTable-liigendtabeli loomiseks uuel või olemasoleval töölehel klõpsake Power Pivoti aknas nuppu PivotTable-liigendtabel.
-
Laiendage väljaloendis tabelit On_Time_Performance ja klõpsake selle lisamiseks väärtustealale väärtust ArrDelayMinutes. PivotTable-liigendtabelis peaksite nüüd nägema lendude hilinemise koguaega minutites.
-
Laiendage tabelit BasicCalendarUS ja klõpsake väärtust MonthInCalendar, et lisada see ridadealale.
-
Nagu näete, on PivotTable-liigendtabelis nüüd ära toodud kuud, ent minutite kogusumma on igas kuus sama. Korduvad samased väärtused näitavad, et vaja on luua seos.
-
Klõpsake väljaloendi jaotises „Vaja võib minna tabelitevahelisi seoseid“ nuppu Loo.
-
Valige jaotises „Seotud tabel“ väärtus On_Time_Performance ja jaotises „Seotud veerg (primaarne)“ väärtus FlightDate.
-
Valige jaotises Tabel väärtus BasicCalendarUS ja jaotises „Veerg (väline)“ väärtus DateKey. Seose loomiseks klõpsake nuppu OK.
-
Nagu näete, on hilinemisminutite arv nüüd igas kuus erinev.
-
Lohistage tabelist BasicCalendarUS väärtus YearKey alale Read (väärtuse MonthInCalendar kohale).
Nüüd saate saabumiste hilinemisi tükeldada nii aasta kui ka kuu alusel ning ka muude kalendriväärtuste järgi.
Näpunäited: Vaikimisi on kuud tähestikulises järjestuses. Lisandmooduli Power Pivot abil saate sortimist muuta nii, et kuud kuvataks kronoloogilises järjestuses.
-
Veenduge, et tabel BasicCalendarUS oleks Power Pivoti aknas avatud.
-
Klõpsake menüüs Avaleht nuppu Sordi veeru alusel.
-
Valige jaotises Sortimine väärtus MonthInCalendar.
-
Valige jaotises Alus väärtus MonthOfYear.
PivotTable-liigendtabelis sorditakse nüüd iga kuu ja aasta kombinatsioon (Oktoober 2011, November 2011) kuunumbri järgi aastas (10, 11). Sortimisjärjestust on lihtne muuta, kuna kanal DateStream sisaldab kõiki selle stsenaariumi toimimiseks vajalikke veerge. Kui kasutate mõnda muud ajaanalüüsitabelit, erineb toiming siinkirjeldatust.
„Tabelitevahelised seosed võivad olla nõutavad“
Väljade lisamisel PivotTable-liigendtabelisse teavitatakse teid, kui tabeliseos on PivotTable-liigendtabelis valitud väljadest arusaamiseks nõutav.
Ehkki Excel oskab teile öelda, kui seos on vajalik, ei saa ta teile öelda seda, milliseid tabeleid ja veerge kasutada või kas tabeliseose loomine on üldse võimalik. Soovitud vastuste saamiseks proovige järgmisi variante.
1. toiming: määratlege, millised tabelid seoses määrata
Kui teie mudelis on ainult mõni tabel, saate tõenäoliselt kohe aru, milliseid peaksite kasutama. Suuremate mudelite korral võib teil aga abi tarvis minna. Üks võimalus on kasutada Power Pivoti lisandmoodulis diagrammivaadet. Diagrammivaade annab visuaalse ülevaate kõigist andmemudeli tabelitest. Diagrammivaate abil saate kiiresti määratleda, millised tabelid on ülejäänud mudelist omaette.
Märkus.: Luua saab ka mitmemõttelisi seoseid, mis ei sobi PivotTable-liigendtabelis või Power View aruandes kasutamiseks. Oletagem, et kõik teie tabelid on mingil viisil seotud teiste andmemudeli tabelitega, ent kui proovite erinevate tabelite välju omavahel kombineerida, kuvatakse teade "Tabelitevahelised seosed võivad olla nõutavad". Põhjus on tõenäoliselt selles, et olete kasutanud seost "mitu mitmele". Kui vaatate lähemalt soovitud tabelitega seotud tabeliseoste ahelat, näete ilmselt, et teil on vähemalt kaks üks-mitmele tabeliseost. Ühte lihtsat lahendust, mis toimiks igas olukorras, ei ole, kuid võite näiteks proovida luua arvutuslikud veerud, et konsolideerida soovitud veerud ühte tabelisse.
2. toiming: leidke veerud, mida saab kasutada ühest tabelist teise viiva tee loomiseks
Kui olete kindlaks teinud, milline tabel on ülejäänud mudelist lahutatud, vaadake selle veerud üle ja tehke kindlaks, kas mõni muu veerg samas mudelis sisaldab vastavaid väärtusi.
Oletagem näiteks, et teil on mudel, mis sisaldab toodete müüginäitajaid piirkondade lõikes, ning seejärel impordite demograafilised andmed, et teha kindlaks, kas iga territooriumi müüginäitajate ja demograafiliste trendide vahel on seos. Kuna demograafilised andmed pärinevad mõnest muust andmeallikast, on selle tabelid alguses ülejäänud mudelist isoleeritud. Demograafiliste andmete integreerimiseks ülejäänud mudeliga peate ühes demograafiatabelitest üles otsima veeru, mis vastab mõnele teie juba kasutatavale veerule. Kui demograafilised andmed on näiteks korraldatud piirkonniti ja teie müügiandmetes on määratud, millises piirkonnas müük aset leidis, saate need kaks andmehulka omavahel seostada nii, et määrate ühe ühise veeru (nt maakond, sihtnumber või piirkond), mida otsingu jaoks kasutada.
Lisaks vastavuses olevatele väärtustele on seose loomiseks veel mõned nõuded.
-
Otsinguveeru andmeväärtused peavad olema kordumatud. Teisisõnu ei tohi veerg sisaldada kordusi. Andmemudelis võrduvad nullväärtused ja tühjad stringid tühiväärtusega, mis on eraldi andmeväärtus. See tähendab, et otsinguveerus ei või mitut nulli olla.
-
Lähteveeru ja otsinguveeru andmetüübid peavad omavahel ühilduma. Lisateavet andmetüüpide kohta leiate artiklist Andmemudelites toetatud andmetüübid.
Tabeliseoste kohta leiate lisateavet artiklist Tabelitevahelised seosed andmemudelis.
