腾讯视频/爱奇艺/优酷/外卖 充值4折起
单位教务部门拿来excel两张工作表，要把“成绩表”中成绩列数据复制到“学生基本信息表”成绩列中。我对照了两个表，发现几个难点(如图)。
(1) “学生基本信息表”的姓名与“成绩表”中的姓名不一样，“学生基本信息表”中的“王一”在“成绩表”中为“ 王 一”，出现了全角或半角空格。
(2) “学生基本信息表”中王小平在“成绩表”中无此人，即“学生基本信息表”的人数多于“成绩表”的人数。
(3) “成绩表”中成绩列为文本方式，且出现了全角数字。
(4) 每个表的数据为几千条。如果对“成绩表”中的姓名列进行排序，把成绩列进行复制粘贴到“学生基本信息表”中的成绩列，出现错位。
我通过excel函数substitute和lookup来解决，将“学生基本信息表”和“成绩表”进行了一些修改，实现将“成绩表”中的数据复制到“学生基本信息表”中，并且保持最终表格的清爽和数据的正确。
除去“成绩表”中全角或半角空格
首先，我要解决的问题是将“成绩表”中姓名的空格去掉，让“成绩表”中的学生姓名显示和“学生基本信息表”中的一样。此时我利用替换公式substitute(substitute(a2，"半角空格 "，"")，"全角空格"，"")。在d2单元格输入公式=substitute(substitute(a2，" "，"")，" "，"")，然后在整个d列复制公式。选择d列数据→进行复制，再选择a列所有数据→选择性粘贴→值和数字格式。
转化“成绩表”中成绩列为数字
删除了空格，下面的工作就是将“成绩表”中的数字规范为半角形式。同样利用函数substitute。在e2单元格输入公式=(substitute(c2，"。"，"."))*1，其中substitute(c2，"。"，".")表示句号“。”转化为点号“.”，“*1”表示转化为数字。然后在e列复制公式。同样进行选择性粘贴。选择e列数据→进行复制，再选择c列所有数据→选择性粘贴→值和数字格式。删除“成绩表”中d列、e列。
复制“成绩表”中数据到“学生基本信息表”
最后一步就是复制“成绩表”中的数据到“学生基本信息表”了，但是我们不能简单地利用复制粘贴来实现，因为我们的这个具体案例中包含没有成绩的同学，所以为了数据的正确性，我们知道查询函数lookup有一个特性就是在查询结束后会在指定的区域返回查询结果，我就用它来达到复制“成绩表”中数据的效果。
其语法为lookup(lookup_value，lookup_vector，result_vector)。其中lookup_value为要查找的数值，lookup_vector为只包含一行或一列的区域，且必须按升序排列，否则要返回错误，result_vector 返回只包含一行或一列的区域。
如果函数lookup找不到lookup_value，则查找lookup_vector中小于或等于lookup_value的最大数值，如果lookup_value 小于lookup_vector 中的最小值，函数lookup 返回错误值 #n/a，利用这个特性，我们把公式改为=lookup(1，0/(条件)，引用区域)，条件——产生的是逻辑值true、false数组，0/true=0，0/false=#div0!，即lookup的第2参数便是由0、#div0!组成的数组(都比1小)，如果找到满足条件，就返回对应行引用区域的值;如果没有找到满足条件的记录则返回#n/a错误，从而实行精确查找。
在“学生基本信息表”中d2输入公式=lookup(1，0/(成绩表!a$2:a$5=b2)，成绩表!c$2:c$5)。在没找到数据的一栏出现了#n/a，影响了表格的美观。稍微改进一下，利用isna函数判断是否为#n/a，如果是，设置为空。
因此在d2输入公式=if(isna(lookup(1，0/(成绩表!a$2:a$5=b2)，成绩表!c$2:c$5))，""，lookup(1，0/(成绩表!a$2:a$5=b2)，成绩表!c$2:c$5))，这样#n/a不会出现在单元格中。最后在d列进行公式复制即可。