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

How to make the following data into a chart, which can not only show the proportion of the major categories of ABC, but also reflect the proportion of small category A, B, C in A.

I used two circles to show the relationship between them.

It's a bit of a shame. At that time, some of my friends saw some problems, and even jokingly called this picture a "helmet picture", which made me "bitter" all the time.

The reason may be that the "head" is too big, so that the primary and secondary are not clear enough.

So I made changes to the chart again, here! This is the picture below.

Doesn't it look better?

This kind of chart is suitable to reflect the percentage structure of the data, for example, there is a classification in the data, or a smaller percentage of the data, and the classification or smaller data can be placed on the outer ring chart to reflect the percentage of the subdivided data.

The main idea of making this chart is to superimpose two rings of the primary axis and four rings of the secondary axis, and then set the color of the auxiliary space-occupying part (such as the gray part of the following image) to colorless.

Let me briefly talk about the steps of making this kind of chart:

❶ first collates the drawing data according to the original data.

It should be noted that the proportion of column F is the mapping data obtained by multiplying the percentage of subclass A, B, C (20%, 50%, 30%) by the proportion of A, respectively.

For example, 8% of F3 cells are calculated from 40% to 20%. In addition, the function of "other" in the subcategory is to occupy space for drawing.

❷ select the cell range "B2:C5"-open the insert tab-find the pie chart icon and click-Select "Ring Chart".

❸ check area "E2:F6"-press the shortcut key [CTRL+C] to copy-then select the chart-press the shortcut key [CTRL+V] to paste.

❹ for the convenience of setting up later, we renamed the two series names to large class and small class, respectively.

Select Chart-[right]-[Select data]-[Edit]-modify the series name.

PS. Note that the names of "big category" and "small class" should not be reversed.

❺ then adds four series with a series value of 1, named "large class placeholder", "subclass placeholder 1", "subclass placeholder 2", and "subclass placeholder 3".

At this time, there are six circles in the chart:

❻ select any series in the chart-right-click-[change series chart type]-check [secondary coordinate axis] for "small class", "small class occupies 1", "small class occupies 2", and "small class occupies 3".

❼ selects the series "big class" and sets it to 10% in [donut size], then selects the series "small class" and sets the [donut size] to 55%.

The effect is shown in the following figure:

❽ sets the colors of all auxiliary series (placeholder series) in the chart to none:

The final result is shown in the following figure:

Reassemble each series of colors, delete legends, etc.

To make the chart more three-dimensional, we can add shadows to the data series format.

❾ finally adds tags and sets the font color, as shown in the image below.

Write at the end:

This helmet chart is suitable for displaying multiple category data and the proportion of subcategories in one of the specified categories.

Used when showing the inclusion or affiliation of the data.

Through the transformation of the drawing data, the space occupying series is added by using the primary and secondary axes to make the ring diagram of the subclass thinner.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Zhao Jiaoyang, Editor: Yali Zi, Zhu Lan

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.