There is such a table on the stock market page of Oriental Fortune Network:

The table data are the financial indicators of the corresponding stocks. In addition to the conventional quantitative data, what is commendable is the last row of " "Quartile Attributes" helps us interpret each financial indicator in a qualitative way, thereby forming an overall understanding of the stock's target. The form of In-cell Chart is used for visualization, which is very novel and the effect is very intuitive.

In this issue, we take the above picture as an example to introduce you to the Excel method and extended gameplay of quantile attribute diagrams.

Production ideas:

First, let’s look at the qualitative labels contained in this picture: low, lower, high, higher, *** four labels, in the picture There are four gears corresponding to bottom to top. The color of the gear is determined by the indicator attribute. For example, when the indicator has a "higher" attribute, the second gear on the graph turns blue, and the other gears remain colorless.

Next, decide what type of chart you want to use. Obviously, it is most appropriate to use a bar chart. The chart series contains 4 values. For example, when the corresponding label of the indicator data is "higher", the chart series value should be (0, 0, 1, 0).

1. Construct the data source

The data source construction process and effect are as follows:

What needs to be explained is:

Table 8 The row "Gear Division" uses data validity to make a drop-down list (low, lower, higher, high).

The "numeric conversion" in row 13 of the table is an intermediate variable, and a function (hint: MATCH function) is used to return the serial number corresponding to the label. For example, when cell E8 selects "lower", cell E13 returns the value 2 .

The "Data Source Production" table in Part 3 is used to generate charting data, that is, when the corresponding label of the indicator is "Higher", the other label data of the data source is 0, and the "Higher" label data is 1. (Tip: Use the IF function)

2. Make a chart

1) Select cells C18:C21 and insert a bar chart;

2) Select the series , set the category spacing to 0 in the series format and have no border lines. At the same time, set the maximum coordinate value to 1 and delete the series flag;

3) Delete the vertical grid lines and add the main horizontal grid lines. Delete the horizontal and vertical coordinates, set the drawing area border to gray, and set the series fill color to dark blue;

4) Cancel the chart border, adjust the chart size, and drag and drop to cell C28;

5) Copy the chart to several other cells (D28 to J28) and modify the data source of each chart. The final effect is as follows:

The above is the drawing process of the quartile attribute chart. Of course, you can create attribute maps for fifth, sixth, and...any quantiles as needed. Due to limited time, I am unable to list more detailed steps, so please bear with me. Friends who are interested can try to make it. If you have any questions, you can leave a message on the public account.

The following is the GIF dynamic effect:

In addition to displaying qualitative attribute labels, quantile attribute diagrams can also appropriately display changes in object rankings.

Recently, in order to prepare for the strategic evaluation of credit cards, I needed to create a PPT showing the changes in the rankings of credit card competitors’ main indicators in the past three years. When I was racking my brains on how to effectively show the changes in rankings, I suddenly thought of the ranking changes. Bit attribute map. So, I tried to use PPT to make a ranking change chart:

Of course, in PPT, it is completely drawn with shapes, without any technical content.

Finally, let’s make a brief summary.

Quantile attribute diagram:

Purpose

- Display qualitative attribute sequence, suitable for 3-5 sequence items (approximately)

< p> - Display object ranking changes, the number of objects is not limited

Advantages: The form is novel, the data is visible, and can be placed in cells

The above is the content of this issue, thank you all for your support Follow and read, I hope it can give you some inspiration.

If you have any questions or suggestions during operation, please leave a message in the background of this official account;

If you have more application ideas, you are welcome to communicate at any time.