A列和B列显示距离范围(从0到29km,30到59km等),C列显示相应的费用(货币金额),如下所示:
1. | A | B | C |
2. | 0 | 29 | 400 |
3. | 30 | 59 | 500 |
4. | 60 | 89 | 700 |
5. | 90 | 119 | 900 |
等到2000左右 .
我需要在一个给定的单元格中输入一个值 - 在 columns A and B
范围内的距离(最终在另一个工作表上),并获得匹配结果 - 另一个单个单元格中的费用(最终在另一个工作表上) .
我得到了通用公式,但只是在相应行中显示匹配的程度,根据 E column
中的公式将非匹配行中的值显示为"0": =IF(AND($D$3>=$A3;$D$3<=$B3);$C3;0)
其中 cell D3
是用于向公式单元格提取费用的用户距离输入单元格 .
回顾一下,我得到一列“0”和一个与我的ABC范围值对应的匹配,但相反,我只需要一个匹配的单个单元格,位于我选择的位置 .
3 回答
您应该使用 MATCH 函数, match_type 等于 1 ("less than"匹配)来查找行号,然后使用 INDEX 函数从费用范围内的相应行号中获取费用 .
假设您的查找值在单元格B7中 . 使用公式:
在这个例子中
在INDEX的第二个参数中_UP9_ MATCH返回A2:A5范围中第一行的编号,其中单元格值小于B7且
实际上你不需要B列(前提是它只指定距离的上边界,它应该等于下一个距离仓的下边界) .
请参阅有关INDEX和MATCH函数的文档,并在“公式”选项卡的“函数库”中查看“查找和引用”组以获取更多信息 .
使用您的引用使用VLOOKUP:
替代解决方案,尝试使用趋势线公式猜测数量 .
取B和C列,添加散点图,然后右键单击绘图上的其中一个点,然后单击
Add Trendline...
. 尝试不同的趋势线设置并检查R2值,越接近1越好 .看下面的例子,只有4个点就不容易找到一个好的趋势线公式,但它仍然给出多项式趋势线的R2 = 0.9968 . 你可以看到趋势线的公式,复制粘贴到B10并进行调整,在这个例子中它是:
值718足够接近700的预期值 . 如你所说,“它上升到2000”,有了这么多点,我们将得到一个更好的拟合公式来更好地猜测数量 .