[第121期]多区域中连续数字的个数[已总结]

wangg913

题目截至，开贴，禁止进行修改。

wangg913

365版的公式，暂时未测试。

答案汇总附件：

mmlzh

恭喜各位老师，都赚到3分了，绝大多数都在200字符内哦，可喜可贺。

Look我在这

两个COUNTIF是败笔，
向各位学习了。
^._.^

太阳之子

wangg913 发表于 2018-4-15 20:31
365版的公式，暂时未测试。

答案汇总附件：

经测试，365版本的公式结果都正确。

太阳之子

学习了各位03版公式，谢谢大家。自从有了365版，我的脑子退化了，03版都不会了。

wangg913

评分完毕。16楼的 U7 单元格结果错误，未得分。
其他楼层的公式大同小异，简化比较好的是 4楼 LSL1128xgw 和 5楼 象山海鲜。

如果单纯对某一区域求多个连续数字的个数，相对容易一些。
例如，求 B2:O15 中有多少个连续数字，公式如下：

1. =SUM(N(FREQUENCY(ROW($101:$600),IF(FREQUENCY(B$2:O$15,ROW($1:$99))<COLUMN(A:E),COLUMN(A:E)*100+ROW($1:$100)))>2))

复制代码

这种用法已经比较常用，但是多区域合起来，就麻烦一些。因此，本题目主要考核引用和区域的复杂运用。

前一段时间，写过一个帖子，简述一下引用的主要形式。
http://club.excelhome.net/thread-1406406-1-1.html

常说的引用，就是矩形区域，例如：B2:O15，是一个14行、14列的矩形区域，代表左上角单元格 B2 和右下角单元格 O15 之间对角线所在的矩形区域，然后使用区域运算符“：”结合起来的。

一、引用运算符
引用运算符包括，区域运算符“:”、联合运算符“,”和交集运算符“ ”三种。
1、区域运算符

2、联合运算符
使用“,”将多个矩形区域集合在一起，形式如：

1. (B2:O15,C19:O21)

复制代码

其中的区域可以行列数不相同，因此这其实是一个区域集合。
能够将联合引用作为参数的函数，多为统计函数，例如：MAX、MIN、LARGE、SMALL 和 FREQUENCY，和部分数学函数，例如：SUM、SUMSQ 和 AGGREGATE。

3、交集运算符
使用空格“ ”运算符可以求出两个及以上矩形区域的交集，例如：

1. (B2:L12 H3:I14)

复制代码

这个公式可以去掉括号，代表2个区域的交集，即：H3:I12。

二、公式生成的引用
1、简单示例
无论是普通引用、联合引用和交集引用中的单个矩形区域，都可以使用公式生成。例如：

1. (H3:I14,INDEX(C:O,MATCH(S3,B:B,),))

复制代码

代表 H3:I14 和 C19:O19 组成的联合引用.

2、生成引用的函数都有哪些？
我们经常使用 INDEX、INDIRECT、OFFSET 这三个函数生成引用。例如：
INDIRECT("A1:A4")
INDEX(A1:B4,,1)
OFFSET(A1,,,4)
INDEX(A:A,3):B5
INDEX(A:A,3):OFFSET(B1,4,)
(OFFSET(A1,,,2,2),A3,INDIRECT("A5:C5"))
(INDEX(A:A,1):B2,A3,INDEX(A:C,5,))
(A1:B8 A3:INDEX(D:D,5))

3、IF 和 CHOOSE 函数
还有两个函数也可以生成引用，它们是 IF 和 CHOOSE ，因为很少用到，常常被大家忽略.

对于 IF 生成的引用，下面的帖子（2楼）很早之前就提到了。
http://club.excelhome.net/thread-494347-1-1.html
当然，如果单纯使用 =IF(1,A1:A3) ，不具有应用意义。

IF(logical_test,value_if_true,value_if_false)
IF 函数的第一个参数是 logical_test ，只要它不是数组，公式结果则会保留原参数的数据类型。
因此，在公式 “=IF(1,A1:A3)”中， logical_test = 1，value_if_true = A1:A3，IF(1,A1:A3) = A1:A3，结果是个引用，不是数组。
如果公式生成的是引用，就可以用使用“引用运算符”进行运算，也可以用于专门将引用做参数的函数。

CHOOSE 函数的情况和 IF 函数类似。

三、解决本题
应用到本题，求 A1 区域和 A2 区域的交叉区域，可以用如下公式生成：

1. (CHOOSE(Q3,B$2:L$12,,F$3:O$7,D$8:K$15) IF(R3="",B:O,H$3:I$14))

复制代码

再求 A1 区域和 A2 区域的交叉区域与B区域的合集，可以用如下公式：

1. (CHOOSE(Q3,B$2:L$12,,F$3:O$7,D$8:K$15) IF(R3="",B:O,H$3:I$14),INDEX(C:O,MATCH(S3,B:B,),)

复制代码

区域合集求出来了，再带入之前的公式。

1. =SUM(N(FREQUENCY(ROW($101:$600),IF(FREQUENCY((CHOOSE(Q3,B$2:L$12,,F$3:O$7,D$8:K$15) IF(R3="",B:O,H$3:I$14),INDEX(C:O,MATCH(S3,B:B,),)),ROW($1:$99))<COLUMN(A:E),COLUMN(A:E)*100+ROW($1:$100)))>2))

复制代码

alan57

wangg913 发表于 2018-4-16 12:06
评分完毕。16楼的 U7 单元格结果错误，未得分。
其他楼层的公式大同小异，简化比较好的是 4楼 LSL1128xgw  ...

太棒了，学习，