{"id":4355,"date":"2023-07-11T18:16:42","date_gmt":"2023-07-11T18:16:42","guid":{"rendered":"https:\/\/statorials.org\/id\/tabel-pivot-filter-vba\/"},"modified":"2023-07-11T18:16:42","modified_gmt":"2023-07-11T18:16:42","slug":"tabel-pivot-filter-vba","status":"publish","type":"post","link":"https:\/\/statorials.org\/id\/tabel-pivot-filter-vba\/","title":{"rendered":"Cara memfilter pivottable menggunakan vba (dengan contoh)"},"content":{"rendered":"

<\/p>\n

\n

Anda dapat menggunakan metode berikut untuk memfilter tabel pivot di Excel menggunakan VBA:<\/span><\/p>\n

Metode 1: Filter tabel pivot berdasarkan nilai<\/strong><\/span><\/p>\n

 SubFilterPivotTable<\/span> ()\n   Dim<\/span> pf As<\/span> PivotField\n   Dim<\/span> myFilter As<\/span> String\n   Set<\/span> pf = ActiveSheet.PivotTables(\" PivotTable1<\/span> \").PivotFields(\" Position<\/span> \")\n   myFilter = ActiveWorkbook.Sheets(\" Sheet1<\/span> \").Range(\" J2<\/span> \").Value\n   pf.PivotFilters.Add2 xlCaptionEquals, , myFilter\nEnd Sub\n<\/span><\/strong><\/span><\/pre>\n
 Makro khusus ini akan memfilter tabel pivot yang disebut PivotTable1<\/strong> untuk memperlihatkan hanya baris-baris yang nilai di kolom Posisi<\/strong> tabel pivot sama dengan nilai di sel J2<\/strong> pada Sheet1<\/strong> .<\/span><\/p>\n
 Metode 2: Filter PivotTable berdasarkan beberapa nilai<\/strong><\/span><\/p>\n
 Sub<\/span> FilterPivotTableMultiple()\n  Dim<\/span> v Ace<\/span> Variant\n  Dim<\/span> i As<\/span> Integer, j As<\/span> Integer\n  Dim<\/span> pf As<\/span> PivotField\n  Set<\/span> pf = ActiveSheet.PivotTables(\" PivotTable1<\/span> \").PivotFields(\" Position<\/span> \")\n  \n  'specify range with values to filter on<\/span>\n  v = Range(\" J2:J3<\/span> \")\n  \n  'clear existing filters<\/span>\n  pf.ClearAllFilters\n  \n  'apply filter to pivot table<\/span>\n  With<\/span> pf\n    For<\/span> i = 1 TB<\/span> pf.PivotItems.Count\n      j = 1\n      Do While<\/span> j <= UBound(v, 1) - LBound(v, 1) + 1\n         If<\/span> pf.PivotItems(i).Name = v(j, 1) Then\n           pf.PivotItems(pf.PivotItems(i).Name).Visible = True\n           Exit Do\n        Else<\/span>\n          pf.PivotItems(pf.PivotItems(i).Name).Visible = False\n       End If<\/span>\n        j = j + 1\n      Loop<\/span>\n    Next<\/span> i\n End With<\/span>\nEnd Sub\n<\/span><\/strong><\/span><\/pre>\n
 Makro khusus ini akan memfilter tabel pivot yang disebut PivotTable1<\/strong> untuk hanya menampilkan baris-baris yang nilai dalam kolom Posisi<\/strong> tabel pivot sama dengan salah satu nilai dalam rentang sel J2:J3<\/strong> .<\/span><\/p>\n
 Metode 3: Hapus Filter PivotTable<\/strong><\/span><\/p>\n
 SubClearPivotTableFilter<\/span> ()\n   Dim<\/span> pt As<\/span> PivotTable\n   Set<\/span> pt = ActiveSheet.PivotTables(\" PivotTable1<\/span> \")\n   pt.ClearAllFilters\nEnd Sub\n<\/span><\/strong><\/span><\/pre>\n
 Makro khusus ini akan menghapus semua filter dari tabel pivot yang disebut PivotTable1<\/strong> .<\/span><\/p>\n
 Contoh berikut menunjukkan cara menggunakan masing-masing metode ini dalam praktik.<\/span><\/p>\n
 Contoh 1: Filter tabel pivot berdasarkan nilai<\/strong><\/span><\/h2>\n
 Katakanlah kita membuat tabel pivot dari kumpulan data di Excel untuk merangkum poin yang dicetak oleh pemain bola basket dari tim dan posisi berbeda:<\/span> <\/p>\n
\"\"<\/p>\n
 Katakanlah kita ingin memfilter tabel pivot untuk hanya menampilkan baris yang nilainya di kolom Posisi adalah Penjaga.<\/span><\/p>\n
 Kita dapat membuat makro berikut untuk melakukan ini:<\/span><\/p>\n
 SubFilterPivotTable<\/span> ()\n   Dim<\/span> pf As<\/span> PivotField\n   Dim<\/span> myFilter As<\/span> String\n   Set<\/span> pf = ActiveSheet.PivotTables(\" PivotTable1<\/span> \").PivotFields(\" Position<\/span> \")\n   myFilter = ActiveWorkbook.Sheets(\" Sheet1<\/span> \").Range(\" J2<\/span> \").Value\n   pf.PivotFilters.Add2 xlCaptionEquals, , myFilter\nEnd Sub<\/span><\/strong><\/span><\/pre>\n
 Saat kita menjalankan makro ini, tabel pivot secara otomatis difilter untuk memperlihatkan hanya baris yang nilainya di kolom Posisi adalah Penjaga:<\/span> <\/p>\n
\"Tabel<\/p>\n
 Tabel pivot telah difilter untuk hanya menampilkan baris yang nilainya di kolom Posisi adalah Penjaga.<\/span><\/p>\n
 Contoh 2: Filter tabel pivot berdasarkan beberapa nilai<\/strong><\/span><\/h2>\n
 Misalkan kita ingin memfilter tabel pivot agar hanya menampilkan baris yang nilainya di kolom Posisi adalah Penjaga atau<\/em> Tengah.<\/span><\/p>\n
 Kita dapat membuat makro berikut untuk melakukan ini:<\/span><\/p>\n
 Sub<\/span> FilterPivotTableMultiple()\n  Dim<\/span> v Ace<\/span> Variant\n  Dim<\/span> i As<\/span> Integer, j As<\/span> Integer\n  Dim<\/span> pf As<\/span> PivotField\n  Set<\/span> pf = ActiveSheet.PivotTables(\" PivotTable1<\/span> \").PivotFields(\" Position<\/span> \")\n  \n  'specify range with values to filter on<\/span>\n  v = Range(\" J2:J3<\/span> \")\n  \n  'clear existing filters<\/span>\n  pf.ClearAllFilters\n  \n  'apply filter to pivot table<\/span>\n  With<\/span> pf\n    For<\/span> i = 1 TB<\/span> pf.PivotItems.Count\n      j = 1\n      Do While<\/span> j <= UBound(v, 1) - LBound(v, 1) + 1\n         If<\/span> pf.PivotItems(i).Name = v(j, 1) Then\n           pf.PivotItems(pf.PivotItems(i).Name).Visible = True\n           Exit Do\n        Else<\/span>\n          pf.PivotItems(pf.PivotItems(i).Name).Visible = False\n       End If<\/span>\n        j = j + 1\n      Loop<\/span>\n    Next<\/span> i\n End With<\/span>\nEnd Sub<\/span><\/strong><\/span><\/pre>\n
 Saat kita menjalankan makro ini, tabel pivot secara otomatis difilter untuk memperlihatkan hanya baris yang nilainya di kolom Posisi adalah Penjaga atau Tengah:<\/span> <\/p>\n
\"Tabel<\/p>\n
 Tabel pivot telah difilter untuk memperlihatkan hanya baris yang nilainya di kolom Posisi adalah Penjaga atau Tengah.<\/span><\/p>\n
 Sumber daya tambahan<\/strong><\/span><\/h2>\n
 Tutorial berikut menjelaskan cara melakukan tugas umum lainnya di VBA:<\/span><\/p>\n
 VBA: cara menyegarkan tabel pivot<\/a>
 VBA: cara menghapus nilai duplikat<\/a>
 VBA: Cara menghitung jumlah baris dalam jangkauan<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
Anda dapat menggunakan metode berikut untuk memfilter tabel pivot di Excel menggunakan VBA: Metode 1: Filter tabel pivot berdasarkan nilai SubFilterPivotTable () Dim pf As PivotField Dim myFilter As String Set pf = ActiveSheet.PivotTables(” PivotTable1 “).PivotFields(” Position “) myFilter = ActiveWorkbook.Sheets(” Sheet1 “).Range(” J2 “).Value pf.PivotFilters.Add2 xlCaptionEquals, , myFilter End Sub Makro khusus ini akan […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[11],"tags":[],"yoast_head":"\nCara Memfilter PivotTable Menggunakan VBA (dengan Contoh) - Statorial<\/title>\n<meta name=\"description\" content=\"Tutorial ini menjelaskan cara memfilter tabel pivot di Excel menggunakan VBA, dengan beberapa contoh.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/statorials.org\/id\/tabel-pivot-filter-vba\/\" \/>\n<meta property=\"og:locale\" content=\"id_ID\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Cara Memfilter PivotTable Menggunakan VBA (dengan Contoh) - Statorial\" \/>\n<meta property=\"og:description\" content=\"Tutorial ini menjelaskan cara memfilter tabel pivot di Excel menggunakan VBA, dengan beberapa contoh.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/statorials.org\/id\/tabel-pivot-filter-vba\/\" \/>\n<meta property=\"og:site_name\" content=\"Statorials\" \/>\n<meta property=\"article:published_time\" content=\"2023-07-11T18:16:42+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/statorials.org\/wp-content\/uploads\/2023\/08\/pf1.jpg\" \/>\n<meta name=\"author\" content=\"Benjamin anderson\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Ditulis oleh\" \/>\n\t<meta name=\"twitter:data1\" content=\"Benjamin anderson\" \/>\n\t<meta name=\"twitter:label2\" content=\"Estimasi waktu membaca\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 menit\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/statorials.org\/id\/tabel-pivot-filter-vba\/\",\"url\":\"https:\/\/statorials.org\/id\/tabel-pivot-filter-vba\/\",\"name\":\"Cara Memfilter PivotTable Menggunakan VBA (dengan Contoh) - Statorial\",\"isPartOf\":{\"@id\":\"https:\/\/statorials.org\/id\/#website\"},\"datePublished\":\"2023-07-11T18:16:42+00:00\",\"dateModified\":\"2023-07-11T18:16:42+00:00\",\"author\":{\"@id\":\"https:\/\/statorials.org\/id\/#\/schema\/person\/3d17a1160dd2d052b7c78e502cb9ec81\"},\"description\":\"Tutorial ini menjelaskan cara memfilter tabel pivot di Excel menggunakan VBA, dengan beberapa contoh.\",\"breadcrumb\":{\"@id\":\"https:\/\/statorials.org\/id\/tabel-pivot-filter-vba\/#breadcrumb\"},\"inLanguage\":\"id\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/statorials.org\/id\/tabel-pivot-filter-vba\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/statorials.org\/id\/tabel-pivot-filter-vba\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/statorials.org\/id\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Cara memfilter pivottable menggunakan vba (dengan contoh)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/statorials.org\/id\/#website\",\"url\":\"https:\/\/statorials.org\/id\/\",\"name\":\"Statorials\",\"description\":\"Panduan anda untuk kompetensi statistik!\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/statorials.org\/id\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"id\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/statorials.org\/id\/#\/schema\/person\/3d17a1160dd2d052b7c78e502cb9ec81\",\"name\":\"Benjamin anderson\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"id\",\"@id\":\"https:\/\/statorials.org\/id\/#\/schema\/person\/image\/\",\"url\":\"http:\/\/statorials.org\/id\/wp-content\/uploads\/2023\/10\/Dr.-Benjamin-Anderson-96x96.jpg\",\"contentUrl\":\"http:\/\/statorials.org\/id\/wp-content\/uploads\/2023\/10\/Dr.-Benjamin-Anderson-96x96.jpg\",\"caption\":\"Benjamin anderson\"},\"description\":\"Halo, saya Benjamin, pensiunan profesor statistika yang menjadi guru Statorial yang berdedikasi. Dengan pengalaman dan keahlian yang luas di bidang statistika, saya ingin berbagi ilmu untuk memberdayakan mahasiswa melalui Statorials. Baca selengkapnya\",\"sameAs\":[\"http:\/\/statorials.org\/id\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Cara Memfilter PivotTable Menggunakan VBA (dengan Contoh) - Statorial","description":"Tutorial ini menjelaskan cara memfilter tabel pivot di Excel menggunakan VBA, dengan beberapa contoh.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/statorials.org\/id\/tabel-pivot-filter-vba\/","og_locale":"id_ID","og_type":"article","og_title":"Cara Memfilter PivotTable Menggunakan VBA (dengan Contoh) - Statorial","og_description":"Tutorial ini menjelaskan cara memfilter tabel pivot di Excel menggunakan VBA, dengan beberapa contoh.","og_url":"https:\/\/statorials.org\/id\/tabel-pivot-filter-vba\/","og_site_name":"Statorials","article_published_time":"2023-07-11T18:16:42+00:00","og_image":[{"url":"https:\/\/statorials.org\/wp-content\/uploads\/2023\/08\/pf1.jpg"}],"author":"Benjamin anderson","twitter_card":"summary_large_image","twitter_misc":{"Ditulis oleh":"Benjamin anderson","Estimasi waktu membaca":"3 menit"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/statorials.org\/id\/tabel-pivot-filter-vba\/","url":"https:\/\/statorials.org\/id\/tabel-pivot-filter-vba\/","name":"Cara Memfilter PivotTable Menggunakan VBA (dengan Contoh) - Statorial","isPartOf":{"@id":"https:\/\/statorials.org\/id\/#website"},"datePublished":"2023-07-11T18:16:42+00:00","dateModified":"2023-07-11T18:16:42+00:00","author":{"@id":"https:\/\/statorials.org\/id\/#\/schema\/person\/3d17a1160dd2d052b7c78e502cb9ec81"},"description":"Tutorial ini menjelaskan cara memfilter tabel pivot di Excel menggunakan VBA, dengan beberapa contoh.","breadcrumb":{"@id":"https:\/\/statorials.org\/id\/tabel-pivot-filter-vba\/#breadcrumb"},"inLanguage":"id","potentialAction":[{"@type":"ReadAction","target":["https:\/\/statorials.org\/id\/tabel-pivot-filter-vba\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/statorials.org\/id\/tabel-pivot-filter-vba\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/statorials.org\/id\/"},{"@type":"ListItem","position":2,"name":"Cara memfilter pivottable menggunakan vba (dengan contoh)"}]},{"@type":"WebSite","@id":"https:\/\/statorials.org\/id\/#website","url":"https:\/\/statorials.org\/id\/","name":"Statorials","description":"Panduan anda untuk kompetensi statistik!","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/statorials.org\/id\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"id"},{"@type":"Person","@id":"https:\/\/statorials.org\/id\/#\/schema\/person\/3d17a1160dd2d052b7c78e502cb9ec81","name":"Benjamin anderson","image":{"@type":"ImageObject","inLanguage":"id","@id":"https:\/\/statorials.org\/id\/#\/schema\/person\/image\/","url":"http:\/\/statorials.org\/id\/wp-content\/uploads\/2023\/10\/Dr.-Benjamin-Anderson-96x96.jpg","contentUrl":"http:\/\/statorials.org\/id\/wp-content\/uploads\/2023\/10\/Dr.-Benjamin-Anderson-96x96.jpg","caption":"Benjamin anderson"},"description":"Halo, saya Benjamin, pensiunan profesor statistika yang menjadi guru Statorial yang berdedikasi. Dengan pengalaman dan keahlian yang luas di bidang statistika, saya ingin berbagi ilmu untuk memberdayakan mahasiswa melalui Statorials. Baca selengkapnya","sameAs":["http:\/\/statorials.org\/id"]}]}},"yoast_meta":{"yoast_wpseo_title":"","yoast_wpseo_metadesc":"","yoast_wpseo_canonical":""},"_links":{"self":[{"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/posts\/4355"}],"collection":[{"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/comments?post=4355"}],"version-history":[{"count":0,"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/posts\/4355\/revisions"}],"wp:attachment":[{"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/media?parent=4355"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/categories?post=4355"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/tags?post=4355"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}