excel中用VBA自定义公式计算查找数值中最大值或者最大值与最小值间隔？

Question

在一个工作簿中，sheet1中A列是各类订单号，G列是每个订单号下层机型的需求日期，1个订单号会对应多行不同的需求日期现需要在sheet2中，用VBA设置一个自定义公式，能快速计算出一个订单号对应的最长需求时间，最长和最短时间的间隔需要用VBA自定义公式解决这个问题

Answer 1

1、最大时间：

Function Maxtime(Rng As Range)

    Application.Volatile

    Dim Cell As Range, UnioCell As Range

    With Sheets("Sheet1")

        For Each Cell In .Range("A2:A" & .Cells(Rows.Count, 1).End(xlUp).Row)

            If Cell = Rng Then

                If UnioCell Is Nothing Then

                    Set UnioCell = Cell.Offset(0, 6)

                Else

                    Set UnioCell = Union(UnioCell, Cell.Offset(0, 6))

                End If

            End If

        Next

    End With

    If Not UnioCell Is Nothing Then

        Maxtime = Application.Max(UnioCell)

    Else

        Maxtime = ""

    End If

End Function

2、时间间隔：

Function IntervalTime(Rng As Range)

    Application.Volatile

    Dim Cell As Range, UnioCell As Range

    With Sheets("Sheet1")

        For Each Cell In .Range("A2:A" & .Cells(Rows.Count, 1).End(xlUp).Row)

            If Cell = Rng Then

                If UnioCell Is Nothing Then

                    Set UnioCell = Cell.Offset(0, 6)

                Else

                    Set UnioCell = Union(UnioCell, Cell.Offset(0, 6))

                End If

            End If

        Next

    End With

    If Not UnioCell Is Nothing Then

        IntervalTime = Application.Max(UnioCell) - Application.Min(UnioCell)

    Else

        IntervalTime = ""

    End If

End Function

3、以下是实测图：(预先设定单元格格式)

提示，如果是365版本，新函数可以直接取代自定义函数；

=MAXIFS(Sheet1!$G$2:$G$16,Sheet1!$A$2:$A$16,A2)

‍=MAXIFS(Sheet1!$G$2:$G$16,Sheet1!$A$2:$A$16,A2)-MINIFS(Sheet1!$G$2:$G$16,Sheet1!$A$2:$A$16,A2)