0.进校的第一张Excel表:“住宿分布表” | 您所在的位置:网站首页 › excel智能表格怎么做 › 0.进校的第一张Excel表:“住宿分布表” |
《Excel“智能化”之路》 系列文章
系列目录
0.进校的第一张Excel表:“住宿分布表” 系列前言转眼间就大二了,感觉自己还没学到什么,最近想回顾一下大一做的最多的工作——Excel,毕竟之前只是在用,还没总结过。这样可以回顾一下自己的大一生活,也为后续深入学习打下一定的基础,同时也可以给其他人做参考,如果有不同思路或者更好的建议也可以提出来。 本文目录 《Excel“智能化”之路》 系列文章系列目录系列前言 本文前言零、案例背景一、案例原数据二、 案例最终效果三、实现过程3.0 原数据预处理3.0.1 分析需求3.0.2 分析原有数据并做初步的预处理3.0.2.1 先把原表复制到先前设计的空表中3.0.2.1 分析两表的差异 3.1 楼栋和宿舍号3.2 室长和室长电话3.3 成员分布3.3.1 构造辅助列+“CV大法”——之前的办法3.3.2 VLookup函数多条件查询 3.4 专业班级3.4.1 提取班级信息3.4.2 编写公式3.4.3 此方法的不足——VLOOKUP函数的注意事项 3.5 附加效果:寝室长加粗显示——条件格式3.6 去公式、整理文件——上交的最后一步 四、待完善的地方五、结束语案例源文件参考资料 本文前言本文主要回顾大一刚开始时制作的表格和遇到的一些问题。 注: 所有案例使用的数据都是重新随机生成的(使用python的Faker库)。 参考的知乎:《【造数】Python批量生成测试数据》 零、案例背景 进校不到几个小时就接到了这个任务。 原表如下: 最终效果如下(寝室长是后面才选的): 再回顾一下原表(这里仅截取部分): 这种是学校系统导出来的数据,便于数据的储存和管理,但不方便人查看和使用(例如老师或者学生会查寝,班级管理等)。 为了方便,想将上表改成一个寝室分布表,能查看各个寝室成员的分布,班级分布,寝室长等信息。 当时我设计了下面的结构: 我一般习惯复制一份原有表格,以防后续误操作导致原有数据丢失,起到备份的作用 如下图:先将两个表都打开,在原表的相应工作表右键,然后选择“移动和复制”命令 !!!这里要特别注意一个问题!!! 如下图: 那怎么把它转换为数字格式呢? 先选中其中一个有问题的单元格,然后Ctrl+A全选当然活动区域(如果还有其他不连续的表格就再按一次,就会全选整个工作表),然后点击“感叹号”,选择“转换为数字”。 如下图: 这个都是使用Excel的“删除重复项”功能实现的。 1.复制到新的表中 还是之前的备份原则,在工作簿里面再新建一个工作表,再将刚刚的表复制过去,这里使用复制粘贴的选择性粘贴,而不是直接复制工作表,以防一些格式的影响。 1.收集室长的信息,如下表: 到此为止,整体的表完成效果如下: 这个还是比较难,记得我当时用的时构造辅助列,然后复制粘贴,现在的话方法也比较多。 3.3.1 构造辅助列+“CV大法”——之前的办法 主要思路是利用原有数据的床位号和寝室排列的规律性 因为开学排的寝室还是有规律的,将床位号为“1”的删选出来,排序,就可以直接复制到新表中对应的位置。 那有没有更通用或者说更“智能”的一点的办法呢? 3.3.2 VLookup函数多条件查询这个办法是后来制作成绩汇总表时用的,也和这个类似,原数据是学校导出的一条条记录,然后制作成更直观的表。 这里需要用到VLookup函数,在F4单元格输入如下公式,注意标红的两个“&”符号,然后按ctrl+shift+回车生成结果(Excel2021也可以直接按回车) =VLOOKUP($C4&F$3,IF({1,0},Sheet1!$B$2:$B$321&Sheet1!$C$2:$C$321,Sheet1!$D$2:$D$321),2,FALSE)
效果如图: 以上是VLookup两个条件的查询,由于原数据不同楼栋的寝室号也不同,所以可以直接用寝室号来区分不同寝室,那如果不同楼栋的寝室号有重复呢? 那就要用到三个条件来查询。 可以参考百度经验:VLOOKUP函数多条件查询 VLOOKUP三个条件查询 3.4 专业班级这里也需要分析原数据特点,由于是开学校分的寝室,那么一般都是连续分的,也就是说个寝室最多两个班的同学。 由于是连续的,那么就只用看第一个同学和最后一个同学是几班的。如果相同(或者不存在,也就是其他专业的同学),那就说明这个寝室只有一个班;如果不同,那么就说明有两个班,也就是这两个同学的班级。 由此需要先提取同学们的班级,原表中只有学号包含班级信息(这里学号的第9位代表班级)。 3.4.1 提取班级信息1.复制原数据到新表(如果有备份也可以不复制) 2.使用 MID 函数提取特定位置的数字 在F2单元格输入以下公式,然后
有了班级数据,就可以开始编写公式了。 1.先分析问题 总体思路是判断每个寝室的1号位和4号位是否相同或者不存在,然后根据判断结果输出不同结果,如下面的流程图: 可以复制到对应的地方查看效果: 合并到之前的公式中: =IF(OR(F4="——",I4="——"),IF(F4="——",VLOOKUP(I4,Sheet1!$D$2:$F$321,3,FALSE),VLOOKUP(F4,Sheet1!$D$2:$F$321,3,FALSE)))在有“——”的地方测试,得到:
即如果相同,输出第一个班级,不同就合并后输出 最后整合到之前的公式中得最终公式为: =IF(OR(F4="——",I4="——"),IF(F4="——",VLOOKUP(I4,Sheet1!$D$2:$F$321,3,FALSE)&"班",VLOOKUP(F4,Sheet1!$D$2:$F$321,3,FALSE)&"班"),IF(VLOOKUP(I4,Sheet1!$D$2:$F$321,3,FALSE)=VLOOKUP(F4,Sheet1!$D$2:$F$321,3,FALSE),VLOOKUP(I4,Sheet1!$D$2:$F$321,3,FALSE)&"班",VLOOKUP(F4,Sheet1!$D$2:$F$321,3,FALSE)&"、"&VLOOKUP(I4,Sheet1!$D$2:$F$321,3,FALSE)&"班")) 然后填充: 到此,达到了预期效果。 可以看出函数确实强大,但感觉有些麻烦。 对于这个案例来说,确实写个公式还不如手动敲快😂😂😂 但是如果情况更为复杂,比如进行专业分流后的寝室分布,或者可以自己调整的混寝,又或者说数据量比较大,从一个学院变成一个学校,那么写公式就比较方便。 练习这中长的公式也可以锻炼思维和解决问题的能力。 如果使用的是Excel2021,那么使用新的函数: LET ,这样更简洁,运算量更小。 具体的函数介绍可参考微软官网的帮助:LET函数 在之前的公式基础上更改,如下: =LET(x,F4,xx,VLOOKUP(F4,Sheet1!$D$2:$F$321,3,FALSE),y,I4,yy,VLOOKUP(I4,Sheet1!$D$2:$F$321,3,FALSE),IF(OR(x="——",I4="——"),IF(x="——",yy&"班",xx&"班"),IF(xx=yy,xx&"班",xx&"、"&yy&"班"))) 可以看到要简短很多,效果如下: 专业班级里面使用的VLOOKUP和之前在成员分布里面有一点不同,之前成员分布是通过寝室号查找的,寝室号唯一确定一个寝室(进行了简化,如果有楼栋影响可以加上),但这里不一样,通过人名去查找可能会出错,因为人名可能会重复(本案例为了简便,没有设置重名的情况)。 如果有人名重复,那么可以加辅助列,将学号加进来,然后隐藏这些辅助列。但这样就不好使用拉动填充,操作起来较为麻烦。 所以使用VLOOKUP函数之前一定要看查找项是否有重复的情况,一般使用学号去查询较为保险,但要注意 “以文本储存的数字” ,否则容易出错 3.5 附加效果:寝室长加粗显示——条件格式上面已经完成了之前的要求,但在此基础上还可以进行优化,可以使用条件格式,对寝室长进行强调 1.选中成员部分,点击“开始”选项卡中的“条件格式”,如下图: 上面的表格虽然效果以及出来了,但要上交或者发出去还是不行的,还有以下问题: 最终结果还含有公式,要根据情况去除公式删除多余的表,特别是含有多余的个人信息的表重命名文件以本案例的表为例,还应该对文件进行如下处理: 1.复制文件 将整个文件复制一份,根据上交的要求重命名,这里重命名为“宿舍安排表-上交示例(虚拟的个人数据) ”。 2.去公式 由于这里的分布表后续不会变动,所以不需要保留公式,选中整个表,复制,选择性粘贴,仅数值: 注: 如果有些特殊情况,需要一些辅助的表或者辅助列,那么可以将其隐藏。但要注意不要把多余的信息留在里面,比如有些原数据含有学生个人信息如身份证等,而本案例不需要身份证信息,应该将其删除(包括隐藏的部分),注意信息安全。 四、待完善的地方 其实上面的表还可以优化,即标注混合寝室具体的班级,如下图: 到此,完成了对进校第一张表的回顾,当时并没有考虑到这么多,有很多还是手动敲的😂😂😂。现在重新回顾,加进来后面的一些经验教训,同时也学到一些新的东西和思路。 公式部分没有详细展开,比如一些地方的相对引用和绝对引用(现在还没想好思路,怎用什么方式可以讲清楚)。 如果有什么问题或者更好的建议可以留言或者联系我,甚至包括文章排版、图片大小等问题(对Markdown不是很熟😂)。 案例源文件百度网盘链接:https://pan.baidu.com/s/1hye11qpwvCC72OfWS7XbXw 提取码:ybsh |
CopyRight 2018-2019 实验室设备网 版权所有 |