Excel数据透视表如何做的15个设置办法，含非重复计数与百分比统计及四个区域间的用途

在 Excel 中，数据透视表用于做各项统计，例如统计每个部门的员工占总员工的百分比、每种产品的销量占总销量的百分比、每种产品的某个地区的销量占所有地区销量的百比分等。它的计算类有求和、求平均值、计数、求最大最小值、求标准差和非重复计数等。

Excel数据透视表怎么做？它的创建方法有两种，一种为创建空数据透视表，另一种为创建 Excel 推荐的数据透视表，即按某些项统计好的数据透视表。如果是创建空数据透视表，需要自己统计要统计的项；完成统计要知道四个区域间的用途、计算类型的选择和值显示方式的选择等；文章列举了非重复计数和百分比两个统计实例。

一、Excel数据透视表怎么创建

（一）创建空数据透视表

（1）把数据透视表创建到另一个工作表

1、选中要创建数据透视表的表格其中一个单元格（如 A2），选择“插入”选项卡，单击屏幕左上角的“数据透视表”，打开“创建数据透视表”窗口，“表/区域”已自动填好了对表格的引用（即 工资表!$A$1:$F$11）；“选择放置数据透视表的位置”选择“新工作表”，勾选“将此数据添加到数据模型”，单击“确定”，开始创建数据透视表，所用时间长短取决表格大小；数据透视表创建好后自动切换到该工作表，默认不勾选任何字段，勾选“部门”和“应发工资”，则统计出各部门的“应发工资”；操作过程步骤，如图1所示：

图1

2、说明：

A、如果不是把整个表格都创建数据透视表，只把部分单元格或列创建，可以只框选它们；例如只把 A、B 和 C 列创建数据透视表，只选中这三列即可。

B、“使用外部数据源”可以把另一个 Excel 文档或数据库作为创建数据透视表的数据源，如果要使用数据库作为数据源，需要建立与数据库的连接。

C、“将此数据添加到数据模型”的作用是非重复计数，也就是重复的项只统计一次，如果表格有重复数据，可以勾它；如果不勾选，在值列表中将没有“非重复计数”选项。

提示：打开“创建数据透视表”窗口也可以按快捷键 Alt + N + V，按键方法为：按住 Alt，依次按一次 N 和 V。

（2）把数据透视表创建到现有工作表

1、假如只把 B、C、D 三列创建数据透视表。单击列号 B，选中 B 列，按住 Shift，再单击列号 D，选中 B、C、D 三列，按住 Alt，分别按一次 N 和 V，打开“创建数据透视表”窗口，选择“现有工作表”，单击“位置”右边的输入框把光标定位到那里，单击 H1，则 Sheet1!$H$1 自动填充到里面，表示把数据透视表创建到 H1 的位置，单击“确定”，成功创建数据透视表，勾选“部门”和“基本工资”，它们作一列显示，把“计数项：基本工资”拖到“Σ 值”列表，它们显示为一行；操作过程步骤，如图2所示：

图2

2、如果只把几列创建数据透视表，这几列必须连续，否则无法创建，不连续的列可以把它们移到一起，按住 Shift，把鼠标移到要移动列的左边线或右边线上，鼠标变为带小加号图标后，按住左键移动即可。

（二）创建“推荐的数据透视表”

1、选中要创建数据透视表的其中一单元格，选择“插入”选项卡，单击屏幕左上角的“推荐的数据透视表”，打开“推荐的数据透视表”窗口，选择一种数据透视表样式（如“求和项：应发工资（元），按部门”），单击“确定”，则创建好所选样式的数据透视表，已经统计好每个部门的“应发工资”；操作过程步骤，如图3所示：

图3

2、说明：“推荐的数据透视表”其实是统计好了的一项或几项的表格，例如演示中选择的“求和项：应发工资（元），按部门”就是统计好了每个部门的应发工资。如果“推荐的数据透视表”有现存的样式，选择一个创建数据透视表速度比较快。

提示：创建“推荐的数据透视表”也可以用快捷键 Alt + N + SP，按键方法为：按住 Alt，依次按一次 N、S 和 P。

二、Excel数据透视表，怎么更改数据源和源表格修改数据后怎么更新

（一）怎么更改数据源

假如要把数据源由整个表格改为 A 列到 D 列。单击数据透视表其中一个单元格（如 A3）以便显示“分析”选项卡，选择“分析”，单击“更改数据源”图标，Excel 自动切换到数据源表格“工资表”，框选 A 到 D 列，单击“确定”，则数据源改为 A 列至 D 列，“加班费”列和“应发工资”列已经没有了；操作过程步骤，如图4所示：

图4

（二）源表格修改数据后怎么更新

1、以把源表格中两条“李秀丽”的记录删除一条为例。右键其中一条“李秀丽”记录所在行行号 2，在弹出的菜单中选择“删除”，则删除其中一条重复记录；单击“数据透视表”标签切换到该工作表，选中“李秀丽”对应的“应发工资”单元格 B5，选择“分析”选项卡，单击“更新”，则“李秀丽”对应的“应发工资”减半，说明数据透视表已经更新；操作过程步骤，如图5所示：

图5

提示：如果在表格后面添加了一条记录，需更新数据源，因为所添加的记录不在已选择表格的范围内。

2、如果打开文档时要自动刷新数据，需要设置，方法为：右键 A3 单元格（即“行标签”），在弹出的菜单中选择“数据透视表选项”，打开“数据透视表选项”窗口，选择“数据”选项卡，勾选“打开文件时刷新数据”，单击“确定”，则每次打开文档会自动刷新数据；操作过程步骤，如图6所示：

图6

提示：打开“数据透视表选项”窗口也可以用快捷键 Shift + F10 + O，按键方法为：按住 Shift，依次按一次 F10 和 O。

三、Excel数据透视表的四个区域间

（一）区域间“行”

区域间“行”用于把字段显示到行，如果是“文本”字段，只要勾选，它会自动显示到区域间“行”；如果是数值字段，需要把它拖到区域间“行”才会显示到表格的行。例如勾选“姓名”，它自动显示到区域间“行”，同时显示到表格的行；在区域间“行”，“部门”显示在“姓名”的上面，在表格中也是一样的次序显示，把“姓名”拖到“部门”的上面，表格中的次序也交换了；演示如图7所示：

图7

（二）区域间“列”

区域间“列”用于把字段显示到列，只需把要显示到列的字段拖到区域间“列”，该字段的每一项显示到一列。例如把“姓名”拖到区域间“列”，每个员工的姓名显示到了一列；操作过程步骤，如图8所示：

图8

（三）区域间“筛选器”

1、筛选器用于筛选记录，只要把字段拖到区域间“筛选器”，则以该字段筛选记录。例如把“部门”拖到“筛选器”，则 A2 自动出现“部门”，B2 自动出现 All，单击 All，在弹出的菜单中单击加号（+）展开，单击 All 前的小方框取消选择所有，再勾选“财务部”，则筛选出“财务部”的所有员工；操作过程步骤，如图9所示：

图9

提示：如果把字段（如“部门”）从筛选器中删除了，可以从 A4 中的“行标签”筛选或重新把字段拖到筛选器。

2、“快速浏览”也可以把字段添加到筛选器。右键要快速浏览的单元格，如 A5（或单击在 A5 右边的放大镜图标），展开“浏览”窗口，选择“姓名”，再单击“钻取到 姓名”，则 A5 中的“财务部”被选为筛选，“部门”字段同时被添加到“筛选器”；操作过程步骤，如图10所示：

图10

提示：没有勾选的字段才可以被钻取到。

“快速浏览”后的恢复。把“部门”从“筛选器”拖到“行”，单击 A4 中的筛选图标，在弹出的菜单中单击“全选”勾选所有部门，则显示出所有部门及属于它们的员工；操作过程步骤，如图11所示：

图11

3、双击“值”列完整显示一条记录。假如要完全显示员工“李秀丽”的每个字段的信息；双击“李秀丽”对应的“B5 中的应发工资”，则Excel 开始创建一个新的工作表（即 Sheet1），在里面显示了“李秀丽”的所有信息，由于有两条“李秀丽”的记录，因此显示了两条记录；操作过程步骤，如图12所示：

图12

四、Excel数据透视表怎么做各项统计

（一）求和与求平均值

1、假如要把求和改为求平均值。双击要更改计算类型的标题（如 B3 单元格），打开“值字段设置”窗口，选择“计算类型”下的“平均值”，把“自定义名称”右边的“以下项目的”去掉，单击“确定”，则统计出各部门的平均工资；操作过程步骤，如图13所示：

图13

2、在“值字段设置”窗口中，“计算类型”有多种，需要修改为什么类型，选择即可。

（二）非重复计数统计

假如要对员工“姓名”作非重复计数统计。把“姓名”字段拖到“Σ 值”列表中，自动统计出了每个员工的个数，因为“计算类型”默认选择了“计数”，员工“李秀丽”统计结果为 2；双击“计数”标题所在的单元格 B3，打开“值字段设置”窗口，“计算类型”选择“非重复计数”，单击“确定”，则重复的项只统计一次，员工“李秀丽”统计的结果由 2 变为 1；操作过程步骤，如图14所示：

图14

（三）百分比统计

假如要统计各部门与每个员工的工资占总工资的百分比。把“应发工资”字段拖到“Σ 值”列表中，双击它的标题所在的 C3 单元格，打开“值字段设置”窗口，把名称改为“百分比”，单击“确定”，右键有数值的其中一个单元格，如 C4，在弹出的菜单中依次选择“值显示方式”→ 总计的百分比，则统计出满足条件的百分比；操作过程步骤，如图15所示：

图15

……