excel数组公式在数值列中查找大于指定值的最小值

Excel技巧 2023-01-08

生产设计产品时，一种产品需要使用某种原材料，原料的尺寸是固定的几种类型，其尺寸已在B3:B11中列出，并按升序排列，如下图所示。从控制成本角度出发，希望用大于（或等于）产品要求的最小值来选择原料。

例如产品要求为“50”，从A列尺寸表可以看出最合理的材料尺寸为“55”。

假如产品要求在C4单元格中，可用下面的几个数组公式得到所需尺寸。

注意：数组公式输入完毕按Ctrl+Shift+Enter结束：

=MIN(IF(C4<=B4:B11,B4:B11))

说明：用IF函数取得仅包含大于或等于查找值的数组，再用MIN函数取得最小值。

=-LOOKUP(-C4,-LARGE(B4:B11,ROW(B4:B11)))

说明：由于LOOKUP函数在查找不存在于数组中的值时，会返回小于查找值的最大值，因而本例中直接用公式“=LOOKUP(C4,B4:B11)”会返回“45”。

本公式用LARGE函数与负号结合得到原数组的相反数，并按升序排列，然后对查找值取反后进行查找。

Large函数,返回数据集中第k个最大值。使用此函数可以根据相对标准来选择数值。例如，可以使用函数LARGE得到第一名、第二名或第三名的得分。

使用语法：LARGE(array,k)

row函数：返回引用的行号。

=INDEX(B4:B11,MATCH(TRUE,B4:B11>=C4,))

说明：用MATCH函数取得大于或等于查找值的位置，再用INDEX函数返回所需结果。