看起来很简单的问题实际上很棘手，因为在计算差异时会遇到舍入误差，这会导致错误的答案.我最终将结果四舍五入到小数点后十位，然后再进行比较以解决这个问题，但是它看起来并不是一个优雅的公式:

Well what looks like a fairly simple question actually was quite tricky because you run into rounding errors when you compute the differences which can lead to a wrong answer. I ended up arbitrarily rounding the results to 10 decimal places before comparing them to get round this but it does not look an elegant formula:

=MIN(IF((ROUND(ABS(ROUND(A2:INDEX(A:A,COUNTA(A:A)),0)-A2:INDEX(A:A,COUNTA(A:A))),10)=MIN(ROUND(ABS(ROUND(A2:INDEX(A:A,COUNTA(A:A)),0)-A2:INDEX(A:A,COUNTA(A:A))),10))),A2:INDEX(A:A,COUNTA(A:A))))

必须使用 Ctrl Shift Enter

假定数据中没有间隙(这将抛出Counta，并且最小差也为零).

Assumes there are no gaps in the data (which would throw out the Counta and also give a result of zero for the minimum difference).

编辑

这只是一个实验，以查看您是否使用十进制类型获得了正确的答案

This is only an experiment to see if you get the right answer using decimal types

Option Explicit
Option Base 1

Sub findClosestToInt()

Dim sht As Worksheet
Dim LastRow As Long, nRows As Long, nData As Long, nMins As Long
Dim i As Long
Dim data() As Variant, differences() As Variant, minData() As Variant
Dim minDiff As Variant, minValue As Variant, maxData As Variant


Set sht = ActiveSheet
LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
Debug.Print ("LR=" & LastRow)
nRows = LastRow - 1


ReDim data(LastRow - 1)
ReDim differences(LastRow - 1)

' store data as decimal

nData = 0
For i = 2 To LastRow
    If sht.Cells(i, 1) <> "" Then
        nData = nData + 1
        data(nData) = CDec(sht.Cells(i, 1))
    End If
Next i

ReDim Preserve data(nData)
ReDim differences(nData)

Debug.Print ("nData=" & nData)

' find differences from nearest integer

For i = 1 To nData
    differences(i) = Abs(data(i) - Round(data(i), 0))
    Debug.Print (differences(i)) ' no rounding errors
Next i

minDiff = Application.WorksheetFunction.Min(differences)

Debug.Print ("minDiff=" & minDiff)

ReDim minData(nData)

' find min of data where difference is equal to min difference

nMins = 0
For i = 1 To nData
    If differences(i) = minDiff Then
        nMins = nMins + 1
        minData(nMins) = data(i)
    End If
Next i

ReDim Preserve minData(nMins)

minValue = Application.WorksheetFunction.Min(minData)

Debug.Print ("minValue=" & minValue)

End Sub

结果为1.99，这是正确的.如果仅使用(例如)double，则会得到错误的答案.

The result is 1.99 which is correct. If you just use (say) double instead, you get the wrong answer.

我认为一旦解决了差异，就可以使用工作表函数Min.

I think it is OK to use the worksheet function Min once you've worked out the differences.

如果需要的话，允许在数据中保留空白单元格非常简单-我认为VBA方式确实赢得了全部胜利.

It is straightforward to allow for blank cells in the data if required - the VBA approach does win all round I think.