根据列标题比较两个不同表中的单元格值组。

根据列标题比较两个不同表中的单元格值组。

我需要将表 A 中的一组值与表 B 中的另一组值进行比较。如果两个表中的列标题(每个表中的列名称)相同,则将比较下面行中的相应值。我有两个问题:1) 如何检查一个表中的列标题是否与另一个表中的列标题匹配;2) 如何选择和比较相应列中的值?

在下面的第一个示例中,两个表中的 3 个列标题都不匹配,尽管它们有一个匹配的列标题 ID。如果两个表中的所有列标题都相同,我可以使用什么公式来检查具有多个列标题的表并返回 TRUE 值?

ID  Name    Address           ID    Item    Quan
1   A       123A               1    U       22
2   B       234B               2    V       33
3   C       345C               3    W       44
4   D       456D               4    X       55
5   E       567E               5    Y       66
6   F       678F               6    Z       77
7   G       789G               7    T       88

在下面的第二个示例中,列标题匹配。但是,尽管 ID 和 Address 列中的值都匹配,但 Name 地址中的值却不匹配。一旦所有列标题都匹配,我可以使用什么公式来比较所有 3 列中的值?

ID  Name    Address               ID    Name    Address
1   A       123                    1    D   123
2   B       234                    2    C   234
3   C       345                    3    E   345
4   D       456                    4    B   456
5   E       567                    5    A   567
6   F       678                    6    F   678

下面是我的最后一个例子,虽然列标题的名称匹配,但列的顺序不同。我可以使用什么公式来比较 ID、名称和地址列中的值,而不管它们的顺序如何?

ID  Name    Address               ID    Address Name
1   A       123                    1    123     A
2   B       234                    2    234     B
3   C       345                    3    345     C
4   D       456                    4    456     D
5   E       567                    5    567     E
6   F       678                    6    678     F

任何帮助深表感谢。

答案1

这取决于您是想不定期地快速完成这项工作,还是经常进行这项工作。如果是前者,我的建议是对工作表进行一些操作,然后使用 IF 函数公式。如果是后者,那么一些宏工作(本质上是操作工作表,然后执行相同的计算)将是您的最佳答案。我将使用前一种方法来回答。

示例1:我将复制两张工作表的第 1 行,然后将其粘贴:转换为新工作表,这样它们每张工作表都占用一列(例如 A 列和 B 列)。如果列需要按顺序返回 TRUE,那么我将向 C1 添加以下公式并将其复制到列标题信息的最后一行。

IF(A1=B1, TRUE, FALSE)

然后,在 D1 中插入公式:

=COUNTIF(C:C, FALSE)

D1 中的任何非零值表示列不匹配。

如果列不需要按相同顺序返回 TRUE,则在复制过程之后对每列进行排序,然后添加 IF 和 COUNTIF 公式。

示例2:在您确定列确实匹配(并且它们的顺序相同)后,我比较值的方法是在每个工作表的右侧插入一个新列,该列将原始列中的所有数据连接起来。根据您提供的 Ex2 数据,此公式将在每个工作表的单元格 D1 中执行此操作(将公式复制到最后一行):

=CONCATENATE(A1, "##", B1, "##", C1)

我添加了哈希值以使连接的数据保持离散。否则,如果您说,工作表 A 的 A 列中有 1,B 列中有 10,而工作表 B 的 A 列中有 11,B 列中有 0,那么它们都会连接成 110。

接下来,向其中一个工作表添加类似的 IF 公式:

=IF(sheet1!D1=sheet2!D1, TRUE, FALSE)

再次将 COUNTIF 公式添加到下一列,查看是否有任何值不匹配。

=COUNTIF(D:D, FALSE)

示例 3(顺序无关紧要),我会对数据进行排序,使列按字母顺序排列,然后执行 Ex2 的解决方案。要正确排序数据,请选择信息,然后在“数据”->“排序和筛选”功能区组中单击排序。在出现的对话框中,单击按钮Options....并选择Sort left to right。当字段区域中的列标题从“列”更改为“行”时,在框中选择第 1 行Sort by。这会将两个工作表中的列顺序更改为(在您的示例中):地址、ID、名称。然后,正如我所说,按照 Ex2 的过程进行操作。

现在,如果您想频繁执行这些计算,您可以将上述步骤编码到 Excel 宏中。这可能是另一个问题的主题。

答案2

不确定您通过“比较值”来寻找什么,但为了回答这个问题,我使用它MATCH来返回找到匹配值的行。

我根据你提供的数据,整理了一下,对应的公式如下……

在此处输入图片描述

确定标题是否匹配...

  • 单元格 I1 包含 Control-Shift-Enter (CSE) 公式{=IF(ISERROR(SUM(MATCH(E2:G2,A2:C2,0))),FALSE,TRUE)}
  • 单元格 I11 包含 CSE 公式{=IF(ISERROR(SUM(MATCH(E12:G12,A12:C12,0))),FALSE,TRUE)}
  • 单元格 I20 包含 CSE 公式{=IF(ISERROR(SUM(MATCH(E21:G21,A21:C21,0))),FALSE,TRUE)}

为了进行比较...

  • 单元格 I2 (填充至 K2) 包含=IF($I$1,OFFSET($E$2,0,MATCH(A$2,$E$2:$G$2,0)-1),"")
  • 单元格 I12 (填充至 K12) 包含=IF($I$11,OFFSET($E$12,0,MATCH(A$12,$E$12:$G$12,0)-1),"")
  • 单元格 I21 (填充至 K21) 包含=IF($I$20,OFFSET($E$21,0,MATCH(A$21,$E$21:$G$21,0)-1),"")

进行价值观的比较……

  • 单元格 I3,向下填充至 I9,填充至 K9,包含=IF($I$1,MATCH(A3,OFFSET($E$3,0,MATCH(A$2,$E$2:$G$2,0)-1,COUNT($A$3:$A$9),1),0),"")
  • 单元格 I13,向下填充至 I18,填充至 K18,包含=IF($I$11,MATCH(A13,OFFSET($E$13,0,MATCH(A$12,$E$12:$G$12,0)-1,COUNT($A$13:$A$18),1),0),"")
  • 单元格 I22,向下填充至 I27,填充至 K27,包含=IF($I$20,MATCH(A22,OFFSET($E$22,0,MATCH(A$21,$E$21:$G$21,0)-1,COUNT($A$22:$A$27),1),0),"")

相关内容