基于范围差异的条件格式？
我有一个 Excel 电子表格，用于跟踪几只鸟的每日体重测量值。我设置了条件格式，以便如果当天的体重低于某个阈值（实际上，有三个阈值，并且具有相应的颜色变化），则单元格的颜色会发生变化。然而，毛重只是其中的一个因素，一天到一天的体重变化对于跟踪鸡只的健康状况非常重要。
我遇到的问题是这是一个动态范围。也就是说，我没有固定细胞。我不能只指向两个单元格并告诉它计算差异；每天范围都会发生变化（也就是说，今天差异将在 C4 和 C3 之间，但明天将在 C5 和 C4 之间，然后是 C6 和 C5 之间，等等）。每天的数据都输入到列中的新单元格中，我希望计算基于前面的单元格。
Excel 中是否有一种方法可以使用条件格式，以便如果两个值之间的差异超过给定数量（在本例中，1 就足够了），它会改变颜色，还是我需要使用 VBA？
I have an Excel spreadsheet that is tracking the daily weight measurements for several birds. I have conditional formatting set up so that the color of the cell changes if the weight for that day is below a certain threshold (actually, there are three thresholds, with corresponding color changes). However, gross weight is only one factor here, the weight change from one day to the next is very important for keeping track of the health of the birds.
The problem I am having is that this is a dynamic range. That is, I don't have fixed cells. I can't just point to two cells and tell it to calculate the difference; each day the range is going to change (that is, today the difference would be between C4 and C3, but tomorrow it will be between C5 and C4, then C6 and C5, etc.). Each day data is entered in a new cell in the column and I want calculation to be based on the preceding cell.
Is there a way in Excel to use conditional formatting so that if the difference between two values exceeds a given amount (in this case, 1 is sufficient) it changes color, or do I need to use VBA?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
不需要 VBA，而且按照 @Tim 的建议更好，但如果您坚持！：
选择 C3 并设置规则（在使用公式确定要格式化的单元格中）
=ABS(C3-C2)> 1（
1是您选择的重要性，假设是正负），对于范围（适用于），请确保从 C3 开始（假设您有标题行并且数据从C2)，例如=$C$3:$C$1000或将“1000”增加到所需的数量。No need for VBA and much better to do as @Tim suggests, but if you insist!:
Select C3 and set the rule (in Use a formula to determine which cells to format)
=ABS(C3-C2)>1(
1is your chosen materiality, assumed plus or minus) and for the range (Applies to) make sure you start at C3 (assuming you have a header row and your data starts in C2), eg=$C$3:$C$1000or up the '1000' to however many required.