Ar bandėte perkelti vienos lentelės stulpelį į kitą lentelę naudodami VLOOKUP? Dabar, kai „Excel“ įtaisytas duomenų modelis, VLOOKUP nebenaudojama. Galite sukurti ryšį tarp dviejų duomenų lentelių pagal lentelių duomenų atitikimą. Tada galite sukurti „Power View“ lapus ir sudaryti „PivotTable“ ir kitokias ataskaitas su laukais iš bet kurios lentelės, net jei lentelės yra iš skirtingų šaltinių. Pavyzdžiui, jei turite kliento pardavimo duomenis, galite norėti importuoti ir susieti laiko informacijos duomenis, kad ištirtumėte pardavimo pobūdį pagal metus ir mėnesius.
Visos darbaknygės lentelės išvardytos „PivotTable“ ir „Power View“ laukų sąrašuose.
Importuojant susijusias lenteles iš sąryšinės duomenų bazės, „Excel“ dažnai gali sukurti tuos ryšius fone kuriamame duomenų modelyje. Kitais atvejais ryšius reikia kurti neautomatiniu būdu.
-
Įsitikinkite, kad darbaknygėje yra bent dvi lentelės ir kiekvienoje lentelėje yra stulpelis, kurį galima susieti su kitos lentelės stulpeliu.
-
Atlikite vieną iš šių veiksmų: formatuokite duomenis kaip lentelę arba Importuokite išorinius duomenis kaip lentelę naujame darbalapyje.
-
Suteikite kiekvienai lentelei prasmingą pavadinimą: Dalyje Lentelės įrankiai spustelėkite Dizainas > Lentelės pavadinimas ir įveskite pavadinimą.
-
Patikrinkite, ar vienos iš lentelės stulpelyje yra unikalios duomenų reikšmės be dublikatų. „Excel“ gali sukurti ryšį, tik jei viename stulpelyje yra unikalios reikšmės.
Pavyzdžiui, norint susieti kliento pardavimo duomenis su laiko informacija, abiejose lentelėse turi būti to paties formato datos (pvz., 2012-01-01) ir bent vienoje lentelėje (laiko informacijos) esančiame stulpelyje datos reikšmės turi nesikartoti.
-
Spustelėkite Duomenys > Ryšiai.
Jei parinktis Ryšiai yra papilkinta, darbaknygėje yra tik viena lentelė.
-
Dialogo lange Valdyti ryšius spustelėkite Naujas.
-
Lauke Kurti ryšį spustelėkite parinkties Lentelė rodyklę ir pasirinkite lentelę iš sąrašo. Ryšyje „vienas su daugeliu“ ši lentelė turėtų būti pusėje „daugelis“. Naudodami mūsų klientų ir laiko informacijos pavyzdį, pirmiau turėtumėte pasirinkti klientų pardavimo lentelę, nes per dieną gali įvykti keli pardavimai.
-
Naudodami Stulpelis (išorinis) pasirinkite stulpelį, kuriame yra duomenų, susijusių su parinktimi Susijęs stulpelis (pagrindinis). Pavyzdžiui, jei abiejose lentelėse yra duomenų stulpelis, dabar turėtumėte pasirinkti tą stulpelį.
-
Naudodami Susijusi lentelė pasirinkite lentelę, kurioje yra bet vienas duomenų, susijusių su lentele, pasirinkta parinktyje Lentelė, stulpelis.
-
Naudodami Susijęs stulpelis (pagrindinis) pasirinkite stulpelį, kuriame yra unikalių reikšmių, atitinkančių stulpelio, pasirinkto parinktyje Stulpelis, reikšmes.
-
Spustelėkite Gerai.
Daugiau apie ryšius tarp „Excel“ lentelių
Pastabos apie ryšius
-
Vilkdami laukus iš įvairių lentelių į „PivotTable“ laukų sąrašą, sužinosite, ar yra ryšių. Jeigu nerodomas raginimas sukurti ryšį, „Excel“ jau turi ryšio informaciją, kurios programai reikia duomenims sujungti.
-
Ryšių kūrimas panašus kaip naudojant VLOOKUP: reikės stulpelių, kuriuose yra sutampančių duomenų, kad „Excel“ galėtų kryžmiškai nurodyti vienos ir kitos lentelės eilutes. Laiko informacijos pavyzdyje klientų lentelėje turi būti nurodytos duomenų reikšmės, kurios yra ir laiko informacijos lentelėje.
-
Duomenų modelyje lentelių ryšiai gali būti "vienas su vienu" (kiekvienas keleivis turi vieną įlaipinimo kortelę) arba "vienas su daugeliu" (kiekvienas skrydis turi daug keleivių), bet ne "daugelis su daugeliu". Ryšiai "daugelis su daugeliu" lemia ciklinės priklausomybės klaidas, pvz., "Aptikta ciklinė priklausomybė". Ši klaida įvyks, jei tiesiogiai jungsitės tarp dviejų lentelių, kurios yra "daugelis su daugeliu" arba netiesioginių ryšių (lentelės ryšių grandinė, kuri yra vieno su daugeliu kiekviename ryšyje, bet daugelis su daugeliu, kai peržiūrima iki galo. Skaitykite daugiau apie duomenų modelio lentelių ryšius.
-
Duomenų tipai dviejuose stulpeliuose turi būti suderinami. Išsamios informacijos žr. "Excel" duomenų modelių duomenų tipai .
-
Kiti ryšių kūrimo būdai gali būti intuityvesni, ypač jei nesate tikri, kuriuos stulpelius naudoti. Žr . Ryšio kūrimas "Power Pivot" diagramos rodinyje.
Pavyzdys: laiko informacijos susiejimas su oro linijų skrydžio duomenimis
Galite sužinoti ir apie lentelės ryšius, ir apie laiko informaciją gavę nemokamos informacijos iš „Microsoft Azure“ parduotuvės. Kai kurie nemokami duomenų rinkiniai yra labai dideli, jiems reikia spartaus interneto ryšio, kad būtų galima užbaigti duomenų atsisiuntimą per tinkamą laiko tarpą.
-
Paleiskite „Power Pivot in Microsoft Excel“ papildinį ir atidarykite „Power Pivot“ langą.
-
Spustelėkite Gauti išorinių duomenų > Iš duomenų tarnybos > Iš „Microsoft Azure“ parduotuvės. „Microsoft Azure“ parduotuvės pagrindinis puslapis atidaromas naudojant lentelės importavimo vediklį.
-
Dalyje Price (kaina) spustelėkite Free (nemokamai).
-
Dalyje Category (kategorija) spustelėkite Science & Statistics (mokslas ir statistika).
-
Raskite DateStream spustelėkite Prenumeruoti.
-
Įveskite „Microsoft“ paskyrą ir spustelėkite Sign-in (Prisijungti). Lange turi būti rodoma duomenų peržiūra.
-
Slinkite į apačią ir spustelėkite Select query (pasirinkti užklausą).
-
Spustelėkite Next (pirmyn).
-
Pasirinkite BasicCalendarUS, tada spustelėkite Finish (baigti) ir importuokite duomenis. Naudojant spartų interneto ryšį, importavimas turėtų trukti apie minutę. Baigę turėtumėte matyti 73 414 perkeltų eilučių būsenos ataskaitą. Spustelėkite Close (uždaryti).
-
Norėdami importuoti antrą duomenų rinkinį, spustelėkite Gauti išorinių duomenų > Iš duomenų tarnybos > Iš „Microsoft Azure“ parduotuvės.
-
Dalyje Type (tipas) spustelėkite Data (duomenys).
-
Dalyje Price (kaina) spustelėkite Free (nemokamai).
-
Ieškokite US Air Carrier Flight Delays (JAV oro linijų skrydžių atidėjimai) ir spustelėkite Select (pasirinkti).
-
Slinkite į apačią ir spustelėkite Select query (pasirinkti užklausą).
-
Spustelėkite Next (pirmyn).
-
Spustelėkite Finish (baigti), kad būtų importuojami duomenys. Naudojant spartų interneto ryšį, tai gali trukti 15 minučių. Baigę turėtumėte matyti 2 427 284 perkeltų eilučių būsenos ataskaitą. Spustelėkite Close (uždaryti). Dabar duomenų modelyje turėtų būti dvi lentelės. Norint jas susieti, kiekvienoje lentelėje turi būti suderinamų stulpelių.
-
Atminkite, kad lauko DateKey, esančio BasicCalendarUS, formatas yra 1/1/2012 12:00:00 AM. Lentelėje On_Time_Performance taip pat yra datos/laiko stulpelis, FlightDate, kurių reikšmės nurodomos naudojant tą patį formatą: 1/1/2012 12:00:00 AM. Tuose dvejuose stulpeliuose yra atitinkamų to paties tipo duomenų ir bent viename iš stulpelių (DateKey) yra tik unikalios reikšmės. Atlikdami kitus kelis veiksmus, naudosite šiuos stulpelius, kad susietumėte šiuos stulpelius su lentelėmis.
-
Lange „Power Pivot“ spustelėkite „PivotTable“ ir naujame arba esamame darbalapyje sukurkite „PivotTable“.
-
Laukų sąraše išplėskite On_Time_Performance ir spustelėkite ArrDelayMinutes, kad galėtumėte įtraukti jį į sritį Reikšmės. „PivotTable“ turėtumėte matyti bendrą visų atidėtų skrydžių laiką minutėmis.
-
Išplėskite BasicCalendarUS ir spustelėkite MonthInCalendar, kad įtrauktumėte jį į sritį Eilutės.
-
Atminkite, kad „PivotTable“ dabar pateikia mėnesius, tačiau ties kiekvienu mėnesiu nurodo tą patį bendrą minučių skaičių. Pasikartojančios vienodos eilutės rodo, kad būtinas susiejimas.
-
Dalyje Laukų sąrašas, esančioje srityje „Gali reikėti lentelių ryšių“, spustelėkite Kurti.
-
Dalyje Susijusi lentelė pasirinkite On_Time_Performance, o lauke Susijęs stulpelis (pagrindinis) pasirinkite FlightDate.
-
Dalyje Lentelė pasirinkite BasicCalendarUS, dalyje Stulpelis (išorinis) pasirinkite DateKey. Norėdami sukurti ryšį, spustelėkite Gerai.
-
Atminkite, kad dabar atidėtų skrydžių bendra minučių suma ties kiekvienu mėnesiu skiriasi.
-
Dalyje BasicCalendarUS vilkite YearKey į sritį eilutės, virš MonthInCalendar.
Nuo šiol galite dalinti atvykimo skrydžių vėlavimo laiką pagal metus ir mėnesį arba kitas kalendoriaus reikšmes.
Patarimai: Pagal numatytuosius nustatymus mėnesiai išvardijami pagal abėcėlę. Naudodami „Power Pivot“ papildinį, galite keisti rikiavimą taip, kad mėnesiai būtų rodomi chronologine tvarka.
-
Įsitikinkite, kad lange „Power Pivot“ atidaryta lentelė BasicCalendarUS.
-
Lentelėje Pagrindinis spustelėkite Rikiuoti pagal stulpelį.
-
Lauke Rikiuoti pasirinkite MonthInCalendar
-
Lauke Pagal pasirinkite MonthOfYear.
Nuo šiol „PivotTable“ rūšiuos kiekvieną mėnesio-metų derinį (2011 spalis, 2011 lapkritis) pagal mėnesio numerį metuose (10, 11). Rūšiavimo tvarką pakeisti lengva, nes DateStream duomenų santrauka pateikia visus stulpelių, kurių reikia, kad šis scenarijus veiktų. Jei naudojate kitą laiko informacijos lentelę, turėsite atlikti kitokį veiksmą.
Gali reikėti ryšio tarp lentelių
Įtraukus laukus į „PivotTable“, jums bus pranešta, ar būtinas lentelės ryšys tam, kad laukai, kuriuos pasirinkote „PivotTable“, būtų panaudoti prasmingai.
„Excel“ gali pranešti, kada reikia sukurti ryšį, tačiau programa negali pranešti, kurias lenteles ir stulpelius naudoti, ir ar apskritai galima sukurti lentelės ryšį. Bandykite atlikti šiuos veiksnys, kad gautumėte reikiamų atsakymų.
1 veiksmas: nustatykite, kurias lenteles nurodyti ryšiui kurti
Jei jūsų modelyje yra tik kelios lentelės, gali netrukus paaiškėti, kurias iš jų reikia naudoti. Tačiau naudojant didesnius modelius, jums gali praversti pagalba. Vienas iš būdų yra naudoti „Power Pivot“ papildinio diagramos rodinį. Diagramos rodinys pateikia vaizdą, kaip atrodo visos duomenų modelio lentelės. Naudodami duomenų rodinį, galite greitai nustatyti, kurios lentelės yra atskirtos nuo likusios modelio dalies.
Pastaba: Galima sukurti painių ryšių, kurių negalima naudoti „PivotTable“ arba „Power View“ ataskaitoje. Tarkime, kad jūsų lentelės yra kažkaip susietos su kitomis modelio lentelėmis, tačiau pabandžius sujungti skirtingų lentelių laukus, gaunate pranešimą: „Gali teksi susieti lenteles“. Dažniausia priežastis – jūsų naudotas ryšys „daugelis su daugeliu“. Jei seksite lentelių ryšių grandinę, kuri jungia norimas naudoti lenteles, tikriausiai atrasite, kad turite du ar daugiau lentelės ryšių „vienas su daugeliu. Nėra lengvo sprendimo būdo, kuris veiktų kiekvienoje situacijoje, pabandykite kurti apskaičiuotus stulpelius, kad sujungtumėte stulpelius, kuriuos norite naudoti vienoje lentelėje.
2 veiksmas: suraskite stulpelius, kurie gali būti naudojami kuriant kelią iš vienos lentelės į kitą
Kai nustatysite, kuri lentelė yra atjungta nuo likusios modelio dalies, peržiūrėkite jos stulpelius, kad nustatytumėte, ar kitame stulpelyje o gal kokioje kitoje modelio vietoje yra sutampančių reikšmių.
Pavyzdžiui, turite modelį, kuriame yra gaminių pardavimo duomenys pagal teritoriją, o jūs vėliau importuosite demografinius duomenis, kad sužinotumėte, ar kiekvienoje teritorijoje yra koreliacija tarp pardavimo duomenų ir demografinių tendencijų. Demografiniai duomenys gaunami iš skirtingo duomenų šaltinio, todėl jo lentelės yra izoliuotos nuo likusios modelio dalies. Norėdami integruoti demografinius duomenis su likusias modelio dalimi, turėsite rasti stulpelį vienoje iš demografinių duomenų lentelių, kurio sutampa su jūsų naudojama. Pavyzdžiui, jei demografiniai duomenys tvarkomi pagal regioną, o jūsų pardavimo duomenyse nurodomas pardavimo regionas, galite susieti su duomenų rinkinius, kad rastumėte bendrą stulpelį, pvz., valstija, Pašto indeksas arba Regionas, ir leisti peržvalgą.
Be sutampančių reikšmių, yra dar keli papildomi ryšio kūrimo reikalavimai:
-
Duomenų reikšmės peržvalgos stulpelyje turi būti unikalios. Kitaip tariant, stulpelyje negali būti dublikatų. Duomenų modelyje nuliai ir tuščios eilutės yra atitikmenys tuščių reikšmių, kurios yra konkrečios duomenų reikšmės. Tai reiškia, kad peržvalgos stulpelyje negali būti kelių nulių.
-
Šaltinio stulpelio ir peržvalgos stulpelio duomenų tipai turi būti suderinami. Daugiau informacijos apie duomenų tipus žr. Duomenų modelių duomenų tipai.
Norėdami daugiau sužinoti apie lentelių ryšius, žr. Lentelių ryšiai duomenų modelyje.
