References to cells and scopes in Excel (that is, the way access is represented)

Computer, is nothing more than data and data processing these two things. Excel's worksheets can store large amounts of data, and in addition to these raw data, we also use functions to process the data, such as summation, averaging, sorting, and so on, and the processing results in cells. In Excel, we don't usually give this data a special name, such as Studentname1,studentname2,score1,score2 (I'll say in another article how to name the data in a cell or range), Excel itself implies the way these variables are referred to, and does not make them "lost storage space." In general, when we are programming, we pass our Programmer's variable name to the function, but in Excel, in the face of such a large amount of data, dense cells, we may not have thought about and do not need to give each cell a variable name. People who have used Excel will naturally use A5,B1:C3 as a means of accessing a cell or range. This method of referring to data in a table is a reference. In short, in Excel, the way in which a cell or range of data is represented is a reference.

For the same cell or range, Excel provides several different reference modes.

A1 reference mode (reference style)

Excel uses the A1 reference by default. This method uses letters to denote columns (A to IV, altogether 256 columns) and numbers to represent rows (1 to 65536). These letters and numbers are also known as column headers and costumes. When referring to a cell, write the letter of the column, followed by the number of the row. For example, B2 is the cell at the intersection of the B column and the second row. For a few examples:

The cell and range to represent	expression
cells in column A and line tenth	a10
	a10:a20
15 Row from column B to column e a series of cells	b15:e15
All cells in row 5th	5:5
5? to 10 rows of all cells	5:10
h bar	h:h
h" bar to all cells in column J	h:j
Column A to E column and 10 to 20 rows in all cells	A10:e20

point to another sheet??? In the following example, the average function calculates the average of the b1:b10 of the "Marketing" form in the same workbook.

Note the name of the form and the exclamation point before the range reference (!).?

How to quote

If you want to analyze data for cells or ranges that are in the same location on different forms in the same workbook, you can use the-I reference method. The-i-quote method first writes the name of a series of forms, followed by a reference to a cell or range. Excel uses any form that exists between the start and end names. For example, =sum (sheet2:sheet13! B5) adds the data for B5 cells on all forms, including Form 2 and form 13.

• You can use-references to refer to cells in other sheets, to define names, and to create formulas by using the FOLLOWI Ng Functions:sum, AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV, STDEVA, STDEVP, STDEVPA, VAR, V ARA, VARP, and VARPA.
• The reference method cannot be used in an array formula.
• References cannot is used with the intersection operator? (a single space) or in formulas this use implicit intersection.

How do I change when you move, copy, insert, or delete a form

The following example explains how things change when you move, copy, insert, or delete a form that is contained in a-m reference. The example uses the formula =sum (sheet2:sheet6! A2:A5), A2 all cells to A5 between the second and sixth forms.

insert or copy ??? If you copy or insert a form between Form 2 and form 6 (two endpoints in this case), Microsoft Excel includes data from A2 to A5 cells on the new form when it is calculated.

Delete ??? If you delete the form between Form 2 and form 6, Excel deletes their data from the calculation.

Mobile ??? If you move a form from between Form 2 and form 6 to this range, Excel removes its data from the calculation.

Move Endpoint ??? If you move form 2 or form 6 to another location in the same workbook, Excel adjusts the calculation range to accommodate a new range between them.

Delete Endpoint ??? If you delete form 2 or form 6,excel adjust the calculation range to accommodate a new range between them.

R1C1 Citation method

We can also use a reference method where the rows and columns on the form are numbered numerically. The R1C1 reference method is useful for calculating the position of rows and columns in a macro. When using R1C1 mode, Excel uses "R" to follow the number of a row, and "C" follows the column number to represent a cell.

expression	meaning
r[-2]c	this is a relative reference? A cell that has two rows in the same column
r[2]c[2"	relative reference, two lines down, Cells to the right two columns
r2c2	absolute reference? Cell at the intersection of the second row and the second column
r[-1]	relative reference, The entire row above the current cell
R	Absolute reference, referring to the forward

When you record a macro, Excel uses the R1C1 reference to record some commands. For example, when you record an order, click? AutoSum? button to insert a formula, add a total series of cells, Excel records the formula with R1C1 instead of A1 reference.

Enable or disable? R1C1 Citation method

1. Click the Toolsmenu? options, then select? General label.
2. In? Settings, select or clear the R1C1 reference style? check box.

References to cells and scopes in Excel (that is, the way access is represented)