提示:其他語言是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。 支持所有語言。 在您的企業或組織中輕鬆部署。 全功能30天免費試用。
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.
    Jay · 1 months ago
    My Excel dont have check box " Add this data to the Data Model"
    So, What can i do?
  • To post as a guest, your comment is unpublished.
    Accountant master · 2 months ago
    Thank you so much !!!!!
  • To post as a guest, your comment is unpublished.
    chemheang · 4 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 · 5 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 · 5 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 · 6 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.