excel多條件不重複資料
excel表格是我們工作時常會用到的,那麼關於excel多條件不重複資料你又清楚操作嗎,以下是pincai小編蒐集並整理的有關內容,希望在閱讀之餘對大家能有所幫助!
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函式修正。
3.COUNTIF函式對資料型別沒有要求,文字、數值、邏輯值、錯誤值均可,每一種錯誤值算作一個不重複資料。
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.引數不受引用或陣列的尺寸範圍限制,可以支援多行多列的數值資料的不重複統計。
【excel多條件不重複資料】相關文章: