数据透视表系列教程.docx

数据透视表系列教程.docx

《数据透视表系列教程.docx》由会员分享，可在线阅读，更多相关《数据透视表系列教程.docx（34页珍藏版）》请在冰豆网上搜索。

数据透视表系列教程

数据透视表系列教程

内容提要：

本文讲解三个知识点：

第一，什么是数据透视表，第二，数据透视表的作用，第三，以工作表数据制作数据透视表的注意事项

　　数据透视表是excel中功能最大、使用最灵活、操作最简单的工具。

使用数据透视表不必输入复杂的公式和函数，仅仅通过向导就可以创建一个交互式表格，从而自动提取、组织和汇总数据。

如果将数据透视表和函数结合使用，更能创建出满足各种需求的报表。

　　什么是数据透视表呢?

数据透视表就是一种交互式报表，可以快速分类汇总大量的数据，并可以随时选择页、行和列中的不同元素，快速查看源数据的不同统计结果，同时还可以随意显示和打印出用户感兴趣区域的明细数据，使分析、组织复杂的数据更加快捷有效。

　　数据透视表的作用就是将用户从创建复制公式、使用各种函数的烦琐工作中解脱出来，使其迅速而准确的对数据进行处理分析，制作出漂亮的报告和图表。

　　以工作表数据制作数据透视表的注意事项有以下七点：

　　以工作表数据制作数据透视表，这些工作表数据必须是一个数据清单。

所谓数据清单，就是在工作表数据区域的顶端行为字段名称（标题），以后各行为数据（记录），并且各列只包含一种类型数据的数据区域。

这种结构的数据区域就相当于一个保存在工作表的数据库。

　　第一，数据区域的顶端行为字段名称（标题）。

　　第二，避免在数据清单中存在有空行和空列。

这里需指明以下，所谓空行，是指在某行的各列中没有任何数据，如果某行的某些列没有数据，但其他列有数据，那么该行就不是空行。

同样，空列也是如此。

　　第三，各列只包含一种类型数据。

　　第四，避免在数据清单中出现合并单元格。

　　第五，避免在单元格的开始和末尾输入空格。

　　第六，尽量避免在一张工作表中建立多个数据清单，每张工作表最好仅使用一个数据清单。

　　第七，工作表的数据清单应与其他数据之间至少留出一个空列和一个空行，以便于检测和选定数据清单。

　　在制作数据透视表之前，应该按照以上7点来检查数据区域，如果不满足上面的要求，需要先进行整理工作表数据从而使之规范。

　　在下面一篇文章，我们将以实例介绍如何整理数据清单：

删除数据区域内的所有空行的4种方法。

内容提要：

删除数据区域内的所有空行的四种方法：

排序、高级筛选、自动筛选、VBA编写。

删除数据区域内所有空行的方法有多种，比如排序、高级筛选、自动筛选、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）.Delete

   Nextr

   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）.Delete

   Nextr

   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"。

内容提要：

本文是数据透视表系列教程六，主要介绍为了能够利用数据透视表进行汇总分析，将存在合并和空行的单元格进行处理的方法。

下面所示的数据报表，这个表格看起来很直观，但它不是一个数据清单，因为在A列商品类别中，存在合并单元格，而且某些行没有相应的数据。

　　我们为了能够利用数据透视表进行汇总分析，需要将下面的工作表进行整理。

　　具体操作步骤如下介绍：

　　第一步，选中A列所有的合并单元格，即A1：

A15区域，单击工具栏中的“合并及居中”按钮，就可以取消所有的合并单元格。

　　第二步，再次选中A1：

A15区域，按下F5键，打开“定位”对话框，单击“定位条件”，选中“空值”，就可以一次性选中A列数据区域的所有空白单元格。

　　第三步，在选择这些空白单元格的状态下，直接输入“=A2”，然后按下“CTRL+ENTER”键，即可迅速的将A列数据区域的所有空白单元格填充为与上一个单元格相同的数据。

　　第四步，选中A列的A1：

A15数据区域，按下CTRL+C键复制，然后单击“编辑”下的“选择性粘贴”，选中“数值”单选按钮，单击确定按钮。

这样就把A列的所有公式转换为结果了。

这样处理的好处是防止以后在进行数据处理时损坏公式而导致无法在A列得到正确的数据。

内容提要：

本文是数据透视表系列教程七，主要介绍从外部导入数据中含有多个空格的字符串文本转换为真正的数字的方法，因为只有真正的数字才能正确参与计算。

这些多余的空格会影响数据的分析和处理。

有时从外部导入数据进来，比如从SQLServer数据库导入数据时，如果字符串文本前后存在空格，尤其是字符串文本后有多个空格，尽管在表面上与文本前后没有什么不同，但这些空格的存在会影响数据的分析和处理。

　　TRIM函数只能去掉删除单元格数据前后的空格，已经删除字符串中间的多余空格，如果字符串中间有很多个空格，那么删除空格后总会保留一个空格，而不会一个不剩的全部删除。

　　比如下表的数据，在制作数据透视表之前，必须先将B列的数据利用公式进行规范化处理，否则是不能正确求和的。

　　IT部落窝小编的思路是利用FIND函数、MID函数、LEFT函数从数据字符串中提取出真正的数字。

我们在C2单元格输入公式：

=LEFT（MID（B2,FIND（"",B2）,9999）,FIND（"",MID（B2,FIND（"",B2）,9999））-1）*1，然后向下复制公式即可得到真正的数字。

　　公式分析：

这个公式比较复杂，思路是先利用FIND函数确定B2单元格字符串前面的特殊字符在字符串中的位置，然后利用MID函数提取出包括后面特殊字符在内的字符串，再利用FIND函数确定B2单元格字符串后面的特殊字符在字符串中的位置，最后，利用LEFT函数提取出中间的数字连带。

　　如果对公式不是特别熟悉，也可以尝试使用查找和替换工具进行操作，思路是先查找单元格数据前面的特殊字符，再查找单元格数据后面的特殊字符。

经过两次查找替换后，就可以得到真正的数字。

内容提要：

本文是数据透视表系列教程八，讲解在制作数据透视表之前，非法日期转换为合法日期的方法。

非法日期的存在，直接影响到的进一步分析，尤其是无法对日期进行分组。

因此，在制作数据透视表之前，必须将非法日期转换为合法日期。

　　非法日期有两种，一种是输入格式问题，如2008.05.26或20080514，就是非法的日期，前者是文本，后者是数字，它们都不是真正的日期。

数字形式的日期更多的是从数据库导入的数据，另外一种非法日期是根本不存在的日期，比如2008-2-30。

因为2月份就没有30号。

　　小编接下来讲解两种查找并修改非法日期的方法。

工作表如下图所示。

　　第一种方法：

使用格式查找错误非法的日期：

在B2输入公式：

=IF（ISNUMBER（A2）,"","错"），向下复制就可以知道哪些单元格的日期是非法错误的。

　　第二种方法：

使用数据有效性和圈释无效数据来查找非法日期。

　　第一步，选中A列的数据区域。

　　第二步，单击“数据”——“有效性”命令，切换到“设置”选项卡，在“允许”下拉列表中选择“日期”数据，在“数据”下拉列表选择“介于”选项，在“开始日期”和“结束日期”分别输入：

2008-1-1和2008-12-31，确定。

　　第三步，单击“视图”——“工具栏”——“公式审核”命令，单击公式审核工具栏中的“圈释无效数据”，即可立即圈释出所有非法日期，然后进行修改为合法日期后，该单元格的红色标识会自动消失。

内容提要：

本文是数据透视表系列教程九，介绍将二维报表整理为数据清单的方法。

下面1图所示的二维报表数据，是我们实际工作中常见到的。

这种报表看起来整齐清楚，就算是数据透视表，然而它并不是真正的数据透视表，无法像数据透视表一样随意进行行列转换并分析数据。

因为下面这种表格中含有合并单元格，它也不是最原始的数据清单。

为了能够进行各种各样的透视分析，我们需要将其转换为真正的数据清单，如2图所示。

　　这个问题有多种方法可以解决，无论采用哪种方法，首先必须将合并单元格取消，并填充为上一个单元格（行）或前一个单元格（列）的数据，具体转换方法可以参考前面的数据透视表系列教程。

取消合并单元格并填充相应数据后，就可以进行数据表格转换了。

　　一个最简单的方法就是分别复制粘贴各列的数据到新的数据区域中，需要特别注意准确确定该数据对应的地区、城市、成色和产品。

　　如果数据量很大，可以使用公式或者VBA来解决。

　　下面是利用VBA解决上述问题的程序代码。

假定取消合并单元格并填充相应数据后表格数据保存在工作表“取消合并单元格”中。

下面是相关的代码：

PublicSubDataList（）

   DimmyArrayAsVariant

   DimnAsLong,mAsInteger,iAsLong,kAsLong

   Dimws0AsWorksheet

   DimwsNewAsWorksheet

   myArray=Array（"地区","城市","成色","产品","销售数量"）

   Setws0=Worksheets（"取消合并单元格"）

   n=ws0.Range（"A65536"）.End（xlUp）.Row-2

   m=ws0.Range（"IV3"）.End（xlToLeft）.Column-2

   ReDimDistrict（1Ton）AsString,Province（1Ton）AsString

   Fori=1Ton

       District（i）=ws0.Range（"A"&i+2）

       Province（i）=ws0.Range（"B"&i+2）

   Nexti

   OnErrorResumeNext

   Application.DisplayAlerts=False

   Worksheets（"数据清单"）.Delete

   Application.DisplayAlerts=False

   OnErrorGoTo0

   SetwsNew=Worksheets.Add

   WithwsNew

       .Name="数据清单"

       .Range（"A1:

E1"）=myArray

       Forj=1Tom

           Fori=1Ton

               .Cells（（j-1）*n+i+1,1）=District（i）

               .Cells（（j-1）*n+i+1,2）=Province（i）

               .Cells（（j-1）*n+i+1,3）=ws0.Cells（1,j+2）

               .Cells（（j-1）*n+i+1,4）=ws0.Cells（2,j+2）

               .Cells（（j-1）*n+i+1,5）=ws0.Cells（i+2,j+2）

           Nexti

       Nextj

   EndWith

   Setws0=Nothing

   SetwsNew=Nothing

EndSub

　　只要运行上面代码，就可以迅速的将报表数据转换为数据清单，并保存在一个新建的工作表“数据清单”中。

如下图2所示。

　　现在就可以利用整理好的“数据清单”制作数据透视表了。

下图3所示的就是利用整理好的数据清单制作的数据透视表的一种报表结构，利用数据透视表可以对数据进行各种统计分析，制作各种统计报表。

内容提要：

本文是数据透视表系列教程十，介绍一个更为普遍的二维报表整理为数据清单的方法。

下面1图所示的二维报表数据。

这种表格设计的初衷是为了方便输入数据，但是却为数据汇总和分析造成了麻烦。

例如，要统计办公室的复印纸的耗量和费用，就比较麻烦。

对于这样的表格，最后将其设计为日记流水账的形式，如图2所示。

尽管在输入数据时有些麻烦，但统计汇总分析就很方便。

　　那么如何将图1所示的二维表格转换为图2所示的规范表格呢，利用函数也是很复杂的，可以利用VBA编制程序比较方便。

下面就是相关的VBA程序代码。

PublicSubDataList（）

   DimmyArrayAsVariant

   DimnAsLong,iAsLong,kAsLong,jAsLong

   Dimws0AsWorksheet

   DimwsNewAsWorksheet

   myArray=Array（"日期","材料","单位","部门","数量","金额"）

   Setws0=Worksheets（"不科学表格"）

   n=ws0.Range（"A65536"）.End（xlUp）.Row-2

   OnErrorResumeNext

   Application.DisplayAlerts=False

   Worksheets（"数据清单"）.Delete

   Application.DisplayAlerts=False

   OnErrorGoTo0

   SetwsNew=Worksheets.Add

   WithwsNew

       .Name="数据清单"

       .Range（"A1:

F1"）=myArray

       k=1

       Forj=4To8Step2

           Fori=1Ton

               Ifws0.Cells（i+2,j）<>""Then

                   .Cells（k+1,1）=Format（ws0.Cells（i+2,1）,"yyyy-m-d"）

                   .Cells（k+1,2）=ws0.Cells（i+2,2）

                   .Cells（k+1,3）=ws0.Cells（i+2,3）

                   .Cells（k+1,4）=ws0.Cells（1,j）

                   .Cells（k+1,5）=ws0.Cells（i+2,j）

                   .Cells（k+1,6）=ws0.Cells（i+2,j+1）

                   k=k+1

               EndIf

           Nexti

       Nextj

   EndWith

   Setws0=Nothing

   SetwsNew=Nothing

EndSub

　　只要运行上面代码，就可以迅速的将二维报表数据转换为数据清单。

现在就可以利用整理好的“数据清单”制作数据透视表。

内容提要：

本文是数据透视表系列教程十一，通过一个实例来讲解利用数据透视表向导制作数据透视表的方法。

本文以一个实例（如下图所示）来讲解利用数据透视表向导制作数据透视表的具体方法。

制作数据透视表必须保证数据源是一个数据清单。

　　第一步，单击数据清单中的任意非空单元格，单击菜单“数据”——“数据透视表和数据透视图”命令，打开“数据透视表和数据透视图向导——3步骤之1”对话框。

　　第二步，在数据源类型下面，选用默认选项，“MicrosoftOfficeExcel数据列表或数据库”。

在