用excel中数据透视表分类求数据平均值方法
用excel中数据透视表分类求数据平均值方法
　　考试结束，教育部门要求全市学生的成绩都汇总在如图1的同一个Excel工作表中，并要求求出各学校、各专业、各学科的平均分。
　　全市三个学校、每个学校都有六个专业、每个专业都有五门课、各专业人数不等，总计有1000余人，工作量巨大。但如果采用Excel数据透视表来完成的话，呵呵，就简单多了。
　　一、创建数据透视表
　　点击菜单命令&数据&数据透视表和数据透视图&，打开&数据透视表和数据透视图向导&对话框。
　　第一步，如图2所示，选择&Microsoft Excel数据列表或数据库&及下面的&数据透视表&单选项。
　　第二步，如图3所示，在&选定区域&输入全部数据所在的单元格区域，或者点击输入框右侧的&压缩对话&按钮，在工作表中用鼠标选定数据区域。
　　第三步，在对话框中选定&新建工作表&单选项，以便将创建的数据透视表放到一个新的工作表中，再点击&完成&按钮，如图4所示。
　　这样，就可以建立一个空的数据透视表，并同时显示&数据透视表&工具栏和&数据透视表字段列表&对话框，如图5所示。
　　二、利用数据透视表得到需要的结果
　　根据要求，我们应该得到各学校、各专业、各学科的平均分数。因此，我们应该把&学校&、&专业&作为行字段，而把各学科数据作为数据项。
　　从&数据透视表字段列表&中，把&学校&拖到数据透视表左侧&将行字段拖至此处&位置，再将&专业&字段拖至&学校&字段的右侧，待鼠标变成&I&字形时松开鼠标，如图6所示。如此就会得到如图7所示的效果。这正是我们想要计算平均分的各个项目。
　　现在，我们只要从&数据透视表字段列表&中依次将学科字段拖至数据透视表中&请将数据项拖至此处&位置，可以得到各学校、各专业、各学科的成绩总和。如图8所示。
　　现在我们将各个求和项改为求平均值就可以了。选中透视表中第一个&求和项：语文&，如图中C4单元格，然后点击&数据透视表工具栏&中&字段设置&按钮，打开&数据透视表字段&对话框。在此对话框&汇总方式&列表中选择&平均值&。如图9所示。确定后，所有各学校、各专业的语文科就由求和改成求平均值了。
　　其它学科照此办理。五门课只要五次这样的操作。与此同时，我们还会得到各学校的各学科平均分及全市三所学校的各学科平均分，如图10所示。
　　当然，用分类汇总的办法也可以完成上面各数据的分析。但是，这需要首先对数据进行相应的排序，而且如果我们需要产生报表，那么需要自己设置相应的表格。而用数据透视表来完成，则可以自动产生相应的报表，省却了设计表格的麻烦。另外，如果拖动行字段或列字段相应的字段，则可以方便地创建另外的报表，非常方便。如图11所示表格，即只需要将行字段中的&专业&字段拖至&列字段&，就可以了。
&&&主编推荐
H3C认证Java认证Oracle认证
基础英语软考英语项目管理英语职场英语
.NETPowerBuilderWeb开发游戏开发Perl
二级模拟试题一级模拟试题一级考试经验四级考试资料
港口与航道工程建设工程法规及相关知识建设工程经济考试大纲矿业工程市政公用工程通信与广电工程
操作系统汇编语言计算机系统结构人工智能数据库系统微机与接口
软件测试软件外包系统分析与建模敏捷开发
法律法规历年试题软考英语网络管理员系统架构设计师信息系统监理师
高级通信工程师考试大纲设备环境综合能力
路由技术网络存储无线网络网络设备
CPMP考试prince2认证项目范围管理项目配置管理项目管理案例项目经理项目干系人管理
Powerpoint教程WPS教程
电子政务客户关系管理首席信息官办公自动化大数据
职称考试题目
就业指导签约违约职业测评
招生信息考研政治
网络安全安全设置工具使用手机安全
3DMax教程Flash教程CorelDraw教程Director教程
Dreamwaver教程HTML教程网站策划网站运营Frontpage教程
生物识别传感器物联网传输层物联网前沿技术物联网案例分析
互联网电信IT业界IT生活
Java核心技术J2ME教程
Linux系统管理Linux编程Linux安全AIX教程
Windows系统管理Windows教程Windows网络管理Windows故障
组织运营财务资本
视频播放文件压缩杀毒软件输入法微博
数据库开发Sybase数据库Informix数据库
&&&&&&&&&&&&&&&
希赛网 版权所有 & &&&&湘教QS2-164&&增值电信业务经营许可证湘B2-运用Excel函数、数据透视表和图表灵活分析薪酬数据
& & & 挺长一段时间没在三茅写东西了。最近在各地巡回上课培训，接触了几个比较典型的案例，现在跟大家分享下。
1、月薪酬数据的多维度分析
& & & &对于这个问题，很多人会觉得是多余的，觉得没什么分析的必要。但是，假如领导给你要这个月，每个部门的社保合计是多少？加班费是多少？哪些部门的加班费出现了异常？每个部门的人数是多少？最低工资如何、最高工资如何？人均工资如何？等等，你会如何在5分钟内作出相应的报告出来？
& & & 这样的问题， 如果使用数据透视表来解决，将是非常方便和迅速的。下面举例说明。
& & & 下图是某个月薪酬数据，现在要求制作各种维度的分析报表。&
& & & 首先以原始数据制作一个基本的透视表，然后进行基本布局，就得到每个部门的社保汇总表，如下
& & & 将数据透视表进行重新布局，就得到如下的考勤汇总表格：
& & & & 重新布局透视表，并对应发合计进行不同的汇总计算，就得到如下的报表，然后绘制薪浮图，可以更加清楚的揭示每个部门的工资情况：
& & & & &利用透视表组合功能，还可以对工资区间进行分析，如下：
2、全年12个月工资汇总分析
& & & & 对于这个问题，最简单的方法是先把12个月工资数据复制粘贴到一个工作表，然后再进行透视分析。此外，还有一个更加科学和灵活的方法，就是使用现有连接+SQL语句的方法制作动态汇总分析报表，但是这种方法对每个工资表的要求是极其严格的，并且要编写相应的SQL语句，此处就不再介绍了。
3、月度薪酬滚动分析模版
& & & & 前面的分析方法，都是对已有数据的静态分析。假如要对每个月、每个部门、每个岗位、每个人、每个工资项目进行跟踪分析，并对预算和实际实现情况进行差异分析，及时发现异动数据，又该如何做呢？此时，需要使用函数建立滚动跟踪分析模版了。由于这种模版的制作需要了解掌握很多函数，此处不再介绍了，感兴趣的同学可以加QQ群，跟同行和朋友进行交流，QQ交流学习群号：。
下面是一个薪酬滚动分析模版的示意图，供大家参考借鉴。
【作者简介】
&&&&韩小良，国内著名资深实战型Excel培训讲师和应用解决方案专家。Excel应用方面的著作第一人，出版了近40部关于Excel企业管理应用方面的实用书籍。从事Excel研究及应用多年，对Excel及VBA在企业管理中的应用有着较深的研究和独特的认识，对Excel及VBA在企业管理中的高级应用培训有着丰富的实战经验，已经为数千家大中型企业进行了Excel培训和应用方案解决咨询服务，在业界享有很高的评价，深得客户认可。主要著作：《构建高效数据分析模版——Excel函数和动态图表高级应用》，《Excel高效财务管理和分析模版应用大全》，《Excel 会计与财务高效应用从入门到精通》等等。
专家最新作品
劳动法专家段海宇
红尘醉弥勒徐胜华
实战派HRD贺清君
劳动派张立岩如何利用定义名称创建动态数据透视表 -- Excel技巧网 -- 传送门
如何利用定义名称创建动态数据透视表
1、本站畅销20万册的Excel\Word图书，请点击“阅读原文”。2、本微信首页底部莱单，可查看所有历史消息。3、微信回复：“教程”二字，可获取海量Excel视频学习教程下载地址。（五）疑难116 在Excel中，如何利用定义名称创建动态数据透视表在实际工作中，数据透视表数据源的数据记录往往都是实时变动的，如图 7-24所示为某药店的药品销售明细数据，其销售数据记录每天都在增加，如果使用普通的数据透视表来汇总该数据明细，则每次增加数据记录时，都需要重新更改数据透视表的数据源，透视表才能正确的反映其汇总情况，给实际工作带来诸多不便，那么有没有办法在增加数据记录的同时自动更改数据透视表的数据范围，使得数据透视表能够动态反映明细数据的汇总情况呢？图7-24药品销售明细→ 解决方案：利用名称定义数据源的范围，然后创建数据透视表。→ 操作方法※定义名称法创建动态数据透视表※步骤1 如图 7-25所示，单击“公式”选项卡→“定义的名称”组→“定义名称”→在名称文本框中输入“Data”→在引用位置文本框中输入如下公式：=OFFSET(药品销售明细!$A$1,,,COUNTA(药品销售明细!$A:$A),COUNTA(药品销售明细!$1:$1))→单击“确定”退出“新建名称”对话框。图7-25定义数据源名称步骤2 如图 7-26所示，单击“插入”选项卡→“表格”组→“数据透视表”→在表/区域文本框中输入已定义的名称“Data”→设置数据透视表的位置为“新工作表”→单击“确定”退出“创建数据透视表”对话框。图7-26创建数据透视表步骤3 在数据透视表字段列表中将编号、品名字段拖动至“行标签”区域，将数量、金额字段拖动至数值区域，如图 7-27所示。图7-27拖动字段步骤4 设置数据透视表布局为以表格形式显示，不显示分类汇总，重命名数值字段名为“数量”、“金额”，设置C、D列单元格格式为数值并保留两位小数，最后给数据透视表设置边框，结果如图 7-28所示。图7-28设置布局和样式至此已完成创建动态数据透视表，当数据源增加数据记录时，只需要刷新数据透视表即可动态更新汇总数据。→ 原理分析※OFFSET函数语法※OFFSET函数以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域。并可以指定返回的行数或列数。其语法如下：OFFSET(reference, rows, cols, [height],[width])OFFSET函数的参数说明如表 7-1所示。表7-1 OFFSET函数参数说明参数说明Reference必需参数，作为偏移量参照系的引用区域。Reference 必须为对单元格或相连单元格区域的引用；否则，OFFSET 返回错误值 #VALUE!Rows必需参数。相对于偏移量参照系的左上角单元格，上（下）偏移的行数。如果使用 5 作为参数 Rows，则说明目标引用区域的左上角单元格比 reference 低 5 行。行数可为正数（代表在起始引用的下方）或负数（代表在起始引用的上方）Cols必需参数。相对于偏移量参照系的左上角单元格，左（右）偏移的列数。如果使用 5 作为参数 Cols，则说明目标引用区域的左上角的单元格比 reference 靠右 5 列。列数可为正数（代表在起始引用的右边）或负数（代表在起始引用的左边）Height可选参数。高度，即所要返回的引用区域的行数。Height 必须为正数Width可选参数。宽度，即所要返回的引用区域的列数。Width 必须为正数※COUNTA函数语法※COUNTA 函数计算区域中不为空的单元格的个数。其语法如下：COUNTA(value1, [value2], ...)COUNTA函数的参数说明如表7-2所示。表 7-2 COUNTA函数参数说明参数说明value1必需参数。表示要计数的值的第一个参数value2, ...可选参数。表示要计数的值的其他参数，最多可包含 255 个参数版权所有，侵权必究。1、本站畅销20万册的Excel\Word图书，请点击“阅读原文”。2、本微信首页底部莱单，可查看所有历史消息。3、微信回复：“教程”二字，可获取海量Excel视频学习教程下载地址。
微信扫一扫获得更多内容
关于 Excel技巧网
全网第一Excel专业微博
全网第一Excel专业微信
经典技巧 每日推送
图文并茂 详尽易懂
等着，你永远不会错过的精彩！
Excel技巧网 微信二维码
分享这篇文章}