excel公式教程:找到一列的数字去掉另一列的数字

时间：2021-09-28 来源：互联网编辑：宝哥软件园浏览：次

Excel教程：如下图1所示，给定单元格区域A2:A12和B2:B12中的两列数字，应该从C列的单元格C2生成一列数字.规则如下：

1.b列中的数字应小于或等于a列中的数字.

2.b栏的任何数字都可以在a栏找到。

3.A列或B列中存储了数字的单元格之间不能有任何空单元格。

4.c列中的数字是从a列的数字中删除第一次出现在a列中的b列数字后剩下的数字。

5.换句话说，B列和C列的数字加起来就是a列的数字。

图1

单元格D1中的数字等于A列中的数字减去B列中的数字，即c列中的数字

现在，在单元格C2中写一个公式，然后将它放到单元格C12中，得到上面图1所示的结果。

那么，这个公式怎么写呢？

先不看答案，自己试试。

公式

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

=IF(ROWS($1:1)$D$1，""，小(if(1-is number(match(list 1(countif(offset(index(list1,1,1),arry1,arry2,),list1)/10^6)，list 2(countif(offset(index(list2,1,1),arry1,arry2,),list2)/10^6),0)),list1),rows($1:1))

向下拖动到C12单元格。

公式分析

在这种情况下，最大的障碍是列表中的值重复。如果这个障碍能消除，那就容易了。公式的思想是构造一个数组，可以实现在列表1和列表2之间搜索MATCH函数时，C列的值是找不到的值，返回FALSE。

然而，事情并没有想象的那么简单。我们首先要保证生成的值是唯一的，并且还能以某种方式对应原值，这样才能提取原值。

公式中的列表1、列表2、数组1和数组2是定义的四个名称。

名称：列表1。

参考位置：=$ 2:澳元$12澳元。

名称：列表2。

参考位置：=$B$2:$B$12。

名称：Arry1。

参考位置：=行(列表1)-最小(行(列表1))

名称：Arry2。

参考位置：=row(list 1)-row(list 1)min(row(list 1))。

在“D1”单元格中，使用以下公式确定c列中要返回的数字数量：

=计数(列表1)-计数(列表2)

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

IF(ROWS($1:1)$D$1，""

直观地说，如果公式被向下拖动后，ROWS函数的值大于7，它将返回null。

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

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

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

其中：

(1)索引(列表1，1，1)

返回对列表1中第一个单元格的引用，在本例中是单元格A2。

(2)OFFSET函数中的参数行和高度分别为Arry1和Arry2。现在看看这两个名字。

对于Arry1:

=行(列表1)-分钟(行(列表1))

转换为：

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

=行(列表1)-行(列表1)最小值(行(列表1))

转换为：

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}，)，列表1)

如您所见，我们传递了两个包含11个值的数组作为OFFSET函数的row参数和height参数，这意味着我们向COUNTIF函数传递了11个单独的单元格区域。

第一个区域由11行组成，起点从单元格A2偏移0行，单元格A2为单元格A2:A12。第二个区域由单元格A3:A12组成；与单元格A2偏移1行，高度为10行。第三个区域是A4:A12；第四个区域是A5:A12；以此类推，第11个区域是单元格A12。

对应于这11个单元区域中的每一个，传递给COUNTIF函数的第二个参数标准是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}

在这里，我们成功生成了一系列数值，可以帮助我们区分列表1中相同的数字。

3.此时，公式中的部分：

名单1(countif(offset(index(list1,1,1),arry1,arry2,),list1)/10^6)

转换为：

列表1({ 2；1;1;3;2;1;2;1;1;2;1}/10^6)

转换为：

列表1({ 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}

虽然列表1中的每个数字只增加了非常小的数字，但是形成的数组中的每个元素都是唯一的。例如，原来的列表1中有三个3，现在是3.000001、3.000002和3.00003。

请注意，区分列表1中这些小的数字增量不仅仅是随机的。相反，他们计算每个元素的数量。例如，如果生成的数组中整数部分3的最大值是3.000003，那么我们知道列表1中应该正好有三个元素3。同样，这个数组中整数部分2的最大值是2.000001，这告诉我们列表1中只有一个元素1。

4.在列表2中执行相同的操作：

名单2(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}

这样，原始列表2中的元素被转换成由唯一值组成的数组。

5.现在，您可以使用MATCH函数来比较这两个数组。部分为假的IF语句：

小(IF(1-ISNUMBER)(匹配(列表1(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-is number(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))、列表1)、行($1:1))

转换为：

小(如果(1-是数字({ #不适用；1;#不适用；#不适用；2;3;#不适用；#不适用；#不适用；#不适用；4})、列表1)、行(1:1美元))

转换为：

小(如果(1-{假；真；假；假；真；真；假；假；假；假；TRUE}、List1)、ROWS($1:1))

转换为：

小型(中频({ 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；假；2;3;假；假；4;4;5;6;FALSE }，ROWS($1:1))

退货编号：

一个

这正是我们所需要的。

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