☰

尋找列中下一個較高的值

tag-icon • tag-icon microsoft-excel worksheet-function lookup
尋找列中下一個較高的值

我有兩列，A並且B. ColumnB具有我需要在 Column 中尋找的值A。但是，我不需要找到確切的對應值，我需要下一個更高的值。

例如：

Column A    Column B   

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

5因此，對於column 中的值B，我想7從 column返回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()函數提取該數字。

請注意，我在 column 末尾添加了一個額外的值A。這是針對沒有下一個較高值的特殊情況。

未分類

對於列未排序的情況A（如果排序也有效），公式稍微複雜一些：

數組中輸入 ( Ctrl+ Shift+ Enter) 以下公式C1，然後複製貼上/填充到表格列的其餘部分（不要忘記刪除 和{）}：

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

解釋：

此函數傳回數組中第 n 個最小值，SMALL(array,n)忽略布林值。由於IF()函數第三個參數的預設值為，因此僅檢查FALSE大於列中的值的值，從而得出下一個較高的值。B

請注意，columnA不需要特殊的終止值，因為#NUM!如果column 中沒有值A大於column 中的值，則會出現錯誤B。

最後，正如阿文圖林所指出的，有一個替代的、類似的公式，無論排序如何都有效（但有一個重要的警告）。

對於 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。

相關內容