ExcelHome技术论坛

标题: 仙侣情缘-认识透视表和SQL [打印本页]

作者: wuxiang_123 时间: 2010-8-3 16:53
标题: 仙侣情缘-认识透视表和SQL
本帖最后由 wuxiang_123 于 2013-5-30 10:15 编辑

发贴原由：在透视表板块活动有一段不短的时间，发现很多家人对透视表的基本概念还没有掌握，对SQL的语句也不熟悉，只知一味套用，出错了又埋怨透视表和SQL。故发此贴希望能对大家有所裨益。

版本环境：EXCEL2003

SQL应用环境：OLE DB编辑框

大纲：共分 “透视表”、“SQL”、“透视表和SQL的结合” 三卷，每卷下有若干章节。

形式：三步制分享，第一步，出课前预习和课时纲要，第二步，详说本章内容和出本章课后练习，第三步，解答课前预习和课后练习。如此循环（一般情况下，步以天为单位::P ）。

PS：本帖为本人对透视表和SQL的一些理解，未必正确，欢迎各位家人指正。

仙侣贴答案文件-----百度网盘（不用注册也可下载）：
下载链接:http://pan.baidu.com/share/link?shareid=493574&uk=774286369
密码:2311

作者: wuxiang_123 时间: 2010-8-3 16:57
标题: 第一卷第一章：初识数据透视表（课前预习和课时纲要）
课时纲要：
一：初识透视表
二：字段与项
三：数据透视表的四大区域
四：计算字段与计算项
五：透视表运算机制
PS:附件是课时预习题，有兴趣的可以思考下。

感谢whsfhwm 指正笔误之处，已经更正。

[ 本帖最后由 wuxiang_123 于 2010-8-4 17:43 编辑 ]

作者: suwenkai 时间: 2010-8-3 16:57
支持吴老师

一直想报读数据透视表班，就是老是报不上。。

作者: wuxiang_123 时间: 2010-8-3 17:04
啰嗦地说句，前面的透视表都是些基础的东西，不过，千万不能轻视，在以后的SQL学习中有非常非常非常大的用处。
另外，关于透视表和SQL,我现时的观点是：能用透视表完成的就尽量不用SQL。

作者: suwenkai 时间: 2010-8-3 17:10
吴老师，做完了，要不要上传上来

。

作者: wuxiang_123 时间: 2010-8-3 17:14
标题: 回复 5楼 suwenkai 的帖子
我不是老师,做完了,可以上传,但请用压缩文件上传.不能影响其它家人

谢谢支持.

作者: suwenkai 时间: 2010-8-3 17:20
题目我都做完了，就是第二题的为什么是1000而不是10000的，我估计是数据不是相加的，而是前面的金额之和除于数量之和
第三题的为什么不会有累加的效果，我就是想不明白了，我猜是对某一字段的汇总的时候会考虑前面的字段的原因。

作者: wuxiang_123 时间: 2010-8-3 17:28
标题: 回复 7楼 suwenkai 的帖子
呵呵,表是做出来了,可见你有透视表功底.不过,文字说明部分全都没有说明.

作者: suwenkai 时间: 2010-8-3 17:29

没办法，小学的文化。。

作者: 魂断蓝桥 时间: 2010-8-3 18:46
非常感谢吴想老师，早就想系统的学习一下sql的用法，苦于资料太分散了。这下好了。
谢谢了！
附上我做的结果。
2：为什么乙会出现在每组人员下
这个委实不知了！

作者: hooyee 时间: 2010-8-3 18:58
正准备学习。

作者: tomleej 时间: 2010-8-3 19:13
标题: 惭愧
有两个要求没有做到：

预习一的图表二，不知道怎么把“人员”字段放在右上角；
预习二的图表二，不知道怎么添加：插入计算项：女＝甲＋乙

作者: smgk 时间: 2010-8-4 07:52
本帖最后由 ExcelHome 于 2012-10-6 16:16 编辑

预习一的图表二，我也不知道怎么把“人员”字段放在右上角；

作者: greatch 时间: 2010-8-4 08:32
老师，后面的章节快点放上来啊。

作者: whsfhwm 时间: 2010-8-4 09:35
感谢楼主，我也来学习~~~
不过“单价＝金额、数量”应改为“单价＝金额/数量”；
“为什么乙会出现在每组人员下”应为“为什么女会出现在每组人员下”吧。
(, 下载次数: 1023)

作者: koosky 时间: 2010-8-4 09:39
感谢！吴老师！

作者: koosky 时间: 2010-8-4 09:50
是不是初级者不适合啊？我是一个刚接触数据透视表

作者: 老老高 时间: 2010-8-4 10:04
看来我的透视表还是学的只是牛毛

作者: wuxiang_123 时间: 2010-8-4 14:24
标题: 第一卷第一章－初识数据透视表（正文及课后练习）
具体，请查看附件。

附件的说明比较抽象，有如何疑问，请跟贴提问。
本章的课前预习和课后练习答案将在明天公布。

已经重新更新附件，并增加多一个练习，请各位重新下载。

[ 本帖最后由 wuxiang_123 于 2010-8-4 17:41 编辑 ]

作者: wuxiang_123 时间: 2010-8-4 14:25

原帖由 koosky 于 2010-8-4 09:50 发表
是不是初级者不适合啊？我是一个刚接触数据透视表

面向从没有接触过透视表的人群

各位家人有如何疑问可以跟帖提问。

作者: 抹香鲸2009 时间: 2010-8-4 16:11
楼主是活雷锋，学习。

作者: suwenkai 时间: 2010-8-4 16:29
就只有第三题的第二小题不会做，其它的都做了。。

想了半天也没有想出来。

作者: jayct 时间: 2010-8-4 16:52
请问什么时候放答案啊。。。很多都不会。。。汗。。。

PS：我用的07，压力很大。。。

作者: greatch 时间: 2010-8-4 17:21
本帖最后由 ExcelHome 于 2012-10-6 16:18 编辑

suwenkai ，练习5：金额占比这一列是行汇总百分比，但是如何增加这一列啊？

作者: guojianlin1985 时间: 2010-8-4 17:34
做个记号学习

作者: bibisin 时间: 2010-8-4 18:46
谢谢wuxiang_123分享

作者: hhy0206 时间: 2010-8-4 19:02
谢谢老师。

作者: kiddy0793 时间: 2010-8-4 20:44
楼主，已完成！
请指点！谢谢

作者: tomleej 时间: 2010-8-4 20:58
标题: 终于完成，准备休息
请指点，谢谢！ (, 下载次数: 668)

如果有2007版本的教材就更加好了

作者: tomleej 时间: 2010-8-4 21:02

原帖由 greatch 于 2010-8-4 17:21 发表
suwenkai ，练习5：金额占比这一列是行汇总百分比，但是如何增加这一列啊？

这个LZ似乎要我们计算“占同行的百分比”

作者: tomleej 时间: 2010-8-4 21:05

原帖由 suwenkai 于 2010-8-4 16:29 发表
就只有第三题的第二小题不会做，其它的都做了。。想了半天也没有想出来。

好像我也没有做，请LZ指点

[ 本帖最后由 tomleej 于 2010-8-5 07:31 编辑 ]

作者: zgz9146 时间: 2010-8-4 21:36
D、E列隐藏让我想了好半天。。。。。。

作者: fasdfasdfafd 时间: 2010-8-4 23:02
标题: 回复 19楼 wuxiang_123 的帖子
第三题的第二小题只做了一半，不知如何添加差异，请指正！！！

作者: greatch 时间: 2010-8-4 23:23
引用:
原帖由 greatch 于 2010-8-4 17:21 发表
suwenkai ，练习5：金额占比这一列是行汇总百分比，但是如何增加这一列啊？
这个LZ似乎要我们计算“占同行的百分比

我就是不会增加这一列啊

作者: VAT 时间: 2010-8-5 00:00
第二题做了半道

作者: whsfhwm 时间: 2010-8-5 00:24
(, 下载次数: 566)
好像都做出来了，请楼主费心看看是否有不妥之处。

作者: liuyan819 时间: 2010-8-5 07:59
不错，学习一下

作者: suwenkai 时间: 2010-8-5 08:45
昨天我做第三题的第二小题的时候出错，今天做的时候，又可以了

作者: greatch 时间: 2010-8-5 08:47
那个大哥解释一下练习五的金额占比是怎么做的啊？是不是用SQL写的啊？

[ 本帖最后由 greatch 于 2010-8-5 08:56 编辑 ]

作者: nicholas_he 时间: 2010-8-5 09:01
多谢大哥，先学习下，昨晚了给大家看看再

作者: lgcmeli 时间: 2010-8-5 09:02
哈哈不错。题目起得况味。

作者: kinwa.wu 时间: 2010-8-5 10:12
MARK,学习了

作者: wuxiang_123 时间: 2010-8-5 10:42
标题: 第一卷第一章课前预习答案和课后练习答案
文件有些大，传到115U了，有效期为30天。
http://u.115.com/file/f8a69435a5
课前预习答案.rar
http://u.115.com/file/f861d41d9d
课后练习答案.rar

PS:之前注册了是wuxiang_123,但忘记邮箱和密码了

只好新建一个账户：wuxiang_1234
再PS:理解分类可以采用直线法。除了末字段，其它字段下的项用直线连接，一条直线就是一个分类

作者: wuxiang_123 时间: 2010-8-5 10:44
标题: 第一卷第二章－常用透视表创建方法之傲龙九式（课前预习）
如题。期待大家的答案。

请假：明后两天要回乡办事，不便上网，回来后再上传第二章正文及其课后练习

[ 本帖最后由 wuxiang_123 于 2010-8-5 17:25 编辑 ]

作者: suwenkai 时间: 2010-8-5 10:59

又是第一个交作业。

作者: 亡者天下 时间: 2010-8-5 11:08
wuxiang终于出手了

我也来学习一下！

作者: 烦恼了却 时间: 2010-8-5 11:15
谢谢吴老师，我这只菜鸟学习了

作者: L-Yang136 时间: 2010-8-5 11:39
老吴，赞个！
俺也来学习下。。。

作者: zhc19620430 时间: 2010-8-5 11:45
先占个位，学习完后再来做题。

作者: lgcmeli 时间: 2010-8-5 11:51
吴老师太棒了。绝对支持。

作者: 魂断蓝桥 时间: 2010-8-5 12:49
有个疑问？
老师第三题第二题的图是不是贴错了。那个数值我怎么也对不上？

原图。
(, 下载次数: 193)

做好后图

(, 下载次数: 196)

另，第三题如果不用隐藏行的方法，直接用插入计算项，然后自定义单元格式的方法，是否可行。
附上用自定义单元格式做好后的图。

(, 下载次数: 202)

作者: suwenkai 时间: 2010-8-5 12:53
没问题，吴老师用的是按某一字段汇总

（金额和数量都是）

作者: wuxiang_123 时间: 2010-8-5 12:54
标题: 回复 51楼魂断蓝桥的帖子
问题一:数量和金额的显示方式为:按某一字段(月份)汇总.
问题二:方法不限,只要实现效果就可以.这题的目的是提前让大家认识显示方式中"差异"的用法.在某些情况下,是很有用的.

作者: wnlhope 时间: 2010-8-5 13:05
我一定要好好学习

作者: 魂断蓝桥 时间: 2010-8-5 13:07
惭愧！
我考虑的太简单了！

另附上这次的题。
只想到了三种方法。

作者: taxue616 时间: 2010-8-5 14:45
吴想真行啊, 过来学习了!

作者: yh9278 时间: 2010-8-5 16:08
标题: 感谢
谢谢老师，正想学习呢。

作者: wxin0708 时间: 2010-8-5 16:38
今天刚看到这个贴

晚上回家做题目

哈哈，修改下

[ 本帖最后由 wxin0708 于 2010-8-7 10:55 编辑 ]

作者: tq229926 时间: 2010-8-5 17:19
占位学习，没时间做题

作者: simple666 时间: 2010-8-5 17:59
今天刚看到这个贴，学习

作者: baby521 时间: 2010-8-5 18:15
先占个位，晚上回去做题

作者: century_zhang 时间: 2010-8-5 19:00
标题: 第一章课后练习2的解答是否少了一步？
吴老师，正在模拟你的动画，发现练习2的解答和你的要求稍有不同：要求“排名”是按照总分的高低排名的，而解答中却没有实现这一目的，只是按照顺序写出了数字
不知是我理解有误？

作者: tomleej 时间: 2010-8-5 20:23
谢谢
我现在用07版本，在预习二提（2），在选择“计算项”的时候，07版本不会显示右边“人员”类的“甲乙丙丁”等，无法实现03版的功能。

请教07版本在哪里寻找此功能？

还有在隐藏行标签“组/队” “汇总”，您是用什么快捷键选择所有汇总项？

[ 本帖最后由 tomleej 于 2010-8-6 08:14 编辑 ]

作者: magiccq 时间: 2010-8-5 20:37
标题: 求助帖
想学习一下，之前用过简单的透视表，跟着吴老师的进度和大家一起学习吧！
我用的是2007版，在“公式”，“计算项”是总会出现对话框，不能进行计算，图片如下，求高手解答！不甚感激……

[ 本帖最后由 magiccq 于 2010-8-6 22:16 编辑 ]

作者: ZDYCTD 时间: 2010-8-5 20:44
收藏，以后应该用得到。

作者: ybzdexcel 时间: 2010-8-5 21:19
谢谢，我先留下脚印再拜读

作者: dawin 时间: 2010-8-5 21:44
标题: 回复 62楼 century_zhang 的帖子
wuxiang老师公布的课后练习答案和之前的样本有点不一样。
你说的排名问题，应该是他不小心的手误，本来应该是对姓名以总分降序排列，结果点成了以排名降序了。所以给的答案错了。

他手误的还有一个练习3的第一题，“与1月差异金额”打成了“月1月差异金额”。
另外就是练习三的第二题，配图和答案有点不一样，以公布的答案为准。

做完练习了，开始学习第二章。

作者: dawin 时间: 2010-8-5 22:43
1有两个方法没实现。
期待答案。。

作者: peanutgu 时间: 2010-8-6 01:03
那个排名应该是弄错了吧，只是简单的排序。期待新的答案。

作者: wshcw 时间: 2010-8-6 01:33
不错,对初学的很有帮助.

作者: nuimqi 时间: 2010-8-6 01:44
现在没空学，先占个座

作者: tomleej 时间: 2010-8-6 08:14

原帖由 tomleej 于 2010-8-5 20:23 发表
谢谢
我现在用07版本，在预习二提（2），在选择“计算项”的时候，07版本不会显示右边“人员”类的“甲乙丙丁”等，无法实现03版的功能。

请教07版本在哪里寻找此功能？

还有在隐藏行标签“组/队” “汇总” ...

it is weird. I can find "Items" under "Insert Calculate item" in office but failed at home! Maybe the copy version is the point!

anyway thanks

作者: tomleej 时间: 2010-8-6 08:26

原帖由 dawin 于 2010-8-5 21:44 发表

另外就是练习三的第二题，配图和答案有点不一样，以公布的答案为准

Unfortunately I still have no idea how to do the 练习三的第二题?  Waitting for the the answer...

I got it!

但还是不理解这个 "160" & "38000" 代表什么含义?  请指点

[ 本帖最后由 tomleej 于 2010-8-6 17:00 编辑 ]

作者: 海婷 时间: 2010-8-6 08:30
感谢楼主，我也来学习~~~

作者: 晋级菜鸟 时间: 2010-8-6 10:22
想学习一下，之前用过简单的透视表，跟着吴老师的进度和大家一起学习吧！

作者: baby521 时间: 2010-8-6 10:48
想请教第二题的求和项单价，如何做

作者: suwenkai 时间: 2010-8-6 19:51
今天吴老师怎么没有出题的

我想做题啊。。

作者: tomleej 时间: 2010-8-6 21:43

原帖由 suwenkai 于 2010-8-6 19:51 发表
今天吴老师怎么没有出题的我想做题啊。。

今天周末，老师体谅大家也需要休息嘛

作者: jones.zhang 时间: 2010-8-7 14:11
学到一招，刚好能用的到，
期待吴老师下面的展示

作者: wangydm2 时间: 2010-8-7 15:07
多重范围的我不会弄, 用sql弄了一个,能教我弄下多重范围的吗

原帖由 dawin 于 2010-8-5 22:43 发表
1有两个方法没实现。
期待答案。。

作者: simple666 时间: 2010-8-7 19:50

原帖由 magiccq 于 2010-8-5 20:37 发表
想学习一下，之前用过简单的透视表，跟着吴老师的进度和大家一起学习吧！
我用的是2007版，在“公式”，“计算项”是总会出现对话框，不能进行计算，图片如下，求高手解答！不甚感激……

在做题过程中也有此现象，重新建立透视表即可解决内存不足现象

作者: vsgraturn 时间: 2010-8-7 21:51
很管用，只是有些真搞不定，哈哈，现在专研的心思少了

作者: wangydm2 时间: 2010-8-8 10:09
那个提成的合计是错误的,怎样才能得到正确的?

作者: wuxiang_123 时间: 2010-8-9 10:30
标题: 第一卷第二章－常用透视表创建方法之傲龙九式（正文及课后练习）
不好意思，出了点小意外。现在补传。
这章的重点是多重合并计算数据区域。

傲龙九式：
第一式：龙威初现－普通透视表创建法
第二式：群龙戏珠－多重合并计算数据区域
第三式：龙腾四海－定义名称
第四式：深海囚龙－列表（有限制动态）
第五式：潜龙出海－列表（动态）
第六式：龙游浅水－数据再透视
第七式：龙行天下－外部数据源
第八式：飞龙在天－SQL
第九式：龙吟九天－MQ
PS:数据源中，我取消了列表，要使用列表，请大家自行创建。

[ 本帖最后由 wuxiang_123 于 2010-8-9 13:59 编辑 ]

作者: wuxiang_123 时间: 2010-8-9 10:36

原帖由 century_zhang 于 2010-8-5 19:00 发表
吴老师，正在模拟你的动画，发现练习2的解答和你的要求稍有不同：要求“排名”是按照总分的高低排名的，而解答中却没有实现这一目的，只是按照顺序写出了数字
不知是我理解有误？

原帖由 dawin 于 2010-8-5 21:44 发表
wuxiang老师公布的课后练习答案和之前的样本有点不一样。
你说的排名问题，应该是他不小心的手误，本来应该是对姓名以总分降序排列，结果点成了以排名降序了。所以给的答案错了。

他手误的还有一个练习3的第一题 ...

67楼正解。这个是手误。

作者: wuxiang_123 时间: 2010-8-9 10:49

原帖由 tomleej 于 2010-8-5 20:23 发表
谢谢
我现在用07版本，在预习二提（2），在选择“计算项”的时候，07版本不会显示右边“人员”类的“甲乙丙丁”等，无法实现03版的功能。

请教07版本在哪里寻找此功能？

还有在隐藏行标签“组/队” “汇总” ...

一：数据透视表区域，右键－数据透视表选项，显示－勾选经典视图。试试。
二：先启用选定，然后将鼠标放在项左边的边框处，出现箭头时点击就可以全选该项内容。07还可以一次取消所有分类汇总。

作者: suwenkai 时间: 2010-8-9 10:55
做完了，第二题用多重合并的方法，不修改数据源，可以吗？(经吴老师提示，可以用多重合并做第二题了。)

[ 本帖最后由 suwenkai 于 2010-8-9 18:01 编辑 ]

作者: wuxiang_123 时间: 2010-8-9 10:57

原帖由 magiccq 于 2010-8-5 20:37 发表
想学习一下，之前用过简单的透视表，跟着吴老师的进度和大家一起学习吧！
我用的是2007版，在“公式”，“计算项”是总会出现对话框，不能进行计算，图片如下，求高手解答！不甚感激……

呵呵，根据提示，可能的原因有两个，我的预习没有使用宏，可以排除宏，那么来看看内存原因。
下图是透视表帮助中给出的透视表限制，由此可以知道，透视表的字段数目受内存的限制。这时，我们可以关闭一些窗口或进程。或重新创建透视表，或保存透视表并关闭窗口后再重新打开插入计算项等。

作者: wuxiang_123 时间: 2010-8-9 10:58

原帖由 suwenkai 于 2010-8-9 10:55 发表
做完了，第二题用多重合并的方法，不修改数据源，可以吗？

呵呵，除非有特别说明，否则所有题目的前提都是不允许修改数据源。

作者: wuxiang_123 时间: 2010-8-9 11:00

原帖由 tomleej 于 2010-8-6 08:14 发表

it is weird. I can find "Items" under "Insert Calculate item" in office but failed at home! Maybe the copy version is the point!

anyway thanks

原帖由 tomleej 于 2010-8-6 08:26 发表

Unfortunately I still have no idea how to do the 练习三的第二题?  Waitting for the the answer...

I got it!

但还是不理解这个 "160" & "38000" 代表什么含义?  请指点

一：请尽量不要使用英文提问，我英文不懂。呵呵
二：可以具体点说吗？

作者: wuxiang_123 时间: 2010-8-9 11:01

原帖由 wangydm2 于 2010-8-8 10:09 发表
那个提成的合计是错误的,怎样才能得到正确的?

学习第二章就有答案。呵呵。

作者: wangydm2 时间: 2010-8-9 11:53
第一卷第二章課前预习2 的答案呢,我做不出来啊....

作者: 魂断蓝桥 时间: 2010-8-9 11:59
标题: 回复 92楼 wangydm2 的帖子
是那个转换格式的？
运用合并透视，然后双击汇总，生成数据清单，修改字段名即可。

作者: wuxiang_123 时间: 2010-8-9 12:12

原帖由 wangydm2 于 2010-8-9 11:53 发表
第一卷第二章課前预习2 的答案呢,我做不出来啊....

用数据再透视和SQL/MQ均可以解决。不过，一般更倾向于使用SQL和MQ.SQL和MQ将在第二卷详说。

作者: tomleej 时间: 2010-8-9 12:34

原帖由 wuxiang_123 于 2010-8-9 11:00 发表
一：请尽量不要使用英文提问，我英文不懂。呵呵
二：可以具体点说吗？

Sorry I can't use Chinese at office......

作者: wangydm2 时间: 2010-8-9 13:42

原帖由 魂断蓝桥 于 2010-8-9 11:59 发表
是那个转换格式的？
运用合并透视，然后双击汇总，生成数据清单，修改字段名即可。

轉換格式是練習3,我說的是練習2, 不知道怎樣將顏色作為列...

作者: wangydm2 时间: 2010-8-9 13:43

原帖由 wuxiang_123 于 2010-8-9 12:12 发表

用数据再透视和SQL/MQ均可以解决。不过，一般更倾向于使用SQL和MQ.SQL和MQ将在第二卷详说。

SQL我倒會一些,但這課前預習應該還不需要用到SQL吧?

作者: wuxiang_123 时间: 2010-8-9 13:47
标题: 回复 97楼 wangydm2 的帖子
第二章会用到SQL和MQ。不过，不用也可以做的。SQL和MQ将在第二卷中详说。这里只是让大家有个印象而已。

作者: wuxiang_123 时间: 2010-8-9 13:49

原帖由 wangydm2 于 2010-8-9 13:42 发表

轉換格式是練習3,我說的是練習2, 不知道怎樣將顏色作為列...

方法是用本章内容知识。

作者: wuxiang_123 时间: 2010-8-9 14:01

原帖由 suwenkai 于 2010-8-9 10:55 发表
做完了，第二题用多重合并的方法，不修改数据源，可以吗？

可以用多重合并计算数据区域。注意：数据是有规律的。这里将用到下一章的部分知识。

欢迎光临 ExcelHome技术论坛 (https://club.excelhome.net/)