excel公式教程：如下圖1所示，在單元格區(qū)域A2:A12和B2:B12中給定兩列數(shù)字，要在列C中從單元格C2開始生成一列數(shù)字。規(guī)則如下：
1. 列B中的數(shù)字的數(shù)量要小于等于列A中數(shù)字的數(shù)量。
2. 列B中的任意數(shù)字都可以在列A中找到。
3. 在列A或列B已存放數(shù)字的單元格之間不能有任何空單元格。
4. 在列C中的數(shù)字是從列A中的數(shù)字移除列B中的數(shù)字在列A中第一次出現(xiàn)的數(shù)字后剩下的數(shù)字。
5. 換句話說(shuō)，列B和列C中的數(shù)字合起來(lái)就是列A中的數(shù)字。
圖1
在單元格D1中的數(shù)字等于列A中的數(shù)字?jǐn)?shù)量減去列B中的數(shù)字?jǐn)?shù)量后的值，也就是列C中數(shù)字的數(shù)量。
現(xiàn)在，要在單元格C2中編寫一個(gè)公式，然后下拉至單元格C12，得到如上圖1所示的結(jié)果。
那么，如何編寫這個(gè)公式呢？
先不看答案，自已動(dòng)手試一試。
公式
在單元格C2中輸入數(shù)組公式：
=IF(ROWS($1:1)>$D$1,””,SMALL(IF(1-ISNUMBER(MATCH(List1+(COUNTIF(OFFSET(INDEX(List1,1,1),Arry1,,Arry2,),List1)/10^6),List2+(COUNTIF(OFFSET(INDEX(List2,1,1),Arry1,,Arry2,),List2)/10^6),0)),List1),ROWS($1:1)))
向下拖拉至單元格C12。
公式解析
這個(gè)案例中，存在的最大障礙是列表中的值有重復(fù)，如果能夠消除這個(gè)障礙，那么就好辦了。公式的思路就是構(gòu)造一個(gè)數(shù)組，能夠?qū)崿F(xiàn)在List1和List2之間執(zhí)行MATCH函數(shù)查找時(shí)，列C中的數(shù)值就是找不到的值，返回FALSE。
然而，實(shí)現(xiàn)起來(lái)并不是想像中的那么簡(jiǎn)單。我們必須首先確保生成的值是唯一的，并且仍然可以通過(guò)某種方式與原始值相對(duì)應(yīng)，從而提取出原始值。
公式中的List1、List2、Arry1和Arry2是定義的四個(gè)名稱。
名稱：List1
引用位置：=$A$2:$A$12
名稱：List2
引用位置：=$B$2:$B$12
名稱：Arry1
引用位置：=ROW(List1)-MIN(ROW(List1))
名稱：Arry2
引用位置：=ROWS(List1)-ROW(List1)+MIN(Row(List1))
在單元格D1中，使用下面的公式確定列C中要返回的數(shù)字?jǐn)?shù)量：
=COUNT(List1)-COUNT(List2)
1. 在公式中IF子句的第一部分：
IF(ROWS($1:1)>$D$1,””
非常直觀，如果公式向下拖放后ROWS函數(shù)的值大于7，則返回空。
重點(diǎn)在IF子句的第二部分，即其判斷條件為FALSE的部分。
2. 看看公式中的COUNTIF函數(shù)部分：
COUNTIF(OFFSET(INDEX(List1,1,1),Arry1,,Arry2,),List1)
其中：
（1）INDEX(List1,1,1)
返回對(duì)List1中的第1個(gè)單元格的引用，示例中為單元格A2。
（2）OFFSET函數(shù)中的參數(shù)rows和height分別是Arry1和Arry2?，F(xiàn)在看看這兩個(gè)名稱。
對(duì)于Arry1：
=ROW(List1)-MIN(ROW(List1))
轉(zhuǎn)換為：
{2;3;4;5;6;7;8;9;10;11;12}-MIN({2;3;4;5;6;7;8;9;10;11;12})
轉(zhuǎn)換為：
{2;3;4;5;6;7;8;9;10;11;12}-2
得到：
{0;1;2;3;4;5;6;7;8;9;10}
對(duì)于Arry2：
=ROWS(List1)-ROW(List1)+MIN(Row(List1))
轉(zhuǎn)換為：
11-{2;3;4;5;6;7;8;9;10;11;12}+MIN({2;3;4;5;6;7;8;9;10;11;12})
轉(zhuǎn)換為：
11-{2;3;4;5;6;7;8;9;10;11;12}+2
得到：
{11;10;9;8;7;6;5;4;3;2;1}
（3）現(xiàn)在，上述COUNTIF函數(shù)部分變?yōu)椋?/p>
COUNTIF(OFFSET(A2,{0;1;2;3;4;5;6;7;8;9;10},,{11;10;9;8;7;6;5;4;3;2;1},),List1)
可以看到，傳遞了兩個(gè)含有11個(gè)值的數(shù)組分別作為OFFSET函數(shù)的rows參數(shù)和height參數(shù)，這意味著我們給COUNTIF函數(shù)傳遞了11個(gè)單獨(dú)的單元格區(qū)域。
第一個(gè)區(qū)域通過(guò)單元格A2偏移0行為起點(diǎn)、高度為11行組成，即為單元格A2:A12；第二個(gè)區(qū)域通過(guò)單元格A2偏移1行為起點(diǎn)、高度為10行組成，即為單元格A3:A12；第三個(gè)區(qū)域?yàn)锳4:A12；第四個(gè)區(qū)域?yàn)锳5:A12；依此類推，第11個(gè)區(qū)域?yàn)閱卧馎12。
對(duì)應(yīng)于這11個(gè)單元格區(qū)域中的每個(gè)區(qū)域，傳遞給COUNTIF函數(shù)的第2個(gè)參數(shù)criteria是Arry1中11個(gè)數(shù)組元素相應(yīng)位置的值，因此，上述COUNTIF函數(shù)部分實(shí)際上執(zhí)行下列公式運(yùn)算：
=COUNTIF(A2:A12,A2)
=COUNTIF(A3:A12,A3)
=COUNTIF(A4:A12,A4)
…
=COUNTIF(A12:A12,A12)
得到數(shù)組：
{2;1;1;3;2;1;2;1;1;2;1}
這里，我們已成功生成一系列數(shù)值，可幫助我們來(lái)區(qū)分List1中相同的數(shù)字。
3. 此時(shí)，公式中的部分：
List1+(COUNTIF(OFFSET(INDEX(List1,1,1),Arry1,,Arry2,),List1)/10^6)
轉(zhuǎn)換為：
List1+({2;1;1;3;2;1;2;1;1;2;1}/10^6)
轉(zhuǎn)換為：
List1+({0.000002;0.000001;0.000001;0.000003;0.000002;0.000001;0.000002;0.000001;0.000001;0.000002;0.000001})
轉(zhuǎn)換為：
{1;1;2;3;3;3;4;4;5;6;6}+({0.000002;0.000001;0.000001;0.000003;0.000002;0.000001;0.000002;0.000001;0.000001;0.000002;0.000001})
結(jié)果為：
{1.000002;1.000001;2.000001;3.000003;3.000002;3.000001;4.000002;4.000001;5.000001;6.000002;6.000001}
雖然只是將List1中的各個(gè)數(shù)字增加了非常小的數(shù)字，但構(gòu)成的數(shù)組中的每個(gè)元素都是唯一的。例如，在原來(lái)的List1中有三個(gè)3，現(xiàn)在變成了3.000001、3.000002和3.000003。
注意，這里區(qū)分這些List1中數(shù)字的小增量不只是隨機(jī)的，相反，它們將計(jì)數(shù)每個(gè)元素的數(shù)量。例如，如果在生成的數(shù)組中整數(shù)部分為3的最大值為3.000003，那么我們知道List1中應(yīng)該恰好有3個(gè)元素3。類似地，該數(shù)組中整數(shù)部分為2的最大值為2.000001，這告訴我們List1中只有1個(gè)元素1。
4. 在List2中執(zhí)行相同的操作：
List2+(COUNTIF(OFFSET(INDEX(List2,1,1),Arry1,,Arry2,),List2)/10^6)
轉(zhuǎn)換為：
{1;3;3;6;0;0;0;0;0;0;0}+(COUNTIF(OFFSET($B$2,{0;1;2;3;4;5;6;7;8;9;10},,{11;10;9;8;7;6;5;4;3;2;1},),{1;3;3;6;0;0;0;0;0;0;0})/10^6)
轉(zhuǎn)換為：
{1;3;3;6;0;0;0;0;0;0;0}+({1;2;1;1;0;0;0;0;0;0;0}/10^6)
最后得到的結(jié)果為：
{1.000001;3.000002;3.000001;6.000001;0;0;0;0;0;0;0}
這樣，將原來(lái)List2中的元素轉(zhuǎn)換成了由唯一值構(gòu)成的數(shù)組。
5. 現(xiàn)在，可以使用MATCH函數(shù)來(lái)比較這兩個(gè)數(shù)組。IF語(yǔ)句中為FALSE的部分：
SMALL(IF(1-ISNUMBER(MATCH(List1+(COUNTIF(OFFSET(INDEX(List1,1,1),Arry1,,Arry2,),List1)/10^6),List2+(COUNTIF(OFFSET(INDEX(List2,1,1),Arry1,,Arry2,),List2)/10^6),0)),List1),ROWS($1:1))
可以轉(zhuǎn)換為：
SMALL(IF(1-ISNUMBER(MATCH({1.000002;1.000001;2.000001;3.000003;3.000002;3.000001;4.000002;4.000001;5.000001;6.000002;6.000001},{1.000001;3.000002;3.000001;6.000001;0;0;0;0;0;0;0},0)),List1),ROWS($1:1))
轉(zhuǎn)換為：
SMALL(IF(1-ISNUMBER({#N/A;1;#N/A;#N/A;2;3;#N/A;#N/A;#N/A;#N/A;4}),List1),ROWS($1:1))
轉(zhuǎn)換為：
SMALL(IF(1-{FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE},List1),ROWS($1:1))
轉(zhuǎn)換為：
SMALL(IF({1;0;1;1;0;0;1;1;1;1;0},{1;1;2;3;3;3;4;4;5;6;6}),ROWS($1:1))
轉(zhuǎn)換為：
SMALL({1;FALSE;2;3;FALSE; FALSE;4;4;5;6; FALSE },ROWS($1:1))
返回?cái)?shù)字：
1
這正是我們需要的。
單元格C3中的公式會(huì)轉(zhuǎn)換為：
SMALL({1;FALSE;2;3;FALSE; FALSE;4;4;5;6; FALSE },ROWS($1:2))
返回?cái)?shù)字：
2
依此類推。
本案例關(guān)鍵技術(shù)：將統(tǒng)計(jì)數(shù)分配給單元格區(qū)域中的每個(gè)值，有效地將含有重復(fù)值的單元格區(qū)域中的值變成唯一值，這是一項(xiàng)很有用的技術(shù)。
