excel多條件不重複數據

excel表格是我們工作時常會用到的，那麼關於excel多條件不重複數據你又清楚操作嗎，以下是本站小編蒐集並整理的有關內容，希望在閲讀之餘對大家能有所幫助!

excel多條件不重複數據

→ 解決方案1：

使用1/COUNTIF與SUM函數組合統計不重複數據個數。

→ 操作方法

在C2單元格輸入以下2個公式之一：

公式1 {=SUM(1/COUNTIF(A2:A16,A2:A16))}

公式2 =SUMPRODUCT(1/COUNTIF(A2:A16,A2:A16))

→ 原理分析

1.使用COUNTIF函數進行條件統計，返回A2:A16單元格區域內每個數據出現次數的數組：

{2;2;1;3;2;1;3;2;1;1;2;1;1;2;3}

被1除後，生成數組：

{1/2;1/2;1;1/3;1/2;1;1/3;1/2;1;1;1/2;1;1;1/2;1/3}

即出現N次重複的，就變成N個1/N，求和就是1，達到重複值只算1次的目的.。

2.最後使用SUM或SUMPRODUCT函數求和即可得到區域內不重複值的個數。

→ 知識擴展

1/COUNTIF函數統計不重複值的優缺點

1.統計區域內不得有空單元格，否則返回#DIV/0!錯誤。可以使用嵌套IF函數的數組公式解決這個問題，公式如下：

{=SUM(IF(A2:A16<>"",1/COUNTIF(A2:A16,A2:A16)))}

2.因為EXCEL浮點運算可能產生誤差而造成答案不正確，即公式返回值比正確值小。對於這種情況，可以用嵌套ROUND函數修正。

TIF函數對數據類型沒有要求，文本、數值、邏輯值、錯誤值均可，每一種錯誤值算作一個不重複數據。

4.統計區域不限於單行或單列(即一維引用)，可以是多行多列的矩形區域，但必須是對單元格區域的引用，而不能是非引用類型的數組。

→ 解決方案2：

使用MATCH=ROW比較判斷統計不重複數據個數。

操作方法

在C2單元格輸入下列2個公式之一：

公式1 {=SUM(--(MATCH(A2:A16,A2:A16,)=ROW(2:16)-1))}

公式2 =SUMPRODUCT(--(MATCH(A2:A16,A2:A16,)=ROW(2:16)-1))

→ 原理分析

1.使用MATCH函數返回區域內每個數據第一次出現的位置數組：

{1;2;3;4;1;6;4;8;9;10;8;12;13;2;4}

2.然後與其對應的行號位置比較，因為只有第一次出現的位置才會一致，所以統計的是不重複個數。

3.使用減負運算將判斷結果返回的邏輯值轉換為1、0，最後使用SUM或SUMPRODUCT求和即可得出不重複數據的個數。

→ 知識擴展

MATCH=ROW法統計不重複值的優缺點

1.統計區域內不得有空單元格，否則MATCH函數返回#N/A錯誤。對於包含空單元格的區域，可以用文本合併進行相應的處理，公式如下：

=SUMPRODUCT((A2:A16<>"")*(MATCH(A2:A16&"",A2:A16&"",0)=ROW(2:16)-1))

其中，(A2:A16<>"")用於防止將空單元格&""後算作一個空文本數據。

2.不會有浮點運算誤差。

3.數據類型可以是文本、數值、邏輯值，但不得包含錯誤值。

4.數據可以是內存數組，也可以是單元格區域的引用，但必須是單行或單列。

→ 解決方案3：

使用FREQUENCY函數統計不重複數字個數。

→ 操作方法

在C2單元格輸入下列2個公式之一：

公式1 =COUNT(1/FREQUENCY(A2:A16,A2:A16))

公式2 =SUM(--(FREQUENCY(A2:A16,A2:A16)>0))

→ 原理分析

1.使用FREQUENCY函數返回統計區域內數字的分佈頻率數組：

{2;2;1;3;0;1;0;2;1;1;0;1;1;0;0;0}

由此可見，第一次出現的數字位置返回數字出現個數，而第2次出現就返回0，因此只要統計非0的個數即可。

2.公式1使用1/FREQUENCY將0轉換為#DIV/0!錯誤值，再利用COUNT函數忽略錯誤值的特性統計數組中非0數字的個數，公式2使用--(FREQUENCY>0)將數組大於0判斷得到的邏輯值轉換為數值1、0，再使用SUM函數求和，由此實現統計不重複數據個數。

3.由於FREQUENCY函數默認返回數組，COUNT、SUM函數均將其視為常量數組代入計算，因而無需按組合鍵形成數組公式。

→ 知識擴展

FREQUENCY函數統計不重複值的優缺點

1.統計區域可以有空單元格。因為FREQUENCY函數將忽略空白單元格和文本。

2.使用公式2沒有浮點運算誤差。

3.數據必須為數值，如果是一維的文本數據，可以藉助MATCH函數轉換為序列號數值再進行統計。

4.參數不受引用或數組的尺寸範圍限制，可以支持多行多列的數值數據的不重複統計。