VLOOKUP vs Tabel Pivot: Kupas Tuntas!

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.

meja penjualan

Dan tabel lain bernama “Produk” dengan detail produk.

meja 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.

fungsi pencarian v

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)

rumus VLOOKUP

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.

Baca Juga:  Pengenalan HLOOKUP vs VLOOKUP Yang Perlu Diketahui

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.

masukkan tabel pivot

Tabel “Penjualan” diambil sebagai sumber data yang akan digunakan. Dan opsi default adalah menyisipkan Tabel Pivot pada lembar kerja baru.

masukkan 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.

bidang tabel pivot

Tabel Pivot menunjukkan total penjualan untuk setiap kategori produk.

total penjualan untuk setiap kategori produk

Untuk memformat nilai dengan benar. Klik kanan nilai Tabel Pivot dan klik Format Angka.

format nomor tabel pivot

Pilih format yang ingin Anda gunakan. Dalam contoh ini, saya telah memilih Akuntansi dengan tempat desimal.

format nomor akuntansi

Tabel Pivot sekarang diformat dengan benar.

format tabel pivot

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.

Baca Juga:  VLOOKUP vs INDEX MATCH: Kupas Tuntas!

data dari tabel atau 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.

data tutup dan muat ke

Pilih opsi untuk Hanya buat koneksi dan centang kotak untuk Tambahkan data ini ke Model Data.

tambahkan data ke model

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.

pertanyaan 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.

mengelola 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.

tampilan diagram

Klik dan seret dari bidang “ID Kategori” di “Penjualan” ke bidang “ID” di “Produk”

klik dan seret bidang ID kategori

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.

hubungan satu ke banyak

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.

masukkan tabel pivot

Pastikan bahwa Gunakan Model Data buku kerja ini opsi dipilih. Klik Lembar Kerja Baru sebagai lokasi Pivot Table.

masukkan tabel pivot pada lembar kerja baru

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.

bidang tabel pivot

Seret bidang “Kategori” dari tabel “Produk” ke dalam Baris. Dan kemudian seret bidang “Total” dari tabel “Penjualan” ke “Nilai”

Baca Juga:  Cara Menggunakan FREKUENSI Fungsi Excel

kolom tabel pivot total

Format nilai dan kami memiliki hasil Tabel Pivot yang sama seperti sebelumnya.

hasil tabel pivot

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.

fungsi vlookup dari data tabel pivot

Ini mengembalikan jawaban yang benar.

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.

fungsi getpivotdata

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.

Tinggalkan komentar