我們來看下面一道例子：
yy01
yy01 xx01
xx01 yy02
yy01 xx02
yy02
xx02
xx02
數(shù)據(jù) 結(jié)果
以上黃色單元格區(qū)域中公式該如何理解：
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，公式應(yīng)該怎樣改？
3 B列的公式能實(shí)現(xiàn)挑出非重復(fù)、非空白的單元的功能。如果要到達(dá)C列的效果，即只挑出非空白的單元格，應(yīng)該怎樣實(shí)現(xiàn)？
分析：
① ② ③ ④
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!
為了敘述的方便，為了減少解釋的難度，我把數(shù)據(jù)改了，改成沒有空白的數(shù)據(jù)了，相信看完后還是能夠處理它的。提取不重復(fù)值，我習(xí)慣建立一個(gè)數(shù)組，并把它定義為一個(gè)名稱，還習(xí)慣用X命名，數(shù)組X中所有數(shù)值為“不重復(fù)值”所在的行號(hào)，如本例，X={2;3;false;5;6;false}，X相當(dāng)于一份花名冊，下一步按花名冊點(diǎn)名就是。 MATCH()是干什么的？
一。
建立X數(shù)組，首先應(yīng)歸功于查找函數(shù)MATCH()，簡單地說，按需要，MATCH()返回一個(gè)數(shù)據(jù)在指定區(qū)域中第一次出現(xiàn)的位置。我們用數(shù)據(jù)區(qū)域 $A$2:$A$7作函數(shù)的前兩個(gè)參數(shù)，第三個(gè)參數(shù)一般為0，公式為：MATCH($A$2:$A$7,$A$2:$A$7,0)，公式的運(yùn)算結(jié)果相當(dāng)于： MATCH($A$2,$A$2:$A$7,0) MATCH($A$3,$A$2:$A$7,0) …… MATCH($A$7,$A$2:$A$7,0)，最后結(jié)果是一個(gè)數(shù)組(見①)。列中有兩個(gè)yy01，兩個(gè)yy01在指定區(qū)域中第一次出現(xiàn)的位置是相同的，都是1，我們要的就是這種效果。
二。Row() ==>返回一個(gè)引用的行號(hào)。
判斷了每一個(gè)數(shù)據(jù)在區(qū)域中首次出現(xiàn)的位置，接著再判斷每一個(gè)數(shù)據(jù)在區(qū)域中所處的位置，用函數(shù)Row()，Row($A$2:$A$7)返回的結(jié)果相當(dāng) 于： Row($A$2) Row($A$3) …… Row($A$7) 最后結(jié)果也是一個(gè)數(shù)組(見②，為了便于對比，②的結(jié)果為Row()-1)。
三。下面的內(nèi)容反復(fù)看，看懂為止。
第三步，對以上兩組結(jié)果進(jìn)行對比，返回一個(gè)新的結(jié)果：如果Row()-1=Match()，公式就記下這個(gè)數(shù)據(jù)所在的行號(hào)： =IF(MATCH(…)=ROW(…)-1,ROW(…)) 為什么要減1？因?yàn)閿?shù)據(jù)區(qū)域$A$2:$A$7的第一行是2，Match()返回的第一個(gè)結(jié)果是1，首次出現(xiàn)的數(shù)據(jù)，它的行號(hào)減1就是Match()的值，如果數(shù)據(jù)區(qū)域是$A$20:$A$70，則公式為: =IF(MATCH(…)=ROW(…)-19,ROW(…)) 如果起始行不是A2,而是A1，公式應(yīng)該怎樣改？這樣： =IF(MATCH(…)=ROW(…),ROW(…)) 最后結(jié)果見③，所有數(shù)值表示該行的數(shù)據(jù)是第一次出現(xiàn)，所有行號(hào)沒有重復(fù)(我們需要的就是它)，所有重復(fù)數(shù)據(jù)在這個(gè)公式中，返回的結(jié)果不是行號(hào)，是一個(gè)邏輯值False。
如果你知道自定義名稱，可以跳過。
實(shí)際運(yùn)用的時(shí)候，我們可以在任一單元格中編寫公式： =IF(MATCH($A$2:$A$7,$A$2:$A$7,0)=ROW($A$2:$A$7)-1,ROW($A$2:$A$7)) 在編輯欄中選擇公式，復(fù)制它。然后按Ctrl+F3定義名稱，在名稱欄中寫X，當(dāng)然，你命名為Y或其它名稱也行，在引用位置按Ctrl+V粘貼，確定。定義名稱完畢。
最終，我們要把不重復(fù)值寫到某單元格區(qū)域，我們用的核心函數(shù)是Index()，我們現(xiàn)在研究的數(shù)據(jù)是在同一列，因此，公式可以用：
Index(A:A,行號(hào))返回結(jié)果，如果數(shù)據(jù)在D列，則： Index(D:D,行號(hào))
這個(gè)行號(hào)，我們用Small()函數(shù)返回，在幫助中我們可以看到，Small()是“返回?cái)?shù)據(jù)集中第 k 個(gè)最小值。”，這里，數(shù)據(jù)集是我們剛才定義的X： Small(X,1) ==>X 中第1個(gè)最小值 Small(X,2) ==>X 中第2個(gè)最小值就這個(gè)意思。
關(guān)于Small(X,Row(1:1))
這里的參數(shù)k，我們不用數(shù)字1、2、3，而用Row(1:1)或Row(A1) 形式的函數(shù)，目的是方便公式的復(fù)制，寫好第一個(gè)公式，用鼠標(biāo)把它往下一拖，第二個(gè)公式中該位置，就變成了Row(2:2)或Row(A2)。記住，無論我們把結(jié)果寫到工作表的哪個(gè)位置，第一個(gè)單元格該參數(shù)為Row(1:1)。結(jié)果見⑤。我們把Small()函數(shù)取得的結(jié)果作為Index()函數(shù)的行號(hào)寫入公式，就能取得我們要的結(jié)果，見⑥。
滿足我們一點(diǎn)小小的要求
我們總不喜歡在結(jié)果中看見錯(cuò)誤值 #NUM! 。外層函數(shù)If()的作用就是它。Count(x)是計(jì)算x數(shù)組中有幾個(gè)數(shù)值，即我們要的不重復(fù)值有幾個(gè)，當(dāng)Row()行號(hào)大于它時(shí)，顯示空值： If(Row(1:1)>Count(x),"",……)
當(dāng)結(jié)果不是垂直排列，而是水平排成一行時(shí)，名稱X不變，單元格公式中將Row(1:1)改為Column(a:a)即可。函數(shù)Large()是返回第k個(gè)最大值，如果用這個(gè)函數(shù)代替Small()，結(jié)果的排列順序?qū)?huì)倒過來。我們在定義X時(shí)，數(shù)據(jù)區(qū)域最好用絕對引用，避免不必要的麻煩，要編輯欄，寫完地址按一下F4，它會(huì)幫你轉(zhuǎn)換為絕對引用。寫公式時(shí)不用寫表名，定義名稱時(shí)，系統(tǒng)自動(dòng)會(huì)加上表名的，這是閑話。關(guān)于不重復(fù)值，是個(gè)大話題，我只對本公式談些心得就是，就本公式而言，也是千變?nèi)f化的，也得根據(jù)實(shí)際情況作些調(diào)整，如X的結(jié)果不一定就是行號(hào)，Index()也可用Offste()等。
下面是第二個(gè)話題，非空白的問題。
這是取不重復(fù)值解決辦法的進(jìn)階問題，我們要排除數(shù)據(jù)列中的空白數(shù)據(jù)，可以在定義X時(shí)外層加一個(gè)If()函數(shù)： 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))) 事實(shí)上，用該公式取不重復(fù)值，返回的結(jié)果也不能包含空值，Match()會(huì)因此返回錯(cuò)誤值。 本例中，如果我們只取xx開頭的數(shù)據(jù)，那么公式可以這樣寫 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))) 你還可以把這個(gè)條件換成其它新鮮一些的，沒關(guān)系，試試看，如Right(a2:a7,2)="01"。
