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样式的一个优点。

技巧1Excel默认为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样式,如下图所示。

office办公软件入门基础教程 » excel表格不一样的R1C1引用样式

本文链接:【excel表格不一样的R1C1引用样式】https://www.officeapi.cn/87897.html

OFFICE,天天学一点,进步多一点

联系我们