Excel: вычисление разницы между ячейкой и ячейкой выше в автоматически отфильтрованной таблице

ПЕРЕСМОТРЕННЫЙ ОТВЕТ

Итак, после вашего объяснения я изучил формулу, которая даст вам разницу между текущим значением B строки за вычетом B-значения появления значения A до этого.

=IFERROR(B2-LOOKUP(2,1/($A$1:A1=A2),$B$1:B2),0)

Взяв ваши образцы данных, это будет выглядеть так:

Затем, когда вы примените фильтр, он будет выглядеть так:

Таким образом, с помощью этого обходного пути у вас нет правильного значения, когда фильтр не применяется, но в этом случае я предположил, что вас интересует разница, когда он IS отфильтрован!

Формула вводится в ячейку C2 и перемещается вниз.

ИЗМЕНИТЬ

Если это не тот ответ, который вам нужен, и вам НЕОБХОДИМО значения, когда он не отфильтрован, используйте UDF, как показано ниже:

Public Function LastVisibleCell(CL As Range) As Long

Dim RW As Long, X As Long
RW = CL.Row - 1
On Error GoTo 1

If RW > 1 Then
    For X = RW To 1 Step -1
        If ActiveSheet.Rows(X).EntireRow.Hidden Then
        Else
            LastVisibleCell = Cells(CL.Row, 2).Value - Cells(X, 2).Value
            Exit For
        End If
    Next X
Else
1:    LastVisibleCell = 0
End If

End Function

Вызовите его из ячейки C2, например: =LastVisibleCell(A2), и потяните вниз. Когда вы примените фильтр, ячейки обновятся.

Осторожно, обновление больших наборов данных займет много времени!

После 3 дней интенсивного (хотя и неэффективного) поиска в Google я наконец наткнулся на answer также при переполнении стека.

Однако, поскольку я работаю с большим набором данных (> 150 000 строк), рассматриваемый метод использует слишком много памяти. Использование VBA для вставки формул только в видимые ячейки, похоже, не решает проблемы.

Sub CopyPasteFormula()
    Dim Ws As Worksheet
    Dim LRow As Long
    Dim PasteRng As Range

    Set Ws = Worksheets("Translated Data")
    Ws.Range("$D$2:$D$200000").AutoFilter Field:=4, Criteria1:="<>-", Operator:=xlFilterValues
    LRow = Ws.Range("D" & Rows.Count).End(xlUp).Row
    Set PasteRng = Ws.Range("A3:A" & LRow).SpecialCells(xlCellTypeVisible)
    Ws.Range("A3").Copy
    PasteRng.PasteSpecial xlPasteFormulas
    Application.CutCopyMode = False
End Sub

Выше мой код макроса, чтобы попытаться уменьшить использование памяти ... Примите во внимание любую обратную связь!