☰

查找列中的下一个更高值

tag-icon • tag-icon microsoft-excel worksheet-function lookup
查找列中的下一个更高值

我有两列，A和B。列中B包含我需要在列中查找的值A。但是，我不需要找到确切的对应值，我需要下一个更高的值。

例如：

Column A    Column B   

   2           3
   4           4
   5           5
   7           6
   8           8
   9           9

5因此，对于列中的值B，我想7从列中返回A。

我想我可能需要某种形式的查找/索引匹配函数，但我自己无法编写公式。

答案1

已排序

A最简单的公式适用于列按升序排序的情况：

工作表截图

输入以下公式C1，然后按 ctrl-enter/copy-paste/fill-down/auto-fill 将其填充到表格其余列中：

=INDEX(A:A,1+MATCH(B1,A:A,1))

解释：

作为1第三个参数意味着MATCH()它会找到小于或等于第一个参数的最大值。将该1索引添加到该索引会得到下一个更大数字的索引。INDEX()然后该函数提取该数字。

请注意，我在列末尾添加了一个额外的值A。这是为了应对没有下一个更高值的特殊情况。

未分类

对于列未排序的情况A（如果已排序也有效），公式会稍微复杂一些：

数组中输入（Ctrl++ ）以下公式并将Shift其复制粘贴/填充到表格列的其余部分（不要忘记删除 and ）：EnterC1{}

{=SMALL(IF($A$1:$A$6>B1,$A$1:$A$6),1)}

解释：

该函数返回数组中第 n 个最小的值，SMALL(array,n)忽略布尔值IF()。由于该函数的第三个参数的默认值为，因此只检查FALSE大于列中值的值，从而得出下一个更高的值。B

A请注意，列不需要特殊的终止值，因为#NUM!如果列 中没有A大于列中的值的值，则会导致错误B。

最后，正如 aventurin 指出的那样，还有一种替代的、类似的公式，无论如何排序都可以起作用（但有一个重要的警告）。

对于 Excel 2016+：

=MINIFS($A$1:$A$6,$A$1:$A$6,">"&B1)

这是有效的，因为该MINIFS()函数在提取最小值之前会过滤掉不符合条件的值。

对于早期版本的 Excel：

{=MIN(IF($A$1:$A$6>B1,$A$1:$A$6))}

其工作原理与SMALL()函数相同——它忽略了函数生成的布尔值IF()。

警告：

如果零可以作为正确的下一个更高值，则=MINIFS()和公式都将无法正常工作，因为当存在{=MIN(IF())}是没有下一个更高的值。（这与第一个公式在列末尾添加额外值的原因相同A- 如果没有更高的值，该公式也会返回零。）

答案2

您可以使用例如数组函数{=MIN(IF(A1:A6 > B1; A1:A6))}或{=MIN(IF(A1:A6 > B1; A1:A6; 1000))}（以 1000 作为后备值）。

它取 A 列中所有大于 B 列当前单元格的值的最小值（此处B1）。因此，两列都无需排序。

使用 Excel >= 2016 您也可以使用该MINIFS函数。

请注意，必须按 插入数组函数Ctrl+Shift+Enter。

相关内容