湖南龙网

  1. 首页 > 生活百科 > >

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

小知识生活百科

Excel公式： 获取非连续单元格区域中只出现一次的数字
本次的练习是：如下图1所示，有一组非连续的单元格区域，由任意数量的单列区域组成，每个区域中的值有数字、文本或空格 。要求从单元格A2开始，使用公式生成一个列表，这个列表由上述非连续单元格区域中所有只出现了一次的数字组成（如图1所示，1、2和9这三个数字在非连续的单元格区域中只出现了一次） 。

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

文章插图
图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(INDIRECT(“1:” &MAX(RNG)+1))-1)=1))
计算该非连续单元格区域中满足要求的数字数量 。
公式解析
公式中的RNG是定义的名称 。
名称：RNG
引用位置：=$C$2:$C$7,$E$2:$E$4,$E$6:$E$9,$G$3:$G$11,$J$1:$J$9,$L$5:$L$6,$N$3:$N$7,$N$9:$N$10,$N$12
注意，这个联合的单元格区域并不能传递给所有的工作表函数，但还是有些工作表函数能够处理它们 。
1. 首先，看看单元格A1中返回满足要求的数字数量的公式：
=SUMPRODUCT(–(FREQUENCY(RNG,ROW(INDIRECT(“1:”&MAX(RNG)+1))-1)=1))
这里的重点是使用FREQUENCY函数，该函数是非常有用的一个函数，能够处理这种不连续的单元格区域 。
另一个函数是MAX函数，也可以操作多个、非连续的单元格区域，因此：
MAX(RNG)
能够得到组成RNG的单元格区域中所有数值的最大值，忽略逻辑值、文本 。很显然，其返回的结果是9 。
这样，公式中的：
ROW(INDIRECT(“1:”&MAX(RNG)+1))-1
转换成：
ROW(INDIRECT(“1:”&9+1))-1
转换成：
ROW(INDIRECT(“1:”&10))-1
转换成：
{1;2;3;4;5;6;7;8;9;10}-1
结果为：
【Excel公式： 获取非连续单元格区域中只出现一次的数字】{0;1;2;3;4;5;6;7;8;9}
这里，我们创建了一个由0到区域中最大值的数值组成的数组，用于FREQUENCY函数的参数bins_array 。
此时，公式中的：
FREQUENCY(RNG,ROW(INDIRECT(“1:”&MAX(RNG)+1))-1)
成为：
FREQUENCY(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(INDIRECT(“1:”&MAX(RNG)+1))-1)=1))
可转换为：
=SUMPRODUCT(–({0;1;1;0;0;5;2;2;0;1;0}=1))
转换为：
=SUMPRODUCT(–({FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE}))
两个减号强迫TRUE/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(INDIRECT(“1:”& MAX(RNG)+1))-1)/(FREQUENCY(RNG,ROW(INDIRECT(“1:” &MAX(RNG)+1))-1)=1),ROWS(A$2:A2))
可以转换为：
AGGREGATE(15,6,({0;1;2;3;4;5;6;7;8;9})/({FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE}),ROWS(A$2:A2))

推荐阅读


上一篇：Excel公式技巧：十进制数转换成指定进制的数

下一篇：Excel公式技巧：从字符串中提取数字——数字位于字符串开头