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

The original title: "how do ordinary people make good-looking charts?" This simple and practical secret is easy to use! "

Today, as you wish, Xiaohua brought the sequel, "Segmentation".

In this article, you will learn how to draw a variety of practical working charts by changing the layout of the same set of data.

The sales of a company from January to December are as follows

For most workers, the first reaction must be to choose A2:B13 to insert a bar chart, intuitive but ordinary!

However, if you arrange column B data in column B by quarter, as shown in the figure below.

At this point, you select A2:E13, insert stacked column chart, a quarterly color separation of the column chart is completed, isn't it a little delicate?

Compared with the two pictures, the latter is obviously more exquisite and more intuitive, and to achieve the transformation of the chart from ordinary to exquisite, all we need to do is to drag the data to arrange them separately.

A simple move, do not need to spend a lot of time to carve to achieve chart Level up, this is the working chart "sub" word formula! Are you impressed?

Then continue to dig deep into the magic use of the word "divide" with Xiaohua.

The "score" formula can also come in handy if you don't need to display data on a quarterly basis, but rather focus on maximum and minimum values.

Just use the Max and Min functions to separate the data into three columns.

Note: the cell format in the figure is dedicated to accounting, and the number 0 is shown as "-".

Among them: C column formula (take C2 cell as an example) is as follows:

= ($B2=MAX ($B$2:$B$13)) * $B2

Description of the formula:

The MAX function is the maximum function, and MAX ($B$2:$B$13) returns the maximum value of B2:B13.

$B2=MAX ($B$2:$B$13) compares B2 with the maximum value to determine whether the current value is equal to the maximum value, and returns TRUE if equal, FALSE otherwise.

Then multiply with B2, and at this time TRUE is 1. Zero false handles multiplication, so if the current value is the maximum, it returns itself, otherwise it returns 0.

The D-column formula (take the D2 cell as an example) is as follows:

= ($B2=MIN ($B$2:$B$13)) * $B2

Description of the formula:

The MIN function is the minimum function, and the rest of the calculation principle is consistent with the C-column formula.

The E-column formula (take E2 cell as an example) is as follows:

= B2-C2-D2

Description of the formula:

Only when B2 is the maximum or minimum, one of C2 and D2 equals B2, where E2 is 0; otherwise, E2=B2.

Note: the above three formula combinations are available only when the maximum value is not equal to the minimum value.

Then select the data to insert the stacked column chart, and a column chart that highlights the most value will be completed!

If you only want to achieve color filling, it is even easier, just interleave the data into C and D columns.

Continue to deepen the difficulty, if you want above-average and below-average cylindrical color separation filling to show the distinction, then we can use the mean function Average to arrange the data separately, as shown in the following figure:

Where:

The C-column formula (in the case of cell C2) is as follows:

= ($B2AVERAGE ($B$2:$B$13)) * $B2D column formula (take D2 cell as an example) is as follows:

= B2-C2

At this point, insert the stacked column chart to complete the high and low column color separation filling.

Similarly, the column chart below, which feels like a stuffed bar, is also drawn with the word "cent".

We just need to subtract the January-December sales from the set value, sort out the salvage value that complements it, and list it in column C.

Then select A2:C3, insert the stacked column chart, and then slightly retouch, you can complete the complementary filling column chart drawing.

If you want the parts of the column that exceed a certain set value to be filled with a specified color, you just need to separate them from the basic data.

Where:

The C-column formula (in the case of cell C2) is as follows:

= B2-D2

The D-column formula (take the D2 cell as an example) is as follows:

= MAX (B2-400)

In fact, there are many advanced charts, such as water drop chart, waterfall chart and so on.

They all use the core meaning of the "divide" formula-dividing a series into multiple series of drawings to show the same set of data, so as to make the data have a certain sense of hierarchy.

The above is the content of the working chart drawing method shared by Xiaohua-the word formula of "sub-division".

It includes quarterly color separation column chart, maximum highlight column chart, interval filling column chart, high and low separation column chart, complementary filling column chart and layered color column chart.

If you have any more charts you want to learn, please feel free to leave a message and let us know.

"sticky" formula link: who says chart beautification is too complicated? Just copy and paste one step and it's in place!

Learn some Excel every day, work efficiency UP~UP~. I'll see you tomorrow!

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: floret Editor: Yali, 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.