Excel中R1C1樣式引用詳解

1、Excel中R1C1樣式引用詳解這篇文章根據(jù)excelhome論壇中的兩個(gè)帖子進(jìn)行整理，稍作了修改。以下內(nèi)容來(lái)自第一個(gè)帖子，點(diǎn)擊這里可以查看原文。一、引用單元格：R1C1樣式與A1樣式比較DanBricklin和BobFrankston使用A1表示電子表格左上角的單元格， MitchKapor在Lotus 1-2-3中也是使用這種編址方案。Microsoft試圖改變這種趨勢(shì)，采用了名為 R1C1樣式編址方案。單元格 A1稱為R1C1,因?yàn)樗挥诘谝恍校谝涣?Row 1, Column 1)。在20世紀(jì)80年代和90年代初期，A1樣式稱為了標(biāo)準(zhǔn)，Microsoft公司認(rèn)識(shí) 到了危機(jī)，最終讓ex

2、cel接受了 A1樣式，并同時(shí)支持R1C1樣式編址方案。當(dāng) 刖，excel默認(rèn)使用A1樣式。為什么學(xué)習(xí)R1C1樣式？答案是：1, excel的宏錄制器采用的是R1C1樣式錄制公式。2, R1C1樣式比之A1樣式有更高的效率，尤其是對(duì)于公式，編寫的代碼效率 會(huì)更圖03,在BVA編輯器中，創(chuàng)建數(shù)組公式或基于公式設(shè)置條件格式時(shí)，采用的是 R1C1樣式輸入公式。怎么切換R1C1樣式？單擊O巾ce按鈕選擇Excel選項(xiàng)，在公式類別中選擇復(fù)選框 "R1CGI用樣式版面上唯一不同是列標(biāo) A,B,C-變成數(shù)字1,2,3,單元格C5變?yōu)镽5C2二、EXCEL公式的神奇之處如動(dòng)畫所示，Excel能智能的

3、填充公式，并向下復(fù)制，第一次看到一定感覺(jué)非 常驚奇。實(shí)際這并不驚奇，因?yàn)椋珽xcel內(nèi)部使用的是R1C1樣式的公式，以A1樣式顯示地址和公式。如果將動(dòng)畫所示的工作表切換成 R1C1樣式表示法，將發(fā)現(xiàn)C2:C7的公式都是形同的。三、在VBA中采用A1樣式與R1C1樣式之比較如上述動(dòng)畫實(shí)例，如用 A1樣式編寫代碼，代碼可以類似下面這樣：Sub chengji()Dim Finalrow As IntegerFinalrow = Cells(Rows.Count, 2).End(xlUp).Row'求第二列數(shù)據(jù)行數(shù)Range("c2").Formula = "=

4、a2*b2"Range("C2").Copy Destination:=Range("C2:C"&Finalrow) End Sub上述代碼在第二行輸入公式，再向下復(fù)制公式如果用R1C1樣式只需一條語(yǔ)句就可整列輸入公式Sub chengji()Dim Finalrow As IntegerFinalrow = Cells(Rows.Count, 2).End(xlUp).Row'求第二列數(shù)據(jù)行數(shù)Range("c2:c" &Finalrow).FormulaR1C1 ="=RC-1*RC-2&

5、quot; End Sub使用R1C1樣式的優(yōu)點(diǎn)是，所有C列的公式都是相同的，不需要改變四、怎么引用R1C1樣式R1C1樣式采用R來(lái)表示行，C來(lái)表示列4.1 樣式的相對(duì)引用對(duì)于列正數(shù)表示向右移指定數(shù)量的列，負(fù)數(shù)反之。對(duì)于行正數(shù)表示向下移指定數(shù)量的行，負(fù)數(shù)反之。如果省略掉R或C后面的方括號(hào)，表示和引用單元格在同行或同列。4.2 樣式的絕對(duì)引用在A1樣式中使用絕對(duì)引用要在行號(hào)或列號(hào)字母 前使用$。但在R1C1樣式中 只需省略方括號(hào)就行了，是不是很簡(jiǎn)單！!！如下所示代碼：Sub huizong()Dim Finalrow As IntegerFinalrow = Cells(Rows.Count,

6、2).End(xlUp).RowCells(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ù)的和，通過(guò)使用R1C1混合引用，可以使用公式求行數(shù)不確定的數(shù)據(jù)，4.3 引用整行和整列有時(shí)候需要編寫整列的公式。例如求G列的最大值，如不知道G列包含多少行，可在單元格中輸入公式=MAX ($G:$G),要找出第一行中最大的值可用 =MAX($1:$1)或 R1C1 公式=MAX

7、(R1)?？梢哉?、整列使用相對(duì)引用。要計(jì)算當(dāng)前單元格上一行的平均值，可用=AVERAGE(R-1)如何在D5引用其周圍的單元格?I RCpRC-1RC1RC2R1CR2C泳圖演示了怎么在D5中引用其周圍的單元格五、R1C1樣式的經(jīng)典實(shí)例，一種有趣的行為創(chuàng)建R1C1公式實(shí)際上比A1公式更直觀。一個(gè)演示 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 Multiplic

8、ationtable8()Range("b1:m1").Value= Array(1, 2, 3, 4, 5, 6, 7, 8,9, 10, 11, 12)Range("b1:m1").Font.Bold =TrueRange("b1:m1").CopyRange("a2:a13").PasteSpecial TransposesTrueRange("b2:m13").FormulaR1C1 = "=rc1*r1c"Cells.EntireColumn.AutoFit'

9、;最合適的列寬End Sub5.2 一種有趣的行為嘗試以下操作，將單元格指針移到F6,單擊 開發(fā)工具“-"錄制宏；'然后單擊 開發(fā)工具”一使用相對(duì)引用“，輸入公式=a1并按ctrl+enter鍵，以保留在F6鍵 中。單擊停止錄制”按鈕。將得到一個(gè)只包含一行的代碼的宏，它在當(dāng)前的單元格輸入公式，該公式引用向上5行，向左5列的單元格：Sub 宏 1()Selection.FormulaR1C1 = "=R-5C-5”End Sub現(xiàn)在將單元格指針移到A1并運(yùn)行剛才的宏，你可能會(huì)認(rèn)為將導(dǎo)致運(yùn)行錯(cuò)誤1004,但實(shí)際并沒(méi)有出現(xiàn)這種錯(cuò)誤。運(yùn)行宏時(shí)，單元格 A1中的公式指向=XF

10、1048572 ( Excel2003指向=IR65532),這意味著R1C1公式從表的左側(cè)繞回到右側(cè)。這是一個(gè)很有趣的行為，但是可能宏將提供一個(gè)與用戶期望不同的結(jié)果！ ！ ！ ！ ！ ！乘法表實(shí)例:扉 融:辨號(hào)嫄 尸虹 t跋E2& =|AE|1Habcdefghijklm N 0 P Q IIM M HIIIM M M !1 1- Mil * Illi * Illi ! M II M 1M M HIIIM 1 I Illi 1 j 1 2 3; 4; 5 6l 7 8 9 10 it 12| I I f: 二 二： 二： 二 二 二 11 二 Tsvea&*' V W

11、B'4V' V *，*" W&.y -fevCwB'#WW-ll-r tbs-bb !«««»> a-b-rbvb!««->»«»= a-ei2 H 1 1 3； 4； sj 6i 7 8 gj 10 111 12； j j j"* ！!<!, a if i. mi«4.aiiMii. sa«aiiaiiAa.aii«ii. saa *4 工* xauii. nii =a aq aiiii *-a u， a

12、，hiijiii n aqxau«a. hiiab k iuji aii«a. muaita !_ '：! an，* a 上！，an，廿 a tkaq aa.iiiidiiiia u=q aaa. muanu iLq j34567891011121314151617L819101 1 i 6； 8； 1012 14 16 18! 20 22 24； ffMIffimHIIRWTf+-i.-小一k.產(chǎn)3| 3; 6| 9“2|15網(wǎng) 21 闞 30 33 361專郊通 碗喇M 鈿d 4： 8：12|16|20|24：28 32； 36| 40； 4d4,醒®

13、 & $ 啟盯 » jj51 510 15；加乃1初打 4C| 45! 50 551 60i6|»|18|24|30|364248|54!6066| 721717； Mi 21| 2835； 424956|研70：77j 84：d8：16：24"2；40；48：5664：72：8088詼:, * i 4»« 事*& "4""" ""k199 427圍45就 6372；81|904血4*T>S*.* 士* 工 L JS * *=9 &4 L !*<上占

14、！* *1 * *W4*I&4L*4* L 4 *-U * * 4 *lol 10 20；3ol40)50|60|70 80； 90! 100； 110 聞ji-ji-es-i,-ieii-i-i- 11|11|22|33|44)55|66|77 88； 99|110=121)1321 121 12 24：加加 60山& %； 108! 120 1321144! 3 -二一； ，丁 二i .1:3 I國(guó)二可患.1戶bam演 NMj囂山I)-8 nUr«ject (mCtES lUkl)I 月后”出衣虱可寥哥 jijbSanl_tMleB nifraject (r

15、71;41att jp)4|舊。阱底Eicil糖卻施式1屆觀電 Thi引arib«ik爭(zhēng)設(shè)flifujiM篋車單制作,口“) 司母TUkojHt Ct作勘,口”)* FlLcr”*ft；攜雪 She 乳 1 fcilteeJ j就靠然立出(| Sh*et3IK IbidlartibHk3淖住眥IBHII-j.4=-4- -<> -I4-5»六、條件格式中的 R1C1樣式應(yīng)用設(shè)置條件格式時(shí)，必須使用R1C1公式，這很重要。文檔沒(méi)有明確之處這一點(diǎn)，但如果不用R1C1公式，有時(shí)可能出問(wèn)題，有研究發(fā)現(xiàn)，如果用A1公式，每對(duì)50 個(gè)單元格設(shè)置條件格式，將有一個(gè)單元格出現(xiàn)奇

16、怪的行為。因?yàn)閷?A1引用轉(zhuǎn)換為R1C1引用有時(shí)存在二義性，例如，R2表示一個(gè)單元格，但可能被錯(cuò)誤理解 為整個(gè)第二行。FormatCond讓ions對(duì)象用于設(shè)置條件格式。每個(gè)單元格可以有3個(gè)Formatconditions,下面的代碼首先遍歷所有工作表，刪除每個(gè)工作表中的條件格式，然后遍歷每個(gè)工作表中所有的非空單元格，并應(yīng)用兩種條件格式。在第一種條件格式中，類型為 xlExpression,這意味著使用的是 公式"語(yǔ)法首先Foumulal指定的公式采用的是 R1C1表示法。第二個(gè)條件格式使用xlCellValue類型，這需要指定一個(gè)運(yùn)算符和一個(gè)值。在添加條件后，為條件1和條件2設(shè)置字

17、體的ColorindexSub ApplySpecialFormattingALL()For Each ws In ThisWorkbook.Worksheets ws.UsedRange.FormatConditions.Delete For Each cell In ws.UsedRange.CellsIf Not IsEmpty(cell) Then'單元格值是任意錯(cuò)誤值時(shí)，'把字體顏色設(shè)置為與單元格底色相同的顏色(即看不出錯(cuò)誤值)cell.FormatConditions.AddType:=xlExpression, Formula1:="=or(ISERR(

18、RC),isna(RC)” cell.FormatConditions(1).Font.Color =cell.Interior.Color'單元格值小于0的，全部用紅色字體標(biāo)出cell.FormatConditions.AddType:=xlCellValue, Operator:=xlLess, Formula1:="0" cell.FormatConditions(2).Font.ColorIndex =3End IfNext cellNext wsEnd Sub一個(gè)演示條件格式的經(jīng)典實(shí)例，顯示包含最小值和最大值的行。代碼如下：Sub FindMinMax()

19、Finalrow = Cells(Cells.Rows.Count,1).End(xlUp).RowWith Range("a2:c" &Finalrow).FormatConditions.Delete.FormatConditions.Add Type:=xlExpression,Formula1:="=rc3=max(c3)”.FormatConditions(1).Interior.ColorIndex = 4用綠色底紋標(biāo)出.FormatConditions.Add Type:=xlExpression,Formula1:="=rc3=m

20、in(c3)”.FormatConditions(2).Interior.ColorIndex = 6用黃色底紋標(biāo)出End WithEnd Sub如果設(shè)置一個(gè)指向單元格 C3的條件格式，這種格式將失敗，因?yàn)镋xcel將C3解釋為第3列。七、VBA中的R1C1樣式數(shù)組公式數(shù)組公式必須是R1C1公式。數(shù)組公式是功能強(qiáng)大的 超級(jí)公式"，被稱為CSE公式，因?yàn)橛脩舯仨毎碈trl+Shift+Enter鍵來(lái)輸入它們，如：=SUM(A$2:A7*B$2:B7)這是個(gè)數(shù)組公式，很好理解。雖然在用戶界面中顯示 A1樣式，但輸入數(shù)組公式要使用 R1C1表示法:Sub EnterArrayFormula

21、s()Finalrow = Cells(Rows.Count, 1).End(xlUp).RowCells(Finalrow + 2, 2).Value ="乘積和"Cells(Finalrow + 2, 3).FormulaArray ="=sum(R2C-2:R-2C-2*R2C-1:R-2C-1)H End Sub上述代碼中的：Cells(Finalrow + 2, 3).FormulaArray ="二sum(R2c-2:R-2C-2*R2c-1:R-2C-1)”可以簡(jiǎn)化為：Cells(Finalrow + 2, 3).FormulaArray

22、="=sum(R2:R-2C-2*R2:R-2C-1)"后面有介紹，但要注意：紅色和藍(lán)色代碼之間有一個(gè)空格。【小技巧】可以采用下面的方法快速輸入R1C1公式：在任何單元格中輸入常規(guī)的A1公式或數(shù)組公式，選擇該單元格并切換到VBA編輯器。然后按Ctrl+G鍵打開立即窗口，再輸入：?Activecell.FormulaR1C1 或者Print Activecell.FormulaR1C1并接回車鍵，Excel將把公式欄中的公式轉(zhuǎn)換為 R1C1樣式。以下內(nèi)容來(lái)自第二個(gè)帖子，點(diǎn)擊這里可以查看原文。單元格引用有A1和R1C1兩種形式，兩種形式中又包含了絕對(duì)引用和相對(duì)引用兩種不同的變化

23、。單個(gè)區(qū)域的引用:先來(lái)講一下R1C1方式的引用對(duì)于G2:J2這樣一個(gè)單元格區(qū)域，使用R1C1來(lái)引用，可以寫作：R2c7:R2c10, 也可以寫作：R2c10:R2c7。1 R必須在C前面，“ C10R2:C7R也樣的寫法是 無(wú)效的。對(duì)于這樣單行的區(qū)域引用，可以使用簡(jiǎn)寫的引用方式，即將R2c7:R2c10中的紅色部分省去，簡(jiǎn)寫為 R2C7:C10O如果對(duì)于單列的區(qū)域引用，例如 G2:G10,則可以將R2c7:R10C7中的紅色部分省去，簡(jiǎn)寫為R2:R10C7O為什么可以這樣簡(jiǎn)寫，這樣的簡(jiǎn)寫有何規(guī)律？在沒(méi)有官方的解釋之前，為了便于大家的理解和記憶，我給出一個(gè)我自己的解釋思路，請(qǐng)看下面的關(guān)于引用運(yùn)算

24、符”：關(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)算符，in=RANK(A1,(A1:A10,C1:C10),表示同時(shí)引用逗號(hào)兩邊的兩個(gè)區(qū)域；還有一種是空格，稱為 交叉運(yùn)算符，表示引用空格兩邊的兩個(gè)區(qū)域的交集， iO=SUM(A1:B5 A4:D9)即等價(jià)于=SUM(A4:B5)。這個(gè)交叉運(yùn)算符也是我這里要重 點(diǎn)引入的一個(gè)使用技巧。對(duì)于G2:J2這個(gè)單元格區(qū)域，如果換一個(gè)角度來(lái)看，可以看作是G:J這4列與 第2行所構(gòu)成的交叉區(qū)域。因此，如果用交叉運(yùn)算符和R1C1樣式來(lái)引用的話，可以寫作：R2 C7:C10,即表示R2與C7:C10這兩個(gè)區(qū)域的交叉區(qū)域。因此R2c7:R2c10等價(jià)于(R2 C7:C10),而這個(gè)形式與上面的簡(jiǎn)寫形式R2c7:C10十分相似；同理，G2:G10可以表示為R2c7:R10C7,等彳于(R2:R10 C7),這個(gè)形式于其 簡(jiǎn)寫形式R2:R10C7也十分相似。以上就是對(duì)于同行或同列的 R1C1引用簡(jiǎn)寫方式的一些理解，但這個(gè)題目中真 正起到大作用的并非那個(gè)簡(jiǎn)寫方式(雖然也可以縮短不少字符)，而是引入交叉