Skip to main content

Kutools for Office — 一套工具,五種功能。完成更多工作。

輕鬆計算唯一值和不同值 – 7 個範例

Author Xiaoyang Last modified

通常,在 Excel 中,唯一值是指在列表中只出現一次且沒有任何重複的值,而不同值則是所有不同的值(唯一值 + 第一次出現的重複值)。當處理大型數據集時,您可能需要計算單元格列表中重複項之間的唯一值和不同值的數量,如下圖所示。本教程將介紹一些快速技巧來計算 Excel 中的唯一值和不同值。

Count unique values or distinct values in a list

計算 Excel 中的唯一值

計算 Excel 中的不同值(唯一值和第一次出現的重複值)


下載範例檔案

計算唯一值與不同值


計算 Excel 中的唯一值

本節將討論一些用於計算唯一值數量的公式範例,包括列表中的文字和數字。

使用公式計算唯一值

假設我有一個名稱列表,其中包含一些重複的名稱。現在,我需要獲取唯一的名稱數量(僅填入黃色的名稱),如下圖所示:

sample data

要解決這個問題,以下陣列公式可以幫助您:

步驟 1: 輸入公式

在您希望輸出結果的空白單元格中輸入或複製以下公式:

=SUM(IF(COUNTIF(A2:A12,A2:A12)=1,1,0))
 注意:在上述公式中,"A2:A12" 是您想要計算唯一值的數據列表。

步驟 2: 按下 Ctrl + Shift + Enter 鍵以獲得正確的結果:

Count unique values with formula

注意:

  1. 如果您的單元格列表包含其他類型的數據,例如數字、布林值、錯誤值等,應用上述公式時,它將忽略數據類型並計算唯一值的數量。
  2. 當數據列表包含空白單元格時,此公式將排除空白單元格。
  3. 如果存在其他類型的數據,並且只想計算唯一的文字值,請應用以下陣列公式,然後按下 "Ctrl" + "Shift" + "Enter" 鍵返回結果:
    =SUM(IF(ISTEXT(A2:A12)*COUNTIF(A2:A12,A2:A12)=1,1,0))
  4. 若要從包含任何類型數據的單元格列表中僅計算唯一數字,請使用以下陣列公式,然後按下 "Ctrl" + "Shift" + "Enter" 鍵返回結果:
    =SUM(IF(ISNUMBER(A2:A12)*COUNTIF(A2:A12,A2:A12)=1,1,0))

使用 Kutools for Excel 幾次點擊計算唯一值

當您下次需要應用這些公式時,記住它們可能會很痛苦。但是,如果您有 "Kutools for Excel",利用其 "公式助手" 中的 "Count Unique Values" 選項,只需幾次點擊即可獲得結果。請參閱以下示範:

  1. 點擊一個單元格以輸出結果;
  2. 啟用此功能,點擊 "Kutools" > "公式助手" > "公式助手" > "統計" > "Count unique values";
  3. 選擇數據列表 > "確定"。
    Count unique values with kutools
提示:
  1. 要應用此功能,您需要下載並安裝 Kutools for Excel
  2. "公式助手" 功能收集了 40 多個常用公式,例如 "提取唯一值", "提取具有唯一值的單元格(包括首次重複值)", "計算逗號分隔值的數量", "基於相同文本求和" 等等...

使用公式計算 Excel 365/2021 中的唯一值

如果您使用的是 Excel 365 或 Excel 2021,有一個新的 UNIQUE 函數可以幫助您創建更簡單的公式來計算一組數據中的唯一值。

例如,要計算 A2:A12 區域內唯一名稱的數量,請輸入以下公式:

步驟 1: 複製或輸入以下公式

=IFERROR(ROWS(UNIQUE(A2:A12,,TRUE)), 0)
注意:在此公式中,"A2:A12" 是您要計算唯一值的數據列表。

步驟 2: 按下 Enter 鍵以獲得結果:

Count unique values in Excel 365/2021 with formula

提示:

  1. 如果數據列表包含其他類型的數據,例如文字、數字、布林值、錯誤值等,此公式將忽略數據類型並計算唯一值的數量。
  2. 上述公式將計算包括空白單元格在內的唯一值,如果您想排除空白單元格,請應用以下公式,只需按下 "Enter" 鍵:
    =SUM(IF(COUNTIF(A2:A12,A2:A12)=1,1,0))

計算 Excel 中的不同值(唯一值和第一次出現的重複值)

要計算單元格列表中的不同值(唯一值和第一次出現的重複值),這裡我將介紹其他公式來完成此任務。

使用公式計算不同值

在 Excel 中,您可以應用以下任一公式來返回不同值的數量。

步驟 1: 輸入以下任一公式

公式 1: 輸入公式後,按下 "Enter" 鍵。

=SUMPRODUCT(1/COUNTIF(A2:A12,A2:A12))      

公式 2: 輸入公式後,按下 "Ctrl" + "Shift" + "Enter" 鍵。

=SUM(1/COUNTIF(A2:A12,A2:A12))             
注意:在這些公式中,"A2:A12" 是您要計算不同值的數據列表。

結果:

Count distinct values with formula

提示:

  1. 如果數據列表包含其他類型的數據,例如數字、布林值、錯誤值等,此公式將忽略數據類型並計算不同值的數量。
  2. 使用上述公式時,如果數據列表包含空白單元格,您將得到 #DIV/0 錯誤值。要修復並忽略空白單元格,您應該應用以下任一公式:
    公式 1: 輸入公式後,按下 "Enter" 鍵。
    =SUMPRODUCT((A2:A12<>"")/COUNTIF(A2:A12,A2:A12&""))        
    公式 2: 輸入公式後,按下 "Ctrl" + "Shift" + "Enter" 鍵。
    =SUM(IF(A2:A12<>"",1/COUNTIF(A2:A12, A2:A12), 0))       

    Count distinct values exclude blank cells with formula

  3. 若要僅獲取數據列表中的不同文字值數量,請應用以下陣列公式,並按下 "Ctrl" + "Shift" + "Enter" 鍵以獲得結果:
    =SUM(IF(ISTEXT(A2:A12),1/COUNTIF(A2:A12, A2:A12),""))
  4. 若要僅計算不同的數值,請使用以下陣列公式,並按下 "Ctrl" + "Shift" + "Enter" 鍵以獲得結果:
    =SUM(IF(ISNUMBER(A2:A12),1/COUNTIF(A2:A12, A2:A12),""))

使用 Kutools for Excel 幾次點擊計算不同值

如果您需要經常在工作簿中應用這些公式,記住它們可能會很痛苦。但是,如果您有 "Kutools for Excel",利用其 "公式助手" 中的 "Count cells with unique values (include the first duplicate)" 選項,只需幾次點擊即可獲得結果。請參閱以下示範:

  1. 點擊一個單元格以輸出結果;
  2. 啟用此功能,點擊 "Kutools" > "公式助手" > "統計" > "Count unique cells with unique values (include the first duplicate)";
  3. 選擇數據列表 > "確定"。
     Count distinct values with kutools
提示:
  1. 要應用此功能,您需要下載並安裝 Kutools for Excel
  2. "公式助手" 功能收集了 40 多個常用公式,例如 "提取唯一值", "提取具有唯一值的單元格(包括首次重複值)", "計算逗號分隔值的數量", "基於相同文本求和" 等等...

使用數據透視表計算不同值

在 Excel 中,數據透視表也可以幫助從數據列表中獲取不同值的數量,請按照以下步驟操作:

步驟 1: 創建數據透視表

  1. 選擇數據列表,然後從功能區中點擊 "插入" > "數據透視表",見截圖:
    create a pivottable for selected data
  2. 在彈出的 "數據透視表" 對話框中:
    (1). 選擇放置數據透視表的新工作表或現有工作表;
    (2). 然後勾選 "將此數據添加到數據模型" 複選框。
    (3). 然後點擊 "確定" 按鈕。
    set options in PivotTable from table or range dialog box

步驟 2: 安排字段並選擇不同值計數選項

  1.  顯示 "數據透視表字段" 面板,將 "名稱" 字段拖到 "值" 區域,見以下示範:
    Arrange the field for pivottable
  2. 然後,點擊 "名稱計數" 下拉菜單,並選擇 "值字段設置",見截圖:
    click value Field Settings option
  3. 然後,將打開 "值字段設置" 對話框,從 "按方式匯總值" 標籤下的列表框中選擇 "不同值計數",然後點擊 "確定" 按鈕,見截圖:
    select Distinct Count option

結果:

現在,創建的數據透視表將顯示數據列表的不同值計數,如下截圖所示:

PivotTable result to count distinct values

提示:

  1. 如果您更新了源數據,要獲取最新的計數,只需右鍵點擊數據透視表並選擇 "重新整理" 選項。
  2. 此 "不同值計數" 選項僅在 Excel 2013 及更高版本中可用。

使用公式計算 Excel 365/2021 中的不同值

在 Excel 365 或 Excel 2021 中,您可以使用新的 UNIQUE 函數與普通的 COUNTA 函數來創建一個簡單的公式。

請複製或輸入以下公式到空白單元格中,並按下 "Enter" 鍵以獲得結果:

=COUNTA(UNIQUE(A2:A12)) 
注意:在此公式中,"A2:A12" 是您要計算不同值的數據列表。

Count distinct values in Excel 365/2021 with formula

提示:

  1. 如果數據列表包含其他類型的數據,例如文字、數字、布林值、錯誤值等,此公式將忽略數據類型並計算不同值的數量。
  2. 上述公式將計算包括空白單元格在內的不同值,如果您想排除空白單元格,請應用以下公式,並按下 "Enter" 鍵以獲得結果:
    =COUNTA(UNIQUE(FILTER(A2:A12, A2:A12<>"")))    

    Count distinct values in Excel 365/2021 with formula exclude blank cells


相關文章:

  • 在數據透視表中計算唯一值
  • 默認情況下,當我們根據包含一些重複值的數據範圍創建數據透視表時,所有記錄都會被計數,但有時我們只想根據某一列來計算唯一值以獲得正確的結果。本文將討論如何在數據透視表中計算唯一值。
  • 計算過濾列中的唯一值
  • 如果您有一個名稱列表,其中填充了一些重複項,並且現在您只想從這個過濾列表中計算唯一名稱的數量。您是否有任何好的解決方案來快速處理這個問題?
  • 使用多個條件計算唯一值
  • 這裡有一個數據範圍,包含幾個名稱、項目和地點,我們需要做的是找到符合我列出的條件的名稱,然後僅對每個匹配的名稱計數一次,如下截圖所示。您有什麼技巧來解決這個問題嗎?本文將介紹這些技巧。
  • 連接唯一值
  • 如果我有一個長列表的值,其中填充了一些重複的數據,現在我想找到唯一的值並將它們連接到單一單元格中。如何快速輕鬆地處理這個問題?