Excel中RC样式引用详解

Excel中R1C1样式引用详解

这篇文章根据excelhome论坛中的两个帖子进行整理;稍作了修改..

以下内容来自第一个帖子;点击这里可以查看原文..

一、引用单元格：R1C1样式与A1样式比较

DanBricklin和BobFrankston使用A1表示电子表格左上角的单元格;MitchKapor在Lotus1-2-3中也是使用这种编址方案..

Microsoft试图改变这种趋势;采用了名为R1C1样式编址方案..单元格A1称为R1C1;因为它位于第一行;第一列Row1;Column1..

在20世纪80年代和90年代初期;A1样式称为了标准;Microsoft公司认识到了危机;最终让excel接受了A1样式;并同时支持R1C1样式编址方案..当前;

excel默认使用A1样式..

为什么学习R1C1样式

答案是：

1;excel的宏录制器采用的是R1C1样式录制公式..

2;R1C1样式比之A1样式有更高的效率;尤其是对于公式;编写的代码效率会更高..

3;在BVA编辑器中;创建数组公式或基于公式设置条件格式时;采用的是R1C1样式输入公式..

怎么切换R1C1样式

单击Ofice按钮选择Excel选项;在公式类别中选择复选框“R1C1引用样式”..版面上唯一不同是列标A;B;C--变成数字1;2;3;单元格C5变为R5C2

二、EXCEL公式的神奇之处

如动画所示;Excel能智能的填充公式;并向下复制;第一次看到一定感觉非常惊奇..

实际这并不惊奇;因为;Excel内部使用的是R1C1样式的公式;以A1样式显示地址和公式..如果将动画所示的工作表切换成R1C1样式表示法;将发现C2:C7的公式都是形同的..

三、在VBA中采用A1样式与R1C1样式之比较

如上述动画实例;如用A1样式编写代码;代码可以类似下面这样：

Sub chengji

Dim Finalrow As Integer

Finalrow=; '求第二列数据行数

Range"c2".Formula="=a2b2"

Range"C2".CopyDestination:=Range"C2:C"&Finalrow

End Sub

上述代码在第二行输入公式;再向下复制公式

如果用R1C1样式只需一条语句就可整列输入公式

Sub chengji

Dim Finalrow As Integer

Finalrow=; '求第二列数据行

数

Range"c2:c"&aR1C1="=RC-1RC-2"

End Sub

使用R1C1样式的优点是;所有C列的公式都是相同的;不需要改变

四、怎么引用R1C1样式

R1C1样式采用R来表示行;C来表示列

4.1样式的相对引用

对于列正数表示向右移指定数量的列;负数反之..

对于行正数表示向下移指定数量的行;负数反之..

如果省略掉R或C后面的方括号;表示和引用单元格在同行或同列..

4.2样式的绝对引用

在A1样式中使用绝对引用要在行号或列号字母前使用$..但在R1C1样式中只需省略方括号就行了;是不是很简单

如下所示代码:

Sub huizong

Dim Finalrow As Integer

Finalrow=;

CellsFinalrow+1;="汇总"

CellsFinalrow+2;1;aR1C1="=SUMR2C:R-2C"

End Sub

引用R2C:R-2C表示将当前列第2行到上2行中;同列数据的和;通过使用R1C1混合引用;可以使用公式求行数不确定的数据;

4.3引用整行和整列

有时候需要编写整列的公式..例如求G列的最大值;如不知道G列包含多少行;可在单元格中输入公式=MAX$G:$G;要找出第一行中最大的值可用

=MAX$1:$1或R1C1公式=MAXR1..

可以整行、整列使用相对引用..要计算当前单元格上一行的平均值;可用=AVERAGER-1

如何在D5引用其周围的单元格

五、R1C1样式的经典实例;一种有趣的行为

创建R1C1公式实际上比A1公式更直观..一个演示R1C1公式的经典实例是创建乘法表..在excel中;使用单个混合引用公式就可创建乘法表..

5.1创建乘法表

在B1：M1中输入数字1-12;在A2:a13中也输入数字1-12;现在创建b2：m13中所有单元格公式;它计算第一行和第一列的乘积..用R1C1样式公式代码如下：

Sub Multiplicationtable8

Range"b1:m1".Value=Array1;2;3;4;5;6;7;8;9;10;11;12

Range"b1:m1".= True

Range"b1:m1".Copy

Range"a2:a13".PasteSpecialTranspose:=True

Range"b2:m13".FormulaR1C1="=rc1r1c"

'最合适的列宽

End Sub

5.2一种有趣的行为

尝试以下操作;将单元格指针移到F6;单击“开发工具”--”录制宏“;然后单击“开发工具”---“使用相对引用”;输入公式=a1并按ctrl+enter键;以保留在F6键中..单击“停止录制”按钮..

将得到一个只包含一行的代码的宏;它在当前的单元格输入公式;该公式引用向上5行;向左5列的单元格：

Sub 宏1

aR1C1="=R-5C-5"

End Sub

现在将单元格指针移到A1并运行刚才的宏;你可能会认为将导致运行错误1004;但实际并没有出现这种错误..运行宏时;单元格A1中的公式指向=XF1048572Excel2003指向=IR65532;这意味着R1C1公式从表的左侧绕回到右侧..这是一个很有趣的行为;但是可能宏将提供一个与用户期望不同的结果

乘法表实例：

六、条件格式中的R1C1样式应用

设置条件格式时;必须使用R1C1公式;这很重要..文档没有明确之处这一点;但如果不用R1C1公式;有时可能出问题;有研究发现;如果用A1公式;每对50个单元格设置条件格式;将有一个单元格出现奇怪的行为..因为将A1引用转换为R1C1引用有时存在二义性;例如;R2表示一个单元格;但可能被错误理解为整个第二行..

FormatConditions对象用于设置条件格式..每个单元格可以有3个FormatConditions;下面的代码首先遍历所有工作表;删除每个工作表中的条件格式;然后遍历每个工作表中所有的非空单元格;并应用两种条件格式..

在第一种条件格式中;类型为xlExpression;这意味着使用的是“公式”语法..首先Foumula1指定的公式采用的是R1C1表示法..

第二个条件格式使用xlCellValue类型;这需要指定一个运算符和一个值..在添加条件后;为条件1和条件2设置字体的ColorIndex

Sub ApplySpecialFormattingALL

For Each ws In eets

For Each cell In

If Not IsEmptycell Then

'单元格值是任意错误值时;

'把字体颜色设置为与单元格底色相同的颜色即看不出错误值

Type:=xlExpression;Formula1:="=orISERRRC;isnaRC"

=

'单元格值小于0的;全部用红色字体标出

Type:=xlCellValue;Operator:=xlLess;Formula1:="0"

ndex=3

End If

Next cell

Next ws

End Sub

一个演示条件格式的经典实例;显示包含最小值和最大值的行..代码如下：

Sub FindMinMax

Finalrow=Cells;

With Range"a2:c"&Finalrow

.

.e:=xlExpression;Formula1:="=rc3=maxc3"

.ndex=4 '用绿色底纹

标出

.e:=xlExpression;Formula1:="=rc3=minc3"

.ndex=6 '用黄色底纹标出

End With

End Sub

如果设置一个指向单元格C3的条件格式;这种格式将失败;因为Excel将C3解释为第3列..

七、VBA中的R1C1样式数组公式

数组公式必须是R1C1公式..

数组公式是功能强大的“超级公式”;被称为CSE公式;因为用户必须按Ctrl+Shift+Enter键来输入它们;如：

=SUMA$2:A7B$2:B7这是个数组公式;很好理解..

虽然在用户界面中显示A1样式;但输入数组公式要使用R1C1表示法：

Sub EnterArrayFormulas

Finalrow=;

CellsFinalrow+2;="乘积和"

CellsFinalrow+2;aArray="=sumR2C-2:R-2C-2R2C-1:R-2C-1"

End Sub

上述代码中的：

CellsFinalrow+2;aArray="=sumR2C-2:R-2C-2R2C-1:R-2C-1"

可以简化为：

CellsFinalrow+2;aArray="=sumR2:R-2 C-2R2:R-2 C-1"

后面有介绍;但要注意：红色和蓝色代码之间有一个空格..

小技巧可以采用下面的方法快速输入R1C1公式：

在任何单元格中输入常规的A1公式或数组公式;选择该单元格并切换到VBA编辑器..然后按Ctrl+G键打开立即窗口;再输入：

aR1C1或者

aR1C1

并按回车键;Excel将把公式栏中的公式转换为R1C1样式..

以下内容来自第二个帖子;点击这里可以查看原文..

单元格引用有A1和R1C1两种形式;两种形式中又包含了绝对引用和相对引用两种不同的变化..

单个区域的引用：

先来讲一下R1C1方式的引用

对于G2:J2这样一个单元格区域;使用R1C1来引用;可以写作：R2C7:R2C10;也可以写作：R2C10:R2C7..但R必须在C前面;“C10R2:C7R2”这样的写法是无效的..

对于这样单行的区域引用;可以使用简写的引用方式;即将R2C7:R2C10中的红色部分省去;简写为R2C7:C10..

如果对于单列的区域引用;例如G2:G10;则可以将R2C7:R10C7中的红色部分省去;简写为R2:R10C7..

为什么可以这样简写;这样的简写有何规律 在没有官方的解释之前;为了便于大家的理解和记忆;我给出一个我自己的解释思路;请看下面的“引用运算符”：

引用运算符：

Excel中包含了3种引用运算符;用于表示对单元格的引用;

一种是冒号;称为区域运算符;这是最常见的;如=sumA2:B10;表示引用冒号两边单元格所围成的矩形区域；

一种是逗号;称为联合运算符;如=RANKA1;A1:A10;C1:C10;表示同时引用逗号两边的两个区域；

还有一种是空格;称为交叉运算符;表示引用空格两边的两个区域的交集;如=SUMA1:B5A4:D9即等价于=SUMA4:B5..这个交叉运算符也是我这里要重点引入的一个使用技巧..

对于G2:J2这个单元格区域;如果换一个角度来看;可以看作是G:J这4列与第2行所构成的交叉区域..因此;如果用交叉运算符和R1C1样式来引用的话;可以写作：R2C7:C10;即表示R2与C7:C10这两个区域的交叉区域..

因此R2C7:R2C10等价于R2C7:C10;而这个形式与上面的简写形式R2C7:C10十分相似；

同理;G2:G10可以表示为R2C7:R10C7;等价于R2:R10C7;这个形式于其简写形式R2:R10C7也十分相似..

以上就是对于同行或同列的R1C1引用简写方式的一些理解;但这个题目中真正起到大作用的并非那个简写方式虽然也可以缩短不少字符;而是引入交叉运算符以后的引用方式..请继续往下看..

交叉运算符同样也可以应用在A1引用方式中：

例如R2C7:C10如果使用A1引用方式;则可以写作：2:2G:J

R2:R10C7如果使用A1引用方式;则可以写作：G:G2:10

从现在的样子看上去;好像A1引用方式更简短;但联系题目的实际情况;经过代入后的比较最终可以发现还是R1C1引用方式更合适;这一点可以留到后面再看..

多个区域的引用：

题目最终结果要求同时超级链接到3行最多喜字的区域;这就意味着需要同时对多个区域进行引用;

以同时引用G13:J13、G15:J15、G17:J17这三行区域为例;

使用R1C1的通常写法是：

R13C7:R13C10;R15C7:R15C10;R17C7:R17C10其中的逗号表示联合运算

可以引入交叉运算符;简化为：

R13C7:C10;R15C7:C10;R17C7:C10

此时;来做一个类似于合并同类项的变形;可以简化为：

R13;R15;R17C7:C10

将几个行号用逗号相连接表示联合运算;同时外面用括号包围以后;再与

C7:C10区域使用空格进行连接;形成交叉运算关系;得到了我们所需要的三个行区域的同时引用..

这个引用方式就是我的公式中最终所取用的最简短的引用方式..

可以跟A1引用方式做个比较;上述区域的A1引用写法为：13:13;15:15;17:17G:J;结合题目的实际情况;可以比较得出A1形式写法的字符代价要远高于R1C1的形式..

至于我两个公式中R00;R00;R00C7:C33和R0R000R00;0C7:C33两种不同Text函数自定义格式的区别;主要在于后面这种格式利用了数字格式中的千分位符可以自动生成“逗号”的便利;从本质上来说两者的单元格引用方式是一致的..

写了这么多;也不知道大家能否看懂;如果结合下面这个附件;相信可以更容易理解一些：

附件：单元格引用.rar