Excel中R1C1樣式引用詳解

1、Excel中R1C1樣式引用詳解這篇文章根據(jù)excelhome論壇中的兩個(gè)帖子進(jìn)行整理，稍作了修改。以下內(nèi)容來自第一個(gè)帖子，。、引用單元格：R1C1樣式與A1樣式比較DanBricklin和BobFrankston使用A1表示電子表格左上角的單元格，MitchKapor在Lotus1-2-3中也是使用這種編址方案。Microsoft試圖改變這種趨勢(shì)，采用了名為R1C1樣式編址方案。單元格A1稱為R1C1,因?yàn)樗欢〉谝恍?，第一?Row1,Column1)。在20世紀(jì)80年代和90年代初期，A1樣式稱為了標(biāo)準(zhǔn)，Microsoft公司認(rèn)識(shí)到了危機(jī)，最終讓excel接受了A1樣式，并同時(shí)支持R1C

2、1樣式編址方案。當(dāng)前，？excel默認(rèn)使用A1樣式。為什么學(xué)習(xí)R1C1樣式答案是：1, excel的宏錄制器采用的是R1C1樣式錄制公式。R1C1樣式比之A1樣式有更高的效率，尤其是對(duì)丁公式，編寫的代碼效率會(huì)更局2, 在BVA編輯器中，創(chuàng)建數(shù)組公式或基于公式設(shè)置條件格式時(shí)，采用的是R1C1樣式輸入公式。怎么切換R1C1樣式？單擊Ofice按鈕選擇Excel選項(xiàng)，在公式類別中選擇復(fù)選框“R1C引用樣式”。版面上唯一不同是列標(biāo)A,B,C-變成數(shù)字1,2,3,單元格C5變?yōu)镽5C2二、EXCEL公式的神奇之處如動(dòng)畫所示，Excel能智能的填充公式，并向下復(fù)制，第一次看到一定感覺非常驚奇。實(shí)際這并不驚

3、奇，因?yàn)?，Excel內(nèi)部使用的是R1C1樣式的公式，以A1樣式顯示地址和公式。如果將動(dòng)畫所示的工作表切換成R1C1樣式表示法，將發(fā)現(xiàn)C2:C7的公式都是形同的。三、在VBA中采用A1樣式與R1C1樣式之比較如上述動(dòng)畫實(shí)例，如用A1樣式編寫代碼，代碼可以類似下面這樣：Subchengji()DimFinalrowAsInteger'求第二列數(shù)據(jù)行數(shù)Finalrow=Cells,2).End(xlUp).RowRange("c2").Formula="=a2*b2"Range("C2").CopyDestination:=Rang

4、e("C2:C"&Finalrow)EndSub上述代碼在第二行輸入公式，再向下復(fù)制公式如果用R1C1樣式只需一條語(yǔ)句就可整列輸入公式Subchengji()DimFinalrowAsIntegerFinalrow=Cells,2).End(xlUp).Row'求第二歹0數(shù)據(jù)行數(shù)Range("c2:c"&Finalrow).FormulaR1C1="=RC-1*RC-2"EndSub使用R1C1樣式的優(yōu)點(diǎn)是，所有C列的公式都是相同的，不需要改變四、怎么引用R1C1樣式R1C1樣式采用R來表示行，C來表示列樣式的

5、相對(duì)引用對(duì)于列正數(shù)表示向右移指定數(shù)量的列，負(fù)數(shù)反之對(duì)于行正數(shù)表示向下移指定數(shù)量的行，負(fù)數(shù)反之。如果省略掉R或C后面的方括號(hào)，表示和引用單元格在同行或同列。樣式的絕對(duì)引用在A1樣式中使用絕對(duì)引用要在行號(hào)或列號(hào)字母前使用$。但在R1C1樣式中只需省略方括號(hào)就行了，是不是很簡(jiǎn)單！!！如下所示代碼:Subhuizong()DimFinalrowAsIntegerFinalrow=Cells,2).End(xlUp).RowCells(Finalrow+1,1).Value="匯總"Cells(Finalrow+2,1).Resize(1,3).FormulaR1C1="=

6、SUM(R2C:R-2C)"EndSub引用R2C:R-2C表示將當(dāng)前列第2行到上2行中，同列數(shù)據(jù)的和，通過使用R1C1混合引用，可以使用公式求行數(shù)不確定的數(shù)據(jù)，引用整行和整列有時(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è)演示R1C1公

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

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

9、沒有出現(xiàn)這種錯(cuò)誤。運(yùn)行宏時(shí)，單元格A1中的公式指向=XF1048572(Excel2003指向=IR65532),這意味著R1C1公式從表的左側(cè)繞回到右側(cè)。這是一個(gè)很有趣的行為，但是可能宏將提供一個(gè)與用戶期望不同的結(jié)果！乘法表實(shí)例：六、條件格式中的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

10、對(duì)象用于設(shè)置條件格式。每個(gè)單元格可以有3個(gè)Formatconditions,下面的代碼首先遍歷所有工作表，刪除每個(gè)工作表中的條件格式，然后遍歷每個(gè)工作表中所有的非空單元格，并應(yīng)用兩種條件格式。在第一種條件格式中，類型為xlExpression，這意味著使用的是公式”語(yǔ)法。首先Foumula1指定的公式采用的是R1C1表示法。第二個(gè)條件格式使用xlCellValue類型，這需要指定一個(gè)運(yùn)算符和一個(gè)值。在添加條件后，為條件1和條件2設(shè)置字體的ColorIndexSubApplySpecialFormattingALL()ForEachwsIn?ForEachcellIn?IfNotIsEmpty(

11、cell)Then'單元格值是任意錯(cuò)誤值時(shí)，'把字體顏色設(shè)置為與單元格底色相同的顏色(即看不出錯(cuò)誤值)Type:=xlExpression,Formula1:="=or(ISERR(RC),isna(RC)"(1) .='單元格值小于0的，全部用紅色字體標(biāo)出Type:=xlCellValue,Operator:=xlLess,Formula1:="0"(2) .=3EndIfNextcellNextwsEndSub一個(gè)演示條件格式的經(jīng)典實(shí)例，顯示包含最小值和最大值的行。代碼如下:SubFindMinMax()Finalrow=Ce

12、lls(,1).End(xlUp).RowWithRange("a2:c"&Finalrow).Type:=xlExpression,Formula1:="=rc3=max(c3)"'用綠色底紋標(biāo)出.FormatConditions(1).=4.Type:=xlExpression,Formula1:="=rc3=min(c3).FormatConditions(2).=6'用黃色底紋標(biāo)出EndWithEndSub如果設(shè)置一個(gè)指向單元格C3的條件格式，這種格式將失敗，因?yàn)镋xcel將C3解釋為第3列。七、VBA中的R1C

13、1樣式數(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表示法：SubEnterArrayFormulas()Finalrow=Cells,1).End(xlUp).RowCells(Finalrow+2,2).Value="乘積和"Cells(Finalrow+2,3).FormulaArray="=sum(R2C-2:R-2C-2*R2C-1

14、:R-2C-1)"EndSub?上述代碼中的：Cells(Finalrow+2,3).FormulaArray="=sum(R2C-2:R-2C-2*R2C-1:R-2C-1)"可以簡(jiǎn)化為：Cells(Finalrow+2,3).FormulaArray="=sum(R2:R-2C-2*R2:R-2C-1)"后面有介紹，但要注意：紅色和藍(lán)色代碼之間有一個(gè)空格?！拘〖记伞靠梢圆捎孟旅娴姆椒焖佥斎隦1C1公式：在任何單元格中輸入常規(guī)的A1公式或數(shù)組公式，選擇該單元格并切換到VBA編輯器。然后按Ctrl+G鍵打開立即窗口，再輸入：或者Print?并

15、按回車鍵，Excel將把公式欄中的公式轉(zhuǎn)換為R1C1樣式。以下內(nèi)容來自第二個(gè)帖子，。單元格引用有A1和R1C1兩種形式，兩種形式中乂包含了絕對(duì)引用和相對(duì)引用兩種不同的變化。單個(gè)區(qū)域的引用:先來講一下R1C1方式的引用對(duì)于G2:J2這樣一個(gè)單元格區(qū)域，使用R1C1來弓I用，可以寫作：R2C7:R2C10,也可以寫作：R2C10:R2C7。但R必須在C前面，“C10R2:C7R：&樣的寫法是無效的。對(duì)于這樣單行的區(qū)域引用，可以使用簡(jiǎn)寫的引用方式，即將R2C7:R2C10中的紅色部分省去，簡(jiǎn)寫為R2C7C10。如果對(duì)于單列的區(qū)域引用，例如G2:G10,則可以將R2C7:R10C7中的紅色部分

16、省去，簡(jiǎn)寫為R2:R10C7。為什么可以這樣簡(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ū)域;還有一種是空格，稱為交義運(yùn)算符，表示引用空格兩邊的兩個(gè)區(qū)域的交集，如=SUM(A1:B5A4:D9)即等價(jià)于=SUM(A4:B5)。

17、這個(gè)交義運(yùn)算符也是我這里要重點(diǎn)引入的一個(gè)使用技巧。對(duì)于G2:J2這個(gè)單元格區(qū)域，如果換一個(gè)角度來看，可以看作是G:J這4列與第2行所構(gòu)成的交義區(qū)域。因此，如果用交義運(yùn)算符和R1C1樣式來引用的話，可以寫作：R2C7:C10,即表示R2與C7:C10這兩個(gè)區(qū)域的交義區(qū)域。因此R2C7:R2C10等價(jià)于(R2C7:C10),而這個(gè)形式與上面的簡(jiǎn)寫形式R2C7:C10十分相似；同理，G2:G10可以表示為R2C7:R10C7,等價(jià)于(R2:R10C7),這個(gè)形式于其簡(jiǎn)寫形式R2:R10C7也十分相似。以上就是對(duì)于同行或同列的R1C1引用簡(jiǎn)寫方式的一些理解，但這個(gè)題目中真正起到大作用的并非那個(gè)簡(jiǎn)寫方式

18、(雖然也可以縮短不少字符)，而是引入交義運(yùn)算符以后的引用方式。請(qǐng)繼續(xù)往下看。交義運(yùn)算符同樣也可以應(yīng)用在A1引用方式中：例如(R2C7:C10)如果使用A1引用方式，則可以寫作：(2:2G:J)(R2:R10C7)如果使用A1引用方式，則可以寫作：(G:G2:10)從現(xiàn)在的樣子看上去，好像A1引用方式更簡(jiǎn)短，但聯(lián)系題目的實(shí)際情況，經(jīng)過代入后的比較最終可以發(fā)現(xiàn)還是R1C1引用方式更合適，關(guān)于這一點(diǎn)可以留到后面再看。多個(gè)區(qū)域的引用：題目最終結(jié)果要求同時(shí)超級(jí)鏈接到3行最多喜字的區(qū)域，這就意味著需要同時(shí)對(duì)多個(gè)區(qū)域進(jìn)行引用，以同時(shí)引用G13:J13G15:J15G17:J17這三行區(qū)域?yàn)槔?，使用R1C1的通常寫法是：R13C7:R13C10,R15C7:R15C10,R17C7:R17C10其中的逗號(hào)表示聯(lián)合運(yùn)算可以引入交義運(yùn)算符，簡(jiǎn)化為：R13C7:C10,R15C7:C10,R17C7:C10此時(shí)，來做一個(gè)類似于合并同類項(xiàng)的變形，可以簡(jiǎn)化為：(R13,R15,R17)C7:C10將幾個(gè)行號(hào)用逗號(hào)相連接表示聯(lián)合運(yùn)算，同時(shí)外面用括號(hào)包圍以后，再與C7:C10區(qū)域使用空格進(jìn)行連接，形成交義運(yùn)算關(guān)系，得到了我們所需要的三個(gè)