Formula explanation: The function of the OFFSET function is to use the specified reference as a reference and obtain a new reference through the given offset. Here, it takes the value of cell C3 as a reference, an offset of 0 rows and columns, a height of the value of E2, and a width of 1. For example, the number in cell E2 is 3, then the new reference area address generated by OFFSET is C3:C5, and then the SUM function is used to sum and calculate its percentage to the target gross production value. It must be noted here that absolute references need to be used in C3, E1, and B9.
1: Use controls
1. Right-click anywhere on the menu bar or toolbar to bring up the "Control Toolbox" dialog box (you must have a VBA environment to use the "Control Toolbox").
2. Select "Numerical Adjustment Button" (icon: ) in the "Control Toolbox" and drag to draw an adjustment button in the blank area of the WPS table.
3. Right-click the "Adjustment Button", select "Properties" from the menu, and set the Linkedcell attribute to "E1", as shown in the figure. It is designed to associate cells and controls. When the user clicks the "Adjustment Button", the value of the "E1" cell will change accordingly.
4. Close the "Properties" dialog box and click the "Exit Design Mode" button (icon: ) in the "Control Toolbox".
2: Define the name
In order to obtain the compliance rate of the actual production progress, it is now necessary to define a name.
1. Select Insert Name Definition in the menu, enter the Define Name dialog box, and enter in the "Reference Position":
=SUM(OFFSET($C$3,0,0,$E$2,1))/$B$9
2. Enter "Compliance Rate" in the name box above, and click the "Add" button on the right toolbar to complete the name definition. As shown in Figure 3.
