Dalam penggunaan Pivot Table, Anda akan membutuhkan Fungsi GETPIVOTDATA untuk mengambil data dari pivot table.
Panduan ini akan mengajarkan Anda cara menggunakan Fungsi GETPIVOTDATA lengkap.
Agar lebih mudah dipahami, silahkan download atau gunakan file Excel online berikut untuk bahan latihan Anda.
Note:
1. Silahkan klik Icon Full Screen Pada Sudut Kanan File Excel Online di bawah untuk menggunakan fitur Excel Online.
2. Klik Icon Download (gambar selembar kertas dengan tanda panah kebawah) Di baris sudut kanan File Excel Online di bawah untuk download file.
3. Jika Link Download atau Excel Online Error, silahkan beritahu Saya melalui kolom komentar (Terima kasih 🙂 ).
Table of Contents
Pengertian Rumus GETPIVOTDATA
Fungsi GETPIVOTDATA Excel adalah rumus untuk mengambil nilai dari laporan Pivot Table yang sedang aktif. Rumus ini lebih berguna dari pada Anda menggunakan referensi biasa seperti =A2.
Dengan Fungsi GETPIVOTDATA, data yang tampil akan otomatis berubah jika ada perubahan pada laporan Pivot Table.
Aturan Penulisan Fungsi GETPIVOTDATA
Seperti rumus / fungsi Excel lainnya, GETPIVOTDATA juga memiliki aturan penulisan. Namun perbedaannya, Excel akan secara otomatis memasukkan Rumus GETPIVOTDATA ketika Anda menggunakan referensi biasa.
Tapi, sebelum Anda menggunakan Fungsi ini, pahami terlebih dahulu aturan penulisannya.
- Data_Field.
Argument ini merupakan data pada bidang Values yang akan Anda ambil nilai nya pada Pivot Table. Ketik bagian ini antara tanda kutip dua (” “). - Pivot_Table.
Argument ini merupakan lokasi Pivot Table berada yang akan Anda ambil nilai nya. Tulis Nama Cell dengan nilai Absolut ($) pada bidang ini seperti $A$3. - [Field1,Item1], [Field2,Item2] dst…
Argument ini merupakan optional jika Anda ingin mengambil data yang lebih spesisfik. Argument ini bisa juga disebut Kriteria pada rumus GETPIVOTDATA.
Field1, Field2 dst. merupakan nama Field pada Laporan Pivot Table. Item1, Item2 dst merupakan lokasi cell data yang lebih spesifik.
Biar lebih paham, silahkan simak contoh-contoh kasus berikut:
Contoh Menggunakan Fungsi GETPIVOTDATA
Pertama-tama, Saya berikan contoh menggunakan rumus referensi biasa. Bagian ini penting, agar Anda tahu kenapa Rumus GETPIVOTDATA lebih baik ketimbang referensi biasa.
Sebagai contoh, Saya memasukkan Field Bulan ke Bidang Filters, Field Suplier ke Bidang Columns, Field Barang ke Bidang Rows, dan Field Total ke Bidang Values. Penampakannya seperti berikut:
Sebagai contoh dengan menggunakan referensi biasa, Saya ingin mengambil nilai Grand Total (Cell E12) untuk ditampilkan pada Cell B15. Silahkan ketik Rumus =E12 pada Cell B15. Kemudian Anda bisa melihat hasil berikut:
Sejauh ini, rumus Anda tidak bermasalah, namun coba Anda Filter pada bagian Rows atau Columns seperti berikut:
- Pertama, klik Icon Filter pada Rows, kemudian hilangkan centang pada Bawang Putih. Jika Anda belum tahu Cara Filter Pivot Table, silahkan kembali ke SUB-BAB Pivot Table Multi-Level.
- Kedua, Klik Ok untuk melanjutkan. Kemudian coba lihat hasil berikut:
Sudah sangat jelas, Rumus =E12 menjadi nol karena laporan pivot table sudah berubah. Nilai Grand Total saat ini (setelah filter) terdapat pada Cell E11. Sudah jelas sangat tidak mungkin Anda akan merubah rumus setiap merubah Laporan Pivot Table.
Selanjutnya, mari selesaikan masalah ini dengan contoh-contoh berikut:
Contoh Mengambil Nilai Grand Total (tanpa [Field1,Field2] dst)
Untuk mengambil Nilai Grand Total menggunakan GETPIVOTDATA, silahkan ikuti tahap-tahap berikut:
- Pertama, hilangkan semua filter pada Rows yang sebelumnya kita lakukan.
- Kedua, Klik Cell B16 kemudian ketik tanda sama dengan =
- Ketiga, klik Cell E12, maka Secara otomatis, Excel akan memasukkan Fungsi GETPIVOTDATA pada rumus Anda. Berikut hasilnya:
Kemudian, coba Anda filter Bawang Putih seperti cara sebelumnya. Coba lihat, hasilnya akan mengikuti perubahan laporan pivot table seperti gambar berikut:
Sedikit saya jelaskan tentang Fungsi GETPIVOTDATA tersebut:
Jadi Excel akan mengambil data Grand Total pada Laporan Pivot Table Anda. Sementara Cell A4 merupakan lokasi header dari Laporan Pivot Table Anda berada. “Field Filter tidak termasuk header”.
Note: Rumus GETPIVOTDATA hanya berlaku jika data tampil pada laporan Pivot Table Anda. Jika data sudah tidak ada pada Laporan Pivot Table, Excel akan memberikan hasil nol (0) atau #Ref
Sebagai contoh, coba Anda ubah Field Total menjadi Field Jumlah. Berikut hasilnya:
Inilah maksud Rumus GETPIVOTDATA hanya akan mengambil data pada Laporan Pivot Table yang sedang aktif (tampil).
Contoh Mengambil Nilai Total (dengan [Field1,Item1] dst)
Silahkan kembalikan Field Bulan ke Bidang Filters, Field Suplier ke Bidang Column, Field Barang ke Bidang Rows, dan Field Total ke Bidang Values.
Pada contoh ini, Saya ingin mengambil data yang lebih spesifik. Maka kita akan menggunakan argument [Field1,Item1] dst..
Sebagai contoh, Saya ingin mengambil nilai Total Pembelian Bawang Putih Pada Toko Laris kemudian menampilkannya pada Cell B17. Caranya silahkan ikuti tahap-tahap berikut:
- Pertama, Klik Cell B15 kemudian ketik tanda sama dengan =
- Kedua, klik data Total Pembelian Bawang Putih yaitu pada Cell C7, maka Secara otomatis, Excel akan memasukkan Fungsi GETPIVOTDATA pada rumus Anda. Berikut hasilnya:
Nice, Anda sudah dapat data Total Pembelian Bawang Putih ke Toko Laris yaitu sebesar Rp 14.990.528. Berikut penjelasan mengenai Fungsi GETPIVOTDATA tersebut
Rumus ini akan memfilter data berdasarkan kriteria yang sudah ditentukan yaitu Value: Total, Barang: Bawang Merah, dan Suplier: Toko Laris. Beginilah cara kerja Rumus GETPIVOTDATA.
Contoh Mengambil Nilai Total Dengan Dynamic Range
Dalam contoh ini, Saya akan menunjukkan cara mengambil nilai total item menggunakan Dynamic Range menggunakan dropdown list.
Penting! Jika Anda belum tahu apa itu Range, silahkan kunjungi Panduan Range, Cell, Column dan Row M Jurnal
Dengan cara ini, Anda bisa membuat laporan Pivot Tabel yang lebih menarik. Kelebihannya, Anda tidak perlu memasukkan rumus berkali-kali. Cukup mengganti kriteria filter menggunakan menu drop-down. Silahkan ikuti tahap-tahap berikut:
- Pertama, Buat filter kriteria menggunakan Fitur Drop-Down Data Validation Excel. Saya membuat 2 buah Filter yaitu Barang dan Suplier.
- Kedua, pada Cell H6 silahkan ketik Fungsi berikut: =GETPIVOTDATA(“TOTAL”,$A$4,”BARANG”,H4,”SUPLIER”,H5). Diketik saja secara manual. Hasilnya sebagai berikut:
Penjelasan mengenai Fungsi yang Saya gunakan:
Jika Anda mengubah nilai pada Cell H4 dan H5, secara otomatis, hasil rumus GETPIVOTDATA akan berubah sesuai kriteria.
Gimana caranya ?, silahkan filter kriteria menggunakan menu drop-down yang sudah dibuat dengan cara berikut:
- Pertama, Ubah filter kriteria Barang dengan cara Klik icon Drop-Down disamping Cell H4, kemudian ubah menjadi Jahe
- Kedua, ubah filter kriteria Suplier menjadi Toko Laris dengan cara yang sama. Berikut hasilnya:
Note: Jika ada belum tahu cara membuat Drop-Down List, silahkan kunjungi Panduan Cara Membuat Drop-Down List dengan Data Validations
Nice !, Excel memberikan hasil yang tepat yaitu Total Pembelian Jahe ke Toko Laris sebesar Rp 45.106.822. Sekarang Anda bisa memfilter kriteria rumus GETPIVOTDATA dengan mudah.