vba FormulaR1C1 的功能是什么？

What is the function of FormulaR1C1?

提问by Matt Ridge

I have been looking at FormulaR1C1as a function, how does this exactly work? I understand what has been said all over the internet which is stands as Row 1 Column 1, but how do people actually make it work? What is the end resultof using FormulaR1C1?

我一直将FormulaR1C1视为一个函数，它究竟是如何工作的？我理解互联网上所说的第 1 行第 1 列，但人们实际上是如何使其工作的？使用FormulaR1C1的最终结果是什么？

Also can it be changed to start at a specific point in a sheet, or it always going to be R1C1? So could it be FormulaR2C3?

也可以将其更改为从工作表中的特定点开始，还是始终为 R1C1？那么它可能是FormulaR2C3吗？

采纳答案by user15741

FormulaR1C1 has the same behavior as Formula, only using R1C1 style annotation, instead of A1 annotation. In A1 annotation you would use:

FormulaR1C1 与 Formula 具有相同的行为，仅使用 R1C1 样式注释，而不是 A1 注释。在 A1 注释中，您将使用：

Worksheets("Sheet1").Range("A5").Formula = "=A4+A10"

In R1C1 you would use:

在 R1C1 中，您将使用：

Worksheets("Sheet1").Range("A5").FormulaR1C1 = "=R4C1+R10C1"

It doesn't act upon row 1 column 1, it acts upon the targeted cell or range. Column 1 is the same as column A, so R4C1 is the same as A4, R5C2 is B5, and so forth.

它不会作用于第 1 行第 1 列，而是作用于目标单元格或范围。第 1 列与 A 列相同，因此 R4C1 与 A4 相同，R5C2 与 B5 相同，依此类推。

The command does not change names, the targeted cell changes. For your R2C3 (also known as C2) example :

该命令不会更改名称，目标单元格会更改。对于您的 R2C3（也称为 C2）示例：

Worksheets("Sheet1").Range("C2").FormulaR1C1 = "=your formula here"

回答by nutsch

Here's some info from my blogon how I like to use formular1c1 outside of vba:

以下是我博客中关于我喜欢如何在 vba 之外使用 formular1c1的一些信息：

You've just finished writing a formula, copied it to the whole spreadsheet, formatted everything and you realize that you forgot to make a reference absolute: every formula needed to reference Cell B2 but now, they all reference different cells.

您刚刚写完一个公式，将其复制到整个电子表格，格式化所有内容，然后您意识到您忘记了绝对引用：每个公式都需要引用单元格 B2，但现在，它们都引用了不同的单元格。

How are you going to do a Find/Replace on the cells, considering that one has B5, the other C12, the third D25, etc., etc.?

考虑到一个单元格有 B5、另一个 C12、第三个 D25 等等，你将如何对单元格进行查找/替换？

The easy way is to update your Reference Style to R1C1. The R1C1 reference works with relative positioning: R marks the Row, C the Column and the numbers that follow R and C are either relative positions (between [ ]) or absolute positions (no [ ]).

简单的方法是将您的参考样式更新为 R1C1。R1C1 引用适用于相对定位：R 标记行，C 标记列，R 和 C 后面的数字要么是相对位置（在 [ ] 之间），要么是绝对位置（没有 [ ]）。

Examples:

例子：

• R[2]C refers to the cell two rows below the cell in which the formula's in
• RC[-1] refers to the cell one column to the left
• R1C1 refers the cell in the first row and first cell ($A$1)

• R[2]C 指的是公式所在单元格下方两行的单元格
• RC[-1] 指的是左边一列的单元格
• R1C1 引用第一行和第一个单元格中的单元格 ($A$1)

What does it matter? Well, When you wrote your first formula back in the beginning of this post, B2 was the cell 4 rows above the cell you wrote it in, i.e. R[-4]C. When you copy it across and down, while the A1 reference changes, the R1C1 reference doesn't. Throughout the whole spreadsheet, it's R[-4]C. If you switch to R1C1 Reference Style, you can replace R[-4]C by R2C2 ($B$2) with a simple Find / Replace and be done in one fell swoop.

有什么关系？好吧，当你在这篇文章的开头写下你的第一个公式时，B2 是你写它的单元格上方 4 行的单元格，即 R[-4]C。当您上下复制它时，虽然 A1 引用发生变化，但 R1C1 引用不会。在整个电子表格中，它是 R[-4]C。如果您切换到 R1C1 参考样式，您可以使用简单的查找/替换将 R[-4]C 替换为 R2C2（$B$2），并且一举完成。

回答by StevieB

I find the most valuable feature of .FormulaR1C1 is sheer speed. Versus eg a couple of very large loops filling some data into a sheet, If you can convert what you are doing into a .FormulaR1C1 form. Then a single operation eg myrange.FormulaR1C1 = "my particular formuala" is blindingly fast (can be a thousand times faster). No looping and counting - just fill the range at high speed.

我发现 .FormulaR1C1 最有价值的特性是绝对的速度。与例如将一些数据填充到工作表中的几个非常大的循环相比，如果您可以将您正在执行的操作转换为 .FormulaR1C1 表单。然后单个操作，例如 myrange.FormulaR1C1 = "my specific formuala" 非常快（可以快一千倍）。没有循环和计数 - 只需高速填充范围。