EXCEL高级应用培训.ppt

《EXCEL高级应用培训.ppt》由会员分享，可在线阅读，更多相关《EXCEL高级应用培训.ppt（26页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、EXCEL高级应用培训高级应用培训数据透视表数据透视表数据透视表数据透视表l关于数据透视表l数据透视表实例l数据透视表相关术语l创建数据透视表l数据透视表项目组合l创建计算字段或者计算条目l基于多重汇总数据区域创建数据透视表关于数据透视表关于数据透视表l实际工作中需要从大量的原始数据中提取出重要的管理决策信息。数据透视表使用特殊的直接操纵技术，方便了对数据的汇总和分析。l之所以称为“透视表”（Pivot Table），因为通过方便、简捷地扭转和重组（即透视）行和列标题，用户可以随心所欲的更改数据透视表的布局。关于数据透视表关于数据透视表l数据透视表是管理数据的一种方法。利用数据透视表，可以重新

2、组织数据清单中的大量数据，按照一定的要求进行分类汇总，从中找出规律，使数据表格更易于理解并能通过绘制数据透视图形象地显示数据。l数据透视表将排序、筛选和分类总3项功能结合起来，对数据清单或列表的数据源重新组织和计算，以多种不同形式显示出来，使用户进一步理解数据的内涵。数据透视表实例数据透视表实例l某数据库包含银行日常新账户信息。包括：每个账户开户日期账户类型（CD,Checking,Savings以及IRA）开户人（出纳员或新账户代表）开户部门（Central,Westside或者North County）是新客户还是老客户开的账户数据透视表实例数据透视表实例开户日期开户日期开户总数开户总数账

3、户类型账户类型开户人开户人开户部门开户部门新新/老客户老客户2010-1-1340 CDTellerCentralExisting2010-1-1156,378 CDNew AcctsCentralExisting2010-1-15,633 CDNew AcctsCentralExisting2010-1-1120 SavingsNew AcctsCentralExisting2010-1-145,343,738 SavingsNew AcctsWestsideExisting2010-1-1522 SavingsNew AcctsNorth CountyExisting2010-1-1899

4、 SavingsNew AcctsNorth CountyExisting2010-1-154,444 SavingsNew AcctsNorth CountyExisting2010-1-16,544 SavingsNew AcctsNorth CountyNew2010-1-1456 SavingsNew AcctsNorth CountyNew2010-1-157,979 checkingNew AcctsNorth CountyNew。数据透视表实例数据透视表实例l有关银行管理的几个问题：按账户类型分，每个部门的总存款数量是多少？不同账户类型的金额分布状态如何？出纳员新开的哪类账户最多

5、？Central部门与其他两个部门的比较情况怎样？哪个部门为新客户开的账户最多？数据透视表实例数据透视表实例求和项求和项:开户总数开户总数开户部门开户部门账户类型账户类型CentralCentralNorth CountyNorth CountyWestsideWestside总计总计CDCD936,484 767 718,554 1,655,805 checkingchecking164,618 70,466 34,535 269,619 IRAIRA129,354 141,623 270,977 SavingsSavings120 70,753 45,547,737 45,618,610

6、总计总计1,230,576 141,986 46,442,449 47,815,011 例题1数据透视表实例数据透视表实例新新/老客户老客户ExistingExisting求和项求和项:开户总数开户总数账户类型账户类型开户部门开户部门CDCDcheckingcheckingIRAIRASavingsSavings总计总计CentralCentral936,484 53 127,699 120 1,064,356 North CountyNorth County55,865 55,865 WestsideWestside1,857 41,737 45,526,953 45,570,547 总计总

7、计938,341 53 169,436 45,582,938 46,690,768 例题2数据透视表相关术语数据透视表相关术语l字段：信息的种类。l列字段：具有列方向的字段。l行字段：具有行方向的字段。l项目：出现在字段中的元素。l数据区：需要总结数据的单元格。l组：被视为一个单独项目的项目集合。l页字段：具有分栏方向的字段。某个时刻，页字段只能显示一个项目（或者所有项目）。l数据源：用于创建数据透视表的数据。创建数据透视表创建数据透视表l定义数据位置l定义数据l实现数据透视表l使用对话框规划数据透视表l使用透视表字段列表工具规划数据透视表数据透视表项目组合数据透视表项目组合l实例：日期日期销

8、售额销售额2010-1-11,231 2010-1-24,561 2010-1-3121 2010-1-4454。2010-1-3184 2010-2-1262。2010-2-2812,865 2010-3-1165。2010-3-311,588 数据透视表项目组合数据透视表项目组合求和项求和项:销售额销售额日期日期汇总汇总2010-1-112312010-1-245612010-1-31212010-1-44542010-1-5846852010-1-6122010-1-745452010-1-815152010-1-91512010-1-1046。求和项求和项:销售额销售额日期日期汇总汇总

9、1月174,923 2月659,672 3月1,665,074 总计2,499,669 例题3快速统计重复项目快速统计重复项目日期日期销售区域销售区域 销售数量销售数量 2005-6-1广州 161 2005-6-1南宁 60 2005-6-2北京 174 2005-6-2广州 13 2005-6-2北京 760 2005-6-2广州 200 2005-6-2上海 80 2005-6-3南宁 12 2005-6-3广州 1 2005-6-3广州 69 2005-6-3北京 61 2005-6-3广州 472 统计各个销售区域在表格中出现的次数例题8利用数据透视表统计考试分数利用数据透视表统计考

10、试分数班级班级成绩成绩一(1)班45一(1)班25一(1)班89一(1)班65一(1)班79一(2)班96一(2)班46一(2)班27一(2)班88一(2)班45一(2)班86一(3)班94一(3)班37各班级成绩最大值、最小值、平均值统计例题9创建计算字段或者计算条目创建计算字段或者计算条目l一旦创建了数据透视表，就可以创建两种类型的公式做进一步的分析：计算字段：在数据透视表上从其他字段上创建的新字段。一个计算字段必须驻留在数据透视表的DATA区。计算条目：计算条目使用数据透视表字段上其他项目的内容。一个计算条目必须驻留在数据透视表的PAGE,ROW或者COLUMN。创建计算字段或者计算条目

11、创建计算字段或者计算条目l实例：销售代表销售代表月份月份销售额销售额销售套数销售套数张三JAN￥456,328239张三FEB￥25,46356张三MAR￥454,37882张三。张三DEC￥52,36656李四JAN￥40,48789李四FEB￥752,35375李四MAR￥373,531231李四APR￥278,232255李四。李四DEC￥645,678287创建计算字段创建计算字段l计算字段：因为一个数据透视表是一个特殊的数据范围，因此，不能向数据透视表中插入新行或新列。即不能插入公式来对数据透视表中的数据进行运算。然而，可以为数据透视表创建计算字段。一个计算字段包含一种计算，该计算可

12、以包含其他字段。l一个计算字段基本上是一种显示数据透视表上新信息的方法。它实质上给出在数据源上创建新DATA字段的一种选择。创建计算字段创建计算字段销售代表销售代表月份月份数据数据李四李四张三张三总计总计JAN求和项:销售额40,487 456,328 496,815 求和项：平均销售额455 1,909 1,515 FEB求和项:销售额752,353 25,463 777,816 求和项：平均销售额10,031 455 5,938 MAR求和项:销售额373,531 454,378 827,909 求和项：平均销售额1,617 5,541 2,645。DEC求和项:销售额645,678 52

13、,366 698,044 求和项：平均销售额2,250 935 2,035 求和项求和项:销售额汇总销售额汇总4,975,048 3,187,916 8,162,964 求和项：平均销售额汇总求和项：平均销售额汇总2,749 1,524 2,092 例题4创建计算条目创建计算条目l计算条目：上例可以创建一个计算条目（比如，季度销售额），显示一、二、三个月的销售额的和。l通过组合项目也能完成相同的任务，但是，使用组合隐藏了不可见的月份信息，只显示组的总和。创建计算条目创建计算条目求和项求和项:销售额销售额销售代表销售代表月份月份李四李四张三张三总计总计JAN40,487 456,328 496,

14、815 FEB752,353 25,463 777,816 MAR373,531 454,378 827,909 季度销售额11,166,371 936,169 2,102,540 APR278,232 54,387 332,619 MAY98,546 321,854 420,400 JUN457,588 524,874 982,462 季度销售额2834,366 901,115 1,735,481。季度销售额4779,897 670,656 1,450,553 总计总计9,950,096 9,950,096 6,375,832 6,375,832 16,325,928 16,325,928

15、例题5基于多重合并数据区域创建数据透视表基于多重合并数据区域创建数据透视表l用户可以使用来自不同工作表中，结构完全相同的数据源来创建数据透视表，这些工作表可以是同一个工作簿的，也可以属于不同的工作簿。l假设有一个公司，专门批发电子器件，有几个位于不同地域的办公室，每个办公室都提交了一份按产品分类的销售预算报表。现在公司总部需要把这些报表合并。基于多重合并数据区域创建数据透视表基于多重合并数据区域创建数据透视表财务科：财务科：科目名称科目名称1月月2月月3月月4月月5月月6月月办公费2036 143 7023 5499 3190 3539 差旅费8486 1391 7661 7865 3695

16、3588 福利4829 7832 3696 4079 5656 7292 工资3581 2812 2452 5303 8886 646 计算机费用4413 4341 4265 8690 530 7391 交通工具费9624 8594 487 6280 2876 1615 教育经费3791 7490 4440 3895 7611 715。合计合计93031 93031 69582 69582 65046 65046 88844 88844 60161 60161 50590 50590 信息办公室：信息办公室：科目名称科目名称1月月2月月3月月4月月5月月6月月办公费7193 9533 4584 3888 5777 1138 差旅费113 4402 9423 2423 3561 3628 例题6例题7复制数据透视表复制数据透视表例题10数据透视表练习数据透视表练习l综合练习1l综合练习2谢谢 谢！谢！