湖南龙网

  1. 首页 > 生活百科 > >

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中的数字 。

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

文章插图
图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个单独的单元格区域 。

推荐阅读


上一篇：洋参对人体有着怎样的功效

下一篇：excel公式教程：TRANSPOSE，非数组版本