提示:其它语言是由 Google 机器翻译的。 你可以访问 English 版本。
登录
x
or
x
x
马上登记
x

or

如何统计数据透视表中的唯一值?

默认情况下,当我们根据包含一些重复值的数据范围创建数据透视表时,所有记录也将被计算在内,但有时,我们只想基于一列计算唯一值以获得正确的数据截图结果。 在本文中,我将讨论如何计算数据透视表中的唯一值。

使用帮助列计算数据透视表中的唯一值

使用Excel 2013及更高版本中的“值字段设置”计算数据透视表中的唯一值

关于数据透视表的更多提示......


按透视表中的会计年度,半年,周数或其他特定日期分组日期

通常,您可以快速按月,年,季度对数据透视表进行分组,但有时,您可能希望根据会计年度,半年和其他特定日期对数据进行分组。 在这种情况下, Kutools for Excel's 数据透视表特殊时间分组 实用程序扩展了原始分组功能并支持更多日期分组。 点击下载Kutools for Excel!


使用帮助列计算数据透视表中的唯一值

在Excel中,您需要创建一个帮助列来标识唯一值,请执行以下步骤:

1。 在除数据外的新栏目中,请输入此公式 =IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1) 进入单元格C2,然后将填充手柄拖动到要应用此公式的范围单元格,并将标识唯一值,如下面的屏幕截图所示:

2。 现在,您可以创建一个数据透视表。 选择包括助手列的数据范围,然后单击 插页 > 数据透视表 > 数据透视表,看截图:

3。 然后在 创建数据透视表 对话框中,选择要放置数据透视表的新工作表或现有工作表,请参阅截图:

4。 点击 OK,然后拖动 字段 行标签 框,然后拖动 帮手 字段 价值 框,您将获得以下数据透视表,它只计算唯一值。


使用Excel 2013及更高版本中的“值字段设置”计算数据透视表中的唯一值

在Excel 2013和更高版本中,一个新的 不同的计数 功能已添加到数据透视表中,您可以应用此功能快速轻松地解决此任务。

1。 选择您的数据范围,然后单击 插页 > 数据透视表创建数据透视表 对话框中,选择要放置数据透视表的新工作表或现有工作表,然后选中 将此数据添加到数据模型 复选框,见截图:

2。 然后在 数据透视表字段 窗格中拖动 领域 框,然后拖动 名字 领域 价值 框中,看截图:

3. 然后点击 名字的数量 下拉列表,选择 值字段设置,看截图:

4。 在 值字段设置 对话框中,单击 总结价值观 选项卡,然后滚动以单击 不同的计数 选项,看截图:

5。 然后点击 OK,您将获得只计算唯一值的数据透视表。

  • 注意:如果你检查 将此数据添加到数据模型 在选项 创建数据透视表 对话框, 计算字段 功能将被禁用。

更多相关的数据透视表文章:

  • 将相同的筛选器应用于多个数据透视表
  • 有时,您可以基于相同的数据源创建多个数据透视表,现在您可以过滤一个数据透视表,并希望以相同的方式过滤其他数据透视表,这意味着您希望一次更改多个数据透视表过滤器Excel中。 在本文中,我将讨论Excel 2010及更高版本中新功能Slicer的使用。
  • 更新Excel中的数据透视表范围
  • 在Excel中,当您在数据区域中删除或添加行或列时,相对数据透视表不会同时更新。 现在本教程将告诉您如何在数据表的行或列更改时更新数据透视表。
  • 在Excel中隐藏数据透视表中的空白行
  • 我们知道,数据透视表很方便我们分析Excel中的数据,但有时候,下面的截图显示的行中会出现一些空白的内容。 现在我会告诉你如何在Excel中的数据透视表中隐藏这些空行。

Kutools for Excel - 最佳办公生产力工具提高80%的生产力

  • 重用: 快速插入 复杂的公式,图表 以及你以前用过的任何东西; 加密单元格 密码; 创建邮件列表 并发送电子邮件...
  • 超级方程式酒吧 (轻松编辑多行文字和公式); 阅读布局 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 不丢失数据; 分裂细胞含量; 组合重复的行/列...防止重复的细胞; 比较范围...
  • 选择复制或唯一 行; 选择空行 (所有细胞都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择......
  • 精确复制 多个单元格而不更改公式参考; 自动创建参考 多张表; 插入项目符号,复选框等等......
  • 提取文本,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级过滤器 (将过滤方案保存并应用到其他工作表); 高级排序 按月/周/日,频率等; 特殊过滤器 用粗体,斜体......
  • 结合工作簿和工作表; 根据键列合并表; 将数据拆分为多个表格; 批量转换xls,xlsx和PDF...
  • 超过300强大的功能。 支持Office / Excel 2007-2019和365。 支持所有语言。 在您的企业或组织中轻松部署。 全功能60天免费试用。
kte tab 201905

Office选项卡为Office提供选项卡式界面,使您的工作更轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,Publisher,Access,Visio和Project。
  • 在同一窗口的新选项卡中打开并创建多个文档,而不是在新窗口中。
  • 通过50%提高您的工作效率,每天为您减少数百次鼠标点击!
官方底部
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Accountant master · 16 days ago
    Thank you so much !!!!!
  • To post as a guest, your comment is unpublished.
    chemheang · 2 months ago
    I cannot edit after I save. Can yo tell me why?
  • To post as a guest, your comment is unpublished.
    Chantal Bot-Roovers · 3 months ago
    sorry, this still doesn't provide a solution for me in excel 2010. You're =if(sumproduct() formula doesn't work. It misses the values for the if formula if you use it like you put it and it doesn't count unique values in my excel sheet if I add =if(>1,01;1;0)...
  • To post as a guest, your comment is unpublished.
    Michael · 3 months ago
    oh man... you saved me so so so much time !!!
    thanks a lot !!!!
  • To post as a guest, your comment is unpublished.
    Dilip · 1 years ago
    Distinct count Option not shown in summarize value by - Excel version 2013
    • To post as a guest, your comment is unpublished.
      Karim Masarweh · 1 years ago
      Please verify that you have ticked the "Add this data to data model" check in the CreatePivot dialog box :)
    • To post as a guest, your comment is unpublished.
      Karim · 1 years ago
      I faced the same issue and then found the resolution.
      Seems that it's available only when you tick the "Add this data to the Data Model" checkbox in the Create PivotTable dialog box.
      Please try if that helps
    • To post as a guest, your comment is unpublished.
      Julio · 1 years ago
      same for me! Any suggestion?
  • To post as a guest, your comment is unpublished.
    Brian Vaughn · 1 years ago
    These all work but only to an extent. I'm trying to find a solution for the issue with all of these. When I create a helper column and use the formula =IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1) I do indeed get the distinct count. But how do you resolve the issue were you need the pivot fields to include one of the lines of data where the formula gives a zero? I also tried using the Data Model and distinct count. This gives the correct count but when you double click the data to drill down you do not get the data specified in the pivot.
  • To post as a guest, your comment is unpublished.
    Ray Man · 1 years ago
    Amazing! thanks a tons - this worked for me on Excel 2016.
  • To post as a guest, your comment is unpublished.
    Guest · 1 years ago
    I don't see the Distinct Count under Summarize Value By tab. My "Add this data to the Data model" check box is also grey out. How can I change this setting?
    • To post as a guest, your comment is unpublished.
      AJ · 4 months ago
      Ran into the same issue... it is probably because the file you opened was as a csv. When I reopened my file as an excel file (either start a new one, copy+paste or save as), I have the functionality of adding to data model
  • To post as a guest, your comment is unpublished.
    Ms Nadia · 1 years ago
    omg!!! yes...thanks for this!!!!
  • To post as a guest, your comment is unpublished.
    Lokesh Tailor · 1 years ago
    Thanks. It is helpful..
  • To post as a guest, your comment is unpublished.
    kash · 1 years ago
    Thank you. saved lot of hours.
  • To post as a guest, your comment is unpublished.
    Nick · 1 years ago
    I am using excel 2016 but I am not seeing the Count Distinct option in the pivot Value Fields Settings window. There are only Sum, Count, Average, Max, Min, Product, Count Numbers, StdDev, StdDevp, Var and Varp. Any thoughts on how to find it?
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello, Nick,
      You should check Add this data to the Data model check box in the first step when you creating the pivot table, see screenshot:
      • To post as a guest, your comment is unpublished.
        Nick · 1 years ago
        Hi Skyyang, Thank you, I did select this but once it is selected, I am not able to add calculated fields. Do you know how to add in calculated fields using this method?
      • To post as a guest, your comment is unpublished.
        Ayush · 1 years ago
        very helpful!
  • To post as a guest, your comment is unpublished.
    Sam · 1 years ago
    So glad I came across this.
  • To post as a guest, your comment is unpublished.
    Garima · 1 years ago
    Very helpful !
  • To post as a guest, your comment is unpublished.
    Filipe · 1 years ago
    Never used that Add this data to the data model before, great tip! Thanks!
  • To post as a guest, your comment is unpublished.
    Ravi Chauhan · 1 years ago
    Awesome ... thank you.
  • To post as a guest, your comment is unpublished.
    TonyL · 3 years ago
    I own and love KuTools, but to find unique values (using 2010) whether with helpers cells or Kutools, do does the data have to be sorted so that the unique field can be found? Thank you.