如何使用Excel的HLOOKUP函数实现给定x值的y值插值计算

假设你的数据按以下结构放置：

• x-values（0.2、0.5、0.8...4.0）放在第一行（如A1:K1）
• y-values（0.1、0.11、0.12...0.32）放在第二行（如A2:K2）

完整插值公式 #

针对x=1.1的计算，可使用以下公式：

=HLOOKUP(1.1, A1:K2, 2, TRUE) + (1.1 - HLOOKUP(1.1, A1:K1, 1, TRUE)) / (INDEX(A1:K1, MATCH(1.1, A1:K1, 1)+1) - HLOOKUP(1.1, A1:K1, 1, TRUE)) * (INDEX(A2:K2, MATCH(1.1, A1:K1, 1)+1) - HLOOKUP(1.1, A1:K2, 2, TRUE))

公式拆解 #

• 获取区间下限的y值：HLOOKUP(1.1, A1:K2, 2, TRUE)
  查找小于等于1.1的最大x值（即1.0），返回对应的y值0.15
• 获取区间下限的x值：HLOOKUP(1.1, A1:K1, 1, TRUE)
  返回上述x值1.0
• 获取区间上限的x值：INDEX(A1:K1, MATCH(1.1, A1:K1, 1)+1)
MATCH(1.1, A1:K1, 1)找到下限x值的位置（第4列），+1得到上限x值（1.25）的位置，再用INDEX提取值
• 获取区间上限的y值：INDEX(A2:K2, MATCH(1.1, A1:K1, 1)+1)
  同理提取上限对应的y值0.18
• 执行线性插值计算：
  代入公式 下限y值 + (目标x - 下限x)/(上限x - 下限x)*(上限y值 - 下限y值) 得到最终结果

示例计算结果 #

代入数值后：

0.15 + (1.1 - 1.0)/(1.25 - 1.0)*(0.18 - 0.15) = 0.15 + 0.4*0.03 = 0.162

内容的提问来源于stack exchange，提问作者GeMa