Model Data dalam Excel

Apakah Model Data di Excel?

Model data dalam excel adalah jenis tabel data di mana kita dua atau lebih dari dua jadual saling berhubungan satu sama lain melalui satu siri data yang umum atau lebih, dalam jadual model data dan data dari pelbagai helaian atau sumber lain berkumpul untuk membentuk suatu yang unik jadual yang boleh mempunyai akses ke data dari semua jadual.

Penjelasan

  • Ia membolehkan mengintegrasikan data dari beberapa jadual dengan membuat hubungan berdasarkan lajur umum.
  • Model data digunakan secara telus, menyediakan data tabular yang dapat digunakan dalam Pivot Table di Excel dan Pivot Charts di excel. Ia mengintegrasikan jadual, memungkinkan analisis yang luas menggunakan Pivot Tables, Power Pivot, dan Power View di Excel.
  • Model data membolehkan memuatkan data ke dalam memori Excel.
  • Ia disimpan dalam ingatan di mana kita tidak dapat melihatnya secara langsung. Kemudian Excel boleh diperintahkan untuk menghubungkan data antara satu sama lain menggunakan lajur biasa. Bahagian 'Model' Model Data merujuk kepada bagaimana semua jadual saling berkaitan antara satu sama lain.
  • Model Data dapat mengakses semua maklumat yang diperlukannya walaupun maklumat tersebut terdapat dalam beberapa jadual. Setelah Model Data dibuat, Excel mempunyai data yang tersedia dalam ingatannya. Dengan data dalam ingatannya, data dapat diakses dengan banyak cara.

Contoh

Anda boleh memuat turun Templat Excel Model Data ini di sini - Templat Excel Model Data

Contoh # 1

Sekiranya kita mempunyai tiga set data yang berkaitan dengan jurujual: Pertama mengandungi maklumat pendapatan, yang kedua mengandungi pendapatan jurujual, dan ketiga berisi perbelanjaan jurujual.

Untuk menghubungkan ketiga set data ini dan membuat hubungan dengan ini, kami membuat Model Data dengan langkah-langkah berikut:

  • Tukar set data ke objek Jadual:

Kami tidak dapat menjalin hubungan dengan set data biasa. Model Data berfungsi dengan objek Excel Tables sahaja. Untuk membuat ini:

  • Langkah 1 - Klik di mana sahaja di dalam set data kemudian, klik pada tab 'Masukkan' dan kemudian klik pada 'Jadual' dalam kumpulan 'Jadual'.

  • Langkah 2 - Tandakan atau hapus centang pilihan: 'Meja saya mempunyai tajuk' dan klik OK.

  • Langkah 3 - Dengan jadual baru yang dipilih, masukkan nama Jadual dalam 'Nama Jadual' dalam kumpulan 'Alat'.

  • Langkah 4 - Sekarang kita dapat melihat bahawa set data pertama ditukar menjadi objek 'Table'. Setelah mengulangi langkah-langkah ini untuk dua set data yang lain, kami melihat bahawa mereka juga ditukarkan ke objek 'Table' seperti di bawah:

Menambah objek 'Table' ke Model Data: Melalui Sambungan atau Hubungan.

Melalui Sambungan

  • Pilih satu jadual dan klik pada tab 'Data' dan kemudian klik pada 'Connections'.

  • Di kotak dialog yang dihasilkan, terdapat ikon 'Tambah'. Kembangkan dropdown 'Add' dan klik 'Add to the Data Model'.

  • Klik 'Tables' di kotak dialog yang dihasilkan dan kemudian pilih salah satu jadual dan klik 'Buka'.

Setelah melakukan ini, model data buku kerja akan dibuat dengan satu meja dan kotak dialog muncul seperti berikut:

Oleh itu, jika kita mengulangi langkah-langkah ini untuk dua jadual yang lain, Model Data sekarang akan mengandungi ketiga-tiga jadual.

Kita sekarang dapat melihat bahawa ketiga-tiga jadual muncul di Buku Kerja Sambungan.

Melalui Hubungan

Buat hubungan: Setelah kedua-dua set data menjadi objek Jadual, kita dapat membuat hubungan di antara keduanya. Untuk membuat ini:

  • Klik pada tab 'Data' dan kemudian klik pada 'Hubungan'.

  • Kami akan melihat kotak dialog kosong kerana tidak ada sambungan semasa.

  • Klik pada 'Baru' dan kotak dialog lain muncul.

  • Luaskan dropdown 'Table' dan 'Table Related': Kotak dialog 'Buat hubungan' muncul untuk memilih jadual dan lajur yang akan digunakan untuk hubungan. Dalam pengembangan 'Tabel', pilih set data yang ingin kita analisis dalam beberapa cara, dan dalam 'Jadual Berkaitan', pilih set data yang memiliki nilai pencarian.
  • Jadual pencarian di excel adalah jadual yang lebih kecil sekiranya terdapat hubungan antara satu hingga banyak dan tidak mengandungi nilai berulang pada lajur umum. Dalam pengembangan 'Kolom (Asing)', pilih lajur umum di tabel utama, di 'Kolom Berkaitan (Utama)', pilih lajur umum di tabel terkait.

  • Dengan keempat-empat tetapan ini dipilih, klik 'OK'. Kotak dialog muncul seperti berikut pada mengklik 'OK'.

Sekiranya kita mengulangi langkah-langkah ini untuk mengaitkan dua jadual lain: Jadual Hasil dengan jadual Perbelanjaan, maka mereka juga berkaitan dalam Model Data seperti berikut:

Excel kini mewujudkan hubungan di belakang tabir dengan menggabungkan data dalam Model Data berdasarkan lajur umum: ID Jurujual (dalam kes ini).

Contoh # 2

Sekarang, katakan pada contoh di atas bahawa kita ingin membuat Jadual Pangsi yang menilai atau menganalisis objek Jadual:

  • Klik pada 'Insert' -> 'Pivot Table'.

  • Pada kotak dialog yang dihasilkan, klik pada pilihan yang menyatakan: 'Gunakan sumber data luaran' dan kemudian klik 'Pilih Sambungan'.

  • Klik pada 'Tables' di kotak dialog yang dihasilkan dan pilih Model data Workbook yang mengandungi tiga jadual dan klik 'Open'.

  • Pilih pilihan 'Lembar Kerja Baru' di lokasi dan klik 'OK'.

  • Panel Pivot Table Fields akan memaparkan objek meja.

  • Sekarang perubahan dalam Jadual Pivot dapat dilakukan dengan sewajarnya sehingga dapat menganalisis objek meja seperti yang diperlukan.

Sebagai contoh, dalam hal ini, jika kita ingin mencari jumlah hasil atau hasil untuk jurujual tertentu, maka Pivot Table dibuat seperti berikut:

Ini sangat membantu sekiranya model / jadual mengandungi sebilangan besar pemerhatian.

Oleh itu, kita dapat melihat bahawa Jadual Pivot dengan serta-merta menggunakan Model Data (memilihnya dengan memilih sambungan) dalam memori Excel untuk menunjukkan hubungan antara jadual.

Perkara yang Perlu Diingat

  • Dengan menggunakan Model Data, kita dapat menganalisis data dari beberapa jadual sekaligus.
  • Dengan menjalin hubungan dengan Model Data, kita melampaui kebutuhan untuk menggunakan formula VLOOKUP, SUMIF, INDEX, dan MATCH kerana kita tidak perlu mendapatkan semua lajur dalam satu jadual.
  • Apabila set data diimport dalam Excel dari sumber luar, maka model dibuat secara tersirat.
  • Hubungan jadual dapat dibuat secara automatik jika kita mengimport jadual berkaitan yang mempunyai hubungan utama dan asing.
  • Semasa membuat hubungan, lajur yang kami sambungkan dalam jadual harus mempunyai jenis data yang sama.
  • Dengan jadual pangsi yang dibuat dengan Model data, kita dapat menambahkan pemotong juga dan memotong jadual pangsi pada bidang apa pun yang kita mahukan.
  • Kelebihan fungsi Model Data berbanding LOOKUP () ialah memerlukan memori yang jauh lebih sedikit.
  • Excel 2013 hanya menyokong satu ke satu atau satu ke banyak hubungan, iaitu salah satu jadual tidak boleh mempunyai nilai pendua pada lajur yang kita pautkan.