☰

在 Excel 中的 x,y 表中插值和查找的最简单方法是什么？

tag-icon • tag-icon microsoft-excel microsoft-excel-2010
在 Excel 中的 x,y 表中插值和查找的最简单方法是什么？

我想根据下表的 x、y 数据进行查找和插值。我希望公式尽可能简单，以减少可能出现的错误。整个表格大约有 50 行 x 30 列。我有大约 20 个这样的表格。以下是其中一个的摘录：

   A       B      C    D 
1        0.1    0.2   0.3 
2  2.4   450    300   50
3  2.3   500    375   52
4  2.1   550    475   55
5  1.8   600    600   60

例如，该方程应找到 x = 2.27 和 y = 0.15 的值

答案1

您有多个表，每个表的第一行和第一列都有索引值，表内部有一组值，每个值都与特定的行和列索引值相关联。

给定两个值，它们分别可能完全等于行或列索引，或者可能位于两个行索引值或两个列索引值之间，您需要根据第一行和第一列的两个给定值对表中的值进行直线插值。

计算插值的表

要对表中索引值之间的输入值进行插值，需要执行以下操作：

  • 大于（或等于）垂直输入值的最小垂直索引值的行号vnum。由于表格第 1 列中的索引值按降序排列，因此可以使用以下方式获取：

    =MATCH(vnum,vrng,-1)

其中vnum是输入值，vrng是表第一列的垂直索引范围。该MATCH函数的第三个参数 -1 表示将执行“小于”查找。MATCH 函数的这种用法要求垂直索引范围按降序排列。

  • 小于（或等于）垂直输入值 ( vnum) 的最大垂直索引值。无法使用MATCH函数获取该值，因为垂直索引值未按 所需的升序排序MATCH。而是使用以下数组公式。

    =MIN(IFERROR(1/(vnum>=vrng)*ROW(INDIRECT("1:"&ROWS(vrng))),ROWS(vrng)))

此公式中的关键元素是vnum>=vrng，它生成一个布尔数组，其中第一个元素TRUE位于保存小于输入值的最大垂直索引值的行位置。（“>=”的使用可能看起来违反直觉；这是必要的，因为列中的索引是按降序排列的。）公式的其余部分将此行位置转换为行号。

这两个上行号和下行号用于计算括住上下输入值的索引值以及与这些索引值相对应的内部表值。

水平索引范围的相应列号和列值以类似的方式构造。

其余步骤通过插值算法完成。

剩余步骤为计算输入值对的插值

有了这么多步骤，计算多个输入值的插值量似乎不太现实。实际上，使用双向数据（“假设”）表相当容易。

双向数据表

通过使用单公式版本，可以进一步简化这些计算在多个表格中的设置。要使用它，需要设置命名范围vnum、hnum、vrng、hrng、datarng和。表格需要位于单独的工作表或单独的工作簿中。如果位于单独的工作表中，validvnum则validhnum必须将每个工作表的名称设置为具有工作表范围。

然后将单步计算公式输入到数据表的左上角单元格中。这个 2,100（减一）个字符的公式包含在可下载的工作表中。

可以使用以下方式下载包含此组计算的工作表此链接。

答案2

实际上，您有一个三维表 -x和y是独立变量，而您需要找到的是z。

我知道二维表的解决方案，但三维表的解决方案应该类似。

因此，如果您有二维表格，您可以使用 Excel 的“趋势线”功能找出最适合您数据的公式。使用该公式，您可以计算y任何x

  1. 为其创建线性散点图（XY）（插入=>散点图）；
  2. 创建多项式或移动平均趋势线，勾选“在图表上显示方程”（右键单击系列=>添加趋势线）；
  3. 将公式复制到单元格中，并x用所需的 x 值替换

在下面的屏幕截图中，A12：A16 包含x，B12：B16 包含y，C12 包含计算y任何 的公式x。

Excel 插值

至于您的情况（3-d 数据），Excel 也有能力构建 3-d 图表，但我不知道它们是否具有 3-d 趋势。

答案3

我已经编写了双线性插值的 VBA 代码，您可以在这里找到：https://github.com/DanGolding/Linear-and-bilinear-interpolation-in-Excel

此方法的优点是它是一个单一函数，可以轻松插入值网格，如下图所示（请注意，单元格根据值着色，以更好地显示插值效果）：

在此处输入图片描述

答案4

这是您要寻找的公式：给定两个点 (x1,y1) 和 (x2,y2)，要在它们之间进行插值，请使用以下公式：

y = (y2-y1)/(x2-x1)*(x-x1) + y1

其中 x 是输入，y 是输出。给定以下桌子

公式为： =IF(ISERROR(MATCH(O3,R$4:R$10)),S$4,IF(ISERROR(INDEX(R$4:R$10,MATCH(O$3,R$4:R$10)+1)),S$10,(INDEX(R$4:S$10,MATCH(O3,R$4:R$10)+1,2)-INDEX(R$4:S$10,MATCH(O3,R$4:R$10),2))/(INDEX(R$4:S$10,MATCH(O3,R$4:R$10)+1,1)-INDEX(R$4:S$10,MATCH(O3,R$4:R$10),1))*(O3-INDEX(R$4:S$10,MATCH(O3,R$4:R$10),1)) +索引（R$4:S$10，匹配（O3，R$4:R$10），2）））

iferror 函数处理终点。它们将值保留在终点处，而不是进行推断。

相关内容