ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

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

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

[复制链接]

TA的精华主题

TA的得分主题

发表于 2018-3-15 20:29 | 显示全部楼层 |阅读模式
本帖最后由 wangg913 于 2018-4-16 12:12 编辑

B:O列为数据区,所有数字介于10-90之间,包括A区和B区
使用类别设置,将A区划分为A1、A2两个区域;将B区划分为3行

题意说明:
1、首先,求出A1和A2这两区的交叉区域
2、然后,将交叉区域数据和B区的合并,形成新的合集
3、最后,求合集中一共有多少个连续数字串

详细的说明请见附件,如存在错误表述、错误结果、歧义、题意不清等情况,请给我发信息

竞赛期间:
2018/3/16至2018/4/15

规则及评分:
1、纯公式题目,不允许自定义函数、自定义名称、迭代
2、鉴于题目繁复,审题不易,凡做出与模拟结果相同的,奖励2分技术分
3、公式长度(含等号)小于等于216的,奖励3分技术分


================================
附件说明修订:
A1区,类别为1时,引用B2:L12
================================



A1区,类别为1、3、4时,分别引用B2:L12、F3:O7、D8:K15
A2区,类别为 P 时,引用H3:I14;类别为空,A2区为空
B区,类别为为Y、A、K时,分别引用 C19:O19、C20:O20、C21:O21

还有不明确的情指正。

本帖子中包含更多资源

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

x

评分

11

查看全部评分

TA的精华主题

TA的得分主题

发表于 2018-3-15 21:56 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖最后由 梦幻小丑 于 2018-3-21 15:01 编辑

版主,1代表的区域是不是B2:L12?
  1. =SUM(N(FREQUENCY(ROW(A:A),(FREQUENCY((CHOOSE(Q3,B$2:L$12,,F$3:O$7,D$8:K$15) IF(R3=0,B:O,H$3:I$14),INDEX(C:O,MATCH(S3,B:B,),)),ROW($1:98))<COLUMN(A:Z))*ROW($1:99)+COLUMN(A:Z)*300)>2))-27
复制代码



再来个365函数的。
  1. =SUM(N(MID(CONCAT(--TRANSPOSE(FREQUENCY((CHOOSE(Q3,B$2:L$12,,F$3:O$7,D$8:K$15) IF(R3=0,B:O,H$3:I$14),INDEX(C:O,MATCH(S3,B:B,),)),ROW($1:98))<COLUMN(A:Z))),ROW(A:A),3)="100"))
复制代码

点评

你说的对,我在顶楼做了改正说明。  发表于 2018-3-16 09:58

评分

10

查看全部评分

TA的精华主题

TA的得分主题

发表于 2018-3-16 10:18 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖最后由 mmlzh 于 2018-4-16 13:11 编辑

1    来个火车,369字符
  1. =SUM(N(FREQUENCY(ROW($10:$90)*10^{0,1,2,3},IF(FREQUENCY((OFFSET(A$1,MAX(MID(1027,Q3,1),1+(R3="P")),MAX(MID(1053,Q3,1),1+(R3="P")*6),MIN(CHOOSE(Q3,12,,7,15),15-(R3="P"))-MAX(MID(1027,Q3,1),1+(R3="P")),MIN(CHOOSE(Q3,12,,15,11),15-(R3="P")*6)-MAX(MID(1053,Q3,1),1+(R3="P")*6)),OFFSET(C$18,FIND(S3,"YAK"),,,13)),ROW($10:$90))>{0,1,2,3},FALSE,ROW($10:$91)*10^{0,1,2,3}))>2))
复制代码

2    286字符
  1. =SUM(N(FREQUENCY(ROW($10:$90)*10^{0,1,2,3},IF(FREQUENCY((INDIRECT(IF(R3="",MID("B0FD",Q3,1),"H")&MAX(MID(2038,Q3,1),3-(R3=""))&":"&IF(R3="",MID("L0OK",Q3,1),"I")&MIN(MID(5008,Q3,1)+7,14+(R3=""))),INDEX(C$19:O$21,FIND(S3,"YAK"),)),ROW($10:$90))>{0,1,2,3},,ROW($10:$91)*10^{0,1,2,3}))>2))
复制代码

3    250字符
  1. =SUM(N(FREQUENCY(ROW($10:$90)*10^{0,1,2,3},IF(FREQUENCY((INDIRECT(LOOKUP(Q3&R3,{"1","B2:L12";"1P","H3:I12";"3","F3:O7";"3P","H3:I7";"4","D8:K15";"4P","H8:I14"})),INDEX(C$19:O$21,FIND(S3,"YAK"),)),ROW($9:$98))>{0,1,2,3},,ROW($9:$99)*10^{0,1,2,3}))>2))
复制代码

4    238字符
  1. =SUM(N(FREQUENCY(ROW($10:$390),IF(FREQUENCY((INDIRECT(LOOKUP(Q3&R3,{"1","B2:L12";"1P","H3:I12";"3","F3:O7";"3P","H3:I7";"4","D8:K15";"4P","H8:I14"})),INDEX(C$19:O$21,FIND(S3,"YAK"),)),ROW($1:$98))>{0,1,2,3},,ROW($1:$99)+{0,1,2,3}/1%))>2))
复制代码

5    217字符
  1. =SUM(N(FREQUENCY(ROW($10:$390),IF(FREQUENCY((INDIRECT(MID("B2:L12H3:I12F3:O7 H3:I7 D8:K15H8:I14",(MAX(Q3-1,1)*2-(R3=""))*6-5,6)),INDEX(C$19:O$21,FIND(S3,"YAK"),)),ROW($1:$98))>{0,1,2,3},,ROW($1:$99)+{0,1,2,3}/1%))>2))
复制代码

6    216字符
  1. =SUM(N(FREQUENCY(COLUMN(J:NZ),IF(FREQUENCY((INDIRECT(MID("B2:L12H3:I12F3:O7 H3:I7 D8:K15H8:I14",(MAX(Q3-1,1)*2-(R3=""))*6-5,6)),INDEX(C$19:O$21,FIND(S3,"YAK"),)),ROW($1:$98))>{0,1,2,3},,ROW($1:$99)+{0,1,2,3}/1%))>2))
复制代码

原写的解题过程中只为记录下公式简化的思路变化,但其中有写法不妥,对老师造成影响,在此表示歉意。
180字符
  1. =SUM(N(FREQUENCY(ROW(1:399),IF(FREQUENCY((CHOOSE(Q3,B$2:L$12,,F$3:O$7,D$8:K$15) IF(R3>0,H$3:I$14,B:O),INDEX(C:O,MATCH(S3,B:B,),)),ROW($1:98))<{1,2,3,4},ROW(1:99)+{0,1,2,3}/1%))>2))
复制代码

非常耗资源,不建议用
179=SUM(N(FREQUENCY(ROW(A:A),IF(FREQUENCY((CHOOSE(Q3,B$2:L$12,,F$3:O$7,D$8:K$15) IF(R3>0,H$3:I$14,B:O),INDEX(C:O,MATCH(S3,B:B,),)),ROW($1:99))<{1,2,3,4},ROW(A:A)+{1,3,5,7}/1%))>2))-5

评分

9

查看全部评分

TA的精华主题

TA的得分主题

发表于 2018-3-16 13:13 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖最后由 LSL1128xgw 于 2018-3-20 16:55 编辑

=SUM(N(FREQUENCY(ROW(A:A)%,IF(FREQUENCY((CHOOSE(Q3,A$1:L$12,,F$3:O$7,D$8:K$15) IF(R3>0,H$3:I$14,A:O),INDEX(A:O,MATCH(S3,B:B,),)),ROW($1:98))<{1,2,3,4},ROW($1:99)%+K$3:N$3))>2))-5
=SUM(N(FREQUENCY(ROW(A:A),({1,2,3,4}-FREQUENCY((CHOOSE(Q3,A$1:L$12,,F$3:O$7,D$8:K$15) IF(R3>0,H$3:I$14,A:O),INDEX(A:O,MATCH(S3,B:B,),)),ROW($1:$98)))/1%-ROW($1:$99))>2))-1
=SUM(N(FREQUENCY(ROW(A:A),({1,2,3,4}-FREQUENCY((CHOOSE(Q3,A$1:L$12,,F$3:O$7,D$8:K$15) IF(R3>0,H$3:I$14,A:O),INDEX(A:O,MATCH(S3,B:B,),)),ROW($1:98)))/1%%-ROW($1:99))>2))-5


=SUM(N(FREQUENCY(ROW(A:A),({1,2,3,4}-FREQUENCY((CHOOSE(Q3,A$1:L$12,,F$3:O$7,D$8:K$15) IF(R3>0,H$3:I$14,A:O),INDEX(A:O,MATCH(S3,B:B,),)),ROW(A:A)))/1%-ROW(1:99))>2))-1 此思路和版本相关,2010以上版本切换03版本后测试,否则卡死,07版本出错。



评分

10

查看全部评分

TA的精华主题

TA的得分主题

发表于 2018-3-16 16:27 | 显示全部楼层
本帖最后由 象山海鲜 于 2018-3-17 13:45 编辑

通用些
  1. =SUM(N(FREQUENCY(ROW(A:A)%,-(FREQUENCY((CHOOSE(Q3,B$2:L$12,,F$3:O$7,D$8:K$15) IF(R3=0,B:O,H$3:I$14),INDEX(B:O,MATCH(S3,B:B,),)),ROW($9:$90))-COLUMN(A:H)&ROW(9:91)%))>2))-9
复制代码

取巧
  1. =SUM(N(FREQUENCY(ROW(A:A),({0,1,2,3}-FREQUENCY((CHOOSE(Q3,B$2:L$12,,F$3:O$7,D$8:K$15) IF(R3=0,B:O,H$3:I$14),INDEX(B:O,MATCH(S3,B:B,),)),ROW($9:$90))&99)+ROW(9:91))>2))-5
复制代码





评分

10

查看全部评分

TA的精华主题

TA的得分主题

发表于 2018-3-16 22:23 | 显示全部楼层
本帖最后由 丢丢表格 于 2018-4-4 08:51 编辑

  186字
  1. =SUM(N(FREQUENCY(ROW($1:$399),IF(FREQUENCY(((CHOOSE(Q3,B$2:L$12,,F$3:O$7,D$8:K$15) IF(R3>0,H$3:I$14,B:O)),INDEX(C:O,MATCH(S3,B:B,),)),ROW($1:$99))<{1,2,3,4},{0,1,2,3}/1%+ROW($1:99)))>2))
复制代码
  214字
  1. =SUM(N(FREQUENCY(ROW($1:$400),IF(COUNTIF((CHOOSE(Q3,B$2:L$12,,F$3:O$7,D$8:K$15) IF(R3>0,H$3:I$14,B:O)),ROW($1:$100))+COUNTIF(OFFSET(C$18:O$18,FIND(S3,"YAK"),),ROW($1:$100))<{1,2,3,4},{0,1,2,3}/1%+ROW($1:$100)))>2))
复制代码

  227字
  1. =SUM(N(FREQUENCY(ROW($1:$400),IF(COUNTIF((IF(Q3=1,B$2:L$12,IF(Q3=3,F$3:O$7,D$8:K$15)) IF(R3>0,H$3:I$14,B$2:O$15)),ROW($1:$100))+COUNTIF(OFFSET(C$18:O$18,FIND(S3,"YAK"),),ROW($1:$100))<{1,2,3,4},{0,1,2,3}*100+ROW($1:$100),))>2))
复制代码










评分

10

查看全部评分

TA的精华主题

TA的得分主题

发表于 2018-3-17 11:08 | 显示全部楼层
本帖最后由 opqazxc 于 2018-4-9 10:14 编辑

`
  1. =SUM(N(MMULT(N(LOOKUP(ROW(1:970)/10+{1,2,3},ROW(A:A),FREQUENCY((CHOOSE(Q3,B$2:L$12,,F$3:O$7,D$8:K$15) IF(R3=0,B:O,H$3:I$14),INDEX(B:O,MATCH(S3,B:B,),)),ROW($1:99)))>MOD(ROW(1:970),10)),{1;2;4})=3))
复制代码
`
  1. =SUM(N(MMULT(MUNIT(98)%+(ABS(COLUMN(A:CT)-ROW($2:$99))<2),N(FREQUENCY((CHOOSE(Q3,B$2:L$12,,F$3:O$7,D$8:K$15) IF(R3=0,B:O,H$3:I$14),INDEX(B:O,MATCH(S3,B:B,),)),ROW($3:$99))>=COLUMN(A:Z)))=2))
复制代码

`
  1. =900-LEN(SUBSTITUTE(CONCAT(MOD(LEN(SUBSTITUTE(TEXTJOIN(U$2,,CHOOSE(Q3,B$2:L$12,,F$3:O$7,D$8:K$15) IF(R3=0,B:O,H$3:I$14),INDEX(B:O,MATCH(S3,B:B,),)),COLUMN(J:DE),1,ROW($1:$9))),2)),"001",22))
复制代码
`
  1. =900-LEN(SUBSTITUTE(CONCAT(N(TRANSPOSE(FREQUENCY((CHOOSE(Q3,B$2:L$12,,F$3:O$7,D$8:K$15) IF(R3=0,B:O,H$3:I$14),INDEX(B:O,MATCH(S3,B:B,),)),ROW($1:$99)))>=ROW($1:$9))),110,11))
复制代码

``






评分

8

查看全部评分

TA的精华主题

TA的得分主题

发表于 2018-3-19 15:21 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖最后由 太阳之子 于 2018-4-14 11:08 编辑
  1. =LEN(SUBSTITUTE(TRIM(SUBSTITUTE(CONCAT(IF(ROW($1:90)<=TRANSPOSE(FREQUENCY((CHOOSE(Q3,A$1:L$12,,F$3:O$7,D$8:K$15) IF(R3>0,H$3:I$14,A:O),INDEX(C:O,MATCH(S3,B:B,),)),ROW($9:90))),1,"  ")),"1 ",)),1,))+1
  2. =LEN(SUBSTITUTE(TRIM(SUBSTITUTE(CONCAT(IF(ROW($1:90)<=TRANSPOSE(FREQUENCY(IFNA(IF(B$1:B21=S3,C$1:O21,CHOOSE(Q3,A$1:L$12,,F$3:O$7,D$8:K$15) IF(R3>0,H$3:I$14,A:O)),""),ROW($9:90))),1,"  ")),"1 ",)),1,))+1
复制代码

公式2区域取值方法有点野蛮,但也算一种方法吧。

今天发现复制公式到表格里居然出错,原来公式中的空格“  ”复制后居然用trim函数无去除多余的,必须用键盘重新键入。现特补上附件。

本帖子中包含更多资源

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

x

评分

9

查看全部评分

TA的精华主题

TA的得分主题

发表于 2018-3-20 16:46 | 显示全部楼层
本帖最后由 sayhi95 于 2018-3-26 00:33 编辑

真的是做出与模拟结果相同的答案都有分么?1K加字符,汗~~~唉,太难为情了,这火车,开得突突的,模拟结果是出来了,相同数字连续组数更多的话还要加,汗~我的天~终于...
  1. =SUM(N(FREQUENCY(ROW($1:$405),IF(FREQUENCY((CHOOSE(Q3,B$2:L$12,,F$3:O$7,D$8:K$15) IF(R3="P",H$3:I$14,B:O),INDEX(C$19:O$21,MATCH(S3,B:B,)-18,)),ROW($9:$89))<{1,2,3,4},ROW($1:81)+{1,2,3,4}*81))>2))-1
复制代码
  1. =SUM(N(FREQUENCY(ROW($1:$405),IF(FREQUENCY((CHOOSE(Q3,B$2:L$12,,F$3:O$7,D$8:K$15) IF(R3>0,H$3:I$14,B:O),INDEX(C$19:O$21,MATCH(S3,B:B,)-18,)),ROW($9:$89))<{1,2,3,4},ROW($1:81)+{1,2,3,4}*81))>2))-1
复制代码

点评

是的,凡结果一致,算法无误就可以。慢慢改进呗。  发表于 2018-3-20 17:26

评分

8

查看全部评分

TA的精华主题

TA的得分主题

发表于 2018-3-22 14:49 | 显示全部楼层
本帖最后由 看见星光 于 2018-3-22 15:35 编辑

  1. =COUNT(0/(FREQUENCY(ROW($101:$6099),IF(FREQUENCY(((CHOOSE(Q3,B$2:L$12,,F$3:O$7,D$8:K$15) IF(R3>0,H$3:I$14,B:O)),INDEX(B:O,MATCH(S3,B:B,),)),ROW($1:$99))<COLUMN(A:BH),COLUMN(A:BH)/1%+ROW($1:$100)))>2))
复制代码




评分

8

查看全部评分

您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

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

GMT+8, 2024-7-23 04:24 , Processed in 0.048552 second(s), 9 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

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

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

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