Excel中R1C1樣式引用詳解

1、Excel中 R 1 C 1樣式引用詳解這篇文章根據(jù)excelhome論壇中的兩個(gè)帖子進(jìn)行整理，稍作了修改以下內(nèi)容來(lái)自第一個(gè)帖子,、引用單元格：R1C1樣式與A1樣式比較DanBricklin和BobFrankston使用A1表示電子表格左上角的單元格，Mitch Kapor 在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ī)，最終讓excel接受了

2、A1樣式，并同時(shí)支持R1C1樣式編址方案。當(dāng)前，？excel默認(rèn)使用A1樣式。為什么學(xué)習(xí)R1C1樣式答案是：1, excel的宏錄制器采用的是R1C1樣式錄制公式。2，R1C1樣式比之A1樣式有更高的效率，尤其是對(duì)于公式，編寫的代碼效率會(huì)更咼3，在 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 能智能的填充公式,并向

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

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

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

6、2C:R-2C 表示將當(dāng)前列第2行到上2行中，同列數(shù)據(jù)的和，通過(guò)使用 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 公式的經(jīng)典實(shí)例是創(chuàng)建乘法表

7、。在 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).PasteSpecial Transpose:=TrueRange(b2:m13).

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

9、2003指向=IR65532),這意味著R1C1公式從表的左側(cè)繞回 到右側(cè)。這是一個(gè)很有趣的行為，但是可能宏將提供一個(gè)與用戶期望不同的結(jié) 果！乘法表實(shí)例：六、條件格式中的 R1C1 樣式應(yīng)用 設(shè)置條件格式時(shí)，必須使用 R1C1 公式，這很重要。文檔沒有明確之處這一點(diǎn)， 但如果不用R1C1公式，有時(shí)可能出問(wèn)題，有研究發(fā)現(xiàn)，如果用A1公式，每對(duì)50 個(gè)單元格設(shè)置條件格式，將有一個(gè)單元格出現(xiàn)奇怪的行為。因?yàn)閷?A1 引用轉(zhuǎn)換 為R1C1引用有時(shí)存在二義性，例如，R2表示一個(gè)單元格，但可能被錯(cuò)誤理解 為整個(gè)第二行。FormatC on ditio ns對(duì)象用于設(shè)置條件格式。每個(gè)單元格可以有3個(gè)Form

10、atC on ditio ns，下面的代碼首先遍歷所有工作表，刪除每個(gè)工作表中的條件 格式，然后遍歷每個(gè)工作表中所有的非空單元格，并應(yīng)用兩種條件格式。在第一種條件格式中，類型為xIExpressio n，這意味著使用的是 公式”語(yǔ)法。首 先 Foumula1 指定的公式采用的是 R1C1 表示法。第二個(gè)條件格式使用 xICeIIVaIue 類型，這需要指定一個(gè)運(yùn)算符和一個(gè)值。在添 加條件后，為條件 1 和條件 2 設(shè)置字體的 CoIorIndexSubAppIySpeciaIFormattingALL()For EachwsIn？For EachceII In？IfNotIsEmpty(ceI

11、I) 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 = Cells(, 1).End(xlUp).RowWith Range(

12、a2:c & Finalrow)用綠色底紋標(biāo)出. Type:=xlExpression, Formula1:=rc3=max(c3).FormatConditions(1). = 4. Type:=xlExpression, Formula1:=rc3=min(c3).FormatConditions(2). = 6用黃色底紋標(biāo)出EndWithEndSub如果設(shè)置一個(gè)指向單元格 C3的條件格式，這種格式將失敗，因?yàn)?Excel將C3解釋為第 3 列。七、VBA中的R1C1樣式數(shù)組公式數(shù)組公式必須是 R1C1 公式。數(shù)組公式是功能強(qiáng)大的 “超級(jí)公式 ”，被稱為 CSE 公式，因?yàn)橛脩舯仨毎碈tr

13、l+Shift+Enter 鍵來(lái)輸入它們，如：=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:R-2C-1)EndSub？上述代碼中的：Cells(Finalrow + 2, 3).FormulaArray

14、 = =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 鍵打開立即窗口，再輸入：或者Print ？并按回車鍵， Excel 將把公式欄中的公式轉(zhuǎn)換為 R1C1 樣式。以下內(nèi)容來(lái)自第二個(gè)帖子，。單元格引用有 A1 和 R1C1 兩種

15、形式，兩種形式中又包含了絕對(duì)引用和相對(duì)引用 兩種不同的變化。單個(gè)區(qū)域的引用：先來(lái)講一下 R1C1 方式的引用對(duì)于G2:J2這樣一個(gè)單元格區(qū)域，使用R1C1來(lái)引用，可以寫作：R2C7:R2C10, 也可以寫作：R2C10:R2C7。但R必須在C前面，“ C10R2:C7R這樣的寫法是 無(wú)效的。對(duì)于這樣單行的區(qū)域引用，可以使用簡(jiǎn)寫的引用方式，即將R2C7:R2C10中的紅色部分省去，簡(jiǎn)寫為 R2C7:C10。如果對(duì)于單列的區(qū)域引用，例如G2:G10,則可以將R2C7:R10C7中的紅色部分 省去，簡(jiǎn)寫為 R2:R10C7。為什么可以這樣簡(jiǎn)寫，這樣的簡(jiǎn)寫有何規(guī)律在沒有官方的解釋之前，為了便于 大家的

16、理解和記憶，我給出一個(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:B5 A4:D9) 即等價(jià)于 =SUM(A4:B5) 。這個(gè)交叉運(yùn)算符也是我這里要重點(diǎn) 引入的一個(gè)使用技巧。對(duì)于G2:J2這個(gè)單元格區(qū)域

17、，如果換一個(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,等價(jià)于(R2:R10 C7)，這個(gè)形式于其簡(jiǎn) 寫形式R2:R10C7也十分相似。以上就是對(duì)于同行或同列的 R1C1 引用簡(jiǎn)寫方式的一些理解，但這個(gè)題目中真 正起到大作用的并非那個(gè)簡(jiǎn)寫方式 (雖然也可以縮短不少字符) ，而是引入交叉 運(yùn)算符

18、以后的引用方式。請(qǐng)繼續(xù)往下看。交叉運(yùn)算符同樣也可以應(yīng)用在 A1 引用方式中：例如(R2 C7:C10)如果使用A1引用方式，則可以寫作：(2:2 G:J)(R2:R10 C7)如果使用A1引用方式，則可以寫作：(G:G 2:10)從現(xiàn)在的樣子看上去，好像 A1 引用方式更簡(jiǎn)短，但聯(lián)系題目的實(shí)際情況，經(jīng) 過(guò)代入后的比較最終可以發(fā)現(xiàn)還是 R1C1 引用方式更合適， 關(guān)于這一點(diǎn)可以留到 后面再看。多個(gè)區(qū)域的引用：題目最終結(jié)果要求同時(shí)超級(jí)鏈接到 3 行最多喜字的區(qū)域，這就意味著需要同時(shí) 對(duì)多個(gè)區(qū)域進(jìn)行引用，以同時(shí)引用G13:J13 G15:J15 G17:J17這三行區(qū)域?yàn)槔?，使?R1C1 的通常寫法是：R13C7:R13C10,R15C7:R15C10,R17C7:R17C10 其中的逗號(hào)表示聯(lián)合運(yùn)算可以引入交叉運(yùn)算符，簡(jiǎn)化為：R13 C7:C10,R15 C7:C10,R17 C7:C10此時(shí)，來(lái)做一個(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è)行區(qū)域的同時(shí)引用這個(gè)引用方式就是我