Tidak punya Power Pivot? Tidak masalah. Sebagian besar Power Pivot dibangun di Excel 2013 dan bahkan lebih banyak lagi di Excel 2016. Hari ini, tip kami dari Ash adalah menggabungkan tabel dalam tabel pivot.
Setiap Rabu selama tujuh minggu, saya menampilkan salah satu tip favorit dari Ash Sharma. Ash adalah manajer produk di tim Excel. Timnya membawakan Anda tabel pivot dan banyak hal baik lainnya. Saat ini, fitur favorit Ash adalah menggabungkan beberapa kumpulan data menggunakan Hubungan dan Model Data.
Katakanlah departemen TI Anda memberi Anda kumpulan data yang diperlihatkan di kolom A: D. Ada bidang untuk pelanggan dan pasar. Anda perlu menggabungkan pasar tertentu menjadi beberapa wilayah. Setiap pelanggan termasuk dalam suatu sektor. Kawasan dan Sektor tidak ada dalam data asli, tetapi Anda memiliki tabel pencarian untuk menyediakan informasi ini.
Biasanya, Anda akan meratakan data dengan menggunakan VLOOKUP untuk menarik data dari tabel oranye dan kuning ke tabel biru. Tetapi karena bidang kunci tidak berada di sisi kiri setiap tabel, Anda harus beralih ke INDEX dan MATCH, atau mengatur ulang tabel pencarian.
Mulai di Excel 2013, Anda dapat meninggalkan tabel pencarian di tempatnya dan menggabungkannya dalam laporan tabel pivot itu sendiri.
Agar teknik ini berfungsi, ketiga tabel harus diformat sebagai Tabel. Pilih satu sel di setiap kumpulan data dan pilih Beranda, Format sebagai Tabel atau tekan Ctrl + T. Ketiga tabel awalnya akan disebut Tabel1, Tabel2, dan Tabel3. Saya menggunakan tab Desain Alat Tabel di Pita dan menamai ulang setiap tabel. Saya juga mengubah warna setiap tabel. Dalam contoh ini, tabel biru disebut Data. Tabel oranye adalah RegionTable. Tabel kuning adalah Tabel Sektor.
Catatan
Beberapa akan memberi tahu Anda bahwa Anda harus menggunakan nama geeky seperti Fact, TblSector dan TblRegion. Jika ada yang merepotkan Anda seperti ini, curi saja pelindung sakunya dan beri tahu mereka bahwa Anda lebih suka nama yang terdengar dalam bahasa Inggris.
Untuk mengganti nama tabel, ketikkan nama baru dalam kotak di sisi kiri tab Desain Alat Tabel. Nama tabel tidak boleh memiliki spasi.
Setelah ketiga tabel ditentukan, buka tab Data dan klik Hubungan.
Dalam dialog Kelola Hubungan, klik Baru. Dalam dialog Buat Hubungan, tentukan bahwa bidang Pelanggan tabel Data terkait dengan Bidang Pelanggan dari Tabel Sektor. Klik OK.
Tentukan hubungan baru lainnya antara bidang Pasar di bidang Data dan RegionTable. Setelah menentukan kedua hubungan, Anda akan melihatnya di dialog Kelola Hubungan.
Selamat: Anda baru saja membuat Model Data di buku kerja Anda. Saatnya membangun tabel pivot.
Pilih sel kosong tempat Anda ingin tabel pivot Anda muncul. Secara default, dialog Buat PivotTable akan memilih Gunakan Model Data Buku Kerja Ini. Lokasi tabel pivot akan default ke sel yang Anda pilih. Klik OK.
Daftar Bidang Tabel Pivot akan mencantumkan ketiga tabel. Gunakan segitiga di sebelah kiri tabel untuk memperluas nama tabel untuk menampilkan bidang.
Perluas tabel Data. Pilih bidang Pendapatan. Ini secara otomatis akan pindah ke area Nilai. Perluas Tabel Sektor. Pilih bidang Sektor. Ini akan pindah ke area Rows. Perluas RegionTable. Seret bidang Wilayah ke area Kolom. Anda sekarang akan memiliki tabel pivot yang meringkas data dari tiga tabel.
Catatan
Dalam setiap buku yang saya tulis sebelumnya hari ini, saya menggunakan teknik yang berbeda untuk menyusun laporan ini. Setelah menentukan tiga tabel, saya memilih sel A1 dan Sisipkan, Tabel Pivot. Saya mencentang kotak untuk Tambahkan Data Ini ke Model Data. Dalam daftar Bidang Tabel Pivot, pilih Semua dari bagian atas daftar. Pilih bidang untuk laporan, lalu tentukan hubungan setelah fakta. Teknik yang dijelaskan di atas tampaknya lebih mulus dan sebenarnya melibatkan sedikit perencanaan ke depan. Orang-orang yang menggunakan Option Explicit dalam kode VBA mereka pasti akan menyukai metode ini.
Hubungan dalam model data membuat Excel terasa lebih seperti Access atau SQL Server, tetapi dengan semua kebaikan Excel.
Saya suka bertanya kepada tim Excel tentang fitur favorit mereka. Setiap Rabu, saya akan membagikan salah satu jawaban mereka. Terima kasih kepada Ash Sharma karena telah menyediakan ide ini.
Pemikiran Excel Hari Ini
Saya telah meminta saran dari teman-teman Master Excel saya tentang Excel. Hari ini pemikiran untuk direnungkan:
"Jangan mencari jika Anda sedang menjalin hubungan"
John Michaloudis
