Perhubungan antara jadual dalam Model Data

Pelayar anda tidak menyokong video. Pasang Microsoft Silverlight, Adobe Flash Player atau Internet Explorer 9.

Jadikan analisis data anda lebih berkesan dengan mencipta perhubungan antara data dalam jadual yang berbeza. Perhubungan ialah sambungan antara dua jadual data, berdasarkan satu lajur dalam setiap satu. Untuk melihat sebab perhubungan adalah berguna, bayangkan bahawa anda menjejaki data untuk pesanan pelanggan dalam perniagaan anda. Anda boleh menjejak semua data dalam jadual tunggal yang mempunyai struktur seperti berikut:

ID Pelanggan

Nama

E-mel

Kadar Diskaun

ID Pesanan

Tarikh Pesanan

Produk

Kuantiti

1

Ashton

chris.ashton@contoso.com

.05

256

2010-01-07

Digital Kompak

11

1

Ashton

chris.ashton@contoso.com

.05

255

2010-01-03

Kamera SLR

15

2

Jaworski

michal.jaworski@contoso.com

.10

254

2010-01-03

Penghasil Wayang Bajet

27

Pendekatan ini boleh berfungsi tetapi ia melibatkan penyimpanan data lewah yang banyak, seperti alamat e-mel pelanggan untuk setiap pesanan. Storan adalah murah, tetapi jika alamat e-mel berubah anda perlu memastikan anda mengemas kini setiap baris untuk pelanggan tersebut. Salah satu penyelesaian bagi masalah ini adalah dengan memisahkan data kepada berbilang jadual dan mentakrifkan perhubungan antara jadual tersebut. Ini pendekatan yang digunakan dalam pangkalan data hubungan seperti SQL Server. Contohnya, pangkalan data yang anda import mungkin mewakili data pesanan menggunakan tiga jadual berkaitan:

Pelanggan

[ID Pelanggan]

Nama

E-mel

1

Ashton

chris.ashton@contoso.com

2

Jaworski

michal.jaworski@contoso.com

Diskaun Pelanggan

[ID Pelanggan]

Kadar Diskaun

1

.05

2

.10

Pesanan

[ID Pelanggan]

ID Pesanan

Tarikh Pesanan

Produk

Kuantiti

1

256

2010-01-07

Digital Kompak

11

1

255

2010-01-03

Kamera SLR

15

2

254

2010-01-03

Penghasil Wayang Bajet

27

Perhubungan wujud dalam Model Data yang anda cipta secara nyata atau yang dicipta Excel bagi pihak anda apabila anda mengimport berbilang jadual secara serentak. Anda juga boleh menggunakan tambahan Power Pivot untuk mencipta atau menguruskan model. Lihat Mencipta Model Data dalam Excel untuk butiran.

Jika anda menggunakan tambahan Power Pivot untuk mengimport jadual ini daripada beberapa pangkalan data yang sama, Power Pivot boleh mengesan perhubungan antara jadual berdasarkan pada lajur yang berada dalam [tanda kurung] dan boleh menghasilkan perhubungan ini dalam Model Data yang dibinanya di sebalik tabir. Untuk maklumat lanjut, lihat Pengesanan Automatik dan Inferens Perhubungan dalam artikel ini. Jika anda mengimport jadual daripada berbilang sumber, anda boleh mencipta perhubungan secara manual seperti yang diperihalkan dalam Mencipta hubungan antara dua jadual.

Halaman Atas

Dalam artikel ini

Lajur dan kunci

Jenis perhubungan

Perhubungan dan prestasi

Berbilang perhubungan antara jadual

Keperluan untuk perhubungan jadual

Tidak disokong dalam perhubungan jadual

Kunci Komposit dan Lajur Carian

Perhubungan Banyak ke Banyak

Cantum Sendiri dan Gelung

Pengesanan automatik dan inferens perhubungan dalam Power Pivot

Pengesanan Automatik untuk Set Bernama

Inferens Perhubungan

Lajur dan kunci

Perhubungan berdasarkan lajur dalam setiap jadual yang mengandungi data yang sama. Contohnya, jadual Pelanggan dan Pesanan boleh dikaitkan antara satu sama lain kerana kedua-duanya mengandungi lajur yang menyimpan ID pelanggan. Dalam contoh, lajur nama adalah sama, tetapi ini bukanlah satu keperluan. Salah satu boleh merupakan ID Pelanggan dan satu lagi Nombor Pelanggan, selagi semua baris dalam jadual Pesanan mengandungi ID yang juga disimpan dalam jadual Pelanggan.

Dalam pangkalan data hubungan, terdapat beberapa jenis kunci yang biasanya hanyalah lajur dengan ciri khas. Memahami tujuan setiap kunci boleh membantu anda menguruskan berbilang jadual Model Data yang menyediakan data untuk Jadual Pangsi, Carta Pangsi atau laporan Power View.

Jenis kunci berikut paling memadan untuk tujuan kita:

  • Kunci primer: mengenal pasti baris dalam jadual secara unik, seperti ID Pelanggan dalam jadual Pelanggan.

  • Kunci alternatif (atau kunci calon): lajur yang selain daripada kunci primer unik. Contohnya, jadual Pekerja mungkin menyimpan ID pekerja dan nombor keselamatan sosial, kedua-duanya adalah unik.

  • Kunci asing: lajur yang merujuk kepada lajur unik dalam jadual lain, seperti ID Pelanggan dalam jadual Pesanan, yang merujuk kepada ID Pelanggan dalam jadual Pelanggan.

Dalam Model Data, kunci primer atau kunci alternatif dirujuk sebagai lajur berkaitan. Jika jadual mempunyai kedua-dua kunci primer dan alternatif, anda boleh menggunakan salah satu sebagai asas perhubungan jadual. Kunci asing dirujuk sebagai lajur sumber atau hanya lajur. Dalam contoh kita, perhubungan akan ditakrifkan antara ID Pelanggan dalam jadual Pesanan (lajur) dan ID Pelanggan dalam jadual Pelanggan (lajur carian). Jika anda mengimport data daripada pangkalan data hubungan, secara lalai Excel memilih kunci asing daripada satu jadual dan kunci primer yang sepadan daripada jadual yang lain. Walau bagaimanapun, anda boleh menggunakan sebarang lajur yang mempunyai nilai unik untuk lajur carian.

Jenis perhubungan

Perhubungan antara Pelanggan dan Pesanan ialah perhubungan satu ke banyak. Setiap pelanggan boleh mempunyai berbilang pesanan, tetapi pesanan tidak boleh mempunyai berbilang pelanggan. Jenis perhubungan yang lain adalah satu ke satu dan banyak ke banyak. Jadual Diskaun Pelanggan yang mentakrifkan kadar diskaun tunggal untuk setiap pelanggan adalah perhubungan satu ke satu dengan jadual Pelanggan.

Jadual berikut menunjukkan perhubungan antara tiga jadual:

Perhubungan

Jenis

Lajur Carian

Lajur

Pelanggan-Diskaun Pelanggan

satu ke satu

Pelanggan.ID Pelanggan

Diskaun Pelanggan.ID Pelanggan

Pelanggan-Pesanan

satu ke banyak

Pelanggan.ID Pelanggan

Pesanan.ID Pelanggan

Nota:  Perhubungan banyak ke banyak tidak disokong dalam Model Data. Contoh perhubungan banyak ke banyak ialah perhubungan langsung antara Produk dan Pelanggan, yang mana pelanggan boleh membeli banyak produk dan produk yang sama boleh dibeli oleh ramai pelanggan.

Perhubungan dan prestasi

Selepas apa jua perhubungan telah dicipta, Excel biasanya mesti mengira semula sebarang formula yang menggunakan lajur daripada jadual dalam perhubungan yang baru dicipta. Mungkin memerlukan sedikit masa untuk memproses, bergantung pada amaun data dan kerumitan perhubungan. Lihat Mengira Semula Formula untuk butiran.

Berbilang perhubungan antara jadual

Model Data boleh mempunyai berbilang perhubungan antara dua jadual. Untuk mencipta pengiraan tepat, Excel memerlukan laluan tunggal daripada satu jadual kepada jadual berikutnya. Oleh itu, hanya satu perhubungan sahaja yang aktif antara setiap pasangan jadual pada satu masa. Perhubungan yang lain tidak aktif tetapi anda boleh menentukan perhubungan tidak aktif dalam formula dan pertanyaan. Dalam Pandangan Gambar Rajah, perhubungan aktif ialah garis padu dan yang tidak aktif ialah garis bersempang. Contohnya, dalam AdventureWorksDW2012, jadual DimDate mengandungi lajur, DateKey, yang berkait dengan tiga lajur yang berbeza dalam jadual FactInternetSales: OrderDate, DueDate dan ShipDate. Jika perhubungan aktif adalah antara DateKey dan OrderDate, iaitu perhubungan lalai dalam formula, melainkan anda menyatakan sebaliknya.

Halaman Atas

Keperluan untuk perhubungan jadual

Perhubungan boleh dicipta apabila keperluan berikut dicapai:

Kriteria

Perihalan

Pengecam Unik untuk Setiap Jadual

Setiap jadual mesti mempunyai lajur tunggal yang mengenal pasti setiap baris dalam jadual secara unik. Lajur ini sering kali dirujuk sebagai kunci primer.

Lajur Carian Unik

Nilai data dalam lajur carian mesti unik. Dengan kata lain, lajur tidak boleh mengandungi pendua. Dalam Model Data, nol dan rentetan kosong sepadan dengan kosong yang merupakan nilai data yang ketara. Ini bermaksud anda tidak boleh mempunyai berbilang nol dalam lajur carian.

Jenis Data Serasi

Jenis data dalam lajur sumber dan lajur carian mesti sepadan. Untuk maklumat lanjut tentang jenis data, lihat Jenis data yang disokong dalamModel Data.

Tidak disokong dalam perhubungan jadual

Dalam Model Data, anda tidak boleh mencipta perhubungan jadual jika kunci ialah kunci komposit. Anda juga disekat daripada mencipta perhubungan satu ke satu dan satu ke banyak. Jenis perhubungan lain tidak disokong.

Kunci Komposit dan Lajur Carian

Kunci komposit terdiri daripada lebih daripada satu lajur. Model Data tidak boleh menggunakan kunci komposit; jadual mesti sentiasa mempunyai satu lajur yang mengecam setiap baris dalam jadual secara unik. Jika anda mengimport jadual yang mempunyai perhubungan sedia ada berdasarkan kunci komposit, Bestari Import Jadual dalam Power Pivot akan mengabaikan perhubungan tersebut kerana tidak dapat dicipta dalam model.

Untuk mencipta perhubungan antara dua jadual yang mempunyai berbilang lajur yang mentakrifkan kunci utama dan asing, gabungkan dahulu nilai untuk mencipta lajur kunci tunggal sebelum mencipta perhubungan. Anda boleh melakukan ini sebelum anda mengimport data, atau dengan mencipta lajur terhitung dalam Model Data menggunakan tambahan Power Pivot.

Perhubungan Banyak ke Banyak

Model Data tidak boleh mempunyai perhubungan banyak ke banyak. Anda tidak boleh sewenang-wenangnya menambah jadual simpang dalam model. Walau bagaimanapun, anda boleh menggunakan fungsi DAX untuk memodelkan perhubungan banyak ke banyak.

Cantum Sendiri dan Gelung

Cantum sendiri tidak dibenarkan dalam Model Data. Cantum sendiri ialah perhubungan rekursif antara jadual dengan dirinya sendiri. Cantum sendiri sering kali digunakan untuk mentakrifkan hierarki induk-anak. Contohnya, anda boleh mencantumkan jadual  Pekerja kepada dirinya sendiri untuk menghasilkan hierarki yang menunjukkan rantaian pengurusan pada perniagaan.

Excel tidak membenarkan gelung dicipta antara perhubungan dalam  buku kerja. Dengan kata lain, set perhubungan berikut adalah dilarang.

  • Jadual 1, lajur a ke Jadual 2, lajur f    

  • Jadual 2, lajur f ke Jadual 3, lajur n    

  • Jadual 3, lajur n ke Jadual 1, lajur a    

Jika anda cuba mencipta perhubungan yang akan menyebabkan gelung dicipta, ralat akan dijana.

Halaman Atas

Pengesanan automatik dan inferens perhubungan dalam Power Pivot

Salah satu kelebihan mengimport data menggunakan tambahan Power Pivot ialah Power Pivot kadangkala boleh mengesan perhubungan dan mencipta perhubungan baru dalam Model Data yang dicipta dalam Excel.

Apabila anda mengimport berbilang jadual, Power Pivot mengesan sebarang perhubungan sedia ada antara jadual secara automatik. Begitu juga, apabila anda mencipta Jadual Pangsi, Power Pivot menganalisis data dalam jadual. Ia mengesan perhubungan mungkin yang tidak ditakrifkan dan mencadangkan lajur yang bersesuaian untuk disertakan dalam perhubungan tersebut.

Algoritma pengesanan menggunakan data statistik mengenai nilai dan metadata lajur untuk membuat inferens mengenai kebarangkalian perhubungan.

  • Jenis data dalam semua lajur berkaitan perlulah serasi. Untuk pengesanan automatik, nombor bulat dan jenis data teks sahaja yang disokong. Untuk maklumat lanjut tentang jenis data, lihat Jenis data yang disokong dalamModel Data.

  • Demi memastikan perhubungan berjaya dikesan, bilangan kunci unik dalam lajur carian mesti lebih besar daripada nilai dalam jadual pada bahagian banyak. Dengan kata lain, lajur utama pada bahagian banyak perhubungan tidak boleh mengandungi sebarang nilai yang tidak berada dalam lajur utama jadual carian. Contohnya, katakan anda mempunyai jadual yang menyenaraikan produk dengan ID (jadual carian) dan jadual jualan yang menyenaraikan jualan bagi setiap produk (bahagian perhubungan banyak). Jika rekod jualan anda mengandungi ID produk yang tidak mempunyai ID yang sepadan dalam jadual Produk, perhubungan tidak boleh dicipta secara automatik, tetapi anda boleh menciptanya secara manual. Untuk membuatkan Excel mengesan perhubungan, terlebih dahulu anda perlu mengemas kini jadual carian Produk dengan ID bagi produk yang hilang.

  • Pastikan nama lajur utama pada bahagian banyak adalah sama dengan nama lajur utama dalam jadual carian. Nama tidak perlu sama. Contohnya, dalam penyediaan perniagaan, anda sering kali mempunyai variasi pada nama lajur yang pada asasnya mengandungi data yang sama: Emp ID, EmployeeID, Employee ID, EMP_ID dan sebagainya. Algoritma mengesan nama yang serupa dan menguntukkan kebarangkalian yang lebih tinggi kepada lajur yang mempunyai nama yang sama atau sepadan. Oleh itu, demi meningkatkan kebarangkalian untuk mencipta perhubungan, anda boleh cuba menamakan semula lajur dalam data yang anda import untuk sesuatu yang serupa dengan lajur dalam jadual anda yang sedia ada. Jika Excel menemui berbilang perhubungan yang mungkin, maka ia tidak mencipta perhubungan.

Maklumat ini mungkin membantu anda memahami sebab bukan semua perhubungan dapat dikesan, atau cara perubahan dalam metadata, seperti nama medan dan jenis data, boleh meningkatkan hasil pengesanan perhubungan automatik. Untuk maklumat lanjut, lihat Penyelesaian Masalah Perhubungan.

Pengesanan Automatik untuk Set Bernama

Perhubungan tidak dikesan secara automatik antara Set Bernama dan medan berkaitan dalam Jadual Pangsi. Anda boleh mencipta perhubungan ini secara manual. Jika anda ingin menggunakan pengesanan perhubungan automatik, alih keluar setiap Set Bernama dan tambahkan medan individu dari Set Bernama terus ke Jadual Pangsi.

Inferens Perhubungan

Dalam sesetengah kes, perhubungan antara jadual dikaitkan secara automatik. Contohnya, jika anda mencipta perhubungan antara dua set pertama jadual di bawah, perhubungan disimpul untuk diwujudkan antara dua jadual lain dan perhubungan diwujudkan secara automatik.

  • Produk dan Kategori, dicipta secara manual

  • Kategori dan SubKategori, dicipta secara manual

  • Produk dan SubKategori, perhubungan tersimpul

Untuk memastikan perhubungan dikaitkan secara automatik, perhubungan mesti berada dalam satu arah, seperti yang ditunjukkan di atas. Jika perhubungan awal adalah antara, contohnya, Jualan dan Produk, serta Jualan dan Pelanggan, perhubungan tidak disimpulkan. Hal ini demikian kerana perhubungan antara Produk dan Pelanggan ialah perhubungan banyak ke banyak.

Halaman Atas

Kembangkan kemahiran anda
Jelajahi latihan
Dapatkan ciri baru terlebih dahulu
Sertai Office Insiders

Adakah maklumat ini membantu?

Terima kasih atas maklum balas anda!

Terima kasih atas maklum balas anda! Nampaknya ia mungkin akan membantu untuk menyambungkan anda kepada salah seorang daripada ejen sokongan Office kami.

×