:Find and reference functions How to use the OFFSET function-Table skills免费ppt模版下载-道格办公

Picture and text skills
Case

Using the specified reference as the frame of reference, obtain a new reference through the given offset. The reference returned can be a cell or a range of cells. and can be specified

The number of rows or columns to return.

Syntax
OFFSET(reference,rows,cols,height,width)
￭ Reference as the reference area of the offset reference system. Reference must be a reference to a cell or a connected range of cells;

Otherwise, function OFFSET returns the #VALUE! error value.

￭ Rows The number of rows offset above (bottom) relative to the upper-left corner cell of the offset reference system. If you use 5 as parameter ROWS, then the target index

UseareaThe upper-left cell of the field is 5 rows below reference.

The number of lines can be positive (below the starting reference) or negative (above the starting reference).

￭ Cols The number of columns offset to the left (right) relative to the upper-left cell of the offset reference system. If 5 is used as parameter Cols, it indicates the target reference area

FieldThe cell in the upper left corner is 5 columns to the right of reference.

The number of columns can be positive (representing to the right of the starting reference) or negative (representing to the left of the starting reference).

￭ Height Height, that is, the number of rows in the reference area to be returned. Height must be positive.

￭ Width width, that is, the number of columns in the reference area to be returned. Width must be positive.

Description

￭ If the row and column offset exceeds the edge of the worksheet, the function OFFSET returns the error value #REF!.

￭ If height or width is omitted, it is assumed that its height or width is the same as reference.

￭ The OFFSET function does not actually move any cells or change the selection, it just returns a reference. Function OFFSET can be used for any need

Function that takes a reference as a parameter. For example, the formula SUM(OFFSET(C2,1,2,3,1)) will calculate 3 rows 1 row below and 2 columns to the right of cell C2

The total value of the range in 1 column.

The OFFSET function returns a reference to a cell or range of cells with a specified number of rows and columns.

The syntax of this function is OFFSET (reference area, number of rows, number of columns, height, width).

分界线.png

▪Taking this table as an example, if we want to return the values in the 2 rows below and 3 columns to the left of cell D4.

Click the [Formula] tab-[Insert Function] button, and in the pop-up [Insert Function] dialog box, insert the OFFSET function.

Enter D4 in the [Reference Area], that is, use cell D4 as the starting point.

Input the number of rows to be offset according to the [Reference Area] in [Number of Rows], enter a positive number to offset downwards, and enter a negative number to offset upwards, because we You want to return 2 rows below cell D4, so enter 2 in [Number of Rows].

Input the number of columns to be offset according to the [Reference Area] in [Number of Columns], enter a positive number to offset to the right, enter a negative number to offset to the left, because We want to return the 3 columns to the left of cell D4, so enter -3 in [Number of Columns].

Click OK to return the values in the 2 rows below and 3 columns to the left of cell D4.

▪How to use the OFFSET function to return a reference to a specified area and combine it with other functions for calculation?

We know that the value of cell D4 moved 2 rows down and 3 columns left is A6. If you want to calculate with A6 as the base point, reference the data area with 3 rows and 3 columns. sum.

Click [Edit Bar] to supplement the formula, enter the number of newly referenced rows in [Height], enter 3, enter the number of newly referenced columns in [Width], enter 3 .

Finally, enter the SUM function before the OFFSET function to calculate the sum of the values referenced by the area returned by the OFFSET function.

▪To put it simply, the formula OFFSET(D4,2,-3) is the calculation result based on D4 as the base point, shifting the value 2 rows down and 3 columns left for A6.

演示文稿1_02.jpg

▪The formula OFFSET(D4,2,-3,3,3) is based on moving D4 to A6, then using A6 as the base point, and newly citing 3 lines 3 The data area of the column is A6:C8.

演示文稿1_06.jpg

▪Then use the SUM function to calculate the sum of the A6:C8 area obtained by the OFFSET function.

演示文稿1_07.jpg

Have you learned it?

Articles are uploaded by users and are for non-commercial browsing only. Posted by: Lomu, please indicate the source: https://www.daogebangong.com/en/articles/detail/cha-zhao-he-yin-yong-han-shu-ru-he-shi-yong-OFFSET-han-shu.html