excel表格不一样的R1C1引用样式

A1样式和R1C1样式的历史

最初发明第一款电子表格软件VisiCalc的Dan Bricklin和Bob Frankston对单元格使用A1样式，而Microsoft早期的电子表格MultiPlan使用的是R1C1样式。上世纪80年代，Lotus是电子表格领域的翘楚，牢牢占据着电子表格市场，而它采用的正是VisiCalc用户熟悉的A1样式。为了在电子表格市场抢战更多的份额，Microsoft让Excel既可以使用A1样式又可以使用R1C1样式。

通常，我们都是使用A1样式，这也是Excel的默认样式。然而，R1C1样式有很多独到之处。

什么是R1C1样式

在“Excel选项”的“公式”下，选取“R1C1引用样式”，即可在公式中使用R1C1样式。Excel提示也给出了R1C1引用样式的说明，见下图所示。此时，工作表中顶部的列标由字母变成了数字。

让我们通过与A1样式对比来认识R1C1样式。如下图所示的工作表，列D中通过公式来计算销售额和销售合计，列F是使用A1样式的公式，列G是使用R1C1样式的公式。

可以看出：

对于R1C1样式表示的相对引用，在方括号中放置行数或列数，表示相对于当前单元格移动指定行数或列数的单元格，负数表示向左或向上移动，正数表示向右或向下移动。例如RC[-2]表示相对于当前单元格在当前行左移2列的单元格，R[-4]C表示相对于当前单元格在当前列向上移8行的单元格。

再看看SUM函数中使用的单元格引用。对比A1样式，R1C1样式的绝对引用为表示行列的字母后直接加上行号和列号。如示例中的R2C4表示单元格D2，R5C4表示单元格D5。那么，RC表示当前单元格，R3C表示当前列中第3行的单元格，RC3表示当前行中第3列的单元格。使用A1样式，如果当前单元格为$D$6，那么R3C等价于D$3，RC3等价于$C6。

为什么要使用R1C1样式

仍以上图所示的工作表为例。我们对比A1样式和R1C1样式求销售额的公式，可以看到使用R1C1样式的公式相同。也就是说，在多个单元格中，只需一个公式就可得出结果。并且，公式引用的单元格的变化并不会影响公式。我想，这或许就是使用R1C1样式的主要优势。

在VBA中，也是如此。我们使用代码来求上图所示工作表中的销售额。

使用A1样式的代码：

Range(“D2”).Formula= “=B2*C2”

Range(“D2”).CopyDestination:=Range(“D3:D5”)

使用R1C1样式的代码：

Range(“D2:D5”).FormulaR1C1= “=RC[-2]*RC[-1]”

使用R1C1样式的代码更简洁，更有效率。

此外，我们在录制宏代码时，宏录制器在录制公式时就是使用R1C1样式。

更进一步地说，Excel是以R1C1样式来完成引用和计算，而是以A1样式来显示地址和公式，因此R1C1样式的公式会更加有效。

这就是R1C1样式的秘密！

还有一点值得一提，在A1样式中，同样的公式，但输入的单元格不同，含义不一样，例如：

在单元格D8中输入公式：$A5+C3

在单元格K10中输入公式：$A5+C3

但是，R1C1样式的公式不依赖公式所在的单元格。

这也是R1C1样式的一个优点。

技巧1：Excel默认为A1样式，因此工作表上方显示的是字母表示的列号。如果想要快速得到某列的列号，可以先选取单元格A1，然后向右拖动至目标列，在名称框可以看到列号数值，如下图所示。

技巧2：可以利用R1C1样式公式的一致性来找出存在错误的公式。在复制或输入完公式后，切换到R1C1引用样式来检查公式，如果某个公式与其它公式不同，那么这个公式可能存在错误。

示例1：创建乘法表

如下图所示，在第2行第2列的单元格中输入公式：

=R1C*RC1

然后向左拖至第10列，向下拖至第10行，即可创建一个九九乘法表。

公式表示当前单元格所在行的第1列与当前单元格所在列的第1行的值相乘。

生成上述乘法表的VBA代码如下：

Sub MultiplySheet()

Range(“B2:J10″).FormulaR1C1 =”=R1C*RC1”

End Sub

示例2：在条件格式中使用R1C1样式

如下图所示的工作表，要求对列A中最大值的单元格所在的行添加红色背景色，对列A中最小值的单元格所在的行添加绿色背景色。

如果使用Excel条件格式对话框，则设置公式：

=$A1=MAX($A:$A)

=$A1=MIN($A:$A)

并设置满足条件时应用的填充色，如下图所示。

使用VBA代码也能达到同样的效果。代码如下：

Sub ToMaxMinApplyFormat()

Application.ReferenceStyle = xlR1C1

With Range(“1:6″)

.FormatConditions.Delete

.FormatConditions.AddType:=xlExpression, Formula1:=”=RC1=MAX(C1)”

.FormatConditions(1).Interior.Color =vbRed

.FormatConditions.AddType:=xlExpression, Formula1:=”=RC1=MIN(C1)”

.FormatConditions(2).Interior.Color =vbGreen

End With

Application.ReferenceStyle = xlA1

End Sub

说明：

代码先删除工作表区域中存在的条件格式。

指定添加条件格式的参数Type为xlExpression，表明要设置公式。

代码使用了R1C1样式的公式，因此，在应用条件格式前，先要将工作簿引用样式改为R1C1样式。

从代码中可以看出，Excel并不认为公式中的C1是指单元格C1，而是指第1列。

示例3：在数组公式中使用R1C1样式

如下图所示的工作表，我们使用数组公式：

=SUM(B2:B5*C2:C5)

一次求出表中4种水果的合计销售金额。

下面的代码使用数组公式计算销售合计金额：

Sub ArrayFormulasGetValue()

Range(“C6”).FormulaArray = _

“=SUM(R[-4]C[-1]:R[-1]C[-1]*R[-4]C:R[-1]C)”

End Sub

VBA帮助文档指出，使用VBA代码输入数组公式时，要使用R1C1样式。然而，我使用下面的代码：

Sub ArrayFormulasGetValue()

Range(“C6”).FormulaArray = _

“=SUM(B2:B5*C2:C5)”

End Sub

仍然能够达到上述效果。

使用下面的代码：

Range(“D2:D5”).FormulaArray= “=B2:B5*C2:C5”

在单元格区域D2:D5中自动输入每种水果销售额，即单价与销售量乘积。

是否是帮助文档有误？还是我理解有误？

技巧3：快速找到数组公式的R1C1样式。选中数组公式单元格，在VBE编辑器的立即窗口中，输入下列语句：

PrintActiveCell.FormulaR1C1

即可得到数组公式的R1C1样式，如下图所示。