☰

突出显示重复的列对并计算行数 Excel

tag-icon • tag-icon microsoft-excel worksheet-function duplicate highlighting multiple-instances
突出显示重复的列对并计算行数 Excel

给定以下数据，应突出显示至少连续 4 行具有相同值的列对。

这是一个图像以便更好地进行可视化：

2   2
3   4
3   4
3   4
3   4
2   3
1   2
2   2
3   3
3   3
3   3
3   3
2   3
2   3
2   3
2   3
2   2
3   4
3   4
3   4
3   4
3   4

输出应该是这样的，其中至少 4 个连续行相同的列对值被突出显示：

2   2
3   4
3   4
3   4
3   4
2   3
1   2
2   2
3   3
3   3
3   3
3   3
2   3
2   3
2   3
2   3
2   2
3   4
3   4
3   4
3   4
3   4

然后，我需要知道 N 个连续相等列对的实例数。考虑到上述数据，N=4 应该是 3，N=5 应该是 1，其中 N 是列对连续相等的行数。

答案1

正如我在评论中提到的，我认为这有点超出了条件格式的能力。

因此，我提交以下 Excel 宏供您阅读，该宏可将其转换为

前

进入这个

后

我对此不承担任何责任。将要需要根据具体情况进行微调（行和列的起始数字等），如果它产生了一个黑洞，你的计算机就会消失得无影无踪。某物那么我只想说：“哇，你用相机拍到了这个吗？”因为那一定非常棒。

话虽如此，它对我来说还是有用的，如果你设置正确，它对你来说也应该有用。或者你可以修改它以更好地满足你的需求。

Sub RepeatedRows()
'Written by Mokubai, 14/10/12
FirstColumn = "A"
LastColumn = "B"
StartRow = 2

' First we establish how far we are going down the table
NumRows = ActiveSheet.UsedRange.Rows.Count
NumCols = ActiveSheet.UsedRange.Columns.Count

'Then we start working out
RowString = ""
oldRowString = ""
numRepeated = 0

For i = StartRow To NumRows
    FirstCell = FirstColumn & i
    'FirstCell = "A1"
    LastCell = LastColumn & i
    'Create a temporary string that is the row contents
    For Each Cell In Range(FirstCell, LastCell)
        RowString = RowString & Cell.Value
    Next Cell
    'compare it to the previous row:
    If RowString = oldRowString Then
        numInGroup = numInGroup + 1
    Else
        numInGroup = 0
        Range(FirstCell).EntireRow.Interior.ColorIndex = 0
    End If

    'now we fill in the current and all previous rows to the same colour
    'I have done absolutely nothing facy with the colouring, it is simply using the value
    For col = 0 To numInGroup
        CellNo = FirstColumn & (i - col)
        Range(CellNo).EntireRow.Interior.ColorIndex = (numInGroup)

        'this next bit is so that only the last member of the group gets a value in it
        'in order to help define where the groups are
        EmptyColumn = "C" & i - col
        Range(EmptyColumn).Select
        If col = 0 Then
            ActiveCell.Value = numInGroup + 1
        Else
            ActiveCell.Value = ""
        End If
    Next col

    'in either case we store the current row string and clear the temporary row string and repeat
    oldRowString = RowString
    RowString = ""
Next

End Sub

相关内容