1、Excel 教程 http:/在 数 据 透 视 表 中 统 计 不 重 复 值 数 量 的 方 法在用数据透视表分析数据的过程中，有时需要统计某字段中不重复值（唯一值）的数量。例如下所示的数据源为一个随日期不断增加记录的销售流水表，其中各“分店”都有一些人员包含多条销售记录，如“姓名 4”。现在需要在数据透视表中得到各“分店”有销售记录的人员数量，即得到表中各“分店”不重复“姓名”的数量。如果直接在数据透视表中把“姓名”字段放到“数值”区域，仅能得到全部记录的计数值，而不是唯一的“姓名”数量。在这种情况下，可以用添加辅助列、使用 SQL 命令和利用 PowerPivot 的方法在数据透视表中
2、得到不重复值数量。本文以上图中的数据为例，介绍在 Excel 2010 的实现方法，供参考。方法一：添加辅助列1.在表格的右侧增加一个辅助列，并设置一个列标题，如“人员数量”，在 E4 单元格中输入公式：=IF(COUNTIF($C$4:C4,C4)=1,1,“)Excel 教程 http:/然后填充公式到最后一行。2.将数据区域转换为 Excel 表格，这样当增加记录后，数据透视表可自动扩展数据源。方法是选择数据区域中的某个单元格，如 C10 单元格，依次选择“插入表格”，弹出“插入表”对话框，单击确定。Excel 将创建一个名称为“表 1”的 Excel 表。3.创建数据透视表。选择上述表
3、格中的某个单元格，依次选择“插入数据透视表数据透视表”，弹出“创建数据透视表”对话框，设置放置数据透视表的位置后确定。将“分店”字段拖入到“行区域”，将“人员数量”字段拖入到“数值区域”。右击数据透视表中的“人员数量”字段，选择“值字段设置”，在弹出的对话框中将该字段的“值汇总方式”由“计数”改为“求和”后确定，即可得到各“分店”中唯一“姓名”的数量。方法二：使用 SQL 命令1 添加连接。Excel 教程 http:/先保存工作簿，然后选择“数据”选项卡，在“连接”组中单击“链接”，弹出“工作簿连接”对话框。在对话框中单击“添加”按钮，打开“现有连接”对话框，单击“浏览更多”按钮。在“选取数
4、据源”对话框中找到并选择当前工作簿后单击“打开”，弹出“选择表格”对话框。假如上述表格在“Sheet1”表中，选择“Sheet1”后确定。这时 Excel在工作簿中添加了一个连接，连接的名称与当前工作簿相同。2.在“工作簿连接”对话框中单击“属性”按钮，弹出“连接属性”对话框，选择“定义”选项卡，在“命令文本”中输入 SQL 命令：select distinct 姓名, 分店 from Sheet1$a3:d1000 where 分店 is not null其中“select distinct ”语句返回字段中的不重复值，“Sheet1$a3:d1000”指定表及数据区域。单击“确定”，关闭“
5、工作簿连接”对话框。3.创建数据透视表。在功能区中选择“数据”选项卡，在“获取外部数据”组中单击“现有连接”，弹出“现有Excel 教程 http:/连接”对话框。在“此工作簿中的连接”下找到上述添加的连接，本例为“2012 年销售流水”。双击该连接或选择该连接后单击“打开”按钮，弹出“导入数据”对话框，选择“数据透视表”，并设置数据透视表的存放位置后确定。在“数据透视表字段列表”中将“分店”和“姓名”两个字段分别拖到“行区域”和“数值”区域即可。另外，还可使用 Microsoft Query 来创建数据透视表，步骤如下。1.在功能区中选择“数据”选项卡，在“获取外部数据”组中单击“自其他来源
6、来自 Microsoft Query”，弹出“选择数据源”对话框，选择“Excel Files*”，单击“确定”按钮。此处默认已勾选“使用查询向导创建/编辑查询”选项2.弹出“选择工作簿”对话框，找到并选择当前工作簿后确定。这时会弹出“查询向导”对话框。如果此时 Excel 提示“数据源中没有包含可见的表格”，“可用的表和列”区域为空，可用下面的方法让可用的表显示出来：单击对话Excel 教程 http:/框中的“选项”按钮，在弹出的对话框中同时勾选“表”和“系统表”后单击“确定”。有时对话框中的两个选项已处于选中状态，但“可用的表和列”区域仍然为空，可先取消选择“系统表”单击“确定”后，再次
7、选择“系统表”并确定。由于数据在“Sheet1”工作表中，此处选择“Sheet1$”，单击 “”按钮将其添加到“查询结果中的列”区域中。接着单击三次“下一步”及“完成”按钮关闭“查询向导”，弹出“导入数据”对话框，单击“属性”按钮，弹出“连接属性”对话框，选择“定义”选项卡，在“命令文本”中输入上述 SQL 命令。以后的步骤同上，不再赘述方法三：用 PowerPivotPowerPivot for Excel 即 Microsoft SQL Server PowerPivot for Microsoft Excel，一种功能十分强大的Excel 外接程序。如果已下载并安装了 PowerPivo
8、t，可以通过创建度量值并设置数据分析表达式(DAX)，从而十分轻松地在数据透视表中统计唯一值数量。本文介绍在Windows 7+Excel 2010 中的操作步骤，旨在说明一种方法，仅供参考。Excel 教程 http:/1.将数据链接到 PowerPivot。选择数据区域中的某个单元格，在功能区中选择“PowerPivot”选项卡，在“Excel 数据”组中单击“创建链接表”。弹出“创建表”对话框，单击“确定”按钮。Excel 会自动将该区域转换成 Excel 表，并打开PowerPivot 窗口将数据链接到 PowerPivot 中，本例表的名称为“表 1”。2.创建数据透视表。选择 Po
9、werPivot 窗口界面的“主页”选项卡，在“报表”组中单击“数据透视表数据透视表”，将返回 Excel工作表界面，并弹出“创建数据透视表”对话框。根据需要选择数据透视表存放的位置后确定。3.新建度量值。度量值是数据透视表或数据透视图中的一种动态计算，它会随着所选择或筛选的数据不同而得到动态的结果，度量值只能放置到数据透视表的“数值”区域中。通过在度量值中创建数据分析表达式(DAX)，就像在 Excel 单元格中Excel 教程 http:/创建公式一样，可以实现许多功能强大的计算。新建度量值的步骤如下：选择“PowerPivot”选项卡，在“度量值”组中单击“新建度量值”。在弹出的“度量值
10、设置”对话框中，给新建的度量值设置一个名称，如“人员数量”，在“公式”下方的区域输入 DAX 公式：=COUNTROWS(DISTINCT(表 1姓名)或：=COUNTROWS(VALUES(表 1姓名)说明：DISTINCT 函数和 VALUES 函数可返回一列由包含不重复值（唯一值）组成的表。单击“确定”按钮，在数据透视表字段列表中会增加一个度量值“人员数量”。勾选“分店”和“人员数量”，即可在数据透视表中得到各“分店”不重复的“姓名”数量。4.刷新数据。当在工作表中添加或更改数据后，分别在“PowerPivot”选项卡和“数据透视表工具-设计”选项卡Excel 教程 http:/中单击“全部更新”和“刷新”，即可更新数据透视表中的数据。
