Mencipta perhubungan antara jadual dalam Excel

Pernahkah anda menggunakan VLOOKUP untuk memindahkan lajur dari satu jadual ke dalam jadual lain? Kini Excel 2013 mempunyai Model Data terbina dalam, VLOOKUP menjadi lapuk. Anda boleh mencipta perhubungan antara dua jadual data, berdasarkan data yang sepadan dalam setiap jadual. Kemudian anda boleh mencipta helaian Power View dan membina Jadual Pangsi dan laporan lain dengan medan daripada setiap jadual, walaupun jadual terdiri daripada sumber yang berbeza. Contohnya, jika anda mempunyai data jualan pelanggan, anda mungkin ingin mengimport dan mengaitkan data kecerdasan masa untuk menganalisis corak jualan mengikut tahun dan bulan.

Semua jadual dalam buku kerja disenaraikan dalam senarai Jadual Pangsi dan Medan Power View.

Apabila anda mengimport jadual berkaitan daripada pangkalan data perhubungan, Excel biasanya dapat mencipta perhubungan ini dalam Model Data yang dibinanya di sebalik tabir. Bagi semua kes lain, anda perlu mencipta perhubungan secara manual.

  1. Pastikan buku kerja mengandungi sekurang-kurangnya dua jadual dan setiap jadual mempunyai lajur yang boleh dipetakan pada lajur dalam jadual lain.
  2. Formatkan data sebagai jadual, atau

Import data luaran sebagai jadual dalam lembaran kerja baru.

  1. Berikan setiap jadual nama yang bermakna: Dalam Alat Jadual, klik Reka Bentuk > Nama Jadual > masukkan nama.
  2. Sahkan lajur dalam salah satu jadual yang mempunyai nilai data unik tanpa pendua. Excel hanya boleh mencipta perhubungan jika satu lajur mengandungi nilai unik.

Contohnya, untuk mengaitkan jualan pelanggan dengan kecerdasan masa, kedua-dua jadual perlu mengandungi tarikh dalam format yang sama (contohnya, 1/1/2012), dan sekurang-kurangnya satu jadual (kecerdasan masa) menyenaraikan setiap tarikh hanya sekali dalam lajur.

  1. Klik Data > Perhubungan.

Jika Perhubungan dikelabukan, buku kerja anda hanya mengandungi satu jadual.

  1. Dalam kotak Uruskan Perhubungan, klik Baru.
  2. Dalam kotak dialog Cipta Perhubungan, klik anak panah untuk Jadual dan pilih jadual daripada senarai. Dalam perhubungan satu kepada banyak, jadual ini patut berada di sebelah banyak. Contohnya, pelanggan dan kecerdasan masa kami, anda akan memilih jadual jualan pelanggan dahulu, kerana banyak jualan mungkin berlaku pada mana-mana hari.
  3. Untuk Lajur (Asing), pilih lajur yang mengandungi data yang berkaitan dengan Lajur Berkaitan (Utama). Contohnya, jika anda mempunyai lajur tarikh dalam kedua-dua jadual, anda akan memilih lajur itu sekarang.
  4. Untuk Jadual Berkaitan, pilih jadual yang mempunyai sekurang-kurangnya satu lajur data yang berkaitan dengan jadual yang anda baru pilih untuk Jadual.
  5. Untuk Lajur Berkaitan (Utama), pilih lajur yang mempunyai nilai unik yang sepadan dengan nilai dalam lajur yang anda pilih untuk Lajur.
  6. Klik OK.
Lanjutan tentang perhubungan antara jadual dalam Excel


Nota mengenai perhubungan

  • Anda akan tahu sama ada perhubungan wujud apabila anda menyeret medan dari jadual berlainan ke senarai Medan Jadual Pangsi. Jika anda tidak diminta untuk mencipta perhubungan, maka bermaksud Excel sudah mempunyai maklumat perhubungan yang diperlukan untuk mengaitkan data itu.
  • Mencipta perhubungan adalah seperti menggunakan VLOOKUP: anda memerlukan lajur yang mengandungi data sepadan supaya Excel dapat merujuk silang baris dalam satu jadual dengan baris dalam jadual lain. Dalam contoh kecerdasan masa, jadual Pelanggan perlu mengandungi nilai tarikh yang juga wujud dalam jadual kecerdasan masa.
  • Dalam model data, perhubungan jadual boleh merupakan satu kepada satu (setiap penumpang mempunyai satu pas masuk) atau satu kepada banyak (setiap penerbangan mempunyai banyak penumpang), tetapi bukan banyak kepada banyak. Perhubungan banyak kepada banyak menghasilkan ralat kebersandaran membulat, seperti “Kebersandaran membulat dikesan.” Ralat ini akan berlaku jika anda membuat sambungan langsung antara dua jadual yang merupakan banyak kepada banyak atau sambungan tidak langsung (suatu rantai perhubungan jadual yang merupakan satu kepada banyak dalam setiap perhubungan, tetapi banyak kepada banyak apabila dilihat secara hujung ke hujung. Ketahui selanjutnya tentang Perhubungan antara jadual dalam Model Data.
  • Jenis data dalam dua lajur tersebut mestilah serasi. Lihat Jenis data dalam Model Data Excel untuk butiran.
  • Terdapat cara lain untuk mencipta perhubungan yang mungkin lebih intuitif, terutamanya jika anda tidak pasti lajur untuk digunakan. Lihat Mencipta perhubungan dalam Pandangan Gambar Rajah dalam Power Pivot.

Contoh: Mengaitkan data kecerdasan masa kepada data penerbangan syarikat penerbangan

Anda dapat mengetahui tentang kedua-dua perhubungan jadual dan kecerdasan masa menggunakan data percuma pada Microsoft Azure Marketplace. Sesetengah set data ini adalah sangat besar, memerlukan sambungan Internet yang pantas untuk menyelesaikan muat turun data dalam tempoh masa yang munasabah.

  1. Mulakan tambahan Power Pivot dalam Microsoft Excel 2013 dan buka tetingkap Power Pivot.
  2. Klik Dapatkan Data Luaran > Daripada Perkhidmatan Data > Daripada Microsoft Azure Marketplace. Halaman utama Microsoft Azure Marketplace terbuka dalam Bestari Import Jadual.
  3. Di bawah Harga, klik Percuma.
  4. Di bawah Kategori, klik Sains & Statistik.
  5. Cari DateStream dan klik Langgan. Lebih lanjut tentang suapan data kecerdasan masa ini.
  6. Masukkan akaun Microsoft anda dan klik Daftar masuk. Pratonton data patut muncul dalam tetingkap.
  7. Skrol ke bawah dan klik Pilih Pertanyaan.
  8. Klik Berikut.
  9. Pilih BasicCalendarUS kemudian klik Selesai untuk mengimport data. Apabila menggunakan sambungan Internet yang pantas, import sepatutnya mengambil lebih kurang seminit. Apabila selesai, anda patut melihat laporan status 73,414 baris dipindahkan. Klik Tutup.
  10. Klik Dapatkan Data Luaran > Daripada Perkhidmatan Data > Daripada Microsoft Azure Marketplace untuk mengimport set data kedua.
  11. Di bawah Jenis, klik Data.
  12. Di bawah Harga, klik Percuma.
  13. Cari Tundaan Penerbangan Syarikat Penerbangan AS dan klik Pilih.
  14. Skrol ke bawah dan klik Pilih Pertanyaan.
  15. Klik Berikut.
  16. Klik Selesai untuk mengimport data. Apabila menggunakan sambungan Internet yang pantas, ini mungkin mengambil 15 minit untuk diimport. Apabila selesai, anda patut melihat laporan status 2,427,284 baris dipindahkan. Klik Tutup. Sekarang anda patut mempunyai dua jadual dalam model data. Untuk mengaitkannya, kita memerlukan lajur serasi dalam setiap jadual.
  17. Perhatikan bahawa DateKey dalam BasicCalendarUS berada dalam format 1/1/2012 12:00:00 AM. Jadual On_Time_Performance juga mempunyai lajur masa tarikh, FlightDate, yang nilainya ditentukan dalam format yang sama: 1/1/2012 12:00:00 AM. Dua lajur itu mengandungi data sepadan, yang terdiri daripada jenis data yang sama, dan sekurang-kurang satu daripada lajur itu (DateKey) hanya mengandungi nilai unik. Dalam beberapa langkah berikut, anda akan menggunakan lajur ini untuk mengaitkan jadual.
  18. Dalam tetingkap Power Pivot, klik Jadual Pangsi untuk mencipta Jadual Pangsi dalam lembaran kerja yang baru atau sedia ada.
  19. Dalam Senarai Medan, kembangkan On_Time_Performance dan klik ArrDelayMinutes untuk menambahkannya kepada kawasan Nilai. Dalam Jadual Pangsi, anda patut melihat jumlah masa penerbangan ditunda, yang diukur dalam minit.
  20. Kembangkan BasicCalendarUS dan klik MonthInCalendar untuk menambahkannya kepada kawasan Baris.
  21. Perhatikan bahawa PivotTable kini menyenaraikan bulan, tetapi jumlah minit adalah sama untuk setiap bulan. Nilai yang berulang dan sama menunjukkan suatu perhubungan diperlukan.
  22. Dalam Senarai Medan, dalam "Perhubungan antara jadual mungkin diperlukan", klik Cipta.
  23. Dalam Jadual Berkaitan, pilih On_Time_Performance dan dalam Lajur Berkaitan (Utama) pilih FlightDate.
  24. Dalam Jadual, pilih BasicCalendarUS dan dalam Lajur (Asing) pilih DateKey. Klik OK untuk mencipta perhubungan.
  25. Perhatikan bahawa jumlah minit yang ditunda kini berlainan untuk setiap bulan.
  26. Dalam BasicCalendarUS dan seret YearKey ke kawasan Baris, di atas MonthInCalendar.

Kini, anda dapat menghiris tundaan ketibaan mengikut tahun dan bulan, atau nilai lain dalam kalendar.

 Petua    Secara lalai, bulan disenaraikan dalam tertib abjad. Dengan tambahan Power Pivot , anda boleh mengubah isihan agar bulan muncul dalam tertib kronologi.

  1. Pastikan jadual BasicCalendarUS terbuka dalam tetingkap Power Pivot .
  2. Pada jadual Rumah, klik Isih mengikut Lajur .
  3. Dalam Isih, pilih MonthInCalendar
  4. Dalam Mengikut, pilih MonthOfYear .

Kini, PivotTable mengisih setiap gabungan bulan-tahun (Oktober 2011, November 2011) mengikut nombor bulan dalam suatu tahun (10, 11). Mengubah tertib isihan mudah kerana suapan DateStream membekalkan semua lajur yang perlu untuk menjayakan senario ini. Jika anda menggunakan jadual kecerdasan masa yang berlainan, langkah anda akan berlainan.

“Perhubungan antara jadual mungkin diperlukan”

Apabila anda menambahkan medan kepada PivotTable, anda akan diberitahu sama ada suatu perhubungan jadual diperlukan untuk memahami medan yang anda pilih dalam Jadual Pangsi.

Butang cipta muncul apabila hubungan diperlukan

Walaupun Excel boleh memberitahu anda apabila perhubungan diperlukan, ia tidak dapat memberitahu anda tentang jadual dan lajur untuk digunakan, atau sama ada suatu perhubungan jadual boleh dibuat. Cuba ikuti langkah ini untuk mendapatkan jawapan yang anda perlukan.

Langkah 1: Menentukan jadual untuk ditentukan dalam perhubungan

Jika model anda hanya mengandungi beberapa jadual, maka mudah untuk mengetahui jadual yang anda perlu gunakan. Tetapi untuk model yang lebih besar, anda mungkin memerlukan bantuan. Satu pendekatan adalah menggunakan Pandangan Gambar Rajah dalam tambahan Power Pivot. Pandangan Gambar Rajah menyediakan perwakilan visual semua jadual dalam Model Data. Dengan Pandangan Gambar Rajah, anda dapat menentukan dengan cepat jadual yang diasingkan daripada model selebihnya.

Pandangan gambar rajah yang menunjukkan jadual terputus sambungan

 Nota    Perhubungan taksa juga boleh dicipta yang tidak sah apabila digunakan dalam Jadual Pangsi atau laporan Power View. Katakan semua jadual anda berkaitan dalam cara tertentu dengan jadual lain dalam model itu, tetapi apabila anda cuba menggabungkan medan daripada jadual berlainan, anda mendapat mesej "Perhubungan antara jadual mungkin diperlukan". Sebab yang paling mungkin adalah anda telah menemui perhubungan banyak ke banyak. Jika anda mengikuti rantai perhubungan jadual yang bersambung dengan jadual yang anda ingin gunakan, anda mungkin mendapati bahawa anda mempunyai dua atau lebih perhubungan jadual satu ke banyak. Tiada penyelesaian mudah yang berkesan untuk setiap situasi, tetapi anda mungkin boleh cuba mencipta lajur terhitung untuk menyatukan lajur yang anda ingin gunakan dalam satu jadual.

Langkah 2: Mencari lajur yang dapat digunakan untuk mencipta laluan dari satu jadual ke jadual berikut

Selepas anda mengenal pasti jadual yang dipisahkan daripada baki model, semak semula lajurnya untuk menentukan sama ada lajur lain, yang berada di tempat lain dalam model, mengandungi nilai sepadan.

Contohnya, katakan anda mempunyai model yang mengandungi jualan produk mengikut kawasan dan anda kemudian mengimport data demografi untuk mengetahui jika ada korelasi antara aliran jualan dan demografi dalam setiap kawasan. Oleh sebab data demografi itu daripada sumber data yang berlainan, jadualnya diasingkan daripada model selebihnya pada mulanya. Untuk menyepadukan data demografi dengan model anda yang selebihnya, anda perlu mencari lajur dalam satu daripada jadual demografi yang sepadan dengan jadual yang anda sedang gunakan. Contohnya jika data demografi diatur mengikut rantau dan data jualan anda menentukan rantau tempat jualan berlaku, anda dapat mengaitkan dua set data itu dengan mencari lajur umum seperti Negeri, Poskod atau Rantau, untuk menyediakan carian.

Di samping nilai sepadan, terdapat beberapa keperluan tambahan untuk mencipta perhubungan:

  • 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 kedua-dua lajur sumber dan lajur carian mesti sepadan. Untuk maklumat lanjut tentang jenis data, lihat Jenis data dalam Model Data .

Untuk mengetahui lebih lanjut tentang perhubungan jadual, lihat Perhubungan antara jadual dalam Model Data.

Atas Halaman Atas Halaman

 
 
Berkenaan dengan:
Excel 2013, Power Pivot in Excel 2013