电脑技术网 - 从此开始了解电脑、科技、手机、智能硬件、网络相关的各项适用知识!

电脑技术网

当前位置: 电脑技术网 > 办公教程 > Excel表格学习 > 用Excel函数快速整理错乱成绩表

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

单位教务部门拿来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列进行公式复制即可。

 

 

你还可能关注到的相关内容
发表评论 网友发言只代表其个人观点,不代表电脑技术网的观点或立场。
验证码:点击我更换图片