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

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

《用excel中数据透视表分类求数据平均值方法.docx》由会员分享，可在线阅读，更多相关《用excel中数据透视表分类求数据平均值方法.docx（19页珍藏版）》请在冰豆网上搜索。

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

2019年用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代码

编写下面一段出现，运行这段程序，就可以迅速的将原始数据区域内的所有空行删除。

SubDeleteEmptyRows（）

DimLastRowAsLong

DimrAsLong

LastRow=ActiveSheet.UsedRange.Row-1+ActiveSheet.UsedRange.Rows.Count

Application.ScreenUpdating=False

Forr=LastRowTo1Step-1

IfApplication.WorksheetFunction.CountA（Rows（r））=0ThenRows（r）.DeleteNextr

Application.ScreenUpdating=True

EndSub

在数据透视表系列教程二，讲解了一次性的删除数据区域内的所有空行的几种方法。

制作数据透视表之前必须把工作表中的空行空列都需要删除，才能避免错误。

本文就讲解一次性的删除数据区域内的所有空列的两种方法。

第一种一次性删除数据区域内的所有空列的方法是借助辅助列和公式来删除空列。

这种方法是设计一个辅助列，并利用COUNTA函数统计各列不为空的单元格个数（如果为空列，那么不为空单元格的个数就是0），然后用一个常量除以统计的单元格个数。

当某列为空列时，就会出现错误值“#DIV/0！

”，这样，就可以利用定位工具定位到所有出现错误值的单元格，删除出现错误值单元格所在的整列。

实例如下图所示：

具体操作步骤如下：

第一步，在数据区域下的任意一行，比如A8单元格输入公式：

=1/COUNTA（A1:

A6），然后向右填充复制到H8，得到计算结果，可以看到D、F两行空列都是错误公式。

第二步，单击任意数据区域的单元格，按下F5键，弹出“定位”对话框，单击“定位条件”，选择“公式”选项组下面的“错误”复选框，确定。

就可以将所有错误公式的列选中。

第三步，单击“编辑”——“删除”——“整列”。

第四步，删除辅助行。

第二种一次性的删除数据区域内的所有空列的方法是使用VBA代码。

下面是编写的一段程序，只要运行这段程序，就可以迅速将所有空列删除。

代码如下：

SubDeleteEmptyColumns（）

DimLastColAsLong,rAsLong

LastCol=ActiveSheet.UsedRange.Column-1+

ActiveSheet.UsedRange.Columns.Count

Application.ScreenUpdating=False

Forr=LastColTo1Step-1

IfApplication.WorksheetFunction.CountA（Columns（r））=0ThenColumns（r）.DeleteNextr

Application.ScreenUpdating=True

EndSub

数据区域的所有小计行会在一定程度上影响数据透视表的统计汇总结果。

尽管可以不在数据透视表中显示这些小计，但这些小计项目的存在终究是多余的。

实际上，数据透视表会自动添加各个类别项目的小计。

如何一次性快速的删除工作表中的小计行和全年的合计行呢，工作表如下图所示。

第一步，将光标定位在工作表数据区域，按下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

篇二：

如何运用EXCEL中数据透视表进行多重合并计算数据

（三）案例学习：

合并和分析数据集：

管理人员转发给你的电子表格如下图所示，并且要求提取各季度每个型号两年的平均收入，管理人员需要这些图形用于如开一个将在15分钟之后开始的会议，因此，你几乎没有时间组织和汇总该数据

图表1

如果这只是一个需要快速完成的一次性分析，那么可以使用数据透视表，要创建数据这视表请遵循以下步骤：

1、数据—数据透视表与数据透视图，选择“多重合并计算数据区域”，单击“一下步”

2、选择“自定义页字段”，单击“下一步”

3、添加一个数据区域，然后选择数字1旁边单选按钮以激活“2字段1”输入枢，在该输入框中输入20XX，对话框如下图所示：

图表2

4、对于每一个数据集重复第3步，直到添加了所有数据区域为至。

务必在FY20XX下的所有数据集中输入20XX，在FY20XX下的所有数据集中输入20XX