查找范围内的数字

查找范围内的数字

您能帮助我满足以下要求吗?

我已在 A 列和 B 列中维护了范围。C 列中提到了每个范围的相关值。例如,如下所示:

| Column A | Column B | Column C |
| 1        | 10       | A        |
| 15       | 20       | B        |
| 21       | 30       | C        |

现在我在另一列 E 中插入了一个数字,例如 17。现在我想知道这个数字属于哪个范围,以便可以引入 C 列中的相关值。在这个例子中,17 属于 15 到 20 的范围,位于第 2 行,其在 C 列中的值为 B。因此对于 17 这个数字,输出应该是 B。如果我输入 25,输出应该是 C。但是如果我输入 12,输出应该是 #N/A,因为这个数字不属于任何范围。

请问是否有任何 Excel 公式可用于获取此类输出。我更喜欢使用公式而不是任何编码。感谢您花时间在这方面指导我。

诚挚问候,Sridhar

答案1

是的,可以做到——您需要在表格中增加一行才能使其工作。

我添加了行

| 0        | 0        | #N/A     |

作为第一行放入表格中,因此表格现在占据A1:C4

我使用INDEX函数来决定需要返回哪一行。
为了计算行,我使用SUMPRODUCT来计算 INDEX 将使用的行。
完整公式如下:

=INDEX(C1:C4,SUMPRODUCT(--(A1:A4<=D1),--(B1:B4>=D1),ROW(C1:C4)))

,其中包含要检查的值D1

使用以下公式D1包含17

=INDEX(C1:C4,SUMPRODUCT(--(A1:A4<=17),--(B1:B4>=17),ROW(C1:C4)))
=INDEX(C1:C4,SUMPRODUCT(--({0,1,15,21}<=17),--({0,10,20,30}>=17),{1,2,3,4}))
=INDEX(C1:C4,SUMPRODUCT(--({True,True,True,False}),--({False,False,True,True}),{1,2,3,4}))
=INDEX(C1:C4,SUMPRODUCT({1,1,1,0}),({0,0,1,1}),{1,2,3,4}))
=INDEX(C1:C4,(1*0*1 + 1*0*2 + 1*1*3 + 0*1*4))
=INDEX(C1:C4,3)
="B"

如果数字超出范围,则 SUMPRODUCT 中不会有值行不等于的部分0,因此它将返回范围内的第一个值,即 #N/A。 相同的公式,12D1

=INDEX(C1:C4,SUMPRODUCT(--(A1:A4<=12),--(B1:B4>=12),ROW(C1:C4)))
=INDEX(C1:C4,SUMPRODUCT(--({0,1,15,21}<=12),--({0,10,20,30}>=12),{1,2,3,4}))
=INDEX(C1:C4,SUMPRODUCT(--({True,True,False,False}),--({False,False,True,True}),{1,2,3,4}))
=INDEX(C1:C4,SUMPRODUCT({1,1,0,0}),({0,0,1,1}),{1,2,3,4}))
=INDEX(C1:C4,(1*0*1 + 1*0*2 + 0*1*3 + 0*1*4))
=INDEX(C1:C4,0)
="#N/A"

从技术上来说#不适用,它是所有可能答案的数组,但由于我们没有以数组公式的形式输入公式,因此它只给出了第一个值

相关内容