Excel MAXIF 函数或仿真？

Question

我在 excel 中有一个中等大小的数据集，我从中希望提取 B 列中值的最大值，但这些值仅对应于 A 列中满足特定条件的单元格。

所需的功能与 SUMIF 或 COUNTIF 类似，但它们都不会返回必需的数据。没有 MAXIF 函数；我如何模仿一个？

Answer 1

您可以使用数组公式。在要计算最大值的单元格中输入：=Max(If([test],[if true],[if false])，其中用测试替换方括号中的值，如果为 true，则返回什么；如果为 false，则返回什么。 例如：

=MAX(IF(MOD(A2:A25,2)=0,A2:A25,0)

在此公式中，如果值除以 2 没有余数，则返回 A 列中的值。请注意，我在比较和值中使用了一系列单元格。 。

现在，在编辑单元格时，按 Ctrl+Shift+Enter（同时按住 Ctrl 键和 Shift 键，然后按 Enter），

这将创建一个作用于单元格中的每个值的数组公式 ？

编辑 顺便说一句，您想以编程方式还是手动执行此操作？如果以编程方式，那么您使用什么环境？

编辑 如果通过 VBA，您需要什么 使用 FormulaArray 属性和 R1C1 引用，如下所示：

Range("A1").Select
Selection.FormulaArray = "=MAX(IF(MOD(R[1]C:R[24]C,2)=0,R[1]C:R[24]C,0))"

Answer 2

当您想要使用动态或命名范围时（例如，“当前行上方与当前行具有相同交易对手的行的最大应付金额），数组公式不能很好地工作。如果您不想使用对于数组公式，您始终可以求助于 VBA 来执行以下操作：

Function maxIfs(maxRange As Range, criteriaRange As Range, criterion As Variant) As Variant

  maxIfs = Empty
  For i = 1 To maxRange.Cells.Count
    If criteriaRange.Cells(i).Value = criterion Then
        If maxIfs = Empty Then
            maxIfs = maxRange.Cells(i).Value
        Else
            maxIfs = Application.WorksheetFunction.Max(maxIfs, maxRange.Cells(i).Value)
        End If
    End If
  Next
End Function

Answer 3

到目前为止提供的代码的一个限制是您仅限于 2 个条件。我决定进一步改进此代码，不限制 MaxIfs 函数的条件数量。请参阅此处的代码：

        Function MaxIfs(MaxRange As Range, ParamArray Criteria() As Variant) As Variant
        Dim n As Long
        Dim i As Long
        Dim c As Long
        Dim f As Boolean
        Dim w() As Long
        Dim k As Long
        Dim z As Variant

        'Error if less than 1 criteria
        On Error GoTo ErrHandler
        n = UBound(Criteria)
        If n < 1 Then
            'too few criteria
            GoTo ErrHandler
        End If
            'Define k
            k = 0            

        'Loop through cells of max range
        For i = 1 To MaxRange.Count

        'Start by assuming there is a match
        f = True

            'Loop through conditions
            For c = 0 To n - 1 Step 2

                'Does cell in criteria range match condition?
                If Criteria(c).Cells(i).Value <> Criteria(c + 1) Then
                    f = False
                End If

            Next c

            'Define z
            z = MaxRange

            'Were all criteria satisfied?
            If f Then
                k = k + 1
                ReDim Preserve w(k)
                w(k) = z(i, 1)
            End If

        Next i

        MaxIfs = Application.Max(w)

        Exit Function
        ErrHandler:
        MaxIfs = CVErr(xlErrValue)

    End Function

此代码允许 1 到多个条件。

该代码是参考 Hans V 在 Eileen's Lounge 发布的多个代码而开发的。

快乐编码

Diedrich