社区首页 >专栏 >Excel公式练习38：求一列中的数字剔除掉另一列中的数字后剩下的数字

Excel公式练习38：求一列中的数字剔除掉另一列中的数字后剩下的数字

fanjy

发布于 2020-02-26 06:07:19

3.7K0

文章被收录于专栏：完美Excel完美Excel

导语：继续研究来自于excelxor.com的案例。这个案例看似简单，然而实现起来却比较难，这里面用到的技巧值得学习。

本次的练习是：如下图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))

返回数字：

这正是我们需要的。

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

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