Sebagai pengguna Excel, Anda mungkin pernah mendengar dua fitur Excel yang paling terkenal yaitu fungsi VLOOKUP dan Tabel Pivot.
Tetapi jika Anda belum pernah menggunakannya sebelumnya, Anda mungkin bertanya-tanya Kapan Anda akan menggunakan Tabel VLOOKUP vs Tabel Pivot? Dan untuk apa Anda harus menggunakannya?
Dalam artikel ini kita akan membahas VLOOKUP, Pivot Tables dan juga Power Pivot, dan kapan Anda harus menggunakan masing-masing untuk hasil terbaik.
Vlookup vs Tabel Pivot
Apa itu VLOOKUP?
VLOOKUP adalah fungsi pencarian dan referensi di Excel.
Ini biasanya digunakan dalam lembar kerja untuk mencari dan menarik data dari tabel atau lembar kerja Excel lain.
Misalnya, kami memiliki tabel Excel bernama “Penjualan” yang berisi rincian penjualan produk untuk semua bulan dalam setahun.
Dan tabel lain bernama “Produk” dengan detail produk.
Kami ingin membuat PivotTable yang menunjukkan total penjualan menurut kategori produk yang berbeda.
Namun tabel “Penjualan” tidak memiliki rincian tentang kategori produk. Informasi tersebut disimpan dalam tabel “Produk”.
Kita dapat menggunakan fungsi VLOOKUP untuk membawa informasi kategori ke dalam tabel “penjualan”.
Cara menggunakan fungsi VLOOKUP
Fungsi VLOOKUP memiliki empat argumen (informasi yang dibutuhkan).
Mereka adalah nilai pencarian, array tabel, jumlah indeks col dan pencarian rentang.
Nilai lookup adalah nilai yang Anda cari. Ini adalah ID kategori dalam contoh kita.
Array Tabel adalah tabel yang kita butuhkan untuk mencari informasi ini. Ini akan menjadi tabel “Produk”.
Nomor Indeks Kol adalah nomor kolom dari tabel yang berisi informasi yang akan dikembalikan. Ini akan menjadi kolom yang berisi kategori, yang merupakan kolom kedua. Jadi kita akan menggunakan 2.
Pencarian Rentang adalah jenis pencarian yang Anda lakukan. Apakah Anda mencari dalam rentang? Dalam contoh kami, kami tidak, karena kami mencari ID kategori tertentu.
Rumus berikut ditambahkan ke tabel “Penjualan” di kolom F.
=VLOOKUP([@[Category ID]],Produk,2,SALAH)
VLOOKUP adalah fungsi yang sangat berguna di Excel yang dapat digunakan dengan banyak cara cerdas lainnya seperti membandingkan daftar atau nilai pengujian.
Selain VLOOKUP, rumus INDEX dan MATCH juga sangat berguna untuk mencari data dari tabel Excel lainnya.
Apa itu Tabel Pivot?
Tabel Pivot adalah alat pelaporan di Excel yang merangkum data dan melakukan agregasi pada nilai.
Misalnya, untuk menunjukkan total penjualan berdasarkan bulan atau jumlah pesanan untuk setiap produk.
Ini sangat kuat dan membuat pembuatan laporan menjadi cepat dan sederhana.
Cara membuat Tabel Pivot
Dengan kolom kategori sekarang di tabel “Penjualan”, kita dapat membuat Tabel Pivot untuk menampilkan total penjualan untuk setiap kategori produk.
Klik pada tabel “Penjualan”, lalu klik Memasukkan > Tabel pivot.
Tabel “Penjualan” diambil sebagai sumber data yang akan digunakan. Dan opsi default adalah menyisipkan Tabel Pivot pada lembar kerja baru.
Lembar kerja baru dimasukkan dan PivotTable ditempatkan di atasnya. Daftar bidang ditampilkan di sebelah kanan dengan semua kolom dari tabel “Penjualan”.
Klik dan Seret bidang “Kategori” ke dalam Baris area Tabel Pivot, dan bidang “Total” ke dalam Nilai daerah.
Tabel Pivot menunjukkan total penjualan untuk setiap kategori produk.
Untuk memformat nilai dengan benar. Klik kanan nilai Tabel Pivot dan klik Format Angka.
Pilih format yang ingin Anda gunakan. Dalam contoh ini, saya telah memilih Akuntansi dengan tempat desimal.
Tabel Pivot sekarang diformat dengan benar.
Tabel Pivot adalah alat Excel yang kuat. Lihat beberapa teknik Tabel Pivot tingkat lanjut.
Apa itu PowerPivot?
Power Pivot juga disebut sebagai model data di Excel.
Secara sederhana, ini memungkinkan kita untuk membuat Tabel Pivot dari beberapa tabel, yang disebut sebagai model data.
Power Pivot adalah fitur lanjutan termasuk bahasa rumusnya sendiri yang disebut DAX. Anda dapat mempelajari lebih lanjut tentangnya dalam panduan komprehensif Power Pivot ini.
Namun, menjaga hal-hal sederhana, itu juga dapat digunakan sebagai alternatif untuk VLOOKUP.
Alih-alih menggunakan rumus pencarian untuk menggabungkan data dari beberapa tabel menjadi satu, Anda bisa menyimpannya di tabel mereka sendiri dan menggunakan Power Pivot untuk menghubungkannya.
Anda kemudian dapat membuat Tabel Pivot dari semua tabel terkait (model data).
Muat tabel ke Power Pivot
Pertama, Anda perlu memuat tabel ke dalam model data.
Klik di tabel “Penjualan” dan klik Data > Dari Tabel/Rentang.
Ini akan membuka jendela Power Query Editor.
Power Query adalah alat untuk membuat transformasi hebat pada data Anda agar siap dianalisis. Sangat bagus untuk menyiapkan data untuk Power Pivot.
Data ini bersih dan tidak memerlukan transformasi, sehingga dapat dimuat langsung ke model data.
Klik Rumah > Tutup & Muat > Tutup & Muat Ke.
Pilih opsi untuk Hanya buat koneksi dan centang kotak untuk Tambahkan data ini ke Model Data.
Tabel dimuat ke dalam model. Ini akan ditampilkan di jendela Query and Connections.
Ulangi langkah-langkah ini untuk tabel “Produk”.
Kedua tabel dimuat ke dalam model data dan terlihat di panel Kueri dan Koneksi.
Buat hubungan antar tabel dalam model
Sekarang untuk membuat hubungan antara dua tabel, kita perlu membuka jendela Power Pivot.
Klik Data > Kelola Model Data.
Jendela Power Pivot untuk Excel terbuka dan membawa Anda ke tampilan Data.
Itu terlihat seperti buku kerja Excel (tapi tidak). Dua tab di bagian bawah layar adalah dua tabel yang dimuat ke dalam model data.
Klik Tampilan Diagram tombol.
Klik dan seret dari bidang “ID Kategori” di “Penjualan” ke bidang “ID” di “Produk”
Saat Anda melepaskan mouse, hubungan dibuat.
Sebuah garis ditarik di antara dua tabel dengan angka 1 di sisi “Produk” dan simbol tak terhingga di sisi “Penjualan”.
Hal ini menunjukkan hubungan satu-ke-banyak karena suatu produk dapat dijual berkali-kali.
Tutup jendela Power Pivot.
Membuat Tabel Pivot dari model data Power Pivot
Sekarang tabel terkait, kita dapat membuat Tabel Pivot menggunakan keduanya.
Klik Memasukkan > Tabel pivot.
Pastikan bahwa Gunakan Model Data buku kerja ini opsi dipilih. Klik Lembar Kerja Baru sebagai lokasi Pivot Table.
Tabel Pivot dibuat dan Daftar Bidang muncul.
Daftar Bidang memperlihatkan dua tabel dalam model data, dan juga dua tabel di lembar kerja.
Kita perlu menggunakan keduanya dalam model data. Ini diidentifikasi oleh ikon yang berbeda di sebelah nama mereka.
Seret bidang “Kategori” dari tabel “Produk” ke dalam Baris. Dan kemudian seret bidang “Total” dari tabel “Penjualan” ke “Nilai”
Format nilai dan kami memiliki hasil Tabel Pivot yang sama seperti sebelumnya.
Kali ini tabel disimpan terpisah dan terkait menggunakan model data.
Ini lebih efisien daripada menulis beberapa fungsi VLOOKUP untuk membawa data dari tabel yang berbeda menjadi satu. Terutama saat Anda bekerja dengan kumpulan data besar dan banyak tabel.
VLOOKUP untuk menarik data dari Tabel Pivot
Jadi VLOOKUP biasanya digunakan untuk mengkonsolidasikan data yang siap untuk Tabel Pivot, tetapi dapat digunakan untuk mengembalikan nilai dari Tabel Pivot.
Ya. Di bawah ini adalah contoh fungsi VLOOKUP yang digunakan untuk mengembalikan total penjualan makanan dari PivotTable yang kita buat.
Ini mengembalikan jawaban yang benar.
Namun, VLOOKUP menggunakan referensi ke rentang sel A4:B6. Dan meskipun ini berhasil, jika PivotTable diubah, VLOOKUP akan rusak.
Itu tidak benar-benar menarik data dari Tabel Pivot, itu menariknya dari rentang sel.
VLOOKUP vs GETPIVOTDATA
Jadi ini bisa menghasilkan masalah. Itu tergantung untuk apa Pivot Table akan digunakan dan bagaimana.
Tabel Pivot adalah alat yang dinamis, tetapi VLOOKUP tidak.
Jadi pendekatan yang lebih baik mungkin menggunakan fungsi pencarian Tabel Pivot bawaan yang disebut GETPIVOTDATA.
Untuk menggunakan fungsi ini, ketik = lalu klik sel di Tabel Pivot.
Fungsi GETPIVOTDATA dibuat secara otomatis setiap kali Anda mengklik sel di Tabel Pivot dari rumus.
Contoh ini menggunakan Tabel Pivot yang dibuat dari model data.
Fungsi GETPIVOTDATA mencari nilai di kolom “Jumlah Total” dan untuk kategori makanan.
Jadi jika Tabel Pivot bertambah besar, GETPIVOTDATA akan berhasil mengambil nilainya. Namun VLOOKUP masih akan terlihat di kisaran A4:B6 yang tidak akan benar.
Bungkus
VLOOKUP dan Pivot Table adalah dua fitur yang saling melengkapi. Jadi ini lebih merupakan kasus bagaimana menggunakannya bersama-sama, daripada mengadu VLOOKUP vs Pivot Table.
Anda biasanya menerima tabel dari sistem dan orang yang berbeda. Jadi VLOOKUP dapat membantu menyiapkan data untuk Pivot Table untuk kemudian melakukan analisis dan laporan darinya.
Power Pivot menawarkan pendekatan alternatif untuk ini dengan menghubungkan tabel yang berbeda untuk kemudian membuat Tabel Pivot.