excel公式教程: 求一列中的数字剔除掉另一列中的数字后剩下的数字

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

依此类推。

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

赞(82)
office办公分享 » excel公式教程: 求一列中的数字剔除掉另一列中的数字后剩下的数字

本文链接:excel公式教程: 求一列中的数字剔除掉另一列中的数字后剩下的数字https://www.officeapi.cn/85943.html

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

OFFICE,天天学一点,进步多一点

联系我们