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

Hello, Hi, I am the uncle Mr Zhao, who prefers function formulas and likes to use Excel charts to manage warehouses.

Today is an intelligent information age, WiFi has long become an indispensable part of our lives.

In the information chart of data visualization, there are also charts showing percentages similar to WIFI signal strength.

Compared with ordinary pie charts and ring charts, this kind of chart has a graceful and colorful figure, giving people a bright feeling in front of their eyes.

It has the following characteristics:

❶ is done by stacking the doughnut chart, which is distributed in the range of 1 to 3 of the size of the ring.

❷ uses curved bars to compare the size of the data.

❸ arc bar is arranged from top to bottom, from long to short, automatically displaying multiple series of data.

After the original data of ❹ is updated, the chart is updated dynamically.

So today we're going to talk about how to use Excel to make this kind of WiFi chart.

1. Data preparation in order to facilitate the automatic update of the original data, the "occupancy" of column C in the following figure is a random number.

To sort the "occupancy" of the original data in ascending order, enter the formula in cell E3:

= SORT (B3Rose C7Pol 2)

The SORT function, a new function of Microsoft version 365, sorts column 2, that is, occupancy, in ascending order in the cell range "B3:C7".

The effect is as follows:

If you are a small partner in a lower version, you can enter the following formula in the E3 cell, and then drag the fill down to the right.

= INDEX (BrangeBMagol MOD (SMALL ($Cut3VOBJING 7 * 10 ^ 6 + ROW ($3VO7), ROW (A1), 10 ^ 4)

It can also achieve the effect of extending ascending order:

Because the chart is distributed in the 1x3 range of the ring size, you need to add two additional columns.

Secondary column 1: enter the formula in cell G3, = 1-F3, and then drop down to fill.

Secondary column 2: enter a value of 2 in the H3 cell column, and then drop down to fill.

2. Make a circle chart ❶ Select drawing data "E2:H7" Click [insert]-[Chart]-[Pie Chart]-Select the second circle chart on the right-click [OK].

Get a chart consisting of five rings, and the effect is as follows:

❷ Click any series in the chart-right-click-[format data Series]-[start Angle of the first sector] to 300-[Ring size] to 25%.

The chart looks like this on the right side of the picture below.

❸ sets the data point "Auxiliary column 2" of each series in the chart to no padding one by one.

Double-click to select a series of data points "Secondary column 2"-right-click-[format data points]-[colorless fill].

Double-click to select the next data point and press the [F4] key to fill it quickly.

PS: the function of the [F4] key is to repeat the last operation.

The effect is shown in the following figure:

❹ follows the above steps to set the colors of the data points "occupancy" and "secondary column 1" respectively, such as blue and light gray in the following figure.

3. Insert circle insert a circle, clear the outline, set the color, and place it in the position shown in the following figure:

4. Add label insert-[shape]-[text box]-[format shape]-[fill none]-[wireless bar].

Then select the Chinese text box-enter the "=" sign in the edit bar, and then select the corresponding cell. In this way, the contents of the text box are updated synchronously as the contents of the cell change.

Repeat the above and add tags in turn.

Finally, change the title, remove the legend, and beautify the chart.

In this way, our WiFi chart is complete.

Did you learn it?

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