Formula generik
=SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2))
Ringkasan
Untuk mencari dan mengganti beberapa nilai dengan formula, anda dapat menyusun beberapa fungsi SUBSTITUTE bersama-sama, dan memberi makan mencari / mengganti pasangan dari jadual lain menggunakan fungsi INDEX. Dalam contoh yang ditunjukkan, kami melakukan 4 operasi cari dan ganti yang berasingan. Formula dalam 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 "cari" adalah rentang bernama E5: E8, dan "ganti" adalah julat bernama F5: F8. Lihat di bawah untuk maklumat mengenai cara membuat formula ini lebih mudah dibaca.
Kata Pengantar
Tidak ada formula terbina dalam untuk menjalankan rangkaian operasi mencari dan menggantikan di Excel, jadi ini formula "konsep" untuk menunjukkan satu pendekatan. Teks yang akan dicari dan diganti disimpan terus pada lembaran kerja dalam jadual, dan diambil dengan fungsi INDEX. Ini menjadikan penyelesaian "dinamik" - mana-mana nilai ini diubah, hasilnya akan segera dikemas kini. Sudah tentu, tidak ada keperluan untuk menggunakan INDEX; anda boleh memasukkan nilai kod keras ke dalam formula jika anda mahu.
Penjelasan
Pada intinya, formula menggunakan fungsi SUBSTITUTE untuk melakukan setiap penggantian, dengan corak asas ini:
=SUBSTITUTE(text,find,replace)
"Teks" adalah nilai masuk, "cari" adalah teks yang harus dicari, dan "ganti" adalah teks yang akan diganti dengan. Teks yang akan dicari dan diganti disimpan dalam jadual di sebelah kanan, dalam julat E5: F8, satu pasang setiap baris. Nilai di sebelah kiri berada dalam julat bernama "cari" dan nilai di sebelah kanan berada dalam julat bernama "ganti". Fungsi INDEX digunakan untuk mengambil teks "cari" dan teks "ganti" seperti ini:
INDEX(find,1) // first "find" value INDEX(replace,1) // first "replace" value
Oleh itu, untuk menjalankan penggantian pertama (cari "merah", ganti dengan "merah jambu") kita gunakan:
=SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1))
Secara keseluruhan, kami menjalankan empat penggantian yang berasingan, dan masing-masing SUBSTITUTE berikutnya bermula dengan hasil dari SUBSTITUT 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 untuk kesediaan membaca
Anda akan dapati formula bersarang seperti ini agak sukar dibaca. Dengan menambahkan jeda baris, kita dapat membuat formula lebih mudah dibaca dan dikekalkan:
= 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))
Bar formula di Excel mengabaikan ruang kosong dan jeda baris tambahan, jadi formula di atas boleh ditampal secara langsung:
Ngomong-ngomong, ada jalan pintas papan kekunci untuk mengembangkan dan meruntuhkan bar formula.
Lebih banyak penggantian
Lebih banyak baris dapat ditambahkan ke jadual untuk menangani lebih banyak pasangan mencari / ganti. Setiap kali pasangan ditambahkan, formula perlu diperbarui untuk memasukkan pasangan baru. Penting juga untuk memastikan julat yang dinamakan (jika anda menggunakannya) dikemas kini untuk memasukkan nilai baru jika diperlukan. Sebagai alternatif, anda boleh menggunakan Jadual Excel yang tepat untuk julat dinamik, dan bukannya julat yang dinamakan.
Kegunaan lain
Pendekatan yang sama dapat digunakan membersihkan teks dengan "melepaskan" tanda baca dan simbol lain dari teks dengan rangkaian penggantian. Contohnya, formula di halaman ini menunjukkan cara membersihkan dan memformat semula nombor telefon.
