Office hacks: interaction in the chart data drill-down can also be used so

Simple data can be expressed in a single chart, but more complex data can be expressed in a single chart, which is overwhelming. The problem can be solved if the chart is made into a dynamic drill-down effect, where the total chart changes when you click on the interactive control, and other data related to the series is obtained when you click on the series in the total chart, and then the other data is used to generate the corresponding sub-chart. Such an effect can be achieved by using the control, a small amount of VBA code and some formulas (Figure 1).

1814A-SJZQ-1

1. Set up interactive controls

Enter month, region, and product in F1:F3, region and product in H1:H2, month and product in I1:I2, and month and region in J1:J2 of the worksheet respectively.

①Construct drop-down list 1

Click “Development Tools→Insert→Form Controls→Combo Box”, draw a combo box control at the appropriate location, set the data source area of the combo box as F1:F3, and the cell link as G1.

② Build drop-down list 2

Click “Formula → Name Manager → New”, the name of “z_cd”, the reference location for “=CHOOSE(Sheet1!$G$1,Sheet1!$H$1:$H$2, Sheet1!$I$1:$I$2, Sheet1!$J$1:$J$2)”; then draw a combo box control, the data source area for “z_cd”, cell link for G2.

In cell G4, type “=INDIRECT(CHOOSE($G$1, “H”, “I”, “J”) & $G$2)” (Figure 2).

1814A-SJZQ-2

2. Write data drill-down VBA code

Click “Development Tools→Visual Basic” to enter the VBA editor, click “Insert→Class Module”, select the inserted “Class 1”, select “View → Properties Window”, change the name of the class to “CEventChart”, double-click the class to enter the edit window, enter the code shown in Figure 3 (Figure 3).

1814A-SJZQ-3

Next, click “Insert→Module”, double-click “Module 1”, and enter the code in the code editing window as shown in Figure 4 (Figure 4).

1814A-SJZQ-4

Next, double-click on “ThisWorkBook” and enter the code shown in Figure 5 in the edit window on the right (Figure 5).

1814A-SJZQ-5

This way, when clicking on a series in the chart, the series name will appear in the G3 cell.

Tip.

The above code can also be downloaded from the web site (link: https://pan.baidu.com/s/1vZhfg86pAHg5tc4UB9E4lw password: 2zba), copy and paste directly to use.

3. Building the chart data source

The data source of the chart is obtained from the current index value of the above control.

① Build the main chart data source

Create 3 new names named lb_11, lb_22, lb_33 respectively, and the reference position of lb_11 is “=INDEX(Sheet1!$A$1:$A$41,N(IF({1},SMALL(99*(MATCH(Sheet1!$A$2:$A$41,Sheet1!$A $1:$A$41,)<ROW(Sheet1!$2:$41))+ROW(Sheet1!$2:$41),ROW(INDIRECT(“Sheet1!1:”&SUM(1/COUNTIF(Sheet1!$A$2:$A$41,Sheet1!$A$2:$A$41)))))) ))&”””; lb_22, lb_33 reference position and lb_11 similar, except that the formula needs to be replaced by A in B, C, respectively.

New name “lb_tmp” name, reference to the location of the input “= CHOOSE (Sheet1!$G$1,lb_11,lb_22,lb_33)”.

Create a new name “sum_1” and enter “=SUMIF(Sheet1!$A$2:$D$41,lb_11,Sheet1!$D$2:$D$41)” at the reference position.

Create a new name “sum_2” and enter “=SUMIF(Sheet1!$B$2:$D$41,lb_22,Sheet1!$D$2:$D$41)” at the reference position.

Create a new name “sum_3” and enter “=SUMIF(Sheet1!$C$2:$D$41,lb_33,Sheet1!$D$2:$D$41)” at the reference position.

Create a new name “sum_tmp” and enter “=CHOOSE(Sheet1!$G$1,sum_1,sum_2,sum_3)” at the reference position.

where lb_11, lb_22, lb_22 get the unduplicated values in each column respectively, thus forming an array; lb_tmp determines which array is used as the category legend of the main chart; sum_1, sum_2, sum_3 sum up the quantities corresponding to each category array respectively; sum_tmp determines which summing result is used as the data source of the main chart.

②Construct sub-chart data source

Create a new name “lb_tmp_tmp” with the reference position “=IF(Sheet1!$G$4=”month”,lb_11,IF(Sheet1!$G$4=”region”,lb_22,lb_33)) “.

Create a new name “sum_tmp_tmp” with the reference bit “=SUMIFS(Sheet1!$D$2:$D$41,IF(Sheet1!$G$1=1,Sheet1!$A$2:$A$41,IF( Sheet1!$G$1=2,Sheet1!$B$2:$B$41,Sheet1!$C$2:$C$41)),Sheet1!$G$3,IF(Sheet1!$G$4=”month”,Sheet1!$A$2:$A$41,IF(Sheet1!$G$4=”region”,Sheet1!$ B$2:$B$41,Sheet1!$C$2:$C$41)),lb_tmp_tmp)”.

where lb_tmp_tmp determines which array is used as the category legend for the sub-chart; sum_tmp_tmp sums the quantities based on the selection of the list in the main chart and the list in the sub-chart, and thus serves as the data source for the sub-chart.

4. Insert and set up charts

Insert two pie charts. Right-click the first pie chart, select “Select Data”, click “Edit” in the pop-up window of “Legend Item”, and enter “=” in the series value. Sheet1!sum_tmp”; in the “Horizontal (Category) Axis Label”, click “Edit”, and enter “=Sheet1!lb_tmp” in the pop-up window. tmp”. Right-click the second pie chart, select “Select Data”, click “Edit” at the “Legend Item” pop-up window, and enter “=Sheet1!lb_ tmp” at the series value. Sheet1!sum_tmp_tmp”; at the “Horizontal (Category) Axis Label”, click “Edit”, and enter “=Sheet1! lb_tmp_tmp”.

Finally, place each drop-down list box at the top, drag and drop to the respective position of the chart, select cell G5, enter “=CONCATENATE(G3, “each”, G4, “Sales Statistics”)”, select the chart title, and enter “= Sheet1!$G$5”. This way, the chart title will follow the selection of the control. Not the same.

Leave a Comment