2009-12-20 EXCEL比对数据

表格A中存有一组号码（文本格式）及对应的用户名称，表格B中存有另一组号码（数值格式）及对应的经理名称。现在想查询表格A中的号码哪些在表格B中的号码中也存在，并显示对应的经理名称。操作步骤如下：

1)将两个表格的内容整合到一起

为了方便操作，将表格B中的号码列和经理名称列复制到表格A中。复制后新表格的情况如下：

A列：表A中的用户名称（702行）
B列：表A中的号码（702行）
C列：表B中的号码（1044行）
D列：表B中的经理名称（1044行）

第1行为列名称，数据从第2行开始。

[@more@]

2)将C列中的号码转换为文本格式

直接选中C列设置单元格格式为文本后，是以科学计数形式显示的，这样子是无法进行比对的。必须双击单元格后才会显示完整的号码，用格式刷无法复制这显示效果，而一一点击又太麻烦。可以使用TEXT函数进行批量转换。
在E2输入 =TEXT(C2,0) ，其中的 0 表示数值转换为文本后的数字格式。然后选中E2，拖动到E1044。复制E列的内容，选择性粘贴-粘贴值到C列，即完成了格式转换。最后清除E列的内容。

3)比对号码并返回对应的经理名称

方法1：使用VLOOKUP函数

此方法使用VLOOKUP函数将B列中的号码逐一与C列中的所有号码进行比对，当在C列中发现相同的号码时，返回与C列中该号码在同一行的D列中的经理名称。
在E2输入 =VLOOKUP(B2,C$2:D$1044,2,FALSE) ，其中 C$2:D$1044 确定了VLOOKUP的处理区域为C、D两列（不包括第一行），用左上角的单元格和右下角的单元格进行标识，VLOOKUP将在区域中的第1列（即C列）中查找B2单元格的值。 $ 可以固定位置，这样当拖动复制单元格内容时，该值保持不变，不会递增。 2 表示发现匹配值时返回区域中第2列（即D列）的对应内容。 FALSE 表示进行精确匹配。
选中E2，拖动到E702，即得到比对结果，其中未找到匹配项的号码对应的经理名称为 #N/A 。

方法2：使用LOOKUP函数和IF、COUNTIF函数

此方法使用LOOKUP函数完成与VLOOKUP类似的操作，但是当在C列中找不到相同的号码时，会与C列中小于查询的号码的所有号码中的最大值匹配，而返回该最大值号码对应的经理名称。这并不是想要的，可以使用IF和COUNTIF函数进行筛选，得出正确的结果。
在F2输入 =LOOKUP(B2,C:C,D:D) ，其中 C:C 表示在C列中进行查询， D:D 表示找到相同的号码时返回D列中的对应值。选中F2，拖动到F702。
在G2输入 =IF(COUNTIF(C:C,B2),F2,"#N/A") ，其中 COUNTIF(C:C,B2) 返回C列中与B2中的号码相同的号码的总数。选中G2，拖动到G702。如果找到相同的号码则返回相同号码的总数（大于0），外层IF函数的判断条件取TRUE值；如果找不到相同的号码则返回0，外层的IF函数的判断条件取FALSE值。这样就对F列的内容进行了筛选，得到最终结果。
当然还可以将上述两部合并，直接在F2中输入 =IF(COUNTIF(C:C,B2),LOOKUP(B2,C:C,D:D),"#N/A") ，然后选中F2，拖动到F702。

上述两种方法得出的结果完全相同，这也验证了方法的正确性。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11662464/viewspace-1029821/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11662464/viewspace-1029821/