Excel - 比较两列，如果匹配从匹配行中减去其他两列的值 [英] Excel - compare two columns, if match subtract values of other two columns from matching rows

问题描述

我试图在单元格中比较两列相同的值。并不是所有的都匹配。对于匹配的那些，即col B1中的单元格与col D2中的单元匹配，我想减去A1-C2。 A和B来自一个数据池，C和D来自另一个数据库。应比较它们，如果字符串匹配，则减去值。

I'm trying to compare two columns for identical values in cells. Not all of them match. For those that match, i.e. cell in col B1 matches a cell in col D2, I would like to subtract A1-C2. A and B come from one pool of data, and C and D from another. They should be compared, and values subtracted if the strings match.

谢谢

推荐答案

要获取匹配字符串的列表，请使用此公式。我把它放在F1中：

To get a list of matching strings then use this formula. I put it in F1:

=IFERROR(INDEX($D$1:$D$500,AGGREGATE(15,6,ROW($1:$500)/(COUNTIF($B$1:$B$500,$D$1:$D$500)),ROW(1:1))),"")

并尽可能多地复制下来。如果您有2010或更高版本，则此公式仅适用。

And copy down as far as you wish. This formula only works if you have 2010 or later.

如果您已将2007或更早版本替换为此数组公式：

If you have 2007 or earlier than replace it with this array formula:

=IFERROR(INDEX($D$1:$D$500,SMALL(IF(COUNTIF($B$1:$B$500,$D$1:$D$500),ROW($1:$500)),ROW(1:1))),"")

你在离开编辑模式时，需要使用Ctrl-Shift-Enter而不是Enter进行确认。

You will need to confirm it with Ctrl-Shift-Enter instead of Enter when leaving edit mode.

然后在G1中，我将：

=IF(F1<>"",SUMIF(B:B,F1,A:A)-SUMIF(D:D,F1,C:C),"")

并复制下来相同数量的行。

And copied down the same number of rows.

这篇关于Excel - 比较两列，如果匹配从匹配行中减去其他两列的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！