excel如何用宏替换某列中的几个内容-excel宏替换,excel多对多批量替换

一、重复值不计数，百在B1输入度=SUMPRODUCT((COUNTIF($A$1:$A$10,$A$1:$A$10)=1)*(A1:A10"")) 如图：

二、重复值计1次数，在内B1输入数组公式=SUM(1/COUNTIF(A1:A10,A1:A10)) 同时按Ctrl+Shift+回车完容成，如图：

Public Sub replace()
For n = 1 To [H65536].End(xlUp).Row
Select Case Cells(n, "H")
Case Is = ""
Cells(n, "H") = "江西zhidao"
Case Is = "beijing"
Cells(n, "H") = "北京版"
Case Is = "shanghai"
Cells(n, "H") = "上海权"
End Select
Next n
End Sub

鼠标移至工作表标签名处来，右键，查看代码，将源下列代码粘贴进去
Sub 替换()
For i = 1 To [H65536].End(xlUp).Row
Cells(i, 8).Replace What:="beijing", Replacement:="北京"
Cells(i, 8).Replace What:="shanghai", Replacement:="上海"
Cells(i, 8).Replace What:="xinjiang", Replacement:="新疆"
Cells(i, 8).Replace What:="", Replacement:="江西"
Next
End Sub
退出zhidaoVB窗口，ALT+F8，运行替换宏。