在数组中找到最接近整数的最小数字 [英] find smallest number closest to an integer in an array excel

问题描述

我在excel工作表中有一个动态值列表.我需要找到一种方法来识别数组中与整数最接近的最低数字.例子:在一种情况下，列表包含以下数字:1.56、1.65、1.71、1.84、1.94、2.00、2.06、2.03、2.22 ........ 2.95、3.05、3.81、4.00等我要在此实例中找到的数字是2.00.我可以在Excel中使用此功能吗?

I have a dynamic list of values in an excel worksheet. I need to find a way to identify the lowest number in the array that is closest to a whole number. example: in one instance the list includes the following numbers: 1.56, 1.65, 1.71, 1.84, 1.94, 2.00, 2.06, 2.03, 2.22........2.95, 3.05, 3.81, 4.00 etc. the number I want to find in this instance is 2.00. Is there a function in Excel that I can use for this?

推荐答案

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

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.

这篇关于在数组中找到最接近整数的最小数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！