excelperfect

導(dǎo)語(yǔ)：繼續(xù)研究來(lái)自于excelxor.com的案例。這個(gè)案例似乎又有點(diǎn)復(fù)雜，但其中許多公式技術(shù)仍值得我們反復(fù)琢磨。

本次的練習(xí)是：如下圖1所示，單元格區(qū)域A2:E5中包含一系列值和空單元格，其中有重復(fù)值，要求從該單元格區(qū)域中生成按字母順序排列的不重復(fù)值列表，如圖1中G列所示。

圖1

在單元格G1中編寫一個(gè)公式，下拉生成所要求的列表。

先不看答案，自已動(dòng)手試一試。

公式

在單元格G1中的公式為：

=IF(ROWS($1:1)>$H$1,'',INDEX(Arry4,MATCH(SMALL(IF(FREQUENCY(IF(Range1<>'',MATCH(Range1,Arry4,0)),Arry1),COUNTIF(Range1,'<'&Arry4)),ROWS($1:1)),IF(Arry4<>'',COUNTIF(Range1,'<'&Arry4)),0)))

下拉直至出現(xiàn)空單元格為止。

在單元格H1中的公式為：

=SUMPRODUCT((Range1<>'')/COUNTIF(Range1,Range1&''))

公式中使用了5個(gè)名稱，分別為：

名稱：Range1

引用位置：=$A$2:$E$5

名稱：Arry1

引用位置：=ROW(INDIRECT('1:'&COLUMNS(Range1)*ROWS(Range1)))

名稱：Arry2

引用位置：=1+INT((Arry1-1)/COLUMNS(Range1))

名稱：Arry3

引用位置：=1+MOD(Arry1-1,COLUMNS(Range1))

名稱：Arry4

引用位置：=INDEX(Range1,N(IF(1,Arry2)),N(IF(1,Arry3)))

公式解析

1. 在單元格H1中的公式比較直接，是一個(gè)獲取列表區(qū)域唯一值數(shù)量的標(biāo)準(zhǔn)公式：

=SUMPRODUCT((Range1<>'')/COUNTIF(Range1,Range1&''))

轉(zhuǎn)換為：

=SUMPRODUCT(({'Due','','Otto','','Otto';'','','','','Tre';'Sei','Cinque','','Quattro','Otto';'Due','','','Quattro','Otto'}<>'')/COUNTIF(Range1,Range1&''))

轉(zhuǎn)換為：

=SUMPRODUCT({TRUE,FALSE,TRUE,FALSE,TRUE;FALSE,FALSE,FALSE,FALSE,TRUE;TRUE,TRUE,FALSE,TRUE,TRUE;TRUE,FALSE,FALSE,TRUE,TRUE}/COUNTIF(Range1,Range1&''))

接著解析COUNTIF部分，該部分計(jì)算Range1中每個(gè)條目在該區(qū)域內(nèi)出現(xiàn)的次數(shù)：

=SUMPRODUCT({TRUE,FALSE,TRUE,FALSE,TRUE;FALSE,FALSE,FALSE,FALSE,TRUE;TRUE,TRUE,FALSE,TRUE,TRUE;TRUE,FALSE,FALSE,TRUE,TRUE}/{2,9,4,9,4;9,9,9,9,1;1,1,9,2,4;2,9,9,2,4})

除法運(yùn)算后：

=SUMPRODUCT({0.5,0,0.25,0,0.25;0,0,0,0,1;1,1,0,0.5,0.25;0.5,0,0,0.5,0.25})

結(jié)果為：

6

2. 在單元格G1的主公式中：

=IF(ROWS($1:1)>$H$1,'',

如果公式向下拖拉的行數(shù)超過(guò)單元格H1中的數(shù)值6，則返回空值。

3. 下面重點(diǎn)看看公式中的：

INDEX(Arry4,MATCH(SMALL(IF(FREQUENCY(IF(Range1<>'',MATCH(Range1,Arry4,0)),Arry1),COUNTIF(Range1,'<'&Arry4)),ROWS($1:1)),IF(Arry4<>'',COUNTIF(Range1,'<'&Arry4)),0))

實(shí)際上，這是提取唯一且按字母順序排列的值的標(biāo)準(zhǔn)公式構(gòu)造，唯一區(qū)別是提取值的區(qū)域不是單列、一維區(qū)域，而是二維區(qū)域。然而，在原理上該技術(shù)是相同的：首先將二維區(qū)域轉(zhuǎn)換成一維區(qū)域，然后應(yīng)用通用的結(jié)構(gòu)來(lái)獲取我們想要的結(jié)果。

上述公式構(gòu)造中的Arry4為：

INDEX(Range1,N(IF(1,Arry2)),N(IF(1,Arry3)))

這里，只是簡(jiǎn)單地索引二維區(qū)域中的每個(gè)元素。然而，我們得到的結(jié)果數(shù)組將是一維數(shù)組且包含的元素與二維區(qū)域中的元素完全相同。

為了解構(gòu)Arry4，我們需要首先查看Arry2和Arry3，它們分別對(duì)應(yīng)著INDEX函數(shù)的參數(shù)row_num和參數(shù)column_num。而它們都引用了Arry1：

=ROW(INDIRECT('1:'&COLUMNS(Range1)*ROWS(Range1)))

名稱Range1代表的區(qū)域有4行5列，因此轉(zhuǎn)換為：

ROW(INDIRECT('1:'&5*4))

得到：

{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20}

再看Arry2：

=1+INT((Arry1-1)/COLUMNS(Range1))

轉(zhuǎn)換為：

1+INT(({1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20}-1)/5)

轉(zhuǎn)換為：

1+INT({0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}/5)

轉(zhuǎn)換為：

1+INT({0;0.2;0.4;0.6;0.8;1;1.2;1.4;1.6;1.8;2;2.2;2.4;2.6;2.8;3;3.2;3.4;3.6;3.8})

轉(zhuǎn)換為：

1+{0;0;0;0;0;1;1;1;1;1;2;2;2;2;2;3;3;3;3;3}

得到：

{1;1;1;1;1;2;2;2;2;2;3;3;3;3;3;4;4;4;4;4}

接著看Arry3：

=1+MOD(Arry1-1,COLUMNS(Range1))

轉(zhuǎn)換為：

1+MOD({0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19},5)

轉(zhuǎn)換為：

1+{0;1;2;3;4;0;1;2;3;4;0;1;2;3;4;0;1;2;3;4}

得到：

{1;2;3;4;5;1;2;3;4;5;1;2;3;4;5;1;2;3;4;5}

再回到Arry4?？梢赞D(zhuǎn)換為：

INDEX(Range1,N(IF(1,{1;1;1;1;1;2;2;2;2;2;3;3;3;3;3;4;4;4;4;4})),N(IF(1,{1;2;3;4;5;1;2;3;4;5;1;2;3;4;5;1;2;3;4;5})))

這里使用了強(qiáng)制INDEX返回?cái)?shù)組的技術(shù)，詳情可參閱《Excel公式技巧03：INDEX函數(shù)，給公式提供數(shù)組》。上述公式可轉(zhuǎn)換為：

INDEX(Range1,{1;1;1;1;1;2;2;2;2;2;3;3;3;3;3;4;4;4;4;4},{1;2;3;4;5;1;2;3;4;5;1;2;3;4;5;1;2;3;4;5})

現(xiàn)在應(yīng)該可以看清楚為INDEX函數(shù)的每個(gè)參數(shù)傳遞數(shù)組的原因了，因?yàn)樯鲜龉降葍r(jià)于執(zhí)行下列每個(gè)公式：

INDEX(Range1,1,1)

INDEX(Range1,1,2)

INDEX(Range1,1,3)

INDEX(Range1,1,4)

INDEX(Range1,1,5)

INDEX(Range1,2,1)

INDEX(Range1,2,2)

…

INDEX(Range1,4,5)

因此，Arry4的結(jié)果為：

{'Due';'';'Otto';'';'Otto';'';'';'';'';'Tre';'Sei';'Cinque';'';'Quattro';'Otto';'Due';'';'';'Quattro';'Otto'}

而Excel將Range1解析為：

{'Due','','Otto','','Otto';'','','','','Tre';'Sei','Cinque','','Quattro','Otto';'Due','','','Quattro','Otto'}

我們可以看到這兩個(gè)數(shù)組中的值沒(méi)有任何區(qū)別。唯一不同的是，Range1包含一個(gè)4行5列的二維數(shù)組，而Arry4是通過(guò)簡(jiǎn)單地將Range1中的每個(gè)元素進(jìn)行索引而得出的，實(shí)際上是20行1列的一維區(qū)域。

好了，現(xiàn)在就可以使用我們掌握的常用的適用于一維區(qū)域的技術(shù)來(lái)操作該數(shù)組了！

4. 再看看主公式中的：

INDEX(Arry4,MATCH(SMALL(IF(FREQUENCY(IF(Range1<>'',MATCH(Range1,Arry4,0)),Arry1),COUNTIF(Range1,'<'&Arry4)),ROWS($1:1)),IF(Arry4<>'',COUNTIF(Range1,'<'&Arry4)),0))

先看看這部分：

IF(Range1<>'',MATCH(Range1,Arry4,0))

轉(zhuǎn)換為：

IF({TRUE,FALSE,TRUE,FALSE,TRUE;FALSE,FALSE,FALSE,FALSE,TRUE;TRUE,TRUE,FALSE,TRUE,TRUE;TRUE,FALSE,FALSE,TRUE,TRUE},MATCH(Range1,Arry4,0))

使用Range1和Arry4替換，得到：

IF({TRUE,FALSE,TRUE,FALSE,TRUE;FALSE,FALSE,FALSE,FALSE,TRUE;TRUE,TRUE,FALSE,TRUE,TRUE;TRUE,FALSE,FALSE,TRUE,TRUE},MATCH({'Due','','Otto','','Otto';'','','','','Tre';'Sei','Cinque','','Quattro','Otto';'Due','','','Quattro','Otto'},{'Due';'';'Otto';'';'Otto';'';'';'';'';'Tre';'Sei';'Cinque';'';'Quattro';'Otto';'Due';'';'';'Quattro';'Otto'},0))

可轉(zhuǎn)換為：

IF({TRUE,FALSE,TRUE,FALSE,TRUE;FALSE,FALSE,FALSE,FALSE,TRUE;TRUE,TRUE,FALSE,TRUE,TRUE;TRUE,FALSE,FALSE,TRUE,TRUE},{1,#N/A,3,#N/A,3;#N/A,#N/A,#N/A,#N/A,10;11,12,#N/A,14,3;1,#N/A,#N/A,14,3})

得到：

{1,FALSE,3,FALSE,3;FALSE,FALSE,FALSE,FALSE,10;11,12,FALSE,14,3;1,FALSE,FALSE,14,3}

這個(gè)數(shù)組是FREQUENCY函數(shù)的第一個(gè)參數(shù)，而Arry1是其第二個(gè)參數(shù)：

FREQUENCY(IF(Range1<>'',MATCH(Range1,Arry4,0)),Arry1)

可轉(zhuǎn)換為：

FREQUENCY({1,FALSE,3,FALSE,3;FALSE,FALSE,FALSE,FALSE,10;11,12,FALSE,14,3;1,FALSE,FALSE,14,3},Arry1)

將Arry1代入：

FREQUENCY({1,FALSE,3,FALSE,3;FALSE,FALSE,FALSE,FALSE,10;11,12,FALSE,14,3;1,FALSE,FALSE,14,3},{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20})

生成數(shù)組：

{2;0;4;0;0;0;0;0;0;1;1;1;0;2;0;0;0;0;0;0;0}

這是我們使用的相當(dāng)標(biāo)準(zhǔn)的技術(shù)：上述數(shù)組中非零值的位置表示在該區(qū)域內(nèi)每個(gè)不同值在該數(shù)組中的首次出現(xiàn)，因此提供了一種僅返回唯一值的方法。將該數(shù)組作為IF函數(shù)的條件：

IF(FREQUENCY(IF(Range1<>'',MATCH(Range1,Arry4,0)),Arry1),COUNTIF(Range1,'<'&Arry4))

轉(zhuǎn)換為：

IF({2;0;4;0;0;0;0;0;0;1;1;1;0;2;0;0;0;0;0;0;0},COUNTIF(Range1,'<'&Arry4))

COUNTIF函數(shù)用于確定字母排序：

IF({2;0;4;0;0;0;0;0;0;1;1;1;0;2;0;0;0;0;0;0;0},{1;0;3;0;3;0;0;0;0;10;9;0;0;7;3;1;0;0;7;3})

結(jié)果為：

{1;FALSE;3;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;10;9;0;FALSE;7;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

這樣，INDEX函數(shù)部分現(xiàn)在變成：

INDEX(Arry4,MATCH(SMALL({1;FALSE;3;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;10;9;0;FALSE;7;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},ROWS($1:1)),IF(Arry4<>'',COUNTIF(Range1,'<'&Arry4)),0))

對(duì)于SMALL函數(shù)，其參數(shù)k的值由ROWS($1:1)指定，在單元格G1中為1，因此上述公式轉(zhuǎn)換為：

INDEX(Arry4,MATCH(0,IF(Arry4<>'',COUNTIF(Range1,'<'&Arry4)),0))

轉(zhuǎn)換為：

INDEX(Arry4,MATCH(0,IF(Arry4<>'',{1;0;3;0;3;0;0;0;0;10;9;0;0;7;3;1;0;0;7;3},0))

轉(zhuǎn)換為：

INDEX(Arry4,MATCH(0,{1;FALSE;3;FALSE;3;FALSE;FALSE;FALSE;FALSE;10;9;0;FALSE;7;3;1;FALSE;FALSE;7;3},0))

轉(zhuǎn)換為：

INDEX(Arry4,12)

將Arry4代入：

INDEX({'Due';'';'Otto';'';'Otto';'';'';'';'';'Tre';'Sei';'Cinque';'';'Quattro';'Otto';'Due';'';'';'Quattro';'Otto'},12)

得到結(jié)果：

Cinque

小結(jié)：

本文至少?gòu)?fù)習(xí)/使用了以下公式技術(shù)：

1. 統(tǒng)計(jì)列表區(qū)域中唯一值數(shù)量。

2. 將二維區(qū)域轉(zhuǎn)換成一維區(qū)域。

3. 強(qiáng)制INDEX返回?cái)?shù)組。

4. 確定字母排序。

5. 提取唯一值并按字母排序。

現(xiàn)在我越來(lái)越相信公式無(wú)所不能了！