Excel chart learning eclectic OFFSET dynamic table

It’s the beginning of winter and it’s cold! No heating in the house? Just move, stamp your feet, rub your hands, and you will feel warm. It’s the beginning of winter, and another year is coming to an end! No salary increase yet? Let your watch move differently than before, update simultaneously and display dynamically. Not only will it save you trouble, but your boss will also like to pay you...

##First move: Impress, impress the boss with the dynamic charts

The boss is used to seeing the plain tables you have been reporting (below), and now you suddenly show him a chart (below) that can be dynamically queried, you say Can you win over your boss's critical heart? Can you surprise your boss with a reward?

Your report now Your report tomorrow

How to achieve this change? It's very simple, just make dynamic charts.

Dynamic chart means that the boss selects different areas, and the chart displays different data. There are three steps to achieve this: one step is to make a drop-down menu for the boss to choose, one step is to make a data area that changes dynamically according to the selection, and one step is to insert a chart based on the dynamic data area.

Dynamic data areas are commonly implemented with the VLOOKUP function, but today we are not taking the usual route. We use the OFFSET function to complete the dynamic data area.

Step one: Make drop-down selection

1. Select cell J1 and click "Data Verification" under the "Data" tab.

2. In the "Settings" option at the bottom of the "Data Validation" window, select "Series" for "Allow" and select the cells where the five sales areas are located as the source. =$A$2:$A$6", click OK.

Step 2: Create a dynamically updated auxiliary data area

3. The next step is to dynamically update the chart based on the value of cell J1. J1 selects "Beijing Area". Enter "=OFFSET(B1,MATCH($J$1,$A$2:$A$6,0),0)" in cell B7. Then the formula fills to the right to cell G7. In this way, cells B7:G7 return the sales volume in Beijing area from January to June.

Analysis:

Use OFFSET to take "B1" as the reference system, and the number of offset rows is Use the MATCH function to get $J$1 in $A$2 :At the position of $A$6, the offset column number is 0, which means no offset. As shown in Figure J1, the value is "Beijing area", and the position of $A$2:$A$6 is 1. The value returned by OFFSET is a reference offset one row downward with "B1" as the reference system. In this way, as the selected area $J$1 continues to change, cells B7:G7 will obtain the sales data of the corresponding area.

4. Then set the average line data and enter "=AVERAGE($B$7:$G$7)" in cell B8 to get the average value of $B$7:$G$7. Then the formula fills to the right to cell G8. If the selected area $J$1 changes, then $B$7:$G$7 changes, and the average value will also change accordingly.

Step 3: Create the chart

5. We create the chart based on the set auxiliary rows. Select the title B1:G1 and the auxiliary row B7:G8 area and click "Two-dimensional Column Chart" in the "Chart" group under the "Insert" tab.

#6. Enter "=J1&"Sales Data"" in cell K1.

Click the chart title box and enter "=Sheet2!$K$1" in the edit bar, so that the chart title and the data verification area are updated simultaneously.

#7. Click "Change Chart Type" under the "Design" tab under "Chart Tools".

8. In the "Change Chart Type" window, click "Combination" under the "All Charts" option, and change the series where the average value is located to "Line Chart".

#9. Finally, delete the chart legend and change the font of the auxiliary data B7:G8 and K1 cells to white and invisible, and you are done.

Second action: linkage, so that the pivot table and the data source can be updated synchronously

If the pivot table wants to be updated synchronously with the data source, I have introduced to you the super table before accomplish. In addition, we can also use OFFSET. As shown in the figure, the pivot table on the right is inserted based on the data source on the left.

Now we need the pivot table to be updated synchronously after the data source is updated, as follows:

Step 1: Define the name

1. Click "Define Name" in the "Defined Name" option group under the "Formula" tab.

2. In the "New Name" window, enter "Data" in the "Name" column and the following formula in the "Reference Position"

=OFFSET(Sheet1 !$A$1,,,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

##Analysis:

"Sheet1" is the worksheet where the data is located. The function takes "Sheet1!$A$1" as the reference system, does not offset (offset rows and columns are empty), and dynamically returns the entire table data. COUNTA(Sheet1!$A:$A) is used to get the number of rows of table data, and COUNTA(Sheet1!$1:$1) is used to get the number of columns of table data. The results they obtain are dynamic and change as the number of rows and columns in the table increases or decreases.

Step 2: Change the data source

3. Click any cell on the pivot table, and "Pivot Table" will appear. tool". Then click "Change Data Source" in the "Analysis" tab under "PivotTable Tools".

#4. In the "Change PivotTable Data Source" window, change "Table/Range" to the just-defined name "Data" and click OK.

Step 3: Refresh synchronization

5. Next, add data in the last row of data.

#6. Right-click the pivot table and select the "Refresh" command. The pivot table will be updated.

Using the Offset function, we have implemented two "actions". The first action is to use dynamic charts to impress the boss so that the "salary" can be easily opened. The second action is to link the pivot table with the data source. Save yourself more trouble. Ok, hurry up and get your form moving! Winter is coming, although cold hands and feet cannot be avoided, but a salary increase will warm your heart!

Related learning recommendations:

excel tutorial

The above is the detailed content of Excel chart learning eclectic OFFSET dynamic table. For more information, please follow other related articles on the PHP Chinese website!