问VBA Excel中的聚合函数

EN

类型不匹配错误的原因是术语("R2C2:R2000C2" = FrmMaster.CmbProduct.Value) * "R2C3:R2000C3"试图将布尔值(("R2C2:R2000C2" = FrmMaster.CmbProduct.Value)的结果)与字符串文本"R2C3:R2000C3"相乘。这当然是一种类型错配。

但还有其他问题。

术语"R2C2:R2000C2" = FrmMaster.CmbProduct.Value将字符串"R2C2:R2000C2"与FrmMaster.CmbProduct.Value进行比较。这总是得到false，除了FrmMaster.CmbProduct.Value将是"R2C2:R2000C2“。而且它与数组公式中的$B$2:$B$2000=N12不一样。在那里，$B$2:$B$2000是一个单元格值的范围，每个单元格值与N12值相比较。但这不是VBA所能做的。甚至(sh.Range("$B$2:$B$2000") = FrmMaster.CmbProduct.Value)也无法工作，因为这尝试将数组(sh.Range("$B$2:$B$2000"))与单个值进行比较。

因此，计算具有这种数组操作的数组公式的唯一方法是使用Evaluate。WorksheetFunction在这里无能为力，因为它需要首先通过VBA计算出的单个函数参数。

解决问题的例子：

 Dim res As Long
 Dim sh As Worksheet
 Dim range1Address As String
 Dim range2Address As String
 Dim arrayFormula As String

 Set sh = ThisWorkbook.Sheets("Database")
 With sh
  range1Address = .Range("B2:B2000").Address(RowAbsolute:=True, ColumnAbsolute:=True, ReferenceStyle:=xlA1, External:=True)
  range2Address = .Range("C2:C2000").Address(RowAbsolute:=True, ColumnAbsolute:=True, ReferenceStyle:=xlA1, External:=True)
  arrayFormula = "AGGREGATE(14,4,(" & range1Address & "=""" & FrmMaster.CmbProduct.Value & """)*" & range2Address & ",1)"
  res = Evaluate(arrayFormula)
  MsgBox res
 End With

从范围中获取range1Address和range2Address作为外部地址(包括工作簿名称和工作表名称)使其独立于活动工作表。

我想知道下面的公式是否对你有帮助。它将返回与查找值+1相关联的任何数字。

=IFERROR(LOOKUP(2,1/($A1:$A$1=A2),$B1:$B$1),0)+1

首先是功能。该函数查找A2 在 A2上的最后一次出现，它位于一个范围内，从绝对$A$1开始，以相对$A1结尾。后者将随着公式的复制而扩展。B列中相同的系统，如果没有发现并且发生错误，如果发生错误，则函数返回0。添加到此1的结果中。

现在，如果将公式中的0(在没有先例的情况下)中的0替换为类似于2022050的数字，则公式将在示例的第一行中返回正确的结果，并返回所有随后出现的“阿莫西林胶囊500”。

它将返回对乙酰氨基酚片线1，因为没有先例。1将突出，也许你有一个系统，你可以用它来填补那里缺失的数字。

无论您是应用VBA还是工作表函数，问题都是一样的。您需要一个种子号来根据基础增量。综合考虑，工作表函数看起来更容易实现。