blocks|key|1845900|text|=OFFSET(INDIRECT(ADDRESS(ROW(),+COLUMN())),0,-1)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1845901|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1)
</code></pre>

blocks|key|1845975|text|最短、最兼容的版本是：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1845976|=INDIRECT("RC[-1]",0)|code-block|syntax|javascript|1845977|"RC-1“表示左侧的一列。"R1C-1“是左下角。|1845978|第二个参数0表示使用R1C1表示法解释第一个参数。|1845979|其他选项：|1845980|=OFFSET(INDIRECT(ADDRESS(ROW(),+COLUMN())),0,-1)|1845981|在我看来太长了。但如果相对值是动态的/从另一个单元格派生而来，则很有用。例如：|1845982|=OFFSET(INDIRECT(ADDRESS(ROW(),+COLUMN())),0,+A1)|1845983|最简单的选项：|1845984|=+RC[-1]|1845985|的缺点是您需要使用选项打开R1C1表示法，这在其他人必须使用excel时是行不通的。|1845986|entityMap^0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|12|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|13|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|14|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|15|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|16|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|17|8|@]|9|@]|A|$E|F]]|$1|S|3|T|5|6|7|18|8|@]|9|@]|A|$]]|$1|U|3|V|5|D|7|19|8|@]|9|@]|A|$E|F]]|$1|W|3|X|5|6|7|1A|8|@]|9|@]|A|$]]|$1|Y|3|-4|5|6|7|1B|8|@]|9|@]|A|$]]]|Z|$]]

The shortest most compatible version is:

<pre><code>=INDIRECT("RC[-1]",0)
</code></pre>

"RC[-1]" means one column to the left. "R[1]C[-1]" is bottom-left.

The second parameter 0 means that the first parameter is interpreted using R1C1 notation.

The other options:

<pre><code>=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1)
</code></pre>

Too long in my opinion. But useful if the relative value is dynamic/derived from another cell. e.g.: 

<pre><code>=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0, A1)
</code></pre>

The most simple option:

<pre><code>= RC[-1]
</code></pre>

has the disadvantage that you need to turn on R1C1 notation using options, which is a no-go when other people have to use the excel.

blocks|key|2059930|text|创建条件格式时，请将条件格式的应用范围设置为您想要的范围(整个工作表)，然后输入相对公式(删除$符号)，就像只设置左上角的格式一样。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2059931|Excel将相应地将格式正确应用于其余单元格。|2059932|在本例中，从B1开始，左边的单元格将是A1。只需使用它--不需要高级公式。|2059933|2059934|如果您正在寻找更高级的工具，可以尝试使用column()、row()和indirect(...)。|2059935|entityMap^0|1B|1|0|0|0|0|K|8|T|5|Z|D|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|R|8|@]|D|@]|E|$]]|$1|H|3|I|5|6|7|S|8|@]|D|@]|E|$]]|$1|J|3|-4|5|6|7|T|8|@]|D|@]|E|$]]|$1|K|3|L|5|6|7|U|8|@$9|V|A|W|B|C]|$9|X|A|Y|B|C]|$9|Z|A|10|B|C]]|D|@]|E|$]]|$1|M|3|-4|5|6|7|11|8|@]|D|@]|E|$]]]|N|$]]

When creating your conditional formatting, set the range to which it applies to what you want (the whole sheet), then enter a relative formula (remove the <code>$</code> signs) as if you were only formatting the upper-left corner.

Excel will properly apply the formatting to the rest of the cells accordingly.

In this example, starting in B1, the left cell would be A1. Just use that--no advanced formula required.

<hr>

If you're looking for something more advanced, you can play around with <code>column()</code>, <code>row()</code>, and <code>indirect(...)</code>.

blocks|key|1845931|text|如果将单元格引用更改为使用R1C1表示法(工具%7C选项，常规选项卡)，则可以使用简单的表示法并将其粘贴到任何单元格中。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1845932|现在你的公式很简单：|1845933|=RC[-1]|code-block|syntax|javascript|1845934|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

If you change your cell reference to use R1C1 notation (Tools|Options, General tab), then you can use a simple notation and paste it into any cell.

Now your formula is simply:

<pre><code>=RC[-1]
</code></pre>

blocks|key|1846008|text|更简单：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1846009|=indirect(address(row(),+column()+-+1))|code-block|syntax|javascript|1846010|OFFSET返回相对于当前引用的引用，因此如果indirect返回正确的引用，则不需要它。|1846011|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Even simpler:

<pre><code>=indirect(address(row(), column() - 1))
</code></pre>

OFFSET returns a reference relative to the current reference, so if indirect returns the correct reference, you don't need it.

blocks|key|1845873|text|您可以使用VBA脚本来更改选定内容的条件格式(可能需要相应地调整条件和格式)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1845874|For+Each+i+In+Selection
i.FormatConditions.Delete
i.FormatConditions.Add+Type:=xlCellValue,+Operator:=xlLess,+Formula1:="="+&+i.Offset(0,+-1).Address
With+i.FormatConditions(1).Font
++++.Bold+=+True
End+With
Next+i|code-block|syntax|javascript|1845875|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You could use a VBA script that changes the conditional formatting of a selection (you might have to adjust the condition &amp; formatting accordingly):

<pre><code>For Each i In Selection
i.FormatConditions.Delete
i.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=" &amp; i.Offset(0, -1).Address
With i.FormatConditions(1).Font
 .Bold = True
End With
Next i
</code></pre>

blocks|key|1846037|text|我偶然发现了这个帖子，因为我想总是引用“向左的单元格”，但关键是要以一种非易失性的方式(没有偏移量，间接的和类似的灾难)。上上下下看了看网页，没有答案。(此线程实际上也不提供答案。)在修修补补之后，我偶然发现了一个最令人惊讶的方法，我想和这个社区分享一下：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1846038|假设E6中的起始值为100。假设我在F5中输入这个值的增量，比方说5。然后我们将在F6+=+E6%2BF5中计算连续值(105)。如果您想添加另一个步骤，很简单:只需将F列复制到G列，然后在G5中输入一个新的增量。|1846039|这就是我们定期做的事情。每列都有一个日期，这些日期必须按时间顺序排列(以帮助匹配等)。我们经常会忘记输入一个步骤。现在假设您想在F和G之间插入一列(以弥补您的遗漏)，并将F复制到新的G中(以重新填充延续公式)。这简直是一场灾难。试试吧--+H6现在会说=F6%2BH5，而不是=G6%2BH5+(这是我们绝对需要的)。(新的G6将是正确的。)|1846040|要做到这一点，我们可以用最惊人的方式F6=index($E6:F6;1;columns($E1:F1)-1)%2BF5来混淆这个平凡的计算。向右复制，得到G6=index($E6:G6;1;columns($E1:G1)-1)%2BG5。|1846041|这应该是行不通的，对吧？循环引用，很明显！试一试，让你大吃一惊。Excel似乎意识到，尽管索引范围跨越了我们正在重新计算的单元格，但该单元格本身并不是由索引寻址的，因此不会创建循环引用。|1846042|所以现在我已经到家了。在F和G之间插入一列，我们就得到了我们所需要的:旧H中的延续值将引用我们在新G中插入的延续值。|1846043|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|P|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|Q|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|R|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|S|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|M|$]]

I stumbled upon this thread because I wanted to always reference the "cell to the left" but CRUCIALLY in a non-volatile way (no OFFSET, INDIRECT and similar disasters). Looking the web up and down, no answers. (This thread does not actually provide an answer either.) After some tinkering about I stumbled upon the most astonishing method, which I like to share with this community:

Suppose a starting value of 100 in E6. Suppose I enter a delta to this value in F5, say 5. We would then calculate the continuation value (105) in F6 = E6+F5. If you want to add another step, easy: just copy column F to column G and enter a new delta in G5. 

This is what we do, periodically. Each column has a date and these dates MUST BE in chronological order (to help with MATCH etc). Every so often it happens that we forget to enter a step. Now suppose you want to insert a column between F and G (to catch up with your omission) and copy F into the new G (to repopulate the continuation formula). This is NOTHING SHORT of a total disaster. Try it - H6 will now say =F6+H5 and NOT (as we absolutely need it to) =G6+H5. (The new G6 will be correct.)

To make this work, we can obfuscate this banal calculation in the most astonishing manner F6=index($E6:F6;1;columns($E1:F1)-1)+F5. Copy right and you get G6=index($E6:G6;1;columns($E1:G1)-1)+G5.

This should never work, right? Circular reference, clearly! Try it out and be amazed. Excel seems to realize that although the INDEX range spans the cell we are recalculating, that cell itself is not addressed by the INDEX and thus DOES NOT create a circular reference.

So now I am home and dry. Insert a column between F and G and we get exactly what we need: The continuation value in the old H will refer back to the continuation value we inserted in the new G.

blocks|key|1846058|text|为什么不直接使用：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1846059|=ADDRESS(ROW(),COLUMN()-1)|code-block|syntax|javascript|1846060|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Why not just use:

<pre><code>=ADDRESS(ROW(),COLUMN()-1)
</code></pre>

blocks|key|1846099|text|因为这是回答“zipper”的唯一方法|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1846100|你的公式的原因：|1846101|="OFFSET(INDIRECT(ADDRESS(ROW(),+COLUMN())),0,-1)=""xyz"""|code-block|syntax|javascript|1846102|没有条件格式，是因为公式litteraly+number+="THIS+PHRASE“等于"THAT+PHRASE"，试着这样写：=OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1)="xyz"，其中"xyz”是你的文本，"-1“是你想要搜索的左边的列数。|offset|length|style|CODE|1846103|entityMap^0|0|0|0|1T|1H|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|T|8|@$K|U|L|V|M|N]]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

As this was the only way to answer @zipper
The reason why your formula:
<pre><code>=&quot;OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1)=&quot;&quot;xyz&quot;&quot;&quot;
</code></pre>
doesn't conditionally format, is because the formula litteraly asks =&quot;THIS PHRASE&quot; is equal to &quot;THAT PHRASE&quot;, try writing it like this:
<code>=OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1)=&quot;xyz&quot;</code> where &quot;xyz&quot; is your Text and &quot;-1&quot; is the number of columns to the left you want to search.

I'm trying to do conditional formatting so that the cell color will change if the value is different from the value in the cell left of it (each column is a month, in each row are the expenses on certain object. I want to monitor easily changes in prices over months.)

I can do it per cell and format-drag it, but I would like a general formula to apply to the whole worksheet.

Thanks!

Excel formula to reference 'CELL TO THE LEFT'

我正在尝试进行条件格式化，这样如果单元格的值与左边单元格中的值不同，单元格的颜色就会改变(每列是一个月，每行是某些对象的费用)。我想轻松监控几个月内价格的变化。)我可以按单元格和格式拖动它，但我想要一个通用公式来应用于整个工作表。谢谢!

问引用“左侧单元格”的Excel公式
EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问引用“左侧单元格”的Excel公式EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问引用“左侧单元格”的Excel公式
EN