Excel2007 making histograms and normal distribution graphs

The distribution and distribution trend of data analysis data of the same dimension should be reflected by making histogram and normal distribution curve.

For example: The daily income of all employees is known and the distribution of employee income is analyzed.(51.7, 50.6,57.9,56.9,56.7,56.7,55.3,56.1,53.7,54.5,56.9,51.9,52.1,55.1,54.9,54.7,55.3,55.3,54.5,54.9,54.5,55.3,54.9,54.3,53.7,5 3.5,53.7,53.1,54.5,53.1,53.9,53.5,53.3,53.9,53.5,53.5,52.5,53.3,53.5,53.3,53.7,53.1,54.5,53.9,56.7,54.5,54.3,55.1,54.1,54 .5,53.9,53.1,53.3,55.3,55.7,56.1,54.7,53.1,53.3,52.7,53.1,52.9,53.1,54.3,53.1,52.7,53.1,53.3,53.1,53.3,53.1,53.3,55.1,54. 7,54.9,54.3,53.9,53.7,53.9,53.5,54.5,54.3,55.5,55.7,55.5,54.9,55.3,55.5,53.7,54.1,53.9,55.7,55.9,53.7,53.5,53.1,52.3,52.7 , 52.9,53.3,53.9,52.7,53.5,53.1,52.7,51.9,52.5,53.9,54.5,55.7,55.3,54.9,53.1,52.9,54.1,53.3,54.7,53.9,54.3,54.1,53.7,53.3 , 52.7,52.9,52.5,53.9,53.5,54.1,54.1,54.7,54.9,54.9,54.1,53.3,52.9,53.7,53.9,54.3,54.1,54.5,54.7,54.9,52.1,52.9,53.5,52.7 , 53.1,53.1,53.5,52.9,52.9,53.1,53.3,52.7,53.5,53.9,54.9,55.1,54.3,55.1,54.3,54.3,53.9,54.5,54.5,54.3,55.3,54.5,54.9,53.5 , 52.1,55.3,55.7,55.7,55.5,54.5,57.7,54.7,53.7,53.1,53.7,55.9,56.1,53.9,53.7,53.3,53.9,53.9,54.5,54.7,56.1,55.7,53.1,53.7 , 53.5,53.9,53.9,53.5,53.3,53.1, 52.5,55.9,55.7,54.1,54.3,54.1,54.1,54.5,54.5,55.1,53.1,53.3,54.1,54.3,53.9,54.1,54.7,54.7,53.7,53.1,53.3,52.7,53.5,52.9 , 53.7,56.5,56.1,55.7,55.5,56.9,57.7,56.5,55.7,54.1,54.7,55.7,55.5,53.1,52.7,53.1,53.3,53.5,54.3,54.1,54.5,54.7,55.7,55.5 , 54.1,54.3,54.7,53.1,53.3,53.1,52.7,53.1,53.7,53.1,54.7,54.5,55.1,54.7,54.5,56.1,55.7,53.3,52.5,53.7,54.1,53.3,52.1,52.3 , 53.1,53.3,53.5,53.3,53.1,52.7,53.1,55.7,55.1,54.3,53.7,53.1,52.9,53.1,52.7,52.5,53.1,53.5,53.1,53.3,54.1,55.1,54.9,56.1 , 55.7,56.5,54.7,53.7)

First, the production of histograms

• Enter the data into the same column in Excel (put it here);
• Calculate "Maximum", "minimum", "Extreme Difference", "Number of groups", "Group spacing";

Maximum value: Max (A:A); (=57.9)

Minimum value: Min (a:a); (=50.6)

Extreme difference: maximum-minimum value; (=7.3)

Number of groups: Roundup (sqrt (A; A), 0), (=18)/*count (A:A) calculates the number of cells in a column that contain numbers, sqrt the square root, and Roundup rounds up the data by the specified number of digits */;

Group spacing: Extreme Difference/number of groups; (0.4)

• Data grouping: Select an appropriate value that is smaller than the minimum value as the starting coordinate for the first group, followed by group spacing until the last data value is larger than the maximum value.

Here, the first group starts with a coordinate of 50.5, increments by 0.4, the last set of coordinates 58.2, and a total of 20 groups

• Statistical frequency: Counts the number of data contained in each grouping.

Methods: Using the frequency function, the frequency distribution of a set of data is returned in a column of vertical arrays,

1, =frequency (the range of raw data, the data source of the histogram grouping);

2. Select the area where you want to count the number of data in each sub-group of the Histogram

3, then press "F2" Jian, enter the "edit" status

4, press and hold "Ctrl" and "Shift" two keys, then press "Enter enter" key, the last three keys at the same time release.

• Make a histogram: Select the frequency number to insert the bar chart
• Trim Column chart: Set data series format-modulation no spacing

second, make the normal distribution diagram

• Get normal distribution concept density: normdist (function: Normal distribution function that returns the specified mean and standard deviation)

Grammar:
Normdist (x,mean,standard_dev,cumulative)

X is the value for which the distribution is to be computed; ( with each grouping boundary value of "X", pull down sequentially )

Arithmetic mean of the Mean distribution (Mean=average (A:A) (data arithmetic average)) "Here is 54.09"

Standard deviation of Standard_dev distribution (Standard_dev=stdev). S (A:A) (standard variance of data) "1.15"

Cumulative=false (probability density function)

Cumulative is a logical value that indicates the form of a function. If cumulative is TRUE, the function normdist returns the cumulative distribution function, or FALSE to return the probability density function.

• Increase the normal distribution curve in the histogram: Set the curve, select the secondary axis

Excel2007 making histograms and normal distribution graphs