Excel非重複、非空白單元格提取

我們來看下面一道例子： 

  yy01

yy01 xx01

xx01 yy02

yy01 xx02

yy02 　

xx02 　

xx02  

   

數據 結果

 以上黃色單元格區域中公式該如何理解：1.x=IF(Sheet1!$A$2:$A$7<>"",IF(MATCH(Sheet1!$A$2:$A$7,Sheet1!$A$2:$A$7,0)=ROW(Sheet1!$A$2:$A$7)-1,ROW(Sheet1!$A$2:$A$7)))   =IF(ROW(2:2)>COUNT(x),"",INDEX(A:A,SMALL(x,ROW(2:2))))2.如果起始行不是A2,而是A1，公式應該怎樣改？3 B列的公式能實現挑出非重複、非空白的單元的功能。如果要到達C列的效果，即只挑出非空白的單元格，應該怎樣實現？分析： 

①   ②   ③   ④

     

MATCH()   ROW()   IF()……   X

1   1   2   2

2   2   3   3

1   3   FALSE   FALSE

4   4   5   5

5   5   6   6

5   6   FALSE   FALSE

             

⑤   ⑥   ⑦    

       

SMALL()   INDEX()   IF()    

2   yy01   yy01    

3   xx01   xx01    

5   yy02   yy02    

6   xx02   xx02    

#NUM!   #NUM!   　    

#NUM!   #NUM!   　    

 為了敘述的方便，為了減少解釋的難度，我把數據改了，改成沒有空白的數據了，相信看完後還是能夠處理它的。提取不重複值，我習慣建立一個數組，並把它定義為一個名稱，還習慣用X命名，數組X中所有數值為「不重複值」所在的行號，如本例，X={2;3;false;5;6;false}，X相當於一份花名冊，下一步按花名冊點名就是。 MATCH()是幹什麼的？

一。建立X數組，首先應歸功於查找函數MATCH()，簡單地說，按需要，MATCH()返回一個數據在指定區域中第一次出現的位置。我們用數據區域 $A$2:$A$7作函數的前兩個參數，第三個參數一般為0，公式為：MATCH($A$2:$A$7,$A$2:$A$7,0)，公式的運算結果相當於： MATCH($A$2,$A$2:$A$7,0) MATCH($A$3,$A$2:$A$7,0) …… MATCH($A$7,$A$2:$A$7,0)，最後結果是一個數組(見①)。列中有兩個yy01，兩個yy01在指定區域中第一次出現的位置是相同的，都是1，我們要的就是這種效果。      二。Row() ==>返回一個引用的行號。判斷了每一個數據在區域中首次出現的位置，接著再判斷每一個數據在區域中所處的位置，用函數Row()，Row($A$2:$A$7)返回的結果相當 於： Row($A$2) Row($A$3) …… Row($A$7) 最後結果也是一個數組(見②，為了便於對比，②的結果為Row()-1)。      三。下面的內容反覆看，看懂為止。第三步，對以上兩組結果進行對比，返回一個新的結果：如果Row()-1=Match()，公式就記下這個數據所在的行號： =IF(MATCH(…)=ROW(…)-1,ROW(…)) 為什麼要減1？因為數據區域$A$2:$A$7的第一行是2，Match()返回的第一個結果是1，首次出現的數據，它的行號減1就是Match()的值，如果數據區域是$A$20:$A$70，則公式為: =IF(MATCH(…)=ROW(…)-19,ROW(…)) 如果起始行不是A2,而是A1，公式應該怎樣改？這樣： =IF(MATCH(…)=ROW(…),ROW(…)) 最後結果見③，所有數值表示該行的數據是第一次出現，所有行號沒有重複(我們需要的就是它)，所有重複數據在這個公式中，返回的結果不是行號，是一個邏輯值False。如果你知道自定義名稱，可以跳過。實際運用的時候，我們可以在任一單元格中編寫公式： =IF(MATCH($A$2:$A$7,$A$2:$A$7,0)=ROW($A$2:$A$7)-1,ROW($A$2:$A$7)) 在編輯欄中選擇公式，複製它。然後按Ctrl+F3定義名稱，在名稱欄中寫X，當然，你命名為Y或其它名稱也行，在引用位置按Ctrl+V粘貼，確定。定義名稱完畢。最終，我們要把不重複值寫到某單元格區域，我們用的核心函數是Index()，我們現在研究的數據是在同一列，因此，公式可以用：Index(A:A,行號)返回結果，如果數據在D列，則： Index(D:D,行號)這個行號，我們用Small()函數返回，在幫助中我們可以看到，Small()是「返回數據集中第 k 個最小值。」，這裡，數據集是我們剛才定義的X： Small(X,1) ==>X 中第1個最小值 Small(X,2) ==>X 中第2個最小值就這個意思。關於Small(X,Row(1:1))這裡的參數k，我們不用數字1、2、3，而用Row(1:1)或Row(A1) 形式的函數，目的是方便公式的複製，寫好第一個公式，用滑鼠把它往下一拖，第二個公式中該位置，就變成了Row(2:2)或Row(A2)。記住，無論我們把結果寫到工作表的哪個位置，第一個單元格該參數為Row(1:1)。結果見⑤。我們把Small()函數取得的結果作為Index()函數的行號寫入公式，就能取得我們要的結果，見⑥。滿足我們一點小小的要求我們總不喜歡在結果中看見錯誤值 #NUM! 。外層函數If()的作用就是它。Count(x)是計算x數組中有幾個數值，即我們要的不重複值有幾個，當Row()行號大於它時，顯示空值： If(Row(1:1)>Count(x),"",……)當結果不是垂直排列，而是水平排成一行時，名稱X不變，單元格公式中將Row(1:1)改為Column(a:a)即可。函數Large()是返回第k個最大值，如果用這個函數代替Small()，結果的排列順序將會倒過來。我們在定義X時，數據區域最好用絕對引用，避免不必要的麻煩，要編輯欄，寫完地址按一下F4，它會幫你轉換為絕對引用。寫公式時不用寫表名，定義名稱時，系統自動會加上表名的，這是閑話。關於不重複值，是個大話題，我只對本公式談些心得就是，就本公式而言，也是千變萬化的，也得根據實際情況作些調整，如X的結果不一定就是行號，Index()也可用Offste()等。下面是第二個話題，非空白的問題。這是取不重複值解決辦法的進階問題，我們要排除數據列中的空白數據，可以在定義X時外層加一個If()函數： x=IF($A$2:$A$7<>"",IF(MATCH($A$2:$A$7,$A$2:$A$7,0)=ROW($A$2:$A$7)-1,ROW($A$2:$A$7))) 事實上，用該公式取不重複值，返回的結果也不能包含空值，Match()會因此返回錯誤值。 本例中，如果我們只取xx開頭的數據，那麼公式可以這樣寫 x=IF(LEFT($A$2:$A$7,2)="xx",IF(MATCH($A$2:$A$7,$A$2:$A$7,0)=ROW($A$2:$A$7)-1,ROW($A$2:$A$7))) 你還可以把這個條件換成其它新鮮一些的，沒關係，試試看，如Right(a2:a7,2)="01"。資料來自: 中國自學網(Www.CnZiXue.Com) 詳細出處參考：http://www.cnzixue.com/html/office/excel/Function/2010/0626/7167.html
推薦閱讀：