excel - 用所有其他单元格减去单元格值并找到最大值

Question

我有上面的值列表。我想首先用所有其他值减去第一个值。之后用所有其他值减去第二个值，依此类推。最终结果应该是最大的减法值和产生最大减法值的两个单元格。

标签： excelvbaexcel-formula

Answer 1

如果您需要 VBA 解决方案，请尝试下一个代码。基本上它适应了@Tom Sharpe 上面提出的公式建议：

Sub testLargestSubtraction()
 Dim sh As Worksheet, lastRow As Long, rng As Range, maxSubstrVal As Double
 Dim maxCellAddress As String, minCellAddress As String, rngMaxS As Range
 
  Set sh = ActiveSheet ' use here your sheet
  lastRow = sh.Range("A" & rows.count).End(xlUp).row
  
  Set rng = sh.Range("A2:A" & lastRow)
  maxSubstrVal = WorksheetFunction.Max(rng) - WorksheetFunction.Min(rng)
  maxCellAddress = "A" & WorksheetFunction.Match(WorksheetFunction.Max(rng), rng, 0) + rng.cells(1).row - 1
  minCellAddress = "A" & WorksheetFunction.Match(WorksheetFunction.Min(rng), rng, 0) + rng.cells(1).row - 1
  Debug.Print maxSubstrVal, maxCellAddress, minCellAddress
  Set rngMaxS = Union(sh.Range(maxCellAddress), sh.Range(minCellAddress))
  rngMaxS.Select
End Sub

它返回（在即时窗口中）您要求的内容，并选择产生最大减法结果的两个单元格，与范围排序无关。