Ar bandėte perkelti vienos lentelės stulpelį į kitą lentelę naudodami VLOOKUP? "Excel" taip pat yra įtaisytasis duomenų modelis, kuris leidžia kurti ryšius tarp lentelių, o tai gali būti alternatyva peržvalgos funkcijoms, pvz., VLOOKUP, naudoti. Galite sukurti ryšį tarp dviejų duomenų lentelių pagal lentelių duomenų atitikimą. Tada galite kurti "PivotTable" ir kitas ataskaitas su kiekvienos lentelės laukais, 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" laukų sąraše.
Ryšiai dažniausiai naudojami kuriant "PivotTable" iš kelių duomenų modelio lentelių. Tai leidžia analizuoti susijusius duomenis nejungiant jų į vieną lentelę.
Pastaba: Jei jūsų darbaknygėje yra duomenų modelis, lentelių ryšius galite valdyti skirtuke Duomenys.
Importuojant susijusias lenteles iš sąryšinės duomenų bazės, "Excel" dažnai gali sukurti tuos ryšius fone kuriamame duomenų modelyje. Visais kitais atvejais ryšius turėsite sukurti rankiniu 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 klientų pardavimą su laiko informacija, abiejose lentelėse turi būti to paties formato datos (pvz., 2026-01-01), o bent vienoje lentelėje (laiko informacijos) pateikiama kiekviena data stulpelyje tik vieną kartą.
-
Pasirinkite Duomenys > ryšiai.
Jei parinktis Ryšiai yra papilkinta, darbaknygėje yra tik viena lentelė.
-
Lauke ryšių valdymas pasirinkite 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.
-
Pasirinkite Gerai.
Daugiau apie ryšius tarp „Excel“ lentelių
Pastabos apie ryšius
-
Vilkdami laukus iš skirtingų lentelių į "PivotTable" laukų sąrašą, žinosite, ar yra ryšys. Jei nesate raginami sukurti ryšį, "Excel" jau turi ryšio informaciją, kurios reikia duomenims susieti.
-
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.
-
"Excel" duomenų modelyje ryšiai paprastai yra "vienas su vienu" arba "vienas su daugeliu". Ryšiams "daugelis su daugeliu" reikia papildomo modeliavimo (pvz., naudojant peržvalgos lentelę). 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 iš pabaigos). Skaitykite daugiau apie duomenų modelio lentelių ryšius.
-
-
Kitaip nei peržvalgos formulėse, ryšiai nedubliuoja duomenų. Vietoj to jos susieja lenteles, kad kiekvienos lentelės laukus būtų galima naudoti kartu "PivotTable".
-
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.
"Gali prireikti ryšių tarp lentelių"
Kai įtraukiate laukus į "PivotTable", jums bus pranešta, ar reikia lentelių ryšio, kad būtų galima prasmingai suprasti "PivotTable" pasirinktus laukus.
Nors "Excel" gali pranešti, kada reikalingas ryšys, ji negali pasakyti, kurias lenteles ir stulpelius naudoti, ir ar galimas lentelės ryšys. 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 kurti nevienareikšmius ryšius, kurie yra neleistini, kai naudojami "PivotTable". Tarkime, kad visos lentelės yra tam tikru būdu susietos su kitomis modelio lentelėmis, bet kai bandote sujungti laukus iš skirtingų lentelių, gaunate pranešimą "Gali prireikti ryšių tarp lentelių". Labiausiai tikėtina priežastis yra tai, kad sujungėte ryšį "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ą
Nustatę, kuri lentelė atjungta nuo likusios modelio dalies, peržiūrėkite jos stulpelius, kad nustatytumėte, ar kitoje kitoje modelio vietoje esančiame stulpelyje 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 į likusią modelio dalį, vienoje iš demografinių duomenų lentelių turėsite rasti stulpelį, kuris atitinka jūsų jau naudojamą. 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.
