Excel格式重复值 - 更改单元格中的文本 [英] Excel Format duplicate values - change the text in the cell

问题描述

使用条件格式格式化具有重复值的单元格（例如设置其背景或其他样式）非常容易，但是如何更改文本？

例如：

A1 2332

A2 2333

A3 2334

A4 2334

成为：

A1 2332

A2 2333（2）

$ b

解决方案

这样做的一个方法是在原始数据旁边添加一个第二列，其中填写下列公式： / p>

  = IF（COUNTIF（$ A $ 1：$ A $ 5000，A1）> 1，A1&（& （A $ 1：A1，A1）&），A1）
  

在A1：A5000。请注意， COUNTIF 非常低效，所以如果你有大量的数据，这可能需要一段时间来计算和影响你的工作簿性能。

对于大型工作簿，我会考虑使用VBA Worksheet_Change事件来编辑值。该代码应插入相应的Worksheet模块中。在5000个测试记录上，它有几秒钟的滞后。

  Option Explicit 
 Private Sub Worksheet_Change（ByVal Target As Range） 
 
 Dim dataRng As Range 
 Dim dataArr（）As Variant，output（）As String 
 Dim y As Long，i As Long，j As Long，tmpcount As Long 
 
'将A1更改为要索引的列的起始地址。 
设置dataRng =范围（A1）。调整大小（Me.UsedRange.Rows.Count，1）
如果不相交（目标，数据Rng）没有，则
 dataArr = dataRng.Value 
 ReDim输出（1到UBound（dataArr，1），1到1）
'从数组中的数据中删除旧的计数。 
对于y = 1对于UBound（dataArr，1）
如果右（dataArr（y，1），1）=）然后
 dataArr（y，1）= Left（dataArr （y，1），InStr（dataArr（y，1），（） -  1）
结束如果
下一个y 
 
对于i = 1 To UBound（dataArr ，1）
 tmpcount = 0 
输出（i，1）= dataArr（i，1）
对于j = 1到UBound（dataArr，1）
如果dataArr ，1）= dataArr（j，1）Then 
 tmpcount = tmpcount + 1 
如果j = i和tmpcount> 1然后
输出（i，1）= dataArr（i，1 ）$
退出
结束If 
如果j> i和tmpcount> 1然后
输出（i，1 ）= dataArr（i，1）&（& tmpcount  -  1&）
退出
结束If 
结束If 
下一步j 
 Next i 
调用打印输出（output，dataRng）
结束如果
 
结束Sub 
 
 
私有子打印输出（什么As Variant，其中As范围）
 Application.EnableEvents = False 
 where.Value = what 
 Application.EnableEvents = True 
 End Sub 
  

小心我做了几个很大的假设：

1. 我假设要在A1开始索引的列。如果它在另一列中，则需要调整代码的第7行。


2. 假设您的数据永远不会以）结尾，除非先前已被索引。如果不是这种情况，请远离此代码！

It is very easy to format the cells that have duplicated values (like setting specific background on them or something other style) using the "Conditional formatting", but how can I change their text?

For example:

A1 2332

A2 2333

A3 2334

A4 2334

to become:

A1 2332

A2 2333

A3 2334(1)

A4 2334(2)

解决方案

One way to do this is to just add a second column next to your original data with the following formula filled down:

=IF(COUNTIF($A$1:$A$5000,A1)>1,A1& " (" & COUNTIF(A$1:A1,A1) & ")",A1)

where your original data is in A1:A5000. Beware that COUNTIF is pretty inefficient, so if you have a lot of data, this could take a while to calculate and affect your workbook performance.

For a large workbook, I'd consider using a VBA Worksheet_Change event to edit the values in place. This code should be inserted in the appropriate Worksheet module. On 5000 test records it had a couple second lag.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim dataRng As Range
Dim dataArr() As Variant, output() As String
Dim y As Long, i As Long, j As Long, tmpcount As Long

'Change "A1" to address of start of column you want to index.
Set dataRng = Range("A1").Resize(Me.UsedRange.Rows.Count, 1)
If Not Intersect(Target, dataRng) Is Nothing Then
    dataArr = dataRng.Value
    ReDim output(1 To UBound(dataArr, 1), 1 To 1)
    'Strip old counts from data once in array.
    For y = 1 To UBound(dataArr, 1)
        If Right(dataArr(y, 1), 1) = ")" Then
            dataArr(y, 1) = Left(dataArr(y, 1), InStr(dataArr(y, 1), " (") - 1)
        End If
    Next y

    For i = 1 To UBound(dataArr, 1)
        tmpcount = 0
        output(i, 1) = dataArr(i, 1)
        For j = 1 To UBound(dataArr, 1)
            If dataArr(i, 1) = dataArr(j, 1) Then
                tmpcount = tmpcount + 1
                If j = i And tmpcount > 1 Then
                    output(i, 1) = dataArr(i, 1) & " (" & tmpcount & ")"
                    Exit For
                End If
                If j > i And tmpcount > 1 Then
                    output(i, 1) = dataArr(i, 1) & " (" & tmpcount - 1 & ")"
                    Exit For
                End If
            End If
        Next j
    Next i
    Call printoutput(output, dataRng)
End If

End Sub


Private Sub printoutput(what As Variant, where As Range)
Application.EnableEvents = False
where.Value = what
Application.EnableEvents = True
End Sub

Beware I've made a couple of big assumptions:

1. I assumed the column you want to index starts at A1. If it is in another column, you will need to adjust line 7 of the code.
2. I assumed your data will never end in ")" unless it has previously been indexed. If that's not the case, stay away from this code!

这篇关于Excel格式重复值 - 更改单元格中的文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！