1、Excel中R1C1样式引用详解 2Excel中R1C1样式引用详解?这篇文章根据excelhome论坛中的两个帖子进行整理,稍作了修改。?以下内容来自第一个帖子,。?一、引用单元格:R1C1样式与A1样式比较?DanBricklin和BobFrankston使用A1表示电子表格左上角的单元格,MitchKapor在Lotus1-2-3中也是使用这种编址方案。?Microsoft试图改变这种趋势,采用了名为R1C1样式编址方案。单元格A1称为R1C1,因为它位于第一行,第一列(Row1,Column1)。?在20世纪80年代和90年代初期,A1样式称为了标准,Microsoft公司认识到了危机
2、,最终让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公式的神奇之处?如动画所示,Exce
3、l能智能的填充公式,并向下复制,第一次看到一定感觉非常惊奇。?实际这并不惊奇,因为,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).Copy
4、Destination:=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-2End?Sub?使用R1C1样式的优点是,所有C列的公式都是相同的,不需要改变?四、怎么引用R1C1样式?R1C1样式采用R来表示行,C来表示列?4.1样式的相对引用
5、?对于列正数表示向右移指定数量的列,负数反之。?对于行正数表示向下移指定数量的行,负数反之。?如果省略掉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).FormulaR
6、1C1=SUM(R2C:R-2C)End?Sub?引用R2C:R-2C表示将当前列第2行到上2行中,同列数据的和,通过使用R1C1混合引用,可以使用公式求行数不确定的数据,?4.3引用整行和整列?有时候需要编写整列的公式。例如求G列的最大值,如不知道G列包含多少行,可在单元格中输入公式=MAX($G:$G),要找出第一行中最大的值可用?=MAX($1:$1)或R1C1公式=MAX(R1)。?可以整行、整列使用相对引用。要计算当前单元格上一行的平均值,可用=AVERAGE(R-1)?如何在D5引用其周围的单元格??五、R1C1样式的经典实例,一种有趣的行为?创建R1C1公式实际上比A1公式更直观
7、。一个演示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).PasteSpecialTranspo
8、se:=True?Range(b2:m13).FormulaR1C1=rc1*r1c?最合适的列宽End?Sub?5.2一种有趣的行为?尝试以下操作,将单元格指针移到F6,单击“开发工具”-”录制宏“,然后单击“开发工具”-“使用相对引用”,输入公式=a1并按ctrl+enter键,以保留在F6键中。单击“停止录制”按钮。?将得到一个只包含一行的代码的宏,它在当前的单元格输入公式,该公式引用向上5行,向左5列的单元格:Sub?宏1()?Selection.FormulaR1C1=R-5C-5End?Sub?现在将单元格指针移到A1并运行刚才的宏,你可能会认为将导致运行错误1004,但实际并没有
9、出现这种错误。运行宏时,单元格A1中的公式指向=XF1048572(Excel2003指向=IR65532),这意味着R1C1公式从表的左侧绕回到右侧。这是一个很有趣的行为,但是可能宏将提供一个与用户期望不同的结果!?乘法表实例:?六、条件格式中的R1C1样式应用?设置条件格式时,必须使用R1C1公式,这很重要。文档没有明确之处这一点,但如果不用R1C1公式,有时可能出问题,有研究发现,如果用A1公式,每对50个单元格设置条件格式,将有一个单元格出现奇怪的行为。因为将A1引用转换为R1C1引用有时存在二义性,例如,R2表示一个单元格,但可能被错误理解为整个第二行。?FormatConditio
10、ns对象用于设置条件格式。每个单元格可以有3个FormatConditions,下面的代码首先遍历所有工作表,删除每个工作表中的条件格式,然后遍历每个工作表中所有的非空单元格,并应用两种条件格式。?在第一种条件格式中,类型为xlExpression,这意味着使用的是“公式”语法。首先Foumula1指定的公式采用的是R1C1表示法。?第二个条件格式使用xlCellValue类型,这需要指定一个运算符和一个值。在添加条件后,为条件1和条件2设置字体的ColorIndexSub?ApplySpecialFormattingALL()?For?Each?ws?In?ThisWorkbook.Work
11、sheets?For?Each?cell?In?If?Not?IsEmpty(cell)?Then?单元格值是任意错误值时,?把字体颜色设置为与单元格底色相同的颜色(即看不出错误值)?Type:=xlExpression,Formula1:=or(ISERR(RC),isna(RC)?cell.FormatConditions(1).Font.Color=?单元格值小于0的,全部用红色字体标出?Type:=xlCellValue,Operator:=xlLess,Formula1:=0?cell.FormatConditions(2).Font.ColorIndex=3?End?If?Next
12、?cell?Next?wsEnd?Sub?一个演示条件格式的经典实例,显示包含最小值和最大值的行。代码如下:Sub?FindMinMax()?Finalrow=Cells(,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:=xlExp
13、ression,Formula1:=rc3=min(c3)?.FormatConditions(2).Interior.ColorIndex=6?用黄色底纹标出?End?WithEnd?Sub?如果设置一个指向单元格C3的条件格式,这种格式将失败,因为Excel将C3解释为第3列。?七、VBA中的R1C1样式数组公式?数组公式必须是R1C1公式。?数组公式是功能强大的“超级公式”,被称为CSE公式,因为用户必须按Ctrl+Shift+Enter键来输入它们,如:?=SUM(A$2:A7*B$2:B7)这是个数组公式,很好理解。?虽然在用户界面中显示A1样式,但输入数组公式要使用R1C1表示法:
14、Sub?EnterArrayFormulas()?Finalrow=Cells(Rows.Count,1).End(xlUp).Row?Cells(Finalrow+2,2).Value=乘积和?Cells(Finalrow+2,3).FormulaArray=sum(R2C-2:R-2C-2*R2C-1:R-2C-1)End?Sub?上述代码中的:Cells(Finalrow+2,3).FormulaArray=sum(R2C-2:R-2C-2*R2C-1:R-2C-1)可以简化为:Cells(Finalrow+2,3).FormulaArray=sum(R2:R-2?C-2*R2:R-2?
15、C-1)后面有介绍,但要注意:红色和蓝色代码之间有一个空格。?【小技巧】可以采用下面的方法快速输入R1C1公式:?在任何单元格中输入常规的A1公式或数组公式,选择该单元格并切换到VBA编辑器。然后按Ctrl+G键打开立即窗口,再输入:?Activecell.FormulaR1C1或者?PrintActivecell.FormulaR1C1?并按回车键,Excel将把公式栏中的公式转换为R1C1样式。?以下内容来自第二个帖子,。?单元格引用有A1和R1C1两种形式,两种形式中又包含了绝对引用和相对引用两种不同的变化。?单个区域的引用:?先来讲一下R1C1方式的引用?对于G2:J2这样一个单元格区
16、域,使用R1C1来引用,可以写作:R2C7:R2C10,也可以写作:R2C10:R2C7。但R必须在C前面,“C10R2:C7R2”这样的写法是无效的。?对于这样单行的区域引用,可以使用简写的引用方式,即将R2C7:R2C10中的红色部分省去,简写为R2C7:C10。?如果对于单列的区域引用,例如G2:G10,则可以将R2C7:R10C7中的红色部分省去,简写为R2:R10C7。?为什么可以这样简写,这样的简写有何规律?在没有官方的解释之前,为了便于大家的理解和记忆,我给出一个我自己的解释思路,请看下面的“关于引用运算符”:?关于引用运算符:?Excel中包含了3种引用运算符,用于表示对单元格
17、的引用,?一种是冒号,称为区域运算符,这是最常见的,如=sum(A2:B10),表示引用冒号两边单元格所围成的矩形区域;?一种是逗号,称为联合运算符,如=RANK(A1,(A1:A10,C1:C10),表示同时引用逗号两边的两个区域;?还有一种是空格,称为交叉运算符,表示引用空格两边的两个区域的交集,如=SUM(A1:B5A4:D9)即等价于=SUM(A4:B5)。这个交叉运算符也是我这里要重点引入的一个使用技巧。?对于G2:J2这个单元格区域,如果换一个角度来看,可以看作是G:J这4列与第2行所构成的交叉区域。因此,如果用交叉运算符和R1C1样式来引用的话,可以写作:R2C7:C10,即表示
18、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)如果使
19、用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?
20、此时,来做一个类似于合并同类项的变形,可以简化为:?(R13,R15,R17)C7:C10?将几个行号用逗号相连接表示联合运算,同时外面用括号包围以后,再与C7:C10区域使用空格进行连接,形成交叉运算关系,得到了我们所需要的三个行区域的同时引用。?这个引用方式就是我的公式中最终所取用的最简短的引用方式。?可以跟A1引用方式做个比较,上述区域的A1引用写法为:(13:13,15:15,17:17)G:J,结合题目的实际情况,可以比较得出A1形式写法的字符代价要远高于R1C1的形式。?至于我两个公式中(R00!,R00!,R00)C7!:C33和(R0R000R00,0)C7!:C33两种不同Text函数自定义格式的区别,主要在于后面这种格式利用了数字格式中的千分位符可以自动生成“逗号”的便利,从本质上来说两者的单元格引用方式是一致的。?写了这么多,也不知道大家能否看懂,如果结合下面这个附件,相信可以更容易理解一些:?附件:
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1