ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
让更多数据处理,一键完成 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
123
返回列表 发新帖
楼主: wangg913

[Excel 函数与公式] [第121期]多区域中连续数字的个数[已总结]

[复制链接]

TA的精华主题

TA的得分主题

 楼主| 发表于 2018-4-15 16:16 | 显示全部楼层
题目截至,开贴,禁止进行修改。

TA的精华主题

TA的得分主题

 楼主| 发表于 2018-4-15 20:31 | 显示全部楼层
本帖最后由 wangg913 于 2018-4-15 20:43 编辑

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

答案汇总附件:

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有账号?免费注册

x

评分

4

查看全部评分

TA的精华主题

TA的得分主题

发表于 2018-4-15 21:57 | 显示全部楼层
恭喜各位老师,都赚到3分了,绝大多数都在200字符内哦,可喜可贺。

TA的精华主题

TA的得分主题

发表于 2018-4-16 08:28 | 显示全部楼层
两个COUNTIF是败笔,
向各位学习了。
^._.^

TA的精华主题

TA的得分主题

发表于 2018-4-16 09:51 | 显示全部楼层
wangg913 发表于 2018-4-15 20:31
365版的公式,暂时未测试。

答案汇总附件:

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

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2018-4-16 09:59 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
学习了各位03版公式,谢谢大家。自从有了365版,我的脑子退化了,03版都不会了。

TA的精华主题

TA的得分主题

 楼主| 发表于 2018-4-16 12:06 | 显示全部楼层
本帖最后由 wangg913 于 2018-4-16 13:33 编辑

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

如果单纯对某一区域求多个连续数字的个数,相对容易一些。
例如,求 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))
复制代码

评分

7

查看全部评分

TA的精华主题

TA的得分主题

发表于 2023-5-17 13:56 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
wangg913 发表于 2018-4-16 12:06
评分完毕。16楼的 U7 单元格结果错误,未得分。
其他楼层的公式大同小异,简化比较好的是 4楼 LSL1128xgw  ...

太棒了,学习,
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-7-13 20:44 , Processed in 0.036725 second(s), 7 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表