Shulou(Shulou.com)11/24 Report--

Recently, Xiaohua helped a fan draw a novel chart, which is both interesting and practical.

This article will share the story and drawing method of this chart. I hope that the way of drawing will be enlightening to all readers.

Here's the thing.

A fan Xiao K is in charge of the South China region of G Group, and the group will evaluate and rank the city companies in the region every quarter. As the assessment results of regional companies are getting worse and worse, it is necessary to make a summary and reflection.

As a result, Xiao K, who has always been known as an expert in charts, drew clusters of bar charts and line charts with scores and rankings for nearly half a year, and happily sent the form to the boss for review, ready to get off work.

Unexpectedly, "understand elder brother" a sentence from the boss, immediately let Xiao K classmate put down his backpack and sit back: these two pictures are very good, but it is not convenient to compare, you score and rank in the same picture.

Xiao K started work immediately, divided into two, and got it done according to the boss's wishes.

Note: the use skills of the above figure include drawing the broken line on the secondary coordinate axis, and the secondary longitudinal coordinate axis uses a reverse scale, etc.

Although Xiao K is full of confidence, the boss of "understanding Brother" is still not satisfied: this picture is much better, but you don't find that all the points are drawn on the top of the column in the middle. You change it and correspond these points to the position of the column, you know what I mean?

With that, the "elder brother" boss did not forget to draw a draft picture.

Although Xiao K even claims to be, his heart has already been galloping, is this a chart that Excel can make?

So Xiao K sent a distress signal to Xiaohua. After thinking for a moment, Xiaohua gave a solution, perfectly reengraved what "know Brother" had in mind, and helped Xiao K pass smoothly.

How is such a dislocated broken-line column combination diagram drawn? And listen to Xiaohua explain step by step.

1. To construct the drawing data area, it is obvious that such a misplaced combination map can not be completed simply by combining the centralized chart types, it needs to be combined with the re-integration and arrangement of the source data.

In order to complete the drawing, we rearrange the original table according to the following drawing format. The formula of C10 cell can make the data collation easier, and the floret is provided together for reference only.

The C10 cell formula is as follows. The data of other cells can be dragged and filled:

= OFFSET ($C$9:C$9 match ($B10)), COUNTA ($2) * 2-1)

Is there anything particular about the data arrangement in the above picture?

The scores of each city in the same month of ❶ are arranged separately. Combined with the cumulative line chart, the monthly bar chart of the score of each city can be arranged in turn.

❷ inserts a blank column between the monthly data, which can make the continuous arrangement of columns leave a blank column in each month to show the interval. Combined with adjusting the classification spacing, the stacked column chart is simulated as a cluster column chart.

❸ different city data branch arrangement, that is, different series, obvious difference after drawing is also easy to edit.

❹ arranges the ranking values in the same way, and inserts a line chart to mark points that correspond to each other on the same vertical line with the same city, and the lines form the effect of mutual dislocation.

2, insert chart to change chart type select B10:Y15, click insert column chart-stacked column chart, modify the first three series for stacked column chart, the last three series for line chart, and check to draw in [secondary coordinate axis].

3, modify hidden and empty cell settings, click the [Select data] button, select any broken line series, click the [Hidden cells and empty cells] button, and check [Connect with straight Line].

4. Modify the axis parameters and continue to select the reference range of the Abscissa value as B9:X15 (misplaced column with the data area, so that the month label of the Abscissa resides in each "cluster" column).

Modify the secondary longitudinal coordinates to reverse the order of the scale, and finally modify the maximum and minimum value of the coordinate axis to distinguish between the column and the broken line, so as to avoid twisting into a mass, which is not easy to observe.

5. Adjust the gap width and adjust the gap width to 10%, so that the corresponding columns of the same month data are more together to form a "cluster".

At this point, the chart has basically taken shape.

You only need to modify the title, legend, filling color, linetype, marking style, etc., and a beautiful combination of dislocated broken-line columns will be on the paper. Have you learned it?

There are three key points in making this chart, which should not be ignored.

The ❶ drawing data area needs to be rearranged, pay attention to the separation of branches and reserve blank columns and cells

After ❷ inserts the data, you need to modify the blank or hidden cell settings so that the valid data are directly connected by a straight line.

After ❸ drawing, it is necessary to modify the gap width of stacked columns to make the same kind of columns form "clusters".

The purpose of drawing is to make the data more intuitive, based on which we should choose the way of drawing, instead of sticking to the existing charts and resting on our laurels.

In addition to using one chart to simulate another chart as in this article to break the restrictions, you can also use cells, insert shapes, picture overlaps and other forms to draw charts. Do you understand?

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Xiaohua

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.