我尝试在范围示例行中找到最小值的行有特殊的单词。对于EX:
现在,我想找到一排排最小编号为“西瓜”的水果。结果应该是5(香蕉行)
我的想法是找到第二低的价值
SecLowVal = objExcel.WorksheetFunction.Small(Range("B2:B6"),2)
然后我们将找到该值的行
For Each rngCell In Range("B2:B6")
If rngCell.Value = SecLowVal Then
Row = rngCell.Row
Exit For
End If
Next rngCell
msg(Row)
但以防万一:
这是错误的,我不知道有什么功能或方法可以找到最小值的行,但行中有特殊的单词
使用此公式。
=CELL("row",INDEX(B2:B6,MATCH(MIN(IF(A2:A6<>"Watermelon",B2:B6,"")),B2:B6,0)))
按CTRL+ SHIFT+ENTER以评估该公式,因为它是一个数组公式。
根据您的评论编辑更新公式。
=CELL("row",INDEX(B2:B6,MATCH(MIN(IF(A2:A6<>"Watermelon",B2:B6,"")),IF(A2:A6<>"Watermelon",B2:B6,""),0)))
按CTRL+ SHIFT+ENTER以评估该公式,因为它是一个数组公式。
========================== VBA功能====================== =======
Public Function MinBasedOnCondition(InRange As Range, valRange As Range, ConditionItem As String) As Variant
Dim MyCell As Range
Dim ValueArray()
Dim MyArray()
Dim CelCount, inc, MinVal, i As Long
Dim Condition As String
Dim ArrItems, Result
Condition = ConditionItem
CelCount = Application.CountIf(InRange, "<>" & Condition)
ReDim ValueArray(CelCount)
inc = 1
For Each MyCell In InRange
If MyCell.Value <> Condition Then
ValueArray(inc) = MyCell.Offset(0, 1).Value
inc = inc + 1
End If
Next
ArrItems = ""
For i = 1 To CelCount
ArrItems = ArrItems & ValueArray(i) & ", "
Next
ArrItems = Left(ArrItems, Len(ArrItems) - 2)
MyArray = Array(ArrItems)
MinVal = Evaluate("Min(" & Join(MyArray, ",") & ")")
For Each MyCell In valRange
If MyCell.Offset(0, -1).Value <> Condition Then
If MyCell.Value = MinVal Then
Result = MyCell.Row
Exit For
End If
End If
Next
MinBasedOnCondition = Result
End Function
在工作表中使用
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句