Tabel pivot excel vlookup kolom kalkulasi

Dalam contoh ini, Anda dapat menggunakan fungsi IFERROR dengan rumus VLOOKUP, untuk mengembalikan nol, bukan kesalahan, jika biaya tidak dapat ditemukan

• =IFERROR(VLOOKUP(D5,PriceLookup,2,FALSE),0)

Kemudian, setelah Anda memperbaiki kesalahan pada data sumber, kembali ke tabel pivot, dan segarkan. Kesalahan harus hilang dari area Nilai

Sembunyikan Kesalahan

Jika Anda tidak dapat memperbaiki data sumber, Anda dapat menyembunyikan kesalahan di tabel pivot

Di opsi tabel pivot, Anda dapat mengubah pengaturan, untuk menyembunyikan kesalahan tersebut, dan menggantinya dengan karakter spasi, atau teks lainnya

Untuk mengubah pengaturan kesalahan tabel pivot, ikuti langkah-langkah berikut

• Klik kanan sel mana pun di tabel pivot, dan klik Opsi PivotTable
• Pada tab Tata Letak & Format, buka bagian Format
• Tambahkan tanda centang ke pengaturan, "Untuk menampilkan nilai kesalahan"
• Dalam kotak, ketikkan teks yang Anda inginkan, bukan kesalahannya. Sebagai contoh
  • Ketik karakter spasi, untuk menyembunyikan nilai kesalahan
  • Atau, ketik T/A, untuk menunjukkan bahwa informasi tidak tersedia
• Klik tombol OK, untuk menutup jendela Opsi PivotTable

Setelah Anda menutup kotak dialog, kesalahan pada nilai tabel pivot akan berubah secara otomatis, untuk menampilkan teks yang Anda masukkan. Anda tidak perlu me-refresh tabel pivot

Mungkin Anda berpikir bahwa Anda cukup mengeklik dan mengetikkan rumus ke dalam Tabel Pivot?

Namun, jika Anda berpikir demikian, Anda salah. Alasannya adalah Anda perlu menggunakan apa yang dikenal sebagai Bidang Terhitung untuk menulis rumus di Tabel Pivot di Excel

Sekarang jika Anda ingin petunjuk langkah demi langkah tentang cara melakukan ini, saya sungguh-sungguh dapat merekomendasikan ini cara menambahkan bidang terhitung ke panduan Tabel Pivot

Dengan cara ini Anda akan melihat bagaimana menghitung laba dengan mengurangkan biaya dari uang yang diterima. Anda juga akan melihat bagaimana menghitung berapa banyak keuntungan yang Anda hasilkan sebagai persentase

Sekarang, tentu saja, Anda perlu membuat Tabel Pivot. Sebelum Anda melakukannya, ada baiknya juga membuat Tabel. Bahkan sebelum Anda membuat tabel, Anda perlu menyusun data Anda dengan benar

Fungsi Excel VLOOKUP dapat digunakan untuk mengambil informasi dari Tabel Pivot berdasarkan referensi sel. Fungsi VLOOKUP dirancang untuk mengambil data dalam tabel yang disusun dalam baris vertikal, sehingga nilai pencarian harus ada di kolom paling kiri dan nilai target yang akan diambil dapat berada di kolom mana pun di sebelah kanan, yang disebut “ kolom

Sintaksis

=VLOOKUP (lookup_value, table, col_index, [range_lookup])

Dalam artikel ini, misalkan kita memiliki dataset catatan penjualan beberapa merek rokok terkenal di lima wilayah berbeda, dan kita telah merangkum penjualan setiap merek di setiap wilayah dalam Tabel Pivot seperti yang ditunjukkan di bawah ini

Sekarang, misalkan kita ingin mengambil angka penjualan yang diringkas dari merek tertentu (nilai pencarian) untuk wilayah tertentu (col_index) dari Tabel Pivot ini menggunakan fungsi VLOOKUP

Di sini kita perlu menyediakan referensi sel dari lookup-value, pivot table, col_index, dan range_lookup untuk mendapatkan informasi yang dihasilkan

=VLOOKUP (H17, B3:H14, 6, FALSE)_

Seperti yang Anda lihat, kami telah menggunakan referensi sel berikut dalam rumus ini

Nilai pencarian– Nilai Sel H17 (NEWPORT) untuk dicari di kolom pertama tabel pivot

Tabel – Referensi rentang sel Tabel Pivot B3. H14 sebagai tabel untuk mengambil nilai

col_index – Wilayah BARAT adalah kolom ke-6 dalam tabel untuk mengambil nilai

range_lookup – [opsional]

• BENAR = perkiraan kecocokan (default)

• SALAH = sama persis

Jika Anda menggunakan fungsi VLOOKUP di lembar kerja lain untuk mengambil data dari lembar Tabel Pivot, maka rumus ini akan memasukkan nama lembar Tabel Pivot sebagai referensi tabel, seperti;

=VLOOKUP (H17, Pivot_Table!B3:H14, 6, FALSE)

Jika Anda ingin memilih nomor col_index secara otomatis sehubungan dengan referensi sel wilayah, maka kita dapat melakukannya dengan memperkenalkan fungsi MATCH dalam fungsi VLOOKUP, seperti;

=VLOOKUP (H17,B3:H14,MATCH(H18,B4:H4,0),FALSE)

Sekarang fungsi MATCH akan mencari nilai referensi sel wilayah, seperti yang diberikan di sel H18, di rentang tajuk kolom Tabel Pivot, B8. H4, dan akan mengembalikan nomor indeks kolom di mana akan ada kecocokan. Seperti, pada contoh di atas, wilayah "Barat" dicocokkan di sel G4, sehingga akan mengembalikan nomor indeks kolom sebagai 6, karena kolom G adalah kolom ke-6 dalam rentang Tabel Pivot

Fungsi GETPIVOTDATA pada Tabel Pivot

Fungsi Excel GETPIVOTDATA mengambil data dari Tabel Pivot berdasarkan struktur Tabel Pivot, bukan referensi sel. Ini adalah fungsi yang lebih fleksibel dan dinamis untuk mendapatkan hasil. Anda tidak perlu mengingat sintaks dan dihasilkan secara otomatis saat Anda mengetikkan tanda sama dengan “=” di mana saja di lembar kerja atau buku kerja dan mengklik sel nilai sebagai referensi di Tabel Pivot seperti Anda memasukkan ini di sel H19

= click on cell F8 in Pivot Table and press ENTER_

Ini akan secara otomatis menghasilkan fungsi GETPIVOTDATA sehubungan dengan nilai sel F8, seperti ini

=GETPIVOTDATA("Sales",$B$3,"Region","East","Brand","NEWPORT")

Namun, Anda dapat menonaktifkan Hasilkan GetPivotData di menu di Alat Tabel Pivot > Opsi dan dapat membuat rumus GETPIVOTDATA dengan mengikuti sintaksnya sesuai kebutuhan Anda sendiri

Sintaksis

=GETPIVOTDATA (data_field, pivot_table, [field1, item1], [field2, item2], .... )_

Di mana,

data_field – Nama kolom nilai yang akan dikueri

pivot_table – Referensi ke sel tempat tabel pivot dimulai

kolom1, item1 – [opsional] – Pasangan kolom/item

Pasangan bidang/item opsional ini digunakan untuk membatasi pengambilan data seperti menerapkan filter berdasarkan struktur tabel pivot. Anda dapat membatasi hasil Anda dengan memperkenalkan beberapa pasangan bidang/item sesuai struktur tabel pivot

Singkatnya, dalam rumus GetPivotData Anda merujuk ke tabel pivot, dan bidang serta item yang Anda inginkan datanya. Dalam contoh ini, Anda merujuk ke sel tabel pivot $B$3, dan menginginkan data penjualan ( bidang_data) dari dua pasangan bidang/item, seperti Wilayah/Barat dan Merek/Unta

Dalam formula, data_field harus diberikan dalam tanda kutip ganda, dan nama pasangan kolom/item dapat dalam tanda kutip ganda atau Anda dapat memberikan referensi sel, seperti berikut;

=GETPIVOTDATA("Sales",$B$3,”Region”,”East”,”Brand”,”Camel”)
OR
=GETPIVOTDATA("Sales",$B$3,G16,H16,G17,H17)

Anda juga dapat memberikan data_field sebagai referensi sel, tetapi Anda harus menggabungkannya dengan tanda kutip ganda di awal atau akhir referensi sel, jika tidak, Anda akan mendapatkan kesalahan dalam rumus, seperti ini;

=GETPIVOTDATA(G18&"",$B$3,G16,H16,G17,H17)_

VLOOKUP vs. fungsi GETPIVOTDATA

Berikut adalah beberapa poin yang membedakan kedua fungsi satu sama lain

• Fungsi VLOOKUP tidak sefleksibel fungsi GETPIVOTDATA
• Fungsi VLOOKUP bergantung pada referensi rentang tabel yang disediakan dalam rumus. Tidak efektif jika rentang tabel Pivot diperluas. Sedangkan GETPIVOTDATA didasarkan pada struktur tabel pivot, sehingga tetap efektif meskipun tabel pivot mengembang atau menciut
• Fungsi GETPIVOTDATA bersifat dinamis dan Anda dapat mengekstrak data dari beberapa tabel pivot yang mengikuti struktur tabel pivot yang sama menggunakan rumus tunggal. Sedangkan fungsi VLOOKUP tidak dinamis
• Dalam fungsi GETPIVOTDATA Anda dapat memfilter hasil Anda dengan memperkenalkan beberapa pasangan bidang/item, tetapi tidak mungkin dengan fungsi VLOOKUP

Jika Anda belum menemukan jawabannya di artikel ini, pakar Excel kami tersedia untuk membantu Anda menghemat berjam-jam dalam mencari informasi. Gunakan tautan ini untuk mengetik pertanyaan Anda dan terhubung dengan pakar Excel yang memenuhi syarat dalam beberapa detik dan mereka akan menyelesaikan masalah Anda di tempat secara langsung, 1. 1 sesi obrolan

Apakah Anda masih mencari bantuan dengan fungsi VLOOKUP?

Bisakah Anda melakukan VLOOKUP dalam bidang terhitung dari PivotTable?

Mengapa VLOOKUP tidak berfungsi di PivotTable?

Bagaimana cara mereferensikan sel dalam bidang terhitung di PivotTable?

Bisakah Anda menggunakan bidang terhitung di PivotTable?