{"id":4339,"date":"2023-07-11T21:24:28","date_gmt":"2023-07-11T21:24:28","guid":{"rendered":"https:\/\/statorials.org\/id\/vba-menghitung-sel-dengan-teks-tertentu\/"},"modified":"2023-07-11T21:24:28","modified_gmt":"2023-07-11T21:24:28","slug":"vba-menghitung-sel-dengan-teks-tertentu","status":"publish","type":"post","link":"https:\/\/statorials.org\/id\/vba-menghitung-sel-dengan-teks-tertentu\/","title":{"rendered":"Vba: cara menghitung sel dengan teks tertentu"},"content":{"rendered":"<p><\/p>\n<hr>\n<p><span style=\"color: #000000;\">Anda dapat menggunakan sintaks dasar berikut untuk menghitung jumlah sel dalam rentang yang berisi teks tertentu menggunakan VBA:<\/span><\/p>\n<pre style=\"background-color: #ececec; font-size: 15px;\"> <span style=\"color: #000000;\"><strong><span style=\"color: #0000ff;\">SubCountCellsWithText<\/span> ()\n    Range(\" <span style=\"color: #ff0000;\">D2<\/span> \") = WorksheetFunction.CountIf(Range(\" <span style=\"color: #ff0000;\">A2:A13<\/span> \"), \" <span style=\"color: #ff0000;\">*avs*<\/span> \")\n<span style=\"color: #0000ff;\">End Sub<\/span><\/strong><\/span><\/pre>\n<p> <span style=\"color: #000000;\">Contoh khusus ini menghitung jumlah sel dalam rentang <strong>A2:A13<\/strong> yang berisi &#8220;avs&#8221;, lalu menetapkan hasilnya ke sel <strong>D2<\/strong> .<\/span><\/p>\n<p> <span style=\"color: #000000;\"><strong>Catatan<\/strong> : Tanda bintang ( <strong>*<\/strong> ) digunakan sebagai wildcard dalam fungsi <strong>Countif<\/strong> .<\/span><\/p>\n<p> <span style=\"color: #000000;\">Jika Anda ingin menampilkan jumlah sel dalam kotak pesan, Anda bisa menggunakan sintaks berikut:<\/span><\/p>\n<pre style=\"background-color: #ececec; font-size: 15px;\"> <span style=\"color: #000000;\"><strong><span style=\"color: #0000ff;\">SubCountCellsWithText<\/span> ()\n<span style=\"color: #008000;\"><span style=\"color: #000000;\"><span style=\"color: #0000ff;\">\n    Dim<\/span> cellCount <span style=\"color: #0000ff;\">As<\/span> Integer\n    \n    <span style=\"color: #008000;\">'Calculate number of cells that contain 'avs'<\/span>\n    cellCount = WorksheetFunction.CountIf(Range(\" <span style=\"color: #ff0000;\">A2:A13<\/span> \"), \"* <span style=\"color: #ff0000;\">avs*<\/span> \")\n    \n    <span style=\"color: #008000;\">'Display the result<\/span>\n    MsgBox \"Cells that contain avs<\/span><\/span> <span style=\"color: #008000;\"><span style=\"color: #000000;\">: \" &amp; cellCount<\/span>\n<span style=\"color: #0000ff;\">End Sub<\/span><\/span><\/strong><\/span><\/pre>\n<p> <span style=\"color: #000000;\">Contoh berikut memperlihatkan cara menggunakan masing-masing metode ini dalam praktik dengan himpunan data berikut di Excel yang berisi informasi tentang berbagai pemain bola basket:<\/span> <\/p>\n<p><img decoding=\"async\" loading=\"lazy\" class=\" wp-image-34098 aligncenter\" src=\"https:\/\/statorials.org\/wp-content\/uploads\/2023\/08\/vbacontains1.jpg\" alt=\"\" width=\"477\" height=\"422\" srcset=\"\" sizes=\"\"><\/p>\n<h2> <span style=\"color: #000000;\"><strong>Contoh 1: Hitung sel dengan teks tertentu menggunakan VBA dan tampilkan hasilnya di sel<\/strong><\/span><\/h2>\n<p> <span style=\"color: #000000;\">Misalkan kita ingin menghitung jumlah sel yang mengandung &#8220;avs&#8221; di nama tim dan menampilkan hasilnya di sel tertentu.<\/span><\/p>\n<p> <span style=\"color: #000000;\">Kita dapat membuat makro berikut untuk melakukan ini:<\/span><\/p>\n<pre style=\"background-color: #ececec; font-size: 15px;\"> <span style=\"color: #000000;\"><strong><span style=\"color: #0000ff;\">SubCountCellsWithText<\/span> ()\n    Range(\" <span style=\"color: #ff0000;\">D2<\/span> \") = WorksheetFunction.CountIf(Range(\" <span style=\"color: #ff0000;\">A2:A13<\/span> \"), \" <span style=\"color: #ff0000;\">*avs*<\/span> \")\n<span style=\"color: #0000ff;\">End Sub<\/span><\/strong><\/span><\/pre>\n<p> <span style=\"color: #000000;\">Saat kami menjalankan makro ini, kami menerima keluaran berikut:<\/span> <\/p>\n<p><img decoding=\"async\" loading=\"lazy\" class=\" wp-image-34099 aligncenter\" src=\"https:\/\/statorials.org\/wp-content\/uploads\/2023\/08\/vbacontains2.jpg\" alt=\"\" width=\"489\" height=\"448\" srcset=\"\" sizes=\"\"><\/p>\n<p> <span style=\"color: #000000;\">Perhatikan bahwa sel <strong>D2<\/strong> berisi nilai <strong>5<\/strong> .<\/span><\/p>\n<p> <span style=\"color: #000000;\">Ini memberitahu kita bahwa ada 5 sel dalam rentang <strong>A2:A13<\/strong> yang berisi &#8220;avs&#8221; di nama tim.<\/span><\/p>\n<h2> <span style=\"color: #000000;\"><strong>Contoh 2: Hitung sel dengan teks tertentu menggunakan VBA dan tampilkan hasilnya di kotak pesan<\/strong><\/span><\/h2>\n<p> <span style=\"color: #000000;\">Misalkan kita ingin<\/span> <span style=\"color: #000000;\">menghitung jumlah sel yang berisi &#8220;avs&#8221; dan menampilkan hasilnya dalam kotak pesan.<\/span><\/p>\n<p> <span style=\"color: #000000;\">Kita dapat membuat makro berikut untuk melakukan ini:<\/span><\/p>\n<pre style=\"background-color: #ececec; font-size: 15px;\"> <span style=\"color: #000000;\"><strong><span style=\"color: #0000ff;\">SubCountCellsWithText<\/span> ()\n<span style=\"color: #008000;\"><span style=\"color: #0000ff;\">\n    Dim<\/span> cellCount <span style=\"color: #0000ff;\">As<\/span> Integer\n    \n    'Calculate number of cells that contain 'avs'\n<span style=\"color: #000000;\">cellCount = WorksheetFunction.CountIf(Range(\" <span style=\"color: #ff0000;\">A2:A13<\/span> \"), \" <span style=\"color: #ff0000;\">*avs*<\/span> \")<\/span>\n    \n    'Display the result\n<span style=\"color: #000000;\">MsgBox \"Cells that contain avs<\/span><\/span> <span style=\"color: #008000;\"><span style=\"color: #000000;\">: \" &amp; cellCount<\/span>\n<span style=\"color: #0000ff;\">End Sub<\/span><\/span><\/strong><\/span><\/pre>\n<p> <span style=\"color: #000000;\">Saat kami menjalankan makro ini, kami menerima keluaran berikut:<\/span> <\/p>\n<p><img decoding=\"async\" loading=\"lazy\" class=\" wp-image-34100 aligncenter\" src=\"https:\/\/statorials.org\/wp-content\/uploads\/2023\/08\/vbacontains3.jpg\" alt=\"\" width=\"499\" height=\"450\" srcset=\"\" sizes=\"\"><\/p>\n<p> <span style=\"color: #000000;\">Kotak pesan memberitahu kita bahwa ada <strong>5<\/strong> sel yang berisi &#8220;avs&#8221; di nama tim.<\/span><\/p>\n<h2> <span style=\"color: #000000;\"><strong>Sumber daya tambahan<\/strong><\/span><\/h2>\n<p> <span style=\"color: #000000;\">Tutorial berikut menjelaskan cara melakukan tugas umum lainnya di VBA:<\/span><\/p>\n<p> <a href=\"https:\/\/statorials.org\/id\/vba-jika-sel-berisi\/\" target=\"_blank\" rel=\"noopener\">VBA: rumus sederhana untuk \u201cJika sel berisi\u201d<\/a><br \/> <a href=\"https:\/\/statorials.org\/id\/vba-menghitung-kemunculan-karakter-dalam-string\/\" target=\"_blank\" rel=\"noopener\">VBA: Cara menghitung kemunculan karakter dalam sebuah string<\/a><br \/> <a href=\"https:\/\/statorials.org\/id\/vba-berisi-string\/\" target=\"_blank\" rel=\"noopener\">VBA: cara memeriksa apakah suatu string berisi string lain<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Anda dapat menggunakan sintaks dasar berikut untuk menghitung jumlah sel dalam rentang yang berisi teks tertentu menggunakan VBA: SubCountCellsWithText () Range(&#8221; D2 &#8220;) = WorksheetFunction.CountIf(Range(&#8221; A2:A13 &#8220;), &#8221; *avs* &#8220;) End Sub Contoh khusus ini menghitung jumlah sel dalam rentang A2:A13 yang berisi &#8220;avs&#8221;, lalu menetapkan hasilnya ke sel D2 . Catatan : Tanda bintang [&hellip;]<\/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":"<!-- This site is optimized with the Yoast SEO plugin v21.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>VBA: Cara menghitung sel dengan teks tertentu - Statorial<\/title>\n<meta name=\"description\" content=\"Tutorial ini menjelaskan cara menghitung sel yang berisi teks tertentu menggunakan VBA, dengan sebuah 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\/vba-menghitung-sel-dengan-teks-tertentu\/\" \/>\n<meta property=\"og:locale\" content=\"id_ID\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"VBA: Cara menghitung sel dengan teks tertentu - Statorial\" \/>\n<meta property=\"og:description\" content=\"Tutorial ini menjelaskan cara menghitung sel yang berisi teks tertentu menggunakan VBA, dengan sebuah contoh.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/statorials.org\/id\/vba-menghitung-sel-dengan-teks-tertentu\/\" \/>\n<meta property=\"og:site_name\" content=\"Statorials\" \/>\n<meta property=\"article:published_time\" content=\"2023-07-11T21:24:28+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/statorials.org\/wp-content\/uploads\/2023\/08\/vbacontains1.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=\"2 menit\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/statorials.org\/id\/vba-menghitung-sel-dengan-teks-tertentu\/\",\"url\":\"https:\/\/statorials.org\/id\/vba-menghitung-sel-dengan-teks-tertentu\/\",\"name\":\"VBA: Cara menghitung sel dengan teks tertentu - Statorial\",\"isPartOf\":{\"@id\":\"https:\/\/statorials.org\/id\/#website\"},\"datePublished\":\"2023-07-11T21:24:28+00:00\",\"dateModified\":\"2023-07-11T21:24:28+00:00\",\"author\":{\"@id\":\"https:\/\/statorials.org\/id\/#\/schema\/person\/3d17a1160dd2d052b7c78e502cb9ec81\"},\"description\":\"Tutorial ini menjelaskan cara menghitung sel yang berisi teks tertentu menggunakan VBA, dengan sebuah contoh.\",\"breadcrumb\":{\"@id\":\"https:\/\/statorials.org\/id\/vba-menghitung-sel-dengan-teks-tertentu\/#breadcrumb\"},\"inLanguage\":\"id\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/statorials.org\/id\/vba-menghitung-sel-dengan-teks-tertentu\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/statorials.org\/id\/vba-menghitung-sel-dengan-teks-tertentu\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/statorials.org\/id\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Vba: cara menghitung sel dengan teks tertentu\"}]},{\"@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":"VBA: Cara menghitung sel dengan teks tertentu - Statorial","description":"Tutorial ini menjelaskan cara menghitung sel yang berisi teks tertentu menggunakan VBA, dengan sebuah 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\/vba-menghitung-sel-dengan-teks-tertentu\/","og_locale":"id_ID","og_type":"article","og_title":"VBA: Cara menghitung sel dengan teks tertentu - Statorial","og_description":"Tutorial ini menjelaskan cara menghitung sel yang berisi teks tertentu menggunakan VBA, dengan sebuah contoh.","og_url":"https:\/\/statorials.org\/id\/vba-menghitung-sel-dengan-teks-tertentu\/","og_site_name":"Statorials","article_published_time":"2023-07-11T21:24:28+00:00","og_image":[{"url":"https:\/\/statorials.org\/wp-content\/uploads\/2023\/08\/vbacontains1.jpg"}],"author":"Benjamin anderson","twitter_card":"summary_large_image","twitter_misc":{"Ditulis oleh":"Benjamin anderson","Estimasi waktu membaca":"2 menit"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/statorials.org\/id\/vba-menghitung-sel-dengan-teks-tertentu\/","url":"https:\/\/statorials.org\/id\/vba-menghitung-sel-dengan-teks-tertentu\/","name":"VBA: Cara menghitung sel dengan teks tertentu - Statorial","isPartOf":{"@id":"https:\/\/statorials.org\/id\/#website"},"datePublished":"2023-07-11T21:24:28+00:00","dateModified":"2023-07-11T21:24:28+00:00","author":{"@id":"https:\/\/statorials.org\/id\/#\/schema\/person\/3d17a1160dd2d052b7c78e502cb9ec81"},"description":"Tutorial ini menjelaskan cara menghitung sel yang berisi teks tertentu menggunakan VBA, dengan sebuah contoh.","breadcrumb":{"@id":"https:\/\/statorials.org\/id\/vba-menghitung-sel-dengan-teks-tertentu\/#breadcrumb"},"inLanguage":"id","potentialAction":[{"@type":"ReadAction","target":["https:\/\/statorials.org\/id\/vba-menghitung-sel-dengan-teks-tertentu\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/statorials.org\/id\/vba-menghitung-sel-dengan-teks-tertentu\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/statorials.org\/id\/"},{"@type":"ListItem","position":2,"name":"Vba: cara menghitung sel dengan teks tertentu"}]},{"@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\/4339"}],"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=4339"}],"version-history":[{"count":0,"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/posts\/4339\/revisions"}],"wp:attachment":[{"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/media?parent=4339"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/categories?post=4339"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/statorials.org\/id\/wp-json\/wp\/v2\/tags?post=4339"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}