0.进校的第一张Excel表：“住宿分布表”

   转眼间就大二了，感觉自己还没学到什么，最近想回顾一下大一做的最多的工作——Excel，毕竟之前只是在用，还没总结过。这样可以回顾一下自己的大一生活，也为后续深入学习打下一定的基础，同时也可以给其他人做参考，如果有不同思路或者更好的建议也可以提出来。

   本文主要回顾大一刚开始时制作的表格和遇到的一些问题。

注: 所有案例使用的数据都是重新随机生成的(使用python的Faker库)。

参考的知乎：《【造数】Python批量生成测试数据》

   进校不到几个小时就接到了这个任务。    记得当时把行李放在寝室后就去办公室报到，报道完就回寝室拿电脑开始制作第一张Excel，可以说报道才几个小时就进入了大学生活😂😂😂

   原表如下：    具体数据如下:

   最终效果如下（寝室长是后面才选的）：

   再回顾一下原表（这里仅截取部分）：

   这种是学校系统导出来的数据，便于数据的储存和管理，但不方便人查看和使用（例如老师或者学生会查寝，班级管理等）。    为了方便，想将上表改成一个寝室分布表，能查看各个寝室成员的分布，班级分布，寝室长等信息。    当时我设计了下面的结构：    这样就可以很方便的查看相关的信息了。

   我一般习惯复制一份原有表格，以防后续误操作导致原有数据丢失，起到备份的作用    如下图：先将两个表都打开，在原表的相应工作表右键，然后选择“移动和复制”命令    在这里选择之前设计的新表（要保证是打开状态）：    然后勾选“副本”，确定：    即可看到原表被复制了过来：

！！！这里要特别注意一个问题！！！

   如下图：    会发现这里有很多绿色三角形，这是Excel的错误提示，选中这个单元格，会发现有下面提示：    这说明这个数字是以文本格式储存的，但工具栏显示的类型却还是常规：    这个有什么影响呢？影响大了！！！    之前写公式或者做某些操作，就是因为这个问题而一直出错，有一些甚至是做完了都没发现，后来才回想起来😵‍💫😵‍💫😵‍💫    简单来说，两个看起来“数字”一样的数据，如果格式不同会认为是不同的数据    如下图示例：    如果不设置，输入文本时Excel会左对齐，而输入数字时会右对齐。但如果设置了居中对齐，有时就不容易区分，甚至有时连绿色的三角形提示也没有，如果不注意，就会有一些莫名其妙的错误，还有表都做完上交了都没发现这个错误😂😂😂    比如进行查找（特别时那个Vlookup函数）、比对、删除重复值等操作时。    一般从数据库里面导出的数据要注意这个问题。

   那怎么把它转换为数字格式呢？    先选中其中一个有问题的单元格，然后Ctrl+A全选当然活动区域（如果还有其他不连续的表格就再按一次，就会全选整个工作表）,然后点击“感叹号”，选择“转换为数字”。    这样就完成了第一步，注意上面的注意事项，不然后面很容易出错。

   如下图：

   这个都是使用Excel的“删除重复项”功能实现的。

1.复制到新的表中    还是之前的备份原则，在工作簿里面再新建一个工作表，再将刚刚的表复制过去，这里使用复制粘贴的选择性粘贴，而不是直接复制工作表，以防一些格式的影响。    这里发现学号那一列变成了科学计数法，这是由于列宽不够造成的。    选中这一列，在列号右边，鼠标变成下图样子时双击即可自动调整列宽： 2.全选表格，在数据选项卡中选择“删除重复值”： 3.勾选楼栋名称和寝室号，确定后就删除完成。 4.复制到新表中，填充好前面的序号：    完成第一个要求。

1.收集室长的信息，如下表：    具体数据如下： 2.和之前一样，将整张表复制到新的工作簿中： 3.分析数据    一个寝室只有一个室长，而且都是按寝室楼栋和寝室号排序的，所以经过排序后可以直接复制信息到新的表（如果情况复咋可能需要多条件查询等，这里考虑简单的情况），按住Ctrl可以选中不连续的区域：

   到此为止,整体的表完成效果如下：

   这个还是比较难，记得我当时用的时构造辅助列，然后复制粘贴，现在的话方法也比较多。

   主要思路是利用原有数据的床位号和寝室排列的规律性    因为开学排的寝室还是有规律的，将床位号为“1”的删选出来，排序，就可以直接复制到新表中对应的位置。    这样筛选4次就可完成要求。

   那有没有更通用或者说更“智能”的一点的办法呢？

   这个办法是后来制作成绩汇总表时用的，也和这个类似，原数据是学校导出的一条条记录，然后制作成更直观的表。    这里需要用到VLookup函数，在F4单元格输入如下公式，注意标红的两个“&”符号，然后按ctrl+shift+回车生成结果(Excel2021也可以直接按回车)

   然后水平和纵向填充即可：    中间这些“N/A”表示未查询到，即这个位置没有人。

   可以再添加 IFERROR 函数来处理，比如用——填充这些单元格，公式如下（F4）：

   效果如图：    这样人员信息就完成了。

   以上是VLookup两个条件的查询，由于原数据不同楼栋的寝室号也不同，所以可以直接用寝室号来区分不同寝室，那如果不同楼栋的寝室号有重复呢？ 那就要用到三个条件来查询。

可以参考百度经验：VLOOKUP函数多条件查询 VLOOKUP三个条件查询

   这里也需要分析原数据特点，由于是开学校分的寝室，那么一般都是连续分的，也就是说个寝室最多两个班的同学。 由于是连续的，那么就只用看第一个同学和最后一个同学是几班的。如果相同（或者不存在，也就是其他专业的同学），那就说明这个寝室只有一个班；如果不同，那么就说明有两个班，也就是这两个同学的班级。 由此需要先提取同学们的班级，原表中只有学号包含班级信息（这里学号的第9位代表班级）。

1.复制原数据到新表（如果有备份也可以不复制） 2.使用 MID 函数提取特定位置的数字    在F2单元格输入以下公式，然后

   到此提取完成

   有了班级数据，就可以开始编写公式了。 1.先分析问题    总体思路是判断每个寝室的1号位和4号位是否相同或者不存在，然后根据判断结果输出不同结果，如下面的流程图： 2.在J4单元格编写第一个判断，公式如下：

   可以复制到对应的地方查看效果： 3.在上述公式的基础上进行更改，使用VLookup函数在之前做的表中查询对应班级。 VLookup函数如下：

   合并到之前的公式中：

   在有“——”的地方测试，得到：

   还可以对显示的结果进行调整：

3.继续编写后面的情况，判断两个班级是否相同

   即如果相同，输出第一个班级，不同就合并后输出    最后整合到之前的公式中得最终公式为：

   然后填充：

   到此，达到了预期效果。    可以看出函数确实强大，但感觉有些麻烦。    对于这个案例来说，确实写个公式还不如手动敲快😂😂😂    但是如果情况更为复杂，比如进行专业分流后的寝室分布，或者可以自己调整的混寝，又或者说数据量比较大，从一个学院变成一个学校，那么写公式就比较方便。    练习这中长的公式也可以锻炼思维和解决问题的能力。

如果使用的是Excel2021，那么使用新的函数： LET ，这样更简洁，运算量更小。

具体的函数介绍可参考微软官网的帮助：LET函数

   在之前的公式基础上更改，如下：

   可以看到要简短很多，效果如下：    至此，整体的效果如下：

   专业班级里面使用的VLOOKUP和之前在成员分布里面有一点不同，之前成员分布是通过寝室号查找的，寝室号唯一确定一个寝室（进行了简化，如果有楼栋影响可以加上），但这里不一样，通过人名去查找可能会出错，因为人名可能会重复（本案例为了简便，没有设置重名的情况）。    如果有人名重复，那么可以加辅助列，将学号加进来，然后隐藏这些辅助列。但这样就不好使用拉动填充，操作起来较为麻烦。

   所以使用VLOOKUP函数之前一定要看查找项是否有重复的情况，一般使用学号去查询较为保险，但要注意 “以文本储存的数字” ，否则容易出错

   上面已经完成了之前的要求，但在此基础上还可以进行优化，可以使用条件格式，对寝室长进行强调

1.选中成员部分，点击“开始”选项卡中的“条件格式”，如下图： 2.选择“新建规则”： 3.选择“使用公式确定要设置格式的单元格”，并输入以下公式： 4.点击“格式”： 5.设置好格式后确定即可：    效果如下：    到此，整个“住宿分布表就完成了”：

上面的表格虽然效果以及出来了，但要上交或者发出去还是不行的，还有以下问题：

以本案例的表为例，还应该对文件进行如下处理：

1.复制文件    将整个文件复制一份，根据上交的要求重命名，这里重命名为“宿舍安排表-上交示例(虚拟的个人数据) ”。

2.去公式    由于这里的分布表后续不会变动，所以不需要保留公式，选中整个表，复制，选择性粘贴，仅数值：    这样就去除了公式，还不改变格式，效果如下： 3.删除多余的表或者辅助列    这里将其他工作表删除即可：    删除后效果如下：    到此，就可以上交了。

   注： 如果有些特殊情况，需要一些辅助的表或者辅助列，那么可以将其隐藏。但要注意不要把多余的信息留在里面，比如有些原数据含有学生个人信息如身份证等，而本案例不需要身份证信息，应该将其删除（包括隐藏的部分），注意信息安全。

   其实上面的表还可以优化，即标注混合寝室具体的班级，如下图：    对混合寝室进行标注，使得信息更加丰富，但暂时没找到简单的办法，之前是手动一个一个寝室标的。    一个思路是处理原数据，将名字全部处理成“名字（班级）”这样的格式，最后再利用筛选和替换，删除多余的标注（全部标注的话感觉看起来很乱）。    还有一个问题就是最后来删改的话，可能会影响之前的条件格式。

   到此，完成了对进校第一张表的回顾，当时并没有考虑到这么多，有很多还是手动敲的😂😂😂。现在重新回顾，加进来后面的一些经验教训，同时也学到一些新的东西和思路。    公式部分没有详细展开，比如一些地方的相对引用和绝对引用（现在还没想好思路，怎用什么方式可以讲清楚）。    如果有什么问题或者更好的建议可以留言或者联系我，甚至包括文章排版、图片大小等问题（对Markdown不是很熟😂）。

百度网盘链接：https://pan.baidu.com/s/1hye11qpwvCC72OfWS7XbXw 提取码：ybsh