Snasui Excel Forum

Excel can also use the R1C1 reference style, in which both the rows and the columns on the worksheet are numbered. The R1C1 reference style is useful if you want to compute row and column positions in macros. In the R1C1 style, Excel indicates the location of a cell with an "R" followed by a row number and a "C" followed by a column number.

It is often advantagous to use the R1C1 refence method, because it allows more flexibility. MS Excel uses R1C1 reference style internally.

NOTE: F4 key changes (cycles thru) the absolute/relative/mixed reference type in a formula. To switch to R1C1 reference style (or back to A1 style), click Tools menu, Options, General tab. Under Settings, select or clear the R1C1 reference style check box.

Examples:

R[-2]C A row-column relative reference (relative reference: In a formula, the address of a cell based on the relative position of the cell that contains the formula and the cell referred to. If you copy the formula, the reference automatically adjusts. A relative reference takes the form A1.) to the cell two rows up and in the same column

R[2]C[2] A relative reference to the cell two rows down and two columns to the right

R2C2 An absolute reference (absolute cell reference: In a formula, the exact address of a cell, regardless of the position of the cell that contains the formula. An absolute cell reference takes the form $A$1.) to the cell in the second row and in the second column

R[-1] A relative reference to the entire row above the active cell

R An absolute reference to the current row

A1 Single cell, Relative. A is column identifier, 1 is row identifier

A1:B3 Range, Relative. This references 6 cells

5:05 Range, Relative. All cells in row 5

B:B Range, Relative. All cells in column B

5:07 Range, Relative. All cells in rows from 5 to 7

B:E Range, Relative. All cells in columns from B to E

A$1$ Cell, Absolute. The cell A1. Cut-pasting formula to another location will not change the reference.

A$1 Cell, Mixed. The $ makes the column reference absolute, leaving the row reference relative

R1C1 Cell, Absolute. R1 is row 1, C1 is column 1

R1C1:R3C2 Range, Absolute. Same as A1:B3 as shown above

R Range, Absolute Reference to current row

C Range, Absolute Reference to current column

R[-2]C Cell, Relative. Cell two rows up and in the same column

RC[-1] Reference to a cell in the same row, but one column to the left.

RC Reference to a cell in the same row and column, which is the current cell itself. You would generally never do this because it would create a circular reference.

R[-2]C1 Cell, Mixed. Row reference is relative, column reference is absolute

R[2]C[3] Cell, Relative. Cell two rows down of current row and three rows to right of current column

R[-1] Range, Relative. Cells in the row above the current row

defined_name Range or Cell, Absolute. Named cells are always absolute

Sheet1!reference Either Range or Cell, Relative or Absolute. Prefixing the sheet number and exclamation mark before any of the above references indicates the source worksheet.

='C:\[sample.xls]Sheet1'!$A$1 Cell, Absolute. Refers to an external file - a worksheet in the file and an absolute position within that worksheet. Note the usage of various delimiters.

='C:\[sample.xls]Sheet1'!defined_name Cell, Absolute. Same as above. Refers to a defined name "defined_name"

Sheet1:Sheet3!A1:B3 Cell, Relative. Refers to all cells from A1 to B3 in all worksheets from Sheet 1 to Sheet 3. "Sheet1" and "Sheet3" are actual worksheet names. This type of reference is called 3D-reference, because it cuts across worksheets.

itsadaw. wrote:อ๋ออ. ความหมายของผมขอแค่ เข้าตามวิธีข้างล่างน่ะครับ

File> Excel Option > Formulas > Uncheck R1C1 Reference Style > OK

ผมเข้าไปถึงตรง File แล้ว แต่ติดตรง Excel option ที่ยัง หาไม่เจออยู่ครับ

ของผมใช้ Version 2003 ครับ

รบกวนอีกครั้งครับ

ขอบคุณครับ.