在excel数组公式中使用MIN / MAX

I have a simple array formula in excel that doesn't work in the way I wish. In columns A and B there is data (A1 is paired with B1 and so on) while in column F there is the calculation based on the parameter in column E.

我在excel中有一个简单的数组公式，不能按照我希望的方式工作。在A列和B列中有数据（A1与B1配对，依此类推），而在F列则有基于E列参数的计算。

In cell F1 the formula is:

在单元格F1中，公式为：

{=SUM(MAX(A$1:A$9, E1)*B$1:B$9)}

What this formula does is:

这个公式的作用是：

=MAX(A$1:A$9, E1)*B$1 + MAX(A$1:A$9, E1)*B$2 + ...

Instead, I need a formula that does this:

相反，我需要一个这样做的公式：

=MAX(A$1, E1)*B$1 + MAX(A$2, E1)*B$2 + ...

In words, the formula I wrote (the first one) always finds the max between the values from A1 to A9 and E1, multiplies it by the i-th B value and sums the results. What I need is a formula that finds the max between the i-th A value and E1, and not between all the A values.

换句话说，我写的公式（第一个）总是找到从A1到A9和E1的值之间的最大值，将它乘以第i个B值并对结果求和。我需要的是一个公式，它找到第i个A值和E1之间的最大值，而不是所有A值之间的最大值。

What I'm looking for is easily done by adding in column C the formula =MAX(A1;E$1)*B1 and then in F1 just =SUM(A1:A9), but I can't use this solution because in column F the same formula is repeated, with the E parameter changing every time.

通过在C列中添加公式= MAX（A1; E $ 1）* B1然后在F1中只添加= SUM（A1：A9），我可以轻松完成我想要的工作，但我无法使用此解决方案，因为在列中F重复相同的公式，每次都改变E参数。

I can use a IF instruction: in F1 I can write

我可以使用IF指令：在F1中我可以写

{=SUM(IF(A$1:A$9>E1, A$1:A$9, E1)*B$1:B$9)}

While this formula does what I need in this case, I think it's a bad solution because I find it difficult to read and to expand. For example, if there is another parameter in column D and the factor is MIN(MAX(A$1:A$9;E1);D1), using IF will result in a very long and very unreadable and complicated formula.

虽然这个公式在我的情况下做了我需要的，但我认为这是一个糟糕的解决方案，因为我发现它很难阅读和扩展。例如，如果列D中有另一个参数且因子是MIN（MAX（A $ 1：A $ 9; E1）; D1），则使用IF将导致非常长且非常难以理解且复杂的公式。

Are there better solutions to my problem? Thank you all!

我的问题有更好的解决方案吗？谢谢你们！

NOTE: syntax may vary a little because I am using the italian version of excel.

注意：语法可能会有所不同，因为我使用的是excel的意大利语版本。

5 个解决方案

#1

The problem is that MAX takes an array as an argument. Functions that normally take an array never return an array - they were designed to turn an array into one number. No matter how many arrays you throw at MAX, it's always just going to return one number.

问题是MAX将数组作为参数。通常采用数组的函数永远不会返回数组 - 它们被设计为将数组转换为一个数字。无论你在MAX上抛出多少个阵列，它总是会返回一个数字。

I couldn't come up with a good solution, so here's a bad one

我无法想出一个好的解决方案，所以这是一个糟糕的解决方案

=SUMPRODUCT(((A1:A9*(A1:A9>E1))+(E1*(A1:A9<=E1)))*B1:B9)

I don't think that really increases the maintainability of the IF-based formula that you're trying to avoid. I think you're stuck with IF or a helper column.

我认为这并没有真正提高您试图避免的基于IF的公式的可维护性。我认为你坚持使用IF或辅助列。

#2

Another possibility is a VBA function.

另一种可能性是VBA功能。

Public Function SumMaxMin(rRng1 As Range, rRng2 As Range, ParamArray vaMinMax() As Variant) As Double

    Dim rCell As Range
    Dim dReturn As Double
    Dim aMult() As Double
    Dim lCnt As Long
    Dim i As Long

    ReDim aMult(1 To rRng1.Cells.Count)

    For Each rCell In rRng1.Cells
        lCnt = lCnt + 1
        aMult(lCnt) = rCell.Value
        For i = LBound(vaMinMax) To UBound(vaMinMax) Step 2
            If Not Evaluate(aMult(lCnt) & vaMinMax(i + 1) & vaMinMax(i)) Then
                aMult(lCnt) = vaMinMax(i)
            End If
        Next i
    Next rCell

    For i = LBound(aMult) To UBound(aMult)
        dReturn = dReturn + aMult(i) * rRng2.Cells(i).Value
    Next i

    SumMaxMin = dReturn

End Function

For your example

以你为榜样

=SumMaxMin(A1:A9,B1:B9,E1,">")

Adding another condition

添加另一个条件

=SumMaxMin(A1:A9,B1:B9,E1,">",D1,"<")

It will error if your ranges aren't the same number of cells or you pass arguments that don't work with Evaluate.

如果您的范围不是相同的单元格数，或者您传递的参数不适用于Evaluate，则会出错。

#3

Another possibility for avoiding repetitions of cell references is:

避免重复细胞参考的另一种可能性是：

=SUM(B1:B9*ABS(A1:A9-E1*{1,-1}))/2

assuming values are non-negative. More generally to return an array of pairwise max values:

假设值是非负的。更一般地，返回成对最大值的数组：

=MMULT((A1:A9-E1*{1,-1})^{2,1}^{0.5,1},{1;1}/2)

which returns:

MAX(A1,E1)
MAX(A2,E1)
...
MAX(A9,E1)

#4

I don't remember ever cracking this problem, but for maintainability I'd probably do something like this:

我不记得曾经破解过这个问题，但为了可维护性，我可能会做这样的事情：

{=SUM((A1:A9<E1)*E1*B$1:B$9)    +    SUM((A1:A9>=E1)*A1:A9*B$1:B$9)}

#5

If I understand the problem correctly, using IF instead of MAX should do:

如果我正确理解问题，使用IF代替MAX应该：

=SUM(IF($A$1:$A$9>E1;$A$1:$A$9;E1)*$B$1:$B$9)

= SUM（IF（$ A $ 1：$ A $ 9> E1; $ A $ 1：$ A $ 9; E1）* $ B $ 1：$ B $ 9）

#1