GETPIVOTDATA, Rumus Mengambil Data Pivot Table Excel

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 file Excel online berikut untuk bahan latihan Anda.

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.

=GETPIVOTDATA(Data_Field,Pivot_Table,[Field1,Item1],[Field2,Item2],[Field3,Item3]…)

1. 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 (” “).
2. 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.
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 E₁₂) untuk ditampilkan pada Cell B₁₅. Silahkan ketik Rumus =E12 pada Cell B₁₅. Kemudian Anda bisa melihat hasil berikut:

Sejauh ini, rumus Anda tidak bermasalah, namun coba Anda Filter pada bagian Rows atau Columns seperti berikut:

1. 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.
2. Klik Ok untuk melanjutkan. Kemudian coba lihat hasil berikut:

Sudah sangat jelas, Rumus =E₁₂ menjadi nol karena laporan pivot table sudah berubah. Nilai Grand Total saat ini (setelah filter) terdapat pada Cell E₁₁.

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:

1. Hilangkan semua filter pada Rows yang sebelumnya kita lakukan.
2. Klik Cell B₁₆ kemudian ketik tanda sama dengan =
3. Klik Cell E₁₂, 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:

Tulis Rumus / Fungsi:
=GETPIVOTDATA(“Total”,“$A$₄“)

Artinya:
Ambil Total data Pivot Table pada Cell A₄

Jadi Excel akan mengambil data Grand Total pada Laporan Pivot Table Anda. Sementara Cell A₄ 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 B₁₇. Caranya silahkan ikuti tahap-tahap berikut:

1. Klik Cell B₁₅ kemudian ketik tanda sama dengan =
2. Klik data Total Pembelian Bawang Putih yaitu pada Cell C₇, 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

Tulis Rumus / Fungsi:
=GETPIVOTDATA(“Total”,“$A$₄“,“BARANG”,“Bawang Putih”,“SUPLIER”,“Toko Laris”)

Artinya:
Ambil Total data Pivot Table pada Cell A₄ Field BARANG Item Bawang Merah dan Field SUPLIER Item Toko Laris

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:

1. Buat filter kriteria menggunakan Fitur Drop-Down Data Validation Excel. Saya membuat 2 buah Filter yaitu Barang dan Suplier.
2. Pada Cell H₆ 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:

Tulis Rumus / Fungsi:
=GETPIVOTDATA(“Total”,“$A$₄“,“BARANG”,H₄,“SUPLIER”,H₅)

Artinya:
Ambil Total data Pivot Table pada Cell A₄ Field BARANG Item pada Cell H4 dan Field SUPLIER Item H₅

Jika Anda mengubah nilai pada Cell H₄ dan H₅, secara otomatis, hasil rumus GETPIVOTDATA akan berubah sesuai kriteria.

Gimana caranya ?, silahkan filter kriteria menggunakan menu drop-down yang sudah dibuat dengan cara berikut:

1. Ubah filter kriteria Barang dengan cara Klik icon Drop-Down disamping Cell H₄, kemudian ubah menjadi Jahe
2. Ubah filter kriteria Suplier menjadi Toko Laris dengan cara yang sama. Berikut hasilnya:

Penting! 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.