When entering formulas in an Excel table, a reference to a range of cells is essential; directly checking a range of cells can achieve basic operational needs, and if you want to implement dynamic cell range references, you can't do anything about it. This article shares the most powerful cell range reference function for friends→ the basic concepts and application examples of the off set function. The Offset function itself is just a cell range reference function and has no powerful function. But with SUM, MATCH, COUNTIF and other functions can complete a lot of very good operations.
The basic concept of the Offset function:
1.语法：Offset(reference, rows, cols, [height], [width])；
The meaning Chinese each parameter: offset (start area, number of rows offset downwards, number of columns offset to the right, number of rows returned, number of columns returned)
2. Explanation of each parameter:
(1) Reference: A cell reference as a reference (not only a cell, but also an offset starting from a range. ）
(2) Rows: The number of rows offset up or down (positive down, negative up; if this parameter is omitted to mean that it is not offset up and down, that is, it defaults to 0 when omitted)
(3) Cols: the number of columns offset to the left or right (positive to the right and negative to the left; if this parameter is omitted to indicate that it is not shifted to the left and right, that is, it defaults to 0 when omitted)
(4) Height: Height, the row height of the reference that needs to be returned, Height must be a positive number. (If this parameter is omitted, the height of the reference is the same as the height of the first parameter, Reference.) ）
(5) Width: The column width of the reference that needs to be returned, width must be a positive number. (If this argument is omitted, it means that the width of the reference is the same as the width of the first parameter, Reference.) ）
3. Example demonstration:
(1) Operation: Select cell E1:F4 and enter the formula =OFFSET(A1,3,1,4,2) While holding ctrl+Shift+Enter on the keyboard to determine the formula.
(2) Parsing: Taking cell A1 as a reference, moving down three rows and moving one column to the right gets a reference to a 4-row 2-column cell range.
Two. Application examples:
1. Use with SUM and MATCH functions to achieve dynamic summation.
(1) Select E2:F2 cells to make a "Month" drop down list through data validation; select G2 cells to make a "Sales" drop down list through data validation.
(2) Enter the formula in cell H2:
=SUM(OFFSET(A1,MATCH(E2,A2:A10,0),MATCH(G2,B1:C1,0),MATCH(F2,A2:A10,0)-MATCH(E2,A2:A10,0)+1))
(3) Formula analysis:
(1) Cell A1 as the reference cell for the offer function.
(2) MATCH(E2,A2:A10,0) finds the position of the E2 cell content in the A2:A10 cell range; for example, when the E2 content is February, the result returned is 2.
(3) MATCH(G2,B1:C1,0) Finds the location of the G2 cell content in the B1:C1 cell range: For example, when the G2 content is the actual sales volume, the result returned is 2.
(4) MATCH(F2,A2:A10,0)-MATCH(E2,A2:A10,0)+1) IN MATCH(MATCH(E2,A2:A10,0)+1) Finds the position of the F2 cell content in the A2:A10 cell range; for example, when the F2 content is May, the result returned is 5. The result of the calculation 5-2 + 1=4 is the number of rows in the cell range from February to May.
(5) The fifth argument of the offset function is omitted, and the default value is the same as the number of columns in the cell range of the first argument A1, that is, the fifth argument is 1.
(6) Finally, use the SUM function to sum the range of cells returned by the OFF SET function.
(4) Precautions:
Because the OFFSET function returns a range of cells, that is, the return result is an array, be sure to hold down the Ctrl+Shift+Enter keys at the same time to determine the formula.
(5) Dynamic presentation:
2. Use with COUNTIF and MATCH functions to make multi-level linkage drop-down lists.
(1) Make a first-level drop-down menu:
Select the cell range under The Province → switch to the Data tab→ Data Validation → switch to the Setting Options interface→ allow to select "Series" under → the source of the dialog box below the input "Liaoning Province, Jilin Province, Heilongjiang Province" → OK.
(2) Make a two-level linkage drop-down menu:
Select the range of cells under the city→ switch to the Data tab→ Data Validation → switch to the Setting Options interface→ allow to enter the formula below the dialog box below the "Series" → source→ OK.
=OFFSET($A$1,MATCH($D2,$A$2:$A$16,0),1,COUNTIF($A$2:$A$16,$D2))
(1) Cell A1 serves as the reference cell for the offset function, note the absolute references to rows and columns here.
(2) MATCH($D 2,$A$2:$A$16,0) looks up the province of cell D2 where cells A2:A16 first appeared. For example, when the D2 cell content is "Heilongjiang Province", the result returned is 12. Note here that the D2 cell only refers to the column absolutely, not the row.
(3) Because the city is in the last column of the province, it is necessary to offset one column to the right, that is, the third parameter is 1.
(4) COUNTIF ($A$2:$A$16, $D 2) counts the number of times the province of cell D2 appears in the A2:A16 cell range, that is, the number of rows in which each province corresponds to several cities, that is, the fourth parameter of the offer function refers to the number of rows in the cell range.
(1) When making a first-level drop-down menu, the different options should be separated by commas under the English input method.
(2) When making a secondary drop-down menu, pay attention to the way the cells are referenced in the formula, and do not confuse absolute references and relative references.
In summary, through the explanation of the basic concepts of the OFFSET function and the introduction of application examples, I believe that you must have mastered the use of this function. If there is anything you don't understand, please leave a message in the comment area to discuss.