在本教程中，我們將研究幾種方法來查找和替換多個(gè)單詞，字符串或單個(gè)角色，因此您可以選擇最適合您需求的單詞。

人們通常如何在Excel中搜索？通常，通過使用查找和替換功能，該功能適用??於單個(gè)值。但是，如果您有數(shù)十個(gè)甚至數(shù)百個(gè)物品要替換怎麼辦？當(dāng)然，沒有人願意一對一手動進(jìn)行所有這些替換，然後在數(shù)據(jù)更改時(shí)再次進(jìn)行。幸運(yùn)的是，有一些更有效的方法可以在Excel中進(jìn)行質(zhì)量替換，我們將詳細(xì)調(diào)查它們。

用嵌套替換查找並替換多個(gè)值

在Excel中查找和替換多個(gè)條目的最簡單方法是使用替代功能。

公式的邏輯非常簡單：您編寫一些單獨(dú)的函數(shù)將舊值替換為新值。然後，您將這些功能嵌套在另一個(gè)功能中，以便每個(gè)後續(xù)替代品都使用上一個(gè)替代的輸出來尋找下一個(gè)值。

替代（替代（替代（ text ， old_text1 ， new_text1 ）， old_text2 ， new_text2 ）， old_text3 ， new_text3 ）

在A2：A10的位置列表中，假設(shè)您想用全名替換縮寫的國家名稱（例如FR ， UK和USA ）。

要完成它，請?jiān)贒2：D4中輸入舊值以及E2：E4中的新值，如下屏幕截圖所示。然後，將以下公式放入B2中，然後按Enter：

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

…您將立即完成所有替代品：

請注意，上述方法僅在支持動態(tài)數(shù)組的Excel 365中起作用。

在Excel 2019，Excel 2016和更早的動態(tài)版本中，需要為最上方的細(xì)胞（B2）編寫該公式，然後復(fù)製到以下細(xì)胞中：

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

請注意，在這種情況下，我們將使用絕對單元格引用鎖定替換值，因此它們在將公式放下時(shí)不會移動。

筆記。替代函數(shù)對大小寫敏感，這意味著您應(yīng)該在與原始數(shù)據(jù)中顯示的同一字母案例中鍵入舊值（ old_text ）。

儘管可能很容易，但此方法具有很大的缺點(diǎn) - 當(dāng)您有數(shù)十個(gè)要替換的項(xiàng)目時(shí)，嵌套功能變得很難管理。

優(yōu)點(diǎn)：易於實(shí)現(xiàn)；在所有Excel版本中支持

缺點(diǎn)：最好用於有限數(shù)量的查找/替換值

用Xlookup搜索並替換多個(gè)條目

在您希望替換整個(gè)單元格內(nèi)容而不是其部分時(shí)，Xlookup功能派上用場。

假設(shè)您有A列中的國家 /地區(qū)列表，目的是用相應(yīng)的全名代替所有縮寫。與上一個(gè)示例一樣，您從在單獨(dú)的列中輸入“查找”和“替換”項(xiàng)目（分別為d和e），然後在B2中輸入此公式：

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

從Excel語言翻譯成人類語言，這是公式的作用：

在D2：D4（Lookup_array）中搜索A2值（Lookup_value），然後從E2：E4（return_array）返回匹配項(xiàng)。如果找不到，請從A2中摘取原始值。

雙擊填充手柄以將公式複製到以下單元格中，結(jié)果不會讓您等待：

由於Xlookup功能僅在Excel 365中可用，因此上述公式在較早版本中無法使用。但是，您可以通過IFERROR或IFNA和VLOOKUP的組合輕鬆模仿這種行為：

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

筆記。與替代品不同，Xlookup和Vlookup功能不是對大小寫的敏感性，這意味著他們搜索忽略字母情況的查找值。例如，我們的公式將用法國代替FR和FR 。

優(yōu)點(diǎn)：不尋常的使用通常功能；在所有Excel版本中使用

缺點(diǎn)：在單元格級別上工作，無法替換部分單元內(nèi)容

使用遞歸lambda功能多次替換

對於Microsoft 365訂戶，Excel提供了一個(gè)特殊的功能，該功能允許使用傳統(tǒng)公式語言創(chuàng)建自定義功能。是的，我說的是Lambda。這種方法的優(yōu)點(diǎn)在於，它可以將非常冗長且複雜的公式轉(zhuǎn)換為非常緊湊且簡單的公式。此外，它使您可以創(chuàng)建自己不存在Excel中的功能，這僅在VBA中才可能。

有關(guān)創(chuàng)建和使用自定義Lambda功能的詳細(xì)信息，請查看本教程：如何在Excel中編寫lambda函數(shù)。在這裡，我們將討論幾個(gè)實(shí)際示例。

優(yōu)點(diǎn)：結(jié)果是一種優(yōu)雅且令人驚訝的功能，無論替換對數(shù)量多少

缺點(diǎn)：僅在Excel 365中可用；特定於工作簿，不能在不同的工作簿中重複使用

示例1。一次搜索並替換多個(gè)單詞 /字符串

為了一次替換多個(gè)單詞或文本，我們創(chuàng)建了一個(gè)自定義的lambda函數(shù)，名為MultirePlace ，可以採用以下形式之一：

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

或者

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

兩者都是自稱的遞歸功能。區(qū)別僅在於如何建立出口點(diǎn)。

在第一個(gè)公式中，IF函數(shù)檢查舊列表是否不是空白（舊“”）。如果為true，則調(diào)用多隔板函數(shù)。如果false，該函數(shù)將其當(dāng)前形式和退出返回文本。

第二公式使用反向邏輯：如果舊的是空白（old =“”），則返回文本和退出；否則請致電MultirePlace 。

最棘手的部分是完成的！您要做的就是在名稱管理器中命名多隔板函數(shù)，如下面的屏幕截圖所示。有關(guān)詳細(xì)指南，請查看如何命名lambda功能。

功能獲得名稱後，您可以像其他任何內(nèi)置功能一樣使用它。

從最終用戶的角度來看，您選擇的兩個(gè)公式變化中的哪個(gè)，語法都一樣簡單：

Multireplace（文本，舊，新）

在哪裡：

• 文本- 源數(shù)據(jù)
• 舊- 找到的價(jià)值觀
• 新- 替換為

以先前的例子為例，讓我們不僅取代國家的縮寫，還要取代國家的縮寫。為此，在d2開始鍵入D列中的縮寫（舊值），在E2中開始E列E中的全名（新值）。

在B2中，輸入多臺詞函數(shù)：

=MultiReplace(A2:A10, D2, E2)

點(diǎn)擊Enter並享受結(jié)果:)

該公式如何工作

理解該公式的線索是理解遞歸。這聽起來可能很複雜，但是原理很簡單。在每次迭代中，遞歸函數(shù)解決了一個(gè)更大問題的一個(gè)小實(shí)例。在我們的情況下，多隔板函數(shù)通過舊值和新值循環(huán)，並且每個(gè)循環(huán)都執(zhí)行一個(gè)替代：

多動(SUBSTITUTE(text, old, new), OFFSET(old, 1, 0), OFFSET(new, 1, 0))

與嵌套替代功能一樣，先前替代的結(jié)果成為下一個(gè)替代的文本參數(shù)。換句話說，在每個(gè)隨後的呼叫中，替代功能不是原始文本字符串，而是上一個(gè)呼叫的輸出。

要處理舊列表中的所有項(xiàng)目，我們從最上方的單元格開始，並使用偏移函數(shù)在每次交互時(shí)向下移動1行：

OFFSET(old, 1, 0)

新列表也是如此：

OFFSET(new, 1, 0)

關(guān)鍵的事情是提供一個(gè)退出點(diǎn)，以防止遞歸電話永遠(yuǎn)進(jìn)行。它是在IF函數(shù)的幫助下完成的 - 如果舊單元格為空，則該函數(shù)將其返回文本其當(dāng)前形式並退出：

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

或者

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

示例2。在Excel中替換多個(gè)字符

原則上，前一個(gè)示例中討論的多隔板函數(shù)也可以處理單個(gè)字符，前提是每個(gè)舊字符都在單獨(dú)的單元格中輸入，就像上面的屏幕截圖中的縮寫和完整名稱一樣。

如果您想在一個(gè)單元格中輸入舊字符和另一個(gè)單元格中的新字符，或者將它們直接在公式中鍵入，則可以通過使用以下公式之一創(chuàng)建另一個(gè)名為替代的自定義函數(shù)：

=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))

或者

=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))))

請記住，以往常為名將您的新lambda函數(shù)命名：

替換函數(shù)“ title =”名稱一個(gè)自定義替換式函數(shù)>

並且您的新自定義功能已準(zhǔn)備就緒：

替換（文本，old_chars，new_chars）

在哪裡：

• 文字- 原始字符串
• 舊- 要搜索的角色
• 新的- 字符要替換為

為了進(jìn)行現(xiàn)場測試，讓我們做一些經(jīng)常在導(dǎo)入的數(shù)據(jù)上執(zhí)行的事情 - 用直引號和直接撇號替換智能報(bào)價(jià)和智能撇號。

首先，我們在E2中輸入智能引號和智能撇號，直接引號和直撇號，將字符的空間分開，以獲得更好的可讀性。 （當(dāng)我們在兩個(gè)單元格中使用相同的定界符時(shí)，它不會對結(jié)果產(chǎn)生任何影響 - excel只會用一個(gè)空間代替一個(gè)空間。）

之後，我們在B2中輸入此公式：

=ReplaceChars(A2:A4, D2, E2)

並準(zhǔn)確獲取我們正在尋找的結(jié)果：

也可以直接在公式中鍵入字符。在我們的情況下，只記得“複製”這樣的直言：

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

該公式如何工作

替代方案的功能通過舊_chars和new_chars字符串循環(huán)，並從左側(cè)的第一個(gè)字符開始一次替換。該部分由替代功能完成：

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

在每次迭代中，右功能都從舊_chars和new_chars字符串的左側(cè)剝離一個(gè)字符，因此左側(cè)可以拿出下一個(gè)字符以替換：

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

在每個(gè)遞歸調(diào)用之前，IF函數(shù)評估old_chars字符串。如果不是空的，則該函數(shù)會自動調(diào)用。一旦取代了最後一個(gè)字符，迭代過程就完成了，該公式將返回文本其當(dāng)前形式和退出。

筆記。由於我們的核心公式中使用的替代功能對病例敏感，因此lambdas（多部和替換）都將大寫和小寫字母視為不同的字符。

群眾查找並用UDF??替換

如果您的Excel中沒有Lambda功能，則可以使用VBA以傳統(tǒng)方式編寫用戶定義的功能。

為了將UDF與lambda定義的多隔板功能區(qū)分開，我們將以不同的命名，例如MassReplace 。該函數(shù)的代碼如下：

函數(shù)subsreplace （ inputrng作為範(fàn)圍， findrng為範(fàn)圍，替換為範(fàn)圍）為變量（ ） iInputCurrow，iinputCurcol，cntinputrows，cntinputCols在源範(fàn)圍內(nèi)當(dāng)前行的索引，當(dāng)前列的索引，源範(fàn)圍中的當(dāng)前列的索引，行計(jì)數(shù)，行計(jì)數(shù)，列計(jì)數(shù)，列計(jì)數(shù)redim rarre（1至cntinputrows，1至cntinputcols） redim arsearchReplace（1至cntfindrows，1至2）'準(zhǔn)備find/replease for fin/替換成對的ifindcurrow = 1 to cntfindrows arsearchreplace arsearchreplace（ifindcurrow，1）= findrng.cells（ifIndrng.celliue）（1） ArsearchReplace（Ifindcurrow，2）= replaceNg.cells（IfindCurrow，1）。接下來的'search'search和更換iinputCurrow = 1 to cntinputrows to cntinputrows for cntinputrows for iinputCurcol = 1 to cntinputcols to cntinputcols stmp = inputrng.celllng.celllng.cellcurlcurlcurrancorlcurrafcurrafcurrafcurncorlcurncorlcurncurrofcurrofcurrofcurrofcurrofcurrofcurrofrow。 '替換每個(gè)單元格中的所有查找/替換對ifindcurrow = 1 = 1 to cntfindrows stmp = replace（stmp，arsearchReplace（ifindcurrow，1），arsearchReplace（arsearchReplace（ifindcurrow，2））下一個(gè)arres （iinputcurrow，iinputcurcol，iinputcurcol ， iinputcurcol ）

像Lambda定義的功能一樣，UDF在範(fàn)圍內(nèi)。這意味著只有在您插入代碼的工作簿中， MassReplace功能才能使用。如果您不確定如何正確執(zhí)行此操作，請按照如何在Excel中插入VBA代碼中所述的步驟。

一旦將代碼添加到您的工作簿中，該函數(shù)將出現(xiàn)在公式IntelliSense中 - 僅函數(shù)的名稱，而不是參數(shù)！不過，我相信記住語法沒什麼大不了的：

massReplace（input_range，find_range，replace_range）

在哪裡：

• input_range-要替換值的源範(fàn)圍。
• find_range-字符，字符串或單詞要搜索。
• replace_range-字符，字符串或單詞要替換為。

在Excel 365中，由於支持動態(tài)陣列，這是正常公式的作用，僅需要輸入頂部單元格（B2）：

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

在預(yù)先動態(tài)的excel中，這是一個(gè)老式的CSE數(shù)組公式：您選擇整個(gè)源範(fàn)圍（b2：b10），鍵入公式，然後按CTRL Shift同時(shí)輸入鍵以完成它。

優(yōu)點(diǎn)：Excel 2019，Excel 2016和更早版本的自定義Lambda功能的不錯的替代品

缺點(diǎn)：必須將工作簿保存為宏啟用.xlsm文件

散裝用VBA宏代替Excel

如果您喜歡使用宏自動化常見任務(wù)，則可以使用以下VBA代碼在範(fàn)圍內(nèi)查找和替換多個(gè)值。

sub bulkreplace（） dim rng作為範(fàn)圍，作為範(fàn)圍的sourcerng ，替換為錯誤恢復(fù)範(fàn)圍下一個(gè)set sec sourcerng = application.inputbox （ “源數(shù)據(jù)：” ， “ bulk替換” ，應(yīng)用程序。替換是什麼，然後應(yīng)用。ScreenupDation = forse for for for for fors for for fors for for plactacerng.columns （1） .cells sourcerng.replace what what：= rng.value，替換：= rng.offset ： = rng.offset（0，1）。

要立即使用宏，您可以下載包含代碼的示例工作簿。或者，您可以在自己的工作簿中插入代碼。

如何使用宏

在運(yùn)行宏之前，將舊值和新值鍵入兩個(gè)相鄰列，如下圖所示（C2：D4）。

然後，選擇您的源數(shù)據(jù)，按Alt F8 ，選擇Bulkreplace宏，然後單擊“運(yùn)行” 。

當(dāng)源憤怒被預(yù)選時(shí)，只需驗(yàn)證參考，然後單擊“確定：

之後，選擇替換範(fàn)圍，然後單擊確定：

完畢！

優(yōu)點(diǎn)：設(shè)置一次，隨時(shí)重複使用

缺點(diǎn)：每個(gè)數(shù)據(jù)都需要運(yùn)行宏

用substring工具中的多個(gè)查找並替換Excel

在第一個(gè)示例中，我提到嵌套替代是替換Excel中多個(gè)值的最簡單方法。我承認(rèn)我錯了。我們的終極套房使事情變得更加容易！

要在工作表中進(jìn)行質(zhì)量替換，請轉(zhuǎn)到“ ablebits數(shù)據(jù)”選項(xiàng)卡，然後單擊子字符串工具>替換子字符串。

“替換子字”對話框?qū)⒊霈F(xiàn)，要求您定義源範(fàn)圍和子字符串範(fàn)圍。

選擇兩個(gè)範(fàn)圍，單擊“替換”按鈕，然後在原始數(shù)據(jù)右側(cè)插入的新列中找到結(jié)果。是的，很容易！

提示。在單擊替換之前，您需要考慮的一件重要的事情 -對案例敏感的框。如果您想處理大寫和小寫字母作為不同的字符，請務(wù)必選擇它。在此示例中，我們會打勾此選項(xiàng)，因?yàn)槲覀冎幌胩鎿Q大寫字母，並在完整的詞中留下諸如“ fr”，“ uk”或“ ak”之類的子字符串。

如果您想知道在字符串上可以執(zhí)行哪些其他批量操作，請查看我們的Ultimate Suite中包含的其他子字符串工具。甚至更好，請?jiān)谙旅嫦螺d評估版本，然後嘗試一下！

這就是如何在Excel中一次找到和替換多個(gè)單詞和字符的方法。我感謝您閱讀，並希望下週在我們的博客上見到您！

可用下載

多個(gè)查找並替換在Excel（.xlsm File）Ultimate Suite 14天完全功能版本（.EXE文件）中

以上是Excel：一次查找並替換多個(gè)值的詳細(xì)內(nèi)容。更多資訊請關(guān)注PHP中文網(wǎng)其他相關(guān)文章！