英文:
EXCEL: Conditional formatting and OFFSET trouble
问题
我有一个大数据文件，我想在我的第一列数值之间进行筛选，这一列是时间（以秒为单位）的数值。这一列的数值从1到x个不等，我想要使用条件格式来更改我感兴趣的单元格的颜色...
我感兴趣的第一件事是选择那些是1200的倍数的单元格，我已经成功地通过选择条件格式 > 公式 > =MOD($A1,1200)=0 来实现了这一点，到目前为止一切正常。
然而...我想在时间列上进行格式化的第二件事是，在每个是1200的倍数的单元格的下方的第5个单元格。这些我感兴趣的单元格也总是位于1200的倍数+0.1。所以在1200.1、2400.1、3600.1等等。
所以我想要做的事情是使用OFFSET函数，并将MOD函数的结果作为参考，但是如果我这样写的话，会出现错误，因为MOD函数必须接受一个单元格范围作为参考，而不是一个操作的结果（在这种情况下是除法的余数）。
请问有人可以帮助我如何选择那些是1200的倍数+0.1的单元格，或者位于那些是1200的倍数的单元格下方的第5行的单元格吗？谢谢。
英文:
I have a large data file where i want to filter between the values of my first column, which is values of time (in seconds). This column goes from 1 to x number, and I want to have to conditional formatting to change the color of the cells of my interest...
The fist thing of my interest is to select was the cells that are a multiple of 1200, and I managed to do it by selecting conditional formatting > formula > =MOD($A1,1200)=0, so far so good.
However... the second thing I want to format on the time column, is the 5th cell below every cell that is a multiple of 1200. These cells of my interest also are also conveniently located alwats at the multiple of 1200+0.1. So at 1200.1,2400.1,3600.1, etc.
So what i was thinking of doing to create the conditional was the OFFSET function and pass as the reference the MOD function described above, however if i write it like this it has an error because the MOD function has to take in a range of cells as a reference, not the result of an operation (in this case the remainder of a division).
Can someone please help me with how to select the cells in the column that are a multiple of 1200+0.1, or that are located 5 rows below the cells that are a multiple of 1200? Thanks
答案1
得分: 0
你可以两次应用相同的格式条件。第一条规则就是你正在做的，它有效：
公式: =MOD($A1, 1200)=0
应用范围: =$A$1:$A$100
对于第二个规则，使用完全相同的条件，但将其错开，使其应用于第一条规则下方5行（注意A6的条件取决于A1中的值，以此类推到A列底部）：
公式: =MOD($A1, 1200)=0
应用范围: =$A$6:$A$100
你可能需要调整一下“应用范围”，但这应该能帮助你。
英文:
You can apply the same format condition twice. The first rule is exactly what you're doing, which works:
Formula: =MOD($A1, 1200)=0
Applies to: =$A$1:$A$100
Use exactly the same condition for the second rule, but stagger it so it applies 5 rows below the first (notice that A6 is conditional on the value in A1, and so on down column A):
Formula: =MOD($A1, 1200)=0
Applies to: =$A$6:$A$100
You might have to play with the "Applies to" ranges a bit, but this should get you going.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论