Ustvarjanje relacij med tabelami v Excelu

Velja za
Excel za Microsoft 365 Excel 2024 Excel 2021

Ali ste že kdaj uporabili funkcijo VLOOKUP za prenašanje stolpca iz ene tabele v drugo tabelo? Excel vključuje tudi vgrajen podatkovni model, ki omogoča ustvarjanje relacij med tabelami, kar je lahko alternativa uporabi funkcij iskanja, kot je VLOOKUP. Med dvema tabelama s podatki lahko ustvarite relacijo, ki temelji na ujemajočih se podatkih v posamezni tabeli. Nato lahko ustvarite vrtilne tabele in druga poročila s polji iz vsake tabele, tudi če so tabele iz različnih virov. Če imate na primer podatke o prodaji, morda želite uvoziti in povezati podatke o času, da lahko analizirate vzorce prodaje po letih in mesecih.

Vse tabele v delovnem zvezku so navedene na seznamu Polja vrtilne tabele.

Relacije se najpogosteje uporabljajo pri ustvarjanju vrtilnih tabel iz več tabel v podatkovnem modelu. To vam omogoča analizo povezanih podatkov, ne da bi jih združili v eno tabelo.

Opomba

Če delovni zvezek vključuje podatkovni model, lahko relacije tabele upravljate na zavihku Podatki.

Ko uvozite sorodne tabele iz relacijske zbirke podatkov, lahko Excel pogosto ustvari te relacije v podatkovnem modelu, ki ga gradi v ozadju. V vseh drugih primerih boste morali relacije ustvariti ročno.

  1. Prepričajte se, da delovni zvezek vsebuje najmanj dve tabeli in da ima vsaka od njih stolpec, ki ga je mogoče preslikati v stolpec druge tabele.
  2. Naredite nekaj od tega: Oblikujte podatke kot tabelo ali uvozite zunanje podatke kot tabelo na nov delovni list.
  3. Vsaki tabeli dodelite smiselno ime: v Orodjih za tabele klikniteIme>tabele načrta> vnesite ime.
  4. Preverite, ali ima stolpec v eni od tabel enolične podatkovne vrednosti brez podvojitev. Excel lahko ustvari relacijo samo, če en stolpec vsebuje enolične vrednosti.
    Če želite na primer povezati prodajo strank s časovnim obveščanjem, morata obe tabeli vključevati datume v isti obliki zapisa (na primer 1. 1. 2026) in vsaj ena tabela (časovno obveščanje) navaja vsak datum samo enkrat v stolpcu.
  5. Izberite Podatkovne>relacije.

Če je možnost Relacije obarvana sivo, vsebuje delovni zvezek le eno tabelo.

  1. V polju Upravljanje relacij izberite Novo.
  2. V polju Ustvari relacijo kliknite puščico dol za možnost Tabela in izberite tabelo na seznamu. V relaciji »ena proti mnogo« bi morala biti ta tabela na strani »mnogo«. Pri primeru »stranka - podatki o času« bi najprej izbrali tabelo prodaje strankam, ker pride do številnih prodaj ne glede na dan.
  3. Za Stolpec (tuji) izberite stolpec, ki vsebuje podatke, ki so povezani s povezanim stolpcem (primarni). Če bi na primer imeli stolpec z datumom v obeh tabelah, bi ga zdaj izbrali.
  4. Za možnost Povezana tabela izberite tabelo, ki ima vsaj en stolpec podatkov, povezan s tabelo, ki ste jo pravkar izbrali za Tabela.
  5. Za Povezana tabela (Primarna) izberite stolpec z enoličnimi vrednostmi, ki ustrezajo vrednostim v stolpcu, ki ste ga izbrali za Stolpec.
  6. Izberite V redu.

Več o relacijah med tabelami v Excelu

Opombe o relacijah

  • Ali relacija obstaja, boste vedeli, ko povlečete polja iz različnih tabel na seznam Polja vrtilne tabele. Če niste pozvani k ustvarjanju relacije, Excel že vsebuje informacije o relaciji, ki jih potrebuje za povezavo podatkov.

  • Ustvarjanje relacij je podobno uporabi funkcij VLOOKUP: potrebujete stolpce, ki vsebujejo ujemajoče se podatke, tako da lahko Excel navzkrižno preverja vrstice ene tabele z vrsticami druge. V primeru »podatki o času« bi bilo treba tabelo »Stranke« dopolniti z datumskimi vrednostmi, ki prav tako obstajajo v tabeli s podatki o času.

    • V Excelovem podatkovnem modelu so relacije običajno ena proti ena ali ena proti več. Relacije "več proti mnogim" zahtevajo dodatno modeliranje (na primer z uporabo iskalne tabele). Relacije "več proti mnogim" povzročijo napake krožne odvisnosti, na primer »Zaznana je bila krožna odvisnost«. Do te napake pride, če vzpostavite neposredno povezavo med dvema tabelama, ki sta več proti mnogim, ali posredne povezave (veriga relacij tabel, ki so ena proti več v vsaki relaciji, vendar več proti mnogim, če si jih ogledate od konca do konca). Več preberite v članku Odnosi med tabelami v podatkovnem modelu.
  • Za razliko od formul za iskanje relacije ne podvajajo podatkov. Namesto tega povežejo tabele, tako da lahko polja iz vsake tabele uporabite skupaj v vrtilni tabeli.

  • Podatkovni tipi v dveh stolpcih morajo biti združljivi. Za podrobnosti glejte Podatkovni tipi v Excelovih podatkovnih modelih.

  • Drugi načini ustvarjanja relacij so lahko bolj intuitivni, zlasti če ne veste, katere stolpce bi uporabili. Glejte Ustvarjanje odnosa v pogledu diagrama v orodju Power Pivot.

»Morda bodo potrebne relacije med tabelami«

Ko dodajate polja v vrtilno tabelo, boste obveščeni, ali je za razumevanje polj, ki ste jih izbrali v vrtilni tabeli, potrebna relacija tabele.

Ko je zahtevana relacija, se pojavi gumb »Ustvari«

Čeprav vam Excel lahko pove, kdaj je relacija potrebna, vam ne more povedati, katere tabele in stolpce uporabiti ali ali je relacija tabele sploh mogoča. Sledite tem korakom, če želite pridobiti odgovore, ki jih potrebujete.

1. korak: Določite, katere tabele bodo določene v relaciji

Če vsebuje vaš model le nekaj tabel, je lahko nemudoma jasno, katere je treba uporabiti. Za večje modele pa bi verjetno potrebovali pomoč. Eden od pristopov je, da uporabite pogled diagrama v Power Pivot dodatku. Pogled diagrama ponuja grafično ponazoritev vseh tabel v podatkovnem modelu. S pogledom diagrama lahko hitro določite, katere tabele so ločene od drugih v modelu.

Pogled diagrama, ki prikaže nepovezane tabele

Opomba

Ustvarite lahko dvoumne relacije, ki so neveljavne, ko jih uporabite v vrtilni tabeli. Recimo, da so vse vaše tabele na nek način povezane z drugimi tabelami v modelu, toda ko poskušate združiti polja iz različnih tabel, se prikaže sporočilo »Morda bodo potrebni odnosi med tabelami«. Najverjetnejši vzrok je, da ste naleteli na razmerje od mnogih do več. Če sledite verigi relacij tabel, ki so povezane s tabelami, ki jih želite uporabiti, boste verjetno ugotovili, da imate več relacij tabel »ena proti mnogo«. Ni preproste rešitve, ki bi delovala v vsaki situaciji, lahko pa poskusite ustvariti izračunane stolpce, da uskladite želene stolpce v eni tabeli.

2. korak: Poiščite stolpce, s katerimi lahko ustvarite pot od ene tabele do naslednje

Ko ugotovite, katera tabela je prekinjena s preostalim modelom, preglejte njene stolpce in ugotovite, ali drug stolpec drugje v modelu vsebuje ujemajoče se vrednosti.

Recimo, da imate model, ki vsebuje prodajo izdelkov po ozemljih, in da nato uvozite demografske podatke, da ugotovite, ali obstaja korelacija med prodajo in demografskimi trendi na posameznem območju. Ker demografski podatki prihajajo iz drugega vira podatkov, so njegove tabele na začetku izolirane od preostalega modela. Če želite demografske podatke integrirati s preostalim modelom, morate v eni od demografskih tabel poiskati stolpec, ki ustreza stolpcu, ki ga že uporabljate. Če so na primer demografski podatki organizirani po regijah in podatki o prodaji določajo, na kateri regiji je prišlo do prodaje, lahko povežete oba nabora podatkov tako, da poiščete skupni stolpec, na primer državo, poštno številko ali regijo, da zagotovite iskanje.

Poleg ujemajočih se vrednosti obstajajo nekatere dodatne zahteve za ustvarjanje relacije:

  • Podatkovne vrednosti v stolpcu za iskanje morajo biti enolične. Z drugimi besedami, stolpec ne sme vsebovati dvojnikov. V podatkovnem modelu so ničle in prazni nizi enaki prazni vrednosti, ki je razlikovalna podatkovna vrednost. To pomeni, da v stolpcu za iskanje ne morete imeti več ničel.
  • Podatkovni tipi v izvornem stolpcu in stolpcu za iskanje morajo biti združljivi. Če želite več informacij o podatkovnih tipih, preberite članek Podatkovni tipi v podatkovnih modelih.

Če želite izvedeti več o relacijah med tabelami, preberite članek Relacije med tabelami v podatkovnem modelu.

Na vrh strani