统计Excel单元格区域中无效数据数量

时间：23-06-17 16:13:36

作者：

　　如下图所示的工作表，使用公式来统计单元格区域C2:C6中没有出现在单元格区域A2:A9中的数据的数量。

　　统计Excel单元格区域中无效数据数量插图

　　也就是说，单元格区域C2:C6中凡是没有出现在单元格区域A2:A9中的数据，都是无效数据。从工作表中可以明显看出，单元格区域C2:C6中的“SX006”没有出现在单元格区域A2:A9中，因此“SX006”为无效数据，即单元格区域C2:C6中的无效数据为1。

　　如何使用公式来统计呢？

　　先不看答案，自已动手试一试。

　　公式思路

　　在单元格区域A2:A9中查找单元格区域C2:C6中的每个值，统计没有找到的值的数量。

　　公式解析

　　在单元格E1中输入下面的数组公式：

　　=SUM(1*ISNA(MATCH(C2:C6,A2:A9,0)))

　　其值为1，表明单元格区域C2:C6中没有出现在单元格区域A2:A9中的数据数为1。如下图所示。

　　统计Excel单元格区域中无效数据数量插图1

　　公式中，MATCH函数在单元格区域A2:A9中依次查找C2:C6中的值，返回各个值在A2:A9中的位置，如果没有找到则返回#N/A，结果为{2;6;2;8;#N/A}，将其作为ISNA函数的参数，得到结果{FALSE;FALSE;FALSE;FALSE;TRUE}，然后与1相乘，将其转换为{0;0;0;0;1}，作为SUM函数的参数得到结果1。

　　ISNA函数检测一个值是否为#N/A，返回TRUE或FALSE。检测值可以是一个单元格、公式，或者是一个单元格、公式或数值的名称。

　　小结

　　使用1与布尔值相乘，将布尔值转换为0或1。

　　本例具有一定的实用性，即可以用于判断输入的数据是否是数据库已经规定的数据。例如，代表设备编码库的单元格区域A2:A9是已经规定的合法数据，而单元格区域C2:C6是实际工作中输入的数据，那么可以用这个公式来判断实际输入的数据是否是已规定的合法数据。

　　可以使用名称来替换单元格区域，使公式更灵活。