用excel中数据透视表分类求数据平均值方法

用excel中数据透视表分类求数据平均值方法 Excel教程中数据透视表的用法实例 数据透视表是一个系列教程，IT部落窝会为大家逐步讲解数据透视表和数据透视图关联的知识，配合实例加以讲解，并附上案例的excel源文件供大家学习使用。 数据透视表是excel教程中功能最大、使用最灵活、操作最简单的工具。使用数据透视表不必输入复杂的公式和函数，仅仅通过向导就可以创建一个交互式表格，从而自动提取、组织和汇总数据。如果将数据透视表和函数结合使用，更能创建出满足各种需求的报表。什么是数据透视表呢?数据透视表就是一种交互式报表，可以快速分类汇总大量的数据，并可以随时选择页、行和列中的不同元素，快速查看源数据的不同统计结果，同时还可以随意显示和打印出用户感兴趣区域的明细数据，使分析、组织复杂的数据更加快捷有效。数据透视表的作用就是将用户从创建复制公式、使用各种函数的烦琐工作中解脱出来，使其迅速而准确的对数据进行处理分析，制作出漂亮的报告和图表。 以工作表数据制作数据透视表的注意事项有以下七点： 以工作表数据制作数据透视表，这些工作表数据必须是一个数据清单。所谓数据清单，就是在工作表数据区域的顶端行为字段名称(标题)，以后各行为数据(记录)，并且各列只包含一种类型数据的数据区域。这种结构的数据区域就相当于一个保存在工作表的数据库。第一，数据区域的顶端行为字段名称(标题)。 第二，避免在数据清单中存在有空行和空列。这里需指明以下，所谓空行，是指在某行的各列中没有任何数据，如果某行的某些列没有数据，但其他列有数据，那么该行就不是空行。同样，空列也是如此。 第三，各列只包含一种类型数据。 第四，避免在数据清单中出现合并单元格。 第五，避免在单元格的开始和末尾输入空格。 第六，尽量避免在一张工作表中建立多个数据清单，每张工作表最好仅使用一个数据清单。 第七，工作表的数据清单应与其他数据之间至少留出一个空列和一个空行，以便于检测和选定数据清单。 在制作数据透视表之前，应该按照以上7点来检查数据区域，如果不满足上面的要求，需要先进行工作表数据从而使之规范。 本文讲解了三个知识点：第一，什么是数据透视表，第二，数据透视表的作用，第三以工作表数据制作数据透视表的注意事项，下面一片文章，我们将以实例介绍如何数据清单：删除数据区域内的所有空行的四种方法。 删除数据区域内所有空行的方法有多种，比如排序、高级筛选、自动筛选、VBA编写。下面就这几种删除空行的方法逐一介绍。 本文实例为员工的工资和个税清单。在这个数据清单中就存在一些空行，为了制造数据透视表，首先就需要将这些空行删除掉。 第一种删除空行的方法：排序法 第一步，在数据清单的右侧插入一个辅助列，D列。 第二步，在D列中输入1,2,3,4,5,6，连续的自然数序列。 第三步，单击“数据”“排序”，对职工姓名列（A列）进行升序排序，这样就将数据区域内的所有空行排在了数据区域的底部。 第四步，删除数据区域内底部的所有空行。 第五步，对D列进行升序排列，恢复数据的原始位置。 第六步，删除辅助列，就得到删除所有空行后的数据区域。 第二种删除空行的方法方法：高级筛选法 在利用高级筛选工具筛选并删除数据区域内的所有空行之前，首先要设置条件区域。进行设置条件区域需要了解条件区域的设置规则。 为了筛选并删除数据区域内的所有空行，需要对数据区域内各列的数据进行判断，也就是判断在某行各列是否有数据。对于文本型数据，星号（*）表示有数据，对于数值型数据，不等于好（）表示有数据，这样，就可以在原始数据区域之外的任意单元格设置条件区域。 设置完成条件区域后，单击“数据”“筛选”“高级筛选”命令，弹出高级筛选对话框，在“列表区域”文本框输入列表区域“$A$1:$C$20”，在“条件区域”输入“$E$2:$G$5”，选中“将筛选结果复制到其他位置”，并在“复制到”输入“$I$1:$K$1”，单击确定即可。 第三种删除空行的方法方法：自动筛选法 第一步，单击“数据”“筛选”“自动筛选”命令。 第二步，从“姓名”单元格的下拉列表中选择（非空白）选项，得到筛选结果。 第三步，选取数据区域的所有单元格，按下F5键，弹出“定位”对话框，单击“定位条件”，选择“可见单元格”，确定。 第四步，复制，在需要保存数据的空白单元格单击，粘贴。 第五步，删除原始数据区域。 第四种删除空行的方法方法：VBA代码 编写下面一段出现，运行这段程序，就可以迅速的将原始数据区域内的所有空行删除。 Sub DeleteEmptyRows() Dim LastRow As Long Dim r As Long LastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count Application.ScreenUpdating = False For r = LastRow To 1 Step -1 If Application.WorksheetFunction.CountA(Rows(r) = 0 Then Rows(r).Delete Next r Application.ScreenUpdating = True End Sub 在数据透视表系列教程二，讲解了一次性的删除数据区域内的所有空行的几种方法。制作数据透视表之前必须把工作表中的空行空列都需要删除，才能避免错误。 本文就讲解一次性的删除数据区域内的所有空列的两种方法。 第一种一次性删除数据区域内的所有空列的方法是借助辅助列和公式来删除空列。这种方法是一个辅助列，并利用COUNTA函数统计各列不为空的单元格个数（如果为空列，那么不为空单元格的个数就是0），然后用一个常量除以统计的单元格个数。当某列为空列时，就会出现错误值“#DIV/0！”，这样，就可以利用定位工具定位到所有出现错误值的单元格，删除出现错误值单元格所在的整列。 实例如下图所示： 具体操作步骤如下： 第一步，在数据区域下的任意一行，比如A8单元格输入公式：=1/COUNTA(A1:A6)，然后向右填充复制到H8，得到计算结果，可以看到D、F两行空列都是错误公式。 第二步，单击任意数据区域的单元格，按下F5键，弹出“定位”对话框，单击“定位条件”，选择“公式”选项组下面的“错误”复选框，确定。就可以将所有错误公式的列选中。第三步，单击“”“删除”“整列”。 第四步，删除辅助行。 第二种一次性的删除数据区域内的所有空列的方法是使用VBA代码。 下面是编写的一段程序，只要运行这段程序，就可以迅速将所有空列删除。代码如下： Sub DeleteEmptyColumns() Dim LastCol As Long, r As Long LastCol = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count Application.ScreenUpdating = False For r = LastCol To 1 Step -1 If Application.WorksheetFunction.CountA(Columns(r) = 0 Then Columns(r).Delete Next r Application.ScreenUpdating = True End Sub 数据区域的所有小计行会在一定程度上影响数据透视表的统计汇总结果。尽管可以不在数据透视表中显示这些小计，但这些小计项目的存在终究是多余的。实际上，数据透视表会自动添加各个类别项目的小计。 如何一次性快速的删除工作表中的小计行和全年的合计行呢，工作表如下图所示。 第一步，将光标定位在工作表数据区域，按下CTRL+F键，打开“查找和替换”对话框，在 “查找”框中输入“*计”，单击“查找全部”按钮，所有最后一个字为“计”的单元格都被查找出来了。 “查找和替换”对话框激活状态下，按下CTRL+A，即可选中所有小计行。 第二步，单击“”“删除”“整行”。 在某些情况下，可能在某列中既输入了数字型文本，有输入了纯数字，比如序号、电话号码等，这样，在利用数据透视表进行汇总计算时，会将看起来相同但实际并不相同的序号等处理为两种类别，从而造成汇总计算错误。因此，在这种情况，就必须将文本型数字和纯数字混杂的行进行统一处理，要么统一处理为文本型数字，要么统一处理为纯数字。 我们看下图，B列的产品编号数据既有文本型数字，也有纯数字，制作的数据透视表如右边所示，显然，这样的汇总计算结果是错误的。因此，我们对B列数据做如下处理。 为了能够对数据进行正确的处理和分析，必须将产品编号处理为统一类型的数据。首先，介绍文本型数字转换为数字的方法 比如，新建一列，输入=VALUE(B2)，然后下拉，或者使用公式“=1*B2”、“=B2/1”、“=-B2”，转换后，再使用选择性粘贴工具将公式转换为数值，然后将原始的B列数据替换。第二种方法，也可以使用智能标记中的“转换为数字”命令。 第三种方法，使用选择性粘贴的批量计算功能，对文本型数字批量修改的方法是：在任何一个空白单元格，输入数字1，选择该单元格，复制，然后再选择要批量进行转换的单元格区域，打开“选择性粘贴”对话框，选中“数值”单选按钮和“乘”或“除”单选按钮，也就是将原始数据乘以或者除以数字1，那么就会将文本型数字转换为数字。 接下来，我们介绍数字转换为文本型数字的方法，可以使用TEXT函数。比如输入公式：=TEXT(B2,0000)，往下拖，就可以实现了。比如上图B列的产品编号是4位数字，所以参数使用0000。 更多Excel教程案例学习请加Excel学习交流群：284029260 （三）案例学习：合并和分析数据集： 管理人员转发给你的电子表格如下图所示，并且要求提取各季度每个型号两年的平均收入，管理人员需要这些图形用于如开一个将在15分钟之后开始的会议，因此，你几乎没有时间组织和汇总该数据 图表 1 如果这只是一个需要快速完成的一次性分析，那么可以使用数据透视表，要创建数据这视表请遵循以下步骤： 1、 数据数据透视表与数据透视图，选择“多重合并计算数据区域”，单击“一下步” 2、 选择“自定义页字段”，单击“下一步” 3、 添加一个数据区域，然后选择数字1旁边单选按钮以激活“2字段1”输入枢，在该输入框中输入xx，对话框如下图所示： 图表 2 4、 对于每一个数据集重复第3步，直到添加了所有数据区域为至。务必在FYxx下的所有数据集中输入xx，在FYxx下的所有数据集中输入xx。完成之后如下图所不： 图表 3 5、 单击“完成” 此时已经成功地将数据合并到一个数据透视表中！，如下图所示，必须将“值”字段的字段设置从计算“计数”更改为“平均值”。 图表 4：生成数据透视表之后，需要将值字段设置从“计数”更改为“平均值” 对字段进行一些格式化和重命名之后，就完成了任务，如下图所示 图表 5 通过对字段进行格式化和重命名改进了数据透视表，但是想对字段进行进一步分析并且重新组织以便提供数据的最佳视图 通过下图中所示的简化结构显示了采用易于读取格式数据的最佳数量 Excel操作练习一 说明：考生必须注意选择指定的工作表，否则不得分。 在文档XLJ.XLS中进行如下操作： 1、计算与填充（7分） 在Sheet1中进行如下操作： (1)公式计算 ?按公式“语文总成绩 = 语文平均成绩 * 人数”计算“语文总成绩”列 ?按公式“数学总成绩 = 数学平均成绩 * 人数”计算“数学总成绩”列 (2)数据填充 ?按日期填充第七列（日期序列），以“一九九一年一月一日”为起点，以年为单位，步长值为1，终点为第11行（包括11行）。 2、图表制作（5分） 在Sheet2中制作嵌入图表，要求如下： ?数据源依次为“产品名称”、“单价”、“库存数”，其中“产品名称”列为分类轴。 ?图表类型为“折线图”，子图表类型为第1行第1个。 ?图表标题为“电脑硬件状态图”。 3、数据库应用（3分） 在Sheet3中进行如下操作： ?自动筛选：筛选出“本期水费”大于15，而且“本期电费”大于40的记录。 4、数据透视表（5分） 在Sheet4中制作数据透视表，要求如下： ?在A18单元格开始的右下区域插入如下图所示的数据透视表（注意：位置错误不得分）。 ?其中行为“名称”，列为“种类”，数据为“产量（公斤）”，汇总方式为“求和”。 5、将本文档存盘，退出EXCEL。 方法与步骤： 1、 （1）打开本题在“语文总成绩 ”下面的单元格内定光标点击或输入“=”按公式“语文总成绩 =语文平均成绩 * 人数”依次点击输入C2*B2点击“”或按回车键得出“5075”即总分首数移动鼠标呈“+”（填充柄）向下拖动鼠标至11行得出“语文总成绩 ”列。 在“数学总成绩”下面的单元格内定光标点击或输入“=”按公式“数学总成绩 = 数学平均成绩 * 人数”依次点击输入E2*B2点击“”或按回车键得出“4408”即总分首数移动鼠标呈“+”（填充柄）向下拖动鼠标至11行得出“数学总成绩 ”列。 （2）选中第七列起点“一九九一年一月一日”终点11行填充序列日期序列以年为单位输入步长1确定点保存按钮。 2、打开Sheet2按住Ctrl键依次选中数据源“产品名称”“单价”“库存数”点“图表向导”（或插入图表）按要求选择图标类型“折线图”下一步 系列产生在“列”下一步图标标题输入“电脑硬件状态图”分类轴（X）输入“产品名称”下一步完成点保存按钮。 3、打开Sheet3选中所有数据区域数据筛选自动筛选本期水费自定义大于15确定本期电费自定义大于40确定点保存按钮。 4、打开Sheet4定光标在A18单元格数据数据透视表和数据透视图向导下一步选中所有数据区域下一步版式按要求拖动行为“名称”列为“种类”数据为“产量（公斤）”汇总方式为“求和”确定完成存盘退出EXCEL。 Excel操作练习二 说明：考生必须注意选择指定的工作表，否则不得分。 在文档XLH.XLS中进行如下操作： 1、计算与填充（6分） 在Sheet1中进行如下操作： (1)公式计算 ?按公式“总分=语文*0.6+数学*0.7+外语*0.8”计算“总分”列。 ?计算“数学”的平均值，并填写到第14行的对应单元格中。 (2)数据填充 ?按等比序列填充第七列，起点为第2行，步长值为3，终点为第13行（包括13行）。 2、图表制作（5分） 在Sheet2中制作嵌入图表，要求如下： ?数据源依次为“姓名”、“一季度奖金”，其中“姓名”列为分类轴。 ?图表类型为“三维簇状柱形图”。 ?图表标题为“xx年奖金发放表”。 ?不显示图例。 3、数据库应用（2分） 在Sheet3中进行如下操作： ?对记录排序，主要关键字为“出厂价”，从小到大，次要关键字为“市场价”，从小到大。 4、数据透视表（4分） 在Sheet4中制作数据透视表，要求如下： ?在A18单元格开始的右下区域插入如下图所示的数据透视表（注意：位置错误不得分）。 ?其中列为“影院”，数据为“卖出票数”，汇总方式为“求和”。 5、格式排版（3分） 在Sheet5中进行如下操作： ?设置标题“xx年奖金发放表”在A-E列水平跨列居中。 ?第三行行高设为23磅。 ?设置第3-15行数据区域的外边框和内部水平、垂直线为单线 6、将本文档存盘，退出EXCEL。 方法与步骤： 1、 （1）打开本题在“总分”下面的单元格内定光标点击或输入“=”按公式“总分=语文*0.6+数学*0.7+外语*0.8”依次点击输入C2*0.6+D2*0.7+E2*0.8点击“”或按回车键得出“188.6”即总分首数移动鼠标呈“+”（填充柄）向下拖动鼠标至13行得出“总分”列。 （2）选中第七列起点第二行终点13行填充序列等比输入步长3确定点保存按钮。 2、打开Sheet2点“图表向导”（或插入图表）按要求选择图标类型下一步按住Ctrl键依次选中数据源“姓名”“一季度奖金系列产生在“列”下一步图标标题输入“xx年奖金发放表”图标类型输入“姓名”图例不显示下一步完成点保存按钮。 3、打开Sheet3选中所有数据区域数据排序主要关键字选“出厂价”次要关键字选“市场价”递增点保存按钮。 4、打开Sheet4定光标在A18单元格数据数据透视表和数据透视图向导下一步选中所有数据区域下一步版式按要求拖动列为“影院”数据为“卖出票数”汇总方式为“求和”确定完成点保存按钮。 5、 （1）打开Sheet5选中“xx年奖金发放表”即A1-E1右击设置单元格格式对齐水平对齐选“跨列居中. （2）选中或定光标第3行格式行行高输入23磅确定. （3）第3-15行数据区域右击设置单元格格式边框单线外边框内部确定存盘关闭退出EXCEL。 Excel操作练习三 说明：考生必须注意选择SHEET，否则不得分。 在文档XLC.XLS中进行如下操作： 1、计算与填充（7分） 在SHEET1中进行如下操作： ?按公式“成绩总评 = 平时成绩 + 期中成绩 + 期末成绩”计算“成绩总评”列 ?分别计算“平时成绩”、“期中成绩”、“期末成绩”三列的合计，并填写到第14行的对应单元格中。 2、格式排版（5分） 在SHEET2中进行如下操作： ?修改标题“银行存款汇总表”的格式为宋体、加粗、14磅，在A-E列水平跨列居中 ?设置第3行表头部分的格式为黑体，12磅 ?设置第4-12行数据区域的格式为宋体、10磅 ?设置第3行行高为20磅 3、图表制作（5分） 在SHEET3中进行制作嵌入图表，要求如下： ?数据源依次为“日期”、“货物一”、“货物二”，其中“日期”列为分类轴。 ?图表类型为“簇状柱形图”（子图表类型为左上角第一种） ?图表标题为“每周货物销售情况” ?图例位置为“右上角” 4、数据库应用（3分） 在SHEET4中进行如下操作： ?对记录排序，主要关键字为“计算机”，从小到大，次要关键字为“英语”，也是从小到大 5、将本文档存盘，退出EXCEL。 方法与步骤： 1、 （1）打开本题在“成绩总评”下面的单元格内定光标输入（或点击工具栏上的）“=”按公式“成绩总评=平时成绩+期中成绩+期末成绩”依次点击第二行相应的数字并依次输入“+”在当前单元格出现“D2+E2+F2”时点击工具栏上的“”或按回车键得出“242.50”即“成绩总评”首数时移动鼠标呈“+”（填充柄）向下拖动鼠标至13行得出“成绩总评”列。 （2）选中“平时成绩”“期中成绩”“期末成绩”三项数据区并选中三项的第14行的单元格相应处点工具栏上的“”（自动求和按钮）点保存按钮。 2、 （1）打开Sheet2选中“银行存款汇总表”即A1-E1右击设置单元格格式对齐水平对齐选“跨列居中”字体宋体加粗14磅确定。 （2）选中第三行数据区域字体黑体12磅。 （3）选中第4-12行数据区域字体宋体10磅。 （4）选中或定光标第3行格式行行高输入20磅确定点保存按钮。 3、打开Sheet3点“图表向导”（或插入图表）按要求选择图标类型下一步按住Ctrl键依次选中数据源“日期”“货物一”“货物二”系列产生在“列”下一步图标标题输入“每周货物销售情况”图标类型输入“日期”图例右上角下一步完成点保存按钮。 4、打开Sheet4选中所有数据区域数据排序主要关键字选“计算机”次要关键字选“英语”递增存盘关闭退EXCEL。 Excel操作练习四 一、打开Excel文件夹下的excelbja.xls工作簿文件，进行如下操作： （1）将第一列的列宽设置为1.75。（1分） （2）合并及居中B2:J2单元格，合并及居中B3:J3单元格。（1分） （3）月份列中C6:C10、C11:C14、C15:C18、C19:C23、C24:C25分别合并，水平垂直居中。（1分） （4）周次列中B6:B25单元格填充蓝色。（1分） （5）B5:J5单元格填充红色。（1分） （6）B5:J25单元格区域的边框和内部水平、垂直线均设为绿色细实线。（1分） （7）保存文件。 操作步骤： 1、在第一列列标“A”上点右键列宽设置列宽为1.75 2、选中B2:J2单元格格式单元格对齐合并单元格水平对齐居中，选中B3:J3单元格格式单元格对齐合并单元格水平对齐居中。 3、分别选中C6:C10、C11:C14、C15:C18、C19:C23、C24:C2单元格格式单元格对齐合并单元格水平对齐居中垂直对齐居中。 4、选中B6:B25单元格格式单元格图案颜色蓝色。 5、选中B5:J5单元格格式单元格图案颜色红色。 6、选中B5:J25单元格格式单元格边框颜色绿色点击外边框和内部，保存退出。 二、打开Excel文件夹下的excelj