您能帮助我满足以下要求吗?
我已在 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。 相同的公式,12
但D1:
=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"
从技术上来说#不适用,它是所有可能答案的数组,但由于我们没有以数组公式的形式输入公式,因此它只给出了第一个值