Excel公式練習8：獲取單元格區域中的不重複值

本次的練習是：在一個單元格區域中含有重複值，使用公式來獲取該區域中的不重複值。

例如，下圖所示的工作表單元格區域A1:A13，將其命名為Data。在該區域中，含有很多重複值。現在要獲取該區域中的不重複值。

先不看答案，動手試一試。

公式思路

首先求出單元格區域Data中每個值在區域中第1次出現的行號，然後根據行號取出這些值。

公式解析

在單元格區域C1:C13中輸入下面的數組公式：

=INDEX(Data,SMALL(IF(MATCH(Data,Data,0)=ROW(INDIRECT("1:"&ROWS(Data))),MATCH(Data,Data,0),""),ROW(INDIRECT("1:"&ROWS(Data)))))

按Ctrl+Shift+Enter組合鍵，即可得到區域Data中不重複值，如下圖所示：

公式中，MATCH(Data,Data,0)得到數組{1;1;1;4;4;4;7;7;7;10;10;7;1}，即區域Data中每個值在該區域中出現的行號。ROW(INDIRECT("1:"&ROWS(Data)))得到數組{1;2;3;4;5;6;7;8;9;10;11;12;13}，INDIRECT函數將「1：13」轉換成行區域$1:$13。

MATCH(Data,Data,0)=ROW(INDIRECT("1:"&ROWS(Data)))即上述兩個數組相比較，得到數組{TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}，IF函數根據該數組獲取MATCH(Data,Data,0)所得數組中的值，即{1;」」;」」;4;」」;」」;7;」」;」」;10;」」;」」;」」}，SMALL函數分別取這個數組的第1、2、3、…、13最小值，即{1;4;7;10;」」;」」;」」;」」;」」;」」;」」;」」;」」},將此數組作為INDEX 函數的參數，分別取區域Data中對應行的值。

從上圖所示的工作表中可以看出，對於數組公式中多餘的單元格會顯示#NUM!。使用下面的數組公式避免顯示#NUM!。

=IFERROR(INDEX(Data,SMALL(IF(MATCH(Data,Data,0)=ROW(INDIRECT("1:"& ROWS(Data))),MATCH(Data,Data,0),""),ROW(INDIRECT("1:"& ROWS(Data))))),"")

IFERROR函數在錯誤值時輸入空。

小結

• ROW函數中不能再包括其它求值的函數，此時使用INDIRECT函數來間接引用。

• IFERROR函數是Excel 2007及其後的版本中的函數，當第一個參數為錯誤值時，將另一個參數作為返回值。若要在Excel 2013中得到同樣的結果，則要將IF函數和ISERR函數結合使用。

• 公式中蘊含著一些通用思想，可以在其他類似情形中借鑒。

下期預告：

Excel公式練習9： 獲取當前單元格所在列的列字元

使用一個公式來獲取當前單元格所在列的列字元，例如當前單元格為B2，其所在列為列B。

轉載本文請聯繫我（xhdsxfjy@163.com）或者註明出處。

歡迎在下面留言，完善本文內容，讓更多的人學到更完美的知識。