返回目录:excel表格制作
数据源是一张课程总表,要查询出每位教师的每周的排课情况。
这是数据源表,虽然看着直观,但是却又很多问题,不是一张符合要求的数据源表格,里面有很多的合并单元格。
这是目标表格,查询的结果要排列成这个样子。
第一步:建立连接
选择文件,然后选择总表作为查询的数据源。
第二步:转换
刚刚导入的数据是原始状态,打散合并单元格的状态,需要通过一系列操作,转换成我们需要的形状:
然后通过教师和时段标签来实现查询。
刚刚导入的是这样的表格:
后面还有很多的空白列,所以我们要耐心来处理。
1、提升标题、删除空白列
将源表的第一行作为标题行
删除后面的空白列
2、筛选掉空白行、向下填充
用第二列进行筛选,去除空白行
第一列需要向下填充,填好所有的星期
3、复制查询,单独处理一下,晚1和晚2的课程
筛选出晚1和晚2,五年级和六年级有晚自习,由于是合并单元格,也需要向下填充
然后选择需要填充的列,按SHIFT+END可以直接选到最后一列,向下填充
因为总表里已经有了晚1,我们只需要把晚2追加到总表就可以了,所以再筛选一下
这个单独处理到这就结束了,返回到总表
4、追加晚2到总表中
5、选择所有年级列进行逆透视
6、处理科目、教师、时段
数据源中的科目和教师是合并在一起的,而且大课间、晚1、晚2是只有教师名没有科目,所以,当我们对最后这一列进行拆分时,就会出现科目与教师姓名错位的情况,即本应该出现在教师名称一列的姓名,有一些会出现在科目这一列,为了解决这个问题,我们就要用自定义列。
用换行符拆分列:
用自定义列调整科目和教师:
教师:=if [值.2]=null then [值.1] else [值.2]
科目:=if [Column2]="大课间" or [Column2]="晚1" or [Column2]="晚2" then [Column2] else [值.1]
7、用自定义列生成班级与科目列
因为我们最终的查询结果是要显示这样的结果,哪一个班级什么课程?
班级科目=[属性]&"#(lf)"&[科目]
8、清理没用的列,透视列到我们需要的形状
通过管理列直接选择要保留的列、或者直接删除不需要的列都可以
使用星期列来透视列,高级选项里选择“不要聚合”
至此,数据源已经处理好了,接下来要做的是查询
第三步:组合
要建立查询,首先要做点准备工作,如果我们是用教师姓名进行查询,就需要有一份教师名单,用来做数据有效性的序列值,其次我们要有一个每天课程的排列表格。
这两个表可以直接通过总表来生成,保留单列,然后删除重复值即可,我是在Excel中删除重复值,再添加进来,效果都一样。
课程排列,添加索引列,将来排序使用
1、新建查询
在Excel中选择两个单元格,通过表格与区域建立查询
2、添加自定义列,生成课程排列
展开List
3、到总表中合并查询
展开Table
4、处理排序
时段的排序是乱的,要根据我们添加的索引来排序
还是合并查询索引过来,升序排序之后,删除索引即可
删除索引列之后,我们的组合工作就做好了。
第四步:共享
在这个例子中就非常简单了,直接加载表格到现有表格就可以了。表4查询,右键加载到,选择现有表格。
这个查询的使用方法很简单,只需要在下拉列表中选择教师,然后在查询表里右键刷新就可以了。