绝对引用 - 在以R1C1表示法写入的方程式中插入等价物“$” [英] Absolute referencing - Inserting the equivalent of '$' in an equation written in R1C1 notation

问题描述

我有这个方程，我使用VBA进入我的电子表格中的一个单元格：

  ActiveCell.FormulaR1C1 = = VLOOKUP（RC [-3]，RC [-1]：R [& CellNumber&] C [-1]，1,0）
  p $ p> 
 
 
如何使用 $ 符号输入公式以保持单元格引用锁定？ 
 
 
 
我尝试在公式中添加 $  s在各自的位置，但没有起作用。 > 
 
解决方案
如果还没有，也许有一天你会注意到的区别是绝对参考（i）不使用方括号，（ii）总是在 R 和 C 的右侧有一个数字，例如R1C1，这意味着第1行，Column1  - 通常表达 A1 （虽然有时 R1C1 在电子表格本身以及宏 - 当在所谓的R1C1参考样式）。 
 
 
 
  RC [-3] 是一个相对参考，意味着任何行恰好是当前的行（无值立即在 R 的右侧，所以没有从当前行的偏移）和三列到左边（因为负数）任何恰好是当前列。 
 
 
 
所以如果在 D1 ， RC [-3] 引用与 R1C1 完全相同的位置，即通常称为 A1 。 
 
 
 
但是，由于相对参考，将 RC [-3]  code>，它将引用 B1 ，而$ code> R1C1 （$ A $ 1）是指code> A1 无论放置在哪里。 
 
 
 
现在，您的示例提到活动单元格  - 这是您将配置公式的位置，并且是起始点在同一行，但三列左。但是您没有显示活动单元格是如何被选中的，因此我们无法确定要从哪个单元格开始，因此您想要结束的位置（而不是方向/距离）。 
 
 
 
如果在棋盘上，例如，相对参考可能是向右移动你的骑士两列，向右移一列，而绝对将把你的骑士置于正方形国王4。 
 
 
 
从某种意义上说，两个系统都是相对的 - 只是绝对一个总是相对于左上角（除了使用RTL布局时） 。 
 
 
 
这可能有助于将 [] 中的方括号与我在我的单元格一起考虑。 
 
 
 
相当于负列表示左表示up。
 
I have this equation that I am using VBA to enter into a cell on my spreadsheet:
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],RC[-1]:R[" & CellNumber & "]C[-1],1,0)"
How do I have it enter the formula with $ signs to keep the cell references locked?  

I tried adding $s in their respective positions in the formula but that didn't work.
解决方案
If not yet maybe one day you may notice the difference is that absolute references (i) do not use square brackets and (ii) always have a number immediately to the right of R and C, eg R1C1, which means Row 1, Column1 – usually expressed A1 (though sometimes R1C1 in the spreadsheet itself, as well as in a macro - when in what is termed R1C1 reference style).  

RC[-3] is a relative reference and means whatever row happens to be the current one (no value immediately to the right of R, so no offset from the current row) and three columns to the left (because negative) of whatever happens to be the current column.  

So if in D1, RC[-3] references exactly the same location as R1C1 does – ie what is generally known as A1.  

However because a relative reference, put RC[-3] in E1 and it will reference B1, whereas R1C1 (as does $A$1) refers to A1 wherever it is placed.  

Now, your example mentions Active Cell - that is where you formula will be placed and is the start point for "in the same row but three columns to the left". But you have not shown how the active cell was selected so we can't tell which cell to start from, therefore the location (rather than the direction/distance away) where you want to end up.  

If on a chessboard for example, relative references might be "move your knight two rows up and one column to the right" whereas absolute would be "put your knight in square King 4".  

In a sense, both 'systems' are relative – just that the "absolute" one is always relative to the top left-hand corner (except when using RTL layout).  

It might help to think of the square brackets together [ ] as "from the cell I am in".  

The equivalent of 'negative columns means to the left' for rows is 'up'.

                        
这篇关于绝对引用 - 在以R1C1表示法写入的方程式中插入等价物“$”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！