excel表格不一樣的R1C1引用樣式

excel表格不一樣的R1C1引用樣式

A1樣式和R1C1樣式的歷史

最初發明第一款電子表格軟件VisiCalc的Dan Bricklin和Bob Frankston對單元格使用A1樣式，而Microsoft早期的電子表格MultiPlan使用的是R1C1樣式。上世紀80年代，Lotus是電子表格領域的翹楚，牢牢占據著電子表格市場，而它采用的正是VisiCalc用戶熟悉的A1樣式。為了在電子表格市場搶戰更多的份額，Microsoft讓Excel既可以使用A1樣式又可以使用R1C1樣式。

通常，我們都是使用A1樣式，這也是Excel的默認樣式。然而，R1C1樣式有很多獨到之處。

什么是R1C1樣式

在“Excel選項”的“公式”下，選取“R1C1引用樣式”，即可在公式中使用R1C1樣式。Excel提示也給出了R1C1引用樣式的說明，見下圖所示。此時，工作表中頂部的列標由字母變成了數字。

讓我們通過與A1樣式對比來認識R1C1樣式。如下圖所示的工作表，列D中通過公式來計算銷售額和銷售合計，列F是使用A1樣式的公式，列G是使用R1C1樣式的公式。

可以看出：

對于R1C1樣式表示的相對引用，在方括號中放置行數或列數，表示相對于當前單元格移動指定行數或列數的單元格，負數表示向左或向上移動，正數表示向右或向下移動。例如RC[-2]表示相對于當前單元格在當前行左移2列的單元格，R[-4]C表示相對于當前單元格在當前列向上移8行的單元格。

再看看SUM函數中使用的單元格引用。對比A1樣式，R1C1樣式的絕對引用為表示行列的字母后直接加上行號和列號。如示例中的R2C4表示單元格D2，R5C4表示單元格D5。那么，RC表示當前單元格，R3C表示當前列中第3行的單元格，RC3表示當前行中第3列的單元格。使用A1樣式，如果當前單元格為$D$6，那么R3C等價于D$3，RC3等價于$C6。

為什么要使用R1C1樣式

仍以上圖所示的工作表為例。我們對比A1樣式和R1C1樣式求銷售額的公式，可以看到使用R1C1樣式的公式相同。也就是說，在多個單元格中，只需一個公式就可得出結果。并且，公式引用的單元格的變化并不會影響公式。我想，這或許就是使用R1C1樣式的主要優勢。

在VBA中，也是如此。我們使用代碼來求上圖所示工作表中的銷售額。

使用A1樣式的代碼：

Range(“D2”).Formula= “=B2*C2”

Range(“D2”).CopyDestination:=Range(“D3:D5”)

使用R1C1樣式的代碼：

Range(“D2:D5”).FormulaR1C1= “=RC[-2]*RC[-1]”

使用R1C1樣式的代碼更簡潔，更有效率。

此外，我們在錄制宏代碼時，宏錄制器在錄制公式時就是使用R1C1樣式。

更進一步地說，Excel是以R1C1樣式來完成引用和計算，而是以A1樣式來顯示地址和公式，因此R1C1樣式的公式會更加有效。

這就是R1C1樣式的秘密！

還有一點值得一提，在A1樣式中，同樣的公式，但輸入的單元格不同，含義不一樣，例如：

在單元格D8中輸入公式：$A5+C3

在單元格K10中輸入公式：$A5+C3

但是，R1C1樣式的公式不依賴公式所在的單元格。

這也是R1C1樣式的一個優點。

技巧1：Excel默認為A1樣式，因此工作表上方顯示的是字母表示的列號。如果想要快速得到某列的列號，可以先選取單元格A1，然后向右拖動至目標列，在名稱框可以看到列號數值，如下圖所示。

技巧2：可以利用R1C1樣式公式的一致性來找出存在錯誤的公式。在復制或輸入完公式后，切換到R1C1引用樣式來檢查公式，如果某個公式與其它公式不同，那么這個公式可能存在錯誤。

示例1：創建乘法表

如下圖所示，在第2行第2列的單元格中輸入公式：

=R1C*RC1

然后向左拖至第10列，向下拖至第10行，即可創建一個九九乘法表。

公式表示當前單元格所在行的第1列與當前單元格所在列的第1行的值相乘。

生成上述乘法表的VBA代碼如下：

Sub MultiplySheet()

Range(“B2:J10″).FormulaR1C1 =”=R1C*RC1”

End Sub

示例2：在條件格式中使用R1C1樣式

如下圖所示的工作表，要求對列A中最大值的單元格所在的行添加紅色背景色，對列A中最小值的單元格所在的行添加綠色背景色。

如果使用Excel條件格式對話框，則設置公式：

=$A1=MAX($A:$A)

=$A1=MIN($A:$A)

并設置滿足條件時應用的填充色，如下圖所示。

使用VBA代碼也能達到同樣的效果。代碼如下：

Sub ToMaxMinApplyFormat()

Application.ReferenceStyle = xlR1C1

With Range(“1:6″)

.FormatConditions.Delete

.FormatConditions.AddType:=xlExpression, Formula1:=”=RC1=MAX(C1)”

.FormatConditions(1).Interior.Color =vbRed

.FormatConditions.AddType:=xlExpression, Formula1:=”=RC1=MIN(C1)”

.FormatConditions(2).Interior.Color =vbGreen

End With

Application.ReferenceStyle = xlA1

End Sub

說明：

代碼先刪除工作表區域中存在的條件格式。

指定添加條件格式的參數Type為xlExpression，表明要設置公式。

代碼使用了R1C1樣式的公式，因此，在應用條件格式前，先要將工作簿引用樣式改為R1C1樣式。

從代碼中可以看出，Excel并不認為公式中的C1是指單元格C1，而是指第1列。

示例3：在數組公式中使用R1C1樣式

如下圖所示的工作表，我們使用數組公式：

=SUM(B2:B5*C2:C5)

一次求出表中4種水果的合計銷售金額。

下面的代碼使用數組公式計算銷售合計金額：

Sub ArrayFormulasGetValue()

Range(“C6”).FormulaArray = _

“=SUM(R[-4]C[-1]:R[-1]C[-1]*R[-4]C:R[-1]C)”

End Sub

VBA幫助文檔指出，使用VBA代碼輸入數組公式時，要使用R1C1樣式。然而，我使用下面的代碼：

Sub ArrayFormulasGetValue()

Range(“C6”).FormulaArray = _

“=SUM(B2:B5*C2:C5)”

End Sub

仍然能夠達到上述效果。

使用下面的代碼：

Range(“D2:D5”).FormulaArray= “=B2:B5*C2:C5”

在單元格區域D2:D5中自動輸入每種水果銷售額，即單價與銷售量乘積。

是否是幫助文檔有誤？還是我理解有誤？

技巧3：快速找到數組公式的R1C1樣式。選中數組公式單元格，在VBE編輯器的立即窗口中，輸入下列語句：

PrintActiveCell.FormulaR1C1

即可得到數組公式的R1C1樣式，如下圖所示。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。