导语:继续研究来自于excelxor.com的案例。这个案例不用数组公式就能解决,但仍有很多值得学习的技术。 本次的练习是:如下图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(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 结果为: {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)) 这是以这种方式使用AGGREGATE函数时要注意的关键技术。因为如果我们在此函数中将第二个参数options设置为6,即“忽略错误值”,那么它将恰好做到这一点。 上述公式可转换为: AGGREGATE(15,6,{#DIV/0!;1;2;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;9;#N/A},ROWS(A$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;#N/A},1) 结果为1。 这样,单元格A2中的公式转换为: =IF(1>$A$1,'',1) 即: =IF(1>3,'',1) 结果为1。 小结 本示例展示了解决涉及到非连续单元格区域的问题的技术,以及哪些函数能够处理非连续单元格区域。此外,也讲解了AGGREGATE函数的使用技术。 |
|
来自: hercules028 > 《excel》