Model Data di Excel

Apa Model Data di Excel?

Model data di excel adalah jenis tabel data di mana kita dua atau lebih dari dua tabel saling berhubungan satu sama lain melalui seri data yang umum atau lebih, dalam model data tabel dan data dari berbagai lembar atau sumber lain berkumpul untuk membentuk yang unik tabel yang dapat memiliki akses ke data dari semua tabel.

Penjelasan

  • Ini memungkinkan pengintegrasian data dari beberapa tabel dengan membuat hubungan berdasarkan kolom umum.
  • Model data digunakan secara transparan, menyediakan data tabular yang dapat digunakan dalam Tabel Pivot di Excel dan Diagram Pivot di excel. Ini mengintegrasikan tabel, memungkinkan analisis ekstensif menggunakan Tabel Pivot, Power Pivot, dan Power View di Excel.
  • Model data memungkinkan memuat data ke dalam memori Excel.
  • Itu disimpan dalam memori di mana kita tidak dapat melihatnya secara langsung. Kemudian Excel dapat diinstruksikan untuk menghubungkan data satu sama lain menggunakan kolom umum. Bagian 'Model' dari Model Data mengacu pada bagaimana semua tabel berhubungan satu sama lain.
  • Model Data dapat mengakses semua informasi yang dibutuhkan bahkan ketika informasi tersebut ada dalam beberapa tabel. Setelah Model Data dibuat, Excel memiliki data yang tersedia di memorinya. Dengan adanya data di dalam memorinya, data tersebut dapat diakses dengan berbagai cara.

Contoh

Anda dapat mengunduh Templat Excel Model Data ini di sini - Templat Excel Model Data

Contoh 1

Jika kita memiliki tiga dataset yang berkaitan dengan penjual: Pertama berisi informasi pendapatan, yang kedua berisi pendapatan tenaga penjual, dan ketiga berisi biaya-biaya tenaga penjual.

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

  • Konversikan kumpulan data ke objek Tabel:

Kami tidak dapat membuat hubungan dengan kumpulan data biasa. Model Data hanya berfungsi dengan objek Tabel Excel. Untuk melakukan ini:

  • Langkah 1 - Klik di mana saja di dalam dataset lalu, klik tab 'Sisipkan' dan kemudian klik 'Tabel' di grup 'Tabel'.

  • Langkah 2 - Centang atau hapus centang opsi: 'Tabel Saya memiliki header' dan klik OK.

  • Langkah 3 - Dengan memilih tabel baru, masukkan nama Tabel di 'Nama Tabel' di grup 'Alat'.

  • Langkah 4 - Sekarang kita dapat melihat bahwa dataset pertama diubah menjadi objek 'Tabel'. Saat mengulangi langkah-langkah ini untuk dua dataset lainnya, kita melihat bahwa mereka juga diubah menjadi objek 'Tabel' seperti di bawah ini:

Menambahkan objek 'Tabel' ke Model Data: Melalui Koneksi atau Hubungan.

Melalui Koneksi

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

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

  • Klik 'Tabel' di kotak dialog yang muncul, lalu pilih salah satu tabel dan klik 'Buka'.

Saat melakukan ini, model data buku kerja akan dibuat dengan satu tabel dan kotak dialog muncul sebagai berikut:

Jadi jika kita mengulangi langkah-langkah ini untuk dua tabel lainnya juga, Model Data sekarang akan berisi ketiga tabel tersebut.

Sekarang kita dapat melihat bahwa ketiga tabel muncul di Koneksi Buku Kerja.

Melalui Hubungan

Buat hubungan: Setelah kedua dataset menjadi objek Tabel, kita dapat membuat hubungan di antara keduanya. Untuk melakukan ini:

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

  • Kami akan melihat kotak dialog kosong karena tidak ada koneksi saat ini.

  • Klik 'Baru' dan kotak dialog lain muncul.

  • Perluas menu tarik-turun 'Tabel' dan 'Tabel Terkait': Kotak dialog 'Buat hubungan' muncul untuk memilih tabel dan kolom yang akan digunakan untuk suatu hubungan. Dalam perluasan 'Tabel', pilih kumpulan data yang ingin kami analisis, dan di 'Tabel Terkait', pilih kumpulan data yang memiliki nilai pencarian.
  • Tabel pencarian di excel adalah tabel yang lebih kecil jika ada hubungan satu ke banyak dan tidak berisi nilai berulang di kolom umum. Dalam perluasan 'Kolom (Asing)', pilih kolom umum di tabel utama, di 'Kolom Terkait (Utama)', pilih kolom umum di tabel terkait.

  • Dengan keempat pengaturan ini dipilih, klik 'OK'. Sebuah kotak dialog muncul sebagai berikut saat mengklik 'OK'.

Jika kita mengulangi langkah-langkah ini untuk menghubungkan dua tabel lainnya: Tabel Pendapatan dengan tabel Beban, maka mereka juga terkait dalam Model Data sebagai berikut:

Excel sekarang membuat hubungan di balik layar dengan menggabungkan data dalam Model Data berdasarkan kolom umum: ID Tenaga Penjual (dalam hal ini).

Contoh # 2

Sekarang, katakanlah dalam contoh di atas kita ingin membuat Tabel Pivot yang mengevaluasi atau menganalisis objek Tabel:

  • Klik 'Sisipkan' -> 'Tabel Pivot'.

  • Di kotak dialog yang muncul, klik opsi yang menyatakan: 'Gunakan sumber data eksternal' dan kemudian klik 'Pilih Koneksi'.

  • Klik 'Tabel' di kotak dialog yang dihasilkan dan pilih Model data Buku Kerja yang berisi tiga tabel dan klik 'Buka'.

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

  • Panel Pivot Table Fields akan menampilkan objek tabel.

  • Sekarang perubahan dalam Tabel Pivot dapat dilakukan sesuai sehingga dapat menganalisis objek tabel sesuai kebutuhan.

Misalnya, dalam kasus ini, jika kita ingin mencari total pendapatan atau pendapatan untuk penjual tertentu, maka Tabel Pivot dibuat sebagai berikut:

Ini sangat membantu dalam kasus model / tabel yang berisi observasi dalam jumlah besar.

Jadi, kita dapat melihat bahwa Tabel Pivot secara instan menggunakan Model Data (memilihnya dengan memilih koneksi) di memori Excel untuk memperlihatkan hubungan antar tabel.

Hal-hal untuk diingat

  • Dengan Model Data, kita dapat menganalisis data dari beberapa tabel sekaligus.
  • Dengan membuat hubungan dengan Model Data, kami melampaui kebutuhan untuk menggunakan VLOOKUP, SUMIF, fungsi INDEX, dan rumus MATCH karena kami tidak perlu mendapatkan semua kolom dalam satu tabel.
  • Saat kumpulan data diimpor di Excel dari sumber luar, maka model dibuat secara implisit.
  • Hubungan tabel dapat dibuat secara otomatis jika kita mengimpor tabel terkait yang memiliki hubungan kunci utama dan asing.
  • Saat membuat hubungan, kolom yang kita hubungkan dalam tabel harus memiliki tipe data yang sama.
  • Dengan tabel pivot yang dibuat dengan Model data, kita juga dapat menambahkan pemotong dan memotong tabel pivot pada bidang apa pun yang kita inginkan.
  • Keuntungan Model Data dibandingkan fungsi LOOKUP () adalah memerlukan lebih sedikit memori.
  • Excel 2013 hanya mendukung satu ke satu atau satu ke banyak hubungan, yaitu salah satu tabel tidak boleh memiliki nilai duplikat pada kolom yang kita tautkan.