我需要将表 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),"")