excel公式教程求一列中的数字剔除掉另一列中的数字后剩下的数字
发布网友
发布时间：2024-09-26 15:48
共1个回答
热心网友
时间：2024-10-04 09:47
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
依此类推。
本案例关键技术：将统计数分配给单元格区域中的每个值，有效地将含有重复值的单元格区域中的值变成唯一值，这是一项很有用的技术。
热心网友
时间：2024-10-04 09:49
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
依此类推。
本案例关键技术：将统计数分配给单元格区域中的每个值，有效地将含有重复值的单元格区域中的值变成唯一值，这是一项很有用的技术。
