Tools/raw materials
computer
Excel2007 and above
Calculate the average, standard deviation and related data
1
Assuming that column A stores such a set of sample data, we first calculate the central value (mean) and standard deviation of the sample.
As shown in the figure below, calculate according to the formula written in the figure. For the convenience of writing formulas, I listed the formulas used in the column next to the "Calculation Results" display.
The formula directly refers to the calculation of column A, which can ensure that no matter how much data there is in column A, it can participate in the calculation. Because it is a template, there will be no calculation error due to the change of sample data every time.
After the 2007 edition, the standard deviation function of Excel is STDEV. S and stdev.p.stdev. S is the standard deviation of the sample, STDEV. P is a sample-based population standard deviation. If you don't have sexually transmitted diseases. Please use the STDEV function.
2
The histogram of normal distribution needs to determine the number of groups, the upper and lower limits of group distance coordinates and so on. Write the formula shown below.
The number of groups is 25 first, and the distance between the upper and lower limits and the central value (how much σ) is 4 first. Because the calculation is done by using formula reference, these two values can be changed arbitrarily. Put it here like this for the time being
Please click to enter a picture description.
three
Calculate group coordinates. "Group" is filled in the order of 1- 100. 100 calculated values are listed here. The reason will be explained later.
Fill in 1 and 2 in G2 and G3 respectively. Select G2 and G3 cells and place the mouse on the small black square in the selected box in the lower right corner. When the mouse becomes a black cross, put down the mouse. Until the value increases to 100. The following two pictures
Please click to enter a picture description.
four
As shown below, the input formula of H2 is = D9, and the input formula of H3 cell is =H2+D$7. In order to always refer to the D7 cell in the formula, the absolute reference of rows is used in the formula here.
Please click to enter a picture description.
five
Select H3 cell and place the mouse on the small black square in the selected box in the lower right corner. Double-click when the mouse turns into a black cross to fill the remaining cells in the H column.
Please click to enter a picture description.
six
Calculate frequency. As shown in the figure, in I2 and i3, fill in the formula to calculate the frequency. Similarly, select the I3 cell and place the mouse on the small black square in the selected box in the lower right corner. Double-click when the mouse turns into a black cross to fill the remaining cells in column I.
Please click to enter a picture description.
Please click to enter a picture description.
seven
Calculate the normal curve function value. As shown in the figure, enter the formula in J2 column. Similarly, select J2 cell and place the mouse on the small black square in the selected box in the lower right corner. Double-click when the mouse turns into a black cross to fill the remaining cells in the J column.
Please click to enter a picture description.
end
Create a name for the normal diagram reference.
1
Create a name. Click [Name Manager] in the [Formula] tab to open the [Name Manager] dialog box and click [New] to open the [New Name] dialog box. Enter "Frequency" in [Name] and enter the formula "= Offset (Normal Distribution! $I$ 1, 1, normal distribution! $D$6). Then click [OK].
Note that the name of the worksheet where my data is located is "Normal Distribution". Yours may be "sheet 1" or "sheet2". In order to ensure the consistency of the formula, it is suggested to double-click the worksheet name and then modify it to "normal distribution" like me.
Please click to enter a picture description.
Please click to enter a picture description.
Please click to enter a picture description.
Also create the name "normal curve" and enter the formula "=OFFSET (normal distribution! $J$ 1, 1, normal distribution! $ D $ 6)"； Create the name "Coordinate" and enter the formula "= Offset (Normal Distribution! $H$ 1, 1, normal distribution! $D$6)"
Please click to enter a picture description.
end
Draw a normal chart
Insert column chart
Please click to enter a picture description.
Select the blank graphic you just inserted, switch to design in the [Chart Tools] option group, and click [Select Data] to open the [Select Data Source] dialog box.
Please click to enter a picture description.
Click Add to open the Edit Data Series dialog box, as shown in the figure, set Series Name and Series Value, and add the Frequency series. The "normal curve series" as shown in the figure is also added.
Please click to enter a picture description.
Please click to enter a picture description.
Please click to enter a picture description.
Edit the axis reference. Click Edit in the [Select Data Source] dialog box to open the [Axis Label] dialog box. Set the reference value as shown. Click [OK] to return to the [Select Data Source] dialog box, and then click [OK].
Please click to enter a picture description.
Please click to enter a picture description.
Make sure that the column chart is selected, and then click [Chart Tools]-[Layout]. Click the [Chart Area] drop-down list in the upper left corner, select "Series Normal Curve", and then click [Format Selection] to open the [Format Data Series] dialog box. Select [countershaft].
Please click to enter a picture description.
Please click to enter a picture description.
Please click to enter a picture description.
The charting tool option group switches to the Design tab. Click [Change Chart Type] to open the [Change Chart Type] dialog box. Select the disconnection diagram as shown.
Please click to enter a picture description.
Please click to enter a picture description.
Select the normal curve in the column chart, and then switch to the Layout tab in the chart tool options group. Click Format Selection to open the Format Data Series dialog box. Switch to [Linetype] and select [Smooth Line].
Please click to enter a picture description.
Please click to enter a picture description.
At this point, the graphics have been drawn. If you only need a column chart and don't need a normal curve, select the normal curve in the column chart and press the Delete key on the keyboard to delete it.
In some professional drawing software, the columns in the columnar distribution map are close together. To set this item, select the column in the column chart, and then switch the Chart Tools option group to the Layout tab. Click Format Selection to open the Format Data Series dialog box. The classification interval is set to 0%. Switch to [Border Color], and then select [Solid Line]. Pick any color, as long as it is not the same color as the column. Choose black here.
Please click to enter a picture description.
Please click to enter a picture description.
Please click to enter a picture description.
Finally, try to modify the value of the lower "group" and the distance between the upper and lower limits and the center value, and the chart will be updated automatically. In the third step, we calculated the value of the 100 group, so as long as the group here does not exceed 100, we can get the correct chart. Generally, 100 is rare, so the data of 100 is reserved in the third step, so that when changing groups, you can always wait for the correct chart. Of course, it doesn't matter if you count 1000 groups. Anyway, you have learned the method.
If the sample data changes in the future, directly replace the data in column A with the new sample data, set the required grouping and the values of the upper and lower limits and the central value, and the normal distribution map will come out in minutes. As for beautification, just change the relevant settings, such as the opening. Of course, you can beautify the chart more beautifully and use your imagination.
Please click to enter a picture description.
How to draw a normal distribution curve Normal distribution is the most widely used continuous probability distribution. Normal distribution curve usually refers to the image of the density function of normal distribution. It is characterized by a bell-shaped curve.
Probability distribution of normal distribution curve. Normal distribution is the distribution of continuous random variables with two parameters μ and σ2. The first parameter μ is the mean of a random variable that obeys normal distribution, and the second parameter σ2 is the variance of this random variable, so the normal distribution is recorded as N(μ, σ 2). The probability law of random variables subject to normal distribution is that the probability of taking values near μ is high, and the probability of taking values far from μ is low; The smaller σ is, the more concentrated the distribution is around μ, and the larger σ is, the more dispersed the distribution is.
The density function of normal distribution is characterized by:
Regarding the symmetry of the straight line x=μ, it reaches the maximum at μ, takes a value of 0 at positive (negative) infinity, and has an inflection point at μ σ;
Its shape is high in the middle and low on both sides, and the image is a bell curve above the X axis.
The area enclosed by the curve and the X axis is1;
When μ = 0 and σ 2 = 1, it is called standard normal distribution, and it is recorded as N(0, 1).
Drawing of normal distribution curve;
1 can be realized by Excel, matlab, Geometer's Sketchpad, geogebra and other software in the computer.
2, through its characteristics, you can draw a rough figure by hand;
The general image is as follows:
The influence of 1 and μ on the image;
2. The influence of σ on images:
How to draw a nonparametric normal distribution map? Normal distribution map: Determine the interval between groups of data, and then draw a histogram according to the frequency of each data group, so as to observe whether the frequency division of data is normal. It is recommended to use mintab software, which is relatively simple. Moreover, it is extensive to judge the normality of data by graphics, and it should be investigated and determined by normal test.
Normal distribution, also known as "normal distribution" and Gaussian distribution, was first obtained by AbrahamdeMoivre in the asymptotic formula of binomial distribution. C.F. Gauss deduced it from another angle when studying the measurement error. Laplace and Gauss studied its properties. It is a very important probability distribution in mathematics, physics, engineering and other fields, and has great influence in many aspects of statistics.