条件格式化时带有错误的单元格 [英] Wrong cell when conditional formatting with values

问题描述

当我使用公式创建新的规则时，我经常遇到Excel 2010的问题。这个问题在我的几台电脑上是可重现的。我选择几个单元格（例如A4：B143），然后使用以下公式之一创建一个新规则：

I regularly encounter an issue on Excel 2010 when I create a new rule using a formula. The issue is reproducible on several of my computers. I select several cells (for instance A4:B143) and then create a new rule using one of the following formulas:

=$A4="issue"
=($A4="issue")

然后按Enter键。当然，目标是看到第一个单元格的值为issue的所有行。但是这样做不是工作，当我去管理规则时，我看到Excel具有以下公式：

then press Enter. The goal is, of course, to see all the rows of which the first cell's value is "issue". But this does not work and when I go to "manage rules", I see that Excel has the following formula:

=$A1048441="issue"

这是我想要但不是与我输入的数字。如果我用我的旧公式手工替换这个新的公式（在我眼中），一切正常，公式保持 = $ A4 =issue。有没有人知道可能导致这个问题的原因呢？

This is the formula that I want but not with the number that I entered. If I manually replace this new formula that comes out of nowhere (in my eyes) with my old formula, everything works correctly and the formula stays =$A4="issue".

推荐答案

请尝试从左上角选择右下角，反之亦然。

Please try selecting from top left to bottom right rather than vice versa.

没有行（ $ ）用于行（必须关闭一个公式规则才能适用于范围）行引用是相对的，但相对于活动单元而不是顶行。

Without the anchor ($) for rows (has to be left off for the one formula rule to apply throughout the range) the row references are relative - but relative to the active cell rather than to the top row.

活动单元格当选择范围是开始范围选择的范围。因此，从B143到A4选择的范围具有B143作为活动单元格（不是A4）。相对于那个Row4早前是139行。对于CF，Excel 2010以前的139行是行1048441（行换行 - 最后一行Row1048576在Row1之前生效，因此Row1048441 + 136返回到Row1，再加上另外3个从那里到达Row4.136 + 3是139的差异）。

The active cell when selecting a range is the one that starts the range selection. So a range that is selected from B143 to A4 has B143 as the active cell (not A4). Relative to that Row4 is 139 rows earlier. For CF, 139 rows earlier than A4 is Row1048441 in Excel 2010 (the rows 'wrap' - the last row, Row1048576, is in effect immediately before Row1, so Row1048441 + 136 gets back to Row1 and plus another 3 from there to get to Row4. 136+3 is the 139 difference).

这篇关于条件格式化时带有错误的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！