Formula umum
=SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2))
Ringkasan
Untuk menemukan dan mengganti beberapa nilai dengan rumus, Anda dapat menumpuk beberapa fungsi SUBSTITUTE bersama-sama, dan memasukkan pasangan temukan / ganti dari tabel lain menggunakan fungsi INDEX. Dalam contoh yang ditampilkan, kami melakukan 4 operasi temukan dan ganti yang terpisah. Rumus di G5 adalah:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2)),INDEX(find,3),INDEX(replace,3)),INDEX(find,4),INDEX(replace,4))
di mana "temukan" adalah rentang bernama E5: E8, dan "ganti" adalah rentang bernama F5: F8. Lihat di bawah untuk info tentang cara membuat rumus ini lebih mudah dibaca.
Kata pengantar
Tidak ada rumus bawaan untuk menjalankan serangkaian operasi temukan dan ganti di Excel, jadi rumus "konsep" ini menunjukkan satu pendekatan. Teks yang akan dicari dan diganti disimpan langsung di lembar kerja dalam tabel, dan diambil dengan fungsi INDEX. Hal ini membuat solusi "dinamis" - salah satu dari nilai ini diubah, hasil akan segera diperbarui. Tentu saja, tidak ada persyaratan untuk menggunakan INDEX; Anda dapat memasukkan nilai kode keras ke dalam rumus jika Anda mau.
Penjelasan
Pada intinya, rumus tersebut menggunakan fungsi SUBSTITUTE untuk melakukan setiap substitusi, dengan pola dasar ini:
=SUBSTITUTE(text,find,replace)
"Teks" adalah nilai yang masuk, "temukan" adalah teks yang dicari, dan "ganti" adalah teks yang akan diganti. Teks yang dicari dan diganti disimpan di tabel di sebelah kanan, dalam kisaran E5: F8, satu pasang per baris. Nilai di sebelah kiri berada dalam rentang bernama "temukan" dan nilai di sebelah kanan ada dalam rentang bernama "ganti". Fungsi INDEX digunakan untuk mengambil teks "temukan" dan teks "ganti" seperti ini:
INDEX(find,1) // first "find" value INDEX(replace,1) // first "replace" value
Jadi, untuk menjalankan substitusi pertama (cari "merah", ganti dengan "merah muda") kita gunakan:
=SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1))
Secara total, kami menjalankan empat substitusi terpisah, dan setiap SUBSTITUTE berikutnya dimulai dengan hasil dari PENGGANTI sebelumnya:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2)),INDEX(find,3),INDEX(replace,3)),INDEX(find,4),INDEX(replace,4))
Jeda baris agar mudah dibaca
Anda akan melihat rumus bersarang semacam ini cukup sulit untuk dibaca. Dengan menambahkan jeda baris, kita dapat membuat rumus lebih mudah dibaca dan dipelihara:
= SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( B5, INDEX(find,1),INDEX(replace,1)), INDEX(find,2),INDEX(replace,2)), INDEX(find,3),INDEX(replace,3)), INDEX(find,4),INDEX(replace,4))
Bilah rumus di Excel mengabaikan spasi ekstra dan jeda baris, sehingga rumus di atas dapat ditempel secara langsung:
Omong-omong, ada pintasan keyboard untuk memperluas dan menciutkan bilah rumus.
Lebih banyak substitusi
Lebih banyak baris dapat ditambahkan ke tabel untuk menangani lebih banyak pasangan temukan / ganti. Setiap kali pasangan ditambahkan, rumus perlu diperbarui untuk menyertakan pasangan baru. Penting juga untuk memastikan rentang bernama (jika Anda menggunakannya) diperbarui untuk menyertakan nilai baru sesuai kebutuhan. Bergantian, Anda bisa menggunakan Tabel Excel yang tepat untuk rentang dinamis, bukan rentang bernama.
Penggunaan lainnya
Pendekatan yang sama dapat digunakan untuk membersihkan teks dengan "menghapus" tanda baca dan simbol lain dari teks dengan serangkaian substitusi. Misalnya, rumus di halaman ini menunjukkan cara membersihkan dan memformat ulang nomor telepon.
