{"id":2043,"date":"2023-10-23T06:04:06","date_gmt":"2023-10-23T06:04:06","guid":{"rendered":"https:\/\/exceladvisor.org\/id\/mengganti-beberapa-nilai\/"},"modified":"2023-12-06T06:16:19","modified_gmt":"2023-12-06T06:16:19","slug":"mengganti-beberapa-nilai","status":"publish","type":"page","link":"https:\/\/exceladvisor.org\/id\/mengganti-beberapa-nilai\/","title":{"rendered":"Bagaimana cara mengganti beberapa nilai (bersarang) di excel?"},"content":{"rendered":"<div class=\"excel-before-content\" id=\"excel-2821248377\"><script type=\"text\/javascript\">\r\n\tatOptions = {\r\n\t\t'key' : 'c1158f160081d6540a8409e6925dab94',\r\n\t\t'format' : 'iframe',\r\n\t\t'height' : 250,\r\n\t\t'width' : 300,\r\n\t\t'params' : {}\r\n\t};\r\n<\/script>\r\n<script type=\"text\/javascript\" src=\"\/\/www.highperformanceformat.com\/c1158f160081d6540a8409e6925dab94\/invoke.js\"><\/script><\/div> <p>Di Excel, jika Anda ingin mengganti beberapa nilai dari satu sel, dalam hal ini Anda perlu membuat rumus bertingkat dengan fungsi SUBSTITUTE. Dan dalam tutorial ini, kita akan mempelajari cara menulis rumus ini.<\/p><h2 class=\"wp-block-heading\"> Tulis rumus substitusi bertingkat untuk mengganti beberapa nilai<\/h2><p> Di bawah ini adalah rumus dimana kita menggunakan fungsi SUBSTITUTE sebanyak lima kali untuk mengganti lima nilai di sel A1. <\/p><div class=\"wp-block-image\"><figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-83064\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-remplacer-plusieurs-valeurs.png\" sizes=\"auto, \" srcset=\"\" alt=\"mengganti beberapa nilai\" width=\"707\" height=\"233\"><\/figure><\/div><pre class=\"wp-block-preformatted\"> =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,\"One\",\"1\"),\"Two\",\"2\"),\"Three\",\"3\"),\"Four\",\"4\"),\" Five\",\"5\")<\/pre><p> Rumus ini merupakan rumus bertingkat dengan fungsi REPLACE yang digunakan sebanyak lima kali untuk mengganti lima nilai dalam sel. Dan untuk membantu Anda memahami rumus ini, saya menyusunnya sebagai berikut: <\/p><div class=\"wp-block-image\"><figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-83065\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/2-formules-imbriquees-avec-fonction-de-substitution.png\" sizes=\"auto, \" srcset=\"\" alt=\"rumus-bersarang-dengan-fungsi-substitusi\" width=\"691\" height=\"211\"><\/figure><\/div><p> Rumus ini menggantikan:<\/p><ul><li> Satu \u21e2 1<\/li><li> Dua \u21e2 2<\/li><li> Tiga \u21e2 3<\/li><li> Oven \u21e2 4<\/li><li> Lima \u21e2 5<\/li><\/ul><p> Karena ini adalah rumus bertingkat, fungsi kedua menggunakan nilai yang dikembalikan oleh fungsi pertama, fungsi ketiga menggunakan nilai yang dikembalikan oleh fungsi kedua, dan seterusnya. <\/p><div class=\"wp-block-image\"><figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-83066\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/3-deuxieme-fonction-utilise-la-valeur-renvoyee-par-first1.png\" sizes=\"auto, \" srcset=\"\" alt=\"fungsi kedua menggunakan nilai yang dikembalikan oleh yang pertama\" width=\"700\" height=\"213\"><\/figure><\/div><h2 class=\"wp-block-heading\"> Pengganti bersarang dengan referensi sel<\/h2><p> Pada rumus di atas, kita memasukkan nilainya langsung ke dalam rumus satu per satu. Namun Anda juga bisa membuat referensi sel untuk semua nilai. <\/p><div class=\"wp-block-image\"><figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-83067\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/4-substituts-imbriques-avec-reference-de-cellule.png\" sizes=\"auto, \" srcset=\"\" alt=\"pengganti-bersarang-dengan-referensi sel\" width=\"648\" height=\"338\"><\/figure><\/div><pre class=\"wp-block-preformatted\"> =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,INDEX(D1:D5,1),INDEX(E1:E5,1)),INDEX(D1:D5,2),INDEX(E1:E5,2)) ,INDEX(D1:D5,3),INDEX(E1:E5,3)),INDEX(D1:D5,4),INDEX(E1:E5,4)),INDEX(D1:D5,5),INDEX( E1:E5,5)) <\/pre><div class=\"wp-block-image\"><figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-83069\" src=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/5-deux-fonctions-dindex-imbriquees-dans-la-formule.png\" sizes=\"auto, \" srcset=\"\" alt=\"dua fungsi bersarang indeks dalam rumus\" width=\"678\" height=\"219\"><\/figure><\/div><p> Di setiap fungsi dalam rumus, Anda memiliki dua fungsi INDEX. Fungsi INDEX pertama mengembalikan nilai yang ingin Anda ganti dalam rentang E1:E5, dan INDEX kedua mendapatkan nilai baru untuk diganti dalam rentang F1:F5. <\/p><div class=\"gb-container gb-container-52dfcee1 gb-container-sample-file\"><h2 class=\"gb-headline gb-headline-e289c143 gb-headline-text gb-headline-4f7959c2\"> Dapatkan file Excelnya<\/h2> <a class=\"gb-button gb-button-e88d6bee gb-button-text gb-button-button1\" href=\"https:\/\/exceladvisor.org\/id\/a\/\" rel=\"nofollow\">Unduh<\/a><\/div><div class=\"gb-container gb-container-0df56012 gb-container-mt\"> <a class=\"gb-button gb-button-f9f432f4 gb-button-text gb-button-mt-button\" href=\"https:\/\/exceladvisor.org\/id\/rumus\/\">Daftar rumus Excel<\/a><h2 class=\"gb-headline gb-headline-233444e8 gb-headline-text gb-headline-mt-heading\"> Rumus terkait <\/h2><div class=\"gb-grid-wrapper gb-grid-wrapper-a16fa65c gb-grid-wrapper-mt-grid\"><div class=\"gb-grid-column gb-grid-column-50d07e0d\"><div class=\"gb-container gb-container-50d07e0d\"><ul><li> <a href=\"https:\/\/exceladvisor.org\/id\/baris-baru-di-sel\/\">Tambahkan baris baru ke sel di Excel (jeda baris)<\/a><\/li><li> <a href=\"https:\/\/exceladvisor.org\/id\/angka-nol-di-depan\/\">Tambahkan angka nol di depan di Excel<\/a><\/li><li> <a href=\"https:\/\/exceladvisor.org\/id\/menggabungkan-sel-dengan-koma\/\">Gabungkan (gabungkan) sel dengan koma di Excel<\/a><\/li><li> <a href=\"https:\/\/exceladvisor.org\/id\/menyatukan-sejumlah-sel\/\">Menggabungkan (menggabungkan) sejumlah sel di Excel<\/a><\/li><li> <a href=\"https:\/\/exceladvisor.org\/id\/menggabungkan-jeda-baris\/\">Gabungkan dengan jeda baris di Excel<\/a><\/li><\/ul><\/div><\/div><\/div><\/div>","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"open","ping_status":"closed","template":"","meta":{"ub_ctt_via":"","footnotes":""},"class_list":["post-2043","page","type-page","status-publish","hentry"],"featured_image_src":null,"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v25.4 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Bagaimana cara mengganti beberapa nilai (bersarang) di Excel?<\/title>\n<meta name=\"description\" content=\"Tutorial ini akan mengajarkan Anda cara menulis rumus Excel untuk mengganti beberapa nilai dari sebuah string. Mari kita periksa ini\u2026\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/exceladvisor.org\/id\/mengganti-beberapa-nilai\/\" \/>\n<meta property=\"og:locale\" content=\"id_ID\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Bagaimana cara mengganti beberapa nilai (bersarang) di Excel?\" \/>\n<meta property=\"og:description\" content=\"Tutorial ini akan mengajarkan Anda cara menulis rumus Excel untuk mengganti beberapa nilai dari sebuah string. Mari kita periksa ini\u2026\" \/>\n<meta property=\"og:url\" content=\"https:\/\/exceladvisor.org\/id\/mengganti-beberapa-nilai\/\" \/>\n<meta property=\"og:site_name\" content=\"Excel Advisor\" \/>\n<meta property=\"article:modified_time\" content=\"2023-12-06T06:16:19+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-remplacer-plusieurs-valeurs.png\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Estimasi waktu membaca\" \/>\n\t<meta name=\"twitter:data1\" content=\"1 menit\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/exceladvisor.org\/id\/mengganti-beberapa-nilai\/\",\"url\":\"https:\/\/exceladvisor.org\/id\/mengganti-beberapa-nilai\/\",\"name\":\"Bagaimana cara mengganti beberapa nilai (bersarang) di Excel?\",\"isPartOf\":{\"@id\":\"https:\/\/exceladvisor.org\/id\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/exceladvisor.org\/id\/mengganti-beberapa-nilai\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/exceladvisor.org\/id\/mengganti-beberapa-nilai\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-remplacer-plusieurs-valeurs.png\",\"datePublished\":\"2023-10-23T06:04:06+00:00\",\"dateModified\":\"2023-12-06T06:16:19+00:00\",\"description\":\"Tutorial ini akan mengajarkan Anda cara menulis rumus Excel untuk mengganti beberapa nilai dari sebuah string. Mari kita periksa ini\u2026\",\"breadcrumb\":{\"@id\":\"https:\/\/exceladvisor.org\/id\/mengganti-beberapa-nilai\/#breadcrumb\"},\"inLanguage\":\"id\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/exceladvisor.org\/id\/mengganti-beberapa-nilai\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"id\",\"@id\":\"https:\/\/exceladvisor.org\/id\/mengganti-beberapa-nilai\/#primaryimage\",\"url\":\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-remplacer-plusieurs-valeurs.png\",\"contentUrl\":\"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-remplacer-plusieurs-valeurs.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/exceladvisor.org\/id\/mengganti-beberapa-nilai\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Rumah\",\"item\":\"https:\/\/exceladvisor.org\/id\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Bagaimana cara mengganti beberapa nilai (bersarang) di excel?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/exceladvisor.org\/id\/#website\",\"url\":\"https:\/\/exceladvisor.org\/id\/\",\"name\":\"Excel Advisor\",\"description\":\"Panduan utama Anda untuk mendominasi data!\",\"publisher\":{\"@id\":\"https:\/\/exceladvisor.org\/id\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/exceladvisor.org\/id\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"id\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/exceladvisor.org\/id\/#organization\",\"name\":\"Excel Advisor\",\"url\":\"https:\/\/exceladvisor.org\/id\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"id\",\"@id\":\"https:\/\/exceladvisor.org\/id\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/exceladvisor.org\/id\/wp-content\/uploads\/2023\/12\/exceladvisor.org_.png\",\"contentUrl\":\"https:\/\/exceladvisor.org\/id\/wp-content\/uploads\/2023\/12\/exceladvisor.org_.png\",\"width\":105,\"height\":36,\"caption\":\"Excel Advisor\"},\"image\":{\"@id\":\"https:\/\/exceladvisor.org\/id\/#\/schema\/logo\/image\/\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Bagaimana cara mengganti beberapa nilai (bersarang) di Excel?","description":"Tutorial ini akan mengajarkan Anda cara menulis rumus Excel untuk mengganti beberapa nilai dari sebuah string. Mari kita periksa ini\u2026","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:\/\/exceladvisor.org\/id\/mengganti-beberapa-nilai\/","og_locale":"id_ID","og_type":"article","og_title":"Bagaimana cara mengganti beberapa nilai (bersarang) di Excel?","og_description":"Tutorial ini akan mengajarkan Anda cara menulis rumus Excel untuk mengganti beberapa nilai dari sebuah string. Mari kita periksa ini\u2026","og_url":"https:\/\/exceladvisor.org\/id\/mengganti-beberapa-nilai\/","og_site_name":"Excel Advisor","article_modified_time":"2023-12-06T06:16:19+00:00","og_image":[{"url":"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-remplacer-plusieurs-valeurs.png","type":"","width":"","height":""}],"twitter_card":"summary_large_image","twitter_misc":{"Estimasi waktu membaca":"1 menit"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/exceladvisor.org\/id\/mengganti-beberapa-nilai\/","url":"https:\/\/exceladvisor.org\/id\/mengganti-beberapa-nilai\/","name":"Bagaimana cara mengganti beberapa nilai (bersarang) di Excel?","isPartOf":{"@id":"https:\/\/exceladvisor.org\/id\/#website"},"primaryImageOfPage":{"@id":"https:\/\/exceladvisor.org\/id\/mengganti-beberapa-nilai\/#primaryimage"},"image":{"@id":"https:\/\/exceladvisor.org\/id\/mengganti-beberapa-nilai\/#primaryimage"},"thumbnailUrl":"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-remplacer-plusieurs-valeurs.png","datePublished":"2023-10-23T06:04:06+00:00","dateModified":"2023-12-06T06:16:19+00:00","description":"Tutorial ini akan mengajarkan Anda cara menulis rumus Excel untuk mengganti beberapa nilai dari sebuah string. Mari kita periksa ini\u2026","breadcrumb":{"@id":"https:\/\/exceladvisor.org\/id\/mengganti-beberapa-nilai\/#breadcrumb"},"inLanguage":"id","potentialAction":[{"@type":"ReadAction","target":["https:\/\/exceladvisor.org\/id\/mengganti-beberapa-nilai\/"]}]},{"@type":"ImageObject","inLanguage":"id","@id":"https:\/\/exceladvisor.org\/id\/mengganti-beberapa-nilai\/#primaryimage","url":"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-remplacer-plusieurs-valeurs.png","contentUrl":"https:\/\/exceladvisor.org\/wp-content\/uploads\/2023\/08\/1-remplacer-plusieurs-valeurs.png"},{"@type":"BreadcrumbList","@id":"https:\/\/exceladvisor.org\/id\/mengganti-beberapa-nilai\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Rumah","item":"https:\/\/exceladvisor.org\/id\/"},{"@type":"ListItem","position":2,"name":"Bagaimana cara mengganti beberapa nilai (bersarang) di excel?"}]},{"@type":"WebSite","@id":"https:\/\/exceladvisor.org\/id\/#website","url":"https:\/\/exceladvisor.org\/id\/","name":"Excel Advisor","description":"Panduan utama Anda untuk mendominasi data!","publisher":{"@id":"https:\/\/exceladvisor.org\/id\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/exceladvisor.org\/id\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"id"},{"@type":"Organization","@id":"https:\/\/exceladvisor.org\/id\/#organization","name":"Excel Advisor","url":"https:\/\/exceladvisor.org\/id\/","logo":{"@type":"ImageObject","inLanguage":"id","@id":"https:\/\/exceladvisor.org\/id\/#\/schema\/logo\/image\/","url":"https:\/\/exceladvisor.org\/id\/wp-content\/uploads\/2023\/12\/exceladvisor.org_.png","contentUrl":"https:\/\/exceladvisor.org\/id\/wp-content\/uploads\/2023\/12\/exceladvisor.org_.png","width":105,"height":36,"caption":"Excel Advisor"},"image":{"@id":"https:\/\/exceladvisor.org\/id\/#\/schema\/logo\/image\/"}}]}},"yoast_meta":{"yoast_wpseo_title":"","yoast_wpseo_metadesc":"","yoast_wpseo_canonical":""},"_links":{"self":[{"href":"https:\/\/exceladvisor.org\/id\/wp-json\/wp\/v2\/pages\/2043","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/exceladvisor.org\/id\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/exceladvisor.org\/id\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/exceladvisor.org\/id\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/exceladvisor.org\/id\/wp-json\/wp\/v2\/comments?post=2043"}],"version-history":[{"count":1,"href":"https:\/\/exceladvisor.org\/id\/wp-json\/wp\/v2\/pages\/2043\/revisions"}],"predecessor-version":[{"id":2518,"href":"https:\/\/exceladvisor.org\/id\/wp-json\/wp\/v2\/pages\/2043\/revisions\/2518"}],"wp:attachment":[{"href":"https:\/\/exceladvisor.org\/id\/wp-json\/wp\/v2\/media?parent=2043"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}