首页 > 软件操作教程 > 办公软件 > Excel
题目内容

在Excel表格数据透视表中统计不重复值数量的方法

题目答案

方法一:添加辅助列

1、在表格的右侧增加一个辅助列,并设置一个列标题,如“人员数量”,在E4单元格中输入公式:

=IF(COUNTIF($C$4:C4,C4)=1,1,"")

然后填充公式到最后一行。

image.png

2、将数据区域转换为Excel 表格,这样当增加记录后,数据透视表可自动扩展数据源。方法是选择数据区域中的某个单元格,如C10单元格,依次选择“插入→表格”,弹出“插入表”对话框,单击确定。Excel将创建一个名称为“表1”的Excel表。

3、创建数据透视表。

选择上述表格中的某个单元格,依次选择“插入→数据透视表→数据透视表”,弹出“创建数据透视表”对话框,设置放置数据透视表的位置后确定。

image.png

将“分店”字段拖入到“行区域”,将“人员数量”字段拖入到“数值区域”。

右击数据透视表中的“人员数量”字段,选择“值字段设置”,在弹出的对话框中将该字段的“值汇总方式”由“计数”改为“求和”后确定,即可得到各“分店”中唯一“姓名”的数量。

image.png

方法二:使用SQL命令

1、添加连接。

先保存工作簿,然后选择“数据”选项卡,在“连接”组中单击“链接”,弹出“工作簿连接”对话框。在对话框中单击“添加”按钮,打开“现有连接”对话框,单击“浏览更多”按钮。

image.png

在“选取数据源”对话框中找到并选择当前工作簿后单击“打开”,弹出“选择表格”对话框。假如上述表格在“Sheet1”表中,选择“Sheet1”后确定。这时Excel在工作簿中添加了一个连接,连接的名称与当前工作簿相同。

image.png

2、在“工作簿连接”对话框中单击“属性”按钮,弹出“连接属性”对话框,选择“定义”选项卡,在“命令文本”中输入SQL命令:

select distinct 姓名, 分店 from [Sheet1$a3:d1000] where 分店 is not null

image.png

其中“select distinct ”语句返回字段中的不重复值,“[Sheet1$a3:d1000]”指定表及数据区域。单击“确定”,关闭“工作簿连接”对话框。

3、创建数据透视表。在功能区中选择“数据”选项卡,在“获取外部数据”组中单击“现有连接”,弹出“现有连接”对话框。在“此工作簿中的连接”下找到上述添加的连接,本例为“2012年销售流水”。

image.png

双击该连接或选择该连接后单击“打开”按钮,弹出“导入数据”对话框,选择“数据透视表”,并设置数据透视表的存放位置后确定。

image.png在Excel表格数据透视表中统计不重复值数量的方法

在“数据透视表字段列表”中将“分店”和“姓名”两个字段分别拖到“行区域”和“数值”区域即可。

image.png在Excel表格数据透视表中统计不重复值数量的方法

另外,还可使用Microsoft Query来创建数据透视表,步骤如下。

1、在功能区中选择“数据”选项卡,在“获取外部数据”组中单击“自其他来源→来自Microsoft Query”,弹出“选择数据源”对话框,选择“Excel Files*”,单击“确定”按钮。此处默认已勾选“使用‘查询向导’创建/编辑查询”选项。

image.png

2、弹出“选择工作簿”对话框,找到并选择当前工作簿后确定。

image.png

这时会弹出“查询向导”对话框。如果此时Excel提示“数据源中没有包含可见的表格”,“可用的表和列”区域为空,可用下面的方法让可用的表显示出来:单击对话框中的“选项”按钮,在弹出的对话框中同时勾选“表”和“系统表”后单击“确定”。有时对话框中的两个选项已处于选中状态,但“可用的表和列”区域仍然为空,可先取消选择“系统表”单击“确定”后,再次选择“系统表”并确定。由于数据在“Sheet1”工作表中,此处选择“Sheet1$”,单击“>”按钮将其添加到“查询结果中的列”区域中。

image.png

接着单击三次“下一步”及“完成”按钮关闭“查询向导”,弹出“导入数据”对话框,单击“属性”按钮,弹出“连接属性”对话框,选择“定义”选项卡,在“命令文本”中输入上述SQL命令。

方法三:用PowerPivot

PowerPivot for Excel即Microsoft SQL Server PowerPivot for Microsoft Excel,一种功能十分强大的Excel外接程序。如果已下载并安装了PowerPivot,可以通过创建度量值并设置数据分析表达式(DAX),从而十分轻松地在数据透视表中统计唯一值数量。本文介绍在Windows 7+Excel 2010中的操作步骤,旨在说明一种方法,仅供参考。

1、将数据链接到PowerPivot。

image.png在Excel表格数据透视表中统计不重复值数量的方法

选择数据区域中的某个单元格,在功能区中选择“PowerPivot”选项卡,在“Excel数据”组中单击“创建链接表”。弹出“创建表”对话框,单击“确定”按钮。

Excel会自动将该区域转换成Excel表,并打开PowerPivot窗口将数据链接到PowerPivot中,本例表的名称为“表1”。

2、创建数据透视表。

选择PowerPivot窗口界面的“主页”选项卡,在“报表”组中单击“数据透视表→数据透视表”,将返回Excel工作表界面,并弹出“创建数据透视表”对话框。

image.png在Excel表格数据透视表中统计不重复值数量的方法

根据需要选择数据透视表存放的位置后确定。

3、新建度量值。

度量值是数据透视表或数据透视图中的一种动态计算,它会随着所选择或筛选的数据不同而得到动态的结果,度量值只能放置到数据透视表的“数值”区域中。通过在度量值中创建数据分析表达式(DAX),就像在Excel单元格中创建公式一样,可以实现许多功能强大的计算。新建度量值的步骤如下:

选择“PowerPivot”选项卡,在“度量值”组中单击“新建度量值”。在弹出的“度量值设置”对话框中,给新建的度量值设置一个名称,如“人员数量”,在“公式”下方的区域输入DAX公式:

=COUNTROWS(DISTINCT('表1'[姓名]))

或:

=COUNTROWS(VALUES('表1'[姓名]))

image.png在Excel表格数据透视表中统计不重复值数量的方法

说明:DISTINCT函数和VALUES函数可返回一列由包含不重复值(唯一值)组成的表。

单击“确定”按钮,在数据透视表字段列表中会增加一个度量值“人员数量”。勾选“分店”和“人员数量”,即可在数据透视表中得到各“分店”不重复的“姓名”数量。

image.png在Excel表格数据透视表中统计不重复值数量的方法

4、刷新数据。

当在工作表中添加或更改数据后,分别在“PowerPivot”选项卡和“数据透视表工具-设计”选项卡中单击“全部更新”和“刷新”,即可更新数据透视表中的数据。


网友评论(共0条评论)

请自觉遵守互联网相关政策法规,评论内容只代表网友观点!

最新评论

点击加载更多评论>>

软件操作 新人注册送三重礼

已有 22658 名学员学习以下课程通过考试

相关视频试题

最需教育客户端 软件问题一手掌握

去 App Store 免费下载 iOS 客户端