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

The original title: "the line chart you made is too Low, this is the data analysis chart of NO.1!" "

The annual double Twelve just ended yesterday. How are your wallets?

Let me show you some of my express deliveries.

Screenshot of an East order in ▲

▲ a cat order screenshot of the express delivery, and then look at my wallet, cold heart 😭

Set up FLAG at the beginning of the year: you must save 2W yuan by the end of the year. Now open your own Alipay, and the balance is less than 5000. 2022 is almost over. Where's all my money, 💰, 😭?

In order to study the consumption situation and manage money well next year, I opened Alipay, loaded all the consumption situations this year, and simply made a consumption trend chart.

We can see that consumption accounts for a large part of the daily offline payment and online shopping. It seems that we will have to hold back the chopping of hands next year. The total monthly consumption is 4000 yuan. The rest is used for financial management. The monthly offline payment is controlled at less than 40%, and the online shopping is less than 20%. The goal will be specified in case it is achieved, 💪.

How do you do it exactly? Share it with you now! I hope it can bring inspiration to everyone.

Step ❶: export the bill details from Alipay. Open the Alipay computer version, find the [transaction] section, search according to the screening time, select [Excel format], and download the bill.

Web site: https://www.alipay.com/

❷ to add a secondary column to create a shaded line chart, you need to add a secondary column whose value is the same as that of the amount column (column B).

Enter the following formula in the C2 cell and drag the mouse down to fill the formula:

= B2

At the same time, add a percentage auxiliary column to show the percentage of each bill to the total.

Enter the following formula in the D2 cell and drag down:

= B2/$B$8 ❸ insert chart

Select the data of A1:C7 and insert [combination Diagram].

Select [line chart with data tag] for the amount column and [area chart] for the secondary column.

❹ modified format ① modified line chart format

Adjust the line color, marking options, fill, and border of the line chart slightly.

Select a line chart, and right-click [format data Series]:

[lines] change the color to "blue" and the width to "1 pound"

Select [tag], [Mark option] to change to [built-in]-Circle-size "5"; Click [fill]-[solid Color fill]-"White"; select [Border]-[automatic], the color should be consistent with the line color, and the width should be changed to "1.5 pounds".

② modifies the area map format

Select the area chart, and right-click [format data Series]:

[fill] is changed to [gradient fill], type is selected [Linear], the position of the gradient aperture is set to 0%, 74%, 100%, and the transparency of the aperture is set to 0%, 54%, 100%.

If you need to modify the aperture, click "+" and "X" to achieve it.

❸ removes the legend and chart title and chart border.

Click to select "Chart title" and "Legend" respectively-press [Delete] key to delete

Click the Chart-[format] tab, and change the shape outline to no outline.

❹ modifies the spacing of the main ordinate axes and the position of the horizontal axes.

Select the ordinate axis, right-click [format coordinate axis], and change the unit to "20000".

Select the Abscissa, right-click [format Axis], and change the position of the axis to [on the tick line].

❺ modifies fonts as needed

Click to select the chart-[Font] tab, and change the font to "source boldface" and the font size is "10".

❻ add percentage data column ① right-click the chart-"Select data", click "add", add the series name "amount percentage" and series value (percentage data of the bill), and [OK].

② changes the newly added chart type to marked line chart and places it on the secondary axis check box.

③ adds the data label and changes the label position to [up].

④ changes the shape fill and shape outline of the chart format to [No fill] and [No outline], respectively.

The font color of the ⑤ secondary ordinate axis is changed to "white"; the label position of the principal ordinate axis is selected [high]

Finally, change the two highest percentage values [font color] to "bold red" (note: only the two numbers with the highest percentage are selected here).

The final chart shows:

To sum up, the ❶ line chart is generally used to show a certain trend, which can be combined with the area chart in both beautiful and professional form.

❷ if you want to stack the charts of regular numbers and percentage numbers together, you can use primary and secondary axes and modify the scale value.

❸ does not want to add a percentage while the chart can show the percentage situation, you can use the pie chart to try, it is easier to make, this is I do a circle chart proportion situation, interested partners can also try.

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