Kas olete kunagi kasutanud funktsiooni VLOOKUP veeru toomiseks ühest tabelist teise tabelisse? Excel sisaldab ka sisseehitatud andmemudelit, mis võimaldab luua tabelite vahel seoseid, mis võib olla alternatiiv otsingufunktsioonidele (nt VLOOKUP). Saate kahe andmetabeli vahel luua seose, võttes aluseks kummaski tabelis asuvate andmete vastendamise. Seejärel saate luua PivotTable-liigendtabeleid ja muid aruandeid iga tabeli väljadega isegi siis, kui tabelid pärinevad eri 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 loetletud PivotTable-liigendtabeli väljaloendis.
Seoseid kasutatakse kõige sagedamini PivotTable-liigendtabelite koostamisel mitmest tabelist andmemudelis. See võimaldab teil analüüsida seotud andmeid ilma neid ühte tabelisse ühendamata.
Märkus.
Kui teie töövihik sisaldab andmemudelit, saate tabeliseoseid hallata menüü Andmed kaudu.
Kui impordite seotud tabeleid relatsioonandmebaasist, saab Excel sageli 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ähendusriistade nimi. Klõpsake menüüs Tabeliriistad nuppu Kujundustabeli>nimi> ja sisestage nimi.
- Veenduge, et ühe tabeli veerus oleksid kordumatud, duplikaatideta andmeväärtused. Excel saab seose luua üksnes juhul, kui üks veerg sisaldab kordumatuid väärtusi.
Näiteks kliendimüügi seostamiseks ajateabega peavad mõlemad tabelid sisaldama kuupäevi samas vormingus (nt 01.01.2026) ja vähemalt üks tabel (ajateave) loetleb iga kuupäeva ainult üks kord veerus. - ValigeAndmeseosed>.
Kui nupp Seosed on tuhm, on põhjus selles, et teie töövihik sisaldab ainult ühte tabelit.
- Valige väljal seoste haldamine nupp 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
Seos on olemas, kui lohistate eri tabelite väljad PivotTable-liigendtabeli väljaloendisse. Kui teilt ei küsita seose loomise kohta, on Excelil juba olemas andmete seostamiseks vajalik seoseteave.
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.
- Exceli andmemudelis on seosed tavaliselt üks-ühele või üks-mitmele. Mitu-mitmele seosed nõuavad täiendavat modelleerimist (nt otsingutabelit kasutades). Mitu-mitmele seosed põhjustavad ringsõltuvustõrkeid ,nt "Tuvastati ringsõltuvus". See tõrge ilmneb juhul, kui loote otsese ühenduse kahe mitu-mitmele tabeli või kaudse ühenduse vahel (tabeliseoste ahel, mis on igas seoses üks-mitmele, kuid mitu-mitmele, kui vaatate lõpp-punkti). Lisateavet seoste kohta leiate artiklist Tabelitevahelised seosed andmemudelis.
Erinevalt otsinguvalemitest ei dubleeri seosed andmeid. Selle asemel lingitakse tabelid nii, et iga tabeli välju saaks kasutada koos PivotTable-liigendtabelis.
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.
"Tabelitevahelised seosed võivad olla vajalikud"
Väljade lisamisel PivotTable-liigendtabelisse teavitatakse teid sellest, kas tabeliseos on vajalik PivotTable-liigendtabelis valitud väljade mõtestamiseks.
Kuigi Excel oskab teile öelda, millal seost on vaja, ei saa excel teile öelda, milliseid tabeleid ja veerge kasutada või kas tabeliseos on isegi 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.
PivotTable-liigendtabelis kasutamisel on võimalik luua mitmetähenduslikke seoseid, mis ei sobi. Oletagem, et kõik teie tabelid on mingil viisil seotud mudeli muude tabelitega, kuid kui proovite kombineerida erinevate tabelite välju, kuvatakse teade "Tabelitevahelised seosed võivad olla vajalikud". Kõige tõenäolisem põhjus on see, et olete kokku puutunud mitu-mitmele seosega. 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, et teha kindlaks, kas mõni muu veerg (mujal 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 demograafilistest tabelitest leidma veeru, mis vastab juba kasutatavale tabelile. 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 duplikaate. Andmemudelis võrduvad nullväärtused ja tühjad stringid tühiväärtusega, mis on eraldi andmeväärtus. See tähendab, et otsinguveerg ei tohi sisaldada mitut nulli.
- 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.