ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

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

[讨论] 从体会条件格式触发时机到解决数据有效性辅助列方式的BUG

[复制链接]

TA的精华主题

TA的得分主题

发表于 2013-1-1 10:35 | 显示全部楼层 |阅读模式
本帖已被收录到知识树中,索引项:常规条件格式
本帖最后由 胡剑0227 于 2013-1-2 21:43 编辑

一闪而过的十字剑.gif
图1  一闪而过的十字剑

图1所示的【条件格式规则】所对应的公式有点小复杂,喜欢函数公式的朋友容易一头就栽进去分析公式,当然这确实是一种乐趣,一种享受。

我想描述一下动画中的动作吧:双击进入某个单元格的编辑状态,然后单击其他单元格来退出编辑状态。这个时候我们会发现进入编辑状态什么事也没有发生,当然也可以理解为什么事情都暂停了。退出编辑状态时首先出现了一个十字交叉(触发条件格式),然后十字交叉又消失了(条件格式又被触发了)。这是否可以理解为退出的时候触发了两次运算。

这个十字剑的条件格式规则公式:

  1. =AND($BT$1="斜线",NOW()-$K$1>TIME(,,1),ABS(ROW()-CELL("row"))=ABS(COLUMN()-CELL("col")))
复制代码

有点小复杂,我想简要讲一下。上面十字剑一闪的效果是由NOW函数引起的,在K1单元格实际也是一个NOW函数,这里就是说两个NOW函数之间有间隔(这里用TIME函数指定为1秒),那么就触发条件格式。AND函数的另两个条件对于闪过的现象没有什么关系。根据上面一闪而过的现象,我尝试得出这样的结论:

已知退出单元格编辑状态时会引起重算,这个没问题吧。

1.条件格式规则重算的触发时机要 先 于单元格公式中的重算。(这就是看到的十字剑出现)

2.第一次触发时条件格式中的NOW是实时的,单元格中的NOW函数是滞后的(这个其实包含在第1条结论中了)

========================================================================

                                                                       也可直接看第8楼







涂鸦画板.rar

16.57 KB, 下载次数: 74

评分

2

查看全部评分

TA的精华主题

TA的得分主题

发表于 2013-1-1 10:39 | 显示全部楼层

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-1-1 10:37 | 显示全部楼层
本帖最后由 胡剑0227 于 2013-1-1 11:36 编辑

先占个位吧,看到这么多跟贴,压力好大呀,这种现场编辑的帖子有点烫手了,哈哈。赶紧了,要吃饭了,呵呵
拖动单元格触发条件格式.gif
图2  切换选中区域触发条件格式重算

从图2可以看出,拖拉单元格区域就触发了条件格式,这些条件格式遗留的痕迹并未消失,是因为K1单元格中的NOW函数并没有触发重算(当然这是不应该触发的,否则一直重算无法操作了)

此外,还可以看出条件格式触发的区域范围是选中的区域。这个自然是从经济核算考虑的,范围越小消耗就越小。




TA的精华主题

TA的得分主题

发表于 2013-1-1 10:42 | 显示全部楼层

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-1-1 10:43 | 显示全部楼层

=============

本帖最后由 胡剑0227 于 2013-1-2 18:13 编辑

再占位置,先会去了,这里本要演示另一种触发形式,呵呵
切换触发(屏幕刷新就触发).gif
图3    定点裱花

        上图做了3个动作,第1个动作是切换工作表,条件格式触发重算了;第2个动作切换至一个浏览器窗口,该窗口覆盖绘图区域,于是条件格式又触发重算了;第3个动作切换到一个写字板窗口,豆腐块大小,达成了定点裱花的功能。当然也能用它做橡皮擦,呵呵。
        从上可以发现,似乎只要对应的界面有更新就会触发Excel中的条件格式,这就很“轻灵”了。



==============================================================================



TA的精华主题

TA的得分主题

发表于 2013-1-1 10:59 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
站位学习胡版的大作

TA的精华主题

TA的得分主题

发表于 2013-1-1 10:50 | 显示全部楼层
看来胡老师还有更加精彩的分享,占位学习,期待。

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-1-2 18:11 | 显示全部楼层
本帖最后由 胡剑0227 于 2013-1-3 12:48 编辑

解释数据有效性辅助列方式发癫的现象
辅助列+ENTER键方式推出编辑.gif
图4 发癫的数据有效性


      如图4所示,如果用切换活动单元格的方式退出编辑状态,那么数据有效性就会出现“该通过的通不过,不该通过的却通过了”,这是完全违背数据有效性的本职功能的。而原因就在于:触发有效性的时机要早于辅助列公式的重算,好比第1楼出现了十字剑,这里就出现了输入错误对话框,但对话框的出现让一切都停止了,辅助列公式无法完成重算,于是逻辑就卡死在这里了,成为用 前一时刻辅助列的值 来判定当前输入的值是否有效了。!!!

     不过好在使用ENTER键来完成录入就能比较好的实现有效性功能,可以猜想使用ENTER键的方式有效性重算触发机制可能就要滞后于辅助列的重算。当然是不是触发前后颠倒了此处不是最重要的,重要的是确实用ENTER键来完成录入就能实现预期的有效性判决。!

      于是,下一个有意义的问题是,如何限定用户用ENTER键来完成录入,否则一律通不过有效性验证!!此处,大家可以先想一想。

下面演示如果用限定以ENTER完成录入,大家可以多测试,不知道是否也会有意外(ENTER也通不过),不过本人暂时还未曾见。
成功限定以ENTER完成输入.gif
图5  用公式限定以ENTER键完成录入

      从第1楼开始,关键就是说的一个触发时机,时,关键就是那个时间点。根据上面的猜想和分析,直接切换单元格完成录入,此时辅助单元格还没有更新。如果用ENTER,那么有效性重算是在辅助列重算之后随即进行的有效性判断。如果辅助列就使用NOW函数,那么前一方式辅助列的时间值要远早于有效性触发时间,后一种方式辅助列的时间值微滞后于有效性判决时机。所以,上图用两者相差小于0.1秒来判决是用ENTER的方式来完成录入的。


      剩下的就简单了,就是把这个对ENTER输入方式的判决融入到一般的有效性判决中,简单说就用一个AND函数把回车判决作为其中一个条件,呵呵

回车输入+普通条件判断.gif

体会有效性的BUG.rar

4.65 KB, 下载次数: 48

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2013-1-9 22:34 | 显示全部楼层

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-1-12 13:52 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
CheryBTL 发表于 2013-1-9 22:34
先占位,改天再细品胡版的新作品。

呵呵,直接看第8楼吧。
数组公式和有效性不能很好搭配,即使用辅助列方式也会出错,这里就是解决了这个问题。
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

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

GMT+8, 2024-3-29 05:26 , Processed in 0.051009 second(s), 14 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

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

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

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