Excel快速批量替換區域中符合條件的數值
在Excel中有時需要將區域中符合某種條件的數值全部替換為相同的文本,如將下圖中小於「60」的成績全部替換為「不及格」。由於符合條件的數值多種多樣,似乎無法直接用「替換」功能全部替換。這種情況下 ,可用下面的一些方法來實現批量替換。以下圖為例,假如數值在B2:J34區域。
方法一:輔助區域+公式法
將B2:J34區域中的數值複製粘貼到一個空白區域,如L2:T34,在B2中輸入公式:
=IF(L2
然後拖動填充柄向右向下填充公式。
將B2:J34區域選擇性粘貼為數值,最後刪除輔助區域。這個方法比較直觀,只是輸入公式時要小心,不能讓數值錯位。
方法二:輔助區域+定位
這也許不是最快捷的方法,但其中的「定位」技巧值得一提。步驟如下:
1.在工作表中某個空白區域的左上角,如L2單元格中輸入公式:
=IF(B2
然後拖動填充柄向右向下填充公式,填充區域的行數和列數與B2:J34相同。該公式的作用是將B2:J34區域中小於「60」的數值在輔助區域的對應位置處顯示為錯誤值,以便定位查找。
2.保持輔助區域的選擇狀態,按F5鍵打開「定位」對話框,單擊「定位條件」按鈕,彈出「定位條件」對話框,選擇「公式」,去掉「數字」、「文本」、「邏輯值」三個選項,僅保留「錯誤」選項,單擊確定。
這時輔助區域中的錯誤值將全部被選中。
3.再次按F5鍵,在「定位條件」對話框中選擇「引用單元格」後單擊確定。
此時B2:J34區域中小於「60」的單元格會被全部選擇,這些單元格即輔助區域中返回錯誤值的公式所引用的單元格。在編輯欄中輸入「不及格」,按「Ctrl+回車鍵」將所有小於「60」的數值全部替換為「不及格」。
方法三:利用「查找和替換」對話框
Excel「查找和替換」雖沒有類似條件格式、篩選等設置條件的功能,但在對話框中可以進行簡單的排序,利用這一點可方便我們選擇符合條件的單元格。步驟如下:
1.選擇B2:J34區域,按 Ctrl+H 打開「查找和替換」對話框,在「查找內容」後輸入「*」,單擊「查找全部」按鈕,Excel會找到所有包含數值的單元格。
可以看到上述對話框中「值」列數值的排列是沒有規律的。此時單擊對話框中部標題行的「值」按鈕,「值」列的數值排序方式會變為升序,這樣就可以方便地進行手動選擇。
在對話框中單擊選擇第一個值小於「60」單元格。
然後向下拖動滾動條,按住Shift鍵選擇對話框中最後一個小於「60」的單元格,B2:J34區域中所有數值小於「60」的單元格就都被選中了。
2.在「查找和替換」對話框的「替換為」後輸入「不及格」,單擊「全部替換」即可。
方法四:自定義數字格式
這種方法利用自定義數字格式讓小於「60」的數值在單元格中顯示為「不及格」,單元格中的數值實際並沒有改變,也可視作一種「替換」。方法是:
1.選擇B2:J34區域滑鼠右擊,依次選擇「設置單元格格式數字自定義」。在「類型」下輸入:
[
2.單擊「確定」。
方法五:VBA法
按Alt+F11,打開VBA編輯器,在代碼窗口中粘貼下面的代碼:
Sub tihuan()
Dim rCell As Range
For Each rCell In Selection
If rCell.Value < 60 Then rCell.Value = "不及格"
Next
End Sub
選擇B2:J34,然後運行上述代碼也可將區域中小於「60」的數值替換為「不及格」。
![](https://pic.pimg.tw/zzuyanan/1488615166-1259157397.png)
![](https://pic.pimg.tw/zzuyanan/1482887990-2595557020.jpg)
TAG:菜鳥玩數據 |