excel中用VBA自定义公式计算查找数值中最大值或者最大值与最小值间隔？
发布网友
发布时间：2022-05-08 00:23
共1个回答
热心网友
时间：2023-11-22 02:57
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)
热心网友
时间：2023-11-22 02:57
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)
热心网友
时间：2023-11-22 02:57
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)
