在Excel中减去两个具有不同大小的列的值

I have the following table with four columns:

我有下表有四列:

> x1    y1      x2  y2
> 1.2   800.5   1.4  200
> 2.6   850.3   7.6  300
> 3.1   900.1   9.2  400
> 4.6   949.9   5.5  500
> 5.2   999.7   10.2 600
> 6.7   1049.5  8.4  700
> 7.8   1099.3      
> 8.3   1149.1      
> 9.1   1198.9      
> 10.1  1248.7

And I would like to have two more columns. One that contains a number that matches column x1 and x2 and the second one that has the difference between the values corresponding to the row match, like in the table below:

我想再增加两个专栏。一个包含与列x1和x2匹配的数字,另一个包含与行匹配对应的值之间的差异,如下表所示:

x1   y1       x2     y2   x3      y3 = y2-y1
1.2  800.5    1.4   200   1.2    (800.5-200)
2.6  850.3    7.6   300   5.2    (999.7-500)
3.1  900.1    9.2   400   7.8    (1099.3-300)
4.6  949.9    5.5   500   8.3    (1149.1-700)
5.2  999.7    10.2  600   9.1    (1198.9-400)
6.7  1049.5   8.4   700   10.1   (1248.7-600)
7.8  1099.3             
8.3  1149.1             
9.1  1198.9             
10.1 1248.7

In words, column x3 should contain the value that matches both x1 and x2 (with a condition that the numbers should be closer e.g. 1.2&1.4) and y3 should contain the value that results from subtracting y1 and y2, corresponding to the matching rows. Both x3 and y3 have to be evaluated using x1,x2,y2 and y3.

换句话说,列x3应该包含与x1和x2匹配的值(条件是数字应该更接近,例如1.2和1.4),y3应该包含减去y1和y2得到的值,对应于匹配的行。必须使用x1,x2,y2和y3来评估x3和y3。

So far I've been doing it manually, but it is exhausting for a long tables and I was thinking that there should be a method in excel, which I can't figure our. I tried with

到目前为止,我一直在手动完成它,但是对于长桌来说这是令人筋疲力尽的,我认为excel中应该有一个方法,我无法想象。我试过了

=INDEX(x1_array,MATCH(x2_array,y1_array,-1))

But it looks like an incomplete formula and I'm not even sure I'm doing it right, because of the differences in the column sizes. Can anyone help me with an idea on how to do this?

但它看起来像一个不完整的公式,我甚至不确定我做得对,因为列大小的差异。谁能帮助我了解如何做到这一点?

In case I wasn't clear, I have sketched an example for reference:

如果我不清楚,我已经勾画了一个例子供参考:

1 个解决方案

#1

Here is one way to solve it: see the below image with your data in col A-D.

以下是解决问题的一种方法:使用col A-D中的数据查看下图。

First, you need a column E that finds the closest x1 to match every x2. Here's a resource: https://exceljet.net/formula/find-closest-match

首先,您需要一个列E,它找到最接近每个x2的x1。这是一个资源:https://exceljet.net/formula/find-closest-match

Then, you need to MATCH the Y1 in col F. For example F2 is =MATCH(E2,$A$2:$A$11,0)

然后,你需要匹配col F中的Y1。例如F2 = = MATCH(E2,$ A $ 2:$ A $ 11,0)

Finally, OFFSET the MATCH from the col that contains y2s and you're done. In my example G2 is =OFFSET($A$1,F2,1) and H2 is simply =G2-D2.

最后,OFFSET来自包含y2s的col的MATCH,你就完成了。在我的例子中,G2是= OFFSET($ A $ 1,F2,1),而H2只是= G2-D2。

#1