Shulou(Shulou.com)06/01 Report--

This article introduces you what EXCEL data analysis skills are, the content is very detailed, interested friends can refer to, hope to be helpful to you.

How do front-line manufacturers analyze data with the help of EXCEL tools?

We know that each company has its own data system, generally exported data for local files, for a small amount of data set, with the help of EXCEL work, can be quickly processed, and get the corresponding results, EXCEL tool with its simple and practical, convenient and fast favored by data analysts, the following will introduce some EXCEL data analysis skills, as well as two practical application cases, learn it! First, the commonly used EXCEL shortcuts are still using Ctrl+C copy, Ctrl+V paste shortcuts? Come and try these powerful Ctrl and Alt keys, 1Ctrl classes.

Ctrl+A full selection form

Ctrl+F Quick search

Ctrl+G rapid positioning

Ctrl+E intelligent filling

Ctrl+Q intelligent analysis

Ctrl+; generates a date stamp

Ctrl+Shift+ ↓ quickly select down

Ctrl+T inserts a chart style

Ctrl+Shift+L rapid screening

2Alt class

Alt+= automatic summation

Alt+Enter forced line feeds

Alt+ ↓ generates drop-down menus

Alt+Tab quickly changes views

Alt+N+V data perspective

Alt+A+M removes duplicate values

Alt+B+M Startup data Model

2. Five kinds of EXCEL functions commonly used

The editor sorts out the commonly used EXCEL functions, including text cleaning class, association matching class, logic operation class, calculation statistics class and time series class. It is classified and summarized according to different functions. There are many contents, and it is recommended to collect them.

3. Commonly used EXCEL analysis skills 1. Data adding conditional format

The case datasets selected in this section are as follows:

Select the data of "current out-of-storage amount", click on the conditional format, and select the first rule in the color level. The greener the color of the level is, the larger the value is, and the redder the color of the level is, the smaller the value is.

Select the "value-added" column, click the conditional format, select the gradient to fill the second rule in the data bar, and add a colored data bar to represent the value in a cell. The higher the value, the longer the data bar. The effect picture is as follows: as can be seen from the image, the amount of blanket out of the warehouse in this period is the largest, and the balance amount of bamboo fiber mat in this period is the smallest. The ring ratio increment of blanket is the highest, while that of bamboo fiber mat decreases the most, taking into account the season and climate, so the demand for blanket is high. The demand for bamboo fiber mats becomes less. Case study 1 the waterfall chart of the balance of the current period

Use the shortcut key Alt+N+V PivotChart in the data source table, drag the first-tier category into the row, drag the current balance amount to the value, and drag it twice

Click "current balance amount 2", and right-click "% of column Summary" in "value display method".

Copy the products in the PivotTable and the current balance amount, paste it into a numerical value selectively, arrange the current balance amount in ascending order, and insert a waterfall chart.

Click the column of the total twice and right-click to set it to summary.

Double-click the axis on the left and adjust the maximum value to 1.0 in the axis options on the right.

The final effect picture is shown below, from which we can see that the current balance of the core accounts for the largest proportion, while the current balance of the blanket accounts for the smallest.

2 Analysis of the achievement of personal income in sales

The dataset selected for this case is as follows:

Select sales, committed income, actual income, and insert a cluster bar chart.

Select the blue column, set it as the secondary axis in the data series format, and reduce the gap width.

In the fill format, set it to no fill, and set the color and width of the border.

Double-click the secondary axis, and in the axis options, set the maximum value to the same as the maximum value of the axis.

Click on the chart, in the + sign, add a data table, from the chart can be concluded that the actual income of Xiao Liang and Xiao Hong did not reach the promised value.

About EXCEL data analysis skills what is shared here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.