Можно ли исключить скрытые строки при поиске дубликатов в Excel?

Я работаю над процедурой в Excel, используя VBA, которая выделяет повторяющиеся строки. Процедура оценивает результат функции sumproduct рабочего листа, чтобы определить, есть ли в строке дубликаты.

Вычисленная формула в конечном итоге выглядит следующим образом:

SUMPRODUCT(--(A1:A10 = A1), --(B1:B10 = B1), --(C1:C10 = C1))

Пока процедура работает отлично, но мне нужно, чтобы она не учитывала скрытые строки и столбцы при оценке. Я могу пропустить скрытые строки в столбцах в своих циклах, используя Range.Hidden = False, но я не нашел способа исключить скрытые строки и столбцы из СУММПРОИЗВ.

Я также пробовал перебирать каждую строку дважды, используя два вложенных цикла и просто сравнивая значения по две строки за раз, но это приводило к N-квадрату, или O(n2), итерациям, поэтому я отказался на том методе.

Есть ли способ заставить СУММПРОИЗВ игнорировать скрытые строки, как это возможно с формулой электронной таблицы ПРОМЕЖУТОЧНЫЙ ИТОГ?

Вот что у меня есть до сих пор, используя Evaluate(SUMPRODUCT): Спасибо!

Private Sub ShowDuplicateRows()

    Dim lngRow As Long
    Dim lngColumn As Long
    Dim strFormula As String

    With Selection

        For lngRow = 1 To .Rows.Count
            If Not .Rows(lngRow).Hidden Then

                strFormula = "SUMPRODUCT("
                For lngColumn = 1 To .Columns.Count
                    If Not .Columns(lngColumn).Hidden Then
                        If strFormula <> "SUMPRODUCT(" Then
                            strFormula = strFormula & ", "
                        End If
                        strFormula = strFormula _
                        & "--(" & .Columns(lngColumn).Address _
                        & " = " & .Cells(lngRow, lngColumn).Address & ")"
                    End If
                Next
                strFormula = strFormula & ")"

                If Evaluate(strFormula) > 1 Then
                    .Rows(lngRow).Font.Color = RGB(255, 0, 0)
                End If

            End If
        Next lngRow

    End With

End Sub

excel vba big-o quadratic
person Kuyenda    schedule 29.01.2010    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Свойство RowHeight/Hidden не подвергается никакой формуле. Решение должно быть в VBA. Один из способов добиться этого — создать определяемую пользователем формулу (UDF), которая делает то, что вы хотите, а затем просто использовать ее в своей формуле суммарного произведения.

Public Function IsVisible(ByVal rng As Excel.Range) As Variant
    Dim varRtnVal As Variant
    Dim lRow As Long, lCol As Long
    Dim ws As Excel.Worksheet
    ReDim varRtnVal(1 To rng.Rows.Count, 1 To rng.Columns.Count)
    For lRow = 1& To rng.Rows.Count
        For lCol = 1& To rng.Columns.Count
            varRtnVal(lRow, lCol) = CDbl(-(rng.Cells(lRow, lCol).RowHeight > 0&))
        Next
    Next
    IsVisible = varRtnVal
End Function

Тогда ваша формула будет выглядеть так:

=SUMPRODUCT(IsVisible($A$2:$A$11),--($A$2:$A$11=1),--($B$2:$B$11=1),--($C$2:$C$11=1))
person Oorang    schedule 29.01.2010
comment
Пугающий. Я уже встал на этот путь, прежде чем вернуться в эту тему. UDF, который я пишу, почти идентичен вашему. В чем смысл & в 1& и 0&? - person Kuyenda; 30.01.2010
comment
Это символ объявления типа, он переводит литерал 1 в тип Long. Если вы не поместите их в свои литералы, VBA выдаст наименьшее из значений Integer, Long или Double. Поскольку родной тип VB — длинный, все операторы (такие как Redim/For и т. д.) используют длинный (отсюда и 1). Кроме того, сравнение должно проходить через неявное преобразование к тем же типам. Итак, поскольку высота строки длинная, я добавил ее к 0, чтобы не пришлось подвергать ее неявному преобразованию. Это микрооптимизация и, вероятно, не будет иметь никакого измеримого значения. Это просто привычка и личный стиль. Единственный раз, когда это ...подробнее... - person Oorang; 30.01.2010
comment
важно вводить литералы, когда вы используете шестнадцатеричные или восьмеричные константы, и они не имеют знака, и поэтому VBA не выполняет преобразование правильно, когда числа находятся в определенных диапазонах (в частности, 2 ^ 16th: support.microsoft.com/kb/38888). Подводя итог, они вам действительно не нужны, я просто положил их туда :) - person Oorang; 30.01.2010
comment
Что ж, мне нужно провести дополнительное тестирование, но, похоже, это работает, и работает быстро. UDF по-прежнему приходится перебирать все строки, чтобы выяснить, скрыты они или нет, но по какой-то причине делать это в UDF намного быстрее, чем в основной процедуре с тем же вложенным циклом. Я отмечаю это как решение, и я помещу обновленную полную процедуру в отдельный пост. - person Kuyenda; 30.01.2010

arrow_upward
0
arrow_downward

Это полный обновленный код. Сначала основная процедура, затем определяемая пользователем функция.

Если кто-нибудь может объяснить, почему размещение вложенного цикла в UDF быстрее, чем в основной процедуре, я был бы очень признателен! Еще раз спасибо Орангу!

Я сделал свою версию IsVisible UDF немного более гибкой. Он может использовать диапазон, переданный в качестве параметра, или, если ничего не передано, он использует Application.Caller.

Private Sub ShowDuplicateRows()

    Dim lngRow As Long
    Dim lngColumn As Long
    Dim strFormula As String

    With Selection

        For lngRow = 1 To .Rows.Count
            If Not .Rows(lngRow).Hidden Then

                strFormula = "SUMPRODUCT(--(ISVISIBLE(" _
                & .Columns(1).Address & "))"
                For lngColumn = 1 To .Columns.Count
                    If Not .Columns(lngColumn).Hidden Then
                        strFormula = strFormula _
                        & ", --(" & .Columns(lngColumn).Address _
                        & " = " & .Cells(lngRow, lngColumn).Address & ")"
                    End If
                Next
                strFormula = strFormula & ")"

                If Evaluate(strFormula) > 1 Then
                    .Rows(lngRow).Font.Color = RGB(255, 0, 0)
                Else
                    .Rows(lngRow).Font.ColorIndex = xlAutomatic
                End If

            End If
        Next lngRow

    End With

End Sub

Public Function IsVisible(Optional ByVal Reference As Range) As Variant

    Dim varArray() As Variant
    Dim lngRow As Long
    Dim lngColumn As Long

    If Reference Is Nothing Then Set Reference = Application.Caller

    With Reference

        ReDim varArray(1 To .Rows.Count, 1 To .Columns.Count)

        For lngRow = 1 To .Rows.Count
            For lngColumn = 1 To .Columns.Count
                varArray(lngRow, lngColumn) _
                = Not .Rows(lngRow).Hidden _
                And Not .Columns(lngColumn).Hidden
            Next lngColumn
        Next lngRow

    End With

    IsVisible = varArray

End Function
person Kuyenda    schedule 30.01.2010