Excel: Cari dan ganti pelbagai nilai sekaligus

Dalam tutorial ini, kami akan melihat beberapa cara untuk mencari dan menggantikan pelbagai perkataan, rentetan, atau watak individu, jadi anda boleh memilih yang paling sesuai dengan keperluan anda.

Bagaimana orang biasanya mencari di Excel? Kebanyakannya, dengan menggunakan ciri Find & Ganti, yang berfungsi dengan baik untuk nilai tunggal. Tetapi bagaimana jika anda mempunyai puluhan atau beratus -ratus item untuk diganti? Sudah tentu, tiada siapa yang mahu membuat semua penggantian secara manual satu demi satu, dan kemudian melakukannya sekali lagi apabila data berubah. Nasib baik, terdapat beberapa kaedah yang lebih berkesan untuk melakukan penggantian massa dalam Excel, dan kami akan menyiasat setiap mereka secara terperinci.

• Formula pengganti bersarang untuk menggantikan pelbagai nilai
• Formula xlookup untuk mencari pelbagai perkataan
• Multi-Replace Menggunakan Fungsi Lambda Rekursif
• Cari Massa dan ganti dengan UDF
• Bulk-Halus Macro untuk mencari dan menggantikan pelbagai nilai
• Pelbagai Cari dan Ganti Menggunakan Alat Substring

Cari dan ganti pelbagai nilai dengan pengganti bersarang

Cara paling mudah untuk mencari dan menggantikan pelbagai penyertaan dalam Excel adalah dengan menggunakan fungsi pengganti.

Logik formula sangat mudah: anda menulis beberapa fungsi individu untuk menggantikan nilai lama dengan yang baru. Dan kemudian, anda bersarang fungsi -fungsi itu satu ke dalam yang lain, supaya setiap pengganti berikutnya menggunakan output pengganti sebelumnya untuk mencari nilai seterusnya.

Pengganti (pengganti (pengganti ( teks , old_text1 , new_text1 ), old_text2 , new_text2 ), old_text3 , new_text3 )

Dalam senarai lokasi dalam A2: A10, katakan anda ingin menggantikan nama negara disingkat (seperti FR , UK dan Amerika Syarikat ) dengan nama penuh.

Untuk melakukannya, masukkan nilai lama dalam D2: D4 dan nilai -nilai baru dalam E2: E4 seperti yang ditunjukkan dalam tangkapan skrin di bawah. Dan kemudian, letakkan formula di bawah dalam B2 dan tekan Enter:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2:A10, D2, E2), D3, E3), D4, E4)

... dan anda akan mempunyai semua penggantian yang dilakukan sekaligus:

Sila ambil perhatian, pendekatan di atas hanya berfungsi dalam Excel 365 yang menyokong tatasusunan dinamik.

Dalam versi pra-dinamik Excel 2019, Excel 2016 dan lebih awal, formula perlu ditulis untuk sel paling atas (B2), dan kemudian disalin ke sel-sel di bawah:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, $D$2, $E$2), $D$3, $E$3), $D$4, $E$4)

Sila perhatikan bahawa, dalam kes ini, kami mengunci nilai penggantian dengan rujukan sel mutlak, jadi mereka tidak akan beralih apabila menyalin formula ke bawah.

Nota. Fungsi pengganti adalah sensitif kes , bermakna anda harus menaip nilai lama ( old_text ) dalam kes huruf yang sama seperti yang muncul dalam data asal.

Semudah mungkin, kaedah ini mempunyai kelemahan yang ketara - apabila anda mempunyai berpuluh -puluh item untuk menggantikan, fungsi bersarang menjadi agak sukar untuk dikendalikan.

Kelebihan : mudah dilaksanakan; disokong dalam semua versi Excel

Kelemahan : terbaik untuk digunakan untuk bilangan nilai mencari/menggantikan yang terhad

Cari dan ganti pelbagai entri dengan xlookup

Dalam keadaan apabila anda ingin menggantikan keseluruhan kandungan sel , bukan bahagiannya, fungsi Xlookup berguna.

Katakan anda mempunyai senarai negara dalam lajur A dan bertujuan untuk menggantikan semua singkatan dengan nama penuh yang sepadan. Seperti dalam contoh terdahulu, anda bermula dengan memasukkan item "Cari" dan "menggantikan" dalam lajur berasingan (D dan E masing -masing), dan kemudian masukkan formula ini dalam B2:

=XLOOKUP(A2, $D$2:$D$4, $E$2:$E$4, A2)

Diterjemahkan dari bahasa Excel ke dalam bahasa manusia, inilah formula yang dilakukan:

Cari nilai A2 (lookup_value) dalam d2: d4 (lookup_array) dan kembalikan perlawanan dari e2: e4 (return_array). Jika tidak dijumpai, tarik nilai asal dari A2.

Klik dua kali pemegang isi untuk mendapatkan formula yang disalin ke sel-sel di bawah, dan hasilnya tidak akan membuat anda menunggu:

Oleh kerana fungsi Xlookup hanya tersedia dalam Excel 365, formula di atas tidak akan berfungsi dalam versi terdahulu. Walau bagaimanapun, anda boleh meniru tingkah laku ini dengan gabungan Iferror atau Ifna dan Vlookup:

=IFNA(VLOOKUP(A2, $D$2:$E$4, 2, FALSE), A2)

Nota. Tidak seperti pengganti, fungsi Xlookup dan Vlookup tidak sensitif kes , bermakna mereka mencari nilai carian yang mengabaikan kes huruf. Sebagai contoh, formula kami akan menggantikan FR dan FR dengan Perancis .

Kelebihan : Penggunaan fungsi biasa yang luar biasa; berfungsi dalam semua versi Excel

Kekurangan : Berfungsi pada tahap sel, tidak dapat menggantikan sebahagian daripada kandungan sel

Pelbagai Mengganti Menggunakan Fungsi Lambda Rekursif

Untuk pelanggan Microsoft 365, Excel menyediakan fungsi khas yang membolehkan membuat fungsi tersuai menggunakan bahasa formula tradisional. Ya, saya bercakap mengenai Lambda. Keindahan kaedah ini adalah bahawa ia boleh menukar formula yang sangat panjang dan kompleks menjadi yang sangat padat dan mudah. Lebih -lebih lagi, ia membolehkan anda membuat fungsi anda sendiri yang tidak wujud dalam Excel, sesuatu yang sebelum ini mungkin hanya dengan VBA.

Untuk maklumat terperinci mengenai membuat dan menggunakan fungsi Lambda tersuai, sila lihat tutorial ini: Cara Menulis Fungsi Lambda di Excel. Di sini, kita akan membincangkan beberapa contoh praktikal.

Kelebihan : Hasilnya adalah fungsi yang elegan dan luar biasa mudah digunakan, tidak kira bilangan pasangan penggantian

Kelemahan : hanya tersedia dalam Excel 365; Buku kerja khusus dan tidak dapat digunakan semula di buku kerja yang berbeza

Contoh 1. Cari dan ganti beberapa perkataan / rentetan sekaligus

Untuk menggantikan pelbagai perkataan atau teks dalam satu perjalanan, kami telah mencipta fungsi lambda tersuai, bernama MultirePlace , yang boleh mengambil salah satu bentuk ini:

=LAMBDA(text, old, new, IF(old"", MultiReplace(SUBSTITUTE(text, old, new), OFFSET(old, 1, 0), OFFSET(new, 1, 0)), text))

Atau

=LAMBDA(text, old, new, IF(old="", text, MultiReplace(SUBSTITUTE(text, old, new), OFFSET(old, 1, 0), OFFSET(new, 1, 0))))

Kedua -duanya adalah fungsi rekursif yang memanggil diri mereka sendiri. Perbezaannya hanya bagaimana titik keluar ditubuhkan.

Dalam formula pertama, fungsi IF memeriksa sama ada senarai lama tidak kosong (lama ""). Jika benar, fungsi multireplace dipanggil. Jika palsu, fungsi mengembalikan teksnya bentuk dan keluar semasa.

Formula kedua menggunakan logik terbalik: jika lama kosong (lama = ""), kemudian kembali teks dan keluar; Jika tidak hubungi MultirePlace .

Bahagian yang paling rumit dicapai! Apa yang tersisa untuk anda lakukan ialah menamakan fungsi multire dalam pengurus nama seperti yang ditunjukkan dalam tangkapan skrin di bawah. Untuk garis panduan terperinci, sila lihat cara menamakan fungsi Lambda.

Sebaik sahaja fungsi mendapat nama, anda boleh menggunakannya seperti fungsi terbina yang lain.

Mana-mana dari dua variasi formula yang anda pilih, dari perspektif pengguna akhir, sintaks adalah semudah ini:

MultirePlace (teks, lama, baru)

Di mana:

• Teks - Data Sumber
• Lama - nilai untuk dicari
• Baru - Nilai untuk diganti dengan

Mengambil contoh sebelumnya sedikit lebih jauh, mari kita menggantikan bukan sahaja singkatan negara tetapi juga singkatan keadaan. Untuk ini, taip singkatan (nilai lama ) dalam lajur D bermula dalam D2 dan nama penuh (nilai baru ) dalam lajur E bermula dalam E2.

Dalam B2, masukkan fungsi MultirePlace:

=MultiReplace(A2:A10, D2, E2)

Tekan masuk dan nikmati hasilnya :)

Bagaimana formula ini berfungsi

Petunjuk untuk memahami formula adalah memahami rekursi. Ini mungkin terdengar rumit, tetapi prinsipnya agak mudah. Dengan setiap lelaran, fungsi rekursif menyelesaikan satu contoh kecil masalah yang lebih besar. Dalam kes kami, gelung fungsi multireplace melalui nilai lama dan baru dan, dengan setiap gelung, melakukan satu pengganti:

MultirePlace (SUBSTITUTE(text, old, new), OFFSET(old, 1, 0), OFFSET(new, 1, 0))

Seperti fungsi pengganti bersarang, hasil pengganti terdahulu menjadi parameter teks untuk pengganti seterusnya. Dalam erti kata lain, pada setiap panggilan seterusnya multire , fungsi pengganti tidak memproses rentetan teks asal, tetapi output panggilan sebelumnya.

Untuk mengendalikan semua item pada senarai lama , kami mulakan dengan sel paling atas, dan gunakan fungsi offset untuk memindahkan 1 baris ke bawah dengan setiap interaksi:

OFFSET(old, 1, 0)

Perkara yang sama dilakukan untuk senarai baru :

OFFSET(new, 1, 0)

Perkara penting adalah untuk memberikan titik keluar untuk mengelakkan panggilan rekursif daripada meneruskan selama -lamanya. Ia dilakukan dengan bantuan fungsi IF - jika sel lama kosong, fungsi mengembalikan teksnya bentuk dan keluar sekarang:

=LAMBDA(text, old, new, IF(old="", text, MultiReplace(…)))

atau

=LAMBDA(text, old, new, IF(old"", MultiReplace(…), text))

Contoh 2. Gantikan pelbagai aksara dalam Excel

Pada dasarnya, fungsi multireplace yang dibincangkan dalam contoh terdahulu dapat mengendalikan aksara individu juga, dengan syarat setiap watak lama dan baru dimasukkan ke dalam sel yang berasingan, sama seperti nama disingkat dan penuh dalam tangkapan skrin di atas.

Jika anda lebih suka memasukkan aksara lama dalam satu sel dan watak -watak baru dalam sel lain, atau taipkannya secara langsung dalam formula, maka anda boleh membuat fungsi tersuai yang lain, bernama GantiChars , dengan menggunakan salah satu formula ini:

=LAMBDA(text, old_chars, new_chars, IF(old_chars"", ReplaceChars(SUBSTITUTE(text, LEFT(old_chars), LEFT(new_chars)), RIGHT(old_chars, LEN(old_chars)-1), RIGHT(new_chars, LEN(new_chars)-1)), text))

Atau

=LAMBDA(text, old_chars, new_chars, IF(old_chars="", text, ReplaceChars(SUBSTITUTE(text, LEFT(old_chars), LEFT(new_chars)), RIGHT(old_chars, LEN(old_chars)-1), RIGHT(new_chars, LEN(new_chars)-1))))

Ingatlah untuk menamakan fungsi Lambda baru anda dalam Pengurus Nama seperti biasa:

Fungsi penggantiChars "title =" Namakan fungsi penggantian Custom ">>

Dan fungsi tersuai baru anda sedia untuk digunakan:

Menggantikanechars (teks, old_chars, new_chars)

Di mana:

• Teks - rentetan asal
• Lama - watak untuk mencari
• Baru - watak untuk diganti dengan

Untuk memberikan ujian lapangan, mari kita lakukan sesuatu yang sering dilakukan pada data yang diimport - menggantikan petikan pintar dan apostrope pintar dengan petikan lurus dan apostrop lurus.

Pertama, kita memasukkan petikan pintar dan apostrophe pintar dalam D2, petikan lurus dan apostrophe lurus di E2, memisahkan watak -watak dengan ruang untuk dibaca yang lebih baik. (Seperti yang kita gunakan pembatas yang sama di kedua -dua sel, ia tidak akan memberi kesan kepada hasilnya - Excel hanya akan menggantikan ruang dengan ruang.)

Selepas itu, kami memasuki formula ini dalam B2:

=ReplaceChars(A2:A4, D2, E2)

Dan dapatkan hasil yang kita cari:

Ia juga mungkin untuk menaip watak -watak secara langsung dalam formula. Dalam kes kami, ingatlah untuk "menduplikasi" petikan lurus seperti ini:

=ReplaceChars(A2:A4, "“ ” '", """ "" '")

Bagaimana formula ini berfungsi

Kitaran fungsi penggantiChars melalui rentetan old_chars dan new_chars dan membuat satu pengganti pada satu masa bermula dari watak pertama di sebelah kiri. Bahagian ini dilakukan oleh fungsi pengganti:

SUBSTITUTE(text, LEFT(old_chars), LEFT(new_chars))

Dengan setiap lelaran, fungsi yang betul melepaskan satu aksara dari kiri kedua -dua rentetan old_chars dan new_chars , supaya kiri dapat mengambil pasangan aksara seterusnya untuk penggantian:

ReplaceChars(SUBSTITUTE(text, LEFT(old_chars), LEFT(new_chars)), RIGHT(old_chars, LEN(old_chars)-1), RIGHT(new_chars, LEN(new_chars)-1))

Sebelum setiap panggilan rekursif, fungsi IF menilai rentetan old_chars . Jika ia tidak kosong, fungsi itu memanggilnya sendiri. Sebaik sahaja watak terakhir telah diganti, proses lelaran selesai, formula mengembalikan teksnya bentuk dan keluarnya sekarang.

Nota. Kerana fungsi pengganti yang digunakan dalam formula teras kami adalah sensitif kes , kedua-dua lambdas ( multire dan pengganti ) merawat huruf besar dan huruf kecil sebagai aksara yang berbeza.

Cari Massa dan ganti dengan UDF

Sekiranya fungsi Lambda tidak tersedia di Excel anda, anda boleh menulis fungsi yang ditentukan pengguna untuk multi-tempat dengan cara tradisional menggunakan VBA.

Untuk membezakan UDF dari fungsi multaplace yang ditentukan oleh Lambda, kami akan menamakannya secara berbeza, kata MassReplace . Kod fungsi adalah seperti berikut:

Fungsi massreplace (inputRng sebagai julat, findrng sebagai julat, penggantian sebagai julat) sebagai varian () dimal arres ( ) sebagai variasi 'array untuk menyimpan hasil yang dimalit arsearchPlace (), stmp sebagai array rentet iinputcurcol, cntinputrows, cntInputcols Sebagai indeks panjang 'baris semasa dalam julat sumber, indeks lajur semasa dalam julat sumber, kiraan baris, kiraan lajur cntInputrows = inputrng.rows.count cntinputcols = inputrng.columng.count.count.count.count.count.count.count.count.count.count.count.count.count.count.count.count.count.count . cntinputrows, 1 hingga cntInputcols) redim arsearchReplace (1 hingga cntfindrows, 1 hingga 2) 'menyediakan pelbagai mencari/menggantikan pasangan untuk ifindcurrow = 1 hingga cntfindrows arsearchReplace (ifindcurrow, 1) Pengganting.cells (ifindcurrow, 1). Nilai seterusnya 'mencari dan menggantikan dalam julat sumber untuk iinputcurrow = 1 hingga cntinputrows untuk iinputcurcol = 1 hingga cntInputcols stmp = inputrng.cells (iinputcurrow, iinputcurcol untuk alllyralcols = iinputcurcols untuk allyralcol ) cntfindrows stmp = gantikan ( stmp, arsearchReplace (ifindcurrow, 1 ) , arsearchReplace (ifindcurrow, 2)) arres seterusnya (iinputcurrow, iinputcurcol )

Seperti fungsi yang ditentukan oleh Lambda, UDFS adalah buku kerja di seluruh dunia . Ini bermakna fungsi MassReplace akan berfungsi hanya dalam buku kerja di mana anda telah memasukkan kod tersebut. Jika anda tidak pasti bagaimana untuk melakukannya dengan betul, sila ikuti langkah -langkah yang diterangkan dalam cara memasukkan kod VBA dalam Excel.

Sebaik sahaja kod itu ditambah ke buku kerja anda, fungsi akan muncul dalam Formula IntelliSense - hanya nama fungsi, bukan argumen! Walaupun, saya percaya ia bukan masalah besar untuk mengingati sintaks:

MassReplace (input_range, find_range, pengganti_range)

Di mana:

• Input_range - julat sumber di mana anda mahu menggantikan nilai.
• Find_range - Watak, rentetan, atau perkataan untuk dicari.
• Ganti_range - aksara, rentetan, atau perkataan untuk diganti.

Dalam Excel 365, disebabkan oleh sokongan untuk tatasusunan dinamik, ini berfungsi sebagai formula biasa, yang hanya perlu dimasukkan ke dalam sel atas (B2):

=MassReplace(A2:A10, D2:D4, E2:E4)

Dalam Excel pra-dinamik, ini berfungsi sebagai formula pelbagai CSE gaya lama: anda memilih keseluruhan julat sumber (B2: B10), taipkan formula, dan tekan CTRL Shift Enter Kekunci serentak untuk menyelesaikannya.

Kelebihan : Alternatif yang baik untuk fungsi Lambda tersuai di Excel 2019, Excel 2016 dan versi terdahulu

Kelemahan : Buku kerja mesti disimpan sebagai fail .xlsm yang didayakan makro

Pukal Ganti dalam Excel dengan VBA Macro

Jika anda suka mengotomatisasi tugas biasa dengan makro, maka anda boleh menggunakan kod VBA berikut untuk mencari dan menggantikan pelbagai nilai dalam julat.

Sub -bulkreplace () rng rng sebagai julat, sourcerng sebagai julat, pengganti sebagai julat pada ralat resume seterusnya set sourcerng = application.InputBox ( "sumber data:" , " pengganti pukal" , application.selection.address , type : = 8 ) err.clear jika tidak sourcerng tidak Tidak pengganti tidak ada apa -apa daripada aplikasi.screenupdating = false untuk setiap rng dalam pengganting.columns (1) .cells sourcerng.replace apa: = rng.value, penggantian : = rng.offset (0, 1 ) .

Untuk menggunakan makro dengan segera, anda boleh memuat turun buku kerja sampel kami yang mengandungi kod. Atau anda boleh memasukkan kod dalam buku kerja anda sendiri.

Cara menggunakan makro

Sebelum menjalankan makro, taipkan nilai lama dan baru ke dalam dua lajur bersebelahan seperti yang ditunjukkan dalam imej di bawah (C2: D4).

Dan kemudian, pilih data sumber anda, tekan Alt F8 , pilih makro bulkreplace , dan klik Run .

Oleh kerana kemarahan sumber dipilih, hanya sahkan rujukan, dan klik OK:

Selepas itu, pilih julat ganti , dan klik OK:

Selesai!

Kelebihan : Persediaan sekali, gunakan semula bila-bila masa

Kelemahan : Makro perlu dijalankan dengan setiap perubahan data

Pelbagai Cari dan Ganti dalam Excel dengan Alat Substring

Dalam contoh pertama, saya menyebut bahawa pengganti bersarang adalah cara paling mudah untuk menggantikan pelbagai nilai dalam Excel. Saya mengakui bahawa saya salah. Suite utama kami menjadikan perkara lebih mudah!

Untuk melakukan penggantian massa dalam lembaran kerja anda, pergi ke tab Data Cabebits dan klik Alat Substring > Gantikan Substrings .

Kotak dialog Ganti Substrings akan muncul meminta anda untuk menentukan julat sumber dan substrings .

Dengan kedua -dua julat yang dipilih, klik butang Ganti dan cari hasil dalam lajur baru yang dimasukkan ke kanan data asal. Ya, itu mudah!

Petua. Sebelum mengklik Ganti , ada satu perkara penting untuk anda pertimbangkan - kotak sensitif kes . Pastikan anda memilih jika anda ingin mengendalikan huruf besar dan huruf kecil sebagai aksara yang berbeza. Dalam contoh ini, kami menandakan pilihan ini kerana kami hanya mahu menggantikan rentetan yang dipermodalkan dan meninggalkan substrings seperti "fr", "UK", atau "Ak" dalam kata lain yang utuh.

Jika anda ingin tahu apa operasi pukal lain boleh dilakukan pada rentetan, lihat alat substring lain yang disertakan dengan suite muktamad kami. Atau lebih baik, muat turun versi penilaian di bawah dan cubalah!

Itulah cara mencari dan menggantikan beberapa perkataan dan aksara sekaligus dalam Excel. Saya mengucapkan terima kasih kerana membaca dan berharap dapat melihat anda di blog kami minggu depan!

Muat turun yang ada

Pelbagai Cari dan Ganti dalam Excel (.xlsm File) Suite Ultimate Suite 14-Day Flought Version (.exe File)

Atas ialah kandungan terperinci Excel: Cari dan ganti pelbagai nilai sekaligus. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!