Excel中RC樣式引用詳解

1、Excel中R1C1樣式引用詳解    這篇文章根據(jù)excelhome論壇中的兩個(gè)帖子進(jìn)行整理，稍作了修改。    以下內(nèi)容來自第一個(gè)帖子，點(diǎn)擊這里可以查看原文。    一、引用單元格：R1C1樣式與A1樣式比較    DanBricklin和BobFrankston使用A1表示電子表格左上角的單元格，Mitch Kapor在Lotus 1-2-3中也是使用這種編址方案。    Microsoft試圖改變這種趨勢(shì)，采用了名為R1C1樣式編址方案。

2、單元格A1稱為R1C1，因?yàn)樗挥诘谝恍?，第一列（Row 1, Column 1）。    在20世紀(jì)80年代和90年代初期，A1樣式稱為了標(biāo)準(zhǔn)，Microsoft公司認(rèn)識(shí)到了危機(jī)，最終讓excel接受了A1樣式，并同時(shí)支持R1C1樣式編址方案。當(dāng)前， excel默認(rèn)使用A1樣式。    為什么學(xué)習(xí)R1C1樣式?    答案是：    1，excel的宏錄制器采用的是R1C1樣式錄制公式。    2，R1C1樣式比之A1樣式有更高的效率

3、，尤其是對(duì)于公式，編寫的代碼效率會(huì)更高。    3，在BVA編輯器中，創(chuàng)建數(shù)組公式或基于公式設(shè)置條件格式時(shí)，采用的是R1C1樣式輸入公式。    怎么切換R1C1樣式？    單擊Ofice按鈕選擇Excel選項(xiàng)，在公式類別中選擇復(fù)選框“R1C1引用樣式”。版面上唯一不同是列標(biāo)A,B,C-變成數(shù)字1,2,3，單元格C5變?yōu)镽5C2    二、EXCEL公式的神奇之處      如動(dòng)畫所示，Excel 能智能的填充公式

4、，并向下復(fù)制，第一次看到一定感覺非常驚奇。    實(shí)際這并不驚奇，因?yàn)?，Excel內(nèi)部使用的是R1C1樣式的公式，以A1樣式顯示地址和公式。如果將動(dòng)畫所示的工作表切換成R1C1樣式表示法，將發(fā)現(xiàn)C2:C7的公式都是形同的。    三、在VBA中采用A1樣式與R1C1樣式之比較    如上述動(dòng)畫實(shí)例，如用A1樣式編寫代碼，代碼可以類似下面這樣：Sub chengji()    Dim Finalrow As Integ

5、er    Finalrow = Cells(Rows.Count, 2).End(xlUp).Row '求第二列數(shù)據(jù)行數(shù)    Range("c2").Formula = "=a2*b2"    Range("C2").Copy Destination:=Range("C2:C" & Finalrow)End Sub    上述代

6、碼在第二行輸入公式，再向下復(fù)制公式    如果用R1C1樣式只需一條語句就可整列輸入公式Sub chengji()    Dim Finalrow As Integer    Finalrow = Cells(Rows.Count, 2).End(xlUp).Row '求第二列數(shù)據(jù)行數(shù)    Range("c2:c" & Finalrow).FormulaR1C

7、1 = "=RC-1*RC-2"End Sub    使用R1C1樣式的優(yōu)點(diǎn)是，所有C列的公式都是相同的，不需要改變    四、怎么引用R1C1樣式    R1C1樣式采用R來表示行，C來表示列    4.1 樣式的相對(duì)引用    對(duì)于列正數(shù)表示向右移指定數(shù)量的列，負(fù)數(shù)反之。    對(duì)于行正數(shù)表示向下移指定數(shù)量的行，負(fù)數(shù)反之。    如果省略掉R或

8、C后面的方括號(hào)，表示和引用單元格在同行或同列。    4.2 樣式的絕對(duì)引用    在A1樣式中使用絕對(duì)引用要在行號(hào)或列號(hào)字母前使用$。但在R1C1樣式中只需省略方括號(hào)就行了，是不是很簡(jiǎn)單!    如下所示代碼:Sub huizong()    Dim Finalrow As Integer    Finalrow = Cells(Rows.Count, 2).End(xlUp).Row

9、    Cells(Finalrow + 1, 1).Value = "匯總"    Cells(Finalrow + 2, 1).Resize(1, 3).FormulaR1C1 = "=SUM(R2C:R-2C)"End Sub    引用R2C:R-2C表示將當(dāng)前列第2行到上2行中，同列數(shù)據(jù)的和，通過使用R1C1混合引用，可以使用公式求行數(shù)不確定的數(shù)據(jù)，    4.3 引用整行和整列 

10、60;  有時(shí)候需要編寫整列的公式。例如求G列的最大值，如不知道G列包含多少行，可在單元格中輸入公式=MAX（$G:$G），要找出第一行中最大的值可用 =MAX($1:$1)或R1C1公式=MAX(R1)。    可以整行、整列使用相對(duì)引用。要計(jì)算當(dāng)前單元格上一行的平均值，可用=AVERAGE(R-1)    如何在D5引用其周圍的單元格？    五、R1C1樣式的經(jīng)典實(shí)例 ，一種有趣的行為    創(chuàng)建R1C1公式實(shí)際上比A1公式更直觀。一個(gè)演示

11、R1C1公式的經(jīng)典實(shí)例是創(chuàng)建乘法表。在excel中，使用單個(gè)混合引用公式就可創(chuàng)建乘法表。    5.1 創(chuàng)建乘法表    在B1：M1中輸入數(shù)字1-12，在A2:a13中也輸入數(shù)字1-12 ，現(xiàn)在創(chuàng)建b2：m13中所有單元格公式，它計(jì)算第一行和第一列的乘積。用R1C1樣式公式代碼如下：Sub Multiplicationtable8()    Range("b1:m1").Value = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11

12、, 12)    Range("b1:m1").Font.Bold = True    Range("b1:m1").Copy    Range("a2:a13").PasteSpecial Transpose:=True    Range("b2:m13").FormulaR1C1 = "=rc1*r1c" 

13、0;  Cells.EntireColumn.AutoFit   '最合適的列寬End Sub    5.2 一種有趣的行為    嘗試以下操作，將單元格指針移到F6，單擊“開發(fā)工具”-”錄制宏“，然后單擊“開發(fā)工具”-“使用相對(duì)引用”，輸入公式=a1并按ctrl+enter鍵，以保留在F6鍵中。單擊“停止錄制”按鈕。    將得到一個(gè)只包含一行的代碼的宏，它在當(dāng)前的單元格輸入公式，該公式引用向上5行，向左5列的單元格：Sub 

14、宏1()    Selection.FormulaR1C1 = "=R-5C-5"End Sub    現(xiàn)在將單元格指針移到A1并運(yùn)行剛才的宏，你可能會(huì)認(rèn)為將導(dǎo)致運(yùn)行錯(cuò)誤1004，但實(shí)際并沒有出現(xiàn)這種錯(cuò)誤。運(yùn)行宏時(shí)，單元格A1中的公式指向=XF1048572（Excel2003指向=IR65532），這意味著R1C1公式從表的左側(cè)繞回到右側(cè)。這是一個(gè)很有趣的行為，但是可能宏將提供一個(gè)與用戶期望不同的結(jié)果！    乘法表實(shí)例：   

15、60;六、條件格式中的R1C1樣式應(yīng)用    設(shè)置條件格式時(shí)，必須使用R1C1公式，這很重要。文檔沒有明確之處這一點(diǎn)，但如果不用R1C1公式，有時(shí)可能出問題，有研究發(fā)現(xiàn)，如果用A1公式，每對(duì) 50個(gè)單元格設(shè)置條件格式，將有一個(gè)單元格出現(xiàn)奇怪的行為。因?yàn)閷1引用轉(zhuǎn)換為R1C1引用有時(shí)存在二義性，例如，R2表示一個(gè)單元格，但可能被錯(cuò)誤理解為整個(gè)第二行。    FormatConditions對(duì)象用于設(shè)置條件格式。每個(gè)單元格可以有3個(gè)FormatConditions，下面的代碼首先遍歷所有工作表，刪除每個(gè)工作表中的條件格式，然后

16、遍歷每個(gè)工作表中所有的非空單元格，并應(yīng)用兩種條件格式。    在第一種條件格式中，類型為xlExpression，這意味著使用的是“公式”語法。首先Foumula1指定的公式采用的是R1C1表示法。    第二個(gè)條件格式使用xlCellValue類型，這需要指定一個(gè)運(yùn)算符和一個(gè)值。在添加條件后，為條件1和條件2設(shè)置字體的ColorIndexSub ApplySpecialFormattingALL()    For Each ws In This

17、Workbook.Worksheets        ws.UsedRange.FormatConditions.Delete        For Each cell In ws.UsedRange.Cells            If Not IsEmp

18、ty(cell) Then                '單元格值是任意錯(cuò)誤值時(shí)，                '把字體顏色設(shè)置為與單元格底色相同的顏色(即看不出錯(cuò)誤值)      

19、0;         cell.FormatConditions.Add Type:=xlExpression, Formula1:="=or(ISERR(RC),isna(RC)"                cell.FormatConditions(1).Font.Color = cell.Interior.Color&

20、#160;               '單元格值小于0的，全部用紅色字體標(biāo)出                cell.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="0"

21、                cell.FormatConditions(2).Font.ColorIndex = 3            End If        Next cell  

22、60; Next wsEnd Sub    一個(gè)演示條件格式的經(jīng)典實(shí)例，顯示包含最小值和最大值的行。代碼如下：Sub FindMinMax()    Finalrow = Cells(Cells.Rows.Count, 1).End(xlUp).Row    With Range("a2:c" & Finalrow)        

23、;.FormatConditions.Delete        .FormatConditions.Add Type:=xlExpression, Formula1:="=rc3=max(c3)"        .FormatConditions(1).Interior.ColorIndex = 4  '用綠色底紋標(biāo)出      

24、;  .FormatConditions.Add Type:=xlExpression, Formula1:="=rc3=min(c3)"        .FormatConditions(2).Interior.ColorIndex = 6  '用黃色底紋標(biāo)出    End WithEnd Sub    如果設(shè)置一個(gè)指向單元格C3的條件格式，這種格式將失敗，

25、因?yàn)镋xcel將C3解釋為第3列。    七、VBA中的R1C1樣式數(shù)組公式    數(shù)組公式必須是R1C1公式。    數(shù)組公式是功能強(qiáng)大的“超級(jí)公式”，被稱為CSE公式，因?yàn)橛脩舯仨毎碈trl+Shift+Enter鍵來輸入它們，如：    =SUM(A$2:A7*B$2:B7)這是個(gè)數(shù)組公式，很好理解。    雖然在用戶界面中顯示A1樣式，但輸入數(shù)組公式要使用R1C1表示法：Sub EnterArrayFormu

26、las()    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).For

27、mulaArray = "=sum(R2C-2:R-2C-2*R2C-1:R-2C-1)"可以簡(jiǎn)化為：Cells(Finalrow + 2, 3).FormulaArray = "=sum(R2:R-2 C-2*R2:R-2 C-1)"后面有介紹，但要注意：紅色和藍(lán)色代碼之間有一個(gè)空格。   【小技巧】可以采用下面的方法快速輸入R1C1公式：    在任何單元格中輸入常規(guī)的A1公式或數(shù)組公式，選擇該單元格并切換到VBA編輯器。然后按Ctrl+G鍵打開立即窗口，再輸入：

28、0;   ?Activecell.FormulaR1C1 或者    Print Activecell.FormulaR1C1     并按回車鍵，Excel將把公式欄中的公式轉(zhuǎn)換為R1C1樣式。    以下內(nèi)容來自第二個(gè)帖子，點(diǎn)擊這里可以查看原文。    單元格引用有A1和R1C1兩種形式，兩種形式中又包含了絕對(duì)引用和相對(duì)引用兩種不同的變化。    單個(gè)區(qū)域的引用：    先

29、來講一下R1C1方式的引用    對(duì)于G2:J2這樣一個(gè)單元格區(qū)域，使用R1C1來引用，可以寫作：R2C7:R2C10，也可以寫作：R2C10:R2C7。但R必須在C前面，“C10R2:C7R2”這樣的寫法是無效的。    對(duì)于這樣單行的區(qū)域引用，可以使用簡(jiǎn)寫的引用方式，即將R2C7:R2C10中的紅色部分省去，簡(jiǎn)寫為R2C7:C10。    如果對(duì)于單列的區(qū)域引用，例如G2:G10，則可以將R2C7:R10C7中的紅色部分省去，簡(jiǎn)寫為R2:R10C7。    為什么可以這樣

30、簡(jiǎn)寫，這樣的簡(jiǎn)寫有何規(guī)律？在沒有官方的解釋之前，為了便于大家的理解和記憶，我給出一個(gè)我自己的解釋思路，請(qǐng)看下面的“關(guān)于引用運(yùn)算符”：    關(guān)于引用運(yùn)算符：    Excel中包含了3種引用運(yùn)算符，用于表示對(duì)單元格的引用，    一種是冒號(hào)，稱為區(qū)域運(yùn)算符，這是最常見的，如=sum(A2:B10)，表示引用冒號(hào)兩邊單元格所圍成的矩形區(qū)域；    一種是逗號(hào)，稱為聯(lián)合運(yùn)算符，如=RANK(A1,(A1:A10,C1:C10)，表示同時(shí)引用逗號(hào)兩邊的兩個(gè)區(qū)域； 

31、0;  還有一種是空格，稱為交叉運(yùn)算符，表示引用空格兩邊的兩個(gè)區(qū)域的交集，如=SUM(A1:B5 A4:D9)即等價(jià)于=SUM(A4:B5)。這個(gè)交叉運(yùn)算符也是我這里要重點(diǎn)引入的一個(gè)使用技巧。    對(duì)于G2:J2這個(gè)單元格區(qū)域，如果換一個(gè)角度來看，可以看作是G:J這4列與第2行所構(gòu)成的交叉區(qū)域。因此，如果用交叉運(yùn)算符和R1C1樣式來引用的話，可以寫作：R2 C7:C10，即表示R2與C7:C10這兩個(gè)區(qū)域的交叉區(qū)域。    因此R2C7:R2C10等價(jià)于(R2 C7:C10)，而這個(gè)形式與上面的簡(jiǎn)寫形式R2C7:C10十分相似；    同理，G2:G10可以表示為R2C7:R10C7，等價(jià)于(R2:R10 C7)，這個(gè)形式于其簡(jiǎn)寫形式R2:R10C7也十分相似。    以上就是對(duì)于同行或同列的R1C1引用簡(jiǎn)寫方式的一些理解，但這個(gè)題目中真正起到大作用的并非那個(gè)簡(jiǎn)寫方式（雖然也可以縮短不少字符），而是引入交叉運(yùn)算符以后的引用方式。請(qǐng)繼續(xù)往下看。    交叉運(yùn)算符同樣也可以應(yīng)用在A1引用方式中：    例如(R2 C7:C10)如果使用A1引用方式，則可以寫作：(2:2 G:J)&