0.进校的第一张Excel表：“住宿分布表” ——《Excel“智能化”之路》 系列文章

《Excel“智能化”之路》 系列文章

系列目录

   0.进校的第一张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 分析两表的差异

   如下图：

1. 楼栋和宿舍号
      分析原表或者根据常识，楼栋和宿舍号可以唯一确定一个寝室，所以这两列可以通过Excel的“删除重复项功能实现”
2. 室长和室长电话
      由于当时才开学，室长还未选出，所以需要收集数据，然后合并到里面
3. 成员分布
      这个当时来说还是比较难的，我使用的是辅助列+“CV”大法，现在看来可以有更简单的办法完成这个。
4. 专业班级
      也就是这个寝室是哪个班的，有可能有混寝的情况。这个当时也是手动敲的。现在的话也可以利用函数来解决。

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

参考资料

1. 知乎：《【造数】Python批量生成测试数据》
2. 百度经验：VLOOKUP函数多条件查询 VLOOKUP三个条件查询
3. 微软官网的帮助：LET函数