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