Summarize and share commonly used data description and analysis functions in Excel

This article brings you relevant knowledge about excel, which mainly introduces commonly used data description and analysis functions. Descriptive statistics is a commonly used method in data analysis. It refers to the Mathematical methods are methods for organizing and analyzing data, and estimating and describing the relationship between data distribution, numerical characteristics and random variables. I hope it will be helpful to everyone.

Related learning recommendations: excel tutorial

Descriptive statistics usually include three parts: central trend analysis, discrete trend analysis and correlation analysis . Use Excel function formulas or analysis tools to meet such analysis needs.

1. Use functions for data analysis

Let me give you an example.

As shown in the above figure, column A is the data of a certain product. Now we need to count its various description analysis data in columns C:D. The relevant function formula is as follows▼

1) Average

=AVERAGE(A2:A16)

2) Standard error

=STDEV(A2:A16)/SQRT(COUNT(A2: A16))

3) Median

=MEDIAN(A2:A16)

4) Mode

=MODE(A2:A16)

5) Standard deviation

=STDEV(A2:A16)

6) Variance

=VAR(A2:A16)

7) Kurtosis

=KURT(A2:A16)

8) Skewness

=SKEW(A2:A16)

9) Extreme Difference

=MAX(A2:A16)-MIN(A2:A16)

10) Average confidence level (95%)

=TINV(0.05,COUNT(A2 :A16)-1)*STDEV(A2:A16)/SQRT(COUNT(A2:A16))

Snap your fingers and explain the meaning of the above nouns

Average, medium The digit, mode, etc. are indicators that express the central tendency of the data. The average is the result of adding N numbers and dividing by N; the median is the value in the middle of a set of data sorted by size; the mode is the value that appears most frequently in a set of data.

Variance and standard deviation are indicators of the dispersion of data. They reflect the degree of dispersion from the mean.

The kurtosis coefficient and skewness coefficient are indicators that express the shape of the data distribution.

The kurtosis coefficient is an indicator that describes the steepness of the peak of a symmetric distribution curve relative to the normal distribution. If the kurtosis coefficient is greater than zero, there will be less extreme data on both sides; if the kurtosis coefficient is less than zero, there will be more extreme data on both sides.

The skewness coefficient is an indicator that describes the symmetry of data based on the normal distribution. If the skewness coefficient is equal to zero, the data distribution is symmetrical. If the skewness coefficient is greater than zero, the distribution is positively skewed; if the skewness coefficient is less than zero, the distribution is negatively skewed. A skewness coefficient greater than 1 or less than -1 is called a highly skewed distribution, and a skewness coefficient in the range of 0.5~1 or -0.5~-1 is called a moderately skewed distribution.

2. Data Analysis Tool Library

In addition to using function formulas, Excel also provides a [Data Analysis] tool library.

Under the [Development Tools] tab, click [Excel Add-in], select [Data Analysis] in the dialog box that opens, and click the [OK] button. Load the tool library under the [Data] tab.

Under the [Data] tab, click [Data Analysis] → [Descriptive Statistics] (located on the far right side of the tab) to open the [Descriptive Statistics] dialog frame.

Set the relevant parameters in the [Descriptive Statistics] dialog box as follows

[Input area]: Select A1:A16.

[Grouping method]: Specify whether the input data is arranged in rows or columns. In this example, progressive is used.

[Logo in first column] checkbox: This example contains a title column, so this option is checked.

[Output Options]: Select [Output Area] and enter F1 in the selection input box as the storage location for the output results.

[Summary statistics] check box: If this check box is checked, the descriptive statistics results will be displayed, otherwise the results will not be displayed. In this example, check this option.

[Mean Confidence] checkbox: If this checkbox is checked, the output will include the confidence of the mean. In this example, enter the default value of 95, indicating that you want to calculate the mean confidence level at a significance level of 5%.

[Kth largest value] check box: Specify the largest value in the data to be output as needed. This example only needs to get the maximum value, so this option is not checked.

[Kth smallest value] check box: Specify the smallest value in the data to be output as needed. This example only needs to get the minimum value, so this option is not checked.

After confirmation, the return result is as follows (consistent with the calculation result of the formula)

Related learning recommendations: excel tutorial

The above is the detailed content of Summarize and share commonly used data description and analysis functions in Excel. For more information, please follow other related articles on the PHP Chinese website!