Anda disini: M Jurnal » Analisis Data Excel » GETPIVOTDATA, Rumus Mengambil Data Pivot Table Excel

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 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 🙂 ).

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:

Pivot Table Excel

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:

Rumus mengambil data pivot table

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

Filter Pivot Table
  1. 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.
  2. Kedua, Klik Ok untuk melanjutkan. Kemudian coba lihat hasil berikut:
Kesalahan Rumus Excel

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:

Kesalahan Rumus Pivot Table Excel
  1. Pertama, hilangkan semua filter pada Rows yang sebelumnya kita lakukan.
  2. Kedua, Klik Cell B16 kemudian ketik tanda sama dengan =
  3. Ketiga, klik Cell E12, maka Secara otomatis, Excel akan memasukkan Fungsi GETPIVOTDATA pada rumus Anda. Berikut hasilnya:
Rumus / Fungsi GETPIVOTDATA - Cara mengambil data pivot table tanpa kesalahan

Kemudian, coba Anda filter Bawang Putih seperti cara sebelumnya. Coba lihat, hasilnya akan mengikuti perubahan laporan pivot table seperti gambar berikut:

Rumus / Fungsi GETPIVOTDATA - Cara mengambil data pivot table tanpa kesalahan

Sedikit saya jelaskan tentang Fungsi GETPIVOTDATA tersebut:

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

Artinya:
Ambil
Total data Pivot Table pada Cell A4

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:

Rumus / Fungsi GETPIVOTDATA - Cara mengambil data pivot table tanpa kesalahan

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:

Rumus / Fungsi GETPIVOTDATA - Cara mengambil data pivot table tanpa kesalahan
  1. Pertama, Klik Cell B15 kemudian ketik tanda sama dengan =
  2. Kedua, klik data Total Pembelian Bawang Putih yaitu pada Cell C7, maka Secara otomatis, Excel akan memasukkan Fungsi GETPIVOTDATA pada rumus Anda. Berikut hasilnya:
Rumus / Fungsi GETPIVOTDATA - Cara mengambil data pivot table tanpa kesalahan

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$4,“BARANG”,“Bawang Putih”,“SUPLIER”,“Toko Laris”)

Artinya:
Ambil
Total data Pivot Table pada Cell A4 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:

Rumus / Fungsi GETPIVOTDATA - Cara mengambil data pivot table tanpa kesalahan
  1. Pertama, Buat filter kriteria menggunakan Fitur Drop-Down Data Validation Excel. Saya membuat 2 buah Filter yaitu Barang dan Suplier.
  2. Kedua, pada Cell H6 silahkan ketik Fungsi berikut: =GETPIVOTDATA(“TOTAL”,$A$4,”BARANG”,H4,”SUPLIER”,H5). Diketik saja secara manual. Hasilnya sebagai berikut:
Rumus / Fungsi GETPIVOTDATA - Cara mengambil data pivot table tanpa kesalahan

Penjelasan mengenai Fungsi yang Saya gunakan:

Tulis Rumus / Fungsi:
=
GETPIVOTDATA(“Total”,“$A$4,“BARANG”,H4,“SUPLIER”,H5)

Artinya:
Ambil
Total data Pivot Table pada Cell A4 Field BARANG Item pada Cell H4 dan Field SUPLIER Item H5

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:

GETPIVOTDATA untuk membuat laporan pivottable menarik
  1. Pertama, Ubah filter kriteria Barang dengan cara Klik icon Drop-Down disamping Cell H4, kemudian ubah menjadi Jahe
  2. 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

Rumus / Fungsi GETPIVOTDATA - Cara mengambil data pivot table tanpa kesalahan

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.

Pintasan Panduan Pivot Table Excel

SUB-BAB Sebelumnya: Chart / Grafik.

  1. Pivot Table: Cara Insert Pivot Table untuk membuat laporan.
  2. Group Pivot Table: Mengelompokkan Data Pivot Table berdasarkan Tanggal, Produk, Kategori, dll.
  3. Pivot Table Multi-Level: Membuat Laporan Pivot Table Multi Rows, Multi Columns, Multi Values, Multi Filters dll
  4. Frequency Distribution: Membuat Distribusi Frekuensi (seperti histogram) menggunakan Pivot Tabel.
  5. Pivot Chart: Belajar cara membuat Pivot Chart kedalam Laporan Pivot Tabel.
  6. Slicers: Cara Menggunakan Slicers untuk Memfilter Data Pivot Table dan Membuat Laporan yang Lebih menarik.
  7. Update Pivot Table: Cara Update atau Mengubah Sumber Data Pivot Table
  8. Calculated Field / ItemMengubah metode kalkulasi values, menggunakan gabungan rumus / fungsi (seperti IF dll) untuk perhitungan laporan yang lebih kompleks.
  9. GETPIVOTDATA: (Anda disini).

Komentar Paling Lambat di Balas Pukul 23:59.