获取从2列数据(无辅助列)计算出的最大值 [英] getting the maximum value calculated from 2 columns of data (without helper column)

问题描述

我有一些数据，存储在A列和B列中. 对于每一行，我想获得A列和B列中的值之间的差. IE.单元格(1,2)-单元格(1,1)，以此类推. 第二步是确定第i行上的差异是否为前面第n行的最大值. 借助helper列，我可以使用公式或类似的代码来做到这一点.

I have some data, stored in Column A and column B. For each row, I would like to get the difference between the values in column A and column B. I.e. cells(1,2) - cells(1,1), so on so forth for the rest of rows. The second step is to determine if the difference on i row was the max of the preceeding nth rows. With helper column, I can do it with formula or with code like this.

for i = 1 to LR
cells(i,3) = cells(i,2) - cells(i,1)
next i

for i = 1 to LR
if cells(i,3) = .max(range(cells(i-19,3),cells(i,3))) then
cells(i,4) = "MAX"
end if
next i

虽然我不想添加帮助器列，但是哪些VBA代码可以做到这一点?您能告诉我我可能不熟悉所需语法的代码吗?

While I don't want to add a helper column, what vba codes can do this? Can you show me the code for I may not be familiar with the syntax I needed?

推荐答案

这可以完成您的公式，而无需使用帮助器列.我假设最终目标是在差异最大的集合旁边写上"MAX"一词

This does what your formula does, without using the helper column. I'm assuming the end goal is to get the word "MAX" written next to the set where the difference peaks

Sub PeakDifferences()

Dim i As Long
Dim j As Long
Dim myArr() As Double
Dim subArr(1 To 20) As Double 'change 20 to 19 here if only looking at last 19 numbers

    With ActiveSheet

        lr = .Cells(.Rows.Count, "A").End(xlUp).Row
        ReDim myArr(1 To lr)

        For i = 1 To lr
            myArr(i) = .Cells(i, 2).value - .Cells(i, 1).value
            For j = LBound(subArr) To UBound(subArr)
                If i - j < 0 Then Exit For
                subArr(j) = myArr(i - j + 1)
            Next
            If myArr(i) = WorksheetFunction.Max(subArr) Then .Cells(i, 3) = "MAX"
        Next i

    End With

End Sub

这篇关于获取从2列数据(无辅助列)计算出的最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！