求excel用公式计算遗漏值的方法

Question

如题所述

Answer 1

在 Excel 中，可以使用 COUNTIFS 函数和 Excel 的数组公式来计算遗漏值。

首先，要根据需要创建一个数据范围，例如 A1:A10，其中包含我们要查找遗漏值的数据。假设我们要查找1到5之间的数字。

1. 在 B1 单元格中输入 1，表示从数字 1 开始查找。

2. 在 B2 单元格中输入以下计数公式，并按 Ctrl + Shift + Enter：

=COUNTIFS(A$1:A$10,"<"&B1+1,A$1:A$10,">="&B1)

注意: 把区间的引用写成绝对引用，防止填充公式时引用错位。上面的公式解释如下：

a) A$1:A$10：“<” & B1 + 1：这部分COUNTIFS函数指定要查找的区间和条件。使用“ <”符号将区间中小于下一个数字的单元格筛选出来，以此来统计在下一个数字之前的数字数目。

b) A$1:A$10：“>=”& B1：这部分COUNTIFS函数统计符合大于或等于当前数字条件的单元格数量，以此来确定数字是否已出现在数据区间中。

3. 将B2单元格的公式向下拖动填充到B6单元格。

4. 最后，在 C1:C5单元格中，输入以下公式计算遗漏值：

=IF(B1="",0,B1-MAX($B$1:B1))

注意：把列的引用写成绝对引用，防止填充公式时引用错位。上述公式的解释：

a) IF 函数判断当前单元格是否为空。如果为空，则将遗漏值设为0。

b) 如果当前单元格不为空，则计算上一批数字和当前数字之间的遗漏值。追问

Answer 2

要用代码的自定义函数才好。