excel表格怎么打乱成绩排名(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列进行公式复制即可。

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 86345@qq.com 举报,一经查实,本站将立刻删除。
(1)
上一篇 2022-04-11 01:00:07
下一篇 2022-04-11 01:10:06

猜你喜欢

  • 芍药的生活习性_芍药的花语

    芍药,别名将离、离草,婪尾春、余容、犁食、没骨花、黑牵夷、红药等,是芍药科芍药属的著名草本花卉。芍药在中国的栽培历史超过4900年,是中国栽培最早的一种花卉。又被称为“五月花神”,…

    2022-09-03
  • 苍蝇的寿命是多久,苍蝇的寿命是几年

    苍蝇是一类常见的昆虫,其在人类生活中随处可见,但其生命周期却相对较短暂。苍蝇的生命周期经历了从卵到成虫的多个阶段,每个阶段都有其独特的特征和功能。本文将深入探讨苍蝇的寿命及其生命周…

    2023-10-21
  • 中国未来的房价会怎么样_未来中国的房价会如何变化

    房价的涨跌向来是诸多楼市主体,最为关注的事情之一,因为经济基础决定上层建筑。而不论是考虑到个人的购买力,还是人民币抵御通货膨胀的能力,一般家庭买房的经济代价,动辄要掏空全部积蓄、再…

    2023-05-25
  • 油卡怎么办理,个人有必要办加油卡吗

    全国加油站,不论是中国石油,还是中国石化,优惠都是差不多的,没有说是特殊渠道或者大打折扣这一说。网络上或朋友圈有些人在发中国石化、中国石油加油卡8折等,这类信息均属骗子割韭菜行为,…

    2023-07-20
  • 红霉素软膏有什么作用_红霉素软膏的作用

    赵女士的皮肤一直很干燥,尤其是现在到了春天,天气也非常的干燥,使赵女士皮肤的干燥情况更加严重,甚至前几天还开始流鼻血,并且时常有流鼻血的情况。赵女士害怕身体出现严重的病情,就立即去…

    2023-03-17
  • 毛冬青是什么_毛冬青的功效

    毛冬青是什么?大家可能对毛冬青很陌生,它是一种植物,也是一种药材。要想详细了解它,就不要错过下文的介绍。 毛冬青(学名:Ilex pubescens)为冬青科冬青属的植物。又名茶叶…

    2022-10-30
  • 石蜂糖有什么功效_石蜂糖的功效

    石蜂糖是马来西亚一种特有的糖类,作为一种降肝火,止咳化痰,清热解毒的很受人们欢迎,石蜂糖的功效很多。《本经》中早有记载:石蜜,味甘、平,主心腹邪气、诸惊痫,安五脏诸不足,益气补中,…

    2022-06-12
  • 什么是劳务派遣人员,劳务派遣人员是事业编还是临时工

    今天看到一个文章,说是政府机关体制内的人员种类包括劳务派遣人员,这纯粹就是在误导人。 劳务派遣这个词可能大家比较少听到,我先给大家解释一下什么什么是劳务派遣人员。举个例子吧,假如甲…

    2023-10-08
  • 电动机的工作原理是什么_电动机的工作原理

    目录 什么是电机的原理:通过电磁驱动电机 电机原理:弗莱明左手定则是什么 什么是电机的原理:电机是如何工作的 直流电机的工作 感应电机的工作 同步电机的工作 我们都听说过电机,但一…

    2022-11-29
  • 燕麦片怎么吃有营养

    1.燕麦片煮、冲服 燕麦片可以煮、冲服。一般需要煮的燕麦片中没有加入任何添加成分,属于纯燕麦片,营养价值最高,而且煮的燕麦片可以提供最大的饱腹感,使体内血糖缓慢上升。而一些冲服的麦…

    2023-07-03

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注