利用Excel函數統計不重復值數量的三種方法詳解 _知識經驗

【利用Excel函數統計不重復值數量的三種方法詳解】有一個數據表如右圖所示，要求統計A2:A11單元格區域中不重復記錄的個數。
方法1：
使用COUNTIF函數創建數組公式：
{=SUM(1/COUNTIF(A2:A11,A2:A11))}
思路解析：
使用條件統計函數COUNTIF返回區域內每個記錄出現次數的數組，被1除后在對得到的商求和。假設記錄X出現N次，則每次都轉為N/1,N個1/N求和得到1 。
這是一個統計不重復值的經典算法，起特點如小。
(1)統計區域內不得有空單元格，否則返回#DIV/0!錯誤 ?？梢杂孟旅娴臄到M公式解決這個問題：
{=SUM(IF(A2:A11<>"",1/COUNTIF(A2:A11,A2:A11))}
(2)因為EXCEL浮點運算可能產生誤差而造成答案不正確，即公式返回值比正確值小。對于這種情況，可以用嵌套ROUND函數修正。
(3)對數據類型沒有要求,文本,數值,邏輯值,錯誤值均可。
(4)統計區域不限于單行或單列（即一維引用），可以是A1:C10這樣的矩形區域，但必須是對單元格區域的引用，而不能是非引用類型的數組。
方法2：
使用FREQUENCY函數法創建數組公式：
{=SUM(IF(FREQUENCY(A2:A11,A2:A11)>0,1))}
此公式可以簡化為
{=SUM(–(FREQUENCY(A2:A11,A2:A11)>0))}
思路解析：使用FREQUENCY函數返回統計區域的分部頻率數組中，在記錄第一次出現的對應位置是該記錄出現的總次數，第二次出現的位置則為0 。
此方法特點如下：
(1)統計區域可以有空單元格。因為該函數將忽略空白單元格和文本。
(2)沒有浮點運算誤差。
(3)參數只能為數值，如果是一維的"文本"數據，可以借助MATCH函數轉換為行,序列號數值再進行統計。
(4)參數不受引用或數組的尺寸范圍限制,可以支持多行多列的"數值"數據的不重復統計。
方法3：
使用MATCH=ROW比較法創建數組公式：
{=SUM(IF(MATCH(A2:A11,A2:A11,)=ROW(2:11)-1,1))}
此公式可以簡化為：
{=SUM(–(MATCH(A2:A11,A2:A11,)=ROW(2:11)-1))}
思路解析：
使用MATCH函數返回區域內每個記錄第一次出現的位置，并與ROW函數返回的記錄所在對應行號減去首條記錄行號得到的1~m（記錄總個數）的數組比較，即第一次出現就算1次，其他記錄忽略。
此方法的特點如下：
(1)統計區域內不得有空單元格，否則MATCH函數返回#N/A錯誤。對于所包含的空單元格，可以用文本合并進行相應的處理，如{=SUM((A2:A11<>"")*(MATCH(A2:A11&"",A2:A11&"",0)=ROW(2:11)-1))} 。
(2)不會有浮點運算誤差。
(3)數據類型可以是文本,數值,邏輯值,但不得包含錯誤值。
(4)僅限于一維區域引用或一維數組。

利用Excel函數統計不重復值數量的三種方法詳解

猜你喜歡