How to achieve the three level linkage of Excel chart

A student summer vacation to a shopping center headquarters to participate in social practice, shopping mall leaders gave him a seemingly simple task-the way to chart linkage statistics of air conditioning sales. This makes it difficult for students who think they are familiar with Excel. In fact, with Excel and Crystal easy to combine, the problem is much simpler.

Specific tasks: to the various shopping malls of the brand air-conditioning January ~ June Sales Situation chart for display. When you click the line chart corresponding month, the column chart will be monthly statistics of all the brand air-conditioning sales; When you click on the column chart in the corresponding column, the pie chart will be monthly statistics of the corresponding market for each brand air-conditioning sales (Figure 1).

1. Basic Data Preparation

Data should be ready before the chart is used. In the Excel data worksheet, to calculate the sales for all the stores in January ~ June, enter the corresponding fields in the J1:P1, enter the store name and total in the j2:j6, and enter =SUMIF ($A: $A, $J 2,c:c) in the K2 cell. To the lower right, drag to the 5th row of the P column, enter =sum (K2:K5) in the K6 cell, and drag the fill to the P6. Add a worksheet called control to hold the temporary data (Figure 2).

2. Make Basic chart

Once the data is ready, it's time to make the necessary charts. Run SAP BusinessObjects Dashboards (crystal Easy table), create an empty model, and click on the "Data → import" menu (or the "Import Spreadsheet" button on the toolbar) to import the prepared data table. To add a title, click on the "parts" on the left side of the window, and drag the "background" in the "trim and background", "text" label "parts to canvas, adjust their position and size; Select the label part, enter the caption content in the General property on the right, and in the Appearance attribute, label the necessary decorations; add" Selector "part of the 3 combo box parts, adjust their position; Drag the line, column, Pie, and a label part in the statistics section to the canvas to adjust their size and position; Select the label part, enter the content for the description of the action, and decorate the label (Figure 3).

3. Chart interactivity settings

The required parts are basically added to the canvas, and then interactive settings are made for each part. The settings for the part, you need to select them, using the various property panels on the right to operate.

Line chart settings. In the general attribute, enter the title of the chart, and select "by series" in the Data section. Click +, value to select K6:p6 in the datasheet, category label to select the K1:P1 in the datasheet, and in the Insert property, select Enable data insertion, insert type, and choose Location, Series, and Select Series 1, Target to select the A2 in the control table, and in the Appearance attribute, select the Series tab, the series shape is set to star, and the fill color is orange (Figure 4).

Column chart settings. Ibid. Enter the title of the chart, select "by Series", and click "+", "value, select I2:i5 in the datasheet, category label to select the J2:j5 of the datasheet, and in the Insert property, select Enable data insertion, insert type, choose Row, Series, and then select Series 1. Source data Select the J2:j5 in the datasheet, and at the target, select the A1 in the control table.

Pie chart settings. Enter the title of the chart, the data "value" Select the L2:L7 in the control table, and the label selection control D2:d7 in the table (Figure 5).

4. Combo Box Interoperability Settings

To make 3 charts behave well, you also need to add 3 combo boxes to the canvas and make the necessary settings.

Select combo box 1, in the general properties, "label select A2:a25 in datasheet, data insertion to select Series 1, insert type Select filtered rows, source data Select A2:h25 in datasheet, target select control c2:j13 in table, and in behavior attribute, select the in?? The Standard tab, select label for the type of entry selected, A1 in the Entry Selection control table (Figure 6).

Combo box 2 general Properties label setting is the same combo box 1, "Data Insert" select "Series 1", insert type Choose "column", "Source data" select K1:P5, "target" select I1:i5; " Behavior property "common" tab, entry Type Select "Location", entry selected A2.

Combo box 3 general Properties label setting, data insertion, insert type setting with combo box 2, "Source data" select E1:j7, "target" select L1:l7; " Behavior Properties Common tab The entries are set to the same combo box 2.

Finally, put the 3 combo boxes together and place them in the next layer of a chart, blocking them with a chart.

When all the part property settings are complete, you can export the chart using the file → export menu, generate a separate SWF file, or export the chart to a PPT file for demonstration.

Extension tips:

The above only listed the product sales linkage demonstration. In fact, in life, as long as the total score data from different angles of the presentation of the occasion, can be used in this way to produce demonstrations. For example: School District students subject score, the medical system of various types of drug sales, large companies in the distribution of office supplies can be used in this way to produce demonstrations. Excel Tutorials