引言：本文的練習(xí)整理自chandoo.org。多一些練習(xí)，想想自己怎么解決問題，看看別人又是怎樣解決的，能夠快速提高Excel公式編寫水平。

在《Excel公式練習(xí)95：返回不同值/重復(fù)值/唯一值作為數(shù)組》中，我們處理了從僅包含數(shù)值的區(qū)域中提取不同的/重復(fù)的/唯一的值的數(shù)組。

在《Excel公式練習(xí)96：返回不同的/重復(fù)的/唯一的字符串作為數(shù)組》中，我們處理了從僅包含文本值的區(qū)域中提取不同的/重復(fù)的/唯一的值的數(shù)組。

本次的練習(xí)是：你能想出一個(gè)公式，從包含數(shù)字和文本值的區(qū)域中提取一組不同值、重復(fù)值和唯一值。要求：

• 原始數(shù)據(jù)區(qū)域命名為“data”，在公式中可以引用該區(qū)域。除此之外，不應(yīng)使用任何命名區(qū)域或中間單元格。

• 每個(gè)公式的輸出必須是一個(gè)數(shù)組，可以直接被其他公式（如 COUNTA等）使用。也就是說，我們追求的是一個(gè)可以直接合并到其他公式中的公式，而不是必須在工作表區(qū)域內(nèi)輸入才能工作的公式。

• 每個(gè)公式都應(yīng)該是完全動態(tài)的，并且能夠用于任何大小的一維數(shù)組。

• 每個(gè)公式必須返回一個(gè)精確大小的數(shù)組，其中只包含不同的、重復(fù)的和唯一的值。因此，根據(jù)下圖1所示數(shù)據(jù)，輸出為：

不同值公式生成的數(shù)組正好是14個(gè)元素。

唯一值公式生成的數(shù)組的長度正好是9個(gè)元素。

重復(fù)值公式生成的數(shù)組長度正好是5個(gè)元素

示例數(shù)據(jù)如下圖1所示。其中，將單元格區(qū)域A2:A21命名為“data”。

圖1

不應(yīng)該使用任何輔助單元格、中間公式或者VBA。

注：不同值，是指不重復(fù)的數(shù)字。

唯一值，是指數(shù)字只出現(xiàn)一次。

重復(fù)值，是指數(shù)字出現(xiàn)不止一次。

寫下你的公式。

解決方案

下面均為數(shù)組公式。

公式1：

不同值：

=LOOKUP(MODE.MULT(IF(MATCH(data,data,0)=(ROW(data)-MIN(ROW(data))+{1,1}),ROW(data))),ROW(data),data)

唯一值：

=LOOKUP(MODE.MULT(IF(COUNTIF(data,data)=1,ROW(data)*{1,1})),ROW(data),data)

重復(fù)值：

=LOOKUP(MODE.MULT(IF((COUNTIF(data,data)>1)*MATCH(data,data,0)=ROW(data)-MIN(ROW(data))+{1,1},ROW(data))),ROW(data),data)

公式2：

不同值：

=INDEX(A:A,SMALL(IF(FREQUENCY(MATCH(data,data,),MATCH(data,data,))>0,ROW(data)),ROW(OFFSET(B1,,,SUM(N(FREQUENCY(MATCH(data,data,),MATCH(data,data,))>0))))))

唯一值：

=INDEX(A:A,SMALL(IF(FREQUENCY(MATCH(data,data,),MATCH(data,data,))=1,ROW(data)),ROW(OFFSET(B1,,,SUM(N(FREQUENCY(MATCH(data,data,),MATCH(data,data,))=1))))))

重復(fù)值：

=INDEX(A:A,SMALL(IF(FREQUENCY(MATCH(data,data,),MATCH(data,data,))>1,ROW(data)),ROW(OFFSET(B1,,,SUM(N(FREQUENCY(MATCH(data,data,),MATCH(data,data,))>1))))))

公式3：

不同值：

=LOOKUP(SMALL(IF(FREQUENCY(MATCH(data,data,),MATCH(data,data,)),ROW(data)),ROW(OFFSET(A1,,,SUM(1/COUNTIF(data,data))))),ROW(data),data)

唯一值：

=LOOKUP(SMALL(IF(COUNTIF(data,data)=1,ROW(data)),ROW(OFFSET(A1,,,SUM(N(COUNTIF(data,data)=1))))),ROW(data),data)

重復(fù)值：

=LOOKUP(SMALL(IF(FREQUENCY(MATCH(data,data,),MATCH(data,data,))>1,ROW(data)),ROW(OFFSET(A1,,,SUM(N(FREQUENCY(MATCH(data,data,),MATCH(data,data,))>1))))),ROW(data),data)

公式4：

不同值：

=OFFSET(OFFSET(A1,SMALL(IF(FREQUENCY(MATCH(data,data,),MATCH(data,data,))>0,ROW(data)),ROW(OFFSET(A1,,,SUM(1/COUNTIF(data,data)))))-1,0),,)

唯一值：

=OFFSET(OFFSET(A1,SMALL(IF(COUNTIF(data,data)=1,ROW(data)),ROW(OFFSET(A1,,,SUM(N(COUNTIF(data,data)=1)))))-1,),,)

重復(fù)值：

=OFFSET(OFFSET(A1,SMALL(IF(FREQUENCY(MATCH(data,data,),MATCH(data,data,))>1,ROW(data)),ROW(OFFSET(A1,,,SUM(N(FREQUENCY(MATCH(data,data,),MATCH(data,data,))>1)))))-1,),,)

公式5：

不同值：

=CELL('contents',OFFSET(A1,SMALL(IF(FREQUENCY(MATCH(data,data,0),MATCH(data,data,0)),ROW(data)),ROW(OFFSET(A1,,,SUM(1/COUNTIF(data,data)))))-1,))

唯一值：

=CELL('contents',OFFSET(A1,SMALL(IF(COUNTIF(data,data)=1,ROW(data)),ROW(OFFSET(A1,,,SUM(N(COUNTIF(data,data)=1)))))-1,))

重復(fù)值：

=CELL('contents',OFFSET(A1,SMALL(IF(FREQUENCY(MATCH(data,data,0),MATCH(data,data,0))>1,ROW(data)),ROW(OFFSET(A1,,,SUM(N(FREQUENCY(MATCH(data,data,),MATCH(data,data,))>1)))))-1,))

可以通過F9鍵或者公式求值功能，加深對上述公式的理解。