excel表格不一样的R1C1引用样式
2023-01-26 餐饮美食网 【 字体:大 中 小 】
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样式,如下图所示。
如何自制蛋挞
肉火烧面怎么和面
奶茶几分糖好喝
过桥米线是哪里的?
面霜和乳液的区别
怎么摘隐形眼镜
粉饼和散粉的区别
冷烫和热烫的区别
高品质香水如何鉴别?用三步就可以解决
如何去除黑眼圈