Excel：一次查找並替換多個值

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

人們通常如何在Excel中搜索？通常，通過使用查找和替換功能，該功能適用​​於單個值。但是，如果您有數十個甚至數百個物品要替換怎麼辦？當然，沒有人願意一對一手動進行所有這些替換，然後在數據更改時再次進行。幸運的是，有一些更有效的方法可以在Excel中進行質量替換，我們將詳細調查它們。

• 嵌套替代公式以替換多個值
• Xlookup公式搜索多個單詞
• 使用遞歸lambda功能的多個重複位置
• 群眾查找並用UDF​​替換
• 散裝宏觀宏以查找和替換多個值
• 多次查找並使用子字符串工具替換

用嵌套替換查找並替換多個值

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

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

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

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

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

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

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

請注意，上述方法僅在支持動態數組的Excel 365中起作用。

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

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

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

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

儘管可能很容易，但此方法具有很大的缺點 - 當您有數十個要替換的項目時，嵌套功能變得很難管理。

優點：易於實現；在所有Excel版本中支持

缺點：最好用於有限數量的查找/替換值

用Xlookup搜索並替換多個條目

在您希望替換整個單元格內容而不是其部分時，Xlookup功能派上用場。

假設您有A列中的國家 /地區列表，目的是用相應的全名代替所有縮寫。與上一個示例一樣，您從在單獨的列中輸入“查找”和“替換”項目（分別為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）返回匹配項。如果找不到，請從A2中摘取原始值。

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

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

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

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

優點：不尋常的使用通常功能；在所有Excel版本中使用

缺點：在單元格級別上工作，無法替換部分單元內容

使用遞歸lambda功能多次替換

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

有關創建和使用自定義Lambda功能的詳細信息，請查看本教程：如何在Excel中編寫lambda函數。在這裡，我們將討論幾個實際示例。

優點：結果是一種優雅且令人驚訝的功能，無論替換對數量多少

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

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

為了一次替換多個單詞或文本，我們創建了一個自定義的lambda函數，名為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))))

兩者都是自稱的遞歸功能。區別僅在於如何建立出口點。

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

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

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

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

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

Multireplace（文本，舊，新）

在哪裡：

• 文本- 源數據
• 舊- 找到的價值觀
• 新- 替換為

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

在B2中，輸入多台詞函數：

=MultiReplace(A2:A10, D2, E2)

點擊Enter並享受結果:)

該公式如何工作

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

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

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

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

OFFSET(old, 1, 0)

新列表也是如此：

OFFSET(new, 1, 0)

關鍵的事情是提供一個退出點，以防止遞歸電話永遠進行。它是在IF函數的幫助下完成的 - 如果舊單元格為空，則該函數將其返回文本其當前形式並退出：

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

或者

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

示例2。在Excel中替換多個字符

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

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

=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函數命名：

替換函數“ title =”名稱一個自定義替換式函數>

並且您的新自定義功能已準備就緒：

替換（文本，old_chars，new_chars）

在哪裡：

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

為了進行現場測試，讓我們做一些經常在導入的數據上執行的事情 - 用直引號和直接撇號替換智能報價和智能撇號。

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

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

=ReplaceChars(A2:A4, D2, E2)

並準確獲取我們正在尋找的結果：

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

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

該公式如何工作

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

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

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

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

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

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

群眾查找並用UDF​​替換

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

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

函數subsreplace （ inputrng作為範圍， findrng為範圍，替換為範圍）為變量（ ） iInputCurrow，iinputCurcol，cntinputrows，cntinputCols在源範圍內當前行的索引，當前列的索引，源範圍中的當前列的索引，行計數，行計數，列計數，列計數redim rarre（1至cntinputrows，1至cntinputcols） redim arsearchReplace（1至cntfindrows，1至2）'準備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。 '替換每個單元格中的所有查找/替換對ifindcurrow = 1 = 1 to cntfindrows stmp = replace（stmp，arsearchReplace（ifindcurrow，1），arsearchReplace（arsearchReplace（ifindcurrow，2））下一個arres （iinputcurrow，iinputcurcol，iinputcurcol ， iinputcurcol ）

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

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

massReplace（input_range，find_range，replace_range）

在哪裡：

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

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

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

在預先動態的excel中，這是一個老式的CSE數組公式：您選擇整個源範圍（b2：b10），鍵入公式，然後按CTRL Shift同時輸入鍵以完成它。

優點：Excel 2019，Excel 2016和更早版本的自定義Lambda功能的不錯的替代品

缺點：必須將工作簿保存為宏啟用.xlsm文件

散裝用VBA宏代替Excel

如果您喜歡使用宏自動化常見任務，則可以使用以下VBA代碼在範圍內查找和替換多個值。

sub bulkreplace（） dim rng作為範圍，作為範圍的sourcerng ，替換為錯誤恢復範圍下一個set sec sourcerng = application.inputbox （ “源數據：” ， “ bulk替換” ，應用程序。替換是什麼，然後應用。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）。

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

如何使用宏

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

然後，選擇您的源數據，按Alt F8 ，選擇Bulkreplace宏，然後單擊“運行” 。

當源憤怒被預選時，只需驗證參考，然後單擊“確定：

之後，選擇替換範圍，然後單擊確定：

完畢！

優點：設置一次，隨時重複使用

缺點：每個數據都需要運行宏

用substring工具中的多個查找並替換Excel

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

要在工作表中進行質量替換，請轉到“ ablebits數據”選項卡，然後單擊子字符串工具>替換子字符串。

“替換子字”對話框將出現，要求您定義源範圍和子字符串範圍。

選擇兩個範圍，單擊“替換”按鈕，然後在原始數據右側插入的新列中找到結果。是的，很容易！

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

如果您想知道在字符串上可以執行哪些其他批量操作，請查看我們的Ultimate Suite中包含的其他子字符串工具。甚至更好，請在下面下載評估版本，然後嘗試一下！

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

可用下載

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

以上是Excel：一次查找並替換多個值的詳細內容。更多資訊請關注PHP中文網其他相關文章！