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

问题描述

我有一个等式，我正在使用VBA来输入电子表格中的单元格:

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.

推荐答案

如果尚未有一天，您可能会注意到不同之处在于，绝对引用(i)不使用方括号，并且(ii)始终有一个数字可以立即在 R 和 C 的右侧，例如R1C1，这意味着第1行，第1列–通常表示为 A1 (尽管有时为 R1C1 "(使用所谓的R1C1参考样式).

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] 是相对引用，表示无论哪行碰巧是当前行( R 的右边都没有值，因此与当前行)和任意三列(由于是负数)而位于当前列的左侧(因为是负数)​​.

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.

因此，如果在 D1 中， RC [-3] 引用的位置与 R1C1 的位置完全相同，即通常称为 A1 .

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

但是由于相对引用，将 RC [-3] 放在 E1 中，它将引用 B1 ，而 R1C1 ($ A $ 1也是如此)是指 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.

现在，您的示例提到了 Active Cell -这是您要放置公式的位置，并且是在同一行中但在左边三列中"的起点.但是您尚未显示如何选择活动的单元格，因此我们无法确定要从哪个单元格开始，因此无法确定要终止的位置(而不是方向/距离).

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.

例如，如果在国际象棋棋盘上，相对引用可能是将您的骑士上移两行，并向右移动一列"，而绝对引用将是将您的骑士放到方形King 4中".

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".

从某种意义上说，两个系统"都是相对的-只是绝对"总是相对于左上角(使用RTL布局时除外).

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屋！