excel公式教程：如下图1所示在单元格区域A2:A12和B2:B12中给定两列数字要在列C中从单元格C2开始生成一列数字。规则如下：

1. 列B中的数字的数量要小于等于列A中数字的数量。

2. 列B中的任意数字都可以在列A中找到。

3. 在列A或列B已存放数字的单元格之间不能有任何空单元格。

4. 在列C中的数字是从列A中的数字移除列B中的数字在列A中第一次出现的数字后剩下的数字。

5. 换句话说列B和列C中的数字合起来就是列A中的数字。

图1

在单元格D1中的数字等于列A中的数字数量减去列B中的数字数量后的值也就是列C中数字的数量。

现在要在单元格C2中编写一个公式然后下拉至单元格C12得到如上图1所示的结果。

那么如何编写这个公式呢？

先不看答案自已动手试一试。

公式

在单元格C2中输入数组公式：

=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。

公式解析

这个案例中存在的最大障碍是列表中的值有重复如果能够消除这个障碍那么就好办了。公式的思路就是构造一个数组能够实现在List1和List2之间执行MATCH函数查找时列C中的数值就是找不到的值返回FALSE。

然而实现起来并不是想像中的那么简单。我们必须首先确保生成的值是唯一的并且仍然可以通过某种方式与原始值相对应从而提取出原始值。

公式中的List1、List2、Arry1和Arry2是定义的四个名称。

名称：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中要返回的数字数量：

=COUNT(List1)-COUNT(List2)

1. 在公式中IF子句的第一部分：

IF(ROWS($1:1)>$D$1,””

非常直观如果公式向下拖放后ROWS函数的值大于7则返回空。

重点在IF子句的第二部分即其判断条件为FALSE的部分。

2. 看看公式中的COUNTIF函数部分：

COUNTIF(OFFSET(INDEX(List1,1,1),Arry1,,Arry2,),List1)

其中：

（1）INDEX(List1,1,1)

返回对List1中的第1个单元格的引用示例中为单元格A2。

（2）OFFSET函数中的参数rows和height分别是Arry1和Arry2。现在看看这两个名称。

对于Arry1：

=ROW(List1)-MIN(ROW(List1))

转换为：

{2;3;4;5;6;7;8;9;10;11;12}-MIN({2;3;4;5;6;7;8;9;10;11;12})

转换为：

{2;3;4;5;6;7;8;9;10;11;12}-2

得到：

{0;1;2;3;4;5;6;7;8;9;10}

对于Arry2：

=ROWS(List1)-ROW(List1)+MIN(Row(List1))

转换为：

11-{2;3;4;5;6;7;8;9;10;11;12}+MIN({2;3;4;5;6;7;8;9;10;11;12})

转换为：

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）现在上述COUNTIF函数部分变为：

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)

可以看到传递了两个含有11个值的数组分别作为OFFSET函数的rows参数和height参数这意味着我们给COUNTIF函数传递了11个单独的单元格区域。

第一个区域通过单元格A2偏移0行为起点、高度为11行组成即为单元格A2:A12；第二个区域通过单元格A2偏移1行为起点、高度为10行组成即为单元格A3:A12；第三个区域为A4:A12；第四个区域为A5:A12；依此类推第11个区域为单元格A12。

对应于这11个单元格区域中的每个区域传递给COUNTIF函数的第2个参数criteria是Arry1中11个数组元素相应位置的值因此上述COUNTIF函数部分实际上执行下列公式运算：

=COUNTIF(A2:A12,A2)

=COUNTIF(A3:A12,A3)

=COUNTIF(A4:A12,A4)

…

=COUNTIF(A12:A12,A12)

得到数组：

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

这里我们已成功生成一系列数值可帮助我们来区分List1中相同的数字。

3. 此时公式中的部分：

List1+(COUNTIF(OFFSET(INDEX(List1,1,1),Arry1,,Arry2,),List1)/10^6)

转换为：

List1+({2;1;1;3;2;1;2;1;1;2;1}/10^6)

转换为：

List1+({0.000002;0.000001;0.000001;0.000003;0.000002;0.000001;0.000002;0.000001;0.000001;0.000002;0.000001})

转换为：

{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})

结果为：

{1.000002;1.000001;2.000001;3.000003;3.000002;3.000001;4.000002;4.000001;5.000001;6.000002;6.000001}

虽然只是将List1中的各个数字增加了非常小的数字但构成的数组中的每个元素都是唯一的。例如在原来的List1中有三个3现在变成了3.000001、3.000002和3.000003。

注意这里区分这些List1中数字的小增量不只是随机的相反它们将计数每个元素的数量。例如如果在生成的数组中整数部分为3的最大值为3.000003那么我们知道List1中应该恰好有3个元素3。类似地该数组中整数部分为2的最大值为2.000001这告诉我们List1中只有1个元素1。

4. 在List2中执行相同的操作：

List2+(COUNTIF(OFFSET(INDEX(List2,1,1),Arry1,,Arry2,),List2)/10^6)

转换为：

{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)

转换为：

{1;3;3;6;0;0;0;0;0;0;0}+({1;2;1;1;0;0;0;0;0;0;0}/10^6)

最后得到的结果为：

{1.000001;3.000002;3.000001;6.000001;0;0;0;0;0;0;0}

这样将原来List2中的元素转换成了由唯一值构成的数组。

5. 现在可以使用MATCH函数来比较这两个数组。IF语句中为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))

可以转换为：

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))

转换为：

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))

转换为：

SMALL(IF(1-{FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE},List1),ROWS($1:1))

转换为：

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))

转换为：

SMALL({1;FALSE;2;3;FALSE; FALSE;4;4;5;6; FALSE },ROWS($1:1))

返回数字：

1

这正是我们需要的。

单元格C3中的公式会转换为：

SMALL({1;FALSE;2;3;FALSE; FALSE;4;4;5;6; FALSE },ROWS($1:2))

返回数字：

2

依此类推。

本案例关键技术：将统计数分配给单元格区域中的每个值有效地将含有重复值的单元格区域中的值变成唯一值这是一项很有用的技术。