Excel公式:获取在不连续单元格区域只出现一次的数字

Excel公式：获取在不连续单元格区域只出现一次的数字。

这个练习是：如下图1所示，有一组不连续的单元格区域，由任意数量的单列区域组成，每个区域的值是数字、文本或空格。需要使用公式从单元格A2生成一个列表，该列表由在非连续单元格区域中只出现一次的所有数字组成(如图1所示，数字1、2和9在非连续单元格区域中只出现一次)。

图1

注意，虽然图1中单元格区域C1:N12中的许多单元格是空的，但是求解公式应该考虑到这些单元格也可能有数据。

先不看答案，自己试试。

公式

在单元格A2中输入公式：

=IF(ROWS(A$2:A2)$A$1，""，AGGREGATE(15，6)，(ROW(INDIRECT(" 1: " MAX(RNG)1))-1)/(FREQUENCY(RNG，ROW(INDIRECT(" 1: " MAX(RNG)1))-1)=1)，ROWS(A$2:A2))

向下拉，直到出现一个空单元格。

在单元格A1中，公式为：

=SUMPRODUCT((FREQUENCY(RNG，ROW(internal)(“1:”MAX(RNG)1))-1)=1))

计算不连续单元格区域中满足要求的数字数量。

公式分析

公式中的RNG就是这个定义的名字。

姓名：RNG。

参考位置：=$ c $2: $ c $7，$ e $2: $ e $4，$ e $6: $ e $9，$ g $: $ g $11，$ j $1: $ j $9，$ l。

请注意，此组合单元格区域不能传递给所有工作表函数，但某些工作表函数可以处理它们。

1.首先，查看单元格A1中返回所需位数的公式：

=SUMPRODUCT((FREQUENCY(RNG，ROW(internal)(“1:”MAX(RNG)1))-1)=1))

这里的重点是使用FREQUENCY函数，这是一个非常有用的函数，可以处理这个不连续的单元格区域。

另一个功能是MAX功能，也可以操作多个不连续的小区区域。因此：

马克斯(RNG)

它可以获得RNG单元格区域中所有数值的最大值，忽略逻辑值和文本。显然，返回结果是9。

这样，在公式中：

行(间接(“1:”最大值(RNG) 1))-1

转换为：

行(间接(" 1:"9 1))-1

转换为：

行(间接(“1:”10))-1

转换为：

{1;2;3;4;5;6;7;8;9;10}-1

结果是：

{0;1;2;3;4;5;6;7;8;9}

这里，我们创建了一个由区域中从0到最大值的值组成的数组，它被用作FREQUENCY函数的参数bins_array。

此时，在公式中：

频率(RNG，世界其他地区(间接)(“1:”MAX(RNG)1))-1)

变成：

频率(RNG，{ 0；1;2;3;4;5;6;7;8;9})

结果是：

{0;1;1;0;0;5;2;2;0;1;0}

因此，公式为：

=SUMPRODUCT((FREQUENCY(RNG，ROW(internal)(“1:”MAX(RNG)1))-1)=1))

可转换为：

=SUMPRODUCT(({ 0；1;1;0;0;5;2;2;0;1;0}=1))

转换为：

=SUMPRODUCT(({ FALSE；真；真；假；假；假；假；假；假；真；FALSE}))

两个负号迫使真/假转换为1/0，即：

=SUMPRODUCT({ 0；1;1;0;0;0;0;0;0;1;0})

结果是3。

2.让我们看看用于从单元格A2获取值的公式：

=IF(ROWS(A$2:A2)$A$1，""，AGGREGATE(15，6)，(ROW(INDIRECT(" 1: " MAX(RNG)1))-1)/(FREQUENCY(RNG，ROW(INDIRECT(" 1: " MAX(RNG)1))-1)=1)，ROWS(A$2:A2))

根据前面公式推导出的内容，在上式中：

AGGREGATE(15，6，(ROW(间接)(“1:”MAX(RNG)1))-1)/(FREQUENCY(RNG，ROW(间接)(“1:”MAX(RNG)1))-1)=1)，ROWS(A$2:A2))

可转换为：

AGGREGATE(15，6，({ 0；1;2;3;4;5;6;7;8;9 })/({ FALSE；真；真；假；假；假；假；假；假；真；FALSE})，row(2: a2)

这是以这种方式使用AGGREGATE函数时应该注意的关键技术。因为如果我们在这个函数中将第二个参数选项设置为6，也就是“忽略错误值”，它就会这样做。

上述公式可转换为：

AGGREGATE(15，6，{#DIV/0！1;2;#DIV/0！#DIV/0！#DIV/0！#DIV/0！#DIV/0！#DIV/0！9;#不适用}，行(澳元2:A2))

第一个参数function_num设置为15，相当于执行了SMALL函数。(你可能会想，为什么不把第一个参数设为5，也就是MIN，这样不合适。因为在AGGREGATE函数第一个参数的所有选项中，只有14-15可以保证传递给函数的数组不是实际工作表区域时的正常操作，这里的数组是由其他函数生成的，如果设置为1-13中的任意一个，那么传递给函数的数组就是实际工作表区域。)

对于单元格A2的公式，最后一个参数k的值为1，是ROWS的返回值(A$2:A2)。因此，AGGREGATE函数被部分转换为：

AGGREGATE(15，6，{#DIV/0！1;2;#DIV/0！#DIV/0！#DIV/0！#DIV/0！#DIV/0！#DIV/0！9;#不适用}，1)

结果是1。

这样，单元格A2中的公式转换为：

=IF(1澳元1，“”，1)

那就是：

=IF(13，""，1)

结果是1。