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

Hi, I'm Xiaolan who knows a little bit about Excel.

Excel is a good software.

It is not only very powerful in data analysis, but also has practical functions to be underestimated.

Take a look at these two pictures:

As you may have guessed, these vivid, interesting and practical pictures are all made with Excel.

But you may not know the specific method of operation, so let's take a look at it together.

01, make good use of functions to get good reviews on Taobao, do not hear that to use the function, feel very difficult, can not learn, in fact, here is only a very simple function formula, Xiaobai can also easily learn!

Before learning the operation, prepare the data, such as Taobao Shopping praise here:

Then in the cell where you want to display the chart, enter the following formula:

= REPT ("★", B2) & REPT ("☆", 5-B2)

Drop-down fill formula!

You can also change the color of the pentagram by modifying the font color:

Description of the formula:

= REPT ("★", B2) & REPT ("☆", 5-B2) REPT ("★", B2)

That is, let the character ★ be displayed repeatedly according to the numerical size of the B2 cell.

REPT ("☆", 5-B2)

That is, let ☆ display 5-B2 times.

Then concatenate the ★ and ☆ characters with &.

02, borrow the chart to change the movie score why is it the chart method? The reason is very simple, that is, the five-star score chart completed with the help of the chart.

Without saying much, let's take a look at the operation.

First of all, prepare data sources, such as movie ratings:

▲ data source: Douban then, get to work!

▋ insert shape-Pentagon ❶ Click [insert] tab-[shape]-"Stars and Flag", select "Star: pentagonal", and hold down the [Shift] key to draw.

❷ hold down the [Ctrl] key, use the mouse to drag the pentagram to copy, copy 4 times, select one of them, [Ctrl+A] all.

❸ click [drawing tools]-[format] tab-[align]-"Top alignment"-"horizontal Distribution", select all again, right-click-[combine].

❹ modifies the "shape color" and "shape outline" of the pentagram.

❺ makes a group of five-pointed stars with the same series of light colors to facilitate obvious contrast after color filling in the later stage.

PS: you can directly hold down the [Ctrl] key, drag to copy, and then change the color.

▋ adds auxiliary columns to prepare mapping data, and adds 3 auxiliary columns on the basis of the data source.

Column D data is the percentage of the score in the highest score.

▋ insert bar chart ❶ select column D, column E data, insert [Cluster Bar Chart].

❷ draws the percentage series on the secondary axis.

❸ adds "secondary axes".

❹ sets the primary and secondary ordinate axes to [reverse category].

❺ sets the maximum value of the primary horizontal axis to 1, removing the secondary horizontal axis.

❻ sets the Gap width to 60% for the percentage and percentage series.

▋ paste the pentagram into the bar chart ❶ select the dark pentagram, click [CTRL+C] to copy, then click on the chart, select the proportion series in the chart, press [CTRL+V] to paste, and fill with the picture.

❷ sets the filling mode of data series images to [cascading and zooming].

❸ pastes the light-colored pentagram into the bar chart as described above, and is also set to cascading and zooming.

▋ add data label and beautify chart ❶ add data label, select "inside axis" and open "more options".

Check "values in cells" in ❷ "label options" and uncheck "values".

❸ removes data labels from the percentage series.

❹ sets the label of each axis in the chart to "none" and the line setting to "wireless bar".

❺ bulk moves the data labels to the far left.

Look, it's done!

03. Extend the application to change thousands of images and learn the above two methods of making five-star ratings. In fact, you only need to make minor modifications, and you can also create other types of grade maps as needed, such as:

▋ adds sun and moon symbols

▋ uses other shapes

Column chart is selected for ▋ chart

04. To sum up, this article talks about two ways to make a five-star rating chart:

❶ function method, using REPT function to display characters repeatedly

❷ chart method, with the help of auxiliary columns and bar charts.

At this point, some students will say, [conditional format] seems to be able to achieve a five-star score, and there is a "star rating" in the icon set:

Of course. I won't talk about it here because of the lack of space.

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