Excel中R1C1样式引用详解.docx

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