Excel中R1C1样式引用详解.docx
- 文档编号:2323349
- 上传时间:2022-10-28
- 格式:DOCX
- 页数:11
- 大小:644.53KB
Excel中R1C1样式引用详解.docx
《Excel中R1C1样式引用详解.docx》由会员分享,可在线阅读,更多相关《Excel中R1C1样式引用详解.docx(11页珍藏版)》请在冰豆网上搜索。
Excel中R1C1样式引用详解
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=Cells(Rows.Count,2).End(xlUp).Row '求第二列数据行数
Range("c2").Formula="=a2*b2"
Range("C2").CopyDestination:
=Range("C2:
C"&Finalrow)
End Sub
上述代码在第二行输入公式,再向下复制公式
如果用R1C1样式只需一条语句就可整列输入公式
Sub chengji()
Dim Finalrow As Integer
Finalrow=Cells(Rows.Count,2).End(xlUp).Row '求第二列数据行数
Range("c2:
c"&Finalrow).FormulaR1C1="=RC[-1]*RC[-2]"
End Sub
使用R1C1样式的优点是,所有C列的公式都是相同的,不需要改变
四、怎么引用R1C1样式
R1C1样式采用R来表示行,C来表示列
4.1样式的相对引用
对于列正数表示向右移指定数量的列,负数反之。
对于行正数表示向下移指定数量的行,负数反之。
如果省略掉R或C后面的方括号,表示和引用单元格在同行或同列。
4.2样式的绝对引用
在A1样式中使用绝对引用要在行号或列号字母前使用$。
但在R1C1样式中只需省略方括号就行了,是不是很简单!
!
!
!
!
!
!
!
如下所示代码:
Sub huizong()
Dim Finalrow As Integer
Finalrow=Cells(Rows.Count,2).End(xlUp).Row
Cells(Finalrow+1,1).Value="汇总"
Cells(Finalrow+2,1).Resize(1,3).FormulaR1C1="=SUM(R2C:
R[-2]C)"
End Sub
引用R2C:
R[-2]C表示将当前列第2行到上2行中,同列数据的和,通过使用R1C1混合引用,可以使用公式求行数不确定的数据,
4.3引用整行和整列
有时候需要编写整列的公式。
例如求G列的最大值,如不知道G列包含多少行,可在单元格中输入公式=MAX($G:
$G),要找出第一行中最大的值可用
=MAX($1:
$1)或R1C1公式=MAX(R1)。
可以整行、整列使用相对引用。
要计算当前单元格上一行的平均值,可用=AVERAGE(R[-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=Array(1,2,3,4,5,6,7,8,9,10,11,12)
Range("b1:
m1").Font.Bold= True
Range("b1:
m1").Copy
Range("a2:
a13").PasteSpecialTranspose:
=True
Range("b2:
m13").FormulaR1C1="=rc1*r1c"
Cells.EntireColumn.AutoFit '最合适的列宽
End Sub
5.2一种有趣的行为
尝试以下操作,将单元格指针移到F6,单击“开发工具”--”录制宏“,然后单击“开发工具”---“使用相对引用”,输入公式=a1并按ctrl+enter键,以保留在F6键中。
单击“停止录制”按钮。
将得到一个只包含一行的代码的宏,它在当前的单元格输入公式,该公式引用向上5行,向左5列的单元格:
Sub 宏1()
Selection.FormulaR1C1="=R[-5]C[-5]"
End Sub
现在将单元格指针移到A1并运行刚才的宏,你可能会认为将导致运行错误1004,但实际并没有出现这种错误。
运行宏时,单元格A1中的公式指向=XF1048572(Excel2003指向=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 ThisWorkbook.Worksheets
ws.UsedRange.FormatConditions.Delete
For Each cell In ws.UsedRange.Cells
If Not IsEmpty(cell) Then
'单元格值是任意错误值时,
'把字体颜色设置为与单元格底色相同的颜色(即看不出错误值)
cell.FormatConditions.AddType:
=xlExpression,Formula1:
="=or(ISERR(RC),isna(RC))"
cell.FormatConditions
(1).Font.Color=cell.Interior.Color
'单元格值小于0的,全部用红色字体标出
cell.FormatConditions.AddType:
=xlCellValue,Operator:
=xlLess,Formula1:
="0"
cell.FormatConditions
(2).Font.ColorIndex=3
End If
Next cell
Next ws
End Sub
一个演示条件格式的经典实例,显示包含最小值和最大值的行。
代码如下:
Sub FindMinMax()
Finalrow=Cells(Cells.Rows.Count,1).End(xlUp).Row
With Range("a2:
c"&Finalrow)
.FormatConditions.Delete
.FormatConditions.AddType:
=xlExpression,Formula1:
="=rc3=max(c3)"
.FormatConditions
(1).Interior.ColorIndex=4 '用绿色底纹标出
.FormatConditions.AddType:
=xlExpression,Formula1:
="=rc3=min(c3)"
.FormatConditions
(2).Interior.ColorIndex=6 '用黄色底纹标出
End With
End Sub
如果设置一个指向单元格C3的条件格式,这种格式将失败,因为Excel将C3解释为第3列。
七、VBA中的R1C1样式数组公式
数组公式必须是R1C1公式。
数组公式是功能强大的“超级公式”,被称为CSE公式,因为用户必须按Ctrl+Shift+Enter键来输入它们,如:
=SUM(A$2:
A7*B$2:
B7)这是个数组公式,很好理解。
虽然在用户界面中显示A1样式,但输入数组公式要使用R1C1表示法:
Su
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Excel R1C1 样式 引用 详解