Excel-scatter chart (correlation and data distribution) Analysis

This article is excerpted from the author "website data analysis: data-driven website management, optimization and operation": item.jd.com112920.0.html scatter chart is a tool used to determine the relationship between two variables. Generally, A scatter chart uses two groups of data to form multiple coordinate points. By observing the coordinate point distribution, it determines whether there is a correlation between variables and

This article from the author "website data analysis: data-driven website management, optimization and operation": http://item.jd.com/11295690.html scatter chart is used to determine the relationship between the two variables, in general, A scatter chart uses two groups of data to form multiple coordinate points. By observing the coordinate point distribution, it determines whether there is a correlation between variables and

From the author's website data analysis: data-driven website management, optimization and operation: http://item.jd.com/11295690.html
A scatter chart is a tool used to determine the relationship between two variables. Generally, a scatter chart uses two sets of data to form multiple coordinate points. By observing the coordinate point distribution, determine whether there is an association between variables and the intensity of the correlation. In addition, if there is no correlation, you can use a scatter chart to summarize the distribution pattern of the feature points, that is, a matrix chart (quadrant chart ).
1. In correlation analysis, it should be noted that the correlation is different from the causal relationship. correlation indicates that two variables change at the same time, while the causal relationship is one variable that leads to another variable change. A scatter chart is just a preliminary data analysis tool. It can intuitively observe the possible relationships between the two groups of data. If possible relationships exist between variables during analysis, you need to further confirm whether there is a causal relationship and use more statistical analysis tools for analysis.
Continuous data should be used for correlation analysis. independent variables should be placed on the X axis (horizontal axis), dependent variables should be placed on the Y axis (vertical axis), and corresponding points should be drawn on the coordinate system. The shape of a scatter chart may be a linear, exponential, or logarithm relationship between variables. Taking a linear relationship as an example, a scatter chart contains the following typical shapes.

Positive correlation: When the independent variable x increases, the dependent variable y increases accordingly;

Negative Correlation: When the independent variable x increases, the dependent variable y decreases;

Unrelated: the dependent variable y does not change with the independent variable x.

For example, the website collects statistics on the customer's receipt days and satisfaction results. The highest satisfaction score is 5, as shown in 9-61. Select the A1: B30 area, click scatter chart in the "chart" module of the "insert" functional area, and select the "scatter chart with data only" button, you can see the scatter chart, right-click a data tag, select the "add trend line" command in the shortcut menu, and add the category axis, data axis title, and other charts to beautify the chart, the final result is 9-62.

Figure 9-61 customer satisfaction survey data

Figure 9-62 scatter plot after final beautification

The analysis scatter chart shows that there is a negative correlation between the receipt days and customer satisfaction. The longer the receipt days, the lower the customer satisfaction.

2. Matrix Analysis

Figure 9-63 shows the impact of a website on the company's strategy and business performance. Enterprise Strategy refers to the long-term development and survival of enterprises. Product settings focus more on competitor factors and later benefits. Business Performance refers to the impact of products on the benefits of enterprises in the current period. Enterprise Strategy is not necessarily related to current performance.

Figure 9-63 analysis results of product impact on enterprise strategy and business performance

Select the B2: C14 area, click scatter chart in the "chart" module of the "insert" functional area, select "scatter chart with data only", and delete the legends and grid lines, the result is 9-64.

Figure 9-64 scatter chart after the legend and grid line are deleted

To achieve the matrix effect, you also need to move the horizontal and vertical axes. Select the horizontal axis, right-click, and select the "set axis format" command to open the Settings dialog box, at the bottom of axis options, enter the calculated average strategic value of 2.7 for an enterprise, set "Main dial type" and "axis label" to "NONE", as shown in 9-65.

Figure 9-65 set the axis format

Similarly. Select the vertical axis, right-click, and select the "set axis format" command to open the Settings dialog box, at the bottom of "axis options", enter the calculated average business performance value of 2.8 in "Cross coordinate axis value, at the same time, set "Main dial line type" and "axis label" to "NONE". The matrix chart effect is 9-66.

Figure 9-66 matrix chart after moving the coordinate axis

It can be seen that the intersection of the ordinate axis and the horizontal axis is somewhat dependent on the upper right corner, which can be solved by setting the maximum and minimum values of the coordinate axis. Re-open the "set axis format" dialog box for horizontal and vertical coordinates, and set the maximum and minimum values to a value slightly greater than the maximum and minimum values of the enterprise strategy and business performance of each product. In this example, the maximum and minimum values of the horizontal and vertical axes are set to 4 and 1.5 respectively.

Add the title of the axis and mark the high and low directions.

Right-click any generation point, select the "add data tag" command to add a tag for each point, and change the tag to the product name [1]. The final result is 9-67.

Figure 9-67 scatter plot after final beautification

It can be found that product A has A great impact on corporate strategy and business performance, and product F has the lowest impact, l, K, G, D, M, and H products have a great impact on enterprise strategy, while C and E products have a great impact on business performance. Through a matrix chart, managers can easily make relevant decisions.