条件格式的绝对引用改相对引用有错误

leaveLK

我做了一个表格，能根据单元格自动生成数据，其中“间距”和“应测点数”是引用其他工作表的数据，我为了不在这里产生混淆，已经把源数据链接去掉，仅保留数值。这个表核心主要是利用RANDBETWEEN函数，某些数值是手动填写（即超出上下限数值），这些数值影响合格点数和合格率。

最后对批量生成的数据（随机函数和手动输入函数）利用条件格式将超出上下限数据做特殊标记（加删除线）。
重点：因为表中B9、B22、B35……这些数值全是另外一个表格引用过来的，没有规律，我不能在每个表格（1、2、3、4、……）添加条件格式，所以就做了一个模板，然后复制单元格，在复制单元格的时候让他自带条件格式。
我设条件格式是用 “只为包含一下内容的单元格设置格式”——“单元格值”——“未介于”添加，添加条件格式的时候Excel自动为上下限添加了绝对引用。
那么我复制单元格的时候，虽然表格数据变了，上下限要求也变了，但是条件格式还是用的原来的值（绝对引用），失去了判断的意义。
我又尝试把条件格式上下限那里把绝对引用符号去掉，在复制粘贴单元格的时候，引用位置到时变了，但是结果明显不对。
求大神些下载附件帮忙解答，如果有更好的思路和办法，不用我这个也行。
通用记录表31程序.zip (31.68 KB, 下载次数: 9)

2021-7-26 23:28 上传

点击文件名下载附件

下面是截图解释。
以下基于“模板”工作表操作

D4:W13添加条件格式，点选上下限，默认是绝对引用。
我在O6输入数值133，超出上限，条件格式判断结果正确。


D17:W26添加条件格式，点选上下限，默认是绝对引用。
我在O19输入数值155，超出上限，条件格式判断结果正确。



条件格式结果是我想要的。注意：此时条件格式是分2次添加，而且间距和上下限也是不一样的。



以下基于“应用”工作表操作

复制“模板”工作表重命名“应用”工作表，
复制14~26行（表2），粘贴于27~39行（表3）。
把间距改为119，相应的上下限自动变为109和129.
条件格式这个时候就“犯错”了，因为之前是绝对引用，所以（表3）还是按照（表2）130、150判断。

将条件格式里的绝对引用符号去掉

点击应用后，条件格式结果完全看不懂，一部分判断超出上下限，一部分又没有超限。为什么？？？
实际上RANDBETWEEN函数是按照上下限生成数据，是没有错的。

点开（表3）的条件格式，从（表2）复制过来的相对引用条件格式。上下限引用位置随着单元格的复制-粘贴已经改变。

把（表4）的条件格式点开，将相对引用改为绝对引用后，点击确认，条件格式显示又正常了。

再此想问：
1、为什么条件格式不能用相对引用，我在baidu上搜索，很多回答可以用相对引用的。但是我进过试验，用相对引用结果会判断错误。
2、我这里要求条件格式里的"条件“随每个表的要求不一样，不能一成不变，我又不可能对每个表添加条件格式。这个问题怎么解决呢？

leaveLK

路过的请看一下，想想办法。

两蛋一瓜

就当前数据，条件格式-公式：
=(MOD(ROW()-3,13)>0)*(D4<INDIRECT("c"&INT(ROW()/13)*13+12))+(D4>INDIRECT("c"&INT(ROW()/13)*13+10))
应用于区域可随意扩展

leaveLK

两蛋一瓜 发表于 2021-7-27 14:46
就当前数据，条件格式-公式：
=(MOD(ROW()-3,13)>0)*(D4INDIRECT("c"&INT(ROW()/13)*13+10))
应用于区域 ...

感谢指导，你的公式只需要一次条件格式就能解决问题。看了很久大概理解你公式的意思了。不过还有点问题需要请教。
1、我这么理解你的条件公式思路=（单元格>0）*（单元格<下限）+（单元格>上限）为什么不是=（单元格>0）*【（单元格<下限）+（单元格>上限）】？2、接上面，（单元格>0)这个条件是不是必须？因为我的随机数据生成公式（=IF(COLUMNS($D:D)<$B10+1-COLUMN($T:$T)*(COLUMN($A:$A)-1),RANDBETWEEN($C12,$C10),"")）里面IF最后参数就是对无数据显示空白，已经是空白了，不管满不满足条件都不用显示，说直接点这个单元格都不需要条件格式了，我把你的条件格式（当前单元格>0)删除了，只保留=(D4<INDIRECT("c"&INT(ROW()/13)*13+12))+(D4>INDIRECT("c"&INT(ROW()/13)*13+10))也可以达到目的，可否解释下你多加了这一个条件的思路？

leaveLK

两蛋一瓜 发表于 2021-7-27 14:46
就当前数据，条件格式-公式：
=(MOD(ROW()-3,13)>0)*(D4INDIRECT("c"&INT(ROW()/13)*13+10))
应用于区域 ...

再麻烦一下，我编的数据的公式还是不太完美，就拿表1举例：
D4单元格的公式是：=IF(COLUMNS($D:D)<$B10+1-COLUMN($T:$T)*(COLUMN($A:$A)-1),RANDBETWEEN($C12,$C10),"")，这个公式只适用于D4:W4,意思就是只可以往右拖动，不能往下拖动，当往下拖动时，RANDBETWEEN($C12,$C10)对上下限的引用就变成RANDBETWEEN($C13,$C11)。如果改成RANDBETWEEN($C$12,$C$10)，可以往下拖动，但是我复制表1到表2、表3、表N的时候，RANDBETWEEN函数永远引用的是C12和C10单元格，又达不到目的。综合考虑，我只能在D4、D5、D6......D13单元格分别编辑公式，然后再往右拖动。这个时候就可以随便复制表格了。

请问我的公式有没有改进的可能？

两蛋一瓜

leaveLK 发表于 2021-7-29 17:34
感谢指导，你的公式只需要一次条件格式就能解决问题。看了很久大概理解你公式的意思了。不过还有点问题需 ...

1、（单元格>0）目的是将所有“名称”这类行（行数-3的整数倍）不进行判断；
2、将<下限与>上限括起来是可以的，我看例子中几个上下限值比较大，所以取了个巧，就没有括起来；

两蛋一瓜

leaveLK 发表于 2021-7-29 17:56
再麻烦一下，我编的数据的公式还是不太完美，就拿表1举例：
D4单元格的公式是：=IF(COLUMNS($D:D)

D4输入以下公式，向下向右填充（可跨表使用）=IF(COLUMN(A$1)<=INDIRECT("B"&INT(ROW()/13)*13+10)-20*MOD(ROW()-4,13),RANDBETWEEN(INDIRECT("C"&INT(ROW()/13)*13+12),INDIRECT("C"&INT(ROW()/13)*13+10)),"")

leaveLK

两蛋一瓜 发表于 2021-7-29 18:17
1、（单元格>0）目的是将所有“名称”这类行（行数-3的整数倍）不进行判断；
2、将上限括起来是可以的， ...

懂了，非常巧妙，我只在数据单元格验证了，没考虑到数据范围垮表的时候，名称行里面也有数字会被误判。
请有时间 再看看另外一个问题。

leaveLK

两蛋一瓜 发表于 2021-7-29 18:23
D4输入以下公式，向下向右填充（可跨表使用）=IF(COLUMN(A$1)

一直在等新回复，结果你在原来楼层里回复，没仔细看还没刷到答案。又学到一个解题思路，真的感谢了。