今天要跟大家分享的仍然是多表合并——MS Query合并报表!
excel中隐藏着一个强大的查询工具——MS Query,但是隐藏的很深,可能很多人都不知道。它的功能却异常强大,特别是报表合并、查询等。
我们经常的会碰到的关于合并表的难题无外乎两大类:
记录合并(横向行合并);
变量合并(纵向列合并)。
记录合并(横向行合并)
这种情况下要求列字段标题与顺序相同(无合并单元格)
本例一共有四个工作薄(一班、二班、三班、四班)(每一个工作薄中只有sheet1是有效的表,每一个表都是15条记录),每一个表列字段数目、标题都是一样的。
学号、语文、数学、英语、政治、历史、地理、综合、总分
步骤:
新建工作薄文档,选择数据——自其他来源——Microsoft Query(2)
在弹出的选择数据源菜单中选择Excel Files*并确定。
在下一步的选择工作薄中选择你想要合并的excel文件目录、文件夹及文件名,并确定。
在弹出的查询向导——选择列中,将sheet1$中的所有字段字段全部移入右侧的查询结果中的列菜单。
在查询向导——筛选结果菜单中,不做任何修改,默认跳过。
在查询向导——排序顺序中也不做任何修改,直接默认跳过。
在查询向导——完成菜单中,选择第二项“在Microsoft Query中查看数据或编辑数据(V)”选项。
此时你已经进入了Microsoft Query页面,其中在左上侧菜单中有一个SQL按钮,点开就可以刚才执行所有操作的代码。
没错我们就是要通过修改SQL代码来完成数据的合并,但是不要担心不需要自己去写,只是稍微修改一下就OK了。
首先将SQL语法框内select之后from之前的语法全部清除,并替换为*,学过数据库的小伙伴们都知道*代表选择所有字段的意思。
然后在语句末尾添加 union all
然后将刚才整理好的语句在语法编辑框内复制三次(一共几个要合并的表就复制n-1次),删掉最后一句的末尾 union all。
同时修改后面三句话里的工作薄名称,点击确定。
此时软件会提示无法以图形显示,是否仍要继续,忽略,直接点击确定。此时你会发现所有的记录(四个表)都已经全部添加进来了。(一共60条记录)
然后点击文件——将数据返回Microsoft Excel(R),就可以将查询讲过返回到excel表格中供我们进一步分析使用了。
最后一步你可以选择以表的形式返回,也可以选择以数据透视表的形式返回。
变量合并(纵向列合并)
这种情况下,要求多表之中有一个共同列字段,且该列字段不存在重复记录。
这里所以使用的案例数据结构如下:
新建一个空工作表,步骤如同第一步一样,在数据中选择 MS Query ,找到三个表文件所在目录,先插入语数英工作表。
完成之后,查询页面会出现语数英表文件的字段列表,双击字段列表中的*符号,下面会出现所有记录列表。
此时在顶部菜单中选择添加表,将三个表全部添加到字段列表页面,通过拖动每个字段列表中的学号字段,将三个表学号进行连接。
连接成功之后,将说表移动到下侧记录表的最后侧空列位置,你会发现那一列有一个小下拉箭头,点开下拉尽头,分别选中下拉表每一个字段,将所有的字段都显示出来。
这下三个表中的所有记录都已经连接成功,并且显示在查询表中了,可以返回excel了(文件——返回excel表)。
返回之后就OK了,可以开始下一步的数据分析了!