Ar bandėte perkelti vienos lentelės stulpelį į kitą lentelę naudodami VLOOKUP? Programoje "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. Galite sukurti ryšį tarp dviejų duomenų lentelių pagal lentelių duomenų atitikimą. Tada galite kurti "PivotTable" ir kitas 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" laukų sąraše.
Ryšiai dažniausiai naudojami kuriant "PivotTable" iš kelių duomenų modelio lentelių. Tai leidžia analizuoti susijusius duomenis jų nejungiant į vieną lentelę.
Pastaba
Jei darbaknygėje yra duomenų modelis, galite valdyti lentelių ryšius 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. 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ą: Lentelės įrankiuose spustelėkite Dizaino>lentelės pavadinimas> Į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., 2026-01-01) ir bent vienoje lentelėje (laiko informacijos) esančiame stulpelyje datos reikšmės turi nesikartoti. - Pasirinkite Duomenų>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.
- Pažymė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.
- "Excel" duomenų modelyje ryšiai paprastai yra "vienas su vienu" arba "vienas su daugeliu". Jei ryšys yra "daugelis su daugeliu", reikalingas papildomas modeliavimas (pvz., naudojant peržvalgos lentelę). Naudojant ryšį "daugelis su daugeliu" gali kilti ciklinės priklausomybės klaidų, pvz., pranešimas "Aptikta 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 apie lentelių ryšius duomenų modelyje.
Skirtingai nei peržvalgos formulės, ryšiai nedubliuoja duomenų. Jie susieja lenteles, kad kiekvienos lentelės laukus būtų galima naudoti kartu "PivotTable".
Duomenų tipai šiuose dviejuose stulpeliuose turi būti suderinami. Išsamesnės informacijos ieškokite "Excel" duomenų modelių duomenų tipų dalyje.
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.
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". 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 rasite duomenų modelių duomenų tipai.
Norėdami daugiau sužinoti apie lentelių ryšius, žr. Lentelių ryšiai duomenų modelyje.