搜索
X\Y-3-15680120-20.250.250.250.250.250.25-10.230.230.240.240.240.2400.170.20.20.20.20.210.140.160.170.170.170.1750.110.140.150.150.150.1580.090.120.130.130.130.13100.080.10.110.120.120.12140.070.090.10.10.110.11170.060.080.090.090.10.1190.060.070.080.090.090.09220.050.070.080.080.080.09x=8.5y=6.02z=0.1275注：输入x和y自动计算对应的z。X轴和Y轴必须升序排列。X\Y不能颠倒，会影响矩阵计算。使用了定义名称x_axis和y_axis。f区域使用条件格式，根据x和y值动态高亮。已处理边界条件。算法：双线性插值Excel里用大括号表示向量。m*1矩阵即行向量，Excel里元素用逗号隔开：[1-xx]=[10]+[-11]*x1*n矩阵即列向量，Excel里元素用分号隔开：[1-yy]^T=[10]^T+[-11]^T*y符号不会写，就写成转置形式了。于是，得出三矩阵相乘的公式（Excel公式支持分行，这样方便看）：=MMULT(MMULT({1,0}+{-1,1}*FORECAST(B16,{0;1},OFFSET($A$1,MATCH(B16,x_axis),0,2,1)),OFFSET($A$1,MATCH(B16,x_axis),MATCH(B17,y_axis),2,2)),{1;0}+{-1;1}*FORECAST(B17,{0,1},OFFSET($A$1,0,MATCH(B17,y_axis),1,2)))分步验证（未考虑边界条件）：68080.130.13100.120.1268066.028080.130.13→80.130.130.138.50.12750.12758.50.12750.12750.1275100.120.12100.120.120.12或者：66.028066.028080.130.130.13→80.130.130.13100.120.120.128.50.12750.12750.1275100.120.120.12