浙江龙网

  1. 首页 > 生活百科 > >

用Excel函数快速整理错乱成绩表2020新发布

电脑知识excel

单位教务部门拿来Excel两张工作表 ， 要把“成绩表”中成绩列数据复制到“学生基本信息表”成绩列中 。 我对照了两个表 ， 发现几个难点(如图) 。

用Excel函数快速整理错乱成绩表2020新发布

文章插图

用Excel函数快速整理错乱成绩表2020新发布

文章插图

(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 ， 如果是 ， 设置为空 。
【用Excel函数快速整理错乱成绩表2020新发布】因此在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列进行公式复制即可 。

    推荐阅读


    上一篇：Access数据库与Excel工作簿巧转换收获很多的

    下一篇：华为左滑新闻怎么设置推荐视频