我有一组产品数据,其中包括代码、描述和价格。代码是唯一的,价格是数字,描述不是唯一的。请看示例。如何根据描述和价格选择每种产品中最便宜的?
这是使用 MIN 公式的结果。
答案1
将此数组公式添加为新的“最低”列(调整范围以适应您的实际范围),然后向下复制:
={IF(B2=MIN(IF($A$2:$A$9=A2,$B$2:$B$9)),"Y","")}
答案2
我为你写了以下 VBa
Dim hasRows As Boolean
hasRows = True
Dim currentRow As Integer
currentRow = 2
Dim previousProduct As String
Dim currentProduct As String
Dim rowOfProduct As Integer
Dim lowPrice As Integer
lowPrice = -999 ' dummy number
Do While (hasRows)
currentProduct = Range("A" & currentRow).Value
If (previousProduct = "") Then
previousProduct = currentProduct
Else
If (previousProduct <> currentProduct) Then
lowPrice = -999 'dummy again
Range("B" & rowOfProduct).Interior.ColorIndex = 36
End If
End If
If (Range("A" & currentRow).Value = "") Then
hasRows = False
Exit Do
End If
If (lowPrice = -999) Then
lowPrice = Range("B" & currentRow).Value
rowOfProduct = currentRow
Else
If Range("B" & currentRow).Value < lowPrice Then
lowPrice = Range("B" & currentRow).Value
rowOfProduct = currentRow
End If
End If
previousProduct = currentProduct
currentRow = currentRow + 1
Loop