利用数组函数实现内插法求解

巴巴立1983

求各位大神帮我解决一下如何利用数组函数实现下表内插法求解，希望大神们帮我制作一下结果，最好把公式详细解释一下，我想多学习学习

hugeinfo

不好意思理解有误..........

miaojohn45

=LET(A,TOCOL(A2:A12+B1:K1),B,TOCOL(B2:K12),X,XLOOKUP(5236,A,B,,-1),Y,XLOOKUP(5236,A,B,,1),MOD(5236,100)/100*(Y-X)+X)

hugeinfo

=TREND(OFFSET($A$1,MATCH(FLOOR(A15,1000),A2:A12),MATCH(A15-FLOOR(A15,1000),B1:K1),1,2),OFFSET(A1,,MATCH(A15-FLOOR(A15,1000),B1:K1),,2),A15-FLOOR(A15,1000))

巴巴立1983

hugeinfo 发表于 2024-2-24 16:19
=TREND(OFFSET($A$1,MATCH(FLOOR(A15,1000),A2:A12),MATCH(A15-FLOOR(A15,1000),B1:K1),1,2),OFFSET(A1,,MA ...

感谢大佬

巴巴立1983

miaojohn45 发表于 2024-2-24 15:54
=LET(A,TOCOL(A2:A12+B1:K1),B,TOCOL(B2:K12),X,XLOOKUP(5236,A,B,,-1),Y,XLOOKUP(5236,A,B,,1),MOD(5236 ...

感谢大佬

miaojohn45

1. =TREND(OFFSET(OFFSET(A1,,MATCH(MOD(A15,1000),B1:K1),,2),MATCH(A15,A2:A12),),OFFSET(A1,,MATCH(MOD(A15,1000),B1:K1),,2),MOD(A15,1000))

复制代码

1. =LET(x,OFFSET(A1,,MATCH(MOD(A15,1000),B1:K1),,2),y,OFFSET(x,MATCH(A15,A2:A12),),TREND(y,x,MOD(A15,1000)))

复制代码

这公式有个缺陷：X9XX数值计算错误。可以通过加辅助列解决：

我的城市你来过

=LET(x,MAX(IF(($A$1:$A$12=FLOOR(A15,1000))*($B$1:$K$1=FLOOR(A15-FLOOR(A15,1000),100)),COLUMN($B$1:$K$1),"")),y,OFFSET($A$1,MATCH(FLOOR(A15,1000),$A$1:$A$12,)-1,x-1,,2),INDEX(y,1)+((INDEX(y,2)-INDEX(y,1))*MOD(A15,100)/100))

=LET(x,OFFSET($A$1:$A$12,,MATCH(FLOOR(MOD(A15,1000),100),$B$1:$K$1),,2),y,CHOOSECOLS(x,2)-CHOOSECOLS(x,1),MIN(INDEX(x+y*MOD(A15,100)/100,MATCH(FLOOR(A15,1000),$A$1:$A$12,))))