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

胡剑0227

图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楼

wlc5201130

胡版主的帖子，占个位学习学习

胡剑0227

先占个位吧，看到这么多跟贴，压力好大呀，这种现场编辑的帖子有点烫手了，哈哈。赶紧了，要吃饭了，呵呵

图2  切换选中区域触发条件格式重算

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

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

mps777

学习胡版的条件格式设置，谢谢啦
下载学习

胡剑0227

再占位置，先会去了，这里本要演示另一种触发形式，呵呵

图3    定点裱花

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



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

dajiahaoxinku12

站位学习胡版的大作

kuangben8

看来胡老师还有更加精彩的分享，占位学习，期待。

胡剑0227

解释数据有效性辅助列方式发癫的现象

图4 发癫的数据有效性

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

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

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

下面演示如果用限定以ENTER完成录入，大家可以多测试，不知道是否也会有意外（ENTER也通不过），不过本人暂时还未曾见。

图5  用公式限定以ENTER键完成录入

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


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

CheryBTL

先占位，改天再细品胡版的新作品。

胡剑0227

CheryBTL 发表于 2013-1-9 22:34
先占位，改天再细品胡版的新作品。

呵呵，直接看第8楼吧。
数组公式和有效性不能很好搭配，即使用辅助列方式也会出错，这里就是解决了这个问题。