ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

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

[Excel 函数与公式] [第130期]又双叒叕——中国式排名[已结]

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2020-10-29 06:44 | 显示全部楼层
本帖最后由 看见星光 于 2020-10-29 06:48 编辑
法法条 发表于 2020-10-28 19:29
不懂,我现在只是很想知道究竟谁先更新,不可能同时更新吧,总有先后吧,丢丢老师能否再详细描述当中过程 ...

差不多是这个意思……
比如C2单元格有个公式:=A2+1。公式里引用到的A2单元格里也有一个公式,比如:=C3+1,系统就会先把A2单元格的公式计算一遍,再计算C2单元格的公式……但这不属于循环引用。
循环引用的意思是,C2单元格引用了A2单元格的值:=A2+1。同时A2单元格的公式又引用了C2单元格:=C2+1,自身调用自身,就形成迭代哈。

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2020-10-29 10:48 | 显示全部楼层
循环引用了半天还是死路,怎么粗看大佬们的公式那么“简单”,一理解就蒙圈呢

评分

2

查看全部评分

TA的精华主题

TA的得分主题

发表于 2020-10-29 11:06 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
本帖最后由 法法条 于 2020-10-29 11:10 编辑
jivy1212 发表于 2020-10-29 10:48
循环引用了半天还是死路,怎么粗看大佬们的公式那么“简单”,一理解就蒙圈呢

其实,多数人都用了COUNTIFS,思路都一样样的,我相信大多数人是卡在了谁先算的坎上,我至今也无法彻底理解。╮(╯▽╰)╭就好像例子10,我觉得不太可能先从下往上更新,因为下往上更新2还是第三名啊,最后是第4名啊,要变2次?从上往下估计也会样,所以我猜测更新的机制是从大到小进行更新,就说得过去哦。

TA的精华主题

TA的得分主题

发表于 2020-10-31 17:24 | 显示全部楼层
法法条 发表于 2020-10-29 11:06
其实,多数人都用了COUNTIFS,思路都一样样的,我相信大多数人是卡在了谁先算的坎上,我至今也无法彻底理 ...

我也没理解,我也是卡在怎么计算已经有了几个2有了几个3,在计算C列的某个格子时不明白为什么可以这样整列引用C列

TA的精华主题

TA的得分主题

发表于 2020-10-31 20:47 | 显示全部楼层
summer.linn 发表于 2020-10-31 17:24
我也没理解,我也是卡在怎么计算已经有了几个2有了几个3,在计算C列的某个格子时不明白为什么可以这样整 ...

COUNTIF要求是大于,所以不会存在循环引用。问题是这个跨层引用的问题不同于常见的错位引用问题,例如常见的求和问题,如图所示
在上图这个问题上,右侧的求和都是上一层引用下一层结果,理解起来没毛病。但是这道题用countif理解起来就不那么容易了,因为是上几层引用最下一层的结果,如果是从下到上逐层逐层去引用最下一层的结果,那么可以肯定的是,上几层的结果要更新好几次。具体的更新机制不得而知,只能说海版这是个好题,让大家知道了新的算法。梦幻小丑老师的0的0次方那个让我温故知新,厉害,丢丢老师跟兴华兄等几位老师功力深厚,膜拜,是实打实的功夫。哈哈

本帖子中包含更多资源

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

x

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2020-10-31 21:20 | 显示全部楼层
本帖最后由 summer.linn 于 2020-10-31 21:22 编辑
法法条 发表于 2020-10-31 20:47
COUNTIF要求是大于,所以不会存在循环引用。问题是这个跨层引用的问题不同于常见的错位引用问题,例如常 ...

经您提醒,我倒是想明白了,=MATCH(1=1,COUNTIFS(B:B,">"&B3,C:C,A$3:A17)<A$3:A17^(A$3:A17<4),),在这个公式中,C列自己这个格子永远不会满足红色的这个条件,所以蓝色这里就不计算了,所以不会循环引用。
为了验证我的想法,我测试了=MATCH(1=1,COUNTIFS(B:B,">="&B3,C:C,A$3:A17)<A$3:A17^(A$3:A17<4),),运行马上提示循环引用。
那么countif是不是按顺序执行条件,如果发现不满足就跳过剩下部分呢?我试着把countifs 里两个条件反一下, =MATCH(1=1,COUNTIFS(C:C,A$3:A17,B:B,">"&B3)<A$3:A17^(A$3:A17<4),),运行马上提示循环引用了,可见我的猜想是正确的。
如您所说,错位向上或错位向下引用是很常见的方法,我之前就是一直没想出来怎么错过自己同时引用上和下的数据,丢老师的公式告诉我原来可以这样用,真是太强大了,感谢丢丢老师,又学了一招!

评分

4

查看全部评分

TA的精华主题

TA的得分主题

发表于 2020-10-31 22:17 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
summer.linn 发表于 2020-10-31 21:20
经您提醒,我倒是想明白了,=MATCH(1=1,COUNTIFS(B:B,">"&B3,C:C,A$3:A17)

你又学到一招?你知道了错位更新的机制了?[捂脸]反正我是不清楚上层的更新顺序,至于你说的countifs,很明显,先大于就直接避开了循环引用,假如先C列,条件a3:a17就直接引用了公式所在单元格,直接循环引用了。这道题你要是想理解透彻,先彻底了解一下错位引用公式的更新顺序吧。

TA的精华主题

TA的得分主题

发表于 2020-11-1 10:33 | 显示全部楼层
法法条 发表于 2020-10-31 22:17
你又学到一招?你知道了错位更新的机制了?[捂脸]反正我是不清楚上层的更新顺序,至于你说的countifs,很 ...

猜一下,我认为就是从上往下更新的,一个格子里给公式涉及的用范围内有更新时就会触发重新计算,一个格子会更新N次。一个格子内容发生变化时所有涉及该单元格引用的格子都会按从上到下从左到右的顺序重新计算一次。你说的那种根据大小来判断顺序我觉得是不可能的,如果用VBA模拟一下计算过程,这种预判大小实现不了吧。

TA的精华主题

TA的得分主题

发表于 2020-11-1 16:49 | 显示全部楼层

小结

思路(点击查看大图)


公式汇总


感谢各位老师和小伙伴的积极参与,祝身体健康,万事如意!



本帖子中包含更多资源

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

x

评分

11

查看全部评分

TA的精华主题

TA的得分主题

发表于 2020-11-12 09:25 | 显示全部楼层
=SUM(($B$3:$B$17>=B3)/COUNTIF($B$3:$B$17,$B$3:$B$17))    三键结束
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

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

GMT+8, 2024-3-29 12:59 , Processed in 0.062866 second(s), 13 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

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

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

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