188 address
Mnemonic: "Address" in English.
Categories: Find and Reference
grammar:
address(row_num,column_num,[abs_num],[a1],[sheet_text])
Parameters: 2 to 5 parameters
row_num Required. 1 numeric value that specifies the row number to use in the cell reference.
column_num Required. 1 numeric value that specifies the column number to use in the cell reference.
abs_num Optional. The reference type returned.
1 or omitted, absolute cell reference
2. Absolute row number, relative column label
3, relative row number, absolute column label
4. Relative cell reference
a1 Optional. 1 logical value. Returns an A1 style reference for true or omitted; for false, returns an R1C1 style reference.
sheet_text Optional. 1 text value that specifies the name of the worksheet to use as an external reference.
usage:
Equivalent to an address translator or address generator. The cell address at that location is converted to the specified reference style, given the number of rows and columns.
1) Create a new worksheet, just a formula as shown in the figure can translate the address of the cell, the row and column of the cell are obtained with row and closeumn functions respectively, and the 3rd parameter "4" is added to adopt the style of cell relative reference.
Get the address
2) Now we add the 3rd parameter in column A, take the value 1 to 4 respectively; column B adds the 4th parameter 0 (0 is equivalent to false, 1 or omits to true); column C adds the 5th parameter, as shown in the figure, the 4th parameter is omitted, but the comma should be retained. You can see the address translation effect of the corresponding parameter.
Address style
3) These addresses can be used as parameters to the input function in the previous lecture to display the contents of the corresponding cell. Note, however, that the function only recognizes the style of the absolute reference to the cell of R1C1, and does not recognize it with the brackets.
189 offset
Mnemonic: "offset" in English.
offset(reference,rows,cols,[height],[width])
Parameters: 3 to 5 parameters
Reference required. As a reference to the offset reference system. If it is a range, only the cell in the upper-left corner is useful.
rows required. Relative to the offset reference system, the number of rows offset up and down, the positive number moves down, and the negative number moves up.
Cols required. Relative to the offset reference system, the number of columns offset left and right, positive numbers move to the right, negative numbers move left.
height is optional. Height, which must be positive, that is, the number of rows of the reference area to be returned. If omitted, it is the same as reference.
width is optional. The width, which must be a positive number, is the number of columns in the reference range to be returned. If omitted, it is the same as reference.
The specified reference is used as a reference system to generate a new reference to the range of cells from the given offset, as well as the height and width, which is primarily used as an argument to other functions. There are often some fixed offsets in everyday Excel tables, for example, "Age" in personal information tables is in column 3 to the right of "Name". Or a simple example of using an information query.
Offset query
1) Create a new worksheet and enter the relevant information of each item.
2) Create a query on the right and enter the ordinal number of the query in cell I3 with a yellow background after the ordinal number, such as 2.
3) Offset the frame of reference select cell A1, and the name to be queried relative to A1, the number of rows moving down is exactly equal to the value we entered into cell I3, move 1 column to the right, enter "=offset(A1,I3,1)" in cell I4. Now change the value of I3, and the name queried will change accordingly.
4) Now using the value in the ordinal number as the number of selected people, we can generate references to the cell range of the "age" column based on the offset and value, and can be used as an argument to other functions for simple calculations. In cell I5, enter "=sum(offset(offset(A1,1,4,I3)", showing the sum of ages according to the ordinal number. Relative to A1, down 1 row, 4 columns to the right, will reach the E2 cell, depending on the number of people, the cell range expands from E2 to the specified height.
6) The same calculation of the average age to be based on the sum of the I5 ages and then divided by the number of people, we enter "=I5/I3" in cell I6.
7) Similarly, we can query who is the top 10 in the student's grades, the average of the top 10, etc., and the sequence number of the above table becomes the ranking.
8) Of course, in the above example, the query and the original table are put together for convenience, in fact, the original table is often hidden, and only part of the information can be retrieved in the query table. If the content of the query is not a number, there are many ways to convert it to a position in a sequence using other functions.
(To be continued)