Home > Topics > excel > Practical Excel skills sharing: One chart to handle data comparison, trend and proportional contribution

Practical Excel skills sharing: One chart to handle data comparison, trend and proportional contribution

青灯夜游
Release: 2023-04-13 17:27:45
forward
8102 people have browsed it

Practical Excel skills sharing: One chart to handle data comparison, trend and proportional contribution

In our daily work, we often encounter data comparison charts. Data trend charts are also charting skills that we must know. I believe many students can complete them. But recently, a classmate’s weird leader suggested: “It needs to have both a comparison effect and a trend effect, and multiple series of charts must be displayed by month throughout the year, and all of this must be done in one chart!”

So, the "difficulty" in our work lies not in lack of skills, but in the boss's imaginative thinking. So today I will give you an idea for making a "multi-series comparison/trend chart".

[Data source]

Practical Excel skills sharing: One chart to handle data comparison, trend and proportional contribution

Before we look at the following article, students can think about it themselves if such data makes If you design it, what will it look like?

【Text】

●If there is only 1 set of data

Practical Excel skills sharing: One chart to handle data comparison, trend and proportional contribution

Nothing to say, just use column chart, bar chart, donut chart, whatever you want.

Practical Excel skills sharing: One chart to handle data comparison, trend and proportional contribution

Even if we need to see percentages and trend lines, we can insert an auxiliary column of percentages in column C in the data, as follows:

Practical Excel skills sharing: One chart to handle data comparison, trend and proportional contribution

STEP1: Select the cell A1:B4 area, insert the toolbar - chart - clustered column chart;

Practical Excel skills sharing: One chart to handle data comparison, trend and proportional contribution

Then you get the first draft of a column chart.

Practical Excel skills sharing: One chart to handle data comparison, trend and proportional contribution

STEP2: Right-click anywhere in the chart area, and in the pop-up menu, left-click and select "Select Data".

Practical Excel skills sharing: One chart to handle data comparison, trend and proportional contribution

In the pop-up setting box, click "Add" series on the left, enter data as follows or select the worksheet area to enter content;

Practical Excel skills sharing: One chart to handle data comparison, trend and proportional contribution

STEP3:Right-click anywhere in the chart area and select "Change Chart Type";

Practical Excel skills sharing: One chart to handle data comparison, trend and proportional contribution

Change the chart type of the percentage series to Scatter with Smooth Line and Data Markers.

Practical Excel skills sharing: One chart to handle data comparison, trend and proportional contribution

This way we got the prototype of the chart for the second step.

Practical Excel skills sharing: One chart to handle data comparison, trend and proportional contribution

STEP4: Select the graph of the proportion series in the figure, right-click the mouse, and select the "Format Data Series" option in the menu;

Practical Excel skills sharing: One chart to handle data comparison, trend and proportional contribution

In the series options, click "Secondary Axis",

Practical Excel skills sharing: One chart to handle data comparison, trend and proportional contribution

Get the chart, as follows:

Practical Excel skills sharing: One chart to handle data comparison, trend and proportional contribution

Let’s beautify the chart interface and add data labels. We won’t go into details. Let’s give an example.

Practical Excel skills sharing: One chart to handle data comparison, trend and proportional contribution

●Let’s look at today’s main data

I have to say that in actual work, the production of charts should still focus on , there is a prominent point to express a certain data. If a lot of information is crowded into one picture, it will appear confusing.

The combination of column chart and line chart (the author uses a scatter chart here) is very commonly used, and this combination has both contrast and trend, which is very convenient to watch. So can our main data today also achieve such an effect? As shown below:

Practical Excel skills sharing: One chart to handle data comparison, trend and proportional contribution

[Initial drawing planning]

If we decide to use a column chart and a line chart to represent this table, then we need to have a rough sketch on paper or in our mind: each set of graphs corresponds to a project team’s 12-month period. data. The difficulty is that the bottom ends of each group of graphics must be aligned; the polyline must appear in the group of graphics for this project.

[Chart type selection]

Obviously, if multiple column charts are superimposed, we should choose "Stacked Column Chart", but if we use If you operate on the original data, it will be the chart below.

Practical Excel skills sharing: One chart to handle data comparison, trend and proportional contribution

The bottom ends of each item group are not aligned, which is not what we need, but if we insert a "placeholder graphic" in the middle of each series, is it okay? What about "raising" each series of graphics? Then set the color and border of the placeholder graphic to "no color" to simulate the effect of each row being a column chart and aligned at the bottom, as follows:

Practical Excel skills sharing: One chart to handle data comparison, trend and proportional contribution

Students can take a look. Here we have removed the colors of the placeholder graphics at points A and B. The green placeholder graphics at point C are still retained. Can you see that the bottom ends are aligned.

After we have space for placeholders, we can add scatter points to these spaces to mark the proportions, and finally connect these scatter points to simulate the effect of a line chart. So for the trend line, we use "scatter chart with smooth lines and data markers".

[Organizing data]

We need to insert a placeholder graphic between the two series of graphics. If considered in the data table, it is to add an auxiliary data . Let’s take a look at the data sorting stage.

Practical Excel skills sharing: One chart to handle data comparison, trend and proportional contribution

"Good charts are often not made from source data." Being good at using auxiliary data is an important sign of advanced chart making, so there is also a saying "Use of Charts" , in the end it all comes down to the use of functions.”

The "red" characters in the above picture are the data label display values ​​of the series, while the "blue" characters are the placeholder graphics of the chart and the marking points of the scatter points. Let's take a look at each data one by one. Function design idea:

● A: Data label value of item A

  • ##B8 ​​cell function: =B2

● Point A placeholder: Placeholder graphic of project A

  • B9 cell function: =MAX($B$2:$M$4)*2-B8

Explanation: B2:M4 is the source data area. We use twice the maximum value in the data as the highest proportion reference in the graph. Then the graphs made by each project will be evenly proportioned. The principle is: the number of data points The value corresponds to the placeholder point value = the maximum value in the data source * 2, so the corresponding placeholder point value = the maximum value in the data source * 2 - the data point value

● B: The data label value of project B

  • B10 cell function: =B3

● Point B placeholder: Placeholder graphic of project B

  • B11 cell function: =MAX($B$2:$M$4)*2-B10

● Point C placeholder: placeholder graphic of project C

  • B12 cell function: =B4

● Point C placeholder: placeholder graphic of project C

  • B13 cell function: =MAX($B$2:$M$4)*2-B12

● Proportions of A, B, and C: items A, B, and C Data points, respectively, account for the proportion of the total data of all projects in this month

  • B14 cell function: =B2/SUM(B$2:B$4)

  • B15 cell function: =B3/SUM(B$2:B$4)

  • B16 cell function: =B4/SUM(B$2:B$4)

● Proportion point A: The trend chart of project A, corresponding to the coordinate position of the scatter point.

  • B17 cell function: =B8 B9*1/3

Description: The scatter plot itself requires two horizontal and vertical coordinates Yes, if we do not set the The project occupies one-third of the site.”

Practical Excel skills sharing: One chart to handle data comparison, trend and proportional contribution

● B and C proportion points: trend charts of B and C projects, corresponding to the coordinate positions of the scatter points.

Practical Excel skills sharing: One chart to handle data comparison, trend and proportional contributionB18 cell function: =SUM(B8:B10) B11*1/3

    B19 cell function: =SUM(B8 :B12) B13*1/3
  • Note: Students should note that because the scattered Y-axis coordinates of each project have "height" requirements, their data sources, It is to accumulate other project data and add one-third of the corresponding proportion point, so that it is closer to the corresponding column chart.
  • 【Chart Production】

The most difficult part of data sorting has been completed. Do you still remember how we inserted the chart when we did a single set of data? Hurry up and give it a try!

Practical Excel skills sharing: One chart to handle data comparison, trend and proportional contribution

Blue area: Column chart

Select the A7:M13 area, insert "Stacked Column Chart", and add A , B, and C series add "data labels"

Yellow area: scatter plot

Follow the steps for designing charts for a single set of data at the beginning of the article, and repeat them Steps 2 and 3.

  • Select the B17:M17 area and insert "Scatter plot with smooth lines and data markers"

  • Select the B18:M18 area and insert "Scatter plot with smooth lines and data markers"

  • Select the B19:M19 area and insert "Scatter plot with smooth lines and data markers"

Add "Data Label"

Add the "Data Label" of the scatter plot, and select "Data Label". In "Set Data Label Format", set it to Press the corresponding "value in the cell" to display the content:

Practical Excel skills sharing: One chart to handle data comparison, trend and proportional contribution

In this way, students will get the following chart "blank".

Practical Excel skills sharing: One chart to handle data comparison, trend and proportional contribution

[Post-arrangement]

For post-arrangement work, you can refer to the author’s E diagram of “one deletion, two adjustment, three rows” ” approach to deal with it.

1. Delete the "vertical axis" and "tick marks" in the chart, as well as the legend that does not need to be displayed;

Practical Excel skills sharing: One chart to handle data comparison, trend and proportional contribution

2. Adjust the column shape The color of the graph and the color of the smooth line, set the placeholder columns that do not need to be displayed and the mark points on the smooth line to no line and no fill.

Practical Excel skills sharing: One chart to handle data comparison, trend and proportional contribution

3. Through the above operations, the chart has been basically formed. Select a series of the column chart and adjust the gap width to 5%.

Practical Excel skills sharing: One chart to handle data comparison, trend and proportional contribution

Adjust the data label position on the column chart to the inside of the axis, and adjust the data label position on the smooth line to the center;

Practical Excel skills sharing: One chart to handle data comparison, trend and proportional contribution

Practical Excel skills sharing: One chart to handle data comparison, trend and proportional contribution

Then adjust the position of each element in the chart, and change the font, font color, etc., to complete the effect of our picture above.

【Editor's Note】

Regarding the production of charts, it is recommended that you do not use all the default effects of Excel. A chart is more like a graphic design. Everyone’s aesthetics are different and the results are different. However, since “visualization” is meant to be viewed by others, it must be beautiful. Finally, let me share the color scheme used in today's graphic tutorial. Just enter the RGB value in "Other Colors - Customize".

Practical Excel skills sharing: One chart to handle data comparison, trend and proportional contribution

Related learning recommendations: excel tutorial

The above is the detailed content of Practical Excel skills sharing: One chart to handle data comparison, trend and proportional contribution. For more information, please follow other related articles on the PHP Chinese website!

Related labels:
source:itblw.com
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template