„Excel“ lentelių ryšio kūrimas

Ar bandėte perkelti vienos lentelės stulpelį į kitą lentelę naudodami VLOOKUP? Dabar, kai „Excel 2013“ į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 visų lentelių laukais, net jei lentelės 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.

“ ryšiai

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

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.

  1. Įsitikinkite, kad darbaknygėje yra bent dvi lentelės ir kiekvienoje lentelėje yra stulpelis, kurį galima susieti su kitos lentelės stulpeliu.

  2. Formatuokite duomenis kaip lentelę, arba

    Importuokite išorinius duomenis kaip lentelę į naują darbalapį.

  3. Suteikite kiekvienai lentelei prasmingą pavadinimą: Dalyje Lentelės įrankiai spustelėkite Dizainas > Lentelės pavadinimas ir įveskite pavadinimą.

  4. 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.

  5. Spustelėkite Duomenys > Ryšiai.

Jei parinktis Ryšiai yra papilkinta, darbaknygėje yra tik viena lentelė.

  1. Dialogo lange Valdyti ryšius spustelėkite Naujas.

  2. 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.

  3. 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į.

  4. Naudodami Susijusi lentelė pasirinkite lentelę, kurioje yra bet vienas duomenų, susijusių su lentele, pasirinkta parinktyje Lentelė, stulpelis.

  5. Naudodami Susijęs stulpelis (pagrindinis) pasirinkite stulpelį, kuriame yra unikalių reikšmių, atitinkančių stulpelio, pasirinkto parinktyje Stulpelis, reikšmes.

  6. Spustelėkite Gerai.

Daugiau apie ryšius tarp „Excel“ lentelių

Pastabos apie ryšius

Pavyzdys: laiko informacijos susiejimas su oro linijų skrydžio duomenimis

Gali reikėti ryšio tarp lentelių

1 veiksmas: nustatykite, kurias lenteles nurodyti ryšiui kurti

2 veiksmas: suraskite stulpelius, kurie gali būti naudojami kuriant kelią iš vienos lentelės į kitą

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 galimi „vienas su vienu“ (kiekvienas keleivis turi savo įsodinimo kortelę) arba „vienas su daugeliu“ (kiekviename skrydyje yra daug keleivių) duomenų lentelės ryšiai, tačiau negalimas „daugelis su daugeliu“ ryšys. Naudojant ryšį „daugelis su daugeliu“ gali kilti ciklinės priklausomybės klaidų, pvz., pranešimas „Nustatyta ciklinė priklausomybė.“ Ši klaida įvyks, jei tiesiogiai siesite dvi lenteles ryšiu „daugelis su daugeliu“ arba susiesite netiesiogiai (lentelių grandinės ryšiai, kurių kiekviename ryšyje yra jungtis „vienas su daugeliu“, tačiau žiūrint iš galutinės pozicijos tai yra jungtis „daugelis su daugeliu“.) Daugiau skaitykite Lentelių ryšiai duomenų modelyje.

  • Duomenų tipai dviejuose stulpeliuose turi būti suderinami. Išsamiau žr. „Excel“ duomenų modelių duomenų tipai.

  • Yra kitų būdų sukurti ryšius, kurie 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ą.

  1. Paleiskite „„Power Pivot“, esanti „Microsoft Excel 2013““ papildinį ir atidarykite „„Power Pivot““ langą.

  2. 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į.

  3. Dalyje Price (kaina) spustelėkite Free (nemokamai).

  4. Dalyje Category (kategorija) spustelėkite Science & Statistics (mokslas ir statistika).

  5. Ieškokite DateStream ir spustelėkite Subscribe (užsakyti). Daugiau informacijos apie tai ieškokite laiko informacijos santraukose.

  6. Įveskite „Microsoft“ abonementą ir spustelėkite Sign-in (Prisijungti). Lange turi būti rodoma duomenų peržiūra.

  7. Slinkite į apačią ir spustelėkite Select query (pasirinkti užklausą).

  8. Spustelėkite Next (pirmyn).

  9. 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).

  1. Norėdami importuoti antrą duomenų rinkinį, spustelėkite Gauti išorinių duomenų > Iš duomenų tarnybos > Iš „Microsoft Azure“ parduotuvės.

  2. Dalyje Type (tipas) spustelėkite Data (duomenys).

  3. Dalyje Price (kaina) spustelėkite Free (nemokamai).

  4. Ieškokite US Air Carrier Flight Delays (JAV oro linijų skrydžių atidėjimai) ir spustelėkite Select (pasirinkti).

  5. Slinkite į apačią ir spustelėkite Select query (pasirinkti užklausą).

  6. Spustelėkite Next (pirmyn).

  7. 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ų.

  8. 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.

  9. Lange „Power Pivot“ spustelėkite „PivotTable“ ir naujame arba esamame darbalapyje sukurkite „PivotTable“.

  10. 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.

  11. Išplėskite BasicCalendarUS ir spustelėkite MonthInCalendar, kad įtrauktumėte jį į sritį Eilutės.

  12. 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.

  13. Dalyje Laukų sąrašas, esančioje srityje „Gali reikėti lentelių ryšių“, spustelėkite Kurti.

  14. Dalyje Susijusi lentelė pasirinkite On_Time_Performance, o lauke Susijęs stulpelis (pagrindinis) pasirinkite FlightDate.

  15. Dalyje Lentelė pasirinkite BasicCalendarUS, dalyje Stulpelis (išorinis) pasirinkite DateKey. Norėdami sukurti ryšį, spustelėkite Gerai.

  16. Atminkite, kad dabar atidėtų skrydžių bendra minučių suma ties kiekvienu mėnesiu skiriasi.

  17. 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.

  1. Įsitikinkite, kad lange „Power Pivot“ atidaryta lentelė BasicCalendarUS.

  2. Lentelėje Pagrindinis spustelėkite Rikiuoti pagal stulpelį .

  3. Lauke Rikiuoti pasirinkite MonthInCalendar

  4. 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.

Prireikus ryšio rodomas mygtukas Kurti

„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.

Diagramos rodinyje rodomos atjungtos lentelės

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ų.

  • Duomenų tipai šaltinio ir peržvalgos stulpelyje 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.

Puslapio viršus

Taikoma: Excel 2013



Ar ši informacija buvo naudinga?

Taip Ne

Kaip galėtume tai pagerinti?

255 liko simbolių

Siekiant apsaugoti jūsų privatumą, neįtraukite kontaktinės informacijos į atsiliepimus. Peržiūrėkite mūsų privatumo strategija.

Dėkojame už jūsų atsiliepimus!

Palaikymo ištekliai

Kalbos pakeitimas