Excel中函数进行快速整理错乱成绩表格操作方法
时间:2023-10-09 09:19:20
如何用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列进行公式复制即可。
Excel中函数进行快速整理错乱成绩表格操作方法
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
如何用U盘安装win10专业版
![](https://img.aspxhome.com/file/2023/1/48231_0s.png)
Windowns xp 系统的扫雷游戏误删了怎么恢复?
![](https://img.aspxhome.com/file/2023/28/a248558_0s.jpg)
Win10 Mobile秋季创意者更新15230预览版修复内容大全:Bug修复
![](https://img.aspxhome.com/file/2023/28/a241926_0s.jpg)
如何在 Apple Watch 上快捷使用 Apple Pay?
![](https://img.aspxhome.com/file/2023/2/a196252_0s.jpg)
戴尔笔记本开不了机怎么办?戴尔开机黑屏解决方法
![](https://img.aspxhome.com/file/2023/6/a323204_0s.png)
Win7系统电脑使用虚拟光驱运行镜像文件的操作方法
![](https://img.aspxhome.com/file/2023/3/a296231_0s.jpg)
如何修改 Office 2016安装路径的方法 图解详细教程
![](https://img.aspxhome.com/file/2023/4/16434_0s.png)
在excel中如何计算搜索满足条件的单元格个数?
![](https://img.aspxhome.com/file/2023/3/42313_0s.jpg)
在Excel中用Findb函数区分大小写查看字节位置方法图解
![](https://img.aspxhome.com/file/2023/4/42214_0s.jpg)
华硕Z97如何安装Win11?华硕Z97安装Win11的方法
![](https://img.aspxhome.com/file/2023/27/a237552_0s.jpg)
office2015新特性/新功能有哪些?2015office16更新内容
![](https://img.aspxhome.com/file/2023/9/15669_0s.jpg)
ppt怎么使用NT插件中的环形复制功能?
![](https://img.aspxhome.com/file/2023/10/a346006_0s.jpg )
u盘查杀,详细教您u盘怎么查杀病毒
![](https://img.aspxhome.com/file/2023/1/a273782_0s.png)
如何为WPS办公手机版本改变皮肤?
![](https://img.aspxhome.com/file/2023/5/a170635_0s.jpg)
重复数据查找,巧用条件格式查找在WPS表格中的重复数据
WPS表格统一日期格式的方法步骤详解
Windows Server 2003 关闭数据执行保护的方法
![](https://img.aspxhome.com/file/2023/29/a257377_0s.png)
Firefox浏览器在Mac上如何查看更新历史?
![](https://img.aspxhome.com/file/2023/3/a205323_0s.jpeg)
Win10专业版提示input not supported错误要如何解决?
![](https://img.aspxhome.com/file/2023/26/a223057_0s.jpg)
快速清理Win7使用痕迹防护文档隐私问题
![](https://img.aspxhome.com/file/2023/29/a258714_0s.jpg)