操作更高效　增強(qiáng)Excel的批量替換功能

俞木發(fā)

用好模糊搜索 指定條件進(jìn)行批量替換

如果需要替換一組數(shù)據(jù)，可以借助“*”號進(jìn)行模糊查找，然后再進(jìn)行批量替換。比如下表中要將成績小于60的數(shù)據(jù)全部替換為“需補(bǔ)考”，打開“查找和替換”后在“查找內(nèi)容”框中輸入“*”，點(diǎn)擊“查找全部”，然后在下方的窗格中點(diǎn)擊“值”，這樣表中學(xué)生所有科目的成績會自動排序，按提示選中分?jǐn)?shù)小于60的數(shù)據(jù)，接著在“替換為”中輸入“需補(bǔ)考”，最后點(diǎn)擊“全部替換”即可（圖1）。

利用類似的方法，我們可以很方便地批量選中符合條件的不連續(xù)數(shù)據(jù)，比如現(xiàn)在需要將小于60分的數(shù)據(jù)全部填充為黃色，在選中所有符合條件的數(shù)據(jù)后，點(diǎn)擊“開始一填充顏色一黃色”即可。這樣，以后就可以使用“查找一格式一從單元格中選中格式”，快速找到填充色是黃色的單元格，還可以對這些單元格中的數(shù)據(jù)進(jìn)行批量替換操作，比如將字體顏色設(shè)置為紅色，或者添加邊框等操作（圖2）。

多個數(shù)據(jù) 輕松替換

上述方法適合于將多個不同的數(shù)據(jù)批量替換為一類名稱。如果有多個數(shù)據(jù)需要替換為對應(yīng)的種類，就不適用了。比如某公司由于技術(shù)標(biāo)準(zhǔn)改變，需要將原來文檔中的舊標(biāo)準(zhǔn)，如GX-ZZ-201801更改為FJGX-201801CBZZ，即將原來標(biāo)準(zhǔn)中的GX替換為FJGX，ZZ-201801替換為201 801 CBZZ，因為每個標(biāo)準(zhǔn)字符都不相同，利用Excel的查找替換效率極低。對于類似格式的字符替換，可以使用智能填充來快速實(shí)現(xiàn)。首先在B1中按照A1的替換格式輸入FJGX-201801CBZZ，接著按下Ctrl+E，這樣Excel會根據(jù)A1、B1的格式對比智能地確定內(nèi)容，并向下進(jìn)行填充，最后將A列刪除即可。這樣省去了每個字符都要查找替換的麻煩（圖3）。

如果需要替換的多個數(shù)據(jù)的格式并不相同，比如“FJ-GX01-dzi01”要替換為“FJ-GX01/zj01”（即將代碼中的第二個“-”更改為“/”）（圖4），因為第二個“-”在原始數(shù)據(jù)中的位置并不相同，而且代碼格式也不同，使用智能填充無法完成快速替換（當(dāng)然內(nèi)置替換組件也無法選擇指定“-”進(jìn)行替換），對于類似的替換可以借助于Replace函數(shù)來實(shí)現(xiàn)。在C1輸入公式“=REPLACE（A1，F(xiàn)IND（"-"，A1，6），1，"/"）”，接著向下填充即可完成替換操作（圖5）。

公式解釋：

先使用Find函數(shù)查找第二個“-”的位置，這里要注意觀察原始數(shù)據(jù)中第二個“-”的位置特性。比如在數(shù)據(jù)中第一個“-”的最小位置是在第3個字符（即FJ-），最大位置則為第5個字符（即AGER-），因此需要使用FIND（"-"A1，6），表示從A1的第6個字符開始搜索“-”的位置，這樣才能找到第二個“-”的正確位置，同時返回整數(shù)值作為Replace開始替換的位置。比如“=FIND（"-"，A1，6）”返回的數(shù)字是“8”，REPLACE（A1，8，1，"/"），表示從A1的第8個字符（即第二個“-”）開始替換，替換1個字符，替換符號是“/”。

簡單高效 使用VBA腳本快速替換

上述方法適合于單個文檔的替換，如果需要替換的文檔很多，比如在某個工作簿中原來有很多中文名稱，如“一分部”、“二分部”等，由于錄入管理系統(tǒng)無法識別中文，現(xiàn)在需要將所有中文使用相應(yīng)的拼音替代。這時，使用上述函數(shù)替換就不方便，可以借助VBA腳本進(jìn)行快速替換。

首先整理好需要替換的所有字符，在當(dāng)前工作表的名稱上右擊并選擇“查看代碼”，打開VB編輯窗口，在第一個下拉框中選擇Worksheet，第二個下拉框中選擇BeforeDoubleClick，在其中輸入圖中顯示的代碼（實(shí)際使用時請自行替換字符，每個需替換字符一行，代碼下載：http：∥dwz.date/bU E5，提取碼：kubg），點(diǎn)擊“運(yùn)行”就可以完成所有替換（圖6）。

由于VB支持正則表達(dá)式，我們可以靈活地使用它來進(jìn)行各種替換。比如為了便于及時記賬，公司食堂采購人員經(jīng)常使用手機(jī)便簽，使用諸如“2020.06.03購買冬瓜123元”的格式記賬，這些記錄導(dǎo)入電腦后，需要使用時間和金額格式進(jìn)行替換（圖7）。為此，先到http：∥suo.im/510iHg（提取碼：pxyf）下載所需的代碼，打開VB編輯窗口，輸入下載到的代碼，點(diǎn)擊“運(yùn)行”即可。也可以將其保存為宏工作簿使用，這樣以后需要替換類似的數(shù)據(jù)時，只要點(diǎn)擊“開發(fā)工具→宏→數(shù)據(jù)轉(zhuǎn)換”即可（圖8）。