用Excel函数快速整理错乱成绩表

任丽虎

任丽虎

2016-01-18 18:36

用Excel函数快速整理错乱成绩表(1) “学生基本信息表”的姓名与“成绩表”中的姓名不一样,“学生基本信息表”中的“王一”在“成绩表”中为“ 王 一”,出现了全角或半角空格。

单位教务部门拿来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有一个特性就是在查询结束后会在指定的区域返回查询结果,我就用它来达到复制“成绩表”中数据的效果。   

(本文来源于图老师网站,更多请访问https://m.tulaoshi.com)

其语法为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列进行公式复制即可。


  更多电脑网络文章推荐:


  百度云怎么看大片 图老师手把手教你  


  win10怎么取消开机密码 win10怎么修改开机密码


  截图快捷键是什么 电脑截图快捷键五种方法

(本文来源于图老师网站,更多请访问https://m.tulaoshi.com)
展开更多 50%)
分享

猜你喜欢

用Excel函数快速整理错乱成绩表

电脑网络
用Excel函数快速整理错乱成绩表

Excel:轻松合并成绩表

办公软件
Excel:轻松合并成绩表

s8lol主宰符文怎么配

英雄联盟 网络游戏
s8lol主宰符文怎么配

用Excel打出考试成绩表来

excel
用Excel打出考试成绩表来

wps如何制作成绩表和统计成绩表

wps教程 wps office教程
wps如何制作成绩表和统计成绩表

lol偷钱流符文搭配推荐

英雄联盟 网络游戏
lol偷钱流符文搭配推荐

Excel快速查找成绩表中出错的号码

办公软件
Excel快速查找成绩表中出错的号码

如何利用wps制作成绩表和统计成绩表

wps教程 wps office教程
如何利用wps制作成绩表和统计成绩表

lolAD刺客新符文搭配推荐

英雄联盟
lolAD刺客新符文搭配推荐

Word的几个使用绝招

Word的几个使用绝招

电脑黑屏的处理技巧

电脑黑屏的处理技巧
下拉加载更多内容 ↓