excel - 查找列表中小于另一个的值

Question

我有以下 Excel 设置，该设置很大，但这是一个简化的设置:

     A     B         C       D       E
   1 Site1 X-Given   Y-Given Site2   X-New-Given 
   2 A     10        400     A       15         
   3 A     20        500     A       25          
   4 A     30        600     A       30          
   5 A     40        700     B       35          
   6 A     50        800     B       30          
   7 B     2         400     B       15          
   8 B     25        500     B       25          
   9 B     300       600     B       30 

这就是我正在尝试做的事情:

给定“E 列”中的值，我想找到一个值<=“E 列”中的值，只要它们位于同一站点

然后我想做同样的事情，只找到“E 列”中的 > 值

因此所需的公式将是两个单元格，一个具有较大的值，一个具有较小的值

Example 1: Given 15 (E2) ---> would return "10" (from B2) and "20" (from B3)
Example 2: Given 15 (E7) ---> would return "2" (from B7) and "25" (from B8)

如您所见，输出会根据站点而变化，这是我似乎无法理解的部分!

Answer 1

这些是使用 Control+Shift+Enter(而不仅仅是 Enter)输入的数组公式。如果您输入正确，Excel 会在它们周围加上大括号 - 不要自己输入大括号。

较小

{=MAX(($A$2:$A$9=D2)*($B$2:$B$9<=E2)*($B$2:$B$9))}

更大

{=MIN(IF((($A$2:$A$9=D2)*($B$2:$B$9>=E2)*($B$2:$B$9))=0,"",$B$2:$B$9))}

较小的值会找到小于或等于 E2 的站点的最大值。较大值查找大于或等于 E2 的站点的最小值。填写完成剩下的事情。

公式中的数组对于 FALSE 返回 0，对于 TRUE 返回 1，因此只要其中一行为 FALSE，MIN 就始终返回 0。这就是为什么你必须使用 IF 语句。通过注入(inject) FALSE 字符串，它会忽略它们，因为 MIN 会忽略字符串。