问题

已知

X和Y有下表的对应关系，当一个新的4<x<11时，对应的y是多少？
例如：x=10，y=？

X	Y
4	54
6	62
9	70
11	100

解决思路

采用线性插值的方法，找到x前后两个数x1和x2，线性计算
y=y1 + x*(y2-y1)/(x2-x1)即可

因此 x=10，y应该是85

Excel函数介绍

trend（）函数

说明

返回线性趋势值。 找到适合已知数组 known_y’s 和 known_x’s 的直线（用最小二乘法）。 返回指定数组 new_x’s 在直线上对应的 y 值。

语法

TREND(known_y’s, [known_x’s], [new_x’s], [const])

TREND 函数语法具有下列参数：
◾ Known_y’s 必需。
关系表达式 y = mx + b 中已知的 y 值集合。
如果数组 known_y’s 在单独一列中，则 known_x’s 的每一列被视为一个独立的变量。如果数组 known_y’s 在单独一行中，则 known_x’s 的每一行被视为一个独立的变量。

◾ Known_x’s 必需。
关系表达式 y = mx + b 中已知的可选 x 值集合。
数组 known_x’s 可以包含一组或多组变量。 如果仅使用一个变量，那么只要 known_x’s 和 known_y’s 具有相同的维数，则它们可以是任何形状的区域。 如果用到多个变量，则 known_y’s 必须为向量（即必须为一行或一列）。
如果省略 known_x’s，则假设该数组为 {1,2,3,…}，其大小与 known_y’s 相同。

◾ New_x’s 必需。
需要函数 TREND 返回对应 y 值的新 x 值。
New_x’s 与 known_x’s 一样，对每个自变量必须包括单独的一列（或一行）。 因此，如果 known_y’s 是单列的，known_x’s 和 new_x’s 应该有同样的列数。 如果 known_y’s 是单行的，known_x’s 和 new_x’s 应该有同样的行数。
如果省略 new_x’s，将假设它和 known_x’s 一样。
如果 known_x’s 和 new_x’s 都省略，将假设它们为数组 {1,2,3,…}，大小与 known_y’s 相同。

◾ Const 可选。
一个逻辑值，用于指定是否将常量 b 强制设为 0。
如果 const 为 TRUE 或省略，b 将按正常计算。
如果 const 为 FALSE，b 将被设为 0（零），m 将被调整以使 y = mx。

直接用trend函数结果

=TREND(B2:B5,A2:A5,D3)

结果显然与分段线性插值的结果有差别，因为trend函数代表的是一组数据的整体趋势，而不是局部的线性。

因此采用trend+offset+match+frequency函数的方式实现

offset（）函数

说明

返回对单元格或单元格区域中指定行数和列数的区域的引用。 返回的引用可以是单个单元格或单元格区域。 可以指定要返回的行数和列数。

语法

OFFSET(reference, rows, cols, [height], [width])

OFFSET 函数语法具有下列参数：

◾ Reference 必需。 要作为偏移基准的参照。 引用必须引用单元格或相邻单元格区域。否则, OFFSET 返回 #VALUE! 。

◾ Rows 必需。 需要左上角单元格引用的向上或向下行数。 使用 5 作为 rows 参数，可指定引用中的左上角单元格为引用下方的 5 行。 Rows 可为正数（这意味着在起始引用的下方）或负数（这意味着在起始引用的上方）。

◾ Cols 必需。 需要结果的左上角单元格引用的从左到右的列数。 使用 5 作为 cols 参数，可指定引用中的左上角单元格为引用右方的 5 列。 Cols 可为正数（这意味着在起始引用的右侧）或负数（这意味着在起始引用的左侧）。

◾ 高度 可选。 需要返回的引用的行高。 Height 必须为正数。

◾ 宽度 可选。 需要返回的引用的列宽。 Width 必须为正数。

采用offset函数找到查表区间

MATCH（）函数

使用 MATCH 函数在 范围 单元格中搜索特定的项，然后返回该项在此区域中的相对位置。 例如，如果 A1:A3 区域中包含值 5、25 和 38，那么公式 =MATCH(25,A1:A3,0) 返回数字 2，因为 25 是该区域中的第二项。

语法

MATCH(lookup_value, lookup_array, [match_type])

MATCH 函数语法具有下列参数：

◾ lookup_value 必需。 要在 lookup_array 中匹配的值。 例如，如果要在电话簿中查找某人的电话号码，则应该将姓名作为查找值，但实际上需要的是电话号码。

lookup_value 参数可以为值（数字、文本或逻辑值）或对数字、文本或逻辑值的单元格引用。

◾ lookup_array 必需。 要搜索的单元格区域。

◾ match_type 可选。 数字 -1、0 或 1。 match_type 参数指定 Excel 如何将 lookup_value 与 lookup_array 中的值匹配。 此参数的默认值为 1。

下表介绍该函数如何根据 match_type 参数的设置查找值。

◾MATCH 返回匹配值在 lookup_array 中的位置，而非其值本身。 例如，MATCH(“b”,{“a”,“b”,“c”},0)返回 2，即“b”在数组 {“a”,“b”,“c”} 中的相对位置。

◾匹配文本值时，MATCH 函数不区分大小写字母。

◾如果 MATCH 函数查找匹配项不成功，它会返回错误值 #N/A。

◾如果 match_type 为 0 且 lookup_value 为文本字符串，您可在 lookup_value 参数中使用通配符 - 问号 (?) 和星号 (*) 。 问号匹配任意单个字符；星号匹配任意一串字符。 如果要查找实际的问号或星号，请在字符前键入波形符 (~)。

采用match函数找到查表区间的位置

FREQUENCY（）函数

FREQUENCY 函数计算值在某个范围内出现的频率，然后返回一个垂直的数字数组。 例如，使用函数 FREQUENCY 可以在分数区域内计算测验分数的个数。 由于 FREQUENCY 返回一个数组，所以它必须以数组公式的形式输入。

语法

FREQUENCY(data_array, bins_array)

FREQUENCY 函数语法具有下列参数：

◾ data_array 必需。 要对其频率进行计数的一组数值或对这组数值的引用。 如果 data_array 中不包含任何数值，则 FREQUENCY 返回一个零数组。

◾ bins_array 必需。 要将 data_array 中的值插入到的间隔数组或对间隔的引用。 如果 bins_array 中不包含任何数值，则 FREQUENCY 返回 data_array 中的元素个数。

采用frequency函数对x进行定位

最后的结果

=TREND(OFFSET($B$2,MATCH(1,FREQUENCY(D3,A2:A5),)-2,,2),OFFSET($A$2,MATCH(1,FREQUENCY(D3,A2:A5),)-2,,2),D3)

结果完美！
仅做抛砖引玉，可以根据实际问题扩展完善。