Question

Excel中如何表示最接近某个数值的数字是多少，每个数字被选择一次

比如一组数：1，1，2，3，3，4，5
第一个目标数是6，最接近6的数我可以用=MAX(IF(A:A<B2, A:A))计算出是5.
第二个目标数是7，最接近7的数应该也是5，但是由于5只有一个，如何用公式跳开已经被使用的5，计算出4？？？

也就是说在Excel2016里想写出一个公式选出最接近目标数的数字，但是每个数字只能被选一次。

求求求！！！

Answer 1

最接近是还有“并小于”这个条件吗？按描述貌似还包含了这个条件。

如图，

C2=MAX((COUNTIF(C$1:C1,A$2:A$8)=0)*(A$2:A$8<B2)*$A$2:$A$8)

同时按Ctrl+Shift+Enter三键输入数组公式，下拉

PS：建议养成尽量不在引用中写整行整列的习惯，特别是引用要参与计算时，而是根据实际数据范围来写公式，可以有效提高公式效率。

追问

请问是哪里出了问题？

追答

请自己对照你的公式与我给你的公式，并不是一样的。我的COUNTIF是从C1开始的

追问

c1的逻辑是不是上面出现过的不能再出现？可是比如说例子中的数据1有两个，1这个数字可以被选2次，但是您的公式如果1被选一次后countif就再也不为0就没法被选第二次了是这样吗？求指教

追答

是c列中前面出现过的不再列为可选数据。

c2就除去c1的，c3就剔去c1，c2的，依次类推。所以前面c1的1前加上美元符号$。

追问

我理解，如果c1是1，c3是不是不可能再选到1了？因为countif不为0了，是不是这个逻辑呀

我理解，如果c1是1，c3是不是不可能再选到1了？因为countif不为0了，是不是这个逻辑呀

追答

c1实际上不是a2：a8中的数据，写c1是为了公式下拉一致。到c3比较好理解些，c1：c2中已有5，用countif 来a2：a8中每个数在c1：c2中（即数字5）出现的次数，只有5为1次，其余都为0，所以5就不再取出作为候选数了。

追问

谢谢您的回答，但是如果接着您说的，如果有2个5岂不是第二个5没有被选中的机会了？

追答

相当难了，暂时还没想到用什么方法发。虽说用large可以依次取，但b列却不一定总是对应相同的最大数据范围。也许得用vba了。

追问

还是谢谢您！