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

У меня есть таблица со столбцом A, содержащим увеличивающиеся числовые значения, а столбец B представляет собой набор имен. Мне нужно отфильтровать таблицу по именам и обновить столбец C с разницей между значением в столбце A в текущей строке и ячейкой выше.

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

Я пытался использовать функцию SUBTOTAL несколькими различными способами, но безрезультатно. В идеале он будет обновляться после замены фильтра в таблице. Я пытался сделать это в VBA, но до сих пор я получил макрос, который фильтрует только с жестко закодированными критериями фильтрации.

Все решения в формулах Excel / Python/ VBA приветствуются и высоко ценятся!

Я заранее прошу прощения, если этот вопрос не соответствует стандартам, так как я здесь новичок:) Заранее спасибо!

@JvdV: Это результат моей попытки реализовать вашу формулу, это после фильтрации.

Источник

2 ответа

Решение

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

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

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

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

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

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

Формула вводится в ячейку 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 я, наконец, наткнулся на этот ответ также о переполнении стека.

Однако, поскольку я работаю с большим набором данных (>150000 строк), метод в вопросе использует слишком много памяти. Использование 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

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

Источник
Другие вопросы по тегам