Excel 中的插值
我有这样的源数据：
35 40
-15 15.0 15.1
-10 17.2 17.4
-5 19.7 19.8
并且我需要找到 (-16, 37) 中的值。我尝试过线性插值，但只能用 (x,y) 几个值来求解。你能帮我吗？
多谢， 安德里亚
I've a source data like this:
35 40
-15 15.0 15.1
-10 17.2 17.4
-5 19.7 19.8
and I need to find the value in (-16, 37). I've tried with linear interpolation but I can solve only with (x,y) couple of values. Could you help me?
thanks a lot,
Andrea
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
您可以尝试将平面拟合到您的数据：
然后使用最小二乘法和 6 个点 (-15,35,15.0) 求解 A、B、C，...
Excel 也许能够为您找到最佳值。
编辑
在这里，我插入了您的数据（绿色单元格），并使用求解器工具构建了最佳线性模型 z=Ax+By+C。 （x 为 35,40，y 为 -15,-10,-5）。 A、B、C 是左侧的蓝色单元格。那么粉色单元格是 Ax+By+C，红色单元格是 (date-model)^2。误差是所有这 6 个红细胞的总和。
然后使用求解器工具（需要从 Excel 选项激活）。将误差单元定义为目标（最小化），将 A、B、C 定义为变量单元。它将找到最佳的 A、B、C 值。
然后将这些值应用于您的请求（x=37 和 y=-16），得到结果 14.5。
You could try to fit a plane to your data:
Then solve for A,B,C using least-squares and the 6 points (-15,35,15.0),...
Excel may be able to find the optimal values for you.
Edit
Here I inserted your data (the green cells), and used the solver tool to build the optimal linear model z=Ax+By+C. (x is 35,40 and y is -15,-10,-5). A,B,C are the blue cells on the left. Then the pink cells are Ax+By+C, and the red cells are (date-model)^2. The error is the sum of all these 6 red cells.
Then use the Solver tool (needs to be activated from Excel options). Define the error cell as target (minimize) and A,B,C as variable cells. It will find the best A,B,C values.
These values are then applied to your request (x=37 and y=-16), giving the result 14.5.
安德里亚，看起来试图提供帮助的人（包括我）仍然对您的数据有困难。
要在 Excel 中对矩阵进行任何操作，您需要使用数组公式。输入完公式后，不要按 Enter 键。请改为按 Ctrl + Shift + Enter。如果您做得正确，您将在公式栏中看到您输入的公式被 {} 包围。
如果您认为您的数据是线性的，并且您想尝试 @tom10 确定的方法，请尝试
为每个提供的新 x 返回一个新 y 数组。
您也可以尝试矩阵公式。如果您可以将问题表达为线性方程组，Excel 有一些矩阵公式。 此处有一个很好的演练。仅当您的矩阵是方阵时，这些才会对您有所帮助。
Andrea, it looks as if the people trying to help (including me) are still having difficulty with your data.
To do anything with a matrix in excel, you need to use array formulas. When you've finished typing in the formula, don't press Enter. Press Ctrl + Shift + Enter instead. If you've done it right, you'll see the formula you entered surrounded by {}'s in the formula bar.
If you think your data is linear, and you want to try the approach identified by @tom10, try
Which will return an array of new y's for each new x provided.
You also might try matrix formulas. If you can express your problem as a system of linear equations, excel has a few matrix formulas. There's a good walkthrough here. These are only going to help you if your matrices are square.
线性插值将找到给定限制内的条目。即-5>=x>=-15。
要找到 -16 的值，您需要进行推断。
一种方法是将曲面拟合到点，例如求解 Ax = b) 或说它与 (-15, 37) 相同 - 所以取决于您的需求并选择正确的方法，这并不是本网站真正的目的。
如果您知道该方法，我们可以告诉您如何计算它，但您应该通过显示代码来展示您所尝试的内容。
Linear interpolation will find entries inside the limits given. ie -5 >= x >= -15.
To find value for -16 you will need to extrapolate.
One way is fitting fitting a surface to the points e.g solving Ax = b) or saying it is the same as (-15, 37) - so depends on your needs and choosing the correct method which is not really what this site is about.
If you know the method we can tell you how to calculate it but you should show what you have tried by showing your code.
您可以通过处理这三个独立的线性问题来做出合理的估计：
这是一个近似值，而不是最佳解决方案，但我怀疑 Excel 是否会进行正确解决问题所需的多元分析 - 您可以在其中将曲面拟合到这些点。不过我不太了解 Excel，可能出于无知而怀疑它。
You could make a reasonable estimate by treating this is three independent linear problems:
This is an approximation and not the best solution, but I doubt Excel does the multivariate analysis that you would need to solve the problem correctly -- where you would fit a surface to these points. I don't know Excel well though, and could be doubting it out of ignorance.
Andrea，如果你想在Excel中的二维中使用分段双线性插值，我可以推荐VBA函数此处。过去这对我来说效果很好。
Andrea, if you want to use piecewise bilinear interpolation in 2D in Excel, can I recommend the VBA function here. That's worked well for me in the past.
xongrid的Interp2dTab函数可用于双线性插值
The Interp2dTab function of xongrid can be used for bilinear interpolation
感谢大家，您的建议对我更好地理解问题非常有帮助。
一开始，我以错误的方式提出了问题，因为我从数据的图形表示（矩阵）中得到了提示。但是，经过一番“咕哝”:)我明白表示问题的正确方法是：
f(x1,x2) = y
所以，即 f(35, -15) = 15.0
要获取 x1 = 37 中的值x2 = -13 我可以使用双线性插值。
中问题的图形表示
http://en.wikipedia.org/wiki/File :Bilinear_interpolation.png
是我的灵感闪现。
感谢大家！
安德里亚
thanks to all, your suggests has been very helpful for me to understand better the problem.
In the beginning I've putted the problem in a wrong way because I've take cue from the graphical representation of the data, a matrix. But, after some "mumble" :) I've understand that the correct way to represent the problem was:
f(x1,x2) = y
so, i.e. f(35, -15) = 15.0
To obtain value in x1 = 37 and x2 = -13 I can use bilinear interpolation.
The graphical representation of the problem in
http://en.wikipedia.org/wiki/File:Bilinear_interpolation.png
was my flash of inspiration.
Thanks to all!
Andrea