Cara Membuat Data Model di Excel: Panduan Komprehensif

Data model memungkinkan kita memuat, mengintegrasikan, dan menganalisis data dari berbagai sumber, sehingga memberikan pandangan yang lebih dinamis dan mendalam terhadap data. 

Di sepanjang tutorial, kita akan mengeksplorasi beberapa cara membuat data model di Excel, termasuk menggunakan tabel yang sudah ada, mengimpor tabel baru, dan memanfaatkan alat seperti Power Query, Power Pivot, dan PivotChart.   

Mari kita mulai!

Cara Membuat Data Model di Excel

Berikut langkah umum yang perlu diikuti untuk membuat data model di Excel:

1. Format data Anda sebagai tabel.
2. Buat relasi antar tabel.
3. Gunakan Power Query untuk memuat dan mentransformasi data.
4. Gunakan Power Pivot untuk pemodelan data lanjutan.
5. Manfaatkan PivotTable dan PivotChart untuk memvisualisasikan data Anda.

Kapan Menggunakan Data Model di Excel

Membuat data model di Excel sangat berguna dalam beberapa skenario umum, termasuk pembersihan data, integrasi dan transformasi data, serta analisis data kompleks. Berikut beberapa manfaat utamanya.

• Mempermudah Pembersihan Data: Data model membantu membersihkan dan menstandarkan berbagai himpunan data untuk menjaga keseragaman dan konsistensi. Ini krusial saat menangani data dari berbagai sumber yang perlu digabung dan dipraolah. 
• Melakukan atau Menyelesaikan Integrasi dan Transformasi Data: Dengan data model, semua data yang tersedia dari sumber mana pun dapat dengan mudah diintegrasikan dan ditransformasikan, sehingga pengguna data memperoleh tampilan terpadu yang mudah diproses dan dianalisis.
• Meningkatkan Visualisasi Data: Data model mendukung pembuatan visualisasi data tingkat lanjut seperti PivotChart dan laporan Power View. 
• Meningkatkan Keamanan dan Manajemen Data: Dengan data model, akses terkontrol ke berbagai bagian data Anda dapat diterapkan. Anda akan lebih mudah mengelola izin dan data dalam model untuk mengurangi risiko akses tidak sah dan kebocoran data.
• Menyederhanakan Pelaporan Data: Melalui data model, pelaporan data dapat diotomatisasi sepenuhnya, sehingga mempermudah proses pelaporan dan pengambilan keputusan. Ini memungkinkan laporan dan dasbor disesuaikan secara dinamis ketika ada penambahan data baru, sehingga semua pemangku kepentingan mendapatkan informasi terbaru.
• Menangani Dataset Besar: Data model dapat bekerja dengan dataset besar di Excel, melampaui keterbatasan lembar kerja Excel tradisional.
• Memungkinkan Analisis Data Kompleks: Fitur pembuatan relasi antar banyak tabel data memungkinkan analisis data tingkat lanjut yang mungkin tidak mudah dilakukan dengan fungsi Excel biasa.

Cara Membuat Data Model dengan Mengimpor Data

Pada bagian ini, kita akan melalui proses membuat data model di Excel dengan mengimpor dataset contoh. Kita akan menggunakan dua dataset contoh: Order dan Product. Saya telah membuat repositori GitHub publik bernama Excel-Data-Model agar Anda dapat mengunduh dataset dan mengikuti tutorial ini.

Langkah pertama adalah mengimpor data ke Excel. Mulailah dengan membuka workbook Excel baru, lalu klik tab Data. Di bawah Get Data, pilih Get Data > From File > From Excel Workbook.

Mengimpor data ke Excel. Gambar oleh Penulis

Pilih workbook Order.xlsx, lalu klik Import. Ini akan membuka jendela Navigator: Pilih tabel Sheet1.

Jendela Navigator di Excel. Gambar oleh Penulis

Klik opsi Load To yang akan membuka jendela Import Data. Di kotak dialog, pilih Only Create Connection dan centang Add this data to the Data Model. Klik OK.

Jendela Import Data. Gambar oleh Penulis

Ulangi langkah di atas untuk memuat sheet Product. Setelah kedua tugas ini selesai, workbook Excel Anda akan terlihat seperti ini:

Panel Queries & Connections. Gambar oleh Penulis

Selamat! Anda telah mengimpor kedua sheet ke file Excel. Langkah berikutnya adalah membangun relasi antar tabel yang diimpor. Untuk melakukannya, buka tab Data Tools dan klik Relationships.

Tab Data Tools. Gambar oleh Penulis 

Sebuah jendela baru akan muncul.

Jendela Manage Relationships. Gambar oleh Penulis

Klik New untuk membuat relasi. Pop-up Create Relationship akan terbuka. Definisikan relasi seperti ditunjukkan di bawah ini. 

Jendela Create Relationship. Gambar oleh Penulis

Klik OK. Relasi dibuat, dan Anda dapat mengonfirmasinya di tab Connections.

Panel Queries & Connections. Gambar oleh Penulis

Tahap berikutnya adalah membuat PivotTable. Buka tab Insert dan klik PivotTable. Lalu, di kotak dialog PivotTable, pilih Use this workbook’s Data Model.

Kotak dialog PivotTable. Gambar oleh Penulis

Pilih lokasi untuk PivotTable. Klik OK. PivotTable akan terbuka dan Anda dapat melihat field di bawah PivotTable Fields.

PivotTable Fields. Gambar oleh Penulis

Untuk membangun PivotTable, lakukan langkah berikut.

Di panel PivotTable Fields, seret field ke area Rows, Columns, Values, dan Filters:

• Rows: ProductName
• Values: Quantity

PivotTable yang dihasilkan akan terlihat seperti ini:

Panel PivotTable Fields. Gambar oleh Penulis

Anda dapat menyesuaikan PivotTable dan memformatnya sesuai kebutuhan.

Cara Membuat Data Model Menggunakan Power Query 

Untuk bagian ini, kita akan menggunakan data yang tersimpan di file Excel bernama Sample_Data. File ini juga tersedia di tautan yang sama seperti pada bagian sebelumnya. Ada dua tabel di sheet bernama Data yang memiliki catatan sebagai berikut.

Tabel Excel untuk digabung. Gambar oleh Penulis

Kita akan membangun data model di sheet Excel yang sudah ada ini. Untuk memulai, pilih sel mana pun dalam tabel Customer ID and Name lalu tekan Ctrl + T.

Memilih sebuah sel. Gambar oleh Penulis

Ini membuka pop-up Create Table seperti di bawah ini.

Pop-up Create table. Gambar oleh Penulis

 Klik OK dan tabel siap digunakan. 

PivotTable di Excel. Gambar oleh Penulis

Sekarang mari beri nama tabel ini. Buka jendela kontekstual Table Design dan di bawah Table Name, ubah nama tabel menjadi CustomerPQ. Anda juga bisa memberikan nama lain. 

Jendela konteks Table Design. Gambar oleh Penulis

Sekarang tabel ini diberi nama CustomerPQ.

Jendela konteks Table Design. Gambar oleh Penulis

Ulangi proses untuk tabel kedua. Kali ini, setelah tabel dibuat, beri nama PurchasePQ.

Dua tabel di Excel siap untuk Data Model. Gambar oleh Penulis

Sekarang, tabel aktif adalah tabel PurchasePQ. Buka Data, lalu pilih From Table Range. Lembar berikut akan muncul.

Membuka Power Query Editor. Gambar oleh Penulis

Untuk membuat koneksi dan menambahkan tabel Purchase PQ ke Data Model, klik menu tarik-turun Close & Load, lalu pilih Close & Load To…  

Menutup Power Query Editor. Gambar oleh Penulis

Ini akan membuka jendela Import Data yang perlu diisi seperti berikut:

Jendela Import Data. Gambar oleh Penulis 

Setelah Anda menyelesaikan langkah di atas, Anda akan melihat kotak Queries & Connections.

Kotak Queries & Connections. Gambar oleh Penulis

Sekarang, tambahkan tabel CustomerPQ ke koneksi. Untuk itu, klik salah satu sel tabel, buka Data, lalu pilih From Table Range. Lembar berikut akan muncul.

Tabel siap untuk Data Model Excel. Gambar oleh Penulis

Ulangi proses yang Anda lakukan untuk tabel PurchasePQ, dan isi jendela Import Data seperti di bawah ini:

Jendela Import Data. Gambar oleh Penulis 

 Klik OK. Tab Queries & Connections kini akan menampilkan kedua tabel.

Queries and Connections. Gambar oleh Penulis

Sekarang kita siap membangun relasi. Di tab Data, buka bagian Data Tools, lalu klik Manage Data Model.

Data Tools. Gambar oleh Penulis

Ini akan membuka Power Pivot for Excel.

Power Pivot untuk Excel. Gambar oleh Penulis

Sekarang untuk membuat relasi antara dua tabel, klik Diagram View.

Panel View. Gambar oleh Penulis

 Ini akan membuka layar seperti di bawah ini. 

Diagram View. Gambar oleh Penulis

Anda dapat melihat ada dua tabel, namun belum terhubung. Untuk membuat relasi, buka tab Design dan klik tab Create Relationship.

Tab Design. Gambar oleh Penulis

Sebuah jendela baru akan terbuka. Isi detail seperti di bawah ini:

Jendela Create a Relationship. Gambar oleh Penulis

Klik OK. Anda akan melihat bahwa relasi kini terbentuk.

Jendela menunjukkan relasi telah terbentuk. Gambar oleh Penulis 

Simpan Power Pivot menggunakan menu simpan atau Ctrl + S, lalu tutup tab. Hasil berikut ditampilkan.

Pop-up Enable Content. Gambar oleh Penulis

Klik Enable Content untuk membuat koneksi. Sekarang koneksi telah dibuat. Kita bisa menggunakan PivotTable sekarang. Untuk itu, buka Insert dan klik opsi PivotTable, lalu pilih From Data Model.

Panel PivotTable. Gambar oleh Penulis

Anda akan mendapatkan pop-up seperti ini:

Tab PivotTable from Data Model. Gambar oleh Penulis

Klik New Worksheet lalu klik OK. Lembar baru bernama Sheet1 dibuat.

Lembar PivotTable. Gambar oleh Penulis 

Di PivotTable Fields, Anda dapat menemukan tabel-tabelnya.

Label di bawah PivotTable Fields. Gambar oleh Penulis

Pilih Customer Name di tabel CustomerPQ. Selanjutnya di bawah PurchasePQ, pilih Purchase. PivotTable Fields akan terlihat seperti ini:

Memilih item di PivotTable Fields. Gambar oleh Penulis 

Ini akan menghasilkan PivotTable di dalam lembar yang akan tampak seperti berikut:

Keluaran PivotTable. Gambar oleh Penulis

Mantap! Dengan cara ini, Anda dapat menggunakan Power Query untuk membuat Data Model dan menggunakan Power Pivot untuk membuat laporan ringkasan.

Cara Membuat Data Model Langsung di Power Pivot

Di bagian ini, Anda akan menggunakan Power Pivot untuk membuat data model, dan menyiapkan laporan ringkasan. 

Untuk memulai, buka sheet bernama Power Pivot pada data yang telah kita bahas di atas, lalu buat tabel untuk dua catatan kita.

Catatan yang akan digunakan. Gambar oleh Penulis 

Kita sudah melihat cara membuat tabel pada bagian sebelumnya, jadi ikuti langkah tersebut untuk membuat dua tabel ini.

Satu-satunya perbedaan adalah kita akan memberi nama baru untuk tabel-tabel ini, agar dapat dibedakan dari bagian sebelumnya: 

• Tetapkan nama tabel pertama menjadi CustomerPP.
• Tetapkan nama tabel kedua menjadi PurchasePP.

Data Tabel dari catatan. Gambar oleh Penulis

Kedua tabel telah dibuat dan langkah berikutnya adalah menambahkan tabel-tabel ini ke data model. Untuk melakukannya, buka Power Pivot, lalu pilih Add to Data Model.

Add to Data Model di bawah PowerPivot. Gambar oleh Penulis

Ini akan membuka pop-up Power Pivot for Excel yang akan terlihat seperti ini.

Pop-up Power Pivot for Excel. Gambar oleh Penulis 

Simpan dan tutup jendela ini. Ulangi proses yang sama untuk tabel CustomerPP untuk menambahkannya ke data model.

Pop-up Power Pivot for Excel. Gambar oleh Penulis

Setelah tabel-tabel ini ditambahkan ke data model, Anda dapat membangun relasi di antara keduanya menggunakan Power Pivot. Untuk melakukannya, klik Diagram View.

Diagram View. Gambar oleh Penulis

Selanjutnya, buka tab Design dan klik Create Relationship.

Tab Create a Relationship. Gambar oleh Penulis

Sebuah jendela baru akan terbuka. Isi detail seperti di bawah ini:

Membuat relasi antara dua tabel. Gambar oleh Penulis

 Klik OK. Anda akan melihat bahwa relasi telah terbentuk.

Relasi telah terbentuk. Gambar oleh Penulis

Membuat PivotTable dari sini sangat mudah, seperti yang telah kita lakukan pada bagian sebelumnya. Hasil akhirnya seharusnya terlihat seperti ini.

Ringkasan PivotTable. Gambar oleh Penulis

Dengan mengikuti langkah di atas, Anda dapat menggunakan Power Pivot untuk membuat Data Model dan melakukan analisis ringkas.

Cara Membuat Data Model dengan PivotTable atau PivotChart

Setelah Anda membuat PivotTable, menyisipkan PivotChart menjadi mudah. Dengan PivotTable (Sheet 1) terpilih, buka tab Insert.

Ringkasan PivotTable. Gambar oleh Penulis

Klik PivotChart, pilih jenis bagan, lalu klik OK.

Opsi Insert Chart. Gambar oleh Penulis

Ini akan membuat PivotChart berikut.

Visualisasi PivotChart. Gambar oleh Penulis

Anda dapat menyesuaikan PivotChart dengan cara:

• Mengatur elemen bagan, seperti judul, legenda, dan label sumbu, serta
• Memformat bagan untuk meningkatkan tampilan dan keterbacaannya.

Bagus, Anda telah menggunakan PivotTable untuk membuat visualisasi pertama Anda.

Pertanyaan dan Masalah Umum Terkait Data Model Excel

Berikut beberapa pertanyaan dan masalah yang umum terkait Data Model Excel:

Apa yang harus saya lakukan jika masalah data model mencegah Microsoft Excel membuka workbook?

Masalah ini dapat terjadi karena kerusakan pada data model atau masalah kompatibilitas. Berikut langkah-langkah untuk mengatasinya:

1. Buka dalam Safe Mode: Coba buka Excel dalam Safe Mode dengan menahan tombol Ctrl saat memulai Excel. Ini terkadang dapat melewati masalah.
2. Periksa Pembaruan: Pastikan Excel mutakhir dengan membuka File > Account > Update Options.
3. Perbaiki Office: Gunakan alat perbaikan Office. Buka Control Panel > Programs and Features, pilih instalasi Office Anda, lalu pilih Change > Repair.
4. Nonaktifkan Add-in: Buka File > Options > Add-ins dan nonaktifkan semua add-in. Mulai ulang Excel dan coba buka kembali workbook.
5. Ekstrak Data: Jika Anda tidak dapat membuka workbook, coba gunakan alat pemulihan data atau hubungi dukungan Microsoft untuk bantuan lanjutan.

Bagaimana saya dapat menelusuri kesalahan dalam data model?

Untuk menelusuri kesalahan dalam data model:

1. Periksa Jenis Data: Pastikan jenis data di kolom yang digunakan untuk relasi konsisten.
2. Validasi Relasi: Verifikasi bahwa semua relasi telah didefinisikan dengan benar.
3. Tinjau Perhitungan: Periksa kolom terhitung dan measure untuk mencari kesalahan.
4. Refresh Data: Pastikan semua koneksi data diperbarui.
5. Pesan Kesalahan: Tinjau pesan kesalahan di jendela Power Pivot untuk masalah spesifik.

Dengan mengikuti langkah-langkah ini, Anda dapat mengelola dan menelusuri data model di Excel secara efektif, memastikan analisis data berjalan lancar dan efisien. Dukungan Microsoft juga merupakan sumber yang membantu untuk pemecahan masalah. 

Penutup

Terima kasih telah menyelesaikan tutorial ini! Anda telah mempelajari konsep penting dalam perjalanan menguasai analisis data menggunakan Excel. Membuat data model akan secara signifikan meningkatkan kemampuan analisis data Anda dan organisasi Anda. Ini juga meningkatkan efisiensi Anda dalam mengelola berbagai tabel dan catatan data. 

Untuk pembelajaran lebih lanjut, pertimbangkan untuk mengeksplorasi sumber berikut:   

• Introduction to Excel: Panduan komprehensif untuk memulai dengan Excel, mencakup fungsi dan fitur dasar.
• Data Analysis in Excel: Pelajari berbagai teknik dan alat analisis data di Excel untuk meningkatkan keterampilan analitis Anda.
• Excel Fundamentals: Kursus yang membahas konsep dan operasi dasar di Excel untuk membangun fondasi yang kuat.
• Data Visualization in Excel: Jelajahi cara membuat visualisasi yang berdampak dan interaktif menggunakan alat bawaan Excel.
• Introduction to Power Query in Excel: Pahami cara menggunakan Power Query untuk mengimpor, membersihkan, dan mentransformasi data secara efisien di Excel.