Excel 公式（返回不同单元格减法的最大数字以及用于答案的单元格）

Question

我试图返回不同单元格差异的最大数字 - 即计算后返回最大数字a1-b1，a1-c1，b1-c1。 假设

a1=3 
b1=2
c1=1 

然后返回的数字是 2（如a1=3 minus c1=1 gives 2）。我还想知道答案具体来自a1-c1，或无论情况如何。实际单元格数量远高于给定的三个，因此在单独的单元格中执行每个方程是不可行的。

Answer 1

以下用户定义函数（UDF）可以处理任意数量的单元格（不只是三个）：

Public Function MaxDiff(rIN As Range) As String
    Dim r1 As Range, r2 As Range, WhichOnes As String
    Dim v As Double, diff As Double
    Dim difftemp As Double

    diff = 0
    For Each r1 In rIN
        v = r1.Value
        For Each r2 In rIN
            difftemp = Abs(v - r2.Value)
            If difftemp > diff Then
                diff = difftemp
                WhichOnes = r1.Address(0, 0) & "-" & r2.Address(0, 0)
            End If
        Next r2
    Next r1
    MaxDiff = CStr(diff) & " -> " & WhichOnes
End Function

它给出了范围内单元格绝对差的最大值以及这些单元格的地址。例如：

用户定义函数 (UDF) 非常容易安装和使用：

1. ALT-F11 打开 VBE 窗口
2. ALT-I ALT-M 打开新模块
3. 粘贴内容并关闭 VBE 窗口

如果您保存工作簿，UDF 将随之保存。如果您使用的是 2003 之后的 Excel 版本，则必须将文件保存为 .xlsm 而不是 .xlsx

要删除 UDF：

1. 调出如上所示的 VBE 窗口
2. 清除代码
3. 关闭 VBE 窗口

要从 Excel 使用 UDF：

=MaxDiff(A1:Z1)

要了解有关宏的更多信息，请参阅：

http://www.mvps.org/dmcritchie/excel/getstarted.htm

和

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

有关 UDF 的详细信息，请参阅：

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

必须启用宏才能使其工作！

Answer 2

三个公式：

由于最大差异始终是最大数与最小数之间的差异，因此找到差异：

=MAX($A$1:$A$10)-MIN($A$1:$A$10)

查找地址：

最大限度：

=ADDRESS(AGGREGATE(15,6,ROW($A$1:$A$10)/($A$1:$A$10=MAX($A$1:$A$10)),1),AGGREGATE(15,6,COLUMN($A$1:$A$10)/($A$1:$A$10=MAX($A$1:$A$10)),1))

最小值：

=ADDRESS(AGGREGATE(15,6,ROW($A$1:$A$10)/($A$1:$A$10=MIN($A$1:$A$10)),1),AGGREGATE(15,6,COLUMN($A$1:$A$10)/($A$1:$A$10=MIN($A$1:$A$10)),1))

如果您希望它们全部放在一个单元格中（如@Gary'sStudent），只需将它们连接起来即可：

=MAX($A$1:$A$10)-MIN($A$1:$A$10) & "->" & ADDRESS(AGGREGATE(15,6,ROW($A$1:$A$10)/($A$1:$A$10=MAX($A$1:$A$10)),1),AGGREGATE(15,6,COLUMN($A$1:$A$10)/($A$1:$A$10=MAX($A$1:$A$10)),1)) & "-" & ADDRESS(AGGREGATE(15,6,ROW($A$1:$A$10)/($A$1:$A$10=MIN($A$1:$A$10)),1),AGGREGATE(15,6,COLUMN($A$1:$A$10)/($A$1:$A$10=MIN($A$1:$A$10)),1))