Excel数据透视表学习笔记04

最新推荐文章于 2024-07-14 16:32:48 发布

Zoey_ov

最新推荐文章于 2024-07-14 16:32:48 发布

阅读量888

点赞数 11

文章标签： excel 学习笔记

本文链接：https://blog.csdn.net/Zoey_ov/article/details/135938966

版权

本文详细介绍了如何在Excel数据透视表中进行各种计算，包括对同一字段的不同汇总方式、更改默认汇总、自定义数据显示方式、计算字段和计算项的创建与管理，以及解决数据透视表计算局限性的方法。

摘要由CSDN通过智能技术生成

七、在数据透视表中进行计算

本节介绍在不改变数据源的前提下，在数据透视表的数据区域中设置不同的数据显示方式，以及通过数据透视表现有字段重新进行组合形成新的计算字段和计算项，还可以进行计算平均单价、奖金提成、账龄分析、预算控制、存货管理等各种数据分析。

7.1 对同一字段使用多种汇总方式

只需将该字段多次拖入数据透视表的数据区中，并利用“数据透视表字段”对话框选择不同的汇总方式即可。

7.2 更改数据透视表默认的字段汇总方式

当数据列表中的某些字段存在空白单元格或文本型数值时，布局到数据透视表的数据区域中，默认的汇总方式是“计数”。逐一更改十分烦琐，可以借助其他方法快速实现更改。

方法一：（1）先通过“分列”将数据源中的文本型数据列变为数值型。

（2）将数据源中除标题行外的第一行中的空白单元格补为0，保证第一行数据中没有空白单元格和文本型数值。

（3）利用第一行数据创建数据透视表，创建完成后再更改数据源为所有数据即可。

方法二：利用VBA代码自动生成默认的字段汇总方式为“求和”的数据透视表

（1）先通过“分列”将数据源中的文本型数据列变为数值型。

（2）在数据源空白区域中绘制一个矩形，添加文字“生成数据透视表”并填充颜色。

（3）在矩形框中点击鼠标右键，指定宏，单击“新建”按钮，在出现的VBA代码窗口输入如下VBA代码（不同文件所需的VBA代码不同，按需更改）。

Sub 新建透视表()
    Dim ws As Worksheet
    Dim ptcache As PivotCache
    Dim pt As PivotTable
    Dim prange As Range
    Set ws = Sheet1
    For Each pt In Sheet2.PivotTables
        pt.TableRange2.Clear
    Next pt
    Set ptcache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=Sheet1.Range("a1").CurrentRegion.Address)
    Set pt = ptcache.CreatePivotTable(tabledestination:=Sheet2.Range("a3"), tablename:="透视表1")
    pt.ManualUpdate = True
    pt.AddFields RowFields:="项目", ColumnFields:="Data"
    For Each prange In ws.Range(ws.Cells(1, 2), ws.Cells(1, 256).End(xlToLeft))
        With pt.PivotFields(prange.Value)
            .Orientation = xlDataField
            .Name = " " & prange
            .Function = xlSum
        End With
    Next prange
    pt.ManualUpdate = False
    pt.ManualUpdate = True
End Sub

7.3 自定义数据透视表的数据显示方式

Excel允许选择更多的计算方式，比如显示数据透视表的数据区域中每项占同行或同列数据总和的百分比，或显示每个数值占综合的百分比等。

表7-1 自定义计算功能描述
选项	功能描述
普通	数据区域字段按默认方式计算
差异	数据区域字段与指定的基本字段和基本项的差值
百分比	数据区域显示为基本字段和基本项的百分比
差异百分比	数据区域字段显示为与基本字段项的差异百分比
按某一字段汇总	数据区域字段显示为基本字段项的汇总
占同行数据总和的百分比	数据区域字段显示为每个数据项占该行所有项汇总和的百分比
占同列数据总和的百分比	数据区域字段显示为每个数据项占该列所有项汇总和的百分比
占总和的百分比	数据区域字段显示为每个数据项占该字段所有项总和的百分比
指数	使用公式：（（单元格的值）（总体汇总之和））/（（行汇总）（列汇总））

7.3.1“差异”数据显示方式示例

鼠标单击“求和项：金额”字段----值字段设置----值显示方式，下拉列表中选中“差异”显示方式，“基本字段”选择“费用属性”，“基本项”选择“实际发生额”，差异计算就会在“实际发生额”字段区域显示“预算额”-“实际发生额”。若“基本项”选择“预算额”，差异计算就会在“预算额”字段区域显示“实际发生额”-“预算额”。

其他数据显示方式的设置方式与“差异”数据显示方式类似。